View Issue Details

IDProjectCategoryView StatusLast Update
0021678mantisbtupgradepublic2018-03-31 20:03
ReporterTomR Assigned To 
PrioritynormalSeverityminorReproducibilityhave not tried
Status newResolutionopen 
Summary0021678: After upgrade 1.2.19 -> 1.3.1 database structure still out of date.
Description

After upgrading I still message in login screen.

Warning: The database structure may be out of date. Please upgrade here before logging in.

How can IO verify that the database upgrade is succesfull?

In notice that the record database_version is not being updated in table mantis_config_table ( still shown 194 in stead of 209 ).
Due to the fact that it is trying an INSERT ( with unique key ) in stead of UPDATE.

Steps To Reproduce

So what I did was:

Write the SQL statements
Manually apply them
Manually update field database_version to '209'

TagsNo tags attached.
Attached Files

Relationships

related to 0022542 closedatrol Error at step 194 when upgrading to mantisbt 2.2.1 
related to 0022118 new upgrade_unattended will not complete if install_stored_filter_migrate is a required step 

Activities

atrol

atrol

2016-09-08 16:51

developer   ~0053984

still shown 194 in stead of 209
So there has been a problem when running step 195
$g_upgrade[195] = array( 'UpdateFunction', 'stored_filter_migrate', array() );
I assume the installer stopped when upgrading from 1.2.19.

You might get problems when using stored filters

Due to the fact that it is trying an INSERT ( with unique key ) in stead of UPDATE.
Which value should be inserted in which table?

dvzrv

dvzrv

2016-09-08 17:42

reporter   ~0053986

I have the same issue.
This is the stuff that should be done, but seemingly can't be done:

Database Creation Suppressed, SQL Queries follow

stored_filter_migrate;

;

stored_filter_migrate;

;

ALTER TABLE mantis_user_table MODIFY COLUMN password VARCHAR(64) NOT NULL DEFAULT '';

ALTER TABLE mantis_user_table MODIFY COLUMN password VARCHAR(64) NOT NULL DEFAULT '';

CREATE TABLE mantis_api_token_table (
id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
user_id INTEGER DEFAULT 0,
name VARCHAR(128) NOT NULL,
hash VARCHAR(128) NOT NULL,
date_created INTEGER UNSIGNED NOT NULL DEFAULT 0,
date_used INTEGER UNSIGNED NOT NULL DEFAULT 0,
PRIMARY KEY (id)
)DEFAULT CHARSET=utf8;

ALTER TABLE mantis_api_token_table ADD UNIQUE INDEX idx_user_id_name (user_id, name);

ALTER TABLE mantis_user_table ADD INDEX idx_email (email);

ALTER TABLE mantis_bug_file_table MODIFY COLUMN content LONGBLOB;

ALTER TABLE mantis_project_file_table MODIFY COLUMN content LONGBLOB;

ALTER TABLE mantis_user_table MODIFY COLUMN username VARCHAR(191) NOT NULL DEFAULT '';

ALTER TABLE mantis_user_table MODIFY COLUMN realname VARCHAR(191) NOT NULL DEFAULT '';

ALTER TABLE mantis_user_table MODIFY COLUMN email VARCHAR(191) NOT NULL DEFAULT '';

ALTER TABLE mantis_api_token_table MODIFY COLUMN user_id INTEGER UNSIGNED NOT NULL DEFAULT 0;

ALTER TABLE mantis_api_token_table MODIFY COLUMN date_created INTEGER UNSIGNED NOT NULL DEFAULT 1;

ALTER TABLE mantis_api_token_table MODIFY COLUMN date_used INTEGER UNSIGNED NOT NULL DEFAULT 1;

INSERT INTO mantis_config_table ( value, type, access_reqd, config_id, project_id, user_id ) VALUES ('209', 1, 90, 'database_version', 0, 0 );

Your database has not been created yet. Please create the database, then install the tables and data using the information above before proceeding.

grply69

grply69

2016-09-11 13:15

reporter   ~0053996

