Phoca Changing Collation Tool: updated index.php to skip tables/columns already updated

Phoca Tools: Phoca Server Unzip tool, Phoca Changing Collation tool, ...
rjlyders
Phoca Newbie
Phoca Newbie
Posts: 1
Joined: 23 Sep 2017, 23:50

Phoca Changing Collation Tool: updated index.php to skip tables/columns already updated

Post by rjlyders » 24 Sep 2017, 00:11

Thanks for the use of your Phoca Changing Collation Tool.

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]'" : ""));
                }
            }
        }

Tags:


User avatar
Jan
Phoca Hero
Phoca Hero
Posts: 40262
Joined: 10 Nov 2007, 18:23
Location: Czech Republic
Contact:

Re: Phoca Changing Collation Tool: updated index.php to skip tables/columns already updated

Post by Jan » 29 Sep 2017, 13:29

Hi, thank you for the modification. Output size limitation - hard to say if there is some limit or there is no memory for this :idea:

Jan
If you find Phoca extensions useful, please support the project

Post Reply