alter table and 'something wrong in indexes'?
Hi! I use mysql/innodb tables on aws services in a small table (about 2M records) I changed some columns datatypes from unsigned int to decimal and from float to decimal I didn't change anything about primary key or other indexes after the change (done without troubles), all my queries where really slow and the "explain" was different; it was like the primary key index was corrupted or not used anymore (even if the 'check table' was fine) I had to drop an create again the table with the new datatypes and all was fine again any idea about what was wrong? I guess is not an aws problem, even if that is the first time I have a similar error thank you in advance bye bye MAS! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
alter table for foreign key
Looking at the docs: http://dev.mysql.com/doc/refman/5.6/en/create-table-foreign-keys.html how do I alter the child table, links, so that it has a foreign key with the parent table, feeds? The feed_id field in links should, in fact, be constrained by the foreign key of feeds with a RESTRICT reference option. mysql mysql show tables; +---+ | Tables_in_rome_aggregator | +---+ | feeds | | links | | pages | +---+ 3 rows in set (0.00 sec) mysql mysql describe feeds; +--+--+--+-+-++ | Field| Type | Null | Key | Default | Extra | +--+--+--+-+-++ | id | int(11) | NO | PRI | NULL| auto_increment | | created | datetime | NO | | NULL|| | accessed | varchar(45) | NO | | NULL|| | url | varchar(767) | NO | UNI | NULL|| | status | int(11) | NO | | 0 || +--+--+--+-+-++ 5 rows in set (0.05 sec) mysql mysql describe links; +-+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-++ | id | int(11) | NO | PRI | NULL| auto_increment | | created | datetime | NO | | NULL|| | feed_id | int(11) | NO | | 0 || | link| varchar(767) | NO | UNI | NULL|| | status | int(11) | NO | | 0 || +-+--+--+-+-++ 5 rows in set (0.00 sec) mysql While I know that I should alter the table, I don't know the syntax to establish the foreign key constraint. thanks, Thufir -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: alter table for foreign key
Hi, This is an example: ALTER TABLE cart ADD CONSTRAINT fk_cart_customers FOREIGN KEY (custid) REFERENCES customers (custid) ON DELETE CASCADE ON UPDATE NO ACTION; (take from the GUI tool Database Workbench, avoids having to know the syntax ;) ) With regards, Martijn Tonies Upscene Productions http://www.upscene.com Download Database Workbench for Oracle, MS SQL Server, Sybase SQL Anywhere, MySQL, InterBase, NexusDB and Firebird! -Original Message- From: thufir Sent: Monday, November 17, 2014 10:42 AM To: mysql@lists.mysql.com Subject: alter table for foreign key Looking at the docs: http://dev.mysql.com/doc/refman/5.6/en/create-table-foreign-keys.html how do I alter the child table, links, so that it has a foreign key with the parent table, feeds? The feed_id field in links should, in fact, be constrained by the foreign key of feeds with a RESTRICT reference option. mysql mysql show tables; +---+ | Tables_in_rome_aggregator | +---+ | feeds | | links | | pages | +---+ 3 rows in set (0.00 sec) mysql mysql describe feeds; +--+--+--+-+-++ | Field| Type | Null | Key | Default | Extra | +--+--+--+-+-++ | id | int(11) | NO | PRI | NULL| auto_increment | | created | datetime | NO | | NULL|| | accessed | varchar(45) | NO | | NULL|| | url | varchar(767) | NO | UNI | NULL|| | status | int(11) | NO | | 0 || +--+--+--+-+-++ 5 rows in set (0.05 sec) mysql mysql describe links; +-+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-++ | id | int(11) | NO | PRI | NULL| auto_increment | | created | datetime | NO | | NULL|| | feed_id | int(11) | NO | | 0 || | link| varchar(767) | NO | UNI | NULL|| | status | int(11) | NO | | 0 || +-+--+--+-+-++ 5 rows in set (0.00 sec) mysql While I know that I should alter the table, I don't know the syntax to establish the foreign key constraint. thanks, Thufir -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: alter table for foreign key
On 14-11-17 01:42 AM, thufir wrote: Looking at the docs: http://dev.mysql.com/doc/refman/5.6/en/create-table-foreign-keys.html how do I alter the child table, links, so that it has a foreign key with the parent table, feeds? The workbench GUI came up with: ALTER TABLE `rome_aggregator`.`links` ADD INDEX `fk_links_1_idx` (`feed_id` ASC); ALTER TABLE `rome_aggregator`.`links` ADD CONSTRAINT `fk_links_1` FOREIGN KEY (`feed_id`) REFERENCES `rome_aggregator`.`feeds` (`id`) ON DELETE CASCADE ON UPDATE CASCADE; which would be a cascade, but otherwise seems correct. It's normal, or necessary, to have ADD INDEX? -Thufir -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: alter table for foreign key
On 14-11-17 01:42 AM, thufir wrote: Looking at the docs: http://dev.mysql.com/doc/refman/5.6/en/create-table-foreign-keys.html how do I alter the child table, links, so that it has a foreign key with the parent table, feeds? The workbench GUI came up with: ALTER TABLE `rome_aggregator`.`links` ADD INDEX `fk_links_1_idx` (`feed_id` ASC); In general, an index is used for performance, while a constraint is used for logical concepts, like uniqueness. Most often, the DBMS uses an index to implement these logical concepts, but it's better to understand the difference. A foreign key needs a unique constraint (either a primary of a secondary unique constraint) to point to: when there's no uniqueness in the parent table, it cannot point to a record in that table. With regards, Martijn Tonies Upscene Productions http://www.upscene.com Download Database Workbench for Oracle, MS SQL Server, Sybase SQL Anywhere, MySQL, InterBase, NexusDB and Firebird! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: alter table for foreign key
On Mon, 17 Nov 2014 10:59:15 +0100, Martijn Tonies (Upscene Productions) wrote: In general, an index is used for performance, while a constraint is used for logical concepts, like uniqueness. Most often, the DBMS uses an index to implement these logical concepts, but it's better to understand the difference. The GUI resulted in: | links | CREATE TABLE `links` ( `id` int(11) NOT NULL AUTO_INCREMENT, `created` datetime NOT NULL, `feed_id` int(11) NOT NULL DEFAULT '0', `link` varchar(767) NOT NULL, `status` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`id`), UNIQUE KEY `UNQ_links_0` (`link`), KEY `fk_links_1_idx` (`feed_id`), CONSTRAINT `fk_links_1` FOREIGN KEY (`feed_id`) REFERENCES `feeds` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=270 DEFAULT CHARSET=latin1 | I'll be reading a bit more about this. thanks, Thufir -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
alter table modify syntax error
Hello, I'm trying to use a very basic alter table command to position a column after another column. This is the table as it exists now: mysql describe car_table; +-+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-++ | car_id | int(11) | NO | PRI | NULL | auto_increment | | vin | varchar(17) | YES | | NULL | | | color | varchar(10) | YES | | NULL | | | year | decimal(4,0) | YES | | NULL | | | make | varchar(10) | YES | | NULL | | | model | varchar(20) | YES | | NULL | | | howmuch | decimal(5,2) | YES | | NULL | | +-+--+--+-+-++ 7 rows in set (0.03 sec) I am trying to position the 'color' column after the 'model' column with the following command: mysql alter table car_table modify column color after model; And I'm getting the following error: ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'after model' at line 1 I'm just wondering what I'm doing wrong here, because the syntax looks correct to me! Thanks -- GPG me!! gpg --keyserver pool.sks-keyservers.net --recv-keys F186197B
Re: alter table modify syntax error
On 28-06-2014 19:11, Tim Dunphy wrote: Hello, I'm trying to use a very basic alter table command to position a column after another column. This is the table as it exists now: mysql describe car_table; +-+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-++ | car_id | int(11) | NO | PRI | NULL | auto_increment | | vin | varchar(17) | YES | | NULL | | | color | varchar(10) | YES | | NULL | | | year | decimal(4,0) | YES | | NULL | | | make | varchar(10) | YES | | NULL | | | model | varchar(20) | YES | | NULL | | | howmuch | decimal(5,2) | YES | | NULL | | +-+--+--+-+-++ 7 rows in set (0.03 sec) I am trying to position the 'color' column after the 'model' column with the following command: mysql alter table car_table modify column color after model; And I'm getting the following error: ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'after model' at line 1 Try: alter table car_table modify column color varchar(10) after model; / Carsten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: alter table modify syntax error
Cool guys, that did it.. ALTER TABLE car_table MODIFY COLUMN color VARCHAR(10) AFTER model; For some reason the book I'm following doesn't specify that you have to note the data type in moves! This helped. and thanks again. Tim On Sat, Jun 28, 2014 at 1:24 PM, Carsten Pedersen cars...@bitbybit.dk wrote: On 28-06-2014 19:11, Tim Dunphy wrote: Hello, I'm trying to use a very basic alter table command to position a column after another column. This is the table as it exists now: mysql describe car_table; +-+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-++ | car_id | int(11) | NO | PRI | NULL | auto_increment | | vin | varchar(17) | YES | | NULL | | | color | varchar(10) | YES | | NULL | | | year | decimal(4,0) | YES | | NULL | | | make | varchar(10) | YES | | NULL | | | model | varchar(20) | YES | | NULL | | | howmuch | decimal(5,2) | YES | | NULL | | +-+--+--+-+-++ 7 rows in set (0.03 sec) I am trying to position the 'color' column after the 'model' column with the following command: mysql alter table car_table modify column color after model; And I'm getting the following error: ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'after model' at line 1 Try: alter table car_table modify column color varchar(10) after model; / Carsten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- GPG me!! gpg --keyserver pool.sks-keyservers.net --recv-keys F186197B
Re: alter table modify syntax error
Hey guys, Sorry to hit you with one more. But I'm trying to use a positional statement in a column move based on what you all just taught me: mysql alter table modify column color varchar(10) sixth; But I am getting this error: ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'column color varchar(10) sixth' at line 1 Here's my table one more time for reference: mysql describe car_table; ++--+--+-+-++ | Field | Type | Null | Key | Default | Extra | ++--+--+-+-++ | car_id | int(11) | NO | PRI | NULL| auto_increment | | vin| varchar(17) | YES | | NULL|| | year | decimal(4,0) | YES | | NULL|| | make | varchar(10) | YES | | NULL|| | model | varchar(20) | YES | | NULL|| | color | varchar(10) | YES | | NULL|| | price | decimal(7,2) | YES | | NULL|| ++--+--+-+-++ 7 rows in set (0.01 sec) I appreciate your suggestions so far and it would be great if I could get some help with this one too. Thanks Tim On Sat, Jun 28, 2014 at 1:34 PM, Tim Dunphy bluethu...@gmail.com wrote: Cool guys, that did it.. ALTER TABLE car_table MODIFY COLUMN color VARCHAR(10) AFTER model; For some reason the book I'm following doesn't specify that you have to note the data type in moves! This helped. and thanks again. Tim On Sat, Jun 28, 2014 at 1:24 PM, Carsten Pedersen cars...@bitbybit.dk wrote: On 28-06-2014 19:11, Tim Dunphy wrote: Hello, I'm trying to use a very basic alter table command to position a column after another column. This is the table as it exists now: mysql describe car_table; +-+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-++ | car_id | int(11) | NO | PRI | NULL | auto_increment | | vin | varchar(17) | YES | | NULL | | | color | varchar(10) | YES | | NULL | | | year | decimal(4,0) | YES | | NULL | | | make | varchar(10) | YES | | NULL | | | model | varchar(20) | YES | | NULL | | | howmuch | decimal(5,2) | YES | | NULL | | +-+--+--+-+-++ 7 rows in set (0.03 sec) I am trying to position the 'color' column after the 'model' column with the following command: mysql alter table car_table modify column color after model; And I'm getting the following error: ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'after model' at line 1 Try: alter table car_table modify column color varchar(10) after model; / Carsten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- GPG me!! gpg --keyserver pool.sks-keyservers.net --recv-keys F186197B -- GPG me!! gpg --keyserver pool.sks-keyservers.net --recv-keys F186197B
RE: alter table modify syntax error
Hi Tim, -Original Message- From: Tim Dunphy [mailto:bluethu...@gmail.com] Sent: Sunday, 29 June 2014 03:45 Cc: mysql@lists.mysql.com Subject: Re: alter table modify syntax error Hey guys, Sorry to hit you with one more. But I'm trying to use a positional statement in a column move based on what you all just taught me: mysql alter table modify column color varchar(10) sixth; But I am getting this error: ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'column color varchar(10) sixth' at line 1 The syntax sixth is not a supported syntax. You should use the syntax AFTER column_name where you replace column_name with the column name you want to position the modified column after. See also: https://dev.mysql.com/doc/refman/5.5/en/alter-table.html Best regards, Jesper Krogh MySQL Support -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: alter table modify syntax error
The syntax sixth is not a supported syntax. You should use the syntax AFTER column_name where you replace column_name with the column name you want to position the modified column after. Oh thanks. That's actually what I ended up doing after I got frustrated with that error. I was following the book 'Head First SQL' which was suggesting that you could do something like what this user was trying in this stack overflow thread: http://stackoverflow.com/questions/19175240/re-arranging-columns-in-mysql-using-position-keywords-such-as-first-second But the answer in that thread too suggests that this is wrong. So is the Head First SQL book just referring to an outdated syntax that doesn't work anymore? I can't imagine that it never worked if it's in that book. But hey ya never know! ;) Thanks Tim On Sat, Jun 28, 2014 at 7:46 PM, Jesper Wisborg Krogh my...@wisborg.dk wrote: Hi Tim, -Original Message- From: Tim Dunphy [mailto:bluethu...@gmail.com] Sent: Sunday, 29 June 2014 03:45 Cc: mysql@lists.mysql.com Subject: Re: alter table modify syntax error Hey guys, Sorry to hit you with one more. But I'm trying to use a positional statement in a column move based on what you all just taught me: mysql alter table modify column color varchar(10) sixth; But I am getting this error: ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'column color varchar(10) sixth' at line 1 The syntax sixth is not a supported syntax. You should use the syntax AFTER column_name where you replace column_name with the column name you want to position the modified column after. See also: https://dev.mysql.com/doc/refman/5.5/en/alter-table.html Best regards, Jesper Krogh MySQL Support -- GPG me!! gpg --keyserver pool.sks-keyservers.net --recv-keys F186197B
RE: alter table modify syntax error
Hi Tim, -Original Message- From: Tim Dunphy [mailto:bluethu...@gmail.com] Sent: Sunday, 29 June 2014 10:09 To: Jesper Wisborg Krogh Cc: mysql@lists.mysql.com Subject: Re: alter table modify syntax error The syntax sixth is not a supported syntax. You should use the syntax AFTER column_name where you replace column_name with the column name you want to position the modified column after. Oh thanks. That's actually what I ended up doing after I got frustrated with that error. I was following the book 'Head First SQL' which was suggesting that you could do something like what this user was trying in this stack overflow thread: http://stackoverflow.com/questions/19175240/re-arranging-columns-in- mysql-using-position-keywords-such-as-first-second But the answer in that thread too suggests that this is wrong. So is the Head First SQL book just referring to an outdated syntax that doesn't work anymore? I can't imagine that it never worked if it's in that book. But hey ya never know! ;) Given the title of the book is Head First SQL and not Head First MySQL it probably isn't exclusively using syntax for MySQL. While SQL is a standard the various SQL databases are not completely identical with the syntax they support. This may be due to not completely conforming to the standard, using different versions of the SQL standard, or that there is not standard for that operation. Best regards, Jesper Krogh MySQL Support -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: alter table modify syntax error
Given the title of the book is Head First SQL and not Head First MySQL it probably isn't exclusively using syntax for MySQL. While SQL is a standard the various SQL databases are not completely identical with the syntax they support. This may be due to not completely conforming to the standard, using different versions of the SQL standard, or that there is not standard for that operation. Hey, that's some good input. Thanks and makes total sense. I guess the reason I thought I could use that syntax is that the book uses MySQL for all it's examples and explains that it does so because MySQL is a free and open source version of SQL that's easy to install. But maybe you're right and they do depart into other syntaxes of SQL. I just don't know where they got that 'first, second, third, etc' version of the alter table syntax from. Definitely not sweatin' this detail tho, I am totally fine with what you showed me that works. Thanks again for your input! Tim On Sat, Jun 28, 2014 at 9:14 PM, Jesper Wisborg Krogh my...@wisborg.dk wrote: Hi Tim, -Original Message- From: Tim Dunphy [mailto:bluethu...@gmail.com] Sent: Sunday, 29 June 2014 10:09 To: Jesper Wisborg Krogh Cc: mysql@lists.mysql.com Subject: Re: alter table modify syntax error The syntax sixth is not a supported syntax. You should use the syntax AFTER column_name where you replace column_name with the column name you want to position the modified column after. Oh thanks. That's actually what I ended up doing after I got frustrated with that error. I was following the book 'Head First SQL' which was suggesting that you could do something like what this user was trying in this stack overflow thread: http://stackoverflow.com/questions/19175240/re-arranging-columns-in- mysql-using-position-keywords-such-as-first-second But the answer in that thread too suggests that this is wrong. So is the Head First SQL book just referring to an outdated syntax that doesn't work anymore? I can't imagine that it never worked if it's in that book. But hey ya never know! ;) Given the title of the book is Head First SQL and not Head First MySQL it probably isn't exclusively using syntax for MySQL. While SQL is a standard the various SQL databases are not completely identical with the syntax they support. This may be due to not completely conforming to the standard, using different versions of the SQL standard, or that there is not standard for that operation. Best regards, Jesper Krogh MySQL Support -- GPG me!! gpg --keyserver pool.sks-keyservers.net --recv-keys F186197B
ALTER TABLE - correct way of adding columns?
Hi All. I would like to change the layout of my production database, I would like to add a column to an existing table. As I think before the ALTER TABLE statement all access to the database should be denied/ended, then the ALTER TABLE should be performed, and then user/applications should be able to use the database once again. My tables is quite small ~4MB data indexes. So is the ALTER TABLE on a running/opened to clients database/table desirable or should it be run when all access to the database/table is forbidden? Best regards, Rafal Radecki.
Re: ALTER TABLE - correct way of adding columns?
- Original Message - From: Rafał Radecki radecki.ra...@gmail.com I would like to change the layout of my production database, I would like to add a column to an existing table. As I think before the ALTER TABLE statement all access to the database should be denied/ended, then the ALTER TABLE should be performed, and then user/applications should be able to use the database once again. Quite right, but ALTER TABLE will acquire a full table lock all by itself - and in 5.6, it's actually getting pretty smart about wether or not it's needed. If it does take a lock, any clients trying to access the table will simply wait for the lock to release, just like happens on other locking queries. The pt-schema-upgrade tool you found is a big help if you need to do long-running changes but want to keep the table online during the operation. My tables is quite small ~4MB data indexes. In this case, however, I shouldn't expect the change to take more than a few seconds in any case; so if your clients won't need to be updated because of the new layout, you could probably just run it and nobody will notice. Given that this is production, though, it is good practice to inform the (human) clients about your changes beforehand :-) -- Unhappiness is discouraged and will be corrected with kitten pictures. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: ALTER TABLE - correct way of adding columns?
Have you used pt-online-schema-change.html from http://www.percona.com/doc/percona-toolkit/2.0/pt-online-schema-change.html ? What do you think about this tool? Best regards, Rafal Radecki. 2013/7/8 Rafał Radecki radecki.ra...@gmail.com Hi All. I would like to change the layout of my production database, I would like to add a column to an existing table. As I think before the ALTER TABLE statement all access to the database should be denied/ended, then the ALTER TABLE should be performed, and then user/applications should be able to use the database once again. My tables is quite small ~4MB data indexes. So is the ALTER TABLE on a running/opened to clients database/table desirable or should it be run when all access to the database/table is forbidden? Best regards, Rafal Radecki.
Re: checking progress of alter table on an InnoDB table (Was: Re: checking progress of alter table on a MyISAM table)
On Thu, Sep 27, 2012 at 3:02 PM, Akshay Suryavanshi akshay.suryavansh...@gmail.com wrote: Hi, 22 indexes are simply too many, assuming they are not composite, which means you already have a very large table. Secondly the most important bottleneck is shown by the inserts/sec, only 405 inserts is very very slow. This could take ages to complete. And the 405 inserts/sec are averages calculated over some small period of time mostly under a minute, not from the uptime, you can see that at the top (initial lines) of the Show engine innodb status \G output. Depending upon the machine footprint, inserts/sec should atleast be more than 1 inserts/sec even on a busy server. Indexes are slowing this down. your calculation of 79 hours should be correct, only if there are no unique indexes, otherwise this will slow down more as the data increases. It finally finished after 55 hours. On Fri, Sep 28, 2012 at 2:22 AM, Larry Martell larry.mart...@gmail.com wrote: On Thu, Sep 27, 2012 at 2:37 PM, Akshay Suryavanshi akshay.suryavansh...@gmail.com wrote: Hi, The alter taking such a long time, could be due to composite indexes on the table. There are 22 indexes on the table, but none are composites. we understand the table is big but not so big to take such a long time. Also we can get a hold of the process looking at the disk space consumed. Usually a tmp table created in data directory would also give a good understanding of the process, remember sizes need not be exact since there might be some defragmentation at file level. Ok, I will look for a temp file. Next you can check inserts/sec in Show engine innodb status \G and calculate the time it should take for the number of rows in the table. The ROW OPERATIONS section has this: 1 queries inside InnoDB, 0 queries in queue 1 read views open inside InnoDB Main thread process no. 7913, id 14020684432, state: sleeping Number of rows inserted 75910241, updated 15602, deleted 70, read 9130481311 405.80 inserts/s, 0.00 updates/s, 0.00 deletes/s, 405.80 reads/s This appears to be statistics over the life of the invocation of the server, correct? But using 405.80 inserts/s give that the alter will take almost 79 hours. Usually, you carry this operation by adding the secondary indexes after the data import or such alters are complete. Regards, Akshay Suryavanshi On Fri, Sep 28, 2012 at 1:56 AM, Rick James rja...@yahoo-inc.com wrote: Isn't ALTER a DDL, not DML? So I don't think you would find anything in undo logs. -Original Message- From: Larry Martell [mailto:larry.mart...@gmail.com] Sent: Thursday, September 27, 2012 1:20 PM To: Rick James Cc: mysql mailing list Subject: checking progress of alter table on an InnoDB table (Was: Re: checking progress of alter table on a MyISAM table) So we changed the table from MyISAM to InnoDB. I read that the 'undo log entries' shown in 'show engine innodb status' would correspond to the number of rows that have been operated on throughout the process of the ALTER. The table we're altering has 115,096,205 rows, and the alter's been running for 28 hours, and the undo log entries is 9309. Also that number seems to go up and down. So clearly, it's not what I think. So anyone know a way to monitor the status of the alter now that it's an InnoDB table? On Wed, Sep 26, 2012 at 10:31 AM, Rick James rja...@yahoo-inc.com wrote: Not really. You could look at the .TYD and .TYI file sizes and compare to the .MYD and .MYI, but that can be deceptive. If the table is really big, and has lots of indexes, the generation of the indexes might go slower and slower -- hence any math on the sizes would be optimistic. -Original Message- From: Larry Martell [mailto:larry.mart...@gmail.com] Sent: Wednesday, September 26, 2012 8:52 AM To: mysql mailing list Subject: checking progress of alter table on a MyISAM table Is there any way to check on the progress of a long running alter table on a MyISAM table? I know it can be done with an InnoDB table, but I haven't found a way to do it on with a MyISAM table. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
checking progress of alter table on an InnoDB table (Was: Re: checking progress of alter table on a MyISAM table)
So we changed the table from MyISAM to InnoDB. I read that the 'undo log entries' shown in 'show engine innodb status' would correspond to the number of rows that have been operated on throughout the process of the ALTER. The table we're altering has 115,096,205 rows, and the alter's been running for 28 hours, and the undo log entries is 9309. Also that number seems to go up and down. So clearly, it's not what I think. So anyone know a way to monitor the status of the alter now that it's an InnoDB table? On Wed, Sep 26, 2012 at 10:31 AM, Rick James rja...@yahoo-inc.com wrote: Not really. You could look at the .TYD and .TYI file sizes and compare to the .MYD and .MYI, but that can be deceptive. If the table is really big, and has lots of indexes, the generation of the indexes might go slower and slower -- hence any math on the sizes would be optimistic. -Original Message- From: Larry Martell [mailto:larry.mart...@gmail.com] Sent: Wednesday, September 26, 2012 8:52 AM To: mysql mailing list Subject: checking progress of alter table on a MyISAM table Is there any way to check on the progress of a long running alter table on a MyISAM table? I know it can be done with an InnoDB table, but I haven't found a way to do it on with a MyISAM table. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: checking progress of alter table on an InnoDB table (Was: Re: checking progress of alter table on a MyISAM table)
Isn't ALTER a DDL, not DML? So I don't think you would find anything in undo logs. -Original Message- From: Larry Martell [mailto:larry.mart...@gmail.com] Sent: Thursday, September 27, 2012 1:20 PM To: Rick James Cc: mysql mailing list Subject: checking progress of alter table on an InnoDB table (Was: Re: checking progress of alter table on a MyISAM table) So we changed the table from MyISAM to InnoDB. I read that the 'undo log entries' shown in 'show engine innodb status' would correspond to the number of rows that have been operated on throughout the process of the ALTER. The table we're altering has 115,096,205 rows, and the alter's been running for 28 hours, and the undo log entries is 9309. Also that number seems to go up and down. So clearly, it's not what I think. So anyone know a way to monitor the status of the alter now that it's an InnoDB table? On Wed, Sep 26, 2012 at 10:31 AM, Rick James rja...@yahoo-inc.com wrote: Not really. You could look at the .TYD and .TYI file sizes and compare to the .MYD and .MYI, but that can be deceptive. If the table is really big, and has lots of indexes, the generation of the indexes might go slower and slower -- hence any math on the sizes would be optimistic. -Original Message- From: Larry Martell [mailto:larry.mart...@gmail.com] Sent: Wednesday, September 26, 2012 8:52 AM To: mysql mailing list Subject: checking progress of alter table on a MyISAM table Is there any way to check on the progress of a long running alter table on a MyISAM table? I know it can be done with an InnoDB table, but I haven't found a way to do it on with a MyISAM table. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: checking progress of alter table on an InnoDB table (Was: Re: checking progress of alter table on a MyISAM table)
I read that here: http://gabrielcain.com/blog/2009/08/05/mysql-alter-table-and-how-to-observe-progress/comment-page-1/ On Thu, Sep 27, 2012 at 2:26 PM, Rick James rja...@yahoo-inc.com wrote: Isn't ALTER a DDL, not DML? So I don't think you would find anything in undo logs. -Original Message- From: Larry Martell [mailto:larry.mart...@gmail.com] Sent: Thursday, September 27, 2012 1:20 PM To: Rick James Cc: mysql mailing list Subject: checking progress of alter table on an InnoDB table (Was: Re: checking progress of alter table on a MyISAM table) So we changed the table from MyISAM to InnoDB. I read that the 'undo log entries' shown in 'show engine innodb status' would correspond to the number of rows that have been operated on throughout the process of the ALTER. The table we're altering has 115,096,205 rows, and the alter's been running for 28 hours, and the undo log entries is 9309. Also that number seems to go up and down. So clearly, it's not what I think. So anyone know a way to monitor the status of the alter now that it's an InnoDB table? On Wed, Sep 26, 2012 at 10:31 AM, Rick James rja...@yahoo-inc.com wrote: Not really. You could look at the .TYD and .TYI file sizes and compare to the .MYD and .MYI, but that can be deceptive. If the table is really big, and has lots of indexes, the generation of the indexes might go slower and slower -- hence any math on the sizes would be optimistic. -Original Message- From: Larry Martell [mailto:larry.mart...@gmail.com] Sent: Wednesday, September 26, 2012 8:52 AM To: mysql mailing list Subject: checking progress of alter table on a MyISAM table Is there any way to check on the progress of a long running alter table on a MyISAM table? I know it can be done with an InnoDB table, but I haven't found a way to do it on with a MyISAM table. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: checking progress of alter table on an InnoDB table (Was: Re: checking progress of alter table on a MyISAM table)
Hi, The alter taking such a long time, could be due to composite indexes on the table. we understand the table is big but not so big to take such a long time. Also we can get a hold of the process looking at the disk space consumed. Usually a tmp table created in data directory would also give a good understanding of the process, remember sizes need not be exact since there might be some defragmentation at file level. Next you can check inserts/sec in Show engine innodb status \G and calculate the time it should take for the number of rows in the table. Usually, you carry this operation by adding the secondary indexes after the data import or such alters are complete. Regards, Akshay Suryavanshi On Fri, Sep 28, 2012 at 1:56 AM, Rick James rja...@yahoo-inc.com wrote: Isn't ALTER a DDL, not DML? So I don't think you would find anything in undo logs. -Original Message- From: Larry Martell [mailto:larry.mart...@gmail.com] Sent: Thursday, September 27, 2012 1:20 PM To: Rick James Cc: mysql mailing list Subject: checking progress of alter table on an InnoDB table (Was: Re: checking progress of alter table on a MyISAM table) So we changed the table from MyISAM to InnoDB. I read that the 'undo log entries' shown in 'show engine innodb status' would correspond to the number of rows that have been operated on throughout the process of the ALTER. The table we're altering has 115,096,205 rows, and the alter's been running for 28 hours, and the undo log entries is 9309. Also that number seems to go up and down. So clearly, it's not what I think. So anyone know a way to monitor the status of the alter now that it's an InnoDB table? On Wed, Sep 26, 2012 at 10:31 AM, Rick James rja...@yahoo-inc.com wrote: Not really. You could look at the .TYD and .TYI file sizes and compare to the .MYD and .MYI, but that can be deceptive. If the table is really big, and has lots of indexes, the generation of the indexes might go slower and slower -- hence any math on the sizes would be optimistic. -Original Message- From: Larry Martell [mailto:larry.mart...@gmail.com] Sent: Wednesday, September 26, 2012 8:52 AM To: mysql mailing list Subject: checking progress of alter table on a MyISAM table Is there any way to check on the progress of a long running alter table on a MyISAM table? I know it can be done with an InnoDB table, but I haven't found a way to do it on with a MyISAM table. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: checking progress of alter table on an InnoDB table (Was: Re: checking progress of alter table on a MyISAM table)
On Thu, Sep 27, 2012 at 2:37 PM, Akshay Suryavanshi akshay.suryavansh...@gmail.com wrote: Hi, The alter taking such a long time, could be due to composite indexes on the table. There are 22 indexes on the table, but none are composites. we understand the table is big but not so big to take such a long time. Also we can get a hold of the process looking at the disk space consumed. Usually a tmp table created in data directory would also give a good understanding of the process, remember sizes need not be exact since there might be some defragmentation at file level. Ok, I will look for a temp file. Next you can check inserts/sec in Show engine innodb status \G and calculate the time it should take for the number of rows in the table. The ROW OPERATIONS section has this: 1 queries inside InnoDB, 0 queries in queue 1 read views open inside InnoDB Main thread process no. 7913, id 14020684432, state: sleeping Number of rows inserted 75910241, updated 15602, deleted 70, read 9130481311 405.80 inserts/s, 0.00 updates/s, 0.00 deletes/s, 405.80 reads/s This appears to be statistics over the life of the invocation of the server, correct? But using 405.80 inserts/s give that the alter will take almost 79 hours. Usually, you carry this operation by adding the secondary indexes after the data import or such alters are complete. Regards, Akshay Suryavanshi On Fri, Sep 28, 2012 at 1:56 AM, Rick James rja...@yahoo-inc.com wrote: Isn't ALTER a DDL, not DML? So I don't think you would find anything in undo logs. -Original Message- From: Larry Martell [mailto:larry.mart...@gmail.com] Sent: Thursday, September 27, 2012 1:20 PM To: Rick James Cc: mysql mailing list Subject: checking progress of alter table on an InnoDB table (Was: Re: checking progress of alter table on a MyISAM table) So we changed the table from MyISAM to InnoDB. I read that the 'undo log entries' shown in 'show engine innodb status' would correspond to the number of rows that have been operated on throughout the process of the ALTER. The table we're altering has 115,096,205 rows, and the alter's been running for 28 hours, and the undo log entries is 9309. Also that number seems to go up and down. So clearly, it's not what I think. So anyone know a way to monitor the status of the alter now that it's an InnoDB table? On Wed, Sep 26, 2012 at 10:31 AM, Rick James rja...@yahoo-inc.com wrote: Not really. You could look at the .TYD and .TYI file sizes and compare to the .MYD and .MYI, but that can be deceptive. If the table is really big, and has lots of indexes, the generation of the indexes might go slower and slower -- hence any math on the sizes would be optimistic. -Original Message- From: Larry Martell [mailto:larry.mart...@gmail.com] Sent: Wednesday, September 26, 2012 8:52 AM To: mysql mailing list Subject: checking progress of alter table on a MyISAM table Is there any way to check on the progress of a long running alter table on a MyISAM table? I know it can be done with an InnoDB table, but I haven't found a way to do it on with a MyISAM table. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: checking progress of alter table on an InnoDB table (Was: Re: checking progress of alter table on a MyISAM table)
Hi, 22 indexes are simply too many, assuming they are not composite, which means you already have a very large table. Secondly the most important bottleneck is shown by the inserts/sec, only 405 inserts is very very slow. This could take ages to complete. And the 405 inserts/sec are averages calculated over some small period of time mostly under a minute, not from the uptime, you can see that at the top (initial lines) of the Show engine innodb status \G output. Depending upon the machine footprint, inserts/sec should atleast be more than 1 inserts/sec even on a busy server. Indexes are slowing this down. your calculation of 79 hours should be correct, only if there are no unique indexes, otherwise this will slow down more as the data increases. Regards, Akshay Surayavanshi On Fri, Sep 28, 2012 at 2:22 AM, Larry Martell larry.mart...@gmail.comwrote: On Thu, Sep 27, 2012 at 2:37 PM, Akshay Suryavanshi akshay.suryavansh...@gmail.com wrote: Hi, The alter taking such a long time, could be due to composite indexes on the table. There are 22 indexes on the table, but none are composites. we understand the table is big but not so big to take such a long time. Also we can get a hold of the process looking at the disk space consumed. Usually a tmp table created in data directory would also give a good understanding of the process, remember sizes need not be exact since there might be some defragmentation at file level. Ok, I will look for a temp file. Next you can check inserts/sec in Show engine innodb status \G and calculate the time it should take for the number of rows in the table. The ROW OPERATIONS section has this: 1 queries inside InnoDB, 0 queries in queue 1 read views open inside InnoDB Main thread process no. 7913, id 14020684432, state: sleeping Number of rows inserted 75910241, updated 15602, deleted 70, read 9130481311 405.80 inserts/s, 0.00 updates/s, 0.00 deletes/s, 405.80 reads/s This appears to be statistics over the life of the invocation of the server, correct? But using 405.80 inserts/s give that the alter will take almost 79 hours. Usually, you carry this operation by adding the secondary indexes after the data import or such alters are complete. Regards, Akshay Suryavanshi On Fri, Sep 28, 2012 at 1:56 AM, Rick James rja...@yahoo-inc.com wrote: Isn't ALTER a DDL, not DML? So I don't think you would find anything in undo logs. -Original Message- From: Larry Martell [mailto:larry.mart...@gmail.com] Sent: Thursday, September 27, 2012 1:20 PM To: Rick James Cc: mysql mailing list Subject: checking progress of alter table on an InnoDB table (Was: Re: checking progress of alter table on a MyISAM table) So we changed the table from MyISAM to InnoDB. I read that the 'undo log entries' shown in 'show engine innodb status' would correspond to the number of rows that have been operated on throughout the process of the ALTER. The table we're altering has 115,096,205 rows, and the alter's been running for 28 hours, and the undo log entries is 9309. Also that number seems to go up and down. So clearly, it's not what I think. So anyone know a way to monitor the status of the alter now that it's an InnoDB table? On Wed, Sep 26, 2012 at 10:31 AM, Rick James rja...@yahoo-inc.com wrote: Not really. You could look at the .TYD and .TYI file sizes and compare to the .MYD and .MYI, but that can be deceptive. If the table is really big, and has lots of indexes, the generation of the indexes might go slower and slower -- hence any math on the sizes would be optimistic. -Original Message- From: Larry Martell [mailto:larry.mart...@gmail.com] Sent: Wednesday, September 26, 2012 8:52 AM To: mysql mailing list Subject: checking progress of alter table on a MyISAM table Is there any way to check on the progress of a long running alter table on a MyISAM table? I know it can be done with an InnoDB table, but I haven't found a way to do it on with a MyISAM table. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: checking progress of alter table on an InnoDB table (Was: Re: checking progress of alter table on a MyISAM table)
On Thu, Sep 27, 2012 at 3:02 PM, Akshay Suryavanshi akshay.suryavansh...@gmail.com wrote: Hi, 22 indexes are simply too many, assuming they are not composite, which means you already have a very large table. Yeah, I agree the table has too many columns and too many indexes. I didn't design it, and I have no control over it. There's a huge app that's built around it, and changing the table would require too many code changes. Secondly the most important bottleneck is shown by the inserts/sec, only 405 inserts is very very slow. This could take ages to complete. And the 405 inserts/sec are averages calculated over some small period of time mostly under a minute, not from the uptime, you can see that at the top (initial lines) of the Show engine innodb status \G output. Depending upon the machine footprint, inserts/sec should atleast be more than 1 inserts/sec even on a busy server. I don't know anything about the machine. I don't have direct access to it (it's at my client's client's site) so I'm doing everything by proxy. Indexes are slowing this down. your calculation of 79 hours should be correct, only if there are no unique indexes, otherwise this will slow down more as the data increases. It is what it is - there's no free lunch - dropping the indexes, doing the alter, and re-adding the indexes would probably take just as long. It would be very nice to speed up the alter, but what I was really looking for here was a way to monitor its progress. On Fri, Sep 28, 2012 at 2:22 AM, Larry Martell larry.mart...@gmail.com wrote: On Thu, Sep 27, 2012 at 2:37 PM, Akshay Suryavanshi akshay.suryavansh...@gmail.com wrote: Hi, The alter taking such a long time, could be due to composite indexes on the table. There are 22 indexes on the table, but none are composites. we understand the table is big but not so big to take such a long time. Also we can get a hold of the process looking at the disk space consumed. Usually a tmp table created in data directory would also give a good understanding of the process, remember sizes need not be exact since there might be some defragmentation at file level. Ok, I will look for a temp file. Next you can check inserts/sec in Show engine innodb status \G and calculate the time it should take for the number of rows in the table. The ROW OPERATIONS section has this: 1 queries inside InnoDB, 0 queries in queue 1 read views open inside InnoDB Main thread process no. 7913, id 14020684432, state: sleeping Number of rows inserted 75910241, updated 15602, deleted 70, read 9130481311 405.80 inserts/s, 0.00 updates/s, 0.00 deletes/s, 405.80 reads/s This appears to be statistics over the life of the invocation of the server, correct? But using 405.80 inserts/s give that the alter will take almost 79 hours. Usually, you carry this operation by adding the secondary indexes after the data import or such alters are complete. Regards, Akshay Suryavanshi On Fri, Sep 28, 2012 at 1:56 AM, Rick James rja...@yahoo-inc.com wrote: Isn't ALTER a DDL, not DML? So I don't think you would find anything in undo logs. -Original Message- From: Larry Martell [mailto:larry.mart...@gmail.com] Sent: Thursday, September 27, 2012 1:20 PM To: Rick James Cc: mysql mailing list Subject: checking progress of alter table on an InnoDB table (Was: Re: checking progress of alter table on a MyISAM table) So we changed the table from MyISAM to InnoDB. I read that the 'undo log entries' shown in 'show engine innodb status' would correspond to the number of rows that have been operated on throughout the process of the ALTER. The table we're altering has 115,096,205 rows, and the alter's been running for 28 hours, and the undo log entries is 9309. Also that number seems to go up and down. So clearly, it's not what I think. So anyone know a way to monitor the status of the alter now that it's an InnoDB table? On Wed, Sep 26, 2012 at 10:31 AM, Rick James rja...@yahoo-inc.com wrote: Not really. You could look at the .TYD and .TYI file sizes and compare to the .MYD and .MYI, but that can be deceptive. If the table is really big, and has lots of indexes, the generation of the indexes might go slower and slower -- hence any math on the sizes would be optimistic. -Original Message- From: Larry Martell [mailto:larry.mart...@gmail.com] Sent: Wednesday, September 26, 2012 8:52 AM To: mysql mailing list Subject: checking progress of alter table on a MyISAM table Is there any way to check on the progress of a long running alter table on a MyISAM table? I know it can be done with an InnoDB table, but I haven't found a way to do it on with a MyISAM table. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http
Re: checking progress of alter table on a MyISAM table
Am 27.09.2012 23:15, schrieb Larry Martell: Indexes are slowing this down. your calculation of 79 hours should be correct, only if there are no unique indexes, otherwise this will slow down more as the data increases. It is what it is - there's no free lunch - dropping the indexes, doing the alter, and re-adding the indexes would probably take just as long you refuse to understand the implication of indexes it would NOT take so long - this was proven thousands of times by many people all over the world having a unique index on a table with many rows means a lot of overhead for EVERY single insert/update without the key you avoid the complete overhead and do it once at the end - why do you not simplay make any test table and compare insert 50.000 rows with and without keys on the test-table this takes a few minues and after that you understand how large the overhead is and that it is grwoing with the table signature.asc Description: OpenPGP digital signature
Re: checking progress of alter table on a MyISAM table
I try to figure out something observing the stats with SHOW STATUS. There are some reads, writes, etc that tell something about what is going on. Looking just at the file sizes is likely going to not tell much about the progress. If there is a better way to monitor this progress, I would like to know this... On Sep 26, 2012, at 3:46 PM, h...@tbbs.net wrote: 2012/09/26 09:31 -0700, Rick James You could look at the .TYD and .TYI file sizes and compare to the .MYD and .MYI, but that can be deceptive. If the table is really big, and has lots of indexes, the generation of the indexes might go slower and slower -- hence any math on the sizes would be optimistic. And under Windows more misleading--often, if a file is small, the directory listing will show 0 until the very end. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
checking progress of alter table on a MyISAM table
Is there any way to check on the progress of a long running alter table on a MyISAM table? I know it can be done with an InnoDB table, but I haven't found a way to do it on with a MyISAM table. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: checking progress of alter table on a MyISAM table
Not really. You could look at the .TYD and .TYI file sizes and compare to the .MYD and .MYI, but that can be deceptive. If the table is really big, and has lots of indexes, the generation of the indexes might go slower and slower -- hence any math on the sizes would be optimistic. -Original Message- From: Larry Martell [mailto:larry.mart...@gmail.com] Sent: Wednesday, September 26, 2012 8:52 AM To: mysql mailing list Subject: checking progress of alter table on a MyISAM table Is there any way to check on the progress of a long running alter table on a MyISAM table? I know it can be done with an InnoDB table, but I haven't found a way to do it on with a MyISAM table. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: checking progress of alter table on a MyISAM table
2012/09/26 09:31 -0700, Rick James You could look at the .TYD and .TYI file sizes and compare to the .MYD and .MYI, but that can be deceptive. If the table is really big, and has lots of indexes, the generation of the indexes might go slower and slower -- hence any math on the sizes would be optimistic. And under Windows more misleading--often, if a file is small, the directory listing will show 0 until the very end. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Tables are lost for DDL and different behaviors for alter table failed situation between innobase and innodb_plugin
*summary:*
Recently we hit lost tables during DDL for online products, and after some
observersion, we found some interesting hehaviors if fil_rename_tablespace
failed as retry 25.
That might be another issue that not discussed here.
THERE MUST BE SINGLE TABLESPACE
Here is the steps to show the behavior:
*1. compile 5.1.48 source code.*
CFLAGS=-O0 -g CXX=gcc CXXFLAGS=-O0 -g -felide-constructors \
-fno-exceptions -fno-rtti
export CFLAGS CXXFLAGS
./configure --with-plugins=innodb_plugin
make -j32
*2. debug with plugin innodb*
2.1 # attach a running pid
(gdb) b fil_rename_tablespace
Breakpoint 1 at 0x2abfed30: file fil/fil0fil.c, line 2435.
(gdb) c
Continuing.
[Switching to Thread 0x4944a940 (LWP 7069)]
Breakpoint 1, fil_rename_tablespace (old_name=0xeacdc20 sbtest/sb1, id=51,
new_name=0xea96fa0 sbtest/#sql2-1b8b-1) at fil/fil0fil.c:2435
2435{
(gdb) c
Continuing.
Breakpoint 1, fil_rename_tablespace (old_name=0xeabb3f0
sbtest/#sql-1b8b_1, id=52, new_name=0xea96fa0 sbtest/sb1) at
fil/fil0fil.c:2435
2435{
(gdb) return 0
Make fil_rename_tablespace return now? (y or n) y
#0 0x2abea8c4 in dict_table_rename_in_cache (table=0xeabb288,
new_name=0xea96fa0 sbtest/sb1,
rename_also_foreigns=1) at dict/dict0dict.c:951
951 } else if (!fil_rename_tablespace(old_name,
table-space,
(gdb)
(gdb) l
946 fputs( (, stderr);
947 ut_print_filename(stderr,
948
table-dir_path_of_temp_table);
949 fputs( )\n, stderr);
950 return(FALSE);
951 } else if (!fil_rename_tablespace(old_name,
table-space,
952 new_name)) {
953 return(FALSE);
954 }
955 }
(gdb) finish
Run till exit from #0 0x2abea8c4 in dict_table_rename_in_cache
(table=0xeabb288, new_name=0xea96fa0 sbtest/sb1, rename_also_foreigns=1)
at dict/dict0dict.c:951
0x2ac6527e in row_rename_table_for_mysql (old_name=0xea97000
sbtest/#sql-1b8b_1, new_name=0xea96fa0 sbtest/sb1, trx=0xeacf728,
commit=1)
at row/row0mysql.c:3905
3905if (!dict_table_rename_in_cache(table, new_name,
Value returned is $1 = 0
(gdb) l
3900trx-error_state = DB_SUCCESS;
3901} else {
3902/* The following call will also rename the .ibd data
file if
3903the table is stored in a single-table tablespace */
3904
3905if (!dict_table_rename_in_cache(table, new_name,
3906!new_is_tmp)) {
3907trx-error_state = DB_SUCCESS;
3908trx_general_rollback_for_mysql(trx, NULL);
3909trx-error_state = DB_SUCCESS;
(gdb) l
3910goto funct_exit;
3911}
3912
3913/* We only want to switch off some of the type
checking in
3914an ALTER, not in a RENAME. */
3915
3916err = dict_load_foreigns(
3917new_name, !old_is_tmp ||
trx-check_foreigns);
3918
3919if (err != DB_SUCCESS) {
(gdb) finish
Run till exit from #0 0x2ac6527e in row_rename_table_for_mysql
(old_name=0xea97000 sbtest/#sql-1b8b_1, new_name=0xea96fa0 sbtest/sb1,
trx=0xeacf728, commit=1) at row/row0mysql.c:3905
0x2ac14e0b in ha_innodb::rename_table () from
/u01/mysql-5.1.48/storage/innodb_plugin/.libs/ha_innodb_plugin.so
Value returned is $2 = 10
2.2 here is the key routines:
storage/innodb_plugin/row/row0mysql.c: row_rename_table_for_mysql
3905 if (!dict_table_rename_in_cache(table, new_name,
3906 !new_is_tmp)) {
3907 trx-error_state = DB_SUCCESS;
3908 trx_general_rollback_for_mysql(trx, NULL);
3909 trx-error_state = DB_SUCCESS;
3910 goto funct_exit;
3911 }
2.3 client's output:
root@sbtest 05:00:24alter table sb1 add column d2 int;
Query OK, 0 rows affected (9 min 47.97 sec)
Records: 0 Duplicates: 0 Warnings: 0
root@sbtest 05:11:26desc sb1;
ERROR 1146 (42S02): Table 'sbtest.sb1' doesn't exist
*3. different code between innodb and plugin*
3.1 innodb add err = DB_ERROR; before goto funct_exit; when
dict_table_rename_in_cache failed.
3.2 test innodb work and result could be refered to modified innodb plugin
just like innobase behavior, add err = DB_ERROR; before exit
3905 if (!dict_table_rename_in_cache(table, new_name,
3906 !new_is_tmp)) {
3907 trx-error_state = DB_SUCCESS;
3908 trx_general_rollback_for_mysql(trx, NULL);
3909
mysql tables are lost for DDL of alter table .. add column ...
mysql tables are lost for DDL of alter table .. add column ... *1. mysqld's error.log* 110803 3:39:16 InnoDB: Warning: problems renaming 'feel_22/#sql-2635_23d3a8' to 'feel_22/feed_send_1451', 25000 iterations （first 25000，fil0fil.c:: fil_rename_tablespace） InnoDB: Warning: tablespace './feel_22/#sql-2635_23d3a8.ibd' has i/o ops stopped for a long time 24999 （fil0fil.c:: fil_mutex_enter_and_prepare_for_io） 110803 3:39:16 InnoDB: Warning: problems renaming 'feel_22/#sql-2635_23d3a8' to 'feel_22/feed_send_1451', 25001 iterations （over 25000, return FALSE） 110803 3:39:16 [ERROR] Cannot find or open table feel_22/feed_send_1451 from the internal data dictionary of InnoDB though the .frm file for the table exists. Maybe you have deleted and recreated InnoDB data files but have forgotten to delete the corresponding .frm files of InnoDB tables, or you have moved .frm files to another database? or, the table contains indexes that this version of the engine doesn't support. there is another interesting error info: Error 1005: Can't create table 'feel_01.#sql-57f0_25a510' (errno: -1) *2. rename tablespace can be successful only if:* if (node-n_pending 0 || node-n_pending_flushes 0) if (node-modification_counter node-flush_counter) *3. we failed to repeat this bug and failed to locate the real reason. * Here is the information we got: * it's single table space with innodb plugin 1.0.9 and mysql-5.1.48. we failed to repeat this issue. * DDL is done at mid-night, and workload is very very slow(both master and slave suffered with issue but with very very low probability). * for 100G ibd file, fsync is so quickly that retry number is 0 for rename condition waiting, so we don't doult the n_pending_flushes. * all the sql are blocked when 2 retry first hit, but unfornantely no other stack/core info saved. * we suspect the io handlers are out-of-order,io_handler_threads and srv_master_thread maybe all os_event_wait_low. We can not prove the deadlock situation, but if the deadlock is exist in such situation, the patch maybe help us to suffering occasional table losting for DDL --- /tmp/mysql-5.1.48/storage/innodb_plugin/fil/fil0fil.c 2010-06-03 23:50:08.0 +0800 +++ storage/innodb_plugin/fil/fil0fil.c 2011-08-11 00:23:31.0 +++ +0800 @@ -938,8 +938,24 @@ mutex_exit(fil_system-mutex); + +#ifndef UNIV_HOTBACKUP +/* Wake the i/o-handler threads to make sure pending i/o's are + performed */ +os_aio_simulated_wake_handler_threads(); + os_thread_sleep(2); + /* Flush tablespaces so that we can close modified files in the LRU + list */ + +fil_flush_file_spaces(FIL_TABLESPACE); +#else + + os_thread_sleep(2); +#endif + + count2++; goto retry; @@ -2457,6 +2473,11 @@ fputs( to , stderr); ut_print_filename(stderr, new_name); fprintf(stderr, , %lu iterations\n, (ulong) count); + if (node) + fprintf(stderr, node info: n_pending=%lu, n_pending_flushes=%lu +modification_counter=%lu, flush_counter=%lu\n, +node-n_pending, node-n_pending_flushes, node-modification_counter, +node-flush_counter); } mutex_enter(fil_system-mutex); *4. we need yours help to solve the table lost issue*
Re: Slow ALTER TABLE on 70M row InnoDB table
Most alter table operations in 5.0 will rebuild the entire table. The best thing to increase for alter table speed in innodb is the buffer pool. For more details on how innodb handles alter table see http://ebergen.net/wordpress/2007/05/07/how-alter-table-locks-tables-and-handles-transactions/ On Wednesday, August 18, 2010, Xn Nooby xno...@gmail.com wrote: From what I have read, ALTER TABLE to add an index causes the entire table to be duplicated, so wouldn't my ALTER TABLE command be duplicating the work done by the SELECT command? On Wed, Aug 18, 2010 at 4:50 PM, mos mo...@fastmail.fm wrote: At 02:52 PM 8/18/2010, Xn Nooby wrote: Below is a generic version of the code I am trying. It does copy the rows very quickly, but I will have to test to see how quickly the indices are built. Is the below code what you were suggesting? I had a little trouble dropping and later adding the primary index, but I think I got it figured out. Below I basically do this: make the_table_clone from the the_table drop the indices on the_table_clone copy the row from the_table to the_table_clone add the indices back to the_table_clone If this runs fast enough, I will then drop the_table, and rename the_table_clone to the_table USE the_database; DROP TABLE IF EXISTS the_table_clone; CREATE TABLE the_table_clone LIKE the_table; Or you can try something like: create table the_table_clone engine=innodb select * from the_table limit=0; This will create the same table structure but not the indexes so you don't have to drop the indexes below. # drop minor indices on clone ALTER TABLE the_table_clone DROP INDEX IX_the_table_on_col2_col3; ALTER TABLE the_table_clone DROP INDEX IX_the_table_on_col4_col6; ALTER TABLE the_table_clone DROP INDEX IX_the_table_on_col5_col2; # drop primary index on clone ALTER TABLE the_table_clone CHANGE id id INT UNSIGNED; You still need the statement above to change the autoinc to integer if you use my Create Table... statement above. ALTER TABLE the_table_clone DROP PRIMARY KEY; # add 2 new columns to clone ALTER TABLE the_table_clone ADD flag_field int(11) DEFAULT '0', ADD price_amount float DEFAULT '0'; # copy rows INSERT the_table_clone SELECT *, NULL, NULL FROM the_table LIMIT 0,1000; #INSERT the_table_clone SELECT *, NULL, NULL FROM the_table; Why do you have two insert statements? If you are inserting a group of records at a time then you need a limit statement on each, and increment the offset by the number of rows that have been added. I would explicitly specify the column list for both the Insert and the Select to make sure they match up. There is no point going through all this if it inserts the data into the wrong columns! Check the data before creating the indexes to make sure the same number of rows have been copied over and the data is in the correct columns. # Add back indices in one command (for max speed) ALTER TABLE the_table_clone \ ADD INDEX IX_the_table_on_col2_col3 (col2,col3),\ ADD INDEX IX_the_table_on_col4_col6 (col4,col6),\ ADD INDEX IX_the_table_on_col5_col2 (col5,col2),\ MODIFY id INT SIGNED AUTO_INCREMENT,\ ADD PRIMARY KEY(col1); Correct. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=eric.ber...@gmail.com -- Eric Bergen eric.ber...@gmail.com http://www.ebergen.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Slow ALTER TABLE on 70M row InnoDB table
I have been trying to speed up an ALTER TABLE command that adds a column to a large InnoDB table of about 80M rows. I have found and tried many different methods, but they are all slow.I have tried both optimizing the ALTER TABLE command, and dumping and loading the table (in both SQL and CSV formats). The table size is about 10GB, and the combined index size is about 6GB. I am trying to understand why it is slow. I have read that dumping and loading in the CSV format should be the absolute fastest, and it does only take 20 minutes to dump the 70M rows. However, it takes the LOAD FILE command 13 hours to import the CSV file. My understanding of LOAD FILE was that it was already optimized to load the data, then build the indices afterwords. I don't understand why it takes so long. I have read that breaking a SQL dump in to chunks is also supposed to be fast, but later chunks insert more slowly than earlier chunks. This is with keys disabled, and other options disabled. Ideally I could stick with the ALTER TABLE command, and I have played around with a lot of the buffer settings. My understanding is, any enabled key indices need to fit in to RAM, and I have played around with a lot of those settings. Mainly I have increased the buffer size and log buffer size. When importing records, I see the free buffers slowly run-out, and the import speed drops off when the buffers are used up. The first few million rows import at up to 30k rows per second, but eventually it slows to a crawl. I have read a lot about this on the mysqlperformance blog. There is a lot of information on the web about this topic, but I am not always sure which parts are for ISAM and which apply to InnoDB. I have not experimented with ISAM, since my tables are InnoDB. This process is slow on a larger box, which belongs to someone else, and on my own desktop PC. Should I stick with trying to make ALTER TABLE work, or should I be trying to get LOAD FILE to work? Any suggestions on adding a column to a large table? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Slow ALTER TABLE on 70M row InnoDB table
At 10:34 AM 8/18/2010, Xn Nooby wrote: minutes to dump the 70M rows. However, it takes the LOAD FILE command 13 hours to import the CSV file. My understanding of LOAD FILE was that it was already optimized to load the data, then build the indices afterwords. I don't understand why it takes so long. A common misconception about Load Data Infile command is that it will rebuild the indexes later only if you are loading data into an empty table (which you probably are) and only for the non-unique indexes. The Unique and Primary indexes are still being built during the loading of the data and I suspect this is why it is slowing down over time. Before loading the data, I would recommend removing ALL of the indexes. Then after the data has been loaded, issue a single Alter statement to rebuild all of the indexes in this one command. See if that makes a difference. The other alternative is to create another table with the new table structure but without the indexes. Then do a insert into newtable select * from oldtable; and then create the indexes on the new table with a single Alter statement. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Slow ALTER TABLE on 70M row InnoDB table
What are you using as your primary key on this table? Is an auto_increment field or something non-sequential? Do you have your secondary indexes in place while you load the table or are you explicitly disabling them and re-enabling them afterward? -Travis -Original Message- From: Xn Nooby [mailto:xno...@gmail.com] Sent: Wednesday, August 18, 2010 9:34 AM To: mysql@lists.mysql.com Subject: Slow ALTER TABLE on 70M row InnoDB table I have been trying to speed up an ALTER TABLE command that adds a column to a large InnoDB table of about 80M rows. I have found and tried many different methods, but they are all slow.I have tried both optimizing the ALTER TABLE command, and dumping and loading the table (in both SQL and CSV formats). The table size is about 10GB, and the combined index size is about 6GB. I am trying to understand why it is slow. I have read that dumping and loading in the CSV format should be the absolute fastest, and it does only take 20 minutes to dump the 70M rows. However, it takes the LOAD FILE command 13 hours to import the CSV file. My understanding of LOAD FILE was that it was already optimized to load the data, then build the indices afterwords. I don't understand why it takes so long. I have read that breaking a SQL dump in to chunks is also supposed to be fast, but later chunks insert more slowly than earlier chunks. This is with keys disabled, and other options disabled. Ideally I could stick with the ALTER TABLE command, and I have played around with a lot of the buffer settings. My understanding is, any enabled key indices need to fit in to RAM, and I have played around with a lot of those settings. Mainly I have increased the buffer size and log buffer size. When importing records, I see the free buffers slowly run-out, and the import speed drops off when the buffers are used up. The first few million rows import at up to 30k rows per second, but eventually it slows to a crawl. I have read a lot about this on the mysqlperformance blog. There is a lot of information on the web about this topic, but I am not always sure which parts are for ISAM and which apply to InnoDB. I have not experimented with ISAM, since my tables are InnoDB. This process is slow on a larger box, which belongs to someone else, and on my own desktop PC. Should I stick with trying to make ALTER TABLE work, or should I be trying to get LOAD FILE to work? Any suggestions on adding a column to a large table? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=travis_...@hotmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Slow ALTER TABLE on 70M row InnoDB table
Below is a generic version of the code I am trying. It does copy the rows very quickly, but I will have to test to see how quickly the indices are built. Is the below code what you were suggesting? I had a little trouble dropping and later adding the primary index, but I think I got it figured out. Below I basically do this: make the_table_clone from the the_table drop the indices on the_table_clone copy the row from the_table to the_table_clone add the indices back to the_table_clone If this runs fast enough, I will then drop the_table, and rename the_table_clone to the_table USE the_database; DROP TABLE IF EXISTS the_table_clone; CREATE TABLE the_table_clone LIKE the_table; # drop minor indices on clone ALTER TABLE the_table_clone DROP INDEX IX_the_table_on_col2_col3; ALTER TABLE the_table_clone DROP INDEX IX_the_table_on_col4_col6; ALTER TABLE the_table_clone DROP INDEX IX_the_table_on_col5_col2; # drop primary index on clone ALTER TABLE the_table_clone CHANGE id id INT UNSIGNED; ALTER TABLE the_table_clone DROP PRIMARY KEY; # add 2 new columns to clone ALTER TABLE the_table_clone ADD flag_field int(11) DEFAULT '0', ADD price_amount float DEFAULT '0'; # copy rows INSERT the_table_clone SELECT *, NULL, NULL FROM the_table LIMIT 0,1000; #INSERT the_table_clone SELECT *, NULL, NULL FROM the_table; # Add back indices in one command (for max speed) ALTER TABLE the_table_clone \ ADD INDEX IX_the_table_on_col2_col3 (col2,col3),\ ADD INDEX IX_the_table_on_col4_col6 (col4,col6),\ ADD INDEX IX_the_table_on_col5_col2 (col5,col2),\ MODIFY id INT SIGNED AUTO_INCREMENT,\ ADD PRIMARY KEY(col1); -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Slow ALTER TABLE on 70M row InnoDB table
At 02:52 PM 8/18/2010, Xn Nooby wrote: Below is a generic version of the code I am trying. It does copy the rows very quickly, but I will have to test to see how quickly the indices are built. Is the below code what you were suggesting? I had a little trouble dropping and later adding the primary index, but I think I got it figured out. Below I basically do this: make the_table_clone from the the_table drop the indices on the_table_clone copy the row from the_table to the_table_clone add the indices back to the_table_clone If this runs fast enough, I will then drop the_table, and rename the_table_clone to the_table USE the_database; DROP TABLE IF EXISTS the_table_clone; CREATE TABLE the_table_clone LIKE the_table; Or you can try something like: create table the_table_clone engine=innodb select * from the_table limit=0; This will create the same table structure but not the indexes so you don't have to drop the indexes below. # drop minor indices on clone ALTER TABLE the_table_clone DROP INDEX IX_the_table_on_col2_col3; ALTER TABLE the_table_clone DROP INDEX IX_the_table_on_col4_col6; ALTER TABLE the_table_clone DROP INDEX IX_the_table_on_col5_col2; # drop primary index on clone ALTER TABLE the_table_clone CHANGE id id INT UNSIGNED; You still need the statement above to change the autoinc to integer if you use my Create Table... statement above. ALTER TABLE the_table_clone DROP PRIMARY KEY; # add 2 new columns to clone ALTER TABLE the_table_clone ADD flag_field int(11) DEFAULT '0', ADD price_amount float DEFAULT '0'; # copy rows INSERT the_table_clone SELECT *, NULL, NULL FROM the_table LIMIT 0,1000; #INSERT the_table_clone SELECT *, NULL, NULL FROM the_table; Why do you have two insert statements? If you are inserting a group of records at a time then you need a limit statement on each, and increment the offset by the number of rows that have been added. I would explicitly specify the column list for both the Insert and the Select to make sure they match up. There is no point going through all this if it inserts the data into the wrong columns! Check the data before creating the indexes to make sure the same number of rows have been copied over and the data is in the correct columns. # Add back indices in one command (for max speed) ALTER TABLE the_table_clone \ ADD INDEX IX_the_table_on_col2_col3 (col2,col3),\ ADD INDEX IX_the_table_on_col4_col6 (col4,col6),\ ADD INDEX IX_the_table_on_col5_col2 (col5,col2),\ MODIFY id INT SIGNED AUTO_INCREMENT,\ ADD PRIMARY KEY(col1); Correct. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Slow ALTER TABLE on 70M row InnoDB table
Hi Mike, my comments are below: On Wed, Aug 18, 2010 at 4:50 PM, mos mo...@fastmail.fm wrote: At 02:52 PM 8/18/2010, Xn Nooby wrote: Below is a generic version of the code I am trying. It does copy the rows very quickly, but I will have to test to see how quickly the indices are built. Is the below code what you were suggesting? I had a little trouble dropping and later adding the primary index, but I think I got it figured out. Below I basically do this: make the_table_clone from the the_table drop the indices on the_table_clone copy the row from the_table to the_table_clone add the indices back to the_table_clone If this runs fast enough, I will then drop the_table, and rename the_table_clone to the_table USE the_database; DROP TABLE IF EXISTS the_table_clone; CREATE TABLE the_table_clone LIKE the_table; Or you can try something like: create table the_table_clone engine=innodb select * from the_table limit=0; This will create the same table structure but not the indexes so you don't have to drop the indexes below. That is good to know. I did not mind dropping the indices in this case, because the table was still empty. # drop minor indices on clone ALTER TABLE the_table_clone DROP INDEX IX_the_table_on_col2_col3; ALTER TABLE the_table_clone DROP INDEX IX_the_table_on_col4_col6; ALTER TABLE the_table_clone DROP INDEX IX_the_table_on_col5_col2; # drop primary index on clone ALTER TABLE the_table_clone CHANGE id id INT UNSIGNED; You still need the statement above to change the autoinc to integer if you use my Create Table... statement above. ALTER TABLE the_table_clone DROP PRIMARY KEY; # add 2 new columns to clone ALTER TABLE the_table_clone ADD flag_field int(11) DEFAULT '0', ADD price_amount float DEFAULT '0'; # copy rows INSERT the_table_clone SELECT *, NULL, NULL FROM the_table LIMIT 0,1000; #INSERT the_table_clone SELECT *, NULL, NULL FROM the_table; Why do you have two insert statements? If you are inserting a group of records at a time then you need a limit statement on each, and increment the offset by the number of rows that have been added. The 2nd INSERT is disabled with the # character. I am using the statement with the LIMIT for testing, and will switch to the other command when I want to process all the records. I would explicitly specify the column list for both the Insert and the Select to make sure they match up. There is no point going through all this if it inserts the data into the wrong columns! Check the data before creating the indexes to make sure the same number of rows have been copied over and the data is in the correct columns. Okay. I thought it was safe to assume that the new columns would appear on the right-side of the column list. # Add back indices in one command (for max speed) ALTER TABLE the_table_clone \ ADD INDEX IX_the_table_on_col2_col3 (col2,col3),\ ADD INDEX IX_the_table_on_col4_col6 (col4,col6),\ ADD INDEX IX_the_table_on_col5_col2 (col5,col2),\ MODIFY id INT SIGNED AUTO_INCREMENT,\ ADD PRIMARY KEY(col1); Correct. The insert took 7 minutes on 10M rows, and that ALTER command took another 46 minutes. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Slow ALTER TABLE on 70M row InnoDB table
It appears the ALTER TABLE starts off quick, and then slows down. I feel like the indices are larger than allocated RAM, and the system is slowing down because it is busy swapping out to disk. Is there an InnoDB specific buffer than can help this? The sort_buffer_size apparently is only for ISAM files. My InnoDB specific settings are: innodb_additional_mem_pool_size=13M innodb_flush_log_at_trx_commit=2 innodb_log_buffer_size=7M innodb_buffer_pool_size=616M innodb_log_file_size=24M innodb_thread_concurrency=10 On Wed, Aug 18, 2010 at 4:50 PM, mos mo...@fastmail.fm wrote: At 02:52 PM 8/18/2010, Xn Nooby wrote: Below is a generic version of the code I am trying. It does copy the rows very quickly, but I will have to test to see how quickly the indices are built. Is the below code what you were suggesting? I had a little trouble dropping and later adding the primary index, but I think I got it figured out. Below I basically do this: make the_table_clone from the the_table drop the indices on the_table_clone copy the row from the_table to the_table_clone add the indices back to the_table_clone If this runs fast enough, I will then drop the_table, and rename the_table_clone to the_table USE the_database; DROP TABLE IF EXISTS the_table_clone; CREATE TABLE the_table_clone LIKE the_table; Or you can try something like: create table the_table_clone engine=innodb select * from the_table limit=0; This will create the same table structure but not the indexes so you don't have to drop the indexes below. # drop minor indices on clone ALTER TABLE the_table_clone DROP INDEX IX_the_table_on_col2_col3; ALTER TABLE the_table_clone DROP INDEX IX_the_table_on_col4_col6; ALTER TABLE the_table_clone DROP INDEX IX_the_table_on_col5_col2; # drop primary index on clone ALTER TABLE the_table_clone CHANGE id id INT UNSIGNED; You still need the statement above to change the autoinc to integer if you use my Create Table... statement above. ALTER TABLE the_table_clone DROP PRIMARY KEY; # add 2 new columns to clone ALTER TABLE the_table_clone ADD flag_field int(11) DEFAULT '0', ADD price_amount float DEFAULT '0'; # copy rows INSERT the_table_clone SELECT *, NULL, NULL FROM the_table LIMIT 0,1000; #INSERT the_table_clone SELECT *, NULL, NULL FROM the_table; Why do you have two insert statements? If you are inserting a group of records at a time then you need a limit statement on each, and increment the offset by the number of rows that have been added. I would explicitly specify the column list for both the Insert and the Select to make sure they match up. There is no point going through all this if it inserts the data into the wrong columns! Check the data before creating the indexes to make sure the same number of rows have been copied over and the data is in the correct columns. # Add back indices in one command (for max speed) ALTER TABLE the_table_clone \ ADD INDEX IX_the_table_on_col2_col3 (col2,col3),\ ADD INDEX IX_the_table_on_col4_col6 (col4,col6),\ ADD INDEX IX_the_table_on_col5_col2 (col5,col2),\ MODIFY id INT SIGNED AUTO_INCREMENT,\ ADD PRIMARY KEY(col1); Correct. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Slow ALTER TABLE on 70M row InnoDB table
From what I have read, ALTER TABLE to add an index causes the entire table to be duplicated, so wouldn't my ALTER TABLE command be duplicating the work done by the SELECT command? On Wed, Aug 18, 2010 at 4:50 PM, mos mo...@fastmail.fm wrote: At 02:52 PM 8/18/2010, Xn Nooby wrote: Below is a generic version of the code I am trying. It does copy the rows very quickly, but I will have to test to see how quickly the indices are built. Is the below code what you were suggesting? I had a little trouble dropping and later adding the primary index, but I think I got it figured out. Below I basically do this: make the_table_clone from the the_table drop the indices on the_table_clone copy the row from the_table to the_table_clone add the indices back to the_table_clone If this runs fast enough, I will then drop the_table, and rename the_table_clone to the_table USE the_database; DROP TABLE IF EXISTS the_table_clone; CREATE TABLE the_table_clone LIKE the_table; Or you can try something like: create table the_table_clone engine=innodb select * from the_table limit=0; This will create the same table structure but not the indexes so you don't have to drop the indexes below. # drop minor indices on clone ALTER TABLE the_table_clone DROP INDEX IX_the_table_on_col2_col3; ALTER TABLE the_table_clone DROP INDEX IX_the_table_on_col4_col6; ALTER TABLE the_table_clone DROP INDEX IX_the_table_on_col5_col2; # drop primary index on clone ALTER TABLE the_table_clone CHANGE id id INT UNSIGNED; You still need the statement above to change the autoinc to integer if you use my Create Table... statement above. ALTER TABLE the_table_clone DROP PRIMARY KEY; # add 2 new columns to clone ALTER TABLE the_table_clone ADD flag_field int(11) DEFAULT '0', ADD price_amount float DEFAULT '0'; # copy rows INSERT the_table_clone SELECT *, NULL, NULL FROM the_table LIMIT 0,1000; #INSERT the_table_clone SELECT *, NULL, NULL FROM the_table; Why do you have two insert statements? If you are inserting a group of records at a time then you need a limit statement on each, and increment the offset by the number of rows that have been added. I would explicitly specify the column list for both the Insert and the Select to make sure they match up. There is no point going through all this if it inserts the data into the wrong columns! Check the data before creating the indexes to make sure the same number of rows have been copied over and the data is in the correct columns. # Add back indices in one command (for max speed) ALTER TABLE the_table_clone \ ADD INDEX IX_the_table_on_col2_col3 (col2,col3),\ ADD INDEX IX_the_table_on_col4_col6 (col4,col6),\ ADD INDEX IX_the_table_on_col5_col2 (col5,col2),\ MODIFY id INT SIGNED AUTO_INCREMENT,\ ADD PRIMARY KEY(col1); Correct. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
ALTER TABLE order / optimization
Given table: CREATE TABLE testtab (d_col CHAR(4)); Question 1: It appears that there is no harm in just appending directives onto the alter table command even if the order doesn't make sense. It appears the parser figures it out... For example... ALTER TABLE testtab ADD COLUMN c_col char(4) FIRST, ADD COLUMN b_col char(4) FIRST, ADD COLUMN a_col char(4) FIRST; ...does end up with a_col then b_col then c_col then d_col... but does it matter and I doing something wrong? Question 2: Is that any more efficient than doing... ALTER TABLE testtab ADD COLUMN c_col char(4) FIRST; ALTER TABLE testtab ADD COLUMN b_col char(4) FIRST; ALTER TABLE testtab ADD COLUMN a_col char(4) FIRST; If it's NOT more efficient then I won't bother rewriting this one app which runs slowly to join them up because it certain is easier to read and debug with each modification on its own line. Thanks! Matt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: ALTER TABLE order / optimization
If your table testtab is populated, neither suggestion is efficient. You could the following instead: # # Create an empty table `testtab_copy` # 1) CREATE TABLE testtab_copy LIKE testtab; 2) Do either of you suggestions: ALTER TABLE testtab ADD COLUMN c_col char(4) FIRST,ADD COLUMN b_col char(4) FIRST,ADD COLUMN a_col char(4) FIRST; or ALTER TABLE testtab ADD COLUMN c_col char(4) FIRST; ALTER TABLE testtab ADD COLUMN b_col char(4) FIRST; ALTER TABLE testtab ADD COLUMN a_col char(4) FIRST; 3) INSERT INTO testtab_copy (d_col) SELECT d_col FROM testtab; 4) DROP TABLE testtab; 5) ALTER TABLE testtab_copy RENAME testtab; Rolando A. Edwards MySQL DBA (CMDBA) 155 Avenue of the Americas, Fifth Floor New York, NY 10013 212-625-5307 (Work) 201-660-3221 (Cell) AIM Skype : RolandoLogicWorx redwa...@logicworks.net http://www.linkedin.com/in/rolandoedwards -Original Message- From: Matt Neimeyer [mailto:m...@neimeyer.org] Sent: Friday, September 04, 2009 3:53 PM To: mysql@lists.mysql.com Subject: ALTER TABLE order / optimization Given table: CREATE TABLE testtab (d_col CHAR(4)); Question 1: It appears that there is no harm in just appending directives onto the alter table command even if the order doesn't make sense. It appears the parser figures it out... For example... ALTER TABLE testtab ADD COLUMN c_col char(4) FIRST, ADD COLUMN b_col char(4) FIRST, ADD COLUMN a_col char(4) FIRST; ...does end up with a_col then b_col then c_col then d_col... but does it matter and I doing something wrong? Question 2: Is that any more efficient than doing... ALTER TABLE testtab ADD COLUMN c_col char(4) FIRST; ALTER TABLE testtab ADD COLUMN b_col char(4) FIRST; ALTER TABLE testtab ADD COLUMN a_col char(4) FIRST; If it's NOT more efficient then I won't bother rewriting this one app which runs slowly to join them up because it certain is easier to read and debug with each modification on its own line. Thanks! Matt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=redwa...@logicworks.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: cannot alter table - solved
On Fri, May 22, 2009 at 12:26 AM, PJ af.gour...@videotron.ca wrote: Michael Dykman wrote: On Thu, May 21, 2009 at 11:06 PM, PJ af.gour...@videotron.ca wrote: I have a seemingly impossible situation. I cannot insert values into the tables and I cannot alter or delete the primary key (which should not exist) or delete the foreign keys nor remove the constraint. G search doesn't help. CREATE TABLE `book_categories` ( `bookID` smallint(6) unsigned NOT NULL, `categories_id` int(2) unsigned NOT NULL, PRIMARY KEY (`bookID`,`categories_id`), KEY `fk_book_categories_books` (`bookID`), KEY `fk_book_categories_categories` (`categories_id`), CONSTRAINT `book_categories_ibfk_1` FOREIGN KEY (`book_id`) REFERENCES `book` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=latin1 Anybody out there still up? I'm rather desperate to fix this this evening... Thanks in advance. We will need a little more information. The table looks sound but is clearly designed to link 2 other tables. If you are failing to insert or update, it seems likely that it is because the data is absent in the foreign tables. Can you confirm? Because without that forgeign data, these rows are pretty meaningless. What is it you are trying to do? I was trying to insert some records to fill up empty id numbers and in the process noticed that there is a primary key in the tables but unnecessary if I am not mistaken. Also the book_categories.categories_id should be referencing categories.id -- I think I had somehow wet up the table erroneously. The problem was that one of the books was not entered as it should have and I was assuming it had been entered (2 others were at the same time - using phpMyAdmin instead of my insert page). It now works with minimal bugs on the back-end, but the panic is over. I'll try to fix the primary key issue next. Thanks for the quick response. I would suggest that the primary key is imoprtant. All relational tables need a primary key and, in this particular case, the primary key is what is preventing you from creating duplicate rows. If anything needs to go: KEY `fk_book_categories_books` (`bookID`), bookID, being the first part of your compound primary key, is effectively indexed already. The key listed above is quite unnecessary. -- - michael dykman - mdyk...@gmail.com - All models are wrong. Some models are useful. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: cannot alter table - solved
Michael Dykman wrote: On Fri, May 22, 2009 at 12:26 AM, PJ af.gour...@videotron.ca wrote: Michael Dykman wrote: On Thu, May 21, 2009 at 11:06 PM, PJ af.gour...@videotron.ca wrote: I have a seemingly impossible situation. I cannot insert values into the tables and I cannot alter or delete the primary key (which should not exist) or delete the foreign keys nor remove the constraint. G search doesn't help. CREATE TABLE `book_categories` ( `bookID` smallint(6) unsigned NOT NULL, `categories_id` int(2) unsigned NOT NULL, PRIMARY KEY (`bookID`,`categories_id`), KEY `fk_book_categories_books` (`bookID`), KEY `fk_book_categories_categories` (`categories_id`), CONSTRAINT `book_categories_ibfk_1` FOREIGN KEY (`book_id`) REFERENCES `book` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=latin1 Anybody out there still up? I'm rather desperate to fix this this evening... Thanks in advance. We will need a little more information. The table looks sound but is clearly designed to link 2 other tables. If you are failing to insert or update, it seems likely that it is because the data is absent in the foreign tables. Can you confirm? Because without that forgeign data, these rows are pretty meaningless. What is it you are trying to do? I was trying to insert some records to fill up empty id numbers and in the process noticed that there is a primary key in the tables but unnecessary if I am not mistaken. Also the book_categories.categories_id should be referencing categories.id -- I think I had somehow wet up the table erroneously. The problem was that one of the books was not entered as it should have and I was assuming it had been entered (2 others were at the same time - using phpMyAdmin instead of my insert page). It now works with minimal bugs on the back-end, but the panic is over. I'll try to fix the primary key issue next. Thanks for the quick response. I would suggest that the primary key is imoprtant. All relational tables need a primary key and, in this particular case, the primary key is what is preventing you from creating duplicate rows. If anything needs to go: KEY `fk_book_categories_books` (`bookID`), bookID, being the first part of your compound primary key, is effectively indexed already. The key listed above is quite unnecessary. Actually, that key (book_categories.bookID) references book.id; book_categories.categories_id references categories.id. Is'nt it necessary for both to have foreigh keys? Things so far are working fine... -- Hervé Kempf: Pour sauver la planète, sortez du capitalisme. - Phil Jourdan --- p...@ptahhotep.com http://www.ptahhotep.com http://www.chiccantine.com/andypantry.php -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: cannot alter table - solved
You do need the foreign keys for integrity and the columns which make the foreign reference should be indexed as well. My only point is that bookID is already indexed as the first element in the primary key, so the additional index on bookID alone is superfluous. - michael On Fri, May 22, 2009 at 3:17 PM, PJ af.gour...@videotron.ca wrote: Michael Dykman wrote: On Fri, May 22, 2009 at 12:26 AM, PJ af.gour...@videotron.ca wrote: Michael Dykman wrote: On Thu, May 21, 2009 at 11:06 PM, PJ af.gour...@videotron.ca wrote: I have a seemingly impossible situation. I cannot insert values into the tables and I cannot alter or delete the primary key (which should not exist) or delete the foreign keys nor remove the constraint. G search doesn't help. CREATE TABLE `book_categories` ( `bookID` smallint(6) unsigned NOT NULL, `categories_id` int(2) unsigned NOT NULL, PRIMARY KEY (`bookID`,`categories_id`), KEY `fk_book_categories_books` (`bookID`), KEY `fk_book_categories_categories` (`categories_id`), CONSTRAINT `book_categories_ibfk_1` FOREIGN KEY (`book_id`) REFERENCES `book` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=latin1 Anybody out there still up? I'm rather desperate to fix this this evening... Thanks in advance. We will need a little more information. The table looks sound but is clearly designed to link 2 other tables. If you are failing to insert or update, it seems likely that it is because the data is absent in the foreign tables. Can you confirm? Because without that forgeign data, these rows are pretty meaningless. What is it you are trying to do? I was trying to insert some records to fill up empty id numbers and in the process noticed that there is a primary key in the tables but unnecessary if I am not mistaken. Also the book_categories.categories_id should be referencing categories.id -- I think I had somehow wet up the table erroneously. The problem was that one of the books was not entered as it should have and I was assuming it had been entered (2 others were at the same time - using phpMyAdmin instead of my insert page). It now works with minimal bugs on the back-end, but the panic is over. I'll try to fix the primary key issue next. Thanks for the quick response. I would suggest that the primary key is imoprtant. All relational tables need a primary key and, in this particular case, the primary key is what is preventing you from creating duplicate rows. If anything needs to go: KEY `fk_book_categories_books` (`bookID`), bookID, being the first part of your compound primary key, is effectively indexed already. The key listed above is quite unnecessary. Actually, that key (book_categories.bookID) references book.id; book_categories.categories_id references categories.id. Is'nt it necessary for both to have foreigh keys? Things so far are working fine... -- Hervé Kempf: Pour sauver la plančte, sortez du capitalisme. - Phil Jourdan --- p...@ptahhotep.com http://www.ptahhotep.com http://www.chiccantine.com/andypantry.php -- - michael dykman - mdyk...@gmail.com - All models are wrong. Some models are useful. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
cannot alter table - rather urgent
I have a seemingly impossible situation. I cannot insert values into the tables and I cannot alter or delete the primary key (which should not exist) or delete the foreign keys nor remove the constraint. G search doesn't help. CREATE TABLE `book_categories` ( `bookID` smallint(6) unsigned NOT NULL, `categories_id` int(2) unsigned NOT NULL, PRIMARY KEY (`bookID`,`categories_id`), KEY `fk_book_categories_books` (`bookID`), KEY `fk_book_categories_categories` (`categories_id`), CONSTRAINT `book_categories_ibfk_1` FOREIGN KEY (`book_id`) REFERENCES `book` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=latin1 Anybody out there still up? I'm rather desperate to fix this this evening... Thanks in advance. -- Hervé Kempf: Pour sauver la planète, sortez du capitalisme. - Phil Jourdan --- p...@ptahhotep.com http://www.ptahhotep.com http://www.chiccantine.com/andypantry.php -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: cannot alter table - rather urgent
On Thu, May 21, 2009 at 11:06 PM, PJ af.gour...@videotron.ca wrote: I have a seemingly impossible situation. I cannot insert values into the tables and I cannot alter or delete the primary key (which should not exist) or delete the foreign keys nor remove the constraint. G search doesn't help. CREATE TABLE `book_categories` ( `bookID` smallint(6) unsigned NOT NULL, `categories_id` int(2) unsigned NOT NULL, PRIMARY KEY (`bookID`,`categories_id`), KEY `fk_book_categories_books` (`bookID`), KEY `fk_book_categories_categories` (`categories_id`), CONSTRAINT `book_categories_ibfk_1` FOREIGN KEY (`book_id`) REFERENCES `book` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=latin1 Anybody out there still up? I'm rather desperate to fix this this evening... Thanks in advance. We will need a little more information. The table looks sound but is clearly designed to link 2 other tables. If you are failing to insert or update, it seems likely that it is because the data is absent in the foreign tables. Can you confirm? Because without that forgeign data, these rows are pretty meaningless. What is it you are trying to do? -- - michael dykman - mdyk...@gmail.com - All models are wrong. Some models are useful. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: cannot alter table - rather urgent
I cannot insert values into the tables What is the error message? Has the smallint key run out of values? and I cannot alter or delete the primary key (which should not exist) Eh? Without a PK, it ain't a table. or delete the foreign keys nor remove the constraint. G search doesn't help. If the pk referenced by the fk is full, I think you need to drop the fk, then drop the pk in the table referenced by the fk, then recreate that pk as an int, then recreate the fk. PB - PJ wrote: I have a seemingly impossible situation. I cannot insert values into the tables and I cannot alter or delete the primary key (which should not exist) or delete the foreign keys nor remove the constraint. G search doesn't help. CREATE TABLE `book_categories` ( `bookID` smallint(6) unsigned NOT NULL, `categories_id` int(2) unsigned NOT NULL, PRIMARY KEY (`bookID`,`categories_id`), KEY `fk_book_categories_books` (`bookID`), KEY `fk_book_categories_categories` (`categories_id`), CONSTRAINT `book_categories_ibfk_1` FOREIGN KEY (`book_id`) REFERENCES `book` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=latin1 Anybody out there still up? I'm rather desperate to fix this this evening... Thanks in advance. No virus found in this incoming message. Checked by AVG - www.avg.com Version: 8.5.339 / Virus Database: 270.12.36/2126 - Release Date: 05/21/09 06:22:00
Re: cannot alter table - solved
Michael Dykman wrote: On Thu, May 21, 2009 at 11:06 PM, PJ af.gour...@videotron.ca wrote: I have a seemingly impossible situation. I cannot insert values into the tables and I cannot alter or delete the primary key (which should not exist) or delete the foreign keys nor remove the constraint. G search doesn't help. CREATE TABLE `book_categories` ( `bookID` smallint(6) unsigned NOT NULL, `categories_id` int(2) unsigned NOT NULL, PRIMARY KEY (`bookID`,`categories_id`), KEY `fk_book_categories_books` (`bookID`), KEY `fk_book_categories_categories` (`categories_id`), CONSTRAINT `book_categories_ibfk_1` FOREIGN KEY (`book_id`) REFERENCES `book` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=latin1 Anybody out there still up? I'm rather desperate to fix this this evening... Thanks in advance. We will need a little more information. The table looks sound but is clearly designed to link 2 other tables. If you are failing to insert or update, it seems likely that it is because the data is absent in the foreign tables. Can you confirm? Because without that forgeign data, these rows are pretty meaningless. What is it you are trying to do? I was trying to insert some records to fill up empty id numbers and in the process noticed that there is a primary key in the tables but unnecessary if I am not mistaken. Also the book_categories.categories_id should be referencing categories.id -- I think I had somehow wet up the table erroneously. The problem was that one of the books was not entered as it should have and I was assuming it had been entered (2 others were at the same time - using phpMyAdmin instead of my insert page). It now works with minimal bugs on the back-end, but the panic is over. I'll try to fix the primary key issue next. Thanks for the quick response. -- Hervé Kempf: Pour sauver la planète, sortez du capitalisme. - Phil Jourdan --- p...@ptahhotep.com http://www.ptahhotep.com http://www.chiccantine.com/andypantry.php -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Alter Table - InnoDB
Hello, we having an 60 GB InnoDB database. The table with the problem is about 12GB. On of our scripts has got a problem and run 60 times an alter table: ALTER TABLE `foo` ADD INDEX ( `bar` ) ; We had to kill the alter table commands with kill on the mysql console. Now we have one index on the column, but this index is broken. We have to delete this index. But the main problem is the disk usage: When this problem occur the disk usage of the ibdata file bumps up to 85 GB. I think after I have killed the alter table commands mysql does not delete the temp tables from the alter table command. How I can remove the temp tables? - I have also read at some bug reports if I now restart the database mysql performs an InnoDB recovery and restores an old temp table to the current table! Is this correct? Thanks, Jonas -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Alter Table - InnoDB
Hi Jonas, When ever an alter table command is executed on mysql tables, mysql creates a temp file with all the data + the current table. Once the alter table command is done, it will drop the current table and rename the new temp file to the current table, that the reason the disk usage goes high. Since u have cancled the job, those in-complete temp files can be deleted from the file system. Alter when adding indexes use set sort_buffer_size=100*1024*1024; so that index creation uses more memory and the execution is faster. regards anandkl On 12/4/08, Jonas Genannt [EMAIL PROTECTED] wrote: Hello, we having an 60 GB InnoDB database. The table with the problem is about 12GB. On of our scripts has got a problem and run 60 times an alter table: ALTER TABLE `foo` ADD INDEX ( `bar` ) ; We had to kill the alter table commands with kill on the mysql console. Now we have one index on the column, but this index is broken. We have to delete this index. But the main problem is the disk usage: When this problem occur the disk usage of the ibdata file bumps up to 85 GB. I think after I have killed the alter table commands mysql does not delete the temp tables from the alter table command. How I can remove the temp tables? - I have also read at some bug reports if I now restart the database mysql performs an InnoDB recovery and restores an old temp table to the current table! Is this correct? Thanks, Jonas -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Alter Table - InnoDB
Hi Ananda, Since u have cancled the job, those in-complete temp files can be deleted from the file system. ok - but I'm using InnoDB. The IBdata file is bumped up. There are no temp files on the database directory. Greets, Jonas -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Alter Table - InnoDB
So, now u dont have free space in your file system. Is this a production db. I think, restarting the db, should not cause any harm. Which version of mysql. regards anandkl On 12/4/08, Jonas Genannt [EMAIL PROTECTED] wrote: Hi Ananda, Since u have cancled the job, those in-complete temp files can be deleted from the file system. ok - but I'm using InnoDB. The IBdata file is bumped up. There are no temp files on the database directory. Greets, Jonas
Re: Alter Table - InnoDB
I thought you always have to go to the physical location and delete the tmp files manually. These are created in tmp folder. I am not sure if restarting helps... Regards, Shachi Govil - Original Message - From: Ananda Kumar [EMAIL PROTECTED] To: Jonas Genannt [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Thursday, December 04, 2008 4:09 PM Subject: Re: Alter Table - InnoDB So, now u dont have free space in your file system. Is this a production db. I think, restarting the db, should not cause any harm. Which version of mysql. regards anandkl On 12/4/08, Jonas Genannt [EMAIL PROTECTED] wrote: Hi Ananda, Since u have cancled the job, those in-complete temp files can be deleted from the file system. ok - but I'm using InnoDB. The IBdata file is bumped up. There are no temp files on the database directory. Greets, Jonas -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Alter Table - InnoDB
Hi Ananda, So, now u dont have free space in your file system. Is this a production db. I think, restarting the db, should not cause any harm. Which version of mysql. no free space it not my problem. I have only noticed that ibdata file uses more space than before. Yes this is an production database :( Greets, Jonas -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Alter Table - InnoDB
Hello Shachi, I thought you always have to go to the physical location and delete the tmp files manually. These are created in tmp folder. I am not sure if restarting helps... since I am using InnoDB, there is no tmp folder. The Ibdata file after killing alter table commands: -rw-rw 1 mysql mysql 85G 2008-12-04 12:04 ibdata1 The Ibdata file before all alter table commands: -rw-rw 1 mysql mysql 60G 2008-12-03 18:04 ibdata1 Greets, Jonas -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Alter Table - InnoDB
Hi, Since u have cancled the job, those in-complete temp files can be deleted from the file system. ok - but I'm using InnoDB. The IBdata file is bumped up. There are no temp files on the database directory. What's the problem with a larger ibdata-file? If the index is dropped, there is free space inside the file that will be re-used. With regards, Martijn Tonies Upscene Productions http://www.upscene.com FREE Database Workbench Lite for MySQL at the above website! Database questions? Check the forum: http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Alter Table - InnoDB
Hi jones, Innodb does not release the space unless you optimize the tables. To dot that you need to run dummy alter on all tables, by issuing Alter table table name engine=InnoDB but the space shall not regaing unless you start the table with innodb_file_per_table option. Then if you run the alter you shall gain space that was occupied. But in your current scenario, there is space that is occupied that is not shall not get released from OS even you run alter on the table. The space shall be available in the table space ibdata. If you have a option of reimporting then take a dump, reimport the same with innodb_file_per_table option enabled. Regards, Chandru. www.mafiree.com On Thu, Dec 4, 2008 at 4:19 PM, Shachi Govil [EMAIL PROTECTED]wrote: I thought you always have to go to the physical location and delete the tmp files manually. These are created in tmp folder. I am not sure if restarting helps... Regards, Shachi Govil - Original Message - From: Ananda Kumar [EMAIL PROTECTED] To: Jonas Genannt [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Thursday, December 04, 2008 4:09 PM Subject: Re: Alter Table - InnoDB So, now u dont have free space in your file system. Is this a production db. I think, restarting the db, should not cause any harm. Which version of mysql. regards anandkl On 12/4/08, Jonas Genannt [EMAIL PROTECTED] wrote: Hi Ananda, Since u have cancled the job, those in-complete temp files can be deleted from the file system. ok - but I'm using InnoDB. The IBdata file is bumped up. There are no temp files on the database directory. Greets, Jonas -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
RE: Alter Table - InnoDB
Actually, that will not reduce the size of the ibdata1 file at all. Here is a GUARANTEED WAY to shrink that ibdata1 file, which YOU WILL NOW HAVE TO REPEAT EVER AGAIN !!! 1. Perform mysqldump of all databases, routines and triggers from the mysql server to /root/MyData.sql 2. Drop all databases from the mysql server 3. Add 'innodb_file_per_table' to [mysqld] section of /etc/my.cnf 4. Do 'service mysql stop' 5. Do 'rm /var/lib/mysql/ibdata1 /var/lib/mysql/ib_log*' 6. Do 'service mysql start' (ibdata1, ib_logfile 0, and ib_logfile1 are rebuilt) 7. Reload mysql sever from /root/MySQLData.sql The only way ibdata1 will grow is with internal data dictionary information on InnoDB tables. Data and Index Info will all reside in its own tablespace on a per-table basis. When you run OPTIMIZE TABLE on an InnoDB table in its own tablespace, that table will actually shrink. Example (without innodb_file_per_table) CREATE TABLE mydb.mytable (...) ENGINE=InnoDB; Does two things 1. creates mytable.frm in /var/lib/mysql/mydb folder 2. writes table index, and data dictionary info about the table in /var/lib/mysql/ibdata1. 'OPTIMIZE TABLE mydb.mytable;' will actually append the entire mytable table in contiguous blocks or pages to ibdata1, and then perform 'ANALYZE TABLE mydb.mytable;' Example (with innodb_file_per_table) CREATE TABLE mydb.mytable (...) ENGINE=InnoDB; Does three things: 1. creates mytable.frm in /var/lib/mysql/mydb folder 2. creates mytable.ibd in /var/lib/mysql/mydb folder 3. writes data dictionary info about the table in /var/lib/mysql/ibdata1 'OPTIMIZE TABLE mydb.mytable;' Will actually shrink mytable.ibd and perform 'ANALYZE TABLE mydb.mytable;' ibdata1 NEVER GROWS due to mytable table !!! They only way to make ibdata1 grow it to create lots of InnoDB tables, which will just data dictionary info for every InnoDB table. No more data and no more index info. I once did this a job where the company had a 50 GB ibdata1 file, which collapsed quickly to 500 MB ( 0.5 GB) due to wasted space from transactions and old data pages and old index pages. Give it a try and let me know if this worked. I know it will work. -Original Message- From: Chandru [mailto:[EMAIL PROTECTED] Sent: Thursday, December 04, 2008 7:10 AM To: Shachi Govil; Jonas Genannt Cc: Ananda Kumar; mysql@lists.mysql.com Subject: Re: Alter Table - InnoDB Hi jones, Innodb does not release the space unless you optimize the tables. To dot that you need to run dummy alter on all tables, by issuing Alter table table name engine=InnoDB but the space shall not regaing unless you start the table with innodb_file_per_table option. Then if you run the alter you shall gain space that was occupied. But in your current scenario, there is space that is occupied that is not shall not get released from OS even you run alter on the table. The space shall be available in the table space ibdata. If you have a option of reimporting then take a dump, reimport the same with innodb_file_per_table option enabled. Regards, Chandru. www.mafiree.com On Thu, Dec 4, 2008 at 4:19 PM, Shachi Govil [EMAIL PROTECTED]wrote: I thought you always have to go to the physical location and delete the tmp files manually. These are created in tmp folder. I am not sure if restarting helps... Regards, Shachi Govil - Original Message - From: Ananda Kumar [EMAIL PROTECTED] To: Jonas Genannt [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Thursday, December 04, 2008 4:09 PM Subject: Re: Alter Table - InnoDB So, now u dont have free space in your file system. Is this a production db. I think, restarting the db, should not cause any harm. Which version of mysql. regards anandkl On 12/4/08, Jonas Genannt [EMAIL PROTECTED] wrote: Hi Ananda, Since u have cancled the job, those in-complete temp files can be deleted from the file system. ok - but I'm using InnoDB. The IBdata file is bumped up. There are no temp files on the database directory. Greets, Jonas -- 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]
Alter table
Hi Everybody, I would like to know that, while changing storage engine from one type to another locks that table or not. alter table table-name engine=myisam; alter table table-name engine=innodb; During the process of changing storage engine from one type to another. Whether it will work fine for an online system. Thanks, -- Krishna Chandra Prajapati MySQL DBA, Ed Ventures e-Learning Pvt. Ltd, 201,202, Ashoka Bhoopal Chambers, S P Road, Secunderabad 53. Ph. No. - 040-39188771 Url: www.ed-ventures-online.com
答复: Alter table
it will lock the table,read and write are blocked during the process. -原始邮件- 发件人: Krishna Chandra Prajapati [mailto:[EMAIL PROTECTED] 发送时间: 2007年11月19日 15:24 收件人: MySql 主题: Alter table Hi Everybody, I would like to know that, while changing storage engine from one type to another locks that table or not. alter table table-name engine=myisam; alter table table-name engine=innodb; During the process of changing storage engine from one type to another. Whether it will work fine for an online system. Thanks, -- Krishna Chandra Prajapati MySQL DBA, Ed Ventures e-Learning Pvt. Ltd, 201,202, Ashoka Bhoopal Chambers, S P Road, Secunderabad 53. Ph. No. - 040-39188771 Url: www.ed-ventures-online.com
Re: Alter table - adding constraints?
Chris White wrote: Jay Paulson wrote: 2) both tables have data in them. This is most likely your issue then, depending on the table size, go through and make sure that anything in the referenced column matches the referencing column. You should also be able to use SHOW INNODB STATUS to see what's possibly failing. No, it's because you already have a constraint in your schema called fk_regions. Just change the constraint name to one that is unique for your schema... This is the reason I usually name myu constraints like: fk_fromtable_totable So this doesn't become an issue. cheers, Jay -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Alter table - adding constraints?
I really don¹t know what to do because I keep getting this error. Any ideas? SQL query: ALTER TABLE pl_reports ADD CONSTRAINT fk_region FOREIGN KEY ( region ) REFERENCES Region( id ) ON UPDATE CASCADE ON DELETE CASCADE MySQL said: Documentation #1005 - Can't create table './survey_localhost/#sql-113_f8.frm' (errno: 150) Thanks!
Re: Alter table - adding constraints?
Jay Paulson wrote: I really don¹t know what to do because I keep getting this error. Any ideas? SQL query: ALTER TABLE pl_reports ADD CONSTRAINT fk_region FOREIGN KEY ( region ) REFERENCES Region( id ) ON UPDATE CASCADE ON DELETE CASCADE MySQL said: Documentation #1005 - Can't create table './survey_localhost/#sql-113_f8.frm' (errno: 150) Thanks! 1) Are both tables InnoDB? 2) Do you have data in either tables? 3) Are the data types of both columns exactly the same ( int(20) unsigned and int(20) will fail, the unsigned has to be there)? you can also run SHOW INNODB STATUS as root to find out exactly what the error is. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Alter table - adding constraints?
1) both tables are InnoDB. 2) both tables have data in them. 3) both table are the exact same data types. On 2/20/07 3:51 PM, Chris White [EMAIL PROTECTED] wrote: Jay Paulson wrote: I really don¹t know what to do because I keep getting this error. Any ideas? SQL query: ALTER TABLE pl_reports ADD CONSTRAINT fk_region FOREIGN KEY ( region ) REFERENCES Region( id ) ON UPDATE CASCADE ON DELETE CASCADE MySQL said: Documentation #1005 - Can't create table './survey_localhost/#sql-113_f8.frm' (errno: 150) Thanks! 1) Are both tables InnoDB? 2) Do you have data in either tables? 3) Are the data types of both columns exactly the same ( int(20) unsigned and int(20) will fail, the unsigned has to be there)? you can also run SHOW INNODB STATUS as root to find out exactly what the error is. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Alter table - adding constraints?
Jay Paulson wrote: 2) both tables have data in them. This is most likely your issue then, depending on the table size, go through and make sure that anything in the referenced column matches the referencing column. You should also be able to use SHOW INNODB STATUS to see what's possibly failing. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: alter table ... import tablespace NFG?
David Sparks wrote: I want to move 3 100GB .ibd files into a new DB. I followed the instructions here: http://dev.mysql.com/doc/refman/5.0/en/multiple-tablespaces.html But it doesn't work: mysql alter table reports discard tablespace; Query OK, 0 rows affected (0.04 sec) mysql alter table reports import tablespace; ERROR 1030 (HY000): Got error -1 from storage engine Oh no! http://bugs.mysql.com/bug.php?id=5904 [5 Oct 2004 20:04] John David Duncan Description: Allow ALTER TABLE t IMPORT TABLESPACE to import a tablespace created on some other MySQL server. This requires changing the tablespace number (unless it is available...) and resetting the transaction identifiers in the imported .ibd file. ---%--- Argh! Note that some other MySQL server above applies to the same hardware and just a different mysql instance. ie If you're trying to restore from backup. Import tablespace has little use without being able to move the files between server instances. :( ds -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
alter table ... import tablespace NFG?
I want to move 3 100GB .ibd files into a new DB. I followed the instructions here: http://dev.mysql.com/doc/refman/5.0/en/multiple-tablespaces.html But it doesn't work: mysql alter table reports discard tablespace; Query OK, 0 rows affected (0.04 sec) mysql alter table reports import tablespace; ERROR 1030 (HY000): Got error -1 from storage engine mysqld.err says: 061206 14:57:35 InnoDB: Error: tablespace id in file './money/reports.ibd' is 88, but in the InnoDB InnoDB: data dictionary it is 34. InnoDB: Have you moved InnoDB .ibd files around without using the InnoDB: commands DISCARD TABLESPACE and IMPORT TABLESPACE? InnoDB: Please refer to InnoDB: http://dev.mysql.com/doc/mysql/en/InnoDB_troubleshooting_datadict.html InnoDB: for how to resolve the issue. 061206 14:57:35 InnoDB: cannot find or open in the database directory the .ibd file of InnoDB: table `money/reports` InnoDB: in ALTER TABLE ... IMPORT TABLESPACE How to fix this? ds -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Alter Table Add Column - How Long to update
On Fri, 2006-10-20 at 09:06 -0700, William R. Mussatto wrote: On Thu, October 19, 2006 18:24, Ow Mun Heng said: Just curious to know, I tried to update a table with ~1.7 million rows (~1G in size) and the update took close to 15-20 minutes before it says it's done. Is this kind of speed expected? I don't really understand how the alter table add column is done, but when I look at the show processlist I see that it says the state is copying into tmp table Does the alter table mean that MySQL has to copy the Entire table, row-by-row into a temporary table, and add in the additional column (or 2)?? Basic process for any change which modifies the structure of the table is to create a temporary table with the new structure, copy the information from the old table table to the new one (modifying as needed to match the new structure), drop the old table and rename the new table to the old tables name. So the time might be realistic. It depends on the hardware you are using and what else is going on on the system. I'm just a bit curious as to why this happens. Looking at the manuals / books etc, it says this is done so that other read processes can still access the DB/table in it's OLD state w/o any hiccups. I'm just not too sold on that idea given that, for eg: a MSSQL server, adds a new column in just secs rather than minutes on MySQL. But anyway, I do understand what is happening right now. Thanks. (So, if I were to want to add a column to a Table which has a couple of million rows, It would take a _looong_ time. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Alter Table Add Column - How Long to update
it says this is done so that other read processes can still access the DB/table in it's OLD state w/o any hiccups. I do not understand so how does the MSSQL DB work when we alter table add column* * 2006/10/22, Ow Mun Heng [EMAIL PROTECTED]: On Fri, 2006-10-20 at 09:06 -0700, William R. Mussatto wrote: On Thu, October 19, 2006 18:24, Ow Mun Heng said: Just curious to know, I tried to update a table with ~1.7 million rows (~1G in size) and the update took close to 15-20 minutes before it says it's done. Is this kind of speed expected? I don't really understand how the alter table add column is done, but when I look at the show processlist I see that it says the state is copying into tmp table Does the alter table mean that MySQL has to copy the Entire table, row-by-row into a temporary table, and add in the additional column (or 2)?? Basic process for any change which modifies the structure of the table is to create a temporary table with the new structure, copy the information from the old table table to the new one (modifying as needed to match the new structure), drop the old table and rename the new table to the old tables name. So the time might be realistic. It depends on the hardware you are using and what else is going on on the system. I'm just a bit curious as to why this happens. Looking at the manuals / books etc, it says this is done so that other read processes can still access the DB/table in it's OLD state w/o any hiccups. I'm just not too sold on that idea given that, for eg: a MSSQL server, adds a new column in just secs rather than minutes on MySQL. But anyway, I do understand what is happening right now. Thanks. (So, if I were to want to add a column to a Table which has a couple of million rows, It would take a _looong_ time. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Re: Alter Table Add Column - How Long to update
On Sun, 2006-10-22 at 21:32 +0800, 黄小聪 wrote: it says this is done so that other read processes can still access the DB/table in it's OLD state w/o any hiccups. I do not understand so how does the MSSQL DB work when we alter table add column Frankly, I don't really know. I tried adding a new column to an existing MSSQL DB and it really just took a few seconds. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Alter Table Add Column - How Long to update
On Thu, October 19, 2006 18:24, Ow Mun Heng said: Just curious to know, I tried to update a table with ~1.7 million rows (~1G in size) and the update took close to 15-20 minutes before it says it's done. Is this kind of speed expected? I don't really understand how the alter table add column is done, but when I look at the show processlist I see that it says the state is copying into tmp table Does the alter table mean that MySQL has to copy the Entire table, row-by-row into a temporary table, and add in the additional column (or 2)?? I'm using InnoDB by the way Basic process for any change which modifies the structure of the table is to create a temporary table with the new structure, copy the information from the old table table to the new one (modifying as needed to match the new structure), drop the old table and rename the new table to the old tables name. So the time might be realistic. It depends on the hardware you are using and what else is going on on the system. Hope this helps. --- William R. Mussatto, Senior Systems Engineer http://www.csz.com Ph. 909-920-9154 ext. 27 FAX. 909-608-7061 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Alter Table Add Column - How Long to update
Just curious to know, I tried to update a table with ~1.7 million rows (~1G in size) and the update took close to 15-20 minutes before it says it's done. Is this kind of speed expected? I don't really understand how the alter table add column is done, but when I look at the show processlist I see that it says the state is copying into tmp table Does the alter table mean that MySQL has to copy the Entire table, row-by-row into a temporary table, and add in the additional column (or 2)?? I'm using InnoDB by the way -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Alter Table Add Column - How Long to update?
Just curious to know, I tried to update a table with ~1.7 million rows (~1G in size) and the update took close to 15-20 minutes before it says it's done. Is this kind of speed expected? I don't really understand how the alter table add column is done, but when I look at the show processlist I see that it says the state is copying into tmp table Does the alter table mean that MySQL has to copy the Entire table, row-by-row into a temporary table, and add in the additional column (or 2)?? Btw, where is this temporary table? I don't see it in the DB. and I don't see it in the default /tmp directory. I'm using InnoDB by the way. Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Alter Table Add Column - How Long to update?
On Wed, 2006-10-18 at 09:29 +0800, Ow Mun Heng wrote: Just curious to know, I tried to update a table with ~1.7 million rows (~1G in size) and the update took close to 15-20 minutes before it says it's done. Is this kind of speed expected? I don't really understand how the alter table add column is done, but when I look at the show processlist I see that it says the state is copying into tmp table Does the alter table mean that MySQL has to copy the Entire table, row-by-row into a temporary table, and add in the additional column (or 2)?? Btw, where is this temporary table? I don't see it in the DB. and I don't see it in the default /tmp directory. This answers some of the questions. http://dev.mysql.com/doc/refman/5.0/en/temporary-files.html I'm using InnoDB by the way. Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ALTER TABLE
Hi, Try this: To modify an existing field: ALTER TABLE doc MODIFY docts timestamp DEFAULT CURRENT_TIMESTAMP; or To add new field ALTER TABLE doc ADD docts timestamp DEFAULT CURRENT_TIMESTAMP; Thanks, ViSolve DB Team. - Original Message - From: Peter Lauri [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Sunday, August 27, 2006 8:27 PM Subject: ALTER TABLE Hi, I am doing this thru the phpmyadmin interface: ALTER TABLE doc ALTER docts SET DEFAULT CURRENT_TIMESTAMP However, it returns #1064 - You have an error in your SQL syntax near 'DEFAULTCURRENT_TIMESTAMP' at line 1 As you can see the error shows that DEFAULT an CURRENT_TIMESTAMP has been written together, strange, or is my syntax in the ALTER TABLE wrong? /Peter
ALTER TABLE
Hi, I am doing this thru the phpmyadmin interface: ALTER TABLE doc ALTER docts SET DEFAULT CURRENT_TIMESTAMP However, it returns #1064 - You have an error in your SQL syntax near 'DEFAULTCURRENT_TIMESTAMP' at line 1 As you can see the error shows that DEFAULT an CURRENT_TIMESTAMP has been written together, strange, or is my syntax in the ALTER TABLE wrong? /Peter
RE: Alter table command don't work
I don't think you can do it prior to 4.1: http://dev.mysql.com/doc/refman/4.1/en/charset-general.html MySQL 4.1 can do these things for you: * Store strings using a variety of character sets * Compare strings using a variety of collations * Mix strings with different character sets or collations in the same server, the same database, or even the same table * Allow specification of character set and collation at any level In these respects, not only is MySQL 4.1 far more flexible than MySQL 4.0, it also is far ahead of most other database management systems. However, to use these features effectively, you need to know what character sets and collations are available, how to change the defaults, and how they affect the behavior of string operators and functions. -Original Message- From: David Logan [mailto:[EMAIL PROTECTED] Sent: Monday, 19 June 2006 10:36 a.m. To: Graham Reeds Cc: mysql@lists.mysql.com Subject: Re: Alter table command don't work Graham Reeds wrote: Quentin Bennett wrote: http://dev.mysql.com/doc/refman/4.1/en/alter-table.html From MySQL 4.1.2 on, if you want to change the table default character set and all character columns (CHAR, VARCHAR, TEXT) to a new character set, use a statement like this: ALTER TABLE tbl_name CONVERT TO CHARACTER SET charset_name; That is the syntax I used. What about prior to 4.1.2? Perhaps it was not available? See http://www.dev.mysql.com/doc/refman/4.1/en/charset-table.htm You may have to dump the data and then reload into a new table with the correct charset. You may be able to copy it using an INSERT SELECT (not sure though, haven't had to do it) Regards -- David Logan South Australia when in trouble, or in doubt run in circles, scream and shout -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] The information contained in this email is privileged and confidential and intended for the addressee only. If you are not the intended recipient, you are asked to respect that confidentiality and not disclose, copy or make use of its contents. If received in error you are asked to destroy this email and contact the sender immediately. Your assistance is appreciated. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Alter table command don't work
Using the text mode mysql client program I tried to alter a table - but received a syntax error. Why? mysql alter table blog convert to character set utf8; ERROR 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'convert to character set utf8' at line 1 G -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Alter table command don't work
Graham Reeds wrote: Using the text mode mysql client program I tried to alter a table - but received a syntax error. Why? This is using MySQL 4.0.25 btw. mysql alter table blog convert to character set utf8; ERROR 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'convert to character set utf8' at line 1 G -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Re: Alter table command don't work
http://dev.mysql.com/doc/refman/4.1/en/alter-table.html From MySQL 4.1.2 on, if you want to change the table default character set and all character columns (CHAR, VARCHAR, TEXT) to a new character set, use a statement like this: ALTER TABLE tbl_name CONVERT TO CHARACTER SET charset_name; -Original Message- From: news [mailto:[EMAIL PROTECTED] Behalf Of Graham Reeds Sent: Monday, 19 June 2006 8:53 a.m. To: mysql@lists.mysql.com Subject: Re: Alter table command don't work Graham Reeds wrote: Using the text mode mysql client program I tried to alter a table - but received a syntax error. Why? This is using MySQL 4.0.25 btw. mysql alter table blog convert to character set utf8; ERROR 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'convert to character set utf8' at line 1 G -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] The information contained in this email is privileged and confidential and intended for the addressee only. If you are not the intended recipient, you are asked to respect that confidentiality and not disclose, copy or make use of its contents. If received in error you are asked to destroy this email and contact the sender immediately. Your assistance is appreciated. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Alter table command don't work
Quentin Bennett wrote: http://dev.mysql.com/doc/refman/4.1/en/alter-table.html From MySQL 4.1.2 on, if you want to change the table default character set and all character columns (CHAR, VARCHAR, TEXT) to a new character set, use a statement like this: ALTER TABLE tbl_name CONVERT TO CHARACTER SET charset_name; That is the syntax I used. What about prior to 4.1.2? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Alter table command don't work
Graham Reeds wrote: Quentin Bennett wrote: http://dev.mysql.com/doc/refman/4.1/en/alter-table.html From MySQL 4.1.2 on, if you want to change the table default character set and all character columns (CHAR, VARCHAR, TEXT) to a new character set, use a statement like this: ALTER TABLE tbl_name CONVERT TO CHARACTER SET charset_name; That is the syntax I used. What about prior to 4.1.2? Perhaps it was not available? See http://www.dev.mysql.com/doc/refman/4.1/en/charset-table.htm You may have to dump the data and then reload into a new table with the correct charset. You may be able to copy it using an INSERT SELECT (not sure though, haven't had to do it) Regards -- David Logan South Australia when in trouble, or in doubt run in circles, scream and shout -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Alter table syntax question -
I am running MySql 4.1.6 in FC-4 Linux amd it is a huge learning exercise for me! I am using O'Reilly's MYSQL Cookbook, have gone through numerous pages of the reference manual and stuff on Google but still can get the proper commands and syntax to alter the following table T1. I would like Createdate to show the date the record was crated and remain that. I would like Workdate to show the current date unless changed by the user and remain there until the next time there was a change in the record. It would be good if these dates would come up immediately without re-opening the table as it seems to do now after my best efforts [which have never been completely successful] although that may well be a problem with Navicat which I am using as a GUI? My efforts at setting up the table have all been via the mysql command line. If someone could provide an example of a working ALTER TABLE T1 . command it would be greatly appreciated. Thank you. Bob Goodwin Zuni, Virginia # uname -a Linux box3 2.6.11-1.1369_FC4 #1 Thu Jun 2 22:55:56 EDT 2005 i686 athlon i386 GNU/Linux mysql select version(); +---+ | version() | +---+ | 4.1.16| +---+ 1 row in set (0.00 sec) mysql describe T1; ++--+--+-+-+ + | Field | Type | Null | Key | Default | Extra | ++--+--+-+-+ + | ID | int(6) | | PRI | NULL| auto_increment | | Createdate | timestamp| YES | | -00-00 00:00:00 | | | Lastname | varchar(75) | YES | | NULL | | | Firstname | varchar(75) | YES | | NULL | | | Address| varchar(75) | YES | | NULL | | | City | varchar(30) | YES | | NULL | | | Zip| varchar(15) | YES | | NULL | | | Area | varchar(20) | YES | | NULL | | | Tel01 | varchar(15) | YES | | NULL | | | Tel02 | varchar(15) | YES | | NULL | | | Tel03 | varchar(15) | YES | | NULL | | | Tel04 | varchar(15) | YES | | NULL | | | Category | varchar(20) | YES | | NULL | | | Workdate | timestamp| YES | | -00-00 00:00:00 | | | Notes | varchar(255) | YES | | NULL | | ++--+--+-+-+ + 15 rows in set (0.01 sec) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Alter table syntax question -
bobgoodwin wrote: I am running MySql 4.1.6 in FC-4 Linux amd it is a huge learning exercise for me! I am using O'Reilly's MYSQL Cookbook, have gone through numerous pages of the reference manual and stuff on Google but still can get the proper commands and syntax to alter the following table T1. I would like Createdate to show the date the record was crated and remain that. I would like Workdate to show the current date unless changed by the user and remain there until the next time there was a change in the record. It would be good if these dates would come up immediately without re-opening the table as it seems to do now after my best efforts [which have never been completely successful] although that may well be a problem with Navicat which I am using as a GUI? My efforts at setting up the table have all been via the mysql command line. If someone could provide an example of a working ALTER TABLE T1 . command it would be greatly appreciated. Thank you. Bob Goodwin Zuni, Virginia # uname -a Linux box3 2.6.11-1.1369_FC4 #1 Thu Jun 2 22:55:56 EDT 2005 i686 athlon i386 GNU/Linux mysql select version(); +---+ | version() | +---+ | 4.1.16| +---+ 1 row in set (0.00 sec) mysql describe T1; ++--+--+-+-+ + | Field | Type | Null | Key | Default | Extra | ++--+--+-+-+ + | ID | int(6) | | PRI | NULL| auto_increment | | Createdate | timestamp| YES | | -00-00 00:00:00 | | | Lastname | varchar(75) | YES | | NULL | | | Firstname | varchar(75) | YES | | NULL | | | Address| varchar(75) | YES | | NULL | | | City | varchar(30) | YES | | NULL | | | Zip| varchar(15) | YES | | NULL | | | Area | varchar(20) | YES | | NULL | | | Tel01 | varchar(15) | YES | | NULL | | | Tel02 | varchar(15) | YES | | NULL | | | Tel03 | varchar(15) | YES | | NULL | | | Tel04 | varchar(15) | YES | | NULL | | | Category | varchar(20) | YES | | NULL | | | Workdate | timestamp| YES | | -00-00 00:00:00 | | | Notes | varchar(255) | YES | | NULL | | ++--+--+-+-+ + 15 rows in set (0.01 sec) You have Createdate and Workdate swapped. The first timestamp gets updated on every update to the record. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ERROR 1025 when doing ALTER TABLE to change a myisam-table to a CLUSTER table?
Hello, I am just doing my first testing on a mysql-cluster system. Curently, I habe 1 management node running and 2 Data-Nodes that also run a mysqld each. The servers are Dual-Opterons with 6GB of RAM each. I did a dump of a database of one of our production systems (about 1.5GB mysqldump-file) and piped that into the first of the new servers. I then startet doing alter table abc type=ndb-queries and everything looked fine at the beginning. After having moved 70-80% of the tables into the NDB-Engine (they all show up correctly on the other mysql-server and everything seems to work) I suddenly got the following error: ERROR 1025 (HY000): Error on rename of './master/#sql-e80_1' to './master/status_system_hist' (errno: 708) I could not find any information on how to fix this and what the reason could be. When I manually create a new table with the same definition in the NDB-Engine and then do a insert into .. select from... I have no trouble. I should not be hitting the memory-limit yet, ndbd only uses 56% of the RAM so far. I attached some SQL-Output, part of the config.ini and the top-output. Can anybody help me with this? thanks Jan [NDBD DEFAULT] NoOfReplicas=2 DataMemory=4200M IndexMemory=1000M NoOfFragmentLogFiles=100 MaxNoOfConcurrentOperations=50 mysql show table status; +-++-++-++-+-+--+---++-+-+-+---+--++---+ | Name| Engine | Version | Row_format | Rows| Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | +-++-++-++-+-+--+---++-+-+-+---+--++---+ [...] | status_system_hist | MyISAM | 10 | Dynamic| 270413 | 91 |24721832 | 281474976710655 | 4409344 | 0 | NULL | 2006-01-27 15:03:48 | 2006-01-27 15:04:12 | NULL| latin1_swedish_ci | NULL || | [...] mysql alter table status_system_hist type=ndb; ERROR 1025 (HY000): Error on rename of './master/#sql-e80_1' to './master/status_system_hist' (errno: 708) mysql show create table status_system_hist; ++--- + | Table | Create Table
Alter table
one of my table columns is unique. i mean cant av duplicate contents. How do i remove this?
RE: Alter table
ALTER TABLE db.table DROP INDEX nameOfTheIndex; If you don't know nameOfTheIndex use: Show create table db.table Regards! -Mensaje original- De: Yemi Obembe [mailto:[EMAIL PROTECTED] Enviado el: Lunes, 02 de Enero de 2006 10:03 a.m. Para: mysql@lists.mysql.com Asunto: Alter table one of my table columns is unique. i mean cant av duplicate contents. How do i remove this? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ALTER TABLE on InnoDB -- behind the scenes?
Confused about how ALTER TABLE foo ADD col2... operates. On an otherwise idle server, iostat 1 shows the disk being written to at a rate of 15MB/sec (its capacity is about 50MB/sec), but the ibdata-autoextend file only grows at a rate of 500kb/sec. 12GB table (w/ 1.5GB index) took 6 hours to complete, which suggests the actual table rewriting did happen at 500kB/sec. Something wrong here? What else was going on? MySQL 4.1.10a RHEL4.1
Re: ALTER TABLE - how to fix truncated data?
Gleb, As a sidenote - shouldn't MySQL raise an error when data gets truncated? MySQL raises a warning after such ALTER operation. See: mysql create table dt(a decimal(4,1)); Query OK, 0 rows affected (0.01 sec) mysql insert into dt set a=999.1; Query OK, 1 row affected (0.00 sec) mysql alter table dt change a a decimal(2,1); Query OK, 1 row affected, 1 warning (0.02 sec) Records: 1 Duplicates: 0 Warnings: 1 mysql show warnings; +-+--+-+ | Level | Code | Message | +-+--+-+ | Warning | 1264 | Out of range value adjusted for column 'a' at row 1 | +-+--+-+ Could be me ... but isn't this a little too late? eg: AFTER you have lost your data? IMO, it should raise an error UNLESS you force it to truncate the data. With regards, Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, Oracle MS SQL Server Upscene Productions http://www.upscene.com Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ALTER TABLE - how to fix truncated data?
In article [EMAIL PROTECTED], Martijn Tonies [EMAIL PROTECTED] writes: mysql insert into dt set a=999.1; Query OK, 1 row affected (0.00 sec) mysql alter table dt change a a decimal(2,1); Query OK, 1 row affected, 1 warning (0.02 sec) Records: 1 Duplicates: 0 Warnings: 1 mysql show warnings; +-+--+-+ | Level | Code | Message | +-+--+-+ | Warning | 1264 | Out of range value adjusted for column 'a' at row 1 | +-+--+-+ Could be me ... but isn't this a little too late? eg: AFTER you have lost your data? IMO, it should raise an error UNLESS you force it to truncate the data. This would contradict the MySQL design philosophy (others call it simply gotcha) that the user should know what he's doing and the DBMS tries its best to obey. Consider this (version 4.1.14): CREATE TEMPORARY TABLE t1 ( i TINYINT ); INSERT INTO t1 VALUES (42); SELECT * FROM t1; -- Ok, shows 42 UPDATE t1 SET i = 4242; -- SHOW WARNINGS; SELECT * FROM t1; -- Oops, shows 127 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ALTER TABLE - how to fix truncated data?
mysql insert into dt set a=999.1; Query OK, 1 row affected (0.00 sec) mysql alter table dt change a a decimal(2,1); Query OK, 1 row affected, 1 warning (0.02 sec) Records: 1 Duplicates: 0 Warnings: 1 mysql show warnings; +-+--+-+ | Level | Code | Message | +-+--+-+ | Warning | 1264 | Out of range value adjusted for column 'a' at row 1 | +-+--+-+ Could be me ... but isn't this a little too late? eg: AFTER you have lost your data? IMO, it should raise an error UNLESS you force it to truncate the data. This would contradict the MySQL design philosophy (others call it simply gotcha) that the user should know what he's doing and the DBMS tries its best to obey. Consider this (version 4.1.14): Yeah yeah ... so the MySQL design philosophy is that users never make mistakes... Guess they want to sell support contracts, ey? Seriously, if you give a user enough rope to hang themselves AND hand them a chair to stand on, better make sure you have a way to deal with the corpses. CREATE TEMPORARY TABLE t1 ( i TINYINT ); INSERT INTO t1 VALUES (42); SELECT * FROM t1; -- Ok, shows 42 UPDATE t1 SET i = 4242; -- SHOW WARNINGS; SELECT * FROM t1; -- Oops, shows 127 Yeah, total rubbish. Do this in your application - by accident - and explain your boss that the totals are wrong or that he had an input value of 4242 and got 127 in return. Rubbish. If a value doesn't fit (in the domain of tinyint), an exception should be raised. Plain and simple. With regards, Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, Oracle MS SQL Server Upscene Productions http://www.upscene.com Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ALTER TABLE - how to fix truncated data?
Martijn Tonies wrote: | Warning | 1264 | Out of range value adjusted for column 'a' at row 1 Could be me ... but isn't this a little too late? eg: AFTER you have lost your data? IMO, it should raise an error UNLESS you force it to truncate the data. This would contradict the MySQL design philosophy (others call it simply gotcha) that the user should know what he's doing and the DBMS tries its best to obey. Consider this (version 4.1.14): Yeah yeah ... so the MySQL design philosophy is that users never make mistakes... Even in more serious cases MySQL silently modifies data and structures: A large database with an INTEGER column with NULL-'values' allowed was modified to include this field in the PRIMARY key. The column definition was automagically modified to NOT NULL and all NULL-'values' where converted to 0 (zero). Yeah, emmm, well, we actually used the NULLs as no value (like it is supposed to be used AFAIK) and there was no way anymore to distinguish between NULL and 0. Luckily this was done on a test database and we only had to spend half an hour or so to restore the table from a backup. It would have been very nice to know of this action before it was completed, to say the least. If a value doesn't fit (in the domain of tinyint), an exception should be raised. Plain and simple. I fully agree. Maybe an option SQL_IGNORE_WARNINGS or something along those lines should be introduced to force the execution of such queries. At least most users will be prevented from shooting themselves in the foot unless they specificly specify to do so. Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ALTER TABLE - how to fix truncated data?
As a sidenote - shouldn't MySQL raise an error when data gets truncated? MySQL raises a warning after such ALTER operation. See: mysql create table dt(a decimal(4,1)); Query OK, 0 rows affected (0.01 sec) mysql insert into dt set a=999.1; Query OK, 1 row affected (0.00 sec) mysql alter table dt change a a decimal(2,1); Query OK, 1 row affected, 1 warning (0.02 sec) Records: 1 Duplicates: 0 Warnings: 1 mysql show warnings; +-+--+-+ | Level | Code | Message | +-+--+-+ | Warning | 1264 | Out of range value adjusted for column 'a' at row 1 | +-+--+-+ btw, it seems MySQL CAN do errors, check: http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html Jigal notified me of this. With regards, Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, Oracle MS SQL Server Upscene Productions http://www.upscene.com Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ALTER TABLE - how to fix truncated data?
Hello. If a value doesn't fit (in the domain of tinyint), an exception should be raised. Plain and simple. MySQL 5.0 has this ability. Check STRICT_ALL_TABLES and STRICT_TRANS_TABLES SQL modes at: http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html CREATE TEMPORARY TABLE t1 ( i TINYINT ); INSERT INTO t1 VALUES (42); SELECT * FROM t1; -- Ok, shows 42 UPDATE t1 SET i = 4242; -- SHOW WARNINGS; SELECT * FROM t1; -- Oops, shows 127 Yeah, total rubbish. Do this in your application - by accident - and explain your boss that the totals are wrong or that he had an input value of 4242 and got 127 in return. Rubbish. If a value doesn't fit (in the domain of tinyint), an exception should be raised. Plain and simple. With regards, Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, Oracle MS SQL Server Upscene Productions http://www.upscene.com Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ALTER TABLE - how to fix truncated data?
Hello. I don't think that it is possible to recover the data from the new table, because ALTER operation creates a new table and fills it with data (and truncates the data). But why did the system backup not help you? Could you describe the situation more in detail. If you haven't flushed you binary logs, you may want to make an attempt to recover the data from them. Jim Seymour wrote: My bad. I was renaming some columns in a table. I incorrectly set the type to decimal(4,2) and the data was truncated/hosed. Is there a way to recover the data. I tried a system backup from yesterday. That changed nothing. I have already set the column type back to the correct settings. I am running MySQL v5.0.13 on a Debian Etch(Testing) box. TIA, Jim Seymour -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ALTER TABLE - how to fix truncated data?
Gleb, I don't think that it is possible to recover the data from the new table, because ALTER operation creates a new table and fills it with data (and truncates the data). But why did the system backup not help you? Could you describe the situation more in detail. If you haven't flushed you binary logs, you may want to make an attempt to recover the data from them. As a sidenote - shouldn't MySQL raise an error when data gets truncated? If the truncation happens silently, chances are you'll never find out until someone does a report or whatever?! With regards, Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, Oracle MS SQL Server Upscene Productions http://www.upscene.com Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ALTER TABLE - how to fix truncated data?
Hello. As a sidenote - shouldn't MySQL raise an error when data gets truncated? MySQL raises a warning after such ALTER operation. See: mysql create table dt(a decimal(4,1)); Query OK, 0 rows affected (0.01 sec) mysql insert into dt set a=999.1; Query OK, 1 row affected (0.00 sec) mysql alter table dt change a a decimal(2,1); Query OK, 1 row affected, 1 warning (0.02 sec) Records: 1 Duplicates: 0 Warnings: 1 mysql show warnings; +-+--+-+ | Level | Code | Message | +-+--+-+ | Warning | 1264 | Out of range value adjusted for column 'a' at row 1 | +-+--+-+ mysql select version(); +-+ | version() | +-+ | 5.0.13-rc-debug-log | +-+ Martijn Tonies wrote: As a sidenote - shouldn't MySQL raise an error when data gets truncated? If the truncation happens silently, chances are you'll never find out until someone does a report or whatever?! With regards, Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, Oracle MS SQL Server Upscene Productions http://www.upscene.com Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ALTER TABLE - how to fix truncated data?
My bad. I was renaming some columns in a table. I incorrectly set the type to decimal(4,2) and the data was truncated/hosed. Is there a way to recover the data. I tried a system backup from yesterday. That changed nothing. I have already set the column type back to the correct settings. I am running MySQL v5.0.13 on a Debian Etch(Testing) box. TIA, Jim Seymour -- I started using something better than the standard back when IBM advertised OS/2 Warp on TV. As Linux matured I made the transition from OS/2 v4 to Linux. You don't have to accept less than you deserve. Use the Power of the Penguin Registered Linux user #316735 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
