Reputation
12,040
Top tag
Next privilege 15,000 Rep.
Protect questions
Badges
1 13 35
Newest
 Revival
Impact
~763k people reached

Feb
18
comment SELECT RELEASE_LOCK causing “Mysql has gone away”
@MatthewHaworth comically, I ran into a ERROR 2013 (HY000): Lost connection to MySQL server during query this morning. In this case, the connection wasn't strictly idle -- the server was trying to send data and the application wasn't ready. When the net_write_timeout timer expires before the client is ready to read, the server drops the connection and this error results when the client is ready. If you're supposed to be reading from the server, and you keep it waiting too long, the symptom is the same. Is that the case, here, or is the connection idle?
Feb
18
comment SELECT RELEASE_LOCK causing “Mysql has gone away”
Here's a command line one-liner: perl -e '$|=1; $i = 0; while (1) { warn "sending query..."; print "SELECT $i,NOW(),CONNECTION_ID();\n"; sleep 1; $i += 60; warn "wait $i seconds...\n"; sleep $i-1; }' | mysql --table --host={hostname} --user={username} --password={password} ... at some point, I'd expect an infinite timeout or "MySQL server has gone away" followed by the displayed connection_id changing since the command line client should try to reconnect.
Feb
18
comment SELECT RELEASE_LOCK causing “Mysql has gone away”
@MatthewHaworth I'm skeptical: the hosting provider's assertion doesn't mean much to me... not only because I'm jaded from experience and don't trust vendors :) but also because in a stateful firewall, timeouts often do not need to be configured. They are there, enabled, often silently and by default. In cheaper hardware, they are there and can't be configured. If this were my issue, I would be attempting to prove or disprove that it is a timeout.
Feb
17
answered SELECT RELEASE_LOCK causing “Mysql has gone away”
Feb
13
comment How to use MySQL stored function argument to refer to column name?
It works, but it shouldn't be done. Objects aren't data and data isn't objects. Crossing that boundary reeks of bad design and introduces potential security vulnerabilities. Try running CALL showUnique('1) UNION ALL SELECT user FROM mysql.user); #');, for example.
Feb
13
comment InnoDB Deadlocks Involving Currency Transactions
Ah, but if I'm reading the deadlocks correctly, you had 23093 row locks and a lock on the primary key all held by trx 2, which suggested a full table scan was being done to find the rows to update, in the absence of that index. Adding that index should result in a lot fewer rows being locked when that "session_lastclick" query runs, reducing the chance of a deadlock. Arguably, there's no need to do that second query, since the value of session_lastclick subtracted from the current time could give you a true/false answer to whether a session is "active" whenever you need to know.
Feb
12
comment InnoDB Deadlocks Involving Currency Transactions
Is there an index on session_lastclick?
Feb
12
comment How to evolve MySQL schema while maintaining integrity
"It will even unlock it even if you had manually locked it." -- this should only be true if the OS is Windows and the table is transactional. Have you considered SET GLOBAL READ_ONLY = 1;...?
Feb
12
awarded  Enlightened
Feb
12
awarded  Nice Answer
Feb
11
comment mysqlslow - excluding queries from mysqldump
heh... it looks like it would actually be something along the lines of -g '(?{{ undef $_ if \/SQL_NO_CACHE\/ }})' ... now, I'm curious. Negative matching doesn't seem to be natively supported, but it "should" be possible (famous last words).
Feb
10
comment MYSQL: InnoDB inserts wont rollback transaction inside a stored procedure
So, something else is either committing or rolling back your transaction. You are calling EXECUTE on 5 different prepared statements, but what those contain is not shown here. It should be one of those that's doing it. (Unfortunately, ROLLBACK; and COMMIT; are both accepted by MySQL without raising an exception, when you don't actually have a transaction running; the ROLLBACK TO SAVEPOINT hack doesn't silently fail like the others, and hence the reason this test works.)
Feb
10
comment MYSQL: InnoDB inserts wont rollback transaction inside a stored procedure
I agree it isn't obvious why this wouldn't work. Right before IF @Canceled = 1 THEN try adding SAVEPOINT foo; ROLLBACK TO SAVEPOINT foo;. The net effect of this should be no change in behavior -- however, it will throw an exception if you are, for whatever reason, not actually inside a transaction at that point, which seems like the most likely explanation, even though it's not obvious from the code shown here why that might be the case.
Feb
9
comment How to recover a dropped mysql database with its procedures?
In the future, you will want to add the --events and --routines options to your backups... and take away the access "someone" has to your database server. Unless the files are still there in spite of the "uninstall," the data is, for all practical purposes, lost... with the possible exception of low, low, low level forensic-style recovery of the delete file contents.
Feb
4
awarded  mysql-5.5
Feb
3
comment mysqldump: Got error: 1045: Access denied for user 'root'@'localhost' (using password: YES) when trying to connect
In spite of the "please ask a new question" phrasing, above, I believe a question that's closed as a duplicate can be edited and reopened by vote or moderator action, if it turns out we were wrong. It isn't necessarily a permanent dismissal of your question and issue. It's true that your actual title and question don't make it obvious why it's a duplicate since you weren't clear on the nature of the problem when you asked, and the comments provide the only clear indication. However, the actual problem you experienced did turn out to be the same problem with the same solution. Happy to help.
Feb
2
comment Cannot create MySQL database with name '5e370227_db'
+1 for solid sleuthing but it's not an entirely unknown issue (as was also noted in the bug report). The same exception applies to usernames, and any reserved word used as an identifier. The workaround, in any of these cases, is backticks. I would wager against this being fixed any time soon, and plan to use the backticks, or prefix the databases with a character, instead.
Feb
1
comment getting an error in trigger while invoking a procedure in it
What are the : symbols for? MySQL stored programs don't use this with column names.
Feb
1
answered mysqlbinlog recovery table wise
Jan
30
comment #2014 - Commands out of sync. you can't run this command now. Query was working before. But now it is not working
Yes, I understand your point... my point is that from a low-level computer programming perspective, unsigned subtraction never gives negative numbers. 4 - 5 = 65535 for 16 bit unsigned.