distal-attribute
distal-attribute
distal-attribute
distal-attribute

error message

malavikakswamy posted 3 years ago in Import/Export
Hello,

I have a .sql file and I am tryin to import it into Heidi SQL. After the import gets over it gives me an error saying that SQL_MODE cannot be set to NULL. Also when I try importing the dump many times it gives me different rows in the tables... The number of rows is not consistent with every import of the same file. Please can you tell me why is that error message coming.

The code is as follows:
/*!40100 SET CHARACTER SET latin1*/;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='ANSI'*/;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0*/;

and the only other place where sql mode is there is towards the end of the code where it is as follows:

/*!40101 SET =@OLD_SQL_MODE;*/
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;*/

Please help!!
malavikakswamy posted 3 years ago
the above post has the wrong code... its actually as follows:

/*!40100 SET CHARACTER SET latin1*/;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='ANSI'*/;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0*/;

and

/*!40101 SET SQL_MODE=@OLD_SQL_MODE*/;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS*/;

it gives me the error variable sql_mode cannot be set to the value of null...
malavikakswamy posted 3 years ago
I would be very thankful if someone could help me with this... I tried looking at other forums as well.. but have not found a solution to the problem... Because of the above error I am not sure if the database I have imported has the correct no. of rows in each table or not...
ansgar posted 3 years ago
The "SET FOREIGN ..." is done at the very end of the dump file, which makes it likely that everything is probably ok.

malavikakswamy wrote: The number of rows is not consistent with every import of the same file


Most obviously you have INNODB tables, which report their row number as an estimation. Each time you refresh the database view, you will find a different number of rows in such tables. You can only be sure if you fire a

SELECT COUNT(*) FROM mytable

malavikakswamy posted 3 years ago
Thank you for the reply.Is it safe to assume that the database has been imported correctly even if the error variable sql_mode cannot be null occurs towards the end of the import?
ansgar posted 3 years ago
Well you should look into your dump and see what comes after the sql_mode stuff which failed. In a normal HeidiSQL dump there are only two additional queries for restoring the connection variables to the previous state, nothing to worry about.

Please login to leave a reply, or register at first.