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) - update
To: eCS ISP Mailing List <ecs-isp@2rosenthals.com>
References: <list-7630187@2rosenthals.com> <list-11332776@2rosenthals.com>
 <list-11332843@2rosenthals.com> <list-11332868@2rosenthals.com>
Organization: Massimo S.
Message-ID: <9ef71b9f-80ad-6a87-ba90-b178dabca635@ecomstation.it>
Date: Tue, 10 Dec 2024 16:18:38 +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-11332868@2rosenthals.com>
Content-Type: text/plain; charset=iso-8859-15; format=flowed
Content-Language: it-IT
Content-Transfer-Encoding: 8bit



Il 10/12/2024 15:46, Massimo S. ha scritto:
> 
> 
> Il 10/12/2024 15:17, Massimo S. ha scritto:
>>
>>
>> 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
> 
> i'm now completely puzzled
> 
> i've entered the webmail, i removed 3 contacts and exited
> then i closed the browser, cleared cache etc.
> re-entered the webmail and it works correctly, the 3 contacts are gone
> 
> the tablespaces on mysql DB machine (the onet 5.1.73) are still with 2018 dates..
> 
> i've found the modifies in ibdata1 file on \mysql\data dir
> is this the way mysql work?
> 
> 
> massimo

i've deleted anything under \data

and re-copied all the stuff with ibdata1 file too, done again the upgrade

now i don't get anymore errors like these:

rcube2.system
Error    : Table 'rcube2.system' doesn't exist
status   : Operation failed
rcube2.users
Error    : Table 'rcube2.users' doesn't exist
status   : Operation failed

and webmails seems to work correctly
even performances are good


massimo