MariaDB - Database Sync Deletes Field (v2024)

PHPMaker General Discussion (PHPMaker)
1

With v2024.16 when I sync to the database I get:

#OrderHistory

CHANGED : (Field) OrderHistory->OrderDate - NativeDataType (7 to -1)

This just appears to delete the field in the PHPMaker project.

In PHPMyAdmin it appears the data type of the field is “timestamp /* mariadb-5.3 */”
Any ideas how to fix this?

2

You need to upgrade your MariaDB database.

v2024 uses DBAL 3 which supports MariaDB 10.2.7 and newer, see system requirements.

v2025 uses DBAL 4 which supports MariaDB 10.4.3 and newer, see system requirements.

3

The database server is MariaDB v10.11.8.

And it appears to be a known issue whenever creating a view with UNION ALL.

Both tables it pulls from are regular timestamps but UNION ALL always results in “timestamp /* mariadb-5.3 */” even when trying to explicitly cast it to a regular timestamp. Can PHPMaker support this data type?

4
  1. Data type name with comment will not be properly detected,
  2. If you are using MariaDB v10, you better try altering the table and make the field name correct again (i.e. “timestamp” without comment). You can google and easily find related info. It might be some leftover from older version.
5

It seems no matter what I do the comment is always added to the view field data type when using UNION (doesn’t have to be UNION ALL).

As for Google solutions:
I’ve tried casting to timestamp explicitly, casting to datetime, casting to string and then to timestamp/datetime and even doing all of the above from a separate view selecting from the UNION view without using UNION. I’ve also made sure the original fields are regular timestamps and that the table/field collation matches.

6

You may post your schema for testing and discussion:

  1. Table schema (INSERT statements),
  2. SQL of your view (with UNION or UNION ALL and casting to timestamp).
7

Before all that.

Can I just save it as a string but have PHPMaker treat it as a date in some manner?

8

It depends on what you meant by “save it as a string”. Did you mean casting the timestamp field to varchar? If yes, you may, since it is a view with UNION, you cannot update it anyway. PHPMaker will not “treat it as a date” because you have casted it to varchar, it will be treated as string. You better cast it to varchar in your date format in the view directly.

9

After much research I’ve found this became an issue due to updating MariaDB to a new version when changing servers, which is why I didn’t have the issue before.

Basically the version of MariaDB I use (v10.11.8) always adds the comment /* mariadb-5.3 */ to timestamp field types when using UNION statements for internal compatibility reasons. While I’m not positive I believe future versions of MariaDB do this as well.

So to maintain full compatibility with MariaDB going forward, PHPMaker needs to be coded to ignore these comments. Generating the site using an old database on an old version of MariaDB works fine, since that comment isn’t in the field type, and if I use that generated site with the new database on the new version of MariaDB it works fine too. It’s just that I cannot do a database sync with the new database because the new version of MariaDB adds that comment which PHPMaker doesn’t know how to handle.

Either it needs to ignore these comments, or there needs to be a way to have PHPMaker cast fields to a different type at runtime, like cast VARCHAR back to TIMESTAMP.

Until then I cannot use UNION statements.

10

Bumping this because of how much it affects my project.

It does appear to be an issue with MariaDB, not an issue with PHPMaker, but because it is such a widespread issue across multiple versions of MariaDB, and because it affects the latest versions, it doesn’t seem like it’s getting fixed on their end any time soon. Can PHPMaker please support this somehow?