Re: [PHP] Help needed with mysql import
You shouldn't have a default value in an auto increment field. You can set AUTO_INCREMENT to 0 and start with 1, but as auto increment is a unique field and its automagically incremented, you should not set a default value on it... -- Sent from my Android phone with K-9 Mail. Please excuse my brevity. Thijs Lensselink d...@lenss.nl wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 03/02/2011 07:56 AM, Ashim Kapoor wrote: Dear all, I am trying to make a website with php and I found the following code in a book and I am trying to import it. The following are the beginning of the file i am trying to import with the command mysql -u root -pmypassword certainty dump I get the following error : ERROR 1067 (42000) at line 9: Invalid default value for 'id' but when I see line 9 i see the value '0' for id which seems ok to me, I also tried removing the quotes but same error. Can someone guide me ? Thank you, Ashim # MySQL dump 7.1 # # Host: [host deleted] Database: certainty #_ # Server version 3.22.32 # # Table structure for table 'high_scores' # CREATE TABLE high_scores ( id int(11) DEFAULT '0' NOT NULL auto_increment, name varchar(30), answer_count int(11), credit double(16,4), PRIMARY KEY (id) ); It's not really a PHP question. But here goes. Your first field id is an auto_increment field this means the counter goes up by every insert. Normally this will start at 1 not 0. So either change the 0 to a higher number or remove the auto_increment part before you import change the 0 after and alter the table to put back auto_increment -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) iQIcBAEBAgAGBQJNbfOvAAoJEMffsHAOnubXC7UP/1k5qA4TDxDnUKrYZLV/rl9s bLRPTQ21riFMIzt0ne14No4+MTwjNtfVAdSnjpCWEZP+Y2MEixaiz8gIcOt9GIOD f9QPJZFEIcVADs3lqeS88eqdgRBNiYy3x2PHyslR3jtuaeFrRvxOLBTgBISq6Ih4 Dd5nRCbo6WObQ5e26HhbDeMJDAnOw4iQMjpoxc6UD9syxkJrORYw6XFvEmJA/QNF RDTNIO7P62ROamGor8urmPdfIemFLyqjD5YAQ64O6aWVHp0ehjO4l1xPWCeI84sV 2g8C3yqi06UjYOE8NHrf64VYcQtvLFkJbzGT6mmPwEP0gBdqX6o2YDwnudv7+APN F5zoVBv/7wygFaP+P0zgJ+EWVML35VfJFuq5VCH3CUk1hROS4X/JtsNXdVkAbaFA BpEhQ4jN0x/34HrI1cWjEUwaUuU6m9XoMIuO+1tQRLFatEW9I5z1c3hrJsPUNImX qSxEGLAZyA7tex++4YFn8DZXWz4mdllI7yejRe0nl1vl4Nn1+t2se/vF0TfZAGdB HgDeUWTdY/N2KeT4z9gPjGEDlRp8Wqo13Sv1yVhzWDdAJQdWaH8+Kk0GCI0jBrgT Pthmjr0e4bKCW19SJtL7/mTRU12qX/kbjMG5JqIh1ixn72qgqcvkTjgvEeQ1Y0DM xBBFUUedwoKevRJI05/2 =mdri -END PGP SIGNATURE- -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Help needed with mysql import
On 3/2/2011 4:25 AM, Alex wrote: You shouldn't have a default value in an auto increment field. You can set AUTO_INCREMENT to 0 and start with 1, but as auto increment is a unique field and its automagically incremented, you should not set a default value on it... This is fine and dandy until you want to do a restore of your data. With a restore, you will probably want to retain your auto-increment IDs since they are probably used else where in your DB. Jim -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Help needed with mysql import
On 3/2/2011 9:48 AM, Ashley Sheridan wrote: Jim Lucas li...@cmsws.com wrote: On 3/2/2011 4:25 AM, Alex wrote: You shouldn't have a default value in an auto increment field. You can set AUTO_INCREMENT to 0 and start with 1, but as auto increment is a unique field and its automagically incremented, you should not set a default value on it... This is fine and dandy until you want to do a restore of your data. With a restore, you will probably want to retain your auto-increment IDs since they are probably used else where in your DB. Jim -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php Restoring data wouldn't need default values though, would it. You can just perform a regular insert statement and update the next id value to be used if necessary. Also, I'm not 100% sure on this one, but I thought that some versions of Mysql had problems with a 0 in an auto_inc field? Thanks Ash Ash, Sorry, I didn't mean to imply that the AUTO_INCREMENT set to 0 is fine. Doing an export of the table structure for one of my smaller tables (that is empty), I see that the AUTO_INCREMENT value is set to 1 by default. CREATE TABLE IF NOT EXISTS `ajax_products` ( `id` int(11) NOT NULL auto_increment, `name` varchar(64) NOT NULL default '', `version` tinytext NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ; It doesn't show a default value, but rather a default starting point for the AUTO_INCREMENT OP: Show us an export of your db table structure. Jim -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Fwd: [PHP] Help needed with mysql import
-- Forwarded message -- From: Ashim Kapoor ashimkap...@gmail.com Date: Thu, Mar 3, 2011 at 7:22 AM Subject: Re: [PHP] Help needed with mysql import To: Jim Lucas li...@cmsws.com CREATE TABLE IF NOT EXISTS `ajax_products` ( `id` int(11) NOT NULL auto_increment, `name` varchar(64) NOT NULL default '', `version` tinytext NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ; It doesn't show a default value, but rather a default starting point for the AUTO_INCREMENT OP: Show us an export of your db table structure. Dear Jim, I read this in a book. I dont have the table to show you. Thank you, Ashim
[PHP] Help needed with mysql import
Dear all, I am trying to make a website with php and I found the following code in a book and I am trying to import it. The following are the beginning of the file i am trying to import with the command mysql -u root -pmypassword certainty dump I get the following error : ERROR 1067 (42000) at line 9: Invalid default value for 'id' but when I see line 9 i see the value '0' for id which seems ok to me, I also tried removing the quotes but same error. Can someone guide me ? Thank you, Ashim # MySQL dump 7.1 # # Host: [host deleted] Database: certainty # # Server version 3.22.32 # # Table structure for table 'high_scores' # CREATE TABLE high_scores ( id int(11) DEFAULT '0' NOT NULL auto_increment, name varchar(30), answer_count int(11), credit double(16,4), PRIMARY KEY (id) );
Re: [PHP] Help needed with mysql import
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 03/02/2011 07:56 AM, Ashim Kapoor wrote: Dear all, I am trying to make a website with php and I found the following code in a book and I am trying to import it. The following are the beginning of the file i am trying to import with the command mysql -u root -pmypassword certainty dump I get the following error : ERROR 1067 (42000) at line 9: Invalid default value for 'id' but when I see line 9 i see the value '0' for id which seems ok to me, I also tried removing the quotes but same error. Can someone guide me ? Thank you, Ashim # MySQL dump 7.1 # # Host: [host deleted] Database: certainty # # Server version 3.22.32 # # Table structure for table 'high_scores' # CREATE TABLE high_scores ( id int(11) DEFAULT '0' NOT NULL auto_increment, name varchar(30), answer_count int(11), credit double(16,4), PRIMARY KEY (id) ); It's not really a PHP question. But here goes. Your first field id is an auto_increment field this means the counter goes up by every insert. Normally this will start at 1 not 0. So either change the 0 to a higher number or remove the auto_increment part before you import change the 0 after and alter the table to put back auto_increment -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) iQIcBAEBAgAGBQJNbfOvAAoJEMffsHAOnubXC7UP/1k5qA4TDxDnUKrYZLV/rl9s bLRPTQ21riFMIzt0ne14No4+MTwjNtfVAdSnjpCWEZP+Y2MEixaiz8gIcOt9GIOD f9QPJZFEIcVADs3lqeS88eqdgRBNiYy3x2PHyslR3jtuaeFrRvxOLBTgBISq6Ih4 Dd5nRCbo6WObQ5e26HhbDeMJDAnOw4iQMjpoxc6UD9syxkJrORYw6XFvEmJA/QNF RDTNIO7P62ROamGor8urmPdfIemFLyqjD5YAQ64O6aWVHp0ehjO4l1xPWCeI84sV 2g8C3yqi06UjYOE8NHrf64VYcQtvLFkJbzGT6mmPwEP0gBdqX6o2YDwnudv7+APN F5zoVBv/7wygFaP+P0zgJ+EWVML35VfJFuq5VCH3CUk1hROS4X/JtsNXdVkAbaFA BpEhQ4jN0x/34HrI1cWjEUwaUuU6m9XoMIuO+1tQRLFatEW9I5z1c3hrJsPUNImX qSxEGLAZyA7tex++4YFn8DZXWz4mdllI7yejRe0nl1vl4Nn1+t2se/vF0TfZAGdB HgDeUWTdY/N2KeT4z9gPjGEDlRp8Wqo13Sv1yVhzWDdAJQdWaH8+Kk0GCI0jBrgT Pthmjr0e4bKCW19SJtL7/mTRU12qX/kbjMG5JqIh1ixn72qgqcvkTjgvEeQ1Y0DM xBBFUUedwoKevRJI05/2 =mdri -END PGP SIGNATURE- -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php