Reputation
13,544
Top tag
Next privilege 15,000 Rep.
Protect questions
Badges
1 18 39
Newest
 Mortarboard
Impact
~977k people reached

Feb
5
comment How do I stop tables from being created in my mysql database?
Sorry, that's incorrect. There is no special privilege accorded of necessity to any user that happens to be named "root" in MySQL. The SUPER privilege is what grants superuser status to accounts.
Feb
1
comment Tuning MySQL variables to accommodate high load
You almost certainly have too many parameters that are not using default values. Bigger is not always better. See also dba.stackexchange.com/a/45570/11651 for my thoughts and other info on "tuning" your server's performance right into the ground, and a great pull-quote from the book High Performance MySQL about tuning scripts and performance tuning, where the bottom line, according to the authors (with whom I agree but am not affiliated), is this: “Tuning” your server can be a stunning waste of time.
Jan
28
comment Slave Waiting to reconnect
Didn't you say you had a power outage? If the master server lost power there isn't much you can do, but it's also possible that you have a physical disk issue.
Jan
28
comment Slave Waiting to reconnect
yep, looks like corrupt logs.
Jan
28
comment Slave Waiting to reconnect
Okay, just making sure... you didn't show the error output, so I wasn't clear if you'd had a genuine error or just the error that would have been generated if you had used that as-is with no log 000004.
Jan
28
comment Idempotent user and database creation script with MySQL
No, my intention was not that GRANT USAGE would provide a blank slate... GRANT USAGE is a no-op on existing users, so my intention was that you could use that first, then DROP USER would not fail, because you'd then always have something to drop. Your shortcut runs the risk of the user having old/unexpected permissions.
Jan
27
comment Slave Waiting to reconnect
I thought that command pipeline looked familiar. :) Note that you need to substitute mysql-bin.000004 with the actual binlog file you're trying to read.
Jan
20
comment How to clean up tables that don't belong into mysql/mysql database?
@donquixote you could also grep for them in your most recent backup file from before the mistake was made.
Jan
13
comment AWS Webapp tutorial: how to choose source in database security group?
Start typing sg- in the box for source IP address. Does anything pop up?
Jan
12
comment Slave stopped : Could not parse relay log event entry
The advice from @NawazSohail is not appropriate for the observed conditions, and should not be followed here. You can't blissfully skip over an event of unknown content without consequences, which may not become evident until later.
Jan
11
comment Slave stopped : Could not parse relay log event entry
If you do CHANGE MASTER TO do not use the Read_Master_Log_Pos value. Use Exec_Master_Log_Pos value (95264000) or you'll break replication for sure.
Jan
10
comment Avoid MySQL self-crashing on long semaphore waits
The only time I have seen this, the disk was worse than "slow" -- it was failing -- and because of that, individual reads were still succeeding, but taking an extremely long amount of time. The server isn't trying to crash itself because it's busy -- that is not the intention of the watchdog, as is clear from the response to Bug #47183. The intention is to guard against an actual hang, so changing the timer is likely not the right thing to do -- it should not fire. I suspect you have a disk issue or a bug causing a false alarm. Test the disks?
Jan
7
comment How do I synchronize rows in tables in remote databases?
There are many ways to compare data, such as generating a cryptographic hash (in SQL) of all the concatenated column values and sending that, along with the pk for each row, across the wire to the far end; mismatched rows will have a mismatched hash, and much less data needs to traverse the wire this way, because then only rows with known or suspected mismatches need be transferred on their entirety... but this is an implementation detail you needn't handle, yet, until you solve some much larger problems you likely have not yet identified. This is no minor undertaking.
Jan
7
comment How do I synchronize rows in tables in remote databases?
If the uuid is the primary key, then the "id" column seems superfluous. Your timestamps are microseconds, not milliseconds, but this is only as useful as the accuracy of the respective system clocks, and contrary to your assertion, actually does nothing to prevent simultaneous -- or, more importantly -- conflicting updates. If the records on both systems have changed, why should the one changed first or last be automatically considered the "correct" one. What about deletions? If a row is missing from A, delete it from B? or copy B to A?
Jan
5
comment Why does Table Definitions Opened differ this much between cluster nodes?
One factor could be tables opened for reading, which would only be opened on the server being queried. Another could be writes that were rolled back, since Galera replicates transactions among servers at commit time, not before.
Jan
4
comment Error on connecting to mysqli
The (110) at the end is a code for the specific failure, and 110 means Connection timed out. This is a network connectivity issue, not a MySQL specific issue. TCP port 3306 is (still) not open end-to-end.
Jan
3
comment MySQL / MariaDB pid ended (cannot start mysql) in CentOS
Also servers.frm, servers.myd, plugin.frm, plugin.myd are probably among the files the log messages indicate aren't found. Several of them are also likely to have a .myi file of the same base name.
Jan
3
comment MySQL / MariaDB pid ended (cannot start mysql) in CentOS
Do you have files called user.frm, user.myd, innodb_table_stats.frm, innodb_table_stats.ibd, innodb_index_stats.frm, innodb_index_stats.ibd somewhere on your system, at this point? If so, where? The lettercase of the extensions may vary, but these files, and several others, should be in /var/lib/mysql/mysql but if they are elsewhere, mention where you found them. You will need to proceed with extreme caution before moving anything around.
Jan
3
comment MySQL / MariaDB pid ended (cannot start mysql) in CentOS
This looks fairly serious. Unfortunately, if you moved the files with the server running, there's only one way to flawlessly recover, and that has to be done using a delicate manual process prior to the server daemon being stopped. The immediate issue, though, is that files that should be in /var/lib/mysql/mysql can't be located.
Jan
3
comment relationships in access
The relationships should reference the primary key of the "ips" table, and the "IP ID" is what should be stored in the referencing columns.