Please consider my updated code below for your next revision of Phoca Changing Collation Tool.
When I first ran this tool against my MySQL database, it seemed to complete successfully, but when I reviewed the database tables I found that 10 of the 32 database tables were not converted to the desired collation. After review, It appears to be a program related to the size of the output being generated because when I played around with the output I found that more tables were updated as the output was reduced.
I needed to re-run the code to fix the remaining 10 tables and didn't need to update tables and columns that had already been converted. So, I updated the index.php file to skip tables that already have the desired collation if none of that table's columns need to be converted. Additionally, this updated code skips over individual columns that already have the desired collation.
So, there is still a problem with the size of the output when this is run against my whole database at once, but this updated code allows me to run it more than once to convert the tables that didn't get converted the first time.
If you know what might be causing the output size limitation, it would be nice to learn how to fix that issue also. For now, this solution got me over my immediate hurdle.
Thanks for your consideration.
PS: I was going to attach the whole index.php file here, but I don't see anyway to attach a file in this post. So, I just pasted the updated lines of code below.
Code: Select all
//$result = mysqli_query($conn, "SHOW TABLES");
$get_tables_sql = "select table_name from information_schema.tables t where 1=1 and table_schema = lower('$mysqldatabase') and (table_collation != '$collation' or exists (select null from information_schema.columns c where c.table_schema = t.table_schema and c.table_name = t.table_name and c.collation_name != '$collation'))" ;
$result = mysqli_query($conn, $get_tables_sql);
while ($row = mysqli_fetch_row($result)) {
mysql_convert($conn, "ALTER TABLE $row[0] COLLATE $collation");
//$result1 = mysqli_query($conn, "SHOW COLUMNS FROM $row[0]");
$get_cols_sql = "select column_name from information_schema.columns where table_schema = lower('$mysqldatabase') and table_name = '+$row[0]+' and collation_name != '$collation'";
$result1 = mysqli_query($conn, $get_cols_sql);
while ($row1 = mysqli_fetch_assoc($result1)) {
if (preg_match('~char|text|enum|set~', $row1["Type"])) {
mysql_convert($conn, "ALTER TABLE $row[0] MODIFY $row1[Field] $row1[Type] CHARACTER SET binary");
mysql_convert($conn, "ALTER TABLE $row[0] MODIFY $row1[Field] $row1[Type] COLLATE $collation" . ($row1["Null"] ? "" : " NOT NULL") . ($row1["Default"] && $row1["Default"] != "NULL" ? " DEFAULT '$row1[Default]'" : ""));
}
}
}
