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

error message

User, date Message
Written by malavikakswamy
3 years ago
Category: Import/Export
9 posts since Mon, 18 Jul 11
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!!
Written by malavikakswamy
3 years ago
9 posts since Mon, 18 Jul 11
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...
Written by malavikakswamy
3 years ago
9 posts since Mon, 18 Jul 11
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...
Written by ansgar
3 years ago
4785 posts since Fri, 07 Apr 06
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

Written by malavikakswamy
3 years ago
9 posts since Mon, 18 Jul 11
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?
Written by ansgar
3 years ago
4785 posts since Fri, 07 Apr 06
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.