Dependency Graph

Dependency Graph
related to related to child of child of duplicate of duplicate of

View Issue Details

IDProjectCategoryView StatusLast Update
0034143mantisbtdb mysqlpublic2024-04-14 07:28
Reportervictorespina Assigned Toatrol  
PrioritynormalSeveritycrashReproducibilityalways
Status closedResolutionunable to reproduce 
Product Version2.26.1 
Summary0034143: MySQL 8 won't accept time() values for DATETIME columns
Description

Fresh install on a shared hosting server with the following environment:

PHP v8.0.28
Apache 2.4.6 (CentOS)
MySQL 8.0.26-17

When trying to register a new user the INSERT on the users table failed because the date_created column won't accept values like '1711512841'. Following the stack trace the problem seems to be caused by MySQL v8 not accepting values for DATETIME columsn that comes from the php time() function in db_now() .

Steps To Reproduce
  1. Create a fresh install using PHP 8 & MySQL 8
  2. Register a new user
Additional Information
APPLICATION ERROR #401

Database query failed. Error received from database was #1292: Incorrect datetime value: '1710906367' for column 'date_created' at row 1 for the query: INSERT INTO mantis_user_table
( username, email, password, date_created, last_visit,
enabled, access_level, login_count, cookie_string, realname )
VALUES
( ?, ?, ?, ?, ?,
?,?,?,?, ?).

Please use the "Back" button in your web browser to return to the previous page. There you can correct whatever problems were identified in this error or select another action. You can also click an option from the menu bar to go directly to a new section.
Detailed error information
Full path: /data/11/0/133/105/785920/user/804992/htdocs/mantis/core/classes/DbQuery.class.php
Line number: 301
Stack trace
#   Filename    Line    Class   Type    Function    Args
0   /data/11/0/133/105/785920/user/804992/htdocs/mantis/core/classes/DbQuery.class.php  301 -   -   trigger_error   <string>'401', <integer>256
1   /data/11/0/133/105/785920/user/804992/htdocs/mantis/core/classes/DbQuery.class.php  796 DbQuery ->  db_execute  <integer>-1, <integer>-1
2   /data/11/0/133/105/785920/user/804992/htdocs/mantis/core/database_api.php   318 DbQuery ::  compat_db_query <string>'INSERT INTO {user} ( username, email, password, date_created, last_visit, enabled, access_level, login_count, cookie_string, realname ) VALUES ( ?, ?, ?, ?, ?, ?,?,?,?, ?)', <array> { [0] => 'victorespina', [1] => 'vespinas@gmail.com', [2] => '8508d5e7c00720979aaf232c9846408b', [3] => 1710906367, [4] => 1710906367, [5] => true, [6] => 25, [7] => 0, [8] => 'Pyx_tA4o1Oxy62cOzpSxpRiyBQLX3K4XoAto7YkvkQBojW8PsnxrcYzR1oprRh0j', [9] => '' }, <integer>-1, <integer>-1, <boolean>true
3   /data/11/0/133/105/785920/user/804992/htdocs/mantis/core/user_api.php   675 -   -   db_query    <string>'INSERT INTO {user} ( username, email, password, date_created, last_visit, enabled, access_level, login_count, cookie_string, realname ) VALUES ( ?, ?, ?, ?, ?, ?,?,?,?, ?)', <array> { [0] => 'victorespina', [1] => 'vespinas@gmail.com', [2] => '8508d5e7c00720979aaf232c9846408b', [3] => 1710906367, [4] => 1710906367, [5] => true, [6] => 25, [7] => 0, [8] => 'Pyx_tA4o1Oxy62cOzpSxpRiyBQLX3K4XoAto7YkvkQBojW8PsnxrcYzR1oprRh0j', [9] => '' }
4   /data/11/0/133/105/785920/user/804992/htdocs/mantis/core/user_api.php   743 -   -   user_create <string>'victorespina', <string>'aE8WjzeLE2IrDMo9', <string>'vespinas@gmail.com', <integer>25
5   /data/11/0/133/105/785920/user/804992/htdocs/mantis/signup.php  84  -   -   user_signup <string>'victorespina', <string>'vespinas@gmail.com'
TagsNo tags attached.

Relationships

related to 0034142 closeddregad Class "VBoctor\Email\DisposableEmailChecker" not found 

Activities

atrol

atrol

2024-03-20 03:10

developer   ~0068672

Fresh install on a shared hosting server

Please provide step by step instructions how you installed.
Especially how the database was created. (done by the installer, or some manual steps)
The type of the date_created column should not be DATETIME after a fresh install.

victorespina

victorespina

2024-03-20 10:05

reporter   ~0068675

