Koozali.org: home of the SME Server

[Solved] MySQL upgrade to 5.5 walkthrough with problems

Offline LANMonkey

  • ****
  • 350
  • +0/-0
    • Database Collection of Transcripts for the ICTY
[Solved] MySQL upgrade to 5.5 walkthrough with problems
« on: March 04, 2016, 02:09:55 AM »
I have been running SME server for a number of years with MySQL 5.5 upgraded with the instructions I found here:

https://forums.contribs.org/index.php/topic,50081.0.html

The instructions at the original post were to accomodate Moodle 2.5 back in 2013.  I have abbreviated them along with the altered repository and some additional instructions for the specific purpose of installing MySQL 5.5.

You should copy and paste the commands into the command line to speed things up.

1. We first enable the InnoDB property of mysqld service.
Code: [Select]
/sbin/e-smith/db configuration setprop mysqld InnoDB enabled
2. Expand configuration to register
Code: [Select]
/sbin/e-smith/expand-template /etc/my.cnf
3. Then we fetch the latest MySQL 5.5 from the atomic repository as so:
Code: [Select]
/sbin/e-smith/db yum_repositories set atomic repository Name 'Atomic EL6' MirrorList 'http://www.atomicorp.com/mirrorlist/atomic/centos-6-$basearch' GPGCheck yes GPGKey 'https://www.atomicorp.com/RPM-GPG-KEY.art.txt' Visible no
4. Signal event now to register the yum command in accepting the added repository above:
Code: [Select]
/sbin/e-smith/signal-event yum-modify
5. Finally we yum install the MySQL with the command:
Code: [Select]
/usr/bin/yum -y --enablerepo='atomic' install mysql
At this point, MySQL still fails to start up properly. For we are going to be adding a template that is related to InnoDB.

6. We first make the directory of the file we want to expand: (read SME Developer Manual to understand the logic behind why we do this)
Code: [Select]
/bin/mkdir -p /etc/e-smith/templates-custom/etc/my.cnf/
7. We need to write a template.  The template that we want to expand to form my.cnf: call it 009innodb and have the following contents:

Code: [Select]
{
    my $innodb = $mysqld{InnoDB} || 'disabled';

    return "skip-innodb" unless $innodb eq 'enabled';

    # innodb is enabled. We'll use the MySQL-AB-recommended innodb
    # defaults for small to medium sites, from the my-medium.cnf file
    # distributed with MySQL
    $OUT .= "innodb_data_home_dir = /var/lib/mysql/\n";
    $OUT .= "innodb_data_file_path = ibdata1:10M:autoextend\n";
    $OUT .= "innodb_log_group_home_dir = /var/lib/mysql/\n";
    $OUT .= "innodb_buffer_pool_size = 16M\n";
    $OUT .= "innodb_additional_mem_pool_size = 2M\n";
    $OUT .= "innodb_log_file_size = 5M\n";
    $OUT .= "innodb_log_buffer_size = 8M\n";
    $OUT .= "innodb_flush_log_at_trx_commit = 1\n";
    $OUT .= "innodb_lock_wait_timeout = 50\n";
}

Open a new text file copy the above into it and save it as 009innodb. 

8. We then copy this file, 009innodb, to the template directory to be expanded:
Code: [Select]
cp /path/to/009innodb /etc/e-smith/templates-custom/etc/my.cnf/009innodb (substitute "/path/to/" with the actual path to the file.)

9. Naturally, we finally expand this template to form my.cnf file:
Code: [Select]
/sbin/e-smith/expand-template /etc/my.cnf
10. All of the changes need to be registered in as post-upgrade and mysqld service would need to be restarted for the changes to take effect. Maybe a reboot command would be nice as well.
Code: [Select]
/sbin/e-smith/signal-event post-upgrade
Code: [Select]
/usr/bin/sv t mysqldreboot computer

11.  Go into mysql from the command line and type \s to see if you have installed the correct version.

At this point, you may be getting errors from your backups

Quote
Backup terminated: pre-backup failed - status: 256

In the logs,