I had the same issue.
I tracked it down to the call to filter_ensure_valid_filters in core/filter_api.pĥp.
I think that when you have custom_fields in the database, this routine is trying to get the current user, finds then that there is no current one and redirects to the login page; at this point Mantis discovers that the database is not up to date and helpfully give a link to restart installation.
The sql way works but the filters are not correct as atrol pointed.
Another way to get rid of the problem is to delete from mantis_filters_table.
However, I had success in a complete upgrade with filters by forcing a current user so:

--- install.php.ori 2016-09-11 19:07:57.000000000 +0200
+++ install.php 2016-09-11 19:07:58.000000000 +0200
@@ -999,11 +999,14 @@
if( $t_sql ) {
$t_ret = $t_dict->ExecuteSQLArray( $t_sqlarray, false );
} else {

  • $suser = $g_cache_current_user_id;
  • $g_cache_current_user_id = 1;
    if( isset( $t_sqlarray[1] ) ) {
    $t_ret = call_userfunc( 'install' . $t_sqlarray[0], $t_sqlarray[1] );
    } else {
    $t_ret = call_userfunc( 'install' . $t_sqlarray[0] );
    }
  • $g_cache_current_user_id = $suser;
    }
    }
    echo '</td>';

it explains to Mantis that the current user is 1 (administrator) and this is enough to pass this hurdle.
Looking at the table values, the filters are at level 9 and look like JSON.
I am sure that this is not the correct fix, I just hope that this can lead a Mantis dev to a correct one.

atrol

atrol

2016-09-11 14:47

developer   ~0053997

Thanks @grply69,
indeed, your note helps to find the root cause of the issue.

atrol

atrol

2016-09-11 14:48

developer   ~0053998

Reminder sent to: dregad, vboctor

install_stored_filter_migrate() uses filter_ensure_valid_filter to migrate filters of older versions.

The upgrade process has to migrate all filters, which means filters from all projects and all users.
filter_ensure_valid_filter() calls helper_get_columns_to_view() which performs actions based on current project and current user.
There is no current user and no current project during upgrade process, thus we might see curious results.

Independant from that, it's a wrong concept at this place to use a function that depends on settings of a user for a project (view_issues_page_columns).

Maybe we should tell users not to upgrade to 1.3.x until this is fixed.

vboctor

vboctor

2016-09-26 23:39

manager   ~0054075

Looking at install_stored_filter_migrate(), I wonder if we would be OK upgrading the filter serialization format and renaming of fields, but skipping the following:

    # Ff the filter version does not match the latest version, pass it through filter_ensure_valid_filter to do any updates
    # This will set default values for filter fields
    if( $t_filter_arr['_version'] != FILTER_VERSION ) {
        $t_filter_arr = filter_ensure_valid_filter( $t_filter_arr );
    }

    :

    # We now have a valid filter in with updated version number (version is updated by filter_ensure_valid_filter)
    # Check that this is the case, to before storing the updated filter values.
    # Abort if the filter is invalid as this should not be possible
    if( $t_filter_arr['_version'] != FILTER_VERSION ) {
        return 1; # Fatal: invalid data found in filters table
    }

We generally should be executing ensure_valid_filter() on filters after loading them from the database, and hence doing this as upgrade time doesn't necessarily add much value. Also the adjustment of the filter seems to be based on context of project/user which will vary at runtime and based on modified access levels, and hence, it should be part of the apply-to-all generic db upgrade.

@atrol do you have a repro that you can validate with the above suggested change?

atroladmin

atroladmin

2016-09-27 05:44

administrator   ~0054078

@vboctor I don't have a testing environment where I can reproduce the issue.

atrol

atrol

2016-09-30 14:30

developer   ~0054100

There is a hint in forum that could explain some of the issues around stored_filter_migrate
https://www.mantisbt.org/forums/viewtopic.php?f=3&amp;t=24093#p60298

We had about 5000 entries in filters table. We deleted those that were linked to deleted projects and deleted users, leaving about 300 remaining filters.
After that, upgrade script could go on and finish succesfully.

cproensa

cproensa

2016-10-19 09:57

developer   ~0054276

