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

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

Nothing of which can HeidiSQL warn you efficiently.

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.

> 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.

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".

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.

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.

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?

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

small flaw: you have to trim table aliases before you remove whitespace since whitespace defines them. oops.

Well, as said, I won't add such complex stuff before sending queries to the server. Please read my above comments.

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