Shulou(Shulou.com)06/01 Report--

Phenomenon description

Porting Mysql-based applications to the Dream database, we encountered the following two problems.

Scenario 1:

After completing the migration of the data and the application system, as soon as the application starts, we can see that the log box reports an error: unable to assign a value to the self-increment column.

[you can assign a value to a self-incrementing column only if you specify a column list and SET_IDENTITY_INSERT is ON]

Scenario 2:

SQL writing is not supported. Application execution encounters an error: [invalid ORDER BY sentence] [not GROUP BY expression]

Invalid ORDER BY clause of select v1 from test group by v1 order by v2 Matsumi Murr

Select v1 from test group by v1 order by v2;-- err is not an GROUP BY expression

Treatment method

This is the Mysql migration to the dream database, the need to know the basic knowledge points of the second: summary of three sentences.

-Dameng database, like Oracle, cannot directly assign values to self-incrementing columns. It is recommended to change the syntax of self-increment columns in Mysql to take sequence values, and the default attribute can still be maintained.

-Dameng database is compatible with Mysql's Group and Order, which do not conform to the SQL standard, but the database configuration file COMPATIBLE_MODE should be changed to 4.

How to set self-increment column properties that can be assigned

We can use the sequence as the default value to achieve the pluggability of the self-incrementing column, but note that the business logic is not equivalent and needs to be considered. (note: it is not reasonable to assign values to self-increment columns.)

Here is an example of self-incrementing column insertion for Mysql5.6:

Here is an example of replacing a self-incrementing column with a sequence value default on DM (or Oracle) [Note: not exactly equivalent]:

How to change COMPATIBLE_MODE to 4

Log in to the database and execute this SQL: sp_set_para_value.

After the execution is successful, restart the database.

When changed to 4, syntax errors that were previously incompatible between group and order will become normal. Here is a simple example:

-- when the compatibility mode is 4, that is, sp_set_para_value is executed, that is, after restarting the Damon database service

Drop table if exists test

Create table test (v1 int,v2 int,v3 int)

Insert into test select level,level,level from dual connect by level

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.