UTF8 and zabbix 1.8 upgrade

[ataylo13]

I have seen some posts about the length of time the UTF conversion takes. I am hesitant to make the update in my distributed environment due to not knowing how long this upgrade could take. I have seen posts of a 20 hour conversion on a 3G database. My zabbix DB is ~110G so would this take days? If so there has to be a better (faster) way. Any help/insight here would be greatly appreciated.

[Alexei]

You may skip historical tables having no text data: history, history_uint, trends, trends_uint. This would make transition much faster.

[ataylo13]

Thanks for the tip. I will give that a try.

[ataylo13]

Alexei, I noticed the upgrade script mentions it is not inteded for distributed monitoring databases... what does that mean? Should I not run it on the script on the master and child nodes?

[richlv]

1. if you do not need other scripts than the basic latin one, you can skip utf-8 conversion completely;

2. the bash script is not intended and tested for distributed setup databases. it will most likely miss some required changes.
the sql patches, official upgrade path, should support such databases, though. any problems with that should be reported (hint - test upgrade)

[ataylo13]

thanks for the quick reply richlv. I was assuming that I did not need it, but I was unsure what if anything internal to zabbix relied on it.

[Samd]

upgrade script and UTF

Perhaps something like this will help for speeding up the UTF conversion, based on the suggestions in this thread. I will try tomorrow if the conversion takes too long.

"upgrade" script



=====================
Replace this section:
=====================


# ******************** 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
echo "... converting table $i"
echo "ALTER TABLE $i CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;" | $MYSQL $MYSQLPARAMS
done
echo -n " ... "
timer stop conversion to UTF8

==========
With this:
==========


# ******************** 2

excluded=( history history_uint trends trends_uint )

function elementExists()
{

if [ -z "$1" ]
then
return
fi

for i in ${excluded[@]}
do
if [ $i == $1 ]
then
return 1
fi
done

return 0
}


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
elementExists $i
x=$?
if [ $x == "0" ]
then
echo "... converting table $i"
echo "ALTER TABLE $i CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;" | $MYSQL $MYSQLPARAMS
fi
done
echo -n " ... "
timer stop conversion to UTF8

[richlv]

oh, well... a shorter (i'm lazy ) version of skipping history & trends tables for that script was committed to svn. uncommenting single line should allow to leave all history & trends tables unconverted

[boy01]

oh, well... a shorter (i'm lazy ) version of skipping history & trends tables for that script was committed to svn. uncommenting single line should allow to leave all history & trends tables unconverted

Did you test it?

> # skiptables="history\|history_log\|history_str\|history_text\|history_uint\|trends\|trends_uint
>
104c107
< for i in $(echo "show tables;" | $MYSQL -N $MYSQLPARAMS); do
---
> for i in $(echo "show tables;" | $MYSQL -N $MYSQLPARAMS | grep -v "${skiptables:-not_skipping_any_tables}"); do

Maybe it does what _you_ want, but it surely isn't safe.
Including "history" skips also history_log, history_str,... (grep -v).
Or am I missing something here?
(There's missing "-char from end of skiptables-variable)

[richlv]

Did you test it?

that would be a lot of effort, eh ?
attempted to improve.
if you have the time, evaluation of the latest version would be appreciated, thanks

[boy01]

if you have the time, evaluation of the latest version would be appreciated, thanks

I upgraded my installation on CentOS 5.5 x86_64 w/ your script.
Worked ok, but maybe you should give better help for usage?
Fg:
./upgrade --user=xxxx --password=xxxxxxx zabbix

I don't know what will happen if db is missing from parameters.
Could the script do it's job to mysql db...?

[richlv]

Worked ok, but maybe you should give better help for usage?
Fg:
./upgrade --user=xxxx --password=xxxxxxx zabbix

well... that would be quite an encouragement to use it, given that it's not really supported...

I don't know what will happen if db is missing from parameters.
Could the script do it's job to mysql db...?

it should pass through index & utf8 parts not doing anything, then fail on the patch step

[boy01]

well... that would be quite an encouragement to use it, given that it's not really supported...

If you don't think one should use the script maybe you shouldn't
include the script in the zabbix tar file, but separate downloadable file...