search

1NutWunDeR / KZTimerOffical

KZTimer
http://steamcommunity.com/groups/KZTIMER
15 stars 6 forks source link

Very strange inconsistency in table alterations in function 'db_createTables' (MySQL). Breaks player rank functionalities in new instances. #28

Open Boux opened 8 years ago

Boux commented 8 years ago

Some of the ALTER TABLE queries do not work, and some do work, I'll list everything I've tested.

    SQL_FastQuery(g_hDb, "ALTER TABLE playerrank ADD lastseen DATE");

As lastseen is not in the original CREATE TABLE, we need to add it, but it's simply not added, here's what my table looks like

mysql> show columns from playerrank;
+-----------------+-------------+------+-----+---------+-------+
| Field           | Type        | Null | Key | Default | Extra |
+-----------------+-------------+------+-----+---------+-------+
| steamid         | varchar(32) | NO   | PRI | NULL    |       |
| name            | varchar(32) | NO   |     | NULL    |       |
| country         | varchar(32) | NO   |     | NULL    |       |
| points          | int(12)     | NO   |     | 0       |       |
| winratio        | int(12)     | NO   |     | 0       |       |
| pointsratio     | int(12)     | NO   |     | 0       |       |
| finishedmaps    | int(12)     | NO   |     | 0       |       |
| multiplier      | int(12)     | NO   |     | 0       |       |
| finishedmapstp  | int(12)     | NO   |     | 0       |       |
| finishedmapspro | int(12)     | NO   |     | 0       |       |
+-----------------+-------------+------+-----+---------+-------+

The lastseen field is used in multiple select queries, which simply gives an error in the server's console and doesn't do anything, as in the player rank is not calculated or saved.

If I manually execute the table alteration query in the mysql prompt, the column is added without error.

