Michael - sqlbot
  • Member for 9 years, 4 months
  • Last seen this week
  • Cincinnati, OH
Is there a graceful or safe restart for mysql like for apache httpd?
Accepted answer
43 votes

Any "requested" shutdown sequence in MySQL (short of kill -9) is somewhat graceful, since transactions in progress (on transactional tables) are rolled back, but here are a couple of ways to make a ...

View answer
What is the optimal way to upgrade production RDS instance?
40 votes

Upgrading an instance in RDS means RDS will be physically migrating the database to a new instance, likely on a different physical host, so downtime would not be avoidable. Migrating to provisioned ...

View answer
intermittent MySQL crashes with error "Fatal error: cannot allocate memory for the buffer pool"
Accepted answer
36 votes

Please review my answer to this recent question. I believe the circumstances are identical. Do not change your MySQL configuration at this point, as MySQL is not the problem -- it's only a symptom ...

View answer
MySQL InnoDB crash post-mortem
Accepted answer
34 votes

I have good news, and bad news. The good news is, your filesystem and mysql are most likely fine... but check /var/log/syslog or equivalent to see what else was happening on your system before 10:21:...

View answer
Is it possible to modify an existing trigger definition in MySQL?
Accepted answer
31 votes

It sounds like you're asking whether it's possible to alter a trigger in an atomic operation, where, if the new definition fails, you don't lose the old one... similar to CREATE OR REPLACE VIEW, which ...

View answer
MySQL -> Loop through a table, running a stored procedure on each entry
Accepted answer
25 votes

Create a second procedure that uses two nested cursors. Cursors in stored procedures allow you to do a very non-SQL-like thing: iterate through a result set one row at a time, putting the selected ...

View answer
Is there a tool to check if my database is normalized to the third normal form?
Accepted answer
25 votes

Normalization absolutely is used in the real world... and hopefully you know that 3NF is only the third one of... what is is now, 8? But 3NF should be an easy target. However... I would venture to ...

View answer
MySQL 5.6 DateTime Incorrect datetime value: '2013-08-25T17:00:00+00:00' with Error Code 1292
Accepted answer
25 votes

'2013-08-25T17:00:00+00:00' This is a valid iso-8601 datetime value, but it is not a valid MySQL datetime literal. On that point, the developer is incorrect. The documentation explains what ...

View answer
mysqladmin not taking inline password
Accepted answer
21 votes

Command line arguments are subject to interpretation by the system's command shell, changing the behavior of the command or changing the value of the arguments before they are passed into the called ...

View answer
Qcache_free_memory not full yet I get alot of Qcache_lowmem_prunes
Accepted answer
21 votes

The query cache is a very nice feature, but don't be tempted to pay too much attention to it and don't be tempted to make it too large. Understanding some of its internals will probably help in that ...

View answer
What's the difference between POINT(X,Y) and GeomFromText("POINT(X Y)")?
Accepted answer
20 votes

There are two different binary formats related to the MySQL spatial extensions, the "well-known binary" (WKB) format from the standards, and the MySQL internal GEOMETRY data type. Prior to MySQL 5.1....

View answer
Error while restoring a Database from an SQL dump
20 votes

The reference to --binary-mode (introduced in MySQL 5.6.3) is probably a distraction. It doesn't sound like you're dealing with a mysqldump output file, there. Try the file utility. shell> ...

View answer
Amazon RDS for MySQL vs installing MySQL on an Amazon EC2 instance
Accepted answer
20 votes

I'm a big AWS fan in general... but RDS, not so much. @RolandoMySQLDBA has pointed out are some pretty good points against it. The only advantage I see in RDS compared to MySQL on EC2 are the ...

View answer
Why does MySQL ignore the index even on force for this order by?
20 votes

Actually, the problem here is that this looks like a prefix index. I don't see the table definition in the question, but sub_part = 700? You haven't indexed the whole column, so the index can't be ...

View answer
How to determine master in mysql master-slave
Accepted answer
19 votes

