Support

Site Restoration

#34596 How do I restore the DB with a different mysql port

Posted in ‘Site restoration’
This is a public ticket

Everybody will be able to see its contents. Do not include usernames, passwords or any other sensitive information.

Environment Information

PHP version
n/a
CMS Type
Other
CMS Version
n/a
Backup Tool Version
n/a
Kickstart version
n/a

Latest post by gregkeys on Thursday, 25 February 2021 18:08 CST

gregkeys

Im trying to restore my Akeeba Backup to a digital ocean droplet and a digital ocean mysql database which uses a non standard port, ANGIE does not seem to have a way to pass in a mysql host port

gregkeys

I finally got it to connect but now I'm getting this error

Β 

<h2>Database error processing line 3</h2> <p>Database server error reply:</p> <tt>ErrNo #3750</tt> <pre>Unable to create or change a table without a primary key, when the system variable 'sql_require_primary_key' is set. Add a primary key to the table or unset this variable to avoid this message. Note that tables without a primary key can cause performance problems in row-based replication, so please consult your DBA before changing this setting. SQL=CREATE TABLE `j_utf8_conversion` ( `converted` tinyint(4) NOT NULL DEFAULT 0 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci</pre> <p>Raw query text:</p> <pre>CREATE TABLE `j_utf8_conversion` ( `converted` tinyint(4) NOT NULL DEFAULT 0 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci</pre>

nicholas
Akeeba Staff
Manager

Using a different port is done by changing the port when restoring your site. Please note that the hostname matters. localhost always connects using a named pipe, 127.0.0.1 always connects using TCP/IP. This is how all MySQL software works and we have documented it anyway.  You can access the documentation by clicking the documentation icon at the top of the Restore Database page; it open in a new tab/window.

Regarding your error, it comes from your server which is misconfigured. You have set up a MySQL configuration option which requires a primary key on all tables in the database. Joomla does not have a primary key on all tables. Therefore your MySQL server configuration is incompatible with Joomla. That's why you are getting an error restoring the core j_utf8_conversion table. If we worked around that on restoration your site would still be broken after the restoration is finished.

You need to change your MySQL server configuration to not use the sql_require_primary_key option. A cursory Google search (took me about 10 seconds) on your error message yields a simple solution: https://stackoverflow.com/questions/66149804/change-sql-require-primary-key-value-permanently-in-mysql-8

Nicholas K. Dionysopoulos

Lead Developer and Director

πŸ‡¬πŸ‡·Greek: native πŸ‡¬πŸ‡§English: excellent πŸ‡«πŸ‡·French: basic β€’ πŸ• My time zone is Europe / Athens
Please keep in mind my timezone and cultural differences when reading my replies. Thank you!

gregkeys

Seems like the error was with Digital Ocean's DB only using MySQL 8 after deploying a MySQL server running Mysql 5+ it worked.

nicholas
Akeeba Staff
Manager

The key difference between MySQL 8 and 5 is that MySQL 8 has sql_require_primary_key set by default whereas MySQL 5 doesn't. That's why I pointed you towards that direction.

For what it's worth, my local development server is on MySQL 8.0 using that change and another change about using legacy passwords by default (since Joomla 3 can't support anything else).

Nicholas K. Dionysopoulos

Lead Developer and Director

πŸ‡¬πŸ‡·Greek: native πŸ‡¬πŸ‡§English: excellent πŸ‡«πŸ‡·French: basic β€’ πŸ• My time zone is Europe / Athens
Please keep in mind my timezone and cultural differences when reading my replies. Thank you!

gregkeys

Yeah I saw that part in the original response which is what got me to look at the mysql version, unfortunately digital does not allow you to change that setting because it breaks their replication and failover configuration.

Seems like Digital Ocean is not a good solution for their MySQL until Joomla catches up with the primary key in every table
https://www.digitalocean.com/docs/databases/mysql/#managed-database-cluster-known-issues

  • MySQL databases containing tables without a primary key and which contain more than 5000 rows may experience replication issues. To prevent this, DigitalOcean now requires you to add a primary key for each new table you create in any managed MySQL database created after 8 April 2020. We strongly recommend that you alsoΒ add primary keysΒ in existing databases to avoid replication issues.

gregkeys

Hey, I just had a thought for a good feature request, would it be possible to add a feature to admin tools and or Akeeba Backup which adds a primary key to every table that doesn't have one in order to make it compatible with MySQL 8?

nicholas
Akeeba Staff
Manager

Joomla 4 has added a primary key to every core table. The most recent versions of our software have followed suite. I cannot vouch for third party extensions.

BTW you are right that removing the need for a primary key breaks replication. That's exactly why it was made a requirement in MySQL 8!

Your problem isn't that Digital Ocean is not goo enough for Joomla but the fact that Joomla 3 is a 12-year old architecture (first released 9 years ago) created before any of these considerations made any practical sense. Joomla 4 at least takes all of that into account. Hopefully 2021 will be the year of Joomla 4.

Regarding your request, it's something I did consider, briefly, before realising it's a pipe dream :(

We cannot add a primary key to tables indiscriminately. It will break things. Doing that for our own software required going through each table which didn't have a primary key and consider how it's used, should we make an existing column a primary key, should we make a column group a primary key or should we add a new column and make it a primary key. This cannot be automated in any sensible way.

For example, n-to-n relation tables ("glue" tables) can usually use the two foreign keys as a primary key group since you cannot assign the same record from table B to the same record form table A multiple times. If, however, this is possible because of a denormalised schema (the glue table contains more than FKs, i.e. you can now have multiple instances of an item described by a table B record assigned to the same record in table A) you can't do that. You will have to put more keys into the group or refactor the code.

Unless you know the intent of the table structure you can't make these decisions. The intent is not conveyed at all, let alone in machine readable format. You need to study the extension code to figure it out.

Nicholas K. Dionysopoulos

Lead Developer and Director

πŸ‡¬πŸ‡·Greek: native πŸ‡¬πŸ‡§English: excellent πŸ‡«πŸ‡·French: basic β€’ πŸ• My time zone is Europe / Athens
Please keep in mind my timezone and cultural differences when reading my replies. Thank you!

gregkeys

I see, well thank you for your time.

Support Information

Working hours: We are open Monday to Friday, 9am to 7pm Cyprus timezone (EET / EEST). Support is provided by the same developers writing the software, all of which live in Europe. You can still file tickets outside of our working hours, but we cannot respond to them until we're back at the office.

Support policy: We would like to kindly inform you that when using our support you have already agreed to the Support Policy which is part of our Terms of Service. Thank you for your understanding and for helping us help you!