View Issue Details

IDProjectCategoryView StatusLast Update
0026556mantisbtupgradepublic2020-01-18 12:21
Reportermushu Assigned Todregad  
PrioritynormalSeveritycrashReproducibilityhave not tried
Status closedResolutionno change required 
Product Version2.23.0 
Summary0026556: Upgrading from 2.22.0 crashed when updating database
Description

Windows Server 2012 R2
MantisBT 2.23.0
ADOdb 5.20.15
PHP 5.6.36

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.

Additional Information

See forum for more info: https://mantisbt.org/forums/viewtopic.php?f=3&t=26758

TagsNo tags attached.

Relationships

related to 0026568 closeddregad Use appropriate statement to update DB schema when generating SQL 

Activities

mushu

mushu

2020-01-02 13:58

reporter   ~0063363

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.

dregad

dregad

2020-01-02 20:34

developer   ~0063364

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

mushu

mushu

2020-01-03 11:25

reporter   ~0063367

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.

dregad

dregad

2020-01-06 07:08

developer   ~0063369

I can use the password while remoted into the server to log in to mysql so it isn't a password problem.

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 root@%.hostname.com or similar; please refer to MySQL documentation for further information.

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).

The problem is that the normal mantis installer code should test if the primary key exists and if so do an Update

It does, but only when actually performing the update, not when generating SQL.
I'll see if this can be improved.

Issue History

Date Modified Username Field Change
2020-01-02 13:55 mushu New Issue
2020-01-02 13:58 mushu Note Added: 0063363
2020-01-02 20:34 dregad Assigned To => dregad
2020-01-02 20:34 dregad Status new => resolved
2020-01-02 20:34 dregad Resolution open => no change required
2020-01-02 20:34 dregad Note Added: 0063364
2020-01-03 11:25 mushu Status resolved => feedback
2020-01-03 11:25 mushu Resolution no change required => reopened
2020-01-03 11:25 mushu Note Added: 0063367
2020-01-06 07:08 dregad Status feedback => resolved
2020-01-06 07:08 dregad Resolution reopened => no change required
2020-01-06 07:08 dregad Note Added: 0063369
2020-01-06 09:13 dregad Relationship added related to 0026568
2020-01-18 12:21 atrol Status resolved => closed