Independant from that, it's a wrong concept at this place to use a function that depends on settings of a user for a project (view_issues_page_columns).

For the record, i corrected that approach in PR 862

atrol

atrol

2016-10-19 16:14

developer   ~0054283

For the record, i corrected that approach in PR 862

@cproensa I don't understand what you mean with it.
The problem is in function install_stored_filter_migrate, but I don't see a change of this function in your PR 862

cproensa

cproensa

2016-10-19 16:29

developer   ~0054284

I mean, the bit about filter_ensure_valid_filter:

filter_ensure_valid_filter() calls helper_get_columns_to_view() which performs actions based on current project and current user.
There is no current user and no current project during upgrade process, thus we might see curious results.

atrol

atrol

2016-10-19 16:51

developer   ~0054285

I don't see how the wrong concept is changed.
filter_ensure_valid_filter() does still perform actions based on current project and current user (calls of config_get)

cproensa

cproensa

2016-10-19 17:04

developer   ~0054286

hmm, ok
i removed the part about calls helper_get_columns_to_view()

cproensa

cproensa

2016-10-19 17:06

developer   ~0054287

(calls of config_get)
let's see if they shoud be config_get_global instead

atrol

atrol

2016-10-19 17:19

developer   ~0054288

let's see if they shoud be config_get_global instead
I don't think so.
You would remove existing functionality for real use cases.
e.g., an administrator might configure that users of a certain project should just be able to use simple filters.

ordina_beheer

ordina_beheer

2016-12-29 08:16

reporter   ~0054865

Happy to let you know that by the tips in this post a workaround could be found for us. After we deleted all the filters referring to disabled users, the upgrade went smooth:
delete from mantis_filters_table
where id in ( select f.id
from mantis_filters_table f
join mantis_user_table u
on u.id=f.user_id
where u.enabled=false
);

saadhaq

saadhaq

2016-12-31 22:33

reporter   ~0054883

Hi, I tried this SQL command, but it does not seem to work. I get an error saying "#1093 - You can't specify target table 'mantis_filters_table' for update in FROM clause". Is there something I am missing?

ordina_beheer

ordina_beheer

2017-01-02 08:52

reporter   ~0054895