Last edited: 2024-03-20 10:06

  1. Downloaded install package from SourceForge
  2. Extracted files locally (my hosting doesn't allow to expand archive files)
  3. Uploaded all files to /mantis folder inside the web server's document folder
  4. Pointed my browser to http://mydomain.com/mantis/
  5. Install script got executed
  6. Filled in the required setup info. Target database was already created and the dbuser has enough privilegies to create all required objets on the database.
  7. Pressed on Install/Upgrade. Process completed normally.
  8. Moved config_inc.php to config/config_inc.php (I edited it to activate extended error report)
  9. Pointed my browser to http://mydomain.com/mantis/ and login page came up
  10. Pressed on SIGNUP button and filled the signup form including captcha.
  11. Pressed SIGNUP button to complete the signup process
  12. Error 401 when performing INSERT into the users table complaining about the value provided for date_created column (a timestamp numeric value).
victorespina

victorespina

2024-03-20 10:09

reporter   ~0068676

On a side note, I checked the users table structure on my database and it was created using DATETIME data type. I then modified the db_now() function from core/database_api.php to return a valida date string instead of datetime and then the error raised again further in the signup process caused by the same situation (a timestamp value being used as value for a DATETIME column).

victorespina

victorespina

2024-03-20 11:04

reporter   ~0068677

Last edited: 2024-03-20 11:12

I traced the database schema creation routine for the users table in admin/schema.php (400) and the datatype assigned is "T". I then traced the actual table creation routine at vendor/adodb/adodb-php/adodb-datadict.inc.php, and the actualType() function is calling the customMetaTypes array on the current MySQL connection to get the actual MySQL datatype. That array is defined on vendor/adodb/adodb-php/datadict/datadict-mysql.inc.php and it seems that the "T" datatype is being translated to MySQL DATETIME datatype (line 126) instead of TIMESTAMP.

I changed that file to force a TIMESTAMP datatype for T value and then rerun a clean install and this time the DATE_CREATED column on the user table was indeed created as TIMESTAMP, but the problem persisted: the date_created column doesn't accept a time() value as a valid value.

atrol

atrol

2024-03-20 16:50

developer   ~0068678

Do not change any source code of MantisBT!
This will introduce other side effects.
As told before, the type of the date_created column should not be DATETIME after a fresh install.
This is the root cause of your issue that must be fixed first.

The installer creates in a first step column date_created (and some more) as DATETIME, but later on, all DATETIME colums are converted to INT.
E.g. for date_created column this is done in install/schema.php line

$g_upgrade[120] = array( 'UpdateFunction', 'date_migrate', array( db_get_table( 'user' ), 'id', array( 'last_visit', 'date_created' ), array( 'last_visit_int', 'date_created_int' ) ) );

Finally the migration is done during installation via function install_date_migrate in file core/install_helper_functions_api.php

You should try to find out why this function get's not executed.

victorespina

victorespina

2024-03-20 18:55

reporter   ~0068679

Ok I will check that code to see what can I find and I will let you know. I will revert all my changes to the original code (except the ones to avoid the missing DisposableEMailChecker class).

victorespina

victorespina

2024-03-20 19:59

reporter   ~0068680

Last edited: 2024-03-22 05:42

Ok, I cleared my mantis database and retried the install process, but now I checked the "Print SQL Queries instead of Writing to the Database" option so I could see all the commands that were going to be sent to the database. There I could see the upgrade commands for date_created column:

-- Schema step 121
ALTER TABLE mantis_user_table DROP COLUMN date_created;

-- Schema step 122
ALTER TABLE mantis_user_table CHANGE COLUMN date_created_int date_created INTEGER UNSIGNED NOT NULL DEFAULT 1;

I copied the script and ran it on the database and first time it fails because an index being created twice. Step 159 creates this index:

-- Schema step 159
ALTER TABLE mantis_bug_revision_table ADD  INDEX idx_bug_rev_id_time  (bug_id, timestamp);

but that index was already created on step 85. I checked schema.php and found that step 154 seems to DROP that index before being recreated:

$g_upgrade[154] = array( 'CreateIndexSQL', array( 'idx_bug_rev_id_time', db_get_table( 'bug_revision' ), 'bug_id, timestamp', array( 'DROP' ) ), array( 'db_index_exists', array( db_get_table( 'bug_revision' ), 'idx_bug_rev_id_time' ) ) );

but that step is being deemed as "no operation" when the install process run:

-- Schema step 154
-- No operation

I modified the script , ran it again and this time it worked perfectly and now the app seems to be working normally. I tried to find the reason why those steps seems to not being sent when the installer runs in the normal way but its some heavy code and I couldn't find anything obvious at first sight.

atrol

atrol

2024-03-21 10:44

developer   ~0068683

Last edited: 2024-03-21 11:52

According 0034142:0068682 you are obviously not using the original complete MantisBT source code.
Please try again from scratch and let us know, if you are able to install without any manual tweaks of the code.

dregad

dregad

2024-03-21 14:10

developer   ~0068685

Sounds like the same root cause as 0034142:0068682...

victorespina

victorespina

2024-03-21 15:33

reporter   ~0068686

Yes, it mat be. I will do a full install in another folder, makes sure everything was copied and let you know how it went.

victorespina

victorespina

2024-03-21 21:28

reporter   ~0068687

I just downloaded the official installer again, uploaded to a new folder, ran the installation but the datetime columns problem persists. For some reason the script fails to update those columns datatype, and this time I made sure all files were copied.

Let me know if there is something I can check or do to help you with this problem.

dregad

dregad

2024-03-23 05:15

developer   ~0068690

This has been working flawlessly for many years, so I'm pretty sure that whatever the cause, the problem is probably specific to your environment.

The installer works in steps, and historically timestamp columns were defined as DATETIME, and later on we switched to storing them as INT, resulting in additional schema steps to create a new column, migrate the data and drop the old column. It sounds like the install process did not go all the way through, despite your statement that Pressed on Install/Upgrade. Process completed normally.

For some reason the script fails to update those columns datatype

Can you find out why it's failing ?

atrol

atrol

2024-03-23 11:17

developer   ~0068691

Check your web server / PHP / MySQL logs for errors and warnings.
You might not be able to do this, as according 0034142:0068674 your PHP is currently not configured to log or report errors.

victorespina

victorespina

2024-03-23 12:07

reporter   ~0068692

Ok, I will try again when I have some time to spare. Unfortunately, this is a shared hosting and a very limited one, so I'm not sure if I will be able to access php or mysql internal logs. I am sure you are right and this is a problem somehow related to my environment. For the time being, my installation is working ok (after I created the database using the script generated by the installer and ignored the dup CREATE INDEX issue). Great software, BTW.

atrol

atrol

2024-03-23 12:35

developer   ~0068693

Thanks for the feedback.
Setting to resolved for now.
Feel free to reopen if you should be able to provide the requested information.