View Issue Details

IDProjectCategoryView StatusLast Update
0026109mantisbtdb postgresqlpublic2019-09-13 17:59
Reportermereo Assigned Todregad  
PrioritynormalSeverityminorReproducibilityalways
Status assignedResolutionopen 
Product Version2.0.0-beta.1 
Target Version2.23.0 
Summary0026109: 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).
This check wrongly suggests that the redirect_delay column in user_perf table should be in boolean format.

See https://github.com/mantisbt/mantisbt/blob/380fc71029341faae4cbe8dee181be28ba124031/core/install_helper_functions_api.php#L124

See also 0021624

TagsNo tags attached.

Relationships

related to 0021624 closeddregad mantis_user_pref_table.redirect_delay should not be a boolean (broken Account Preferences) 

Activities

dregad

dregad

2019-09-04 03:21

developer   ~0062721

Thanks, good catch !

dregad

dregad

2019-09-04 07:12

developer   ~0062722

@mereo
Just to clarify your install/upgrade process, as I don't have access to a pgsql instance for testing now... Was this a fresh install of 2.21.3, or are you coming from an older Mantis ? If so, which Mantis version / schema version) ? A desc mantis_user_pref_table before and after installation may be helpful

In theory, userpref.redirect_delay should be an integer at the end of the install process:

  1. created as type L(boolean on pgsql) at step 43 (Mantis 1.0.0)
  2. converted to type I (integer) at step 65 (Mantis 1.2.0a2)

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.

mereo

mereo

2019-09-06 04:54

reporter   ~0062743

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.
Thaht's probably what you call the corner case.

In anyway, I always think the check wrongly suggests that the redirect_delay column in user_pref table should be in boolean format.

dregad

dregad

2019-09-06 05:21

developer   ~0062744

move from a mysql database to postgresql

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 userpref.redirect_delay column, at all steps of your process

  • Before start (I expect standard 1.2.0 MySQL schema 183 here)
  • After migration to PostgreSQL but before running the upgrade process (possibly a non-standard pgsql 1.2.0 schema 183 at that point)
  • After completing the upgrade (2.21.1 pgsql schema 209)

A couple more things you could try:

  1. fresh MantisBT 1.2.0 install on PostgreSQL (assuming that works, as pgsql support in 1.2.x branch had some issues) and compare that schema with the one you migrated from MySQL.
  2. migrate to 2.21.1 on MySQL side, and move to PostgreSQL afterwards, and again compare the schema with a fresh 2.21.1 install on PostgreSQL.

I suspect "boolean" columns in mysql are represented by an integer type.

That's correct. ADOdb type L (boolean) columns which we use for yes/no values are created as tinyint on MySQL.

I always think the check wrongly suggests that the redirect_delay column in user_pref table should be in boolean format.

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.

dregad

dregad

2019-09-11 08:59

developer   ~0062765

@mereo it would be great if you could answer the questions in my earlier note 0026109:0062744 .

mereo

mereo

2019-09-11 09:59

reporter   ~0062767

How did you actually perform this move ? Dump schema as SQL script and import that on the other end ? Any transformations in between ?