This is a limitation of MySQL (I assume you're using that as database backend).
http://dev.mysql.com/doc/refman/5.6/en/update.html : "You cannot update a table and select from the same table in a subquery."
You could overcome this by eliminating the join with the mantis_filters_table, which is strictly not necessary (it merely evolved from a query I used to analyse the situation):
delete from mantis_filters_table where user_id in (select u.id from mantis_user_table u where u.enabled=false);
This was by the way a workaround that solved the problem for us, it might or might not work in your specific situation. Keep in mind that you're updating the database directly so make sure you have a proper backup and fallback strategy.

TomR

TomR

2017-01-28 09:09

reporter   ~0055383

Upgrade is hanging on duplicate key.

So when updaing manualy I changed the last line into:

INSERT INTO mantis_config_table ( value, type, access_reqd, config_id, project_id, user_id ) VALUES ('209', 1, 90, 'database_version', 0, 0 ) ON DUPLICATE KEY UPDATE value=209;

mbremer

mbremer

2017-04-20 03:41

reporter   ~0056630

Hi,

This bug has been hanging around for more than half a year without a proper solution. As a result I cannot upgrade several Mantis installations. Since I can replicate the issue I might be able to help, if I am pointed in the right direction. So far the solutions posted seem to indicate that some of the old filters in the database have an issue, but I don't see a real solution that works in all cases. Is it clear for the developers what is really causing the issue? Or is it just a problem to implement it properly in the upgrade script?
Like I said, I can replicate the issue and might give some help to resolve this.

atrol

atrol

2017-04-21 02:43

developer   ~0056635

Is it clear for the developers what is really causing the issue?

There is obviously a conceptual problem in the way how filter conversion is done.
But it's not clear if there is more than one issue and some more work has to be done, e.g. https://github.com/mantisbt/mantisbt/pull/1091#issuecomment-294347763

I can replicate the issue

@mbremer could you provide a database export?

mbremer

mbremer

2017-04-21 03:42

reporter   ~0056636

No problem to send a database export, but I don't want to upload it in a public area. Is there a way to send it privately?

atrol

atrol

2017-04-21 04:08

developer   ~0056637

Is there a way to send it privately?

You could upload it to a storage (Dropbox, Google Drive, Your FTP server, ...) and add a private note with a link to it.

atrol

atrol

2017-04-21 05:55

developer   ~0056639

@mbremer, I don't see any error when upgrading your database, see screen shot.
It seems you did not send me a database before trying to run the upgrade, but a database where upgrade has stopped before or during step 195.

mbremer-upgrade.PNG (35,215 bytes)   
mbremer-upgrade.PNG (35,215 bytes)   
atrol

atrol

2017-05-01 09:38

developer   ~0056747

@mbremer, still the same with your latest upload. I don't see any error when upgrading your database.
And also still the same:
It seems you did not send me a database before trying to run the upgrade, but a database where upgrade has stopped before or during step 195.
Check mantis_config_table entry database_version. The value is 194 before running the upgrade.

mbremer

mbremer

2017-05-01 09:50

reporter   ~0056748

Hi atrol, not sure what went wrong here. The database dump mantis_jwst_bak.sql should contain a dump with database_version 183. I just double checked this, it has indeed version 183 when I open the sql export file from my posted link.

atrol

atrol

2017-05-01 10:00

developer   ~0056749

it has indeed version 183

You mean 194?

mbremer

mbremer

2017-05-01 10:06

reporter   ~0056750

No, I see in the dump 183

atrol

atrol

2017-05-01 10:53

developer   ~0056751

Quite strange, seems something went wrong with my first download as there was no mantis_jwst_bak.sql on my system.
Maybe I used an older file.

I confirm there is version 183 in latest file, but I don't see any error when upgrading the database.

mbremer

mbremer

2017-05-01 11:06

reporter   ~0056752

Good to hear that you now have a dump with version 183. You've managed to upgrade without any issues, can you tell me which mantis version you are using for the upgrade?

atrol

atrol

2017-05-01 11:10

developer   ~0056753

I used a developer version, but I don't expect any difference when using latest 2.4.0.

mbremer

mbremer

2017-05-01 11:22

reporter   ~0056754

OK, when I tried it I used the 1.3.x branch. This could explain the difference. What I can do is check if the 2.4.0 release is doing its job when running the upgrade.

mbremer

mbremer

2017-05-02 05:15

reporter   ~0056758

I read the documentation and noticed that the latest Mantis installation requires PHP 5.5.x or higher. We are currently running PHP 5.3.x (as part of the Oracle Linux distribution), so I need to request an upgrade before I can try out Mantis 2.4.0 and report back if it works fine.

mbremer

mbremer

2017-05-05 02:52

reporter   ~0056776

I've tested the migration using the v2.4.0 release on an Oracle Linux 7 distribution with a proper version of PHP and I can confirm that the complete upgrade works fine! It does all the steps correctly without any errors. :)

Just to make it clear for everybody, it was an upgrade from Mantis 1.2.19 (database version 183) to 2.4.0 (database version 209).

dregad

dregad

2017-05-05 03:58

developer   ~0056777

@mbremer that's good to hear, thanks for the feedback !

wniva

wniva

2017-05-31 04:08

reporter   ~0057000

I tried upgrading from 1.2.19 to 2.4.1 today.
This led to the mistake:
"INSERT INTO mantis_config_table ( value, type, access_reqd, config_id, project_id, user_id ) VALUES ('209', 1, 90, 'database_version', 0, 0 );
Your database has not been created yet. Please create the database, then install the tables and data using the information above before proceeding."

In the table mantis_config_table, there is already an entry for the primary key (config_id, project_id, user_id = 'database_version', 0, 0) with the value 183 !

System:
Windows 8.1. Prof
Apache 2.4.25 (Win64)
PHP 5.6.30

wniva

wniva

2017-05-31 05:32

reporter   ~0057001

Sorry.
I had "Print SQL Queries instead of Writing to the Database " activated. Without this the update run through (then DB version 209).