View Issue Details
|ID||Project||Category||View Status||Date Submitted||Last Update|
|0026556||mantisbt||upgrade||public||2020-01-02 13:55||2020-01-18 12:21|
|Priority||normal||Severity||crash||Reproducibility||have not tried|
|Status||closed||Resolution||no change required|
|Summary||0026556: Upgrading from 2.22.0 crashed when updating database|
Windows Server 2012 R2
When attempting to upgrade the database from 2.22.0 to 2.23.0 due to the table change, the webpage wouldn't connect to MySQL even though the verified correct root password was being used. So we tried the update using just the User UID/PWD and that worked except died on the TABLE UPDATE since the user account doesn't have that access level. So we logged into MySQL through a remote connection and logged in as root using the previous password (which was correct) and ran the table update command manually and it worked fine. Then tried to run the other command but it died with a DUPLICATE PRIMARY KEY error.
|Steps To Reproduce|
The error is that the primary key consists of three _id columns, which already existed in the database due to the prior attempt to run the update with a lower-privileged user account.
Thus the error: instead of using an ADD on the update script, first test for Primary Key existing, then if it doesn't exist use the ADD otherwise just use an UPDATE to add the new data to that config table, since it appears it is a "history" table of sorts and has a running update of all of the rows from previous installs/updates.
See forum for more info: https://mantisbt.org/forums/viewtopic.php?f=3&t=26758
|Tags||No tags attached.|
Still unsure why the latest version is unable to login remotely to the MySQL instance with the root password when it was verified correct due to being able to log in locally.
Perhaps a clue is that the error text from MySQL came back with the username "root" (correct) appending "@local" (incorrect). So something somewhere is appending the "@" sign and the spurious domain data to the username entry. Unknown if this is normal operation for MySQL or not, or if it is something in Mantis doing it.
It is perfectly normal that the _databaseversion entry already exists in the config table - this holds the schema version, and indeed there should be only one entry. The installer will correctly execute an UPDATE statement if the record already exists.
Even though what you have done seems correct, it is not supported or recommended. You should not try to second-guess what the installer is doing to manually update the database as this may impact system operation or future upgrades.
You should rather try to figure out why your superuser DB account (root or whatever) can't connect from your webserver. Suggested reading: https://dev.mysql.com/doc/refman/5.7/en/problems-connecting.html
Thinking I didn't explain myself well.
I can use the password while remoted into the server to log in to mysql so it isn't a password problem.
I was able to use the mantis regular install process to update the database on our DEV server just fine, but on the PROD server the regular mantis install process failed.
So I checked the box to only "print the commands" used to update the database. That is where I got the commands that would be run by the installer.
Then I used those same commands while logged in locally to mysql to do the update.
The problem is that the normal mantis installer code should test if the primary key exists and if so do an Update. But it actually only does an Add. This is what I was reporting needs to be enhanced in the installer code. The error from mysql actually says that the mantis installer code is trying to do an ADD not an UPDATE.
By remoted into the server I assume you mean via SSH / Remote desktop session. This means that you are effectively connecting locally to your database.
Your webserver on the other hand, most likely does not connect locally, but over a TCP/IP connection. Therefore you need to authorize your privileged account (root or whatever) to also connect remotely. This may require creating a new user account
This is a MySQL setup issue, not related to MantisBT. Consequently, I will resolve this issue as "no change required".
As for the steps you used to extract the upgrade instructions, they are correct but be aware that they may not result in a fully functional database in the end, depending on the schema change (some upgrade steps execute PHP code, which cannot be translated to SQL statements).
It does, but only when actually performing the update, not when generating SQL.