Re: auto_increment trouble (not the usual check || alter table solution, though)
On 5/16/05, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Hi, with a similar structure, you can have : mysql select * from users where uid =262140; ++--+ | uid| nickname | ++--+ | 262140 | text | | 262141 | text | | 262142 | text | | 262143 | text | | 262144 | text | ++--+ 5 rows in set (0.00 sec) it's surprising that you can insert NULL in a primary key auto_increment. second, with only 1 values, if the auto_increment reached 2147483647 this means that you have an intensive delete, or the auto_increment had been altered. you can create table toto like users, and insert data again into toto. This will reincremente between 1 and 10xxx. Then rename toto to users. Mathias, Unfortunately, this didn't work out. I created, by hand, a table with the exact same definition although with a different name (newusers). Then, using a dump from the users table, I restored the data into the new table. Right after restoring the data, a show table status like 'newusers' shows that the auto_increment value is 2147483647. This could lead to three paths: the problem would be somewhere in the data, in the restore procedure or in the mysql engine itself. I've analyzed the data by hand (all the ~10900 lines), and I am sure that there isn't a single uid above 10900. Does this make any sense? Thanks in advance for any leads, Ricardo Oliveira -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
auto_increment trouble (not the usual check || alter table solution, though)
Hi everyone, First time on the list, although I've been (quietly) a (not-so)long-time quiet observer. This isn't a FAQ and I can assure you I've tried every procedure out there (both on and off-list) to solve it. A couple weeks ago, I started having problems with a MyISAM table which holds records from user profiles. It's a very simple table, with one auto_increment field and a bunch of other field - nothing out of the ordinary. To those of you familiar with slashcode, it's an old version of the users table: CREATE TABLE users ( uid int(11) NOT NULL auto_increment, nickname varchar(20) NOT NULL default '', realemail varchar(50) NOT NULL default '', fakeemail varchar(50) default NULL, homepage varchar(100) default NULL, passwd varchar(12) NOT NULL default '', sig varchar(160) default NULL, seclev int(11) NOT NULL default '0', matchname varchar(20) default NULL, banned int(11) NOT NULL default '0', permmod int(11) NOT NULL default '0', PRIMARY KEY (uid), KEY login (uid,passwd,nickname), KEY chk4user (nickname,realemail), KEY chk4email (realemail) ) TYPE=MyISAM PACK_KEYS=1; We have about 10k lines (more precisely, 10885 lines). Our code inserts data using a insert into users values (NULL, .) statement which hasn't changed over the last few years. Recently, we started seeing the last record with a _way_ high uid value - more precisely, 2147483647 (which keeps new inserts from happening). Obviously this is a problem with the auto_increment mechanism, and we have followed all the (documented) and traditional approaches, which follow: * search the row with the wrong uid and correct it: - upon inserting a new record, its uid will be 2147483647; * correct the uid and modify the auto_increment value from the table: mysql alter table users AUTO_INCREMENT=10900; Query OK, 10885 rows affected (0.33 sec) Records: 10885 Duplicates: 0 Warnings: 0 mysql show table status like 'users'; +---+++---++-+-+--+---++-+-+-++-+ | Name | Type | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Create_options | Comment | +---+++---++-+-+--+---++-+-+-++-+ | users | MyISAM | Dynamic| 10885 | 68 | 748604 | 4294967295 | 1051648 | 0 | --2147483647-- | 2005-05-16 20:23:49 | 2005-05-16 20:23:49 | 2005-05-16 20:23:49 | pack_keys=1| | +---+++---++-+-+--+---++-+-+-++-+ * dump the table, make sure it doesn't have any uid above =~10k, delete the table and restore the table (and data) from disk: - data is okay, select * from users where uid11k shows ZERO records - insert into users values (NULL,.) results in a new row with uid=2147483647 * several combinations of CHECK TABLE, myisamchk (-r|-o|), dump remore_wrong_record restore, drop the entire database: - data is OKAY, select results in no records above 11k, next insert will have uid=2147483647 The table fits in about 1MB on disk, and its indexes fit in about 700kB. Does anyone have a clue? Any help is highly appreciated. Best of regards, Ricardo Oliveira PS: Sorry for such a long message. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: auto_increment trouble (not the usual check || alter table solution, though)
This may not be a very elegant solution, but you can do this just to get the inserts going again: ALTER TABLE users MODIFY COLUMN uid INTEGER UNSIGNED NOT NULL AUTO_INCREMENT; If you don't have any negative uids, then you will be able to store 2 billion more uids. Just out of curiosity, have you DROPPED re-created the table? -- Partha Dutta, Senior Consultant MySQL Inc, NY, USA, www.mysql.com Are you MySQL certified? www.mysql.com/certification -Original Message- From: Ricardo Oliveira [mailto:[EMAIL PROTECTED] Sent: Monday, May 16, 2005 5:00 PM To: mysql@lists.mysql.com Subject: auto_increment trouble (not the usual check || alter table solution, though) Hi everyone, First time on the list, although I've been (quietly) a (not-so)long-time quiet observer. This isn't a FAQ and I can assure you I've tried every procedure out there (both on and off-list) to solve it. A couple weeks ago, I started having problems with a MyISAM table which holds records from user profiles. It's a very simple table, with one auto_increment field and a bunch of other field - nothing out of the ordinary. To those of you familiar with slashcode, it's an old version of the users table: CREATE TABLE users ( uid int(11) NOT NULL auto_increment, nickname varchar(20) NOT NULL default '', realemail varchar(50) NOT NULL default '', fakeemail varchar(50) default NULL, homepage varchar(100) default NULL, passwd varchar(12) NOT NULL default '', sig varchar(160) default NULL, seclev int(11) NOT NULL default '0', matchname varchar(20) default NULL, banned int(11) NOT NULL default '0', permmod int(11) NOT NULL default '0', PRIMARY KEY (uid), KEY login (uid,passwd,nickname), KEY chk4user (nickname,realemail), KEY chk4email (realemail) ) TYPE=MyISAM PACK_KEYS=1; We have about 10k lines (more precisely, 10885 lines). Our code inserts data using a insert into users values (NULL, .) statement which hasn't changed over the last few years. Recently, we started seeing the last record with a _way_ high uid value - more precisely, 2147483647 (which keeps new inserts from happening). Obviously this is a problem with the auto_increment mechanism, and we have followed all the (documented) and traditional approaches, which follow: * search the row with the wrong uid and correct it: - upon inserting a new record, its uid will be 2147483647; * correct the uid and modify the auto_increment value from the table: mysql alter table users AUTO_INCREMENT=10900; Query OK, 10885 rows affected (0.33 sec) Records: 10885 Duplicates: 0 Warnings: 0 mysql show table status like 'users'; +---+++---++-+ -+--+---++ -+-+-++--- --+ | Name | Type | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Create_options | Comment | +---+++---++-+ -+--+---++ -+-+-++--- --+ | users | MyISAM | Dynamic| 10885 | 68 | 748604 | 4294967295 | 1051648 | 0 | --2147483647-- | 2005-05-16 20:23:49 | 2005-05-16 20:23:49 | 2005-05-16 20:23:49 | pack_keys=1| | +---+++---++-+ -+--+---++ -+-+-++--- --+ * dump the table, make sure it doesn't have any uid above =~10k, delete the table and restore the table (and data) from disk: - data is okay, select * from users where uid11k shows ZERO records - insert into users values (NULL,.) results in a new row with uid=2147483647 * several combinations of CHECK TABLE, myisamchk (-r|-o|), dump remore_wrong_record restore, drop the entire database: - data is OKAY, select results in no records above 11k, next insert will have uid=2147483647 The table fits in about 1MB on disk, and its indexes fit in about 700kB. Does anyone have a clue? Any help is highly appreciated. Best of regards, Ricardo Oliveira PS: Sorry for such a long message. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: auto_increment trouble (not the usual check || alter table solution, though)
Partha, On 5/16/05, Partha Dutta [EMAIL PROTECTED] wrote: This may not be a very elegant solution, but you can do this just to get the inserts going again: ALTER TABLE users MODIFY COLUMN uid INTEGER UNSIGNED NOT NULL AUTO_INCREMENT; If you don't have any negative uids, then you will be able to store 2 billion more uids. Although I haven't tried this, I guess it'd possibly work - but I'm trying to have a linear uid column, as it has relations with some other tables (where the linearity of this field is crucial). Nevertheless, I'll try it ASAP and I'll get back to you. Thank you, Ricardo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: auto_increment trouble (not the usual check || alter table solution, though)
Hi, with a similar structure, you can have : mysql select * from users where uid =262140; ++--+ | uid| nickname | ++--+ | 262140 | text | | 262141 | text | | 262142 | text | | 262143 | text | | 262144 | text | ++--+ 5 rows in set (0.00 sec) it's surprising that you can insert NULL in a primary key auto_increment. second, with only 1 values, if the auto_increment reached 2147483647 this means that you have an intensive delete, or the auto_increment had been altered. you can create table toto like users, and insert data again into toto. This will reincremente between 1 and 10xxx. Then rename toto to users. Mathias Selon Ricardo Oliveira [EMAIL PROTECTED]: Hi everyone, First time on the list, although I've been (quietly) a (not-so)long-time quiet observer. This isn't a FAQ and I can assure you I've tried every procedure out there (both on and off-list) to solve it. A couple weeks ago, I started having problems with a MyISAM table which holds records from user profiles. It's a very simple table, with one auto_increment field and a bunch of other field - nothing out of the ordinary. To those of you familiar with slashcode, it's an old version of the users table: CREATE TABLE users ( uid int(11) NOT NULL auto_increment, nickname varchar(20) NOT NULL default '', realemail varchar(50) NOT NULL default '', fakeemail varchar(50) default NULL, homepage varchar(100) default NULL, passwd varchar(12) NOT NULL default '', sig varchar(160) default NULL, seclev int(11) NOT NULL default '0', matchname varchar(20) default NULL, banned int(11) NOT NULL default '0', permmod int(11) NOT NULL default '0', PRIMARY KEY (uid), KEY login (uid,passwd,nickname), KEY chk4user (nickname,realemail), KEY chk4email (realemail) ) TYPE=MyISAM PACK_KEYS=1; We have about 10k lines (more precisely, 10885 lines). Our code inserts data using a insert into users values (NULL, .) statement which hasn't changed over the last few years. Recently, we started seeing the last record with a _way_ high uid value - more precisely, 2147483647 (which keeps new inserts from happening). Obviously this is a problem with the auto_increment mechanism, and we have followed all the (documented) and traditional approaches, which follow: * search the row with the wrong uid and correct it: - upon inserting a new record, its uid will be 2147483647; * correct the uid and modify the auto_increment value from the table: mysql alter table users AUTO_INCREMENT=10900; Query OK, 10885 rows affected (0.33 sec) Records: 10885 Duplicates: 0 Warnings: 0 mysql show table status like 'users'; +---+++---++-+-+--+---++-+-+-++-+ | Name | Type | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Create_options | Comment | +---+++---++-+-+--+---++-+-+-++-+ | users | MyISAM | Dynamic| 10885 | 68 | 748604 | 4294967295 | 1051648 | 0 | --2147483647-- | 2005-05-16 20:23:49 | 2005-05-16 20:23:49 | 2005-05-16 20:23:49 | pack_keys=1| | +---+++---++-+-+--+---++-+-+-++-+ * dump the table, make sure it doesn't have any uid above =~10k, delete the table and restore the table (and data) from disk: - data is okay, select * from users where uid11k shows ZERO records - insert into users values (NULL,.) results in a new row with uid=2147483647 * several combinations of CHECK TABLE, myisamchk (-r|-o|), dump remore_wrong_record restore, drop the entire database: - data is OKAY, select results in no records above 11k, next insert will have uid=2147483647 The table fits in about 1MB on disk, and its indexes fit in about 700kB. Does anyone have a clue? Any help is highly appreciated. Best of regards, Ricardo Oliveira PS: Sorry for such a long message. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: auto_increment trouble (not the usual check || alter table solution, though)
Partha, Partha Dutta wrote: This may not be a very elegant solution, but you can do this just to get the inserts going again: ALTER TABLE users MODIFY COLUMN uid INTEGER UNSIGNED NOT NULL AUTO_INCREMENT; If you don't have any negative uids, then you will be able to store 2 billion more uids. Just out of curiosity, have you DROPPED re-created the table? Sorry about the other message (from my lurker, always-silent identity). I have tried DROPping both the table and the whole database to no avail. As I said in the other reply, I'll try your suggestion, but I really need a solution to this problem other than having a huge leap in the uid values. Thanks, Ricardo Oliveira -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Table solution
Hello All, I want to copy a table from a webserver that has MySQL database server located on it, then, ftp to my local machine to insert into my local database. any ideas on how to do this? my goal is to create an Excel spreadsheet from the table for distribution. I have created the spreadsheet using the database on my local machine, but I want the database on the webserver. I appreciate any direction or guidance. sincerely, Lamar - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Table solution
Mysqlfront has this nice feature where u can open 2 database and copy it From 1 to the other - Chris Kay (Systems Development) Techex Communications Website: www.techex.com.au Email: [EMAIL PROTECTED] Telephone: 1300 88 111 2 - Fax: (02) 9970 5788 - -Original Message- From: Lamar [mailto:lamarts;flash.net] Sent: Tuesday, November 12, 2002 1:15 PM To: [EMAIL PROTECTED] Subject: Table solution Hello All, I want to copy a table from a webserver that has MySQL database server located on it, then, ftp to my local machine to insert into my local database. any ideas on how to do this? my goal is to create an Excel spreadsheet from the table for distribution. I have created the spreadsheet using the database on my local machine, but I want the database on the webserver. I appreciate any direction or guidance. sincerely, Lamar - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Table solution
phpMyAdmin has an 'export to csv or Excel' function. If you are running php this will produce a CSV file you can import directly into Excel http://www.phpwizard.net/projects/phpMyAdmin/ hth Peter --- Excellence in internet and open source software --- Sunmaia Birmingham UK www.sunmaia.net tel. 0121-242-1473 International +44-121-242-1473 --- -Original Message- From: Lamar [mailto:lamarts;flash.net] Sent: 12 November 2002 02:15 To: [EMAIL PROTECTED] Subject: Table solution Hello All, I want to copy a table from a webserver that has MySQL database server located on it, then, ftp to my local machine to insert into my local database. any ideas on how to do this? my goal is to create an Excel spreadsheet from the table for distribution. I have created the spreadsheet using the database on my local machine, but I want the database on the webserver. I appreciate any direction or guidance. sincerely, Lamar - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Table solution
You can use mysqldump to export the data from your webserver to a file, ftp the file, and import into your local database. There are also some tools that might help -- urSQL (http://www.urbanresearch.com/ursql), for example, can export query results directly to Excel. This means you could connect urSQL directly to your webserver database (provided it's accessible), submit a select * from my_table, and export the results directly to an Excel file... There are other tools that provide similar functionality. If you want to do it manually: 1) mysqldump on the webserver (http://www.mysql.com/doc/en/mysqldump.html) 2) Transfer the file to your local machine 3) Import into mysql (mysql dump-file.sql) On Mon, 11 Nov 2002, Lamar wrote: I want to copy a table from a webserver that has MySQL database server located on it, then, ftp to my local machine to insert into my local database. any ideas on how to do this? my goal is to create an Excel spreadsheet from the table for distribution. I have created the spreadsheet using the database on my local machine, but I want the database on the webserver. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Table solution
Thanks to all who responded to my post. I had everything but the import process right. I was trying to use mysqlimport databasename /home/Lamar/filename. but kept getting error message. I can't remember offhand what the error message was, but I used the method below and it worked. sorry for the inconvenience. I am short on time. sincerely, Lamar -Original Message- From: [EMAIL PROTECTED] [mailto:jurban;urbanresearch.com] Sent: Monday, November 11, 2002 8:44 PM To: Lamar Cc: [EMAIL PROTECTED] Subject: Re: Table solution You can use mysqldump to export the data from your webserver to a file, ftp the file, and import into your local database. There are also some tools that might help -- urSQL (http://www.urbanresearch.com/ursql), for example, can export query results directly to Excel. This means you could connect urSQL directly to your webserver database (provided it's accessible), submit a select * from my_table, and export the results directly to an Excel file... There are other tools that provide similar functionality. If you want to do it manually: 1) mysqldump on the webserver (http://www.mysql.com/doc/en/mysqldump.html) 2) Transfer the file to your local machine 3) Import into mysql (mysql dump-file.sql) On Mon, 11 Nov 2002, Lamar wrote: I want to copy a table from a webserver that has MySQL database server located on it, then, ftp to my local machine to insert into my local database. any ideas on how to do this? my goal is to create an Excel spreadsheet from the table for distribution. I have created the spreadsheet using the database on my local machine, but I want the database on the webserver. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
