distal-attribute
distal-attribute
distal-attribute
distal-attribute

warn when running START TRANSACTION or BEGIN on non-innodb tables

User, date Message
Written by leeoniyaMoney, Euro
4 years ago
Category: Feature discussion
13 posts since Fri, 26 Mar 10
i've been stung by this a couple of times. (good thing i had backups)

sometimes i forget to change table type to InnoDB when creating them, and then try to run transactions expecting to have rollback available. then it turns out it quietly ignored the fact that it was a myiasm table. doh!

thanks,
Leon
Written by ansgar
4 years ago
4973 posts since Fri, 07 Apr 06
Nothing of which can HeidiSQL warn you efficiently.
Written by leeoniyaMoney, Euro
4 years ago
13 posts since Fri, 26 Mar 10
well you know which tables are participating in the query (since they get hinted) and whether the query is an UPDATE or INSERT. i don't see why it would be difficult to throw up a notice based on the types of tables defined in the SET clause.
Written by ansgar
4 years ago
4973 posts since Fri, 07 Apr 06
> well you know which tables are participating

Yes, after query execution. But that's totally useless if you want to be warned before executing the query.
Written by leeoniyaMoney, Euro
4 years ago
13 posts since Fri, 26 Mar 10
but the lexer/parser knows what the tables are before anything happens. also, Heidi brings up a hint column list when you type "table." so it must have knowledge of what is and what isnt a table in the query long before you press execute. not only that but it also understands table aliases. i'm not sure what you mean by "after".
Written by ansgar
4 years ago
4973 posts since Fri, 07 Apr 06
Ah you mean the completion proposal. Well that's not a parser, just some string routines detecting the name of the table. Look into the source code if you don't believe me. It's in TMainForm.SynCompletionProposalExecute .

However, such warnings should only be returned by the server, as it is done for unsafe UPDATEs, when the server was started with --safe-updates.

The completion proposal is triggered when editing, and hitting the "." character. Nothing is yet parsed before executing the queries, which you are just proposing. This is not comparable to such a pre-execution parser, which would imply some complex code to start each time you hit F9. That is nothing users want.
Written by leeoniyaMoney, Euro
4 years ago
13 posts since Fri, 26 Mar 10
i'm afraid i still do not understand.

if i have a table named 'customers' and i type "customers" anywhere into the query window, it turns a different color from other SQL code without me having to do anything else. it is instantly recognized as a table without any type of completion proposal and without running the query.
Written by ansgar
4 years ago
4973 posts since Fri, 07 Apr 06
Hm, now you're comparing SynEdit's syntax highlighter which even works more simple than the completion proposal you mentioned in the comment above. Table names are colored by throwing a list of names from the SHOW TABLE STATUS result onto the highlighter. Nothing you can use to warn about queries using a transaction on a non transactional table. I wonder how your idea is on how these both unrelated things could ever work together?
Written by leeoniyaMoney, Euro
4 years ago
13 posts since Fri, 26 Mar 10
okay. let's say you cannot use any of those things.

here is a rudimentary parsing procedure for it in 4 lines:

// sample statement:
BEGIN; UPDATE `h`,i j,set, k SET j.'blah' = NULL;

// regex grabs set of update tables gives you `h`,i j,set, k in capture group 1
/begin;.*?update\s+(.*?)\s+set/i

// split capture group 1 on commas into an array
split(',')

// remove quotes and whitespace from each
replace('\'"`\t\n\r ', '')

// trim table aliases
split(' '), keep result[0]

// check resulting array against SHOW TABLES w/ engine type



it's not perfect by any means, but it will do the job for 90% of cases
Written by leeoniyaMoney, Euro
4 years ago
13 posts since Fri, 26 Mar 10
small flaw: you have to trim table aliases before you remove whitespace since whitespace defines them. oops.
Written by ansgar
4 years ago
4973 posts since Fri, 07 Apr 06
Well, as said, I won't add such complex stuff before sending queries to the server. Please read my above comments.
Written by BeachHouse
2 years ago
3 posts since Fri, 29 Oct 10
Just want to say thanks a million. I wish this was higher on google. Newbies take notice!!!




i've been stung by this a couple of times. (good thing i had backups)

sometimes i forget to change table type to InnoDB when creating them, and then try to run transactions expecting to have rollback available. then it turns out it quietly ignored the fact that it was a myiasm table. doh!

thanks,
Leon
 

Please login to leave a reply, or register at first.