I used pgLoader (https://pgloader.io/) with the following configuration:

`
LOAD DATABASE
FROM mysql://login:password@hostMysql/mantis1.2.0
INTO postgresql://login:password@hostPostgresql/mantis1.2.0

CAST type int when (= precision 10) to int4 drop typemod,
type int when (= precision 11) to integer drop typemod,
type int with extra auto_increment when (= precision 10) to serial drop typemod,
type int with extra auto_increment when (= precision 11) to serial drop typemod,
type longblob to bytea drop typemod using byte-vector-to-bytea,
type tinyint when (= precision 4) to boolean drop typemod using tinyint-to-boolean
;`

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.

Before start (I expect standard 1.2.0 MySQL schema 183 here)

yes - standard 1.2.0 MySQL schema 183 here

After migration to PostgreSQL but before running the upgrade process (possibly a non-standard pgsql 1.2.0 schema 183 at that point)

yes - 'non-standard' pgsql 1.2.0 schema 183

After completing the upgrade (2.21.1 pgsql schema 209)

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).
The 2 schemas are the same except on columns:

  • mantis_bug_file_table.content
  • mantis_project_file_table.content
    With a new 1.2.0 PostgreSQL installation, these 2 columns are nullable (not 'NOT NULL').
    Probably a new MySQL installation defines these fields as not null.

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).
The 2 schemas are the same except on my previous point and:

  • mantis_project_hierarchy_table.inherit_parent
  • mantis_project_table.inherit_global
    Probably a new MySQL installation defines these fields as integers instead of tinyint (aka MySql boolean).

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 :

  • mantis_user_pref_table.redirect_delay fied - wrong
  • mantis_project_hierarchy_table.inherit_parent - OK
  • mantis_project_table.inherit_global - OK
dregad

dregad

2019-09-13 07:47

developer   ~0062790

@mereo thanks for the detailed feedback.

mantis_bug_file_table.content mantis_project_file_table.content
With a new 1.2.0 PostgreSQL installation, these 2 columns are nullable (not 'NOT NULL').
Probably a new MySQL installation defines these fields as not null.

Correct, the NOT NULL attribute was removed in 1.3.0 (schema step 203 & 204)

mantis_project_hierarchy_table.inherit_parent
mantis_project_table.inherit_global
Probably a new MySQL installation defines these fields as integers instead of tinyint (aka MySql boolean).

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.

dregad

dregad

2019-09-13 17:38

developer   ~0062795

Changing product version, because the issue actually exists since 1.3.0-beta.1 when the data_type check was introduced.

dregad

dregad

2019-09-13 17:59

developer   ~0062796

PR https://github.com/mantisbt/mantisbt/pull/1563

Testing preparation:

  • a 1.2.0 reference database was generated following the process described earlier (hereafter called DB-0).
  • DB-0 was upgraded with code from master branch MantisBT master c73fd8b0 --> redirect_delay was converted to bool, as reported in this issue (we'll call this one DB-1)

Testing process for the code in the PR:

  1. upgrade DB-0 --> redirect_delay is no longer converted to boolean pass
  2. run installer on DB-1 --> redirect_delay is converted from boolean to integer pass
  3. re-run the installer on DB upgraded in step 2 --> check passes (redirect_delay is integer), no conversion pass
  4. install MantisBT from scratch --> redirect_delay is created as integer pass
  5. set MANTIS_VERSION to 2.24.0 and run the installer --> check and conversion is not executed pass

Issue History

Date Modified Username Field Change
2019-09-03 12:36 mereo New Issue
2019-09-04 03:08 dregad Relationship added related to 0021624
2019-09-04 03:21 dregad Assigned To => dregad
2019-09-04 03:21 dregad Status new => assigned
2019-09-04 03:21 dregad Note Added: 0062721
2019-09-04 07:12 dregad Status assigned => feedback
2019-09-04 07:12 dregad Note Added: 0062722
2019-09-06 04:54 mereo Note Added: 0062743
2019-09-06 04:54 mereo Status feedback => assigned
2019-09-06 05:21 dregad Note Added: 0062744
2019-09-11 08:59 dregad Status assigned => feedback
2019-09-11 08:59 dregad Note Added: 0062765
2019-09-11 09:59 mereo Note Added: 0062767
2019-09-11 09:59 mereo Status feedback => assigned
2019-09-13 07:47 dregad Note Added: 0062790
2019-09-13 17:38 dregad Product Version 2.21.3 => 2.0.0-beta.1
2019-09-13 17:38 dregad Target Version => 2.23.0
2019-09-13 17:38 dregad Note Added: 0062795
2019-09-13 17:59 dregad Note Added: 0062796