SQL Upgrade script for 1.8

[alj]

Is it really necessary to convert tables to unicode?
This procedure will take days on my database, and I'm not even sure why tables like history_*/trends_* are being converted, they don't have any character columns at all... and they are too big to convert.

[alj]

Modification to upgrade script to skip large tables which do not need conversion:

....
# ********************   2

echo "Converting database to UTF8"

timer start conversion to UTF8
#echo "ALTER DATABASE CHARACTER SET utf8;" | $MYSQL $MYSQLPARAMS
for i in $(echo "show tables;" | $MYSQL -N $MYSQLPARAMS); do
        if [[ "$i" =~ '^history' || "$i" =~ '^trends' ]] ; then
            echo "... skipping table $i"
        else
            echo "... converting table $i"
            echo "ALTER TABLE $i CONVERT TO CHARACTER SET utf8 COLLATE utf8_gene
ral_ci;" | $MYSQL $MYSQLPARAMS
        fi
done
echo -n " ... "
timer stop conversion to UTF8
....

[richlv]

1. that script is unsupported and only provided for convenience;
2. conversion is optional, but non-acii chars will result in problems with graphs, maps and probably other locations. additionally, there are reports about image uploading not working.

skipping some tables as you did should be all fine, but then db has tables with mixed encoding - being not consistent isn't the best thing as well...

[alixen]

Hi Rich,

You are right, but:
1. Is there any interest in converting purely numeric tables like history or history_uint ?

mysql> desc history;
+--------+---------------------+------+-----+---------+-------+
| Field  | Type                | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+-------+
| itemid | bigint(20) unsigned | NO   | MUL | 0       |       |
| clock  | int(11)             | NO   |     | 0       |       |
| value  | double(16,4)        | NO   |     | 0.0000  |       |
+--------+---------------------+------+-----+---------+-------+

mysql> desc history_uint;
+--------+---------------------+------+-----+---------+-------+
| Field  | Type                | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+-------+
| itemid | bigint(20) unsigned | NO   | MUL | 0       |       |
| clock  | int(11)             | NO   |     | 0       |       |
| value  | bigint(20) unsigned | NO   |     | 0       |       |
+--------+---------------------+------+-----+---------+-------+

It took a very long time to upgrade our database and most of the time was spent on converting history, history_uint, trends and trends_uint.

2. Would it be OK to use the script provided by alj if it was modified to skip tables that have only numeric fields ?

Regards,
Alixen

[richlv]

1. Is there any interest in converting purely numeric tables like history or history_uint ?

excluding the consistency approach - not that i know about, so skipping them should be pretty safe indeed