MySQL Bugs: #18124: query buffer is too small (400 characters?). truncates query at execution.

Bug #18124	query buffer is too small (400 characters?). truncates query at execution.
Submitted:	10 Mar 2006 8:51	Modified:	27 Nov 2006 11:39
Reporter:	Jim Michaels	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Query Browser	Severity:	S3 (Non-critical)
Version:	1.1.20	OS:	Windows (Windows XP SP2)
Assigned to:	Mike Lischke	CPU Architecture:	Any
Tags:	Source Editors

Description:
The engine allows for much larger queries than only (?)400 characters.  this is way too small of a size for a query.  When you press the execute button, you get a syntax error message back that shows statement truncation. Original statement had tabs and newlines in it.

also, the query window shrunk between versions, and I can't make it bigger.  frustrating.  It's tiny.

How to repeat:
SELECT quiz_quizzes_questions.question_id AS question_id,quiz_questions.question_num AS question_num 
					FROM quiz_quizzes_questions AS qqq,quiz_questions AS qq 
					WHERE qqq.quiz_id=19 
					  AND qq.question_id=qqq.question_id
					  AND qq.question_num=1

Suggested fix:
find out where the buffer is being truncated and enlarge it.  MySQL Engine has an internal 1MB INSERT limit if I remember correctly, so how about a 1.5MB buffer?

Thank you for a problem report. Please, try to repeat with a newer version of QB, 1.1.20.

truncation still a problem now that I upgraded to 1.1.20.  I paste in
SELECT qug.user_id AS user_id FROM quiz_user_groups AS qug,quiz_users AS qu
					 WHERE qug.test_admin_id=$_SESSION[tid] AND qug.user_id=qu.user_id 
					 ORDER BY qu.ln,qu.fn,qu.mi,qu.uid
and the query gets truncated - I get an error message saying "unknown column 'q' in order clause".

on that last query, I changed the $_SESSION[] to simply 4 so the query wouldn't give me an error

but I still get an error.

tried loading and executing a 7MB mysql administrator backup sql file which had BLOBs. tons of errors and the server disconnects itself in the middle of execution. so I deleted a large chunk. tried to connect. tried to reconnect. tried to execute a create view line.
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `jmichae3`.`workhours` AS select `jmichae3`.`work`.`id` AS `id`,`jmichae3`.`work`.`name` AS `name`,
`jmichae3`.`work`.`prj` AS `prj`,`jmichae3`.`work`.`tasknum` AS `tasknum`,timediff(`jmichae3`.`work`.`end_time`,`jmichae3`.`work`.`start_time`) AS `elapsed`,`jmichae3`.`work`.
`church` AS `church` from `jmichae3`.`work` order by `jmichae3`.`work`.`name`,`jmichae3`.`work`.`prj`,`jmichae3`.`work`.`tasknum`;

I get "Invalid pointer operation"

every time I have this problem, I am pasting code from the clipboard (who doesn't?)  this time, it cuts off at INFORMATION_SCHEMA.STAT
at line 7 (really line 8! line counter is wrong too)

CREATE PROCEDURE ShowIndexSelectivityForDb (
  IN db_name VARCHAR(50)
 , OUT TotalUniqueIndexColumns INT
)
BEGIN

 SELECT COUNT(*) INTO TotalUniqueIndexColumns
 FROM INFORMATION_SCHEMA.STATISTICS s
  INNER JOIN INFORMATION_SCHEMA.TABLES t
   ON s.TABLE_SCHEMA = t.TABLE_SCHEMA
   AND s.TABLE_NAME = t.TABLE_NAME
 WHERE t.TABLE_SCHEMA = db_name
 AND (s.CARDINALITY / IFNULL(t.TABLE_ROWS, 0.01)) = 1.00;

 SELECT
  t.TABLE_SCHEMA
 , t.TABLE_NAME AS "TABLE"
 , s.INDEX_NAME AS "INDEX"
 , s.COLUMN_NAME AS "COLUMN"
 , s.SEQ_IN_INDEX AS "SEQ"
 , (
  SELECT MAX(SEQ_IN_INDEX)
  FROM INFORMATION_SCHEMA.STATISTICS s2
  WHERE s.TABLE_SCHEMA = s2.TABLE_SCHEMA
  AND s.TABLE_NAME = s2.TABLE_NAME
  AND s.INDEX_NAME = s2.INDEX_NAME
 ) AS "COLS_IN_INDEX"
 , s.CARDINALITY AS "CARD"
 , t.TABLE_ROWS AS "ROWS"
 , ROUND(((s.CARDINALITY / IFNULL(t.TABLE_ROWS, 0.01)) * 100), 2) AS "SEL %"
 FROM INFORMATION_SCHEMA.STATISTICS s
  INNER JOIN INFORMATION_SCHEMA.TABLES t
   ON s.TABLE_SCHEMA = t.TABLE_SCHEMA
   AND s.TABLE_NAME = t.TABLE_NAME
 WHERE t.TABLE_SCHEMA = db_name
 AND t.TABLE_ROWS > 10
 AND s.CARDINALITY IS NOT NULL
 AND (s.CARDINALITY / IFNULL(t.TABLE_ROWS, 0.01)) < 1.00
 ORDER BY t.TABLE_SCHEMA, t.TABLE_NAME, s.INDEX_NAME, "SEL %";

END;

Verified just as described in the last comment. Same error message got on XP.

I get the same error on QB 1.2.3 beta, but only when I copy and paste a sql, for example:
[code]
update TTransaccion 
					set activa = 'B' 
					where idTTransaccion=1
[/code]
I get the error:
Unknown column 'idTTransacc' in 'where clause'

If I paste a sql larger I get an error like this:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''2006/' at line 26
and on the line 26 is:
AND T.fechahora between '2006/07/01' and '2006/09/30'

If I delete the last line and I rewrite it manually it runs Ok or even if I comment the last line.

Thank you for your bug report. This issue has already been fixed in the latest released version of that product, which you can download at

  http://www.mysql.com/downloads/