error message

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!!

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...

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...

The "SET FOREIGN ..." is done at the very end of the dump file, which makes it likely that everything is probably ok.

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

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?

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.