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: how to get the levels of a table or a index in Mysql 5.6?
On 08.07.2013 04:23, 李炜(平安科技数据库技术支持部) wrote: how to get the levels of a table or a index in Mysql 5.6? Level? What is level supposed to be in that context? Cardinality? Or something completely different? /me confused ... -- Hartmut Holzgraefe hart...@skysql.com Principal Support Engineer (EMEA) SkySQL AB - http://www.skysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Need query to determine different column definitions across tables
I'm noticing that across our several databases and hundreds of tables that column definitions are not consistent. I'm wondering if there is a tool or query (using INFORMATION_SCHEMA perhaps) that will show me all databases, tables and columns where they don't match (by column name). For example in one table `foo_id` might be UNSIGNED and in other's it is not. Or maybe it's INT(11) in some and INT(10) or worse MEDIUMINT in others. Or extending further Charset/Collation might mismatch and be that stupid latin1_swedish_ci and fixed to be utf8 in others. Stuff like that. I want to see everything where there is some difference.
RE: how to get the levels of a table or a index in Mysql 5.6?
Or maybe the number of levels in the BTree? Rule of Thumb: logarithm base 100 -Original Message- From: Hartmut Holzgraefe [mailto:hart...@skysql.com] Sent: Monday, July 08, 2013 6:38 AM To: mysql@lists.mysql.com Subject: Re: how to get the levels of a table or a index in Mysql 5.6? On 08.07.2013 04:23, 李炜(平安科技数据库技术支持部) wrote: how to get the levels of a table or a index in Mysql 5.6? Level? What is level supposed to be in that context? Cardinality? Or something completely different? /me confused ... -- Hartmut Holzgraefe hart...@skysql.com Principal Support Engineer (EMEA) SkySQL AB - http://www.skysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: Need query to determine different column definitions across tables
See if you like this: SELECT TABLE_SCHEMA as db, TABLE_NAME, COLUMN_NAME, CHARACTER_SET_NAME, COLUMN_TYPE FROM `COLUMNS` ORDER BY 3,4,5; You might be able to embellish on it to avoid consistent definitions, etc. -Original Message- From: Daevid Vincent [mailto:dae...@daevid.com] Sent: Monday, July 08, 2013 2:11 PM To: mysql@lists.mysql.com Subject: Need query to determine different column definitions across tables I'm noticing that across our several databases and hundreds of tables that column definitions are not consistent. I'm wondering if there is a tool or query (using INFORMATION_SCHEMA perhaps) that will show me all databases, tables and columns where they don't match (by column name). For example in one table `foo_id` might be UNSIGNED and in other's it is not. Or maybe it's INT(11) in some and INT(10) or worse MEDIUMINT in others. Or extending further Charset/Collation might mismatch and be that stupid latin1_swedish_ci and fixed to be utf8 in others. Stuff like that. I want to see everything where there is some difference. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
答复: how to get the levels of a table or a index in Mysql 5.6?
The level is the height of a B-tree table or a B-tree index in mysql 5.6. My question is how to get the height(blevel) of a B-tree table or a B-tree index in mysql 5.6? Thanks. In oracle database,we can use the following statement to query the blevel of a index select index_name,blevel from dba_indexes; -Original Message- RE: how to get the levels of a table or a index in Mysql 5.6? Or maybe the number of levels in the BTree? Rule of Thumb: logarithm base 100 -Original Message- From: Hartmut Holzgraefe [mailto:hart...@skysql.com] Sent: Monday, July 08, 2013 6:38 AM To: mysql@lists.mysql.com Subject: Re: how to get the levels of a table or a index in Mysql 5.6? On 08.07.2013 04:23, 李炜(平安科技数据库技术支持部) wrote: how to get the levels of a table or a index in Mysql 5.6? Level? What is level supposed to be in that context? Cardinality? Or something completely different? /me confused ... -- Hartmut Holzgraefe hart...@skysql.com Principal Support Engineer (EMEA) SkySQL AB - http://www.skysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql The information in this email is confidential and may be legally privileged. If you have received this email in error or are not the intended recipient, please immediately notify the sender and delete this message from your computer. Any use, distribution, or copying of this email other than by the intended recipient is strictly prohibited. All messages sent to and from us may be monitored to ensure compliance with internal policies and to protect our business. Emails are not secure and cannot be guaranteed to be error free as they can be intercepted, amended, lost or destroyed, or contain viruses. Anyone who communicates with us by email is taken to accept these risks. 收发邮件者请注意： 本邮件含保密信息，若误收本邮件，请务必通知发送人并直接删去，不得使用、传播或复制本邮件。 进出邮件均受到本公司合规监控。邮件可能发生被截留、被修改、丢失、被破坏或包含计算机病毒等不安全情况。
RE: Need query to determine different column definitions across tables
-Original Message- From: Daevid Vincent [mailto:dae...@daevid.com] Sent: Monday, July 08, 2013 2:11 PM To: mysql@lists.mysql.com Subject: Need query to determine different column definitions across tables I'm noticing that across our several databases and hundreds of tables that column definitions are not consistent. I'm wondering if there is a tool or query (using INFORMATION_SCHEMA perhaps) that will show me all databases, tables and columns where they don't match (by column name). For example in one table `foo_id` might be UNSIGNED and in other's it is not. Or maybe it's INT(11) in some and INT(10) or worse MEDIUMINT in others. Or extending further Charset/Collation might mismatch and be that stupid latin1_swedish_ci and fixed to be utf8 in others. Stuff like that. I want to see everything where there is some difference. Well, here's the query I'm using currently. Will post updates as I tweak it. USE `information_schema`; SELECT t1.`COLUMN_NAME`, t1.`TABLE_NAME`, t1.`COLUMN_TYPE`, -- CONCAT(t1.`TABLE_NAME`,'.',t1.`COLUMN_TYPE`) as t1_type, t2.`TABLE_NAME`, t2.`COLUMN_TYPE` -- CONCAT(t2.`TABLE_NAME`,'.',t2.`COLUMN_TYPE`) AS t2_type FROM `COLUMNS` AS t1 LEFT JOIN `COLUMNS` AS t2 ON t1.`COLUMN_NAME` = t2.`COLUMN_NAME` AND t1.`COLUMN_TYPE` t2.`COLUMN_TYPE` WHERE t1.`TABLE_SCHEMA` = 'mydatabase' AND t2.`TABLE_NAME` IS NOT NULL -- HAVING t2_type IS NOT NULL ORDER BY `COLUMN_NAME` ASC; Having separate columns there is easier to read/compare than CONCAT() I think. Another bulk version that comes in handy: SELECT `COLUMN_NAME`, `COLUMN_TYPE`, `TABLE_SCHEMA`, `TABLE_NAME`, `CHARACTER_SET_NAME`, `COLLATION_NAME` FROM `COLUMNS` WHERE `TABLE_SCHEMA` = ' mydatabase ' ORDER BY `COLUMN_NAME`; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
