View Issue Details
ID | Project | Category | View Status | Date Submitted | Last Update |
---|---|---|---|---|---|
0026109 | mantisbt | db postgresql | public | 2019-09-03 12:36 | 2019-12-09 04:31 |
Reporter | mereo | Assigned To | dregad | ||
Priority | normal | Severity | minor | Reproducibility | always |
Status | closed | Resolution | fixed | ||
Product Version | 2.0.0-beta.1 | ||||
Target Version | 2.23.0 | Fixed in Version | 2.23.0 | ||
Summary | 0026109: check_pgsql_bool_columns: check wrongly suggests that the redirect_delay should be in boolean format | ||||
Description | With PostgreSQL, during installation, a check of some columns is performed (columns in 'numeric' format instead of boolean). See also 0021624 | ||||
Tags | No tags attached. | ||||
Thanks, good catch ! |
|
@mereo In theory, userpref.redirect_delay should be an integer at the end of the install process:
Since the pgsql conversion occurs before executing the schema upgrade steps, having the column as boolean could only occur in some corner cases. I'd like to make sure I have all bases covered for the fix - changing the code in install_helper_functions_api.php is one thing, but I'd like to confirm whether an extra fix is required in the installer code itself. |
|
The use case was to move from a mysql database to postgresql, then apply the mantis update from version 1.2.0 (db 183) to version 2.21.1 (db 209). I suspect "boolean" columns in mysql are represented by an integer type. This could explain why when updating mantis version, I got this alert message on these columns and not before. In anyway, I always think the check wrongly suggests that the redirect_delay column in user_pref table should be in boolean format. |
|
How did you actually perform this move ? Dump schema as SQL script and import that on the other end ? Any transformations in between ? In any case, as requested in my earlier note it would be good to have the following details about the
A couple more things you could try:
That's correct. ADOdb type
I did not mean to say that it was incorrect. Indeed I agree that it is wrong. What I'm trying to do is having a clear and repeatable scenario to reproduce the issue, so I can properly test and confirm that the fix is correct. |
|
@mereo it would be great if you could answer the questions in my earlier note 0026109:0062744 . |
|
I used pgLoader (https://pgloader.io/) with the following configuration: ` CAST type int when (= precision 10) to int4 drop typemod, See also other default casting rules : https://pgloader.readthedocs.io/en/latest/ref/mysql.html#default-mysql-casting-rules Notice: a MySql tinyint field is tranformed to a Postgresql boolean field.
yes - standard 1.2.0 MySQL schema 183 here
yes - 'non-standard' pgsql 1.2.0 schema 183
yes - 2.21.1 pgsql schema 209 I compared the database schemas between a new 1.2.0 PostgreSQL installation and the schema produced by the 1.2.0 MySQL to PostgreSQL migration (pgLoader migration).
Notice mantis_user_pref_table.redirect_delay is in the both cases an integer. I compared the database schemas between a new 2.21.1 PostgreSQL installation and the schema produced by the 1.2.0 MySQL to PostgreSQL + update to 2.21.1 migration (pgLoader migration + mantis update).
Notice mantis_user_pref_table.redirect_delay is in the both cases an integer. At this step, the update procedure indicates a possible error and propose to change to boolean the fields :
|
|
@mereo thanks for the detailed feedback.
Correct, the NOT NULL attribute was removed in 1.3.0 (schema step 203 & 204)
Correct, these columns were initially created as unsigned int, and were later (1.3.0-beta.1) converted to boolean (schema steps 188 & 189) That being said, with your indications I was able to reproduce the problem now, so I'll perform additional testing to ensure all cases are covered. |
|
Changing product version, because the issue actually exists since 1.3.0-beta.1 when the data_type check was introduced. |
|
PR https://github.com/mantisbt/mantisbt/pull/1563 Testing preparation:
Testing process for the code in the PR:
|
|
@mereo it would be great if you could test the PR and confirm it fixes the problem. Thanks in advance ! |
|
MantisBT: master 393bca8e 2019-09-04 02:29 Details Diff |
Install: redirect_delay is integer, not boolean check_pgsql_bool_columns() function incorrectly categorized column 'redirect_delay' in user_pref table as boolean, when in fact it it an integer (see issue 0016392). This prevents users from having a redirect delay > 1s on PostgreSQL. A follow-up fix will be required to ensure that the underlying column is indeed integer as expected by MantisBT. Fixes 0026109 |
Affected Issues 0016392, 0026109 |
|
mod - core/install_helper_functions_api.php | Diff File | ||
MantisBT: master 25110fd3 2019-09-13 13:11 Details Diff |
Convert redirect_delay column back to integer Follow-up fix for user_pref.redirect_delay, which was incorrectly set to boolean in check_pgsql_bool_columns() before MantisBT 2.23.0, so we need to check its type and convert it back to integer if needed. Fixes 0026109 |
Affected Issues 0026109 |
|
mod - admin/install.php | Diff File | ||
mod - core/install_helper_functions_api.php | Diff File |