Re: [PHP] Help needed with mysql import
On 3/2/2011 9:48 AM, Ashley Sheridan wrote: > "Jim Lucas" 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
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
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 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
-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