@RolandoMySQLDBA has answered the question accurately ... but he also pointed out that his solution was "quick-and-dirty." And that is a very true statement. :) The thing that concerns me here is ...

View answer
Making sense of INNODB buffer pool stats
Accepted answer
18 votes

Buffer pool hit rate is 1000 / 1000 This is the only really meaningful value in the situation that you are in... and that situation is that you are lucky enough to have a buffer pool with a perfect ...

View answer
How long will it take to rename table
17 votes

Renaming a table in MySQL does not require a temporary table. There are two statements that perform an equivalent operation. RENAME TABLE t1 TO t2; ALTER TABLE t1 RENAME TO t2; # as long as no other ...

View answer
Mysql Performance optimization, max CPU load, low qps
Accepted answer
16 votes

Based on reading the query plan you've posted from the explain output, you will probably have difficulty believing the explanation of how this query is actually being processed by the server... but ...

View answer
Autoincrement primary key in CREATE TABLE ... AS SELECT
Accepted answer
15 votes

CREATE TABLE `user_mv` (id INT AUTO_INCREMENT PRIMARY KEY) SELECT `user`.`firstname` as `firstname`, `user`.`lastname` as `lastname`, `user`.`lang` as `lang`, `user`.`name` as `user_name`,...

View answer
What is wrong with table_cache hit rate?
Accepted answer
15 votes

One thing, here, is that you should be using this form, instead: mysql> show global status like '%open%'; Some of these counters are global and some of them are session, so not using the GLOBAL ...

View answer
MySQL possibility to create global routines (stored procedures and/or functions)
Accepted answer
14 votes

There is no way to define stored procedures or stored functions (or events) that are global. One approach is to create a shared common schema and then qualify the calls to the functions and ...

View answer
How to get MAX and MIN in GROUP BY query?
Accepted answer
14 votes

Would you believe... SELECT col1, MIN(col2), MAX(col2), MIN(col3), MAX(col3) FROM table1 GROUP BY col1; ...? each row includes the first value of col2 and col3 for each unique value of col. ...

View answer
ERROR 1067 (42000) at line 7: Invalid default value for 'LAST_UPDATED'
12 votes

This error can occur when you attempt to import a file into MySQL Server 5.6.4 and earlier, when the database was created on and exported from MySQL 5.6.5 or later. As of MySQL 5.6.5, TIMESTAMP ...

View answer
How to "warm up" a cold DB server?
Accepted answer
12 votes

Database "warm up" is a real thing... but then again, people making excuses for under-performing systems is also a real thing. An important take-away here is that once a server is warmed up, it doesn'...

View answer
Create a trigger to update table data on another Server's database
12 votes

To accomplish what you are wanting to do, it is possible to use the FEDERATED storage engine on both servers, in conjunction with triggers, to allow each server to update the other server's database. ...

View answer
format of mysql query log
12 votes

In the MySQL General Query Log, the fields are these: yymmdd hh:mm:ss thread_id command_type query_body The timestamp only appears each time it changes. In your example "133" would be the thread id ...

View answer
Mysqldump treats some table as View
Accepted answer
11 votes

So for some tables it looks like Well, it should be that way for all the tables. That message is generated by the --verbose option, and you should see it on all the tables, when mysqldump makes its ...

View answer
What are the ports and directionality of the TCP connections used in MySQL replication?
11 votes

Your assumptions are incorrect. The slave does not bind to 3306 (or whatever port it is listening to) when making its outbound connection to the master, and the port the slave is "listening to" for ...

View answer
How to optimize table_cache?
Accepted answer
11 votes

The reason you didn't find an answer is that there's not an answer. The appropriate value for table_cache doesn't correlate to an amount of system memory. Note that table_cache was renamed ...

View answer
Can I configure MySQL's typecasting to consider 0 != 'foo'?
Accepted answer
11 votes

The problem, though, is that you are supplying a numeric value, unquoted, so MySQL tries to cast the other side of the comparison as a DOUBLE, to compare it with the numeric argument you've supplied......

View answer
1
2 3 4 5
17