Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

When checking current timestamps, make audit replace mysql function usage with preferred CURRENT_TIMESTAMP variable #4079

Closed
yingbaiibm opened this issue Jan 22, 2021 · 4 comments
Labels
bug Undesired behaviour resolved A fixed issue
Milestone

Comments

@yingbaiibm
Copy link

When running audit_database.php under mariadb 10.3.17 found below error
current_timestamp

Reproduce steps:

  1. Prepare two env, mariadb 5.5 and mariadb 10.3.17 with both collation_server = utf8mb4_unicode_ci set in my.cnf
  2. create a table in cacti DB
    [root@lsf1x200 ~]# mysql cacti -e "desc disku_groups;"
    +-----------+------------------+------+-----+---------------------+-------------------------------+
    | Field | Type | Null | Key | Default | Extra |
    +-----------+------------------+------+-----+---------------------+-------------------------------+
    | domain | varchar(20) | NO | PRI | | |
    | name | varchar(64) | NO | PRI | | |
    | groupid | int(10) unsigned | NO | PRI | 0 | |
    | firstSeen | timestamp | NO | | 0000-00-00 00:00:00 | |
    | lastSeen | timestamp | NO | | current_timestamp() | on update current_timestamp() |
    +-----------+------------------+------+-----+---------------------+-------------------------------+
  3. Check audit_schema.sql
    INSERT INTO table_columns VALUES ('disku_groups',5,'lastSeen','timestamp','NO','','CURRENT_TIMESTAMP','on update CURRENT_TIMESTAMP');
  4. Run audit_database.php under mariadb 5.5 env, no error exist
  5. Run audit_database.php under mariadb 10 env, error exist as show in screenshot.
  6. Run mysql cacti -e "show table status" to check table collection value, on mariadb 5.5, collection value for this table is utf8mb4_unicode_ci, but on mariadb 10, collection value for this table is utf8mb4_general_ci.
  7. The audit_database.php does not deal with utf8mb4_general_ci to cause this error
@yingbaiibm yingbaiibm added bug Undesired behaviour unverified Some days we don't have a clue labels Jan 22, 2021
yingbaiibm added a commit to yingbaiibm/cacti that referenced this issue Jan 22, 2021
Fix issue Cacti#4079 current_timestamp() issue when running audit_database.php
@TheWitness
Copy link
Member

I'm still confused.

@yingbaiibm yingbaiibm changed the title current_timestamp() issue when running audit_database.php audit_database.php Jan 22, 2021
@yingbaiibm yingbaiibm changed the title audit_database.php audit_database.php compatibility enhance for current_timestamp() and CURRENT_TIMESTAMP Jan 22, 2021
@yingbaiibm
Copy link
Author

OK, close this workaround fix. Need fix for compatibility support for current_timestamp() and CURRENT_TIMESTAMP

Detail describe:
On mariadb 5.5 For: lastUpdated timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
After creating table it automatically become lastUpdated timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

For mariadb 10.3, it keep same before and after creating table.

The audit_schema.sql is generated based on Maraidb 5.5, so on Mariadb 10, it will have above screenshot error.

audit_database.php should support this capatibility. It should treat as same for current_timestamp() and CURRENT_TIMESTAMP

@TheWitness
Copy link
Member

Yea, this is a small corner case. I think it's easy to fix frankly.

TheWitness added a commit that referenced this issue Jan 23, 2021
- audit_database.php compatibility enhance for current_timestamp() and CURRENT_TIMESTAMP
@TheWitness TheWitness added resolved A fixed issue and removed unverified Some days we don't have a clue labels Jan 23, 2021
@TheWitness TheWitness added this to the v1.2.17 milestone Jan 23, 2021
@netniV netniV changed the title audit_database.php compatibility enhance for current_timestamp() and CURRENT_TIMESTAMP When checking current timestamps, make audit replace mysql function usage with preferred CURRENT_TIMESTAMP variable Apr 30, 2021
@yingbaiibm
Copy link
Author

Still failed with the fix

ddb4github added a commit to ddb4github/cacti that referenced this issue Jun 12, 2021
  audit_database.php compatibility enhance for current_timestamp() and CURRENT_TIMESTAMP
netniV pushed a commit that referenced this issue Jun 12, 2021
audit_database.php compatibility enhance for current_timestamp() and CURRENT_TIMESTAMP
@github-actions github-actions bot locked and limited conversation to collaborators Sep 2, 2021
Sign up for free to subscribe to this conversation on GitHub. Already have an account? Sign in.
Labels
bug Undesired behaviour resolved A fixed issue
Projects
None yet
Development

No branches or pull requests

2 participants