Introduction

This document describes the different problems which may occur during the upgrade.

What’s new in MySQL 5.0 ?

The main new features are listed as below:

• Extension of the varchar limit from 255 to 65,535
• Support of triggers
• Support of procedures
• Introduction of the information_schema database: it contains information about tables, columns of a table, etc. (similar to the dba_xxx tables in Oracle)
• Support of views
• Performance improvments.

More details can be found here : http://dev.mysql.com/doc/refman/5.0/en/mysql-5-0-nutshell.html

Getting and installing the RPMs

It is strongly advised to make a backup of the MySQL DB before the upgrade. Stop all the services which interconnect with MySQL (such as dpm, dpnsdaemon, etc.) The sources of the RPMs are located here (I took the latest stable version): http://dev.mysql.com/downloads/mysql/5.0.html#linux-rhel3-x86-32bit-rpms I have installed the 4 following RPMs (using rpm –Uvh)

• MySQL-shared-community-5.0.37-0.rhel3
• MySQL-client-community-5.0.37-0.rhel3
• MySQL-server-community-5.0.37-0.rhel3
• MySQL-devel-community-5.0.37-0.rhel3

Upgrading MySQL server

After installing the new RPMs, the MySQL DB schema must be upgraded. Normally you should run the mysql_fix_privilege_tables (located under /usr/bin) command. It connects to the DB as root and by default it assumes there is no password. You need to edit the script.

One thing really important, if in your previous version of MySQL you set innodb_thread_concurrency over 500 (to have unlimited), you need to set it to 0 otherwise your performance might decrease.

Tests carried out

• I have upgraded the DPM from 1.5.10 to 1.6.3 using yaim. (/opt/glite/yaim/bin/yaim -c -s site-info.def –n glite-SE_dpm_mysql). It may happen that the update fails and you get can’t connect to MySQL through socket . See section 6.4
• I did a dpm-qryconf both from the DPM and lxb0728, dpns-getacl, dpns-ls, lcg-rep from lxb0728

I successfully tested a perl script (UpdateDomainName) from lxplus (slc3) and lxb0728. Perl-DBI-1.32-9 and Perl-5.0.8-94.EL3 are installed on lxb0728. Depending the version of the mysqlclient, you may get the error 1251 (Client does not support authentication protocol requested by server; consider upgrading MySQL). See section 6.3 to see how to solve it.

• I also successfully tested the C API via the C version of UpdateDomainName.

Stress tests based on the rfcp using 80 threads. It was successful when copying from a local file to the DPM, but unsuccessful if I locally copy files from the DPM. A few of them are copied. Normally I should have 80 files under lana_file. This test works perfectly if I use another DPM (same version 1.6.3 but older MySQL version). However I believe that is not related to MySQL… At the end of the application, I have this error: dpm_getstatus_getreq: Failed for all SURLs /dpm/cern.ch/home/dteam/generated/2007-04-12/file_lana_22_90 : No such file or directory

[labadie@lxb0728 stress_tests]$ cd ..
[labadie@lxb0728 ~/dpm_replicate]$ cd lana_file
[labadie@lxb0728 lana_file]$ ls
lana_file_05  lana_file_12  lana_file_21  lana_file_26  lana_file_32
lana_file_07  lana_file_16  lana_file_24  lana_file_28  lana_file_33
lana_file_08  lana_file_20  lana_file_25  lana_file_31
[labadie@lxb0728 lana_file]$ dpns-ls /dpm/cern.ch/home/dteam/generated/2007-04-12/file_lana_22_90

In the DPM log:

04/12 17:12:33  8423,22 dpm_serv: Could not establish security context: _Csec_recv_token: Connection dropped by remote end !
04/12 17:12:33  8423,22 sendrep: DP002 - send error : System error
04/12 17:12:33  8423,23 dpm_serv: Could not establish security context: _Csec_recv_token: Connection dropped by remote end !
04/12 17:12:33  8423,23 sendrep: DP002 - send error : System error
04/12 17:12:37  8423,24 dpm_serv: Could not establish security context: Csec_server_set_service_name: Could not set service name ! !
04/12 17:12:37  8423,24 sendrep: DP002 - send error : Broken pipe
04/12 17:14:27  8423,22 dpm_srv_getpools: DP092 - getpools request by /C=CH/O=CERN/OU=GRID/CN=host/lxb1532.cern.ch (0,0) from lxb1532.cern.ch
04/12 17:14:27  8423,22 dpm_srv_getpools: returns 0
04/12 17:14:27  8423,22 dpm_srv_getpoolfs: DP092 - getpoolfs request by /C=CH/O=CERN/OU=GRID/CN=host/lxb1532.cern.ch (0,0) from lxb1532.cern.ch
04/12 17:14:27  8423,22 dpm_srv_getpoolfs: returns 0

In the DPNS log

/OU=Users/CN=labadie/CN=622155/CN=Lana Abadie (19247,2688) from lxb1532.cern.ch
04/12 17:12:32  8326,0 Cns_srv_setptime: NS098 - setptime lxb1532.cern.ch:/storage/dteam/2007-04-12/file_lana_46_90.253764.0 1176397952
04/12 17:12:32  8326,0 Cns_srv_setptime: returns 0
04/12 17:12:49  8326,9 Cns_serv: Could not establish security context: Csec_server_negociate_protocol: Could not read protocol token !
04/12 17:12:49  8326,19 Cns_serv: Could not establish security context: Csec_server_negociate_protocol: Could not read protocol token !
04/12 17:12:49  8326,4 Cns_serv: Could not establish security context: Csec_server_negociate_protocol: Could not read protocol token !
04/12 17:14:33  8326,0 Cns_srv_lstat: NS092 - lstat request by /DC=ch/DC=cern/OU=Organic Units/OU=Users/CN=labadie/CN=622155/CN=Lana Abadie (19247,2688) from lxb0728.cern.ch
04/12 17:14:33  8326,0 Cns_srv_lstat: NS098 - lstat 0 /dpm/cern.ch/home/dteam/generated/2007-04-12/file_lana_22_90
04/12 17:14:33  8326,0 Cns_srv_lstat: returns 0

In the rfio log

Apr 12 17:12:30 rfiod[14119]: rclose64_v3(1, 2): 5 bytes read and 0 bytes written
Apr 12 17:12:30 rfiod[7386]: Waiting for end of child 14119, status 0
Apr 12 17:12:31 rfiod[14101]: request by /DC=ch/DC=cern/OU=Organic Units/OU=Users/CN=labadie/CN=622155/CN=Lana Abadie (18118,2690)
Apr 12 17:12:31 rfiod[14101]: doit(1): connection from lxb0728.cern.ch
Apr 12 17:12:31 rfiod[14101]: ropen64_v3: (/storage/dteam/2007-04-12/file_lana_37_90.253802.0,00,0644) for (18118,2690)
Apr 12 17:12:31 rfiod[14099]: srread64_v3: read ctrl socket 1: read(): Connection reset by peer
Apr 12 17:12:31 rfiod[14099]: readerror64_v3(1): 1 read, 3 readahead, 0 write, 0 flush, 0 stat, 0 lseek and 0 lockf
Apr 12 17:12:31 rfiod[14099]: readerror64_v3(1): 859 bytes read and 0 bytes written
Apr 12 17:12:31 rfiod[7386]: Waiting for end of child 14099, status 0
Apr 12 17:12:31 rfiod[14126]: Could not establish context: _Csec_recv_token: Connection dropped by remote end
Apr 12 17:12:31 rfiod[7386]: Waiting for end of child 14126, status 1
Apr 12 17:12:31 rfiod[14101]: ropen64_v3: assigning data port 20523

However it works if I execute the script with at most 20 threads.

Troubleshooting

Can’t connect to mysql after installing the new rpms

In my case, I encounter the following problem is I could not connect to the DB as a root any more. So what I did is I reset the root password and execute the sql script which is called by the command mysql-fix-privilege-tables and I update the root password.

Mysqld –skip-grant-tables –user=root &
Mysql –u root;
Use mysql;
Source <path_to_file>/mysql-fix-privileges.sql;
SET PASSWORD FOR 'root'@'localhost' = PASSWORD ('new_pwd');

You can do the same for the other accounts if necessary.

How to upgrade the DBI and DBD::mysql ?

shell> perl -MCPAN -e shell
cpan> force install DBI
cpan> force install DBD::mysql

To check that everything is OK, type perl -MDBI -e "1;". It should return nothing. To check that the mysql driver is available, type perl -MDBI -we 'print join ",", DBI->available_drivers'. It should return at least mysql

Error 1251 (Client does not support authentication protocol requested by server)

Type the following commands.

mysql -u <login> -p 
>enter <password>
Mysql>Use mysql;
Mysql>SET PASSWORD FOR '<user_name>'@'localhost' = OLD_PASSWORD(&#8216;user_pwd_local'); -- for local connection
Mysql>SET PASSWORD FOR '<user_name>'@'%' = OLD_PASSWORD('user_password_remote'); -- for remote connections

Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock'

You have to check the following things:

• the MySQL server is up (ps aux |grep mysql)
• the sock which is indicated exists
• the output of netstat -ln | grep mysql corresponds to the socket. If not, you can make a symbolic link with the one provided by netstat … or specify the location of the socket in your /etc/my.cnf (socket= for both client and mysqld). You need to restart your MySQL server. The default socket location is socket=/tmp/mysql.sock
• set the environment variable TMPDIR

Can’t find ‘./mysql/host.frm’ (errno 13)

Check the permission of the /mysql location. It should be accessible by mysql (as a user and as a group). If not, type chown/chgrp –R mysql /mysql.

-- LanaAbadie - 04 May 2007