View Issue Details
| ID | Project | Category | View Status | Date Submitted | Last Update |
|---|---|---|---|---|---|
| 0034143 | mantisbt | db mysql | public | 2024-03-20 00:35 | 2024-04-14 07:28 |
| Reporter | victorespina | Assigned To | atrol | ||
| Priority | normal | Severity | crash | Reproducibility | always |
| Status | closed | Resolution | unable to reproduce | ||
| Product Version | 2.26.1 | ||||
| Summary | 0034143: 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 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 |
| ||||
| Additional Information | | ||||
| Tags | No tags attached. | ||||
Please provide step by step instructions how you installed. |
|
|
|
|
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). |
|
|
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. |
|
|
Do not change any source code of MantisBT! 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.
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. |
|
|
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). |
|
|
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:
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:
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:
but that step is being deemed as "no operation" when the install process run:
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. |
|
|
According 0034142:0068682 you are obviously not using the original complete MantisBT source code. |
|
|
Sounds like the same root cause as 0034142:0068682... |
|
|
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. |
|
|
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. |
|
|
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.
Can you find out why it's failing ? |
|
|
Check your web server / PHP / MySQL logs for errors and warnings. |
|
|
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. |
|
|
Thanks for the feedback. |
|