Quote
Dec 21 10:00:02 xxxxx esmith::event[6488]: S20mysql-dump-tables=action|Event|pre-backup|Action|S20mysql-dump-tables|Start|1387648802 266242|End|1387648802 852948|Elapsed|0.586706|Status|256

To solve this,

12. Open the file

/etc/e-smith/events/actions/mysql-dump-tables

find the line with

Code: [Select]
for db in $(mysql -BNre "show databases;"); do mysqldump -x --add-drop-table -QB "$db" -r /home/e-smith/db/mysql/"$db".dump || exit 1; done
and add the -x as shown above.
« Last Edit: March 07, 2016, 06:04:07 PM by LANMonkey »

Offline LANMonkey

  • ****
  • 350
  • +0/-0
    • Database Collection of Transcripts for the ICTY
Re: MySQL upgrade to 5.5 walkthrough with problems
« Reply #1 on: March 04, 2016, 02:14:39 AM »
Twice in the past I have managed to make this work.  I now have to restore a system and I have installed a fresh server with the download smeserver-9.1-i386.iso.

But this time with this walkthrough, I am going into MySQL and finding the old version still coming up.  I tried reinstalling from step 4,

Code: [Select]
/sbin/e-smith/signal-event yum-modify
and I am getting an error message,

Quote
Install       2 Package(s)
Upgrade      13 Package(s)

Total size: 27 M
Downloading Packages:
warning: rpmts_HdrFromFdno: Header V4 RSA/SHA1 Signature, key ID 4520afa9: NOKEY
Retrieving key from https://www.atomicorp.com/RPM-GPG-KEY.art.txt

The GPG keys listed for the "Atomic EL6" repository are already installed but they are not correct for this package.
Check that the correct key URLs are configured for this repository.

Is this something I might have missed on the first try?  Is this why it won't install?

What else might I have missed?

Offline ReetP

  • *
  • 3,722
  • +5/-0
Re: MySQL upgrade to 5.5 walkthrough with problems
« Reply #2 on: March 04, 2016, 03:30:02 AM »
Use the RHEL recommended method :

https://wiki.contribs.org/Scl

Note x64 only I think....
...
1. Read the Manual
2. Read the Wiki
3. Don't ask for support on Unsupported versions of software
4. I have a job, wife, and kids and do this in my spare time. If you want something fixed, please help.

Bugs are easier than you think: http://wiki.contribs.org/Bugzilla_Help

If you love SME and don't want to lose it, join in: http://wiki.contribs.org/Koozali_Foundation

Offline LANMonkey

  • ****
  • 350
  • +0/-0
    • Database Collection of Transcripts for the ICTY
Re: MySQL upgrade to 5.5 walkthrough with problems
« Reply #3 on: March 04, 2016, 05:19:49 AM »
Use the RHEL recommended method :

https://wiki.contribs.org/Scl

Note x64 only I think....

My CPU is not x64 capable, else I would have installed the x64 version of SME 9.1.  Thanks for posting that anyway.  I will keep it in mind.  It might be worth upgrading my CPU.  But that doesn't guarantee that there might not be other complications.

No ideas as to what my problem with the atomic version is?  Anyone?

Offline janet

  • ****
  • 4,812
  • +0/-0
Re: MySQL upgrade to 5.5 walkthrough with problems
« Reply #4 on: March 04, 2016, 08:48:14 AM »
LANMonkey

Well you could delete the Atomic EL6 repository & then re-add the repository using the latest configuration parameters.

See
https://wiki.contribs.org/Category:Yum_Repository
&
https://wiki.contribs.org/Atomic
Please search before asking, an answer may already exist.
The Search & other links to useful information are at top of Forum.

Offline LANMonkey

  • ****
  • 350
  • +0/-0
    • Database Collection of Transcripts for the ICTY
Re: MySQL upgrade to 5.5 walkthrough with problems
« Reply #5 on: March 04, 2016, 06:22:14 PM »
LANMonkey

Well you could delete the Atomic EL6 repository & then re-add the repository using the latest configuration parameters.

See
https://wiki.contribs.org/Category:Yum_Repository
&
https://wiki.contribs.org/Atomic