mysql> ALTER TABLE playerrank ADD lastseen DATE;
Query OK, 0 rows affected (0.34 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show columns from playerrank;
+-----------------+-------------+------+-----+---------+-------+
| Field           | Type        | Null | Key | Default | Extra |
+-----------------+-------------+------+-----+---------+-------+
| steamid         | varchar(32) | NO   | PRI | NULL    |       |
| name            | varchar(32) | NO   |     | NULL    |       |
| country         | varchar(32) | NO   |     | NULL    |       |
| points          | int(12)     | NO   |     | 0       |       |
| winratio        | int(12)     | NO   |     | 0       |       |
| pointsratio     | int(12)     | NO   |     | 0       |       |
| finishedmaps    | int(12)     | NO   |     | 0       |       |
| multiplier      | int(12)     | NO   |     | 0       |       |
| finishedmapstp  | int(12)     | NO   |     | 0       |       |
| finishedmapspro | int(12)     | NO   |     | 0       |       |
| lastseen        | date        | YES  |     | NULL    |       |
+-----------------+-------------+------+-----+---------+-------+

As for other tables, nothing seems to be broken.

In playertmp, the EncTickrate field needs to be added, but it's already in the CREATE TABLE query : EncTickrate INT(12) NOT NULL DEFAULT '-1.0' so it's fine for new instances.

In playeroptions2, these 4 fields need to be added : ViewModel AdvInfoPanel ReplayRoute Language, none of which are in the CREATE TABLE query so the table alteration queries all seem to work properly.

In playerjumpstats3, it seems to be the same as playeroptions2, all fields are correctly added to the table even if they are not in the original CREATE TABLE query.

So there's the inconsistency. The playerrank table is not altered properly while the other tables are. I have no idea what could cause this, so I'm just posting my tests' results here.

AzaZPPL commented 8 years ago

Do you have correct permission to alter tables on specific columns? I myself have never seen this issue.

Boux commented 8 years ago

I should have, I haven't changed anything. If I drop my database and start over by running the server, it creates everything except the lastseen field in playerrank. I use the exact same user/pass in my database.cfg and when I connect to it from the command line, but executing ALTER TABLE playerrank ADD lastseen DATE works from the command line.

Edit:

here's my db config

    "kztimer"
    {
        "driver"            "mysql"
        "host"              "127.0.0.1"
        "database"          "kztimer"
        "user"              "root"
        "pass"              "root"
        //"timeout"         "0"
        "port"              "3306"
    }

and here's my database rights

mysql> show grants for 'root'@'localhost';
+---------------------------------------------------------------------+
| Grants for root@localhost                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION        |
+---------------------------------------------------------------------+

.... actually this could be the issue, since I connect to the host 127.0.0.1 instead of localhost in my db config. (for some reason using localhost in my db config prevented my server to connect to the db, maybe my hosts file).

mysql> show grants for 'root'@'127.0.0.1';
ERROR 1141 (42000): There is no such grant defined for user 'root' on host '127.0.0.1'

but then why would all the other ALTER TABLE queries work?

Wait no what am I saying, this wouldn't change anything since it's where the user connect from, not the opposite. Still don't know what could cause this.

AzaZPPL commented 8 years ago

What is the OS? Are you using mysql? Are the tables InnoDB?

Boux commented 8 years ago

Ubuntu 16.04 and yeah mysql.

Here's my table status

mysql> show table status;
+------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+
| Name             | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation         | Checksum | Create_options | Comment |
+------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+
| LatestRecords    | InnoDB |      10 | Dynamic    |   33 |            496 |       16384 |               0 |            0 |         0 |           NULL | 2016-07-10 15:47:31 | 2016-07-16 15:05:28 | NULL       | latin1_swedish_ci |     NULL |                |         |
| MapButtons       | InnoDB |      10 | Dynamic    |    0 |              0 |       16384 |               0 |            0 |         0 |           NULL | 2016-07-10 15:47:31 | NULL                | NULL       | latin1_swedish_ci |     NULL |                |         |
| challenges       | InnoDB |      10 | Dynamic    |    0 |              0 |       16384 |               0 |            0 |         0 |           NULL | 2016-07-10 15:47:31 | NULL                | NULL       | latin1_swedish_ci |     NULL |                |         |
| playerjumpstats3 | InnoDB |      10 | Dynamic    |    1 |          16384 |       16384 |               0 |            0 |         0 |           NULL | 2016-07-10 15:47:31 | 2016-07-14 17:16:45 | NULL       | latin1_swedish_ci |     NULL |                |         |
| playeroptions2   | InnoDB |      10 | Dynamic    |    1 |          16384 |       16384 |               0 |            0 |         0 |           NULL | 2016-07-10 15:50:15 | 2016-07-10 16:49:52 | NULL       | latin1_swedish_ci |     NULL |                |         |
| playerrank       | InnoDB |      10 | Dynamic    |    1 |          16384 |       16384 |               0 |            0 |         0 |           NULL | 2016-07-11 18:43:35 | 2016-07-16 15:05:29 | NULL       | latin1_swedish_ci |     NULL |                |         |
| playertimes      | InnoDB |      10 | Dynamic    |   29 |            564 |       16384 |               0 |            0 |         0 |           NULL | 2016-07-10 15:47:31 | 2016-07-16 15:05:28 | NULL       | latin1_swedish_ci |     NULL |                |         |
| playertmp        | InnoDB |      10 | Dynamic    |    1 |          16384 |       16384 |               0 |            0 |         0 |           NULL | 2016-07-10 15:47:31 | 2016-07-16 15:05:47 | NULL       | latin1_swedish_ci |     NULL |                |         |
+------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+------
AzaZPPL commented 8 years ago

You are using the latest Metamod snapshot with the latest Sourcemod snapshot and use kztimer 1.84, right?

I'm guessing there are no errors in Sourcemod error logs other than kztimer not being able to select the columns but just to be sure can you still send the sourcemod error log.

Does anything change if you let kztimer remake the database? Try disabling kztimer and start your server. Go to your console and load kztimer with sm plugins load KZTimerGlobal. Check in console if kztimer reports any errors.