10,417 reputation
11127
bio website
location Cincinnati, OH
age 43
visits member for 2 years, 4 months
seen 12 hours ago

If you question or otherwise fail to recognize the superiority of the relational model... you're doing it wrong.


15h
answered On not public server: Host name example.com could not be resolved: Name or service not known
16h
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).
23h
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.
23h
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).
1d
comment Access denied for user to database 'mysql' in amazon RDS
What is the specific statement in the backup file that's failing? I strongly suspect that RDS limits many actions against the "mysql" (system) database and you may need to remove all the DDL from your file and change the INSERT statements into REPLACE statements, for tables in the "mysql" database. If this is the case, it can be automated.
1d
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.
1d
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.
1d
answered Can a trasaction be rolled back if a table is edited outside a transaction?
1d
comment mysqldump: Got error: 1045: Access denied for user 'root'@'localhost' (using password: YES) when trying to connect
Are there any non-alphanumeric characters in the password?
2d
comment Can an index be added to a PRIMARY with a null key?
You can't really "optimize" this query. You need to throw it away and completely rewrite it without the FROM ... JOIN (SELECT ... derived tables, whose purpose is not obvious here. You are materializing the innermost query into a temp table, and doing a full scan of it, repeatedly, almost completely defeating all of your indexes. Using ANSI joins, expressed with t1 JOIN t2 ON instead of joining in WHERE would go a long way toward readability, and consider that DISTINCT is often, through not always, a red flag that says "I got more rows than I expected and I don't understand why."
2d
comment Manage orders based off product attributes and quantity
Food for thought, it's potentially a fundamental error to assume the price and weight of a "S" and an "XXL" are necessarily always the same or that a "red" item and a "blue" item might not be priced differently... or even that a "black" item purchased today is the same price as one purchased tomorrow.
2d
awarded  Revival
Jan
25
answered Alter doesn't show in processlist
Jan
24
comment MySQL profile on query “Creating Sort Index” using 75% of the total time
Are you familiar with EXPLAIN SELECT? Without the query plan and SHOW CREATE TABLE... statements in hand, we can't really know what's going on. Also, the old style join syntax you're using hurts my head. Consider reading up on ANSI join syntax -- much less error-prone and easier to read, although it's unlikely that the optimizer would plan the query differently. (The formatting, on the other hand, follows the Sqlbot Style Guide almost perfectly, so kudos on that, heh).
Jan
23
awarded  Enlightened
Jan
23
awarded  Nice Answer
Jan
22
answered Mysql 1 way replication master lost data replication still has
Jan
22
comment MySQL - Migrating from Master-Slave replication to Master-Master
At the risk of seeming unhelpful, this doesn't seem like a good idea using native master/master (circular) replication, since it has no conflict resolution mechanism. At least, not unless you only intend to use 1 master for writes and the other two as online backup masters. What's the motivation for the change? Have you read up on Galera Cluster for MySQL?
Jan
21
answered How to get something written to MySQL error log without restart
Jan
21
revised what does !! as an operator in mysql?
added the (obvious, but should have been included) case of !! by itself