0

Is there any way to view the entire text of a LONGTEXT column when we select it using the MySQL command line client - mysql?

This query:

select query from statement_analysis;

only shows 64 characters of the query.

This is on 5.7.25.

example from 5.7.33:

--------------
select max(length(query))
from statement_analysis
--------------

+--------------------+
| max(length(query)) |
+--------------------+
|                 65 |
+--------------------+
1 row in set (0.00 sec)

--------------
select query
from statement_analysis
where
length(query) > 60
--------------

+-------------------------------------------------------------------+
| query                                                             |
+-------------------------------------------------------------------+
| SELECT `DIGEST` , `CURRENT_SCH ... . `events_statements_history`  |
| SELECT COALESCE ( SCHEMA_NAME  ... ) - INTERVAL ? SQL_TSI_SECOND  |
| SELECT `sys` . `format_stateme ... cy` , `sys` . `format_time` (  |
| SELECT `performance_schema` .  ... IMER_WAIT` AS `avg_latency` ,  |
| SELECT COALESCE ( SCHEMA_NAME  ... statements_summary_by_digest`  |
+-------------------------------------------------------------------+
5 rows in set (0.00 sec)

If you run this command with 1000 or more it expands this to 1000:

set @sys.statement_truncate_len = 1000

But I would like to get the full query. It looks like the general log has the full length queries for what that is worth.

Bobby

4

1 Answer 1

0

The query column on x$statement_analysis shows more than 1000 characters. This was big enough for the problem I was working on. I do not know if it has some limit.

Here is the max size on the production database that I was working on:

--------------
select max(length(query))
from x$statement_analysis
--------------

+--------------------+
| max(length(query)) |
+--------------------+
|               1747 |
+--------------------+
1 row in set (0.08 sec)

So the answer is to use sys.x$statement_analysis instead of sys.statement_analysis and your query text will not be as limited.

Bobby

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.