Uploaded image for project: 'Percona Monitoring and Management'
  1. Percona Monitoring and Management
  2. PMM-1934

mysqld_exporter/collector/info_schema_auto_increment.go fails if there are same table names but with different cases

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Done
    • Priority: Medium
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 1.8.0
    • Component/s: MySQLd_Exporter
    • Labels:
      None

      Description

      MySQL on linux treats table names to be case sensitive 

      https://dev.mysql.com/doc/refman/5.7/en/identifier-case-sensitivity.html

      However, prometheus is case insensitive. 

      If we have 2 tables:

      ac_GameInformation

      ac_gameinformation

      We will get an error like this one:

      ================

      An error has occurred during metrics gathering: 4 error(s) occurred: * collected metric mysql_info_schema_auto_increment_column label:<name:"column" value:"idx" > label:<name:"schema" value:"dbe8je6i4c3gjd50" > label:<name:"table" value:"ac_GameInformation" > gauge:<value:11 > was collected before with the same name and label values * collected metric mysql_info_schema_auto_increment_column_max label:<name:"column" value:"idx" > label:<name:"schema" value:"dbe8je6i4c3gjd50" > label:<name:"table" value:"ac_GameInformation" > gauge:<value:9.223372036854776e+18 > was collected before with the same name and label values * collected metric mysql_info_schema_auto_increment_column label:<name:"column" value:"idx" > label:<name:"schema" value:"dbe8je6i4c3gjd50" > label:<name:"table" value:"ac_gameinformation" > gauge:<value:7 > was collected before with the same name and label values * collected metric mysql_info_schema_auto_increment_column_max label:<name:"column" value:"idx" > label:<name:"schema" value:"dbe8je6i4c3gjd50" > label:<name:"table" value:"ac_gameinformation" > gauge:<value:9.223372036854776e+18 > was collected before with the same name and label values 
      ====================

      (From https://www.percona.com/forums/questions-discussions/percona-monitoring-and-management/50152-metrics-lr-down ). This will also fail the whole metrics-lr

      This query will return 2 rows for the same table:

      SELECT table_schema, table_name, column_name, auto_increment,
      pow(2, case data_type
      when 'tinyint' then 7
      when 'smallint' then 15
      when 'mediumint' then 23
      when 'int' then 31
      when 'bigint' then 63
      end+(column_type like '% unsigned'))-1 as max_int
      FROM information_schema.tables t
      JOIN information_schema.columns c USING (table_schema,table_name)
      WHERE c.extra = 'auto_increment' AND t.auto_increment IS NOT NULL

      To fix we can do the following options

      1. Change the prometheus to be case sensitive for metrics (if even possible)
      2. Change the query to remove duplicates. One / better way to do it is: SELECT table_schema, table_name, column_name, sum(auto_increment),
        sum(pow(2, case data_type
        when 'tinyint' then 7
        when 'smallint' then 15
        when 'mediumint' then 23
        when 'int' then 31
        when 'bigint' then 63
        end+(column_type like '% unsigned'))-1) as max_int
        FROM information_schema.tables t
        JOIN information_schema.columns c USING (table_schema,table_name)
        WHERE c.extra = 'auto_increment' AND t.auto_increment IS NOT NULL GROUP BY table_name, column_name

        Smart Checklist

          Attachments

            Issue Links

              Activity

                People

                Assignee:
                Unassigned
                Reporter:
                alexander.rubin Alexander Rubin (Inactive)
                Votes:
                0 Vote for this issue
                Watchers:
                3 Start watching this issue

                  Dates

                  Created:
                  Updated:
                  Resolved:

                    Time Tracking

                    Estimated:
                    Original Estimate - Not Specified
                    Not Specified
                    Remaining:
                    Remaining Estimate - 0 minutes
                    0m
                    Logged:
                    Time Spent - 6 hours
                    6h