View Issue Details

IDProjectCategoryView StatusLast Update
0020357mantisbtdb schemapublic2016-06-12 00:43
ReporteratrolAssigned Todregad 
PrioritynormalSeveritymajorReproducibilityalways
Status closedResolutionfixed 
Product Version1.3.0-rc.1 
Target Version1.3.0-rc.2Fixed in Version1.3.0-rc.2 
Summary0020357: Admin checks for UTF-8 collation fail
Description

Run admin/check/index.php

Get
Text column config_id of type varchar(64) on table mantis_config_table is is using UTF-8 collation FAIL
Text column basename of type varchar(40) on table mantis_plugin_table is is using UTF-8 collation FAIL
Text column upgrade_id of type char(20) on table mantis_upgrade_table is is using UTF-8 collation FAIL

Show verbose error messages gives a lot of notices like:
SYSTEM NOTICE: Undefined index: comment
Raised in file /srv/www/bugs/admin/check/check_database_inc.php on line 382
SYSTEM NOTICE: Undefined index: name
Raised in file /srv/www/bugs/admin/check/check_database_inc.php on line 383

TagsNo tags attached.

Relationships

related to 0020365 closeddregad SYSTEM NOTICE: Undefined index with mysqli driver 

Activities

dregad

dregad

2015-12-08 11:26

developer   ~0052078

I think this is actually 2 distinct issues. Follow-up with SYSTEM NOTICEs in 0020365.

atrol

atrol

2015-12-08 11:48

developer   ~0052080

Last edited: 2015-12-08 11:51

View 2 revisions

Sorry, no time to have a deeper look at it.
Just some thoughts I had when looking at the code that might be related to the issue.

Most of the 'CreateTableSQL' entries in schema.php have this parameter
array('mysql' => 'ENGINE=MyISAM DEFAULT CHARSET=utf8', 'pgsql' => 'WITHOUT OIDS')
At least the latest new entry for api_token is missing this.
Thus the new table might be created using another engine and/or another charset than the other tables .

array('mysql', ...) might mean just mysql but not mysqli.
mysqli is our new default in master, maybe the engine and charset options are not considered.

While I am looking at it
ENGINE=MyISAM is questionable.
We should not hardcode any engine but use the setting that the database administrator uses and/or ask for the engine in installer.

dregad

dregad

2015-12-09 03:11

developer   ~0052083

ENGINE=MyISAM is questionable.
We should not hardcode any engine but use the setting that the database
administrator uses and/or ask for the engine in installer.

I agree. In fact I vaguely remember having a discussion about this before, maybe with grangeway.

InnoDB used to have issues back then, so I assume MyISAM was forced to avoid those, but now InnoDB is the default engine so it does not make sense not to use it.

Anyway this needs careful thinking and planning before we change it.

vboctor

vboctor

2015-12-26 20:12

manager   ~0052185

@dregad, did you get a chance to think about this one? Lets assess impact on the 1.3 stable release.

dregad

dregad

2015-12-29 12:53

developer   ~0052206

array('mysql', ...) might mean just mysql but not mysqli.

In this context 'mysql', refers to the underlying ADOdb data dictionary driver, which actually covers both mysql and mysqli "client" drivers.

did you get a chance to think about this one?

I'm actually working on a patch. I think changing the engine for new installations should be pretty straightforward and transparent. Older, upgraded installations might end up with a mix&match of MyISAM and InnoDB tables in the database which should not cause any issues but is not considered best practice.

Probably the best would be to add an admin check to detect each table's DB engine and let the admin change them after the fact.

It's also worth mentioning that InnoDB has a few limitations/drawbacks:

  • no full-text search, until MySQL 5.6.4 (not an issue for us AFAICT)
  • requires more tuning effort (not likely to be a problem unless the DB is very large; and not really our problem in any case - that's under the administrator's responsibility)
  • shorter index length (767 chars vs 1000 in MyISAM).

The last one is a concern for us as it may lead to issues with some of our indexes, especially if we switch to utf8mb4 character set (see 0020431). Need further investigation and testing on this.

dregad

dregad

2016-01-01 19:25

developer   ~0052231

Pull request https://github.com/mantisbt/mantisbt/pull/699

dregad

dregad

2016-01-16 18:00

developer   ~0052322

In the end I merged this fix independently from PR 699, as its primary purpose is to support utf8mb4, which is not blocking for 1.3.0 release.

Related Changesets

MantisBT: master 448ab2f1

2015-12-29 12:32:00

dregad

Details Diff
Do not specify MyISAM engine when creating tables

When creating tables in a MySQL database, the installer will now use
whatever engine is set as default on the DB server.

Fixes 0020357
mod - admin/schema.php Diff File

Issue History

Date Modified Username Field Change
2015-12-07 17:32 atrol New Issue
2015-12-07 17:44 atrol Summary Some admin checks fail => Admin checks for UTF-8 collation fail
2015-12-08 11:18 dregad Issue cloned: 0020365
2015-12-08 11:26 dregad Note Added: 0052078
2015-12-08 11:48 atrol Note Added: 0052080
2015-12-08 11:51 atrol Note Edited: 0052080 View Revisions
2015-12-09 03:11 dregad Note Added: 0052083
2015-12-26 20:12 vboctor Note Added: 0052185
2015-12-29 12:26 dregad Relationship added related to 0020365
2015-12-29 12:53 dregad Note Added: 0052206
2015-12-29 12:54 dregad Assigned To => dregad
2015-12-29 12:54 dregad Status new => assigned
2016-01-01 19:25 dregad Note Added: 0052231
2016-01-16 17:40 dregad Changeset attached => MantisBT master 448ab2f1
2016-01-16 17:40 dregad Status assigned => resolved
2016-01-16 17:40 dregad Resolution open => fixed
2016-01-16 17:40 dregad Fixed in Version => 1.3.0-rc.2
2016-01-16 18:00 dregad Note Added: 0052322
2016-06-12 00:43 vboctor Status resolved => closed