How to find out CAST failed in MySQL

Question

Can someone tell me how I can determine if MySQL crashed while using the CAST() function?

These two lines return the same value: 0.

 SELECT CAST('Banana' AS UNSIGNED INTEGER) AS 'CAST1'; SELECT CAST('0' AS UNSIGNED INTEGER) AS 'CAST2'; 

Answer 1

The SHOW WARNINGS and the @@WARNINGS system variable are built-in methods for this. There is no mechanism to automatically update all error warnings, but there are some things you can do.

You might want to start MySQL with the --show-warnings , although this might just display the number of warnings with row counting. I can no longer remember. I do not know if there is an option my.ini for this option. There is also an option --log-warnings , which, it seems to me, has an option in the ini / cnf file. If you are running a script or using the CLI, the \W command disables the warning display and \W disables the current connection for (IIRC).

You can also see SQL mode . TRADITIONAL is probably the most similar to a normal RDBMS, but it is a kind of wing nest of options. STRICT modes are what you are likely to follow, but read this page. Most MySQL-based applications use (non-deterministic) GROUP BY extensions , which bite almost anyone who moves to or from MySQL, and TRADITIONAL includes ONLY_FULL_GROUP_BY , which effectively disables these extensions, and the RDBMS does not support the OVER() clause. I don't know if silence can succeed with typing, but abort a transaction even in traditional / strict mode.

MySQL is a kind of field for such problems (for example, zero dates), so it has a weak reputation among database administrators, especially those who worked with v3.x or v4.x.

Answer 2

You can use regular expressions to check the data before conversion:

 select (case when val regexp '^[0-9]+$' then cast(val as unsigned integer) end) 

Answer 3

You can, for example, check the warning_count variable:

 MySQL [test]> SELECT CAST(0 AS UNSIGNED INTEGER) AS 'CAST1', @@warning_count; +-------+-----------------+ | CAST1 | @@warning_count | +-------+-----------------+ | 0 | 0 | +-------+-----------------+ 1 row in set (0.01 sec) MySQL [test]> SELECT CAST('Banana' AS UNSIGNED INTEGER) AS 'CAST1', @@warning_count; +-------+-----------------+ | CAST1 | @@warning_count | +-------+-----------------+ | 0 | 1 | +-------+-----------------+ 1 row in set, 1 warning (0.00 sec) 

However, there is a caveat: the number of warnings is only reset per statement, not per result line, so if CAST () is executed several times, for example. for each line of the result, the counter will grow with every failed call.

Also the warnings do not seem to get reset on successful queries that do not concern any tables, therefore in the above example the 2nd

 SELECT CAST(0 AS UNSIGNED INTEGER) AS 'CAST1', @@warning_count; 

still show 1 warning, while, for example,

 SELECT CAST(0 AS UNSIGNED INTEGER) AS 'CAST1', @@warning_count FROM mysql.user LIMIT 1; 

will correctly reset to 0 ...

Answer 4

SQL Server supports try_cast function

Answer 5

Well, you could include @@ warning_count, but somehow create a workaround for its buggy functionality.

Take a look at this code below. Yes, I know this is ugly, but it works.

 SELECT IF(WarningCount = 0, ConversionResult, NULL) FROM ( SELECT CAST('banana' AS DECIMAL(10, 6)) AS ConversionResult , @@warning_count AS WarningCount FROM <any non empty table> LIMIT 1 ) AS i; 

In internal SELECT, I get 1 row (LIMIT 1) from any existing table. I convert the string ('banana') and get a WarningCount. In an external SELECT, I check the WorkingCount, and if it is 0 (the conversion was successful), then I return the converted value.