TIMESTAMP precision not working on older MySQL versions.

[MrCyjaneK]

Issue description

• Expected result: Start without errors?
• Actual result: Start with errors?

Steps to reproduce the issue

1. Change database to mysql
   • MySQL version: 5.5.62
   • Protocol version: 10
2. Start the server

OS and versions

• BedcoreProtect: 0.5.2

• PocketMine-MP: 3.11.3
• PHP: 7.3.13
• Server OS: Ubuntu 19.10
• Game version: PE

Plugins

Plugins: AlwaysSpawn v2.2.4, FileManager v1.0.1, DevTools v1.13.5, BedcoreProtect v0.5.2, EconomyAPI v2.0.9, EconomyLand v2.0.9, SimpleAuth v2.1.3

Crashdump, backtrace or other files

I'm getting following non-fatal error:

[Server thread/ERROR]: [BedcoreProtect] SQL EXECUTION error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(6)        DEFAULT CURRENT_TIMESTAMP NOT NULL,
CHECK (only_one_row)
)' at line 5, for query CREATE TABLE IF NOT EXISTS status
(
only_one_row BOOLEAN PRIMARY KEY DEFAULT TRUE              NOT NULL,
version      VARCHAR(20)                                   NOT NULL,
upgraded_on  TIMESTAMP(6)        DEFAULT CURRENT_TIMESTAMP NOT NULL,
CHECK (only_one_row)
); | []

I'm getting this error for all queries that have TIMESTAMP(x) inside.

My solution: copy queries, remove (x) part and execute them on SQL server. After starting you will still have that errors, but at least plugin will work.

[matcracker]

You need to upgrade your MySQL version to >=5.6.4 because the milliseconds precision is not supported previously. This remind me to check the MySQL version on plugin start-up.

[matcracker]

After some tests, I can't add inside the plugin a database engine version checker due to MySQL different engines versions.

[MrCyjaneK]

how about:

<?php
// MySQLi
$version = $db->query("SELECT version() AS version");
$version = $version->fetch_assoc();
$version = $version["version"]; // For me it holds: 5.5.62-log

?

[matcracker]

It works if everyone uses the same standard engine but if, for example, I use MariaDB, the version will change so, I can't compare two engines because they have different version. It's like to compare Firefox version to Chrome.

[matcracker]

By the way I advise you to update your MySQL version and don't change the tables because the plugin logs could not work properly.

[MrCyjaneK]

It works if everyone uses the same standard engine but if, for example, I use MariaDB, the version will change so, I can't compare two engines because they have different version. It's like to compare Firefox version to Chrome.

You are right, but both of them are web browsers and do similar things, so it's possible.

I've made following function that seems to work:

<?php
$mysql_fork = shell_exec("mysql -V"); //Don't forget to check if shell_exec is enabled.
$mysql_fork = strtolower($mysql_fork);
if (strpos($mysql_fork, "mariadb") !== false) {
    $mysql_fork = "mariadb";
} else {
    $mysql_fork = "mysql";
}

[MrCyjaneK]

By the way I advise you to update your MySQL version and don't change the tables because the plugin logs could not work properly.

I've did it, but your plugin has insane CPU usage on my server and laptop, so I can't use it.

[matcracker]

It works if everyone uses the same standard engine but if, for example, I use MariaDB, the version will change so, I can't compare two engines because they have different version. It's like to compare Firefox version to Chrome.

You are right, but both of them are web browsers and do similar things, so it's possible.

I've made following function that seems to work:

<?php
$mysql_fork = shell_exec("mysql -V"); //Don't forget to check if shell_exec is enabled.
$mysql_fork = strtolower($mysql_fork);
if (strpos($mysql_fork, "mariadb") !== false) {
    $mysql_fork = "mariadb";
} else {
    $mysql_fork = "mysql";
}

There are a lot of engines, not only 2. If someone want to use the plugin must follow the minimum requirements and, said among us, last MySQL version is 8.0 and you're using 5.5 yet🤣

[matcracker]

By the way I advise you to update your MySQL version and don't change the tables because the plugin logs could not work properly.

I've did it, but your plugin has insane CPU usage on my server and laptop, so I can't use it.

Which OS are you using? By the way, the cause is libasynql falsely uses the 100% of CPU (see #12)

[matcracker]

Which OS are you using? By the way, the cause is libasynql falsely uses the 100% of CPU (see #12)

To demonstrate just check the TPS