How do you delete the old Atomic EL6 repository? I have tried "yum help" and "yum info" and don't see any clues about how to do that.  Would "yum erase 'Atomic EL6'" do it?

I also have a 64bit processor on its way, it cost me $8 US.  Not a problem.  I'll try both solutions.

Offline CharlieBrady

  • *
  • 6,918
  • +3/-0
Re: MySQL upgrade to 5.5 walkthrough with problems
« Reply #6 on: March 05, 2016, 12:52:39 AM »
Atomic repo management has been a little muddled and they used two different GPG keys:

https://www.atomicorp.com/forum/viewtopic.php?t=7591&p=40937

Offline ReetP

  • *
  • 3,722
  • +5/-0
Re: MySQL upgrade to 5.5 walkthrough with problems
« Reply #7 on: March 05, 2016, 12:59:03 AM »
How do you delete the old Atomic EL6 repository? I have tried "yum help" and "yum info" and don't see any clues about how to do that.  Would "yum erase 'Atomic EL6'" do it?

I'm surprised that you know enough about templates and hacking MySQL etc, but not how to remove a repo using e-smith commands ! The Wiki is your Bible :

https://wiki.contribs.org/DB_Variables_Configuration#yum

Show installed repos:

db yum_repositories show

I guess it is called 'atomic' from the wiki page

So

db yum_repositories show atomic

db yum_repositories delete atomic

signal-event yum-modify

Quote
I also have a 64bit processor on its way, it cost me $8 US.  Not a problem.  I'll try both solutions.

For the money it is probably a much better solution long term.

B. Rgds
John
...
1. Read the Manual
2. Read the Wiki
3. Don't ask for support on Unsupported versions of software
4. I have a job, wife, and kids and do this in my spare time. If you want something fixed, please help.

Bugs are easier than you think: http://wiki.contribs.org/Bugzilla_Help

If you love SME and don't want to lose it, join in: http://wiki.contribs.org/Koozali_Foundation

Offline warren

  • *
  • 291
  • +0/-0
Re: MySQL upgrade to 5.5 walkthrough with problems
« Reply #8 on: March 06, 2016, 08:01:43 PM »
I'm surprised that you know enough about templates and hacking MySQL etc, but not how to remove a repo using e-smith commands ! The Wiki is your Bible :......

and the forums are the / your  study guide ...

I have been running SME server for a number of years ....

And I still suprise   :-o myself by what we can learn from the forums ... :-D

Offline LANMonkey

  • ****
  • 350
  • +0/-0
    • Database Collection of Transcripts for the ICTY
Re: MySQL upgrade to 5.5 walkthrough with problems
« Reply #9 on: March 06, 2016, 09:13:41 PM »
Well I tried deleting the old repository as according to the posted instructions and installing the wiki repository and I'm getting the same error messages:

Quote
[root@xxxxxxxx ~]# /sbin/e-smith/db yum_repositories set atomic repository \
> Name 'Atomic EL6' \
> MirrorList 'http://www.atomicorp.com/mirrorlist/atomic/centos-6-$basearch' \
> EnableGroups no \
> GPGCheck yes \
> GPGKey 'https://www.atomicorp.com/RPM-GPG-KEY.art.txt' \
> Visible no \
> status disabled
[root@xxxxxxxx ~]# /sbin/e-smith/signal-event yum-modify
[root@xxxxxxxx ~]# /usr/bin/yum -y --enablerepo='atomic' install mysql
....
Setting up Install Process
Loading mirror speeds from cached hostfile
 * atomic: www8.atomicorp.com
 * base: mirror.tocici.com
....
atomic                                                   | 3.4 kB     00:00
Resolving Dependencies
--> Running transaction check
---> Package mysql.i686 0:5.1.73-5.el6_6 will be updated
--> Processing Dependency: mysql = 5.1.73-5.el6_6 for package: mysql-server-5.1.73-5.el6_6.i686
....
---> Package mysql-libs.i686 0:5.5.47-32.el6.art will be an update
---> Package mysql-server.i686 0:5.1.73-5.el6_6 will be updated

....

Dependencies Resolved

================================================================================
 Package              Arch        Version                   Repository     Size
================================================================================
Updating:
 mysql                i686        5.5.47-32.el6.art         atomic        5.8 M
Installing for dependencies:
....
....

Transaction Summary
================================================================================
Install       2 Package(s)
Upgrade      13 Package(s)

Total size: 27 M
Downloading Packages:
warning: rpmts_HdrFromFdno: Header V4 RSA/SHA1 Signature, key ID 4520afa9: NOKEY
Retrieving key from https://www.atomicorp.com/RPM-GPG-KEY.art.txt

The GPG keys listed for the "Atomic EL6" repository are already installed but they are not correct for this package.
Check that the correct key URLs are configured for this repository.



Here is what I have in the repository:

Quote
[root@xxxxxxxx ~]# db yum_repositories show atomic
atomic=repository
    EnableGroups=no
    GPGCheck=yes
    GPGKey=https://www.atomicorp.com/RPM-GPG-KEY.art.txt
    MirrorList=http://www.atomicorp.com/mirrorlist/atomic/centos-6-$basearch
    Name=Atomic EL6
    Visible=no
    status=disabled
[root@xxxxxxxx ~]#


Offline LANMonkey

  • ****
  • 350
  • +0/-0
    • Database Collection of Transcripts for the ICTY
Re: MySQL upgrade to 5.5 walkthrough with problems
« Reply #10 on: March 07, 2016, 12:26:30 AM »
SUCCESS!!

I looked again at CharlieBrady's post,

Atomic repo management has been a little muddled and they used two different GPG keys:

https://www.atomicorp.com/forum/viewtopic.php?t=7591&p=40937

I followed the forum topic,

Quote
We're consolidating under the atomicorp key, you can define multple keys in the .repo file like:

Code:
gpgkey = file:///etc/pki/rpm-gpg/RPM-GPG-KEY.atomicorp.txt   
         file:///etc/pki/rpm-gpg/RPM-GPG-KEY.art.txt

We'll do a updated atomic-release with it soon.

and I just changed,

GPGKey 'https://www.atomicorp.com/RPM-GPG-KEY.art.txt'

to

GPGKey 'https://www.atomicorp.com/RPM-GPG-KEY.atomicorp.txt'

and deleted the old repository again and added it again with this edit.  I ran through the install again, checked my server and I see,

"mysql  Ver 14.14 Distrib 5.5.47, for Linux (i686) using readline 5.1"

So, so far, this solution works. 

In summary the procedure in the first post works with the new repository commands posted here

https://wiki.contribs.org/Atomic

but you must change

GPGKey 'https://www.atomicorp.com/RPM-GPG-KEY.art.txt'

to

GPGKey 'https://www.atomicorp.com/RPM-GPG-KEY.atomicorp.txt'

guest22

Re: MySQL upgrade to 5.5 walkthrough with problems
« Reply #11 on: March 07, 2016, 04:40:08 AM »
JFYI, Mmccarn has written an very nice how-to on how to install MariaaDB alongside the default SME Server MySQL.


https://wiki.contribs.org/MariaDB_alongside_MySQL


That could also be a possible way to use a 'higher' version other then the default version of MySQL.

Offline ReetP

  • *
  • 3,722
  • +5/-0
Re: MySQL upgrade to 5.5 walkthrough with problems
« Reply #12 on: March 07, 2016, 12:36:52 PM »
SUCCESS!!

I looked again at CharlieBrady's post,

Ahhh, let your eyes be your guide :-)

Good that you got it sorted.
...
1. Read the Manual
2. Read the Wiki
3. Don't ask for support on Unsupported versions of software
4. I have a job, wife, and kids and do this in my spare time. If you want something fixed, please help.

Bugs are easier than you think: http://wiki.contribs.org/Bugzilla_Help

If you love SME and don't want to lose it, join in: http://wiki.contribs.org/Koozali_Foundation

Offline CharlieBrady

  • *
  • 6,918
  • +3/-0
Re: MySQL upgrade to 5.5 walkthrough with problems
« Reply #13 on: March 07, 2016, 04:28:44 PM »
LANMonkey, please add '[Solved]' to this thread.