Slide 84
Slide 84 text
Auto-Increment Value Exhaustion (2)
It's the MySQL DBA's responsability to prevent this to happen.
Using the following query, you can have an overview of the auto-increment columns:
SELECT
SELECT TABLE_SCHEMA
TABLE_SCHEMA,
, TABLE_NAME
TABLE_NAME,
, COLUMN_NAME
COLUMN_NAME,
, DATA_TYPE
DATA_TYPE,
, COLUMN_TYPE
COLUMN_TYPE,
, IF
IF(
( LOCATE
LOCATE(
('unsigned'
'unsigned',
, COLUMN_TYPE
COLUMN_TYPE)
) >
> 0
0,
,
1
1,
, 0
0)
) AS
AS IS_UNSIGNED
IS_UNSIGNED,
, (
( CASE
CASE DATA_TYPE
DATA_TYPE
WHEN
WHEN 'tinyint'
'tinyint' THEN
THEN 255
255 WHEN
WHEN 'smallint'
'smallint' THEN
THEN 65535
65535 WHEN
WHEN 'mediumint'
'mediumint' THEN
THEN 16777215
16777215 WHEN
WHEN 'int'
'int' THEN
THEN 4294967295
4294967295
WHEN
WHEN 'bigint'
'bigint' THEN
THEN 18446744073709551615
18446744073709551615
END
END >>
>> IF
IF(
(LOCATE
LOCATE(
('unsigned'
'unsigned',
, COLUMN_TYPE
COLUMN_TYPE)
) >
> 0
0,
, 0
0,
, 1
1)
))
) AS
AS MAX_VALUE
MAX_VALUE,
, AUTO_INCREMENT
AUTO_INCREMENT,
, CONCAT
CONCAT(
(ROUND
ROUND(
( AUTO_INCREMENT
AUTO_INCREMENT /
/ (
(
CASE
CASE DATA_TYPE
DATA_TYPE
WHEN
WHEN 'tinyint'
'tinyint' THEN
THEN 255
255 WHEN
WHEN 'smallint'
'smallint' THEN
THEN 65535
65535 WHEN
WHEN 'mediumint'
'mediumint' THEN
THEN 16777215
16777215 WHEN
WHEN 'int'
'int' THEN
THEN 4294967295
4294967295
WHEN
WHEN 'bigint'
'bigint' THEN
THEN 18446744073709551615
18446744073709551615
END
END >>
>> IF
IF(
(LOCATE
LOCATE(
('unsigned'
'unsigned',
, COLUMN_TYPE
COLUMN_TYPE)
) >
> 0
0,
, 0
0,
, 1
1)
))
)*
*100
100)
),
, '%'
'%')
) AS
AS AUTO_INCREMENT_RATIO
AUTO_INCREMENT_RATIO
FROM
FROM INFORMATION_SCHEMA
INFORMATION_SCHEMA.
.COLUMNS
COLUMNS INNER
INNER JOIN
JOIN INFORMATION_SCHEMA
INFORMATION_SCHEMA.
.TABLES
TABLES USING
USING (
(TABLE_SCHEMA
TABLE_SCHEMA,
, TABLE_NAME
TABLE_NAME)
)
WHERE
WHERE TABLE_SCHEMA
TABLE_SCHEMA NOT
NOT IN
IN (
('mysql'
'mysql',
, 'INFORMATION_SCHEMA'
'INFORMATION_SCHEMA',
, 'performance_schema'
'performance_schema')
) AND
AND EXTRA
EXTRA=
='auto_increment'
'auto_increment'
ORDER
ORDER BY
BY CAST
CAST(
(AUTO_INCREMENT_RATIO
AUTO_INCREMENT_RATIO AS
AS SIGNED
SIGNED INTEGER
INTEGER)
);
;
Copyright @ 2024 Oracle and/or its affiliates.
62