View Issue Details

IDProjectCategoryView StatusLast Update
0021678mantisbtupgradepublic2017-02-01 22:47
ReporterTomR 
Assigned To 
PrioritynormalSeverityminorReproducibilityhave not tried
Status newResolutionopen 
Product Version 
Target Version1.3.7Fixed in Version 
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.

Relationships

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&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;

Issue History

Date Modified Username Field Change
2016-09-08 09:51 TomR New Issue
2016-09-08 16:51 atrol Status new => feedback
2016-09-08 16:51 atrol Note Added: 0053984
2016-09-08 17:42 dvzrv Note Added: 0053986
2016-09-11 13:15 grply69 Note Added: 0053996
2016-09-11 14:47 atrol Note Added: 0053997
2016-09-11 14:48 atrol Note Added: 0053998
2016-09-26 23:39 vboctor Note Added: 0054075
2016-09-27 05:44 atroladmin Note Added: 0054078
2016-09-30 14:25 atrol Target Version => 1.3.2
2016-09-30 14:30 atrol Note Added: 0054100
2016-10-02 19:16 dregad Target Version 1.3.2 => 1.3.3
2016-10-19 09:57 cproensa Note Added: 0054276
2016-10-19 16:14 atrol Note Added: 0054283
2016-10-19 16:29 cproensa Note Added: 0054284
2016-10-19 16:51 atrol Note Added: 0054285
2016-10-19 17:04 cproensa Note Added: 0054286
2016-10-19 17:06 cproensa Note Added: 0054287
2016-10-19 17:19 atrol Note Added: 0054288
2016-10-30 23:23 vboctor Target Version 1.3.3 => 1.3.4
2016-11-27 08:22 dregad Target Version 1.3.4 => 1.3.5
2016-12-29 08:16 ordina_beheer Note Added: 0054865
2016-12-30 16:24 atrol Target Version 1.3.5 => 1.3.6
2016-12-31 22:33 saadhaq Note Added: 0054883
2017-01-02 08:52 ordina_beheer Note Added: 0054895
2017-01-05 16:47 atrol Relationship added related to 0022118
2017-01-28 09:09 TomR Note Added: 0055383
2017-01-28 09:09 TomR Status feedback => new
2017-02-01 22:47 vboctor Target Version 1.3.6 => 1.3.7