10,813 reputation
11229
bio website
location Cincinnati, OH
age 44
visits member for 2 years, 6 months
seen yesterday

I am a professional MySQL Database Administrator and Amazon Web Services infrastructure specialist.

My services are available when time permits; please feel free to contact me, michael at sqlbot dot net for rates, availability, and other information.

When you're browsing the Stack Exchange sites, and find something useful, helpful or just interesting, please take a moment to vote... Your votes on our questions, answers, and even comments that you found helpful, accurate, and informative are important -- and it's really not because of the reputation scores. Every time you vote, the person whose contribution you voted on gets an unobtrusive and anonymous little notification message to remind us that the hours we invest in this community are helpful and valuable.


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.
Jan
30
comment Is making joins between different table spaces slower?
@Mihai ibdata1 is a tablespace. Every ibd and MYD file is also a tablespace, but they aren't potentially shared like ibdata1.
Jan
30
comment Is making joins between different table spaces slower?
The question asks if different tablespaces makes it slower. "Yes, this can make a difference" means faster, not slower... since different disks implies more I/O bandwidth... right?
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
This behavior is really not so egregious. Unsigned integer operations produce unsigned integer results. Declaring a value as unsigned is, as you point out, best reserved for surrogate keys, but it's less because MySQL is Doin' it Wrong™ and more because unsigned values are inappropriate arguments if you expect to do signed arithmetic operations. stackoverflow.com/questions/7221409/…
Jan
30
comment How to change ibdata1 path in mysql?
The error log at /var/log/mysqld.log would be the place to look for an explanation of the various errors.
Jan
28
answered On not public server: Host name example.com could not be resolved: Name or service not known
Jan
28
comment On not public server: Host name example.com could not be resolved: Name or service not known
Regarding the processlist... check the IP addresses seen there and do "nslookup" on them from the MySQL server itself and see if one of them resolves back to that address (even though it doesn't make sense that it would).
Jan
28
comment Host Name Resolution in mysql.db and mysql.user tables
You probably don't want to update the mysql.user table like this, as a matter of practice, since it will invalidate the permissions on any stored programs where the affected user is the DEFINER.
Jan
28
comment On not public server: Host name example.com could not be resolved: Name or service not known
What about the other tables in the "mysql" database (like "db" or "procs_priv"?) What if you set "log-warnings = 2" in my.cnf? What if you "nslookup" all the connected clients that show an UP address from SHOW FULL PROCESSLIST; (perhaps there's a reverse entry but not a forward entry in DNS).
Jan
28
comment mysqldump: Got error: 1045: Access denied for user 'root'@'localhost' (using password: YES) when trying to connect
I suspect that's exactly what it is. The answer I posted to the question in the comment above this one will give you several scenarios and workarounds, although it sounds like you already are familiar with shell behavior now that you're thinking about it.
Jan
28
comment Mysql daemon failed to start: unknown variable password=xxx
Something tells me --no-defaults needs to be the first option, though that may or may not be right. At any rate, mysqld --help --verbose | less should include a list of the various places it could be loading that file from.