Skip to first unread message
Mike Schouten
May 5, 2021, 8:17:11 PM5/5/21
to AtoM Users
Greetings!
I'm trying to upgrade our instance of AtoM from 2.4.1 to 2.6.4 and am running into a problem at the upgrade-sql step. I started off with a blank 2.6.4 install and that worked fine. The error only arose after replacing the database with our 2.4.1 database and running "php symfony tools:upgrade-sql".
SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'INDEX function_FI_2 TO function_object_FI_2' at line 1
We're running MariaDB 10.4.17 rather than specifically the requirement of MySQL 8.0. However, before the upgrade process, the blank 2.6.4 site installed and seemed to be working fine, so I'm not certain if database version is the issue here. If it is for certain, then I'll have a much easier time requesting a non-distro database.
I should also mention that I tried upgrading to 2.5.4, then 2.6.4 to see if it was a byproduct of jumping up too many versions. I was able to upgrade to 2.5.4 without any problems, but the subsequent 2.6.4 update had the same error.
Any hints, fixes or clarification on database requirements would be greatly appreciated!
Mike Schouten
MacEwan University
Dan Gillean
May 6, 2021, 9:59:29 AM5/6/21
to ICA-AtoM Users
Hi Mike,
One thing to check that often makes a difference when loading a sqldump from a previous AtoM version - did you remember to drop and recreate the new site's database first, as per step 4 in this section of the Upgrading documentation?
In any case, I suspect that the issue is in fact a compatibility one. One of the main differences is in the collation formats supported - we've used utf8mb4_0900_ai_ci in the 2.6 releases, and I'm pretty sure this is not currently supported in MariaDB 10.4. There may be other incompatibilities - MariaDB provides a helpful list of differences and incompatibilities here, and while I don't know off the top of my head how many of these directly impact AtoM, you can see that the list of differences is still quite extensive:
As noted in this post on differences between the two:
Most database administrators hoped that MariaDB will be kept as a branch of MySQL, so it will be very easy to migrate between the two. For the last few versions, that’s not the case anymore. For a long time now, MariaDB is actually a fork of MySQL, which means you need to put some thought when you migrate from one to another.
Regards,
he / him
--
You received this message because you are subscribed to the Google Groups "AtoM Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to ica-atom-user...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/ica-atom-users/afa8085a-c6c6-46eb-a98b-821861d8e208n%40googlegroups.com.
Mike Schouten
May 6, 2021, 11:51:26 AM5/6/21
to AtoM Users
Every upgrade attempt was preceded with a drop database and recreate. As you stated, MariaDB did not have the utf8mb4_0900_ai_ci collation option, but based on some reference I found elsewhere, I had believed utf8mb4_unicode_ci would be an adequate substitute.
It is strange (to me at least) that the regular install of 2.6.4 worked fine on MariaDB 10.4.17, at least from a database creation standpoint. No SQL errors there. But the upgrade process had one.
I'll attempt to get MySQL 8.0 installed and will report back if that works so there's a record for others who might run in to this problem.
Dan Gillean
May 6, 2021, 1:47:58 PM5/6/21
to ICA-AtoM Users
Hi Mike,
The Upgrade process is rather different than a clean install, because with each major version, the upgrade task will run a number of migration scripts to account for database schema versions. I talked to one of our developers, and it looks like in this case, one of the methods used in a migration is unavailable in the version of MariaDB you're using:
Specifically, this function in the migration task uses RENAME INDEX to update part of the database. When you make a clean installation, the index is created from scratch, so this particular method is not needed. When you upgrade however, it is.
One hopeful possibility: it looks like RENAME INDEX was added to MariaDB 10.5.2 and later - so it's possible if you're able to run a more recent version, that it will work? See:
That said, there may be other examples that we haven't yet found! But if you're committed to using MariaDB, then trying 10.5 might be your best bet.
Let us know how it goes, however you proceed!
Cheers,
he / him
To view this discussion on the web visit https://groups.google.com/d/msgid/ica-atom-users/e52ce4d0-440b-4aeb-9674-5e0621d1e521n%40googlegroups.com.
Mike Schouten
May 10, 2021, 9:26:52 PM5/10/21
to AtoM Users
Well, I've gotten the upgrade to complete, through one of the more inconvenient ways possible!
It proved to be impossible to get MySQL 8.0 or MariaDB 10.5.2 on a server supported by central ITS, as those databases were not available on our OS of choice. As such, I had to get a bit creative. I used a secondary Ubuntu 20 machine with AtoM minimally installed along with MySQL 8.0. I copied the old database to that server, imported it, upgraded it with the tools:upgrade-sql script, dumped it, replaced all the "collate" type to one accepted by MariaDB 10.4 (utf8mb4_unicode_ci) and imported back into the official AtoM server. So far, that seems to have worked and I believe we'll have a functioning AtoM 2.6.4 for now.
I'll report back if future testing and use reveal any problems. But if not, this is one way to get AtoM 2.6.4 working on MariaDB 10.4.
Reply all
Reply to author
Forward
0 new messages