From: "Massimo S." <ecs-isp@2rosenthals.com>
Reply-To: ml@ecomstation.it
Subject: Re: [eCS-ISP] mysql upgrade 5.1.73 -> 5.6.51 (for Steven)
To: eCS ISP Mailing List <ecs-isp@2rosenthals.com>
References: <list-7630187@2rosenthals.com> <list-11332776@2rosenthals.com>
Organization: Massimo S.
Message-ID: <9b6e8ff4-5727-9406-2432-9a4d16a7efb8@ecomstation.it>
Date: Tue, 10 Dec 2024 15:17:13 +0100
User-Agent: Mozilla/5.0 (OS/2; U; Warp 4.5; it-IT; rv:1.7.13) Gecko/20060424
 Thunderbird/1.0.8 Mnenhy/0.7.4.0
MIME-Version: 1.0
In-Reply-To: <list-11332776@2rosenthals.com>
Content-Type: text/plain; charset=iso-8859-15; format=flowed
Content-Language: it-IT
Content-Transfer-Encoding: 8bit



Il 10/12/2024 14:12, Massimo S. ha scritto:
> Il 10/08/2023 00:19, Steven Levine ha scritto:
>> In <list-7611426@2rosenthals.com>, on 08/09/23
>>     at 12:09 PM, "Massimo S." <ecs-isp@2rosenthals.com> said:
>>
>> Hi all,
>>
>> Here's the mysql upgrade procedure that worked for me
>>
>> ==============================
>> == mysql_upgrade 5.1 -> 5.6 ==
>> ==============================
>>
>>   - Tested with mysqld
>>       Ver 5.1.73 for pc-os2-emx on i386 (Source distribution)
>>     and
>>       Ver 5.6.51 for OS2 on i386 (Source distribution)
>>
>>   - The following assumes
>>       5.6 datadir will be /data/mysql56
>>       5.1 datadir is /data/mysql51
>>       innodb_data_home_dir is /data/mysql56-data/innodb
>>   - Use names that match your setup
> 
> hi Steven,
> 
> i first need to try 5.6.51 and i will point some websites and webmail to the new
> mysql installation, if all runs correctly i will start with this new setup
> 
> i will not upgrade mysql on the same VM/directories
> 
> i'm going to copy all the stuff from the mysql running in production to the other mysql 5.6.51 on another VM
> 
>>   - Open session with current directory set to 5.6 bin directory
>>     Run binaries from the directory
>>
>>   - Verify my.cnf is 5.6 compatible
> 
> yes, i dit
> 
>>     Edit as needed to protect/hide 5.1 data
> 
> i don't need this since mysql 5.1 is on another vm
> 
>>
>>   - Create /data/mysql56
>>   - Create /data/mysql56/mysql
>>   - Create /data/mysql56/innodb
>>   - Copy /data/mysql51/mysql to /data/mysql56/mysql
>>   - Run
>>       mysqld --console
>>     The server should start
>>     There will be warnings
> 
> done, yes, i've warnings :)
> 
>>   - Run
>>       mysql_upgrade
> 
> 
> a couple of crash, but after it completed with OK
> 
>>     The server may crash
>>   - If the server crashes, run
>>       mysqld --console
>>     to restart the server
>>     There will be warnings
>>     Run
>>       mysql_upgrade
>>     to retry the upgrade
>>     The upgrade should run without errors
>>     if not get help
>>
>>   - Copy the rest of the 5.1 databases to /data/mysql56
>>   - Run
>>       mysql_upgrade --force
>>     to upgrade these databases
>>     The upgrade should run without errors
>>     If not get help
>>
>>   - Run
>>       mysqlcheck --all-databases
>>     to cross-check upgrade results
>>
>>   - Check /data/mysql56/mysql_upgrade_info
>>     It should contain 5.6.51
>>
>> The logs imply that, at least in my case, only the mysql database needed
>> unusual modifications.  The log output for all the other databases and
>> tables reported OK with no additional messages or warnings.
>>
>> Steven
> 
> here i've issue, eg. with roundcube webmail
> 
> 2024-12-10 13:49:15 5311 [Warning] InnoDB: Cannot open table rcube2/session from
>   the internal data dictionary of InnoDB though the .frm file for the table exist
> s. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html for ho
> w you can resolve the problem.
> 
> i don't understand if i've to copy also innodb stuff from 5.1 vm to 5.6 vm
> or if i have to let mysql 5.6 recreate it
> 
> please help me ;)
> 
> i need to upgrade to 5.6, 5.1.73 is too much old and i've issues
> importing new websites or managing DB with phpmyadmin
> 
> massimo

that's really strange i've seen that the 2 table spaces used by the 2 webmails
are like untouched since years, webmails are working perfectly, but they don't
write anything in the 2 databases, eg. (one of the 2 DB):

  7/11/18 13:47          8.708    124 a---  cache.frm
  7/11/18 13:47          8.700    124 a---  cache_index.frm
  7/11/18 13:47          8.728    124 a---  cache_messages.frm
  7/11/18 13:47          8.672    124 a---  cache_shared.frm
  7/11/18 13:47          8.668    124 a---  cache_thread.frm
  7/11/18 13:47          8.660    124 a---  contactgroupmembers.frm
  7/11/18 13:47          8.712    124 a---  contactgroups.frm
  7/11/18 13:47          8.874    124 a---  contacts.frm
  7/11/18 11:21             65    124 a---  db.opt
  7/11/18 13:47          8.634    124 a---  dictionary.frm
  7/11/18 13:47          8.976    124 a---  identities.frm
  7/11/18 13:47          8.696    124 a---  searches.frm
  7/11/18 13:47          8.694    124 a---  session.frm
  7/11/18 13:47          8.592    124 a---  system.frm
  7/11/18 13:47          8.912    124 a---  users.frm

but pointing the webmail to the old DB machine (5.1.73) works
pointing it to the new DB machine 5.6.51 don't work
like the wembail is unable to connect to the DB
and on mysqld console i get..

2024-12-10 13:47:24 5288 [Warning] InnoDB: Cannot open table rcube2/session from

anyway the 2 webmails DB do not get updated from mysql_upgrade command
they remain in "5.1 format" and of course the webmail don't work


massimo