Michael - sqlbot
  • Member for 9 years, 4 months
  • Last seen this week
  • Cincinnati, OH
MySQL select...into variable yields null, even though data is there
Accepted answer
6 votes

For a while, this one eluded me. I don't like SELECT ... INTO, so I really wanted to blame it for your trouble, but no. Here'e the problem. SELECT id INTO fqdn_id FROM UrlFqdn WHERE authority=...

View answer
Prepared statement execution with variable number of parameters to be bound
Accepted answer
6 votes

At least in this example, there's an easier approach than this. Remember that the optimizer is always trying to plan the query execution in a way that the path involving least amount of work necessary ...

View answer
Mysql privileges and replication
6 votes

You are over-thinking this. Stopping the slave doesn't cause you to lose your binlog position, and changing the replication username and password doesn't, either, as long as that is all you change. ...

View answer
MYSQL query to find records between two date range
Accepted answer
6 votes

Your query has a logical flaw: it tries to find people who are NOT in the list of people who DO have coaching but only if the coaching is NOT in the target window. Instead, join the employee and ...

View answer
What tool do I use to administer MySql?
6 votes

There are three "official" Oracle tools for managing MySQL in the sense you describe, two of which are currently supported. MySQL Workbench I assume is the administration tool that's most analogous ...

View answer
Amazon RDS - and binary log rotation
6 votes

This doesn't appear to be anything intrinsic to MySQL Server, so I can't give you a proper citation on what "causes" this to happen -- but I strongly suspect it is an element of the design of RDS. ...

View answer
Stored Procedure Performance using Temporary Tables
Accepted answer
6 votes

To answer a question that arises from the comments, temporary tables in MySQL are owned by and only available to the client connection (session, thread) that created them, so there's not an isolation ...

View answer
MYSQL ERROR 2049 (HY000): Connection using old (pre-4.1.1) authentication protocol ref used (client option 'secure_auth' enabled)
Accepted answer
6 votes

This is not a bug if you have user accounts with passwords that used the ancient old hashing algorithm. If you read the bug report mentioned in the link you posted: http://bugs.mysql.com/bug.php?id=...

View answer
MySql is not optimizing the query properly
Accepted answer
6 votes

The MySQL optimizer cannot optimize expressions in this format: WHERE (col_1,col_2) IN ((a,b),(c,d),(e,f)) It's not a matter of getting the indexes right -- it appears that it's just not implemented....

View answer
Will creating a view on a MySQL replication slave break the replication?
Accepted answer
6 votes

This will work fine. The only caveat is that if you, in the future, create a view on the master with the same view name, in the same schema, replication will stop due to the conflict. Another ...

View answer
How to recover/restore corrupted Innodb data files?
Accepted answer
6 votes

Check the MySQL error log. It looks like trying to read table 44 is actually crashing the server. "Lost connection to MySQL server during query" is (or should be) heart-stopping time for a MySQL DBA ...

View answer
How to properly kill MySQL?
6 votes

It sounds as if your question is less about "how to" shut down MySQL and more about why yours is shutting down so slowly. In my answer to a similar question, I offered some suggestions for a smooth ...

View answer
SUPER privilege not defined for master user in Amazon MySQL RDS
6 votes

You can try to change log_bin_trust_function_creators; however, there is an alternative approach that seems more appropriate when you consider the meaning of that variable: It controls whether stored ...

View answer
processlist showing '%' as host from trigger
Accepted answer
6 votes

Are you doing the insert that fires the trigger from inside a stored procedure? Apparently, when inside a stored procedure, the row matching your thread in information_schema.processlist is populated ...

View answer
MySQL order by - one column conditional on another column
Accepted answer
6 votes

How about... ORDER BY (`status` = 'complete'), `priority` The expression status = 'complete' would resolve to 0 for non-complete, and 1 for complete... exactly the order you want... so you'd get all ...

View answer
Foreign Key Constraint fails
Accepted answer
6 votes

You need to have already declared the table that the foreign key references, before you can define a foreign key that references it. Once you declare the second table, you can then declare the first ...

View answer
Does creating an index on BIT(n) attribute in MySQL make sense?
Accepted answer
6 votes

Unless you are querying for an exact match on the whole value stored in the bit column, an index will not help, because it can't generally be used. On the remote chance that you are, then it would. ...

View answer
AWS RDS MyISAM tables from mysql and information_schema and good snapshots
Accepted answer
5 votes

The MyISAM tables in the mysql schema are fully supported in RDS snapshots. It's the MyISAM tables elsewhere that are potentially problematic. Never alter the tables in the mysql schema. That will ...

View answer
Use of GROUP_CONCAT on Mysql
5 votes

Your interests are generally not well-served by learning somewhat vague "rules" like "OR should be avoided" or "group_concat is slow." Neither is actually correct, they only have elements of truth ...

View answer
What's the maximum acceptable latency if using a remote MySQL DB server?
5 votes

There is no single correct answer to this question. The MySQL client/server protocol has no limitations on toleration of round trip time, but responsiveness of your application can be dramatically ...

View answer
How to prevent duplicate VARCHAR without a key limit?
5 votes

We keep giving you answers that do not directly answer the question, because that is how we solve this problem. An index of unlimited length is impractical and inefficient, but a unique hash provides ...

View answer
MySQL Replication query - Primary key need
5 votes

Your assumption is incorrect. The hidden clustered index does nothing to help, here, because the server can't use that index to find rows in a way that replication needs. This causes some problems ...

View answer
Optimizing a slow query to better use indexing
Accepted answer
5 votes

Your query seems to be simply this: SELECT time_stop, some_count FROM foo WHERE user_id = 1 AND time_start >= '2016-07-27 00:00:00' AND time_stop <= '2016-07-27 23:59:59' ORDER ...

View answer
update query field order guaranteed
Accepted answer
5 votes

MySQL, when executing an update against a single table, does -- wrongly -- treat updates as "ordered," and update the columns in the order they're written in the query. The second assignment in ...

View answer
SELECT RELEASE_LOCK causing "Mysql has gone away"
Accepted answer
5 votes

The most likely explanation for this is that an intermediate piece of network hardware is tearing down or "forgetting" your idle TCP connection, assuming your wait_timeout system variable is not set ...

View answer
Use of IN clause within a DELETE command - MySQL
Accepted answer
5 votes

Yes, you can use IN() in the WHERE clause of a DELETE statement. There is no "IN clause" -- IN() is just a comparison operator, perhaps slightly disguised as something more sophisticated. http://...

View answer
MariaDB not caching any queries
Accepted answer
5 votes

You missed one thing. query_cache_size | 0 To set the size of the query cache, set the query_cache_size system variable. Setting it to 0 disables the query cache, as does ...

View answer
Excluding the standard MySQL databases from replication
Accepted answer
5 votes

There's no need to avoid replicating performance_schema or information_schema -- they are not real databases with real tables, they are generated by server internals to interact via SQL, but you'll ...

View answer
MySQL/MariaDB stored procedure compiles with typos
Accepted answer
5 votes

The explanation here is related to the same situation found in the other question... the expression here is at least potentially ambiguous, so it can't reasonably be validated at define time. It ...

View answer
On which fields to index? Fields queried (WHERE) or fields returned (SELECT)
Accepted answer
5 votes

The accepted answer overlooks the concept of covering indexes, and also does not mention the importance of indexes on multiple columns, together in one index. A single index over both columns in the ...

View answer
1 2
3
4 5
17