Page 1 of 3 123 LastLast
Results 1 to 10 of 21

Thread: Mysql problem after upgrade from 18.04 to 20.04.1

  1. #1
    Join Date
    Jan 2008
    Location
    Netherlands
    Beans
    305
    Distro
    Ubuntu 11.04 Natty Narwhal

    Mysql problem after upgrade from 18.04 to 20.04.1

    I finally decided to upgrade my Ubuntu server from 18.04 to 20.04.1 with a do-release-upgrade.
    This was a big mistake!

    No there is a Mysql problem:
    Code:
    sudo apt list --installed |grep mysql
    dbconfig-mysql/focal,focal,now 2.0.13 all [installed,automatic]
    default-mysql-client/focal,focal,now 1.0.5ubuntu2 all [installed,automatic]
    libdbd-mysql-perl/focal,now 4.050-3 amd64 [installed]
    libmysqlclient20/now 5.7.31-0ubuntu0.18.04.1 amd64 [installed,local]
    libmysqlclient21/focal-updates,focal-security,now 8.0.21-0ubuntu0.20.04.4 amd64 [installed,automatic]
    mysql-client-8.0/focal-updates,focal-security,now 8.0.21-0ubuntu0.20.04.4 amd64 [installed,automatic]
    mysql-client-core-8.0/focal-updates,focal-security,now 8.0.21-0ubuntu0.20.04.4 amd64 [installed,automatic]
    mysql-common/focal,focal,now 5.8+1.0.5ubuntu2 all [installed]
    mysql-server-8.0/focal-updates,focal-security,now 8.0.21-0ubuntu0.20.04.4 amd64 [installed,automatic]
    mysql-server-core-8.0/focal-updates,focal-security,now 8.0.21-0ubuntu0.20.04.4 amd64 [installed,automatic]
    mysql-server/focal-updates,focal-updates,focal-security,focal-security,now 8.0.21-0ubuntu0.20.04.4 all [installed]
    systemctl -xe:
    Code:
    Sep 29 11:52:21 server.net systemd[1]: Failed to start MySQL Community Server.
    It looks to me that something went wrong in the upgrade. There are different versions of mysql packages installed as you can see. 5.7 5.8 and 8.0.
    I'm having problems fixing this. Someone could help me out here please?!

    Edit:
    continuing struggling with this.
    I installed mysql-apt-config_0.8.15-1_all.deb from mysql.com and so the repo.
    After that I now have only mysql 8.0 packages, but still the mysql server does not start.

    Code:
    sudo systemctl status mysql.service
    ● mysql.service - MySQL Community Server
         Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
         Active: failed (Result: exit-code) since Tue 2020-09-29 12:21:51 CEST; 17s ago
           Docs: man:mysqld(8)
                 http://dev.mysql.com/doc/refman/en/using-systemd.html
        Process: 59003 ExecStartPre=/usr/share/mysql-8.0/mysql-systemd-start pre (code=exited, status=0/SUCCESS)
        Process: 59055 ExecStart=/usr/sbin/mysqld (code=exited, status=1/FAILURE)
       Main PID: 59055 (code=exited, status=1/FAILURE)
         Status: "Server startup in progress"
    
    Sep 29 12:21:49 server.net systemd[1]: Starting MySQL Community Server...
    Sep 29 12:21:51 server.net systemd[1]: mysql.service: Main process exited, code=exited, status=1/FAILURE
    Sep 29 12:21:51 server.net systemd[1]: mysql.service: Failed with result 'exit-code'.
    Sep 29 12:21:51 server.net systemd[1]: Failed to start MySQL Community Server.
    Is this a incompatibility in the database? Should the database been converted during upgrade?
    Ooh I'm tearing my hair out!
    Last edited by ahbart; September 29th, 2020 at 11:26 AM.

  2. #2
    Join Date
    Jan 2008
    Location
    Netherlands
    Beans
    305
    Distro
    Ubuntu 11.04 Natty Narwhal

    Re: Mysql problem after upgrade from 18.04 to 20.04.1

    I managed to restart the database server!!! But ...
    When I commented out:
    Code:
    #innodb_large_prefix=true
    #innodb_file_format=barracuda
    #innodb_file_per_table=1
    from: /etc/mysql/mysql.conf.d/mysqld.cnf
    The server is starting up.
    But I was using this setting for Nextcloud. Is there a another way to enable this?

    edit:
    I notice that Nextcloud is not complaining about this. So maybe this is not necessary any more.
    Last edited by ahbart; September 29th, 2020 at 12:16 PM.

  3. #3
    Join Date
    Sep 2011
    Location
    Behind you!
    Beans
    1,524
    Distro
    Ubuntu 20.04 Focal Fossa

    Re: Mysql problem after upgrade from 18.04 to 20.04.1

    When upgrading database versions, be sure to upgrade your database schema by running this command after updating the database engine:

    Code:
    mysql_upgrade
    I avoid such issues by installing the server from a fresh start (e.g. Ubuntu Server 20.04) and then install the current database engine (e.g. MariaDB 10.5.5).

    Then import the .sql files from the mysqldump on the old server. I avoid the "all databases" dump file in such a situation because I want to avoid the old "mysql" databases that come with the system. I just import the user databases and the associated users/grants.

    LHammonds

  4. #4
    Join Date
    Jan 2008
    Location
    Netherlands
    Beans
    305
    Distro
    Ubuntu 11.04 Natty Narwhal

    Re: Mysql problem after upgrade from 18.04 to 20.04.1

    Thank you LHammonds,
    I assume that the 'conversion' had taken place, but in my case the problem was the different versions of mysql, mysql-server, mysql-common and -client, and the configuration items innodb in mysqld.cnf.
    At least that fixed the issue. Bit I'm not sure about the real cause en what solved it.

  5. #5
    Join Date
    Sep 2011
    Location
    Behind you!
    Beans
    1,524
    Distro
    Ubuntu 20.04 Focal Fossa

    Re: Mysql problem after upgrade from 18.04 to 20.04.1

    Was everything originally installed from the official repositories or did you have .deb manual installs or compiled from source code?

    If everything was installed from repository, version incompatibility issues in components are rare. Also, config files if not reviewed over time can contain deprecated references and missing new options. Always to compare your old config to the current default to see if any adjustments need to be made.

    I have not run MySQL proper since the original devs moved over to MariaDB so I am not familiar with changes in MySQL over time (it was VERY static when Oracle took it over...thus the move to MariaDB)

    LHammonds

  6. #6
    Join Date
    Jan 2008
    Location
    Netherlands
    Beans
    305
    Distro
    Ubuntu 11.04 Natty Narwhal

    Re: Mysql problem after upgrade from 18.04 to 20.04.1

    I only install from repo on my server. Not even from ppa before. There was a warning about phpmyadmin during upgrade. I skipped it, assuming I could fix that later. But I was not aware of any other problem with mysql packages.
    I used mariadb before. Is it possible to replace Mysql and replace it with mariadb? Without breaking the system?

  7. #7
    Join Date
    Sep 2011
    Location
    Behind you!
    Beans
    1,524
    Distro
    Ubuntu 20.04 Focal Fossa

    Re: Mysql problem after upgrade from 18.04 to 20.04.1

    Quote Originally Posted by ahbart View Post
    Is it possible to replace Mysql and replace it with mariadb? Without breaking the system?
    MariaDB is a "drop-in" replacement. Meaning that if you uninstall MySQL, then install MariaDB, your databases should just work. Make sure you know where the databases are living though. I think the default location is /var/lib/mysql but it might be different. I move mine under /opt and have to reference that in the .cnf file.

    I have not done it recently but when I did the switch, it was quite painless. It was a version jump so I needed to do the "mysql_upgrade" command.

    But as always, before doing such a change, make sure you have the mysqldump exports of each database and also pull out the users/grants which are contained in the "mysql" system database.

    I export my user grants like this:

    Code:
    mysql --skip-column-names --no-auto-rehash --silent --execute="SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') FROM mysql.user WHERE user<>''" | sort | mysql --skip-column-names --no-auto-rehash | sed 's/$/;/g' > /tmp/db-grants.sql
    I export each user database into it's own .sql file (I avoid the "all databases" backup to a single .sql file because I don't want to include the system databases)
    Code:
    mysqldump userdb1 > /tmp/userdb1.sql
    mysqldump userdb2 > /tmp/userdb2.sql
    mysqldump userdb3 > /tmp/userdb3.sql
    I also export the system databases for restore of the exact same system but when migrating, I do not import system databases.

    The only thing I really have in the system databases are the grants so this would be enough for me. If you have stored procedures, roles, etc. you may need to export those in your backup to ensure you have everything.

    LHammonds
    Last edited by LHammonds; September 30th, 2020 at 02:26 PM.

  8. #8
    Join Date
    Jan 2008
    Location
    Netherlands
    Beans
    305
    Distro
    Ubuntu 11.04 Natty Narwhal

    Re: Mysql problem after upgrade from 18.04 to 20.04.1

    hmm. another problem.
    I made some backups. etc.
    Then apt install mariadb-client mariadb-server mariadb-common
    automatically mysql packages where removed.
    It looked good.
    But now mysql won't start:
    Code:
    Sep 30 15:32:53 server.net mysqld[133129]: 2020-09-30 15:32:53 0 [Note] InnoDB: Starting shutdown...
    Sep 30 15:32:54 server.net mysqld[133129]: 2020-09-30 15:32:54 0 [ERROR] Plugin 'InnoDB' init function returned error.
    Sep 30 15:32:54 server.net mysqld[133129]: 2020-09-30 15:32:54 0 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
    Sep 30 15:32:54 server.net mysqld[133129]: 2020-09-30 15:32:54 0 [Note] Plugin 'FEEDBACK' is disabled.
    Sep 30 15:32:54 server.net mysqld[133129]: 2020-09-30 15:32:54 0 [ERROR] Could not open mysql.plugin table. Some plugins may be not loaded
    Sep 30 15:32:54 server.net mysqld[133129]: 2020-09-30 15:32:54 0 [ERROR] Unknown/unsupported storage engine: InnoDB
    Sep 30 15:32:54 server.net mysqld[133129]: 2020-09-30 15:32:54 0 [ERROR] Aborting
    Sep 30 15:32:54 server.net systemd[1]: mariadb.service: Main process exited, code=exited, status=1/FAILURE
    Sep 30 15:32:54 server.net systemd[1]: mariadb.service: Failed with result 'exit-code'.
    Sep 30 15:32:54 server.net systemd[1]: Failed to start MariaDB 10.3.22 database server.
    again something with innodb? Any help is very welcome!

  9. #9
    Join Date
    May 2013
    Location
    Galiza
    Beans
    4,010
    Distro
    Ubuntu

    Re: Mysql problem after upgrade from 18.04 to 20.04.1

    https://websiteforstudents.com/switc...04-lts-server/

    Then apt install mariadb-client mariadb-server mariadb-common
    automatically mysql packages where removed.
    It looked good.
    No, it didn't.
    The comment above and this link show why it didn't and it couldn't.

  10. #10
    Join Date
    Jan 2008
    Location
    Netherlands
    Beans
    305
    Distro
    Ubuntu 11.04 Natty Narwhal

    Re: Mysql problem after upgrade from 18.04 to 20.04.1

    I do not really understand what you mean. The comments under article show that the database is not converted?
    But is it possible to continue with mariadb, or should I better go back to mysql 8.0?

    Edit:
    I went back to mysql 8.0.
    It is not as easy as I hoped for. Maybe I will find a good tutorial.
    Thank you all. If you have a suggestion how go to mariadb, you're welcome!!!
    Last edited by ahbart; September 30th, 2020 at 04:21 PM.

Page 1 of 3 123 LastLast

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •