Mysql – “Alter Table” modify multiple columns vs multiple Alter table single column in INNODB

alter-tableinnodbMySQLmysql-5.7

I am updating collations of my database tables and columns from utf8bin to utf8mb4_bin.
Engine is INNODB with file per table enabled.

I used a query to generate identify all utf8 columns from information_schema database and generated corresponding queries for each.

The generated queries contains "ALTER TABLE" queries for each column separately.

Example:

ALTER TABLE mydb.`cwd_group`
MODIFY `active` char CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL;
ALTER TABLE mydb.`cwd_group`
MODIFY `local` char CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL;

Is it a recommened to join these two as a single statement? What will be the drawbacks?

ALTER TABLE mydb.`cwd_group`
    MODIFY `active` char CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL
   ,MODIFY `local` char CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL
;

I am guessing by joining them as single statement, disk IO is reduced as it not required to write the whole table data multiple times (i.e., to temporary ibd file #sql-*) or does it write only the column data to temporary file? Or how much extra space may it use for the temporary file if I merge the statements?

In the end, I want to run these alter tables in parallel that my server can handle.

Best Answer

You must do it all in a single ALTER TABLE command. Think of it : You are changing the collation of a column, so ALGORITHM=COPY must be done. This requires a full table lock. It is better to execute the collation change of multiple columns in a single ALTER TABLE command than to do it multiple time

There is no drawback in this instance. Why ? Back in May 12, 2011, I wrote about MySQL 5.0's old behavior in executing multiple column modifications (Does MySQL still handle indexes in this way?). Under the hood, MySQL used to execute multiple column changes as individual ALTER TABLE commands. I pointed this out to MySQL back on Oct 10, 2006. They had fixed it since.

Now that ALTER TABLE handles multiple column changes as a single operation, there is no need to change the columns one by one.

Let's say it takes 5 min to change one column. That's 5 minutes the table is locked and no INSERTs, UPDATEs, or DELETEs can happen. You would basically double the time changing two columns to 10 min, triple the time changing three columns to 15 min, and so forth.

Just run the one ALTER TABLE command. MySQL handles it correctly now.

ALTER TABLE mydb.`cwd_group`
    MODIFY `active` char(25) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL
   ,MODIFY `local`  char(25) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL
;

GIVE IT A TRY !!!

P.S. You need to specify a length for char. That's why I put (25).

If you are not sure of the length, then use VARCHAR(25).

I have very bad news for you.

You should not have deleted the ibdata1 file. Here is why:

ibdata1 contains four type of information:

table metadata
MVCC data
data pages (with innodb_file_per_table enabled)
index pages (with innodb_file_per_table enabled)

Each InnoDB table created has a numercial id assigned to it via some auto increment metadata feature to each ibd file. That internal tablespace id (ITSID) is embedded in the .ibd file. That number is checked against the list of ITSIDs maintained, guess where, ... ibdata1.

I also have very good news for you along with some bad news.

It is possible to reconstruct ibdata1 to have the correct ITSIDs but it takes work to do it. While I personally have not done procedure alone, I assisted a client at my employer's web hosting to do this. We figured this out together but since the client hosed ibdata1, I let him do most of the work (30 InnoDB tables).

Anyway, here a past post I made in the DBA StackExchange. I answered another question whose root cause was the mixing up of ITSIDs.

To cut right to the chase, here is the article explaining what to do with reference to ITSID and how to massage ibdata1 into acknowledging the presence of the ITSID contained within the .ibd file.

I am sorry there is no quick-and-dirty method for recovering the .ibd file other than playing games with ITSIDs.

UPDATE 2011-10-17 06:19 EDT

Here is your original innodb configuration from your question:

innodb_file_per_table=1
innodb_flush_method=O_DIRECT
innodb_log_file_size=1G
innodb_buffer_pool_size=4G
innodb_data_file_path=ibdata1:10M:autoextend
innodb_buffer_pool_size = 384M
innodb_log_file_size=5M
innodb_lock_wait_timeout = 18000

Please notice that innodb_log_file_size is there twice. Look carefully...

innodb_file_per_table=1
innodb_flush_method=O_DIRECT
innodb_log_file_size=1G <----
innodb_buffer_pool_size=4G
innodb_data_file_path=ibdata1:10M:autoextend
innodb_buffer_pool_size = 384M
innodb_log_file_size=5M <----
innodb_lock_wait_timeout = 18000

The last setting of innodb_log_file_size takes precedence. MySQL expected to start up with the log files being 5M. Your ib_logfile0 and ib_logfile1 were 1G when you tried to start up mysqld. It saw a size conflict and took the path of least resistance, which was to disable InnoDB. That's why InnoDB was missing from show engines;. Mystery solved !!!

UPDATE 2011-10-17 11:07 EDT

The error message was deceptive because innodb_log_file_size was smaller than the log files (ib_logfile0 and ib_logfile1), which were 1G at the time. What's interesting is this: Corruption was reported because the file was expected to be 5M and the files were bigger. If the situation were reversed and the innodb log files were smaller than the declared size in my.cnf you should get something like this in the error log:

110216 9:48:41 InnoDB: Initializing buffer pool, size = 128.0M
110216 9:48:41 InnoDB: Completed initialization of buffer pool
InnoDB: Error: log file ./ib_logfile0 is of different size 0 5242880 bytes
InnoDB: than specified in the .cnf file 0 33554432 bytes!
110216 9:48:41 [ERROR] Plugin 'InnoDB' init function returned error.
110216 9:48:41 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.

In this example, the log files were already existing as 5M and the setting for innodb_log_file_size was bigger (in this case, 32M).

For this particular question, I blame MySQL (eh Oracle [still hate saying it]) for the inconsistent error message protocol.

Mysql – Issue with connecting to the thesql database

Based on your old_passwords setting, I would ask you to look at three things

What password functions return
What passwords are stored in mysql.user
What authentication style is the client launching

PASSWORD FUNCTIONS

Please run the following:

show variables like 'old_passwords';
select password('rolando'),old_password('rolando');

Depending on where old_passwords is enabled or not, you should get either the following:

MySQL 5.0 with old_passwords off

mysql> show variables like 'old_passwords';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| old_passwords | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

mysql> select password('rolando'),old_password('rolando');
+-------------------------------------------+-------------------------+
| password('rolando')                       | old_password('rolando') |
+-------------------------------------------+-------------------------+
| *C1868CE781FCB521A03168DE044BF3F64E9E485A | 05e97e95241a4409        |
+-------------------------------------------+-------------------------+
1 row in set (0.00 sec)

mysql>

MySQL 5.1 with old_passwords on

mysql> show variables like 'old_passwords';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| old_passwords | ON    |
+---------------+-------+
1 row in set (0.00 sec)

mysql> select password('rolando'),old_password('rolando');
+---------------------+-------------------------+
| password('rolando') | old_password('rolando') |
+---------------------+-------------------------+
| 05e97e95241a4409    | 05e97e95241a4409        |
+---------------------+-------------------------+
1 row in set (0.00 sec)

mysql>

MySQL 5.5 with old_passwords off

mysql> show variables like 'old_passwords';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| old_passwords | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

mysql> select password('rolando'),old_password('rolando');
+-------------------------------------------+-------------------------+
| password('rolando')                       | old_password('rolando') |
+-------------------------------------------+-------------------------+
| *C1868CE781FCB521A03168DE044BF3F64E9E485A | 05e97e95241a4409        |
+-------------------------------------------+-------------------------+
1 row in set (0.00 sec)

mysql>

MySQL 5.5 with old_passwords on

mysql> show variables like 'old_passwords';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| old_passwords | ON    |
+---------------+-------+
1 row in set (0.00 sec)

mysql> select password('rolando'),old_password('rolando');
+---------------------+-------------------------+
| password('rolando') | old_password('rolando') |
+---------------------+-------------------------+
| 05e97e95241a4409    | 05e97e95241a4409        |
+---------------------+-------------------------+
1 row in set (0.00 sec)

mysql>

PASSWORDS STORED

Please run this query:

SELECT COUNT(1) password_length_count,password_length FROM
(SELECT LENGTH(password) password_length FROM mysql.user) A
GROUP BY password_length;

If you get any password_length of 16, you have to enable old_passwords for the old style authentication to work.

PASSWORDS FROM CLIENT

In some cases, using mysql libraries that came from old yum installs of MySQL, PHP, Perl or Apache may have pre-MySQL4.1 user authentication code. Please upgrade such libraries.