Reputation
11,620
Top tag
Next privilege 15,000 Rep.
Protect questions
Badges
1 13 32
Newest
 Caucus
Impact
~714k people reached

Apr
1
reviewed Close Query data from two tables and substitute foreign keys of first table with values of second
Mar
29
comment SQL Query challenge
Notice now that one of the top two points is different, (5,1) replaces (4,3). :) Now, it appears you've actually asked for two different things... find the top line... and identify "whether new data steps above this line". Which of these two is the one you are looking to solve? Find the line for a set of data? Or given the two points on the already-known line, identify whether the data points in the table are above, below, or on it?
Mar
29
comment SQL Query challenge
Verify the accuracy of your illustration... it seems like if (2,8) and (4,3) are true then you have a rise/run of -2.5/1 so the line would intersect (5,0.5) so (5,1) is actually above the line, not below. Right?
Mar
29
comment I want to rewrite my MySQL query so that FIND_IN_SET doesn't do a full table scan unless necessary
ON table2.user_id-[some id] ... you're subtracting ids, here? Also, please post the query plans. I don't see a condition that should trigger a full scan, even with FIND_IN_SET() used, assuming user_id is the leftmost column in the primary key. If it isn't, then it needs its own index.
Mar
11
comment Prevent Reconnections During Application Cluster Restart
Excellent. I'll post an answer with an explanation of why I made that suggestion.
Mar
7
comment Concurrency when simulating autoincrement with triggers on MySQL
InnoDB doesn't require the columns referenced by a foreign key constraint to be the primary key -- only that they be in an index together, in the correct order, as the leftmost columns of the index in the parent table. This suggests you're going to a lot of unnecessary trouble, and you could just stick with ordinary auto-increment primary keys.
Mar
6
comment Problem moving database to new server
Right, but did you also verify that you are actually logging in as the user that you believe you're logging in as? Next step, the MySQL error log... particularly any errors or warnings on startup related to the system grant tables. Especially if you have copied this data from an older version of MySQL Server.
Mar
6
comment Problem moving database to new server
First step... Inside query browser, run the query SHOW GRANTS; to be certain you have actually connected with the credentials you used at the login dialog, and that you have the permissions you expect to have.
Mar
4
comment what will happen to the binary log if it reaches its maximum value ? Does it reset to mysql-bin.00001 again?
It will continue growing past 6 digits and will not throw an error until mysql-bin.2147483647 is reached... over two billion binlogs.
Mar
3
answered what will happen to the binary log if it reaches its maximum value ? Does it reset to mysql-bin.00001 again?
Mar
3
comment Prevent Reconnections During Application Cluster Restart
Have you considered the possibility of increasing the thread_cache_size in the MySQL server? If the overhead is really related to a large number of reconnects in a short time, that should help.
Feb
21
answered If all tables are myisam, should I use myisam engine instead of default innodb?
Feb
21
comment If all tables are myisam, should I use myisam engine instead of default innodb?
Changing the storage engine of tables in the mysql schema is generally unsupported and should not be attempted.
Feb
19
comment SELECT RELEASE_LOCK causing “Mysql has gone away”
@MatthewHaworth did you take anything away from the comment above? What is happening in the time right before the error? Is the server sending data, or just processing data internally (stored procedure), or...? If you issue another query immediately before you release the lock (e.g. SELECT NOW():) what happens?
Feb
18
comment SELECT RELEASE_LOCK causing “Mysql has gone away”
@MatthewHaworth comically, I ran into a ERROR 2013 (HY000): Lost connection to MySQL server during query this morning. In this case, the connection wasn't strictly idle -- the server was trying to send data and the application wasn't ready. When the net_write_timeout timer expires before the client is ready to read, the server drops the connection and this error results when the client is ready. If you're supposed to be reading from the server, and you keep it waiting too long, the symptom is the same. Is that the case, here, or is the connection idle?
Feb
18
comment SELECT RELEASE_LOCK causing “Mysql has gone away”
Here's a command line one-liner: perl -e '$|=1; $i = 0; while (1) { warn "sending query..."; print "SELECT $i,NOW(),CONNECTION_ID();\n"; sleep 1; $i += 60; warn "wait $i seconds...\n"; sleep $i-1; }' | mysql --table --host={hostname} --user={username} --password={password} ... at some point, I'd expect an infinite timeout or "MySQL server has gone away" followed by the displayed connection_id changing since the command line client should try to reconnect.
Feb
18
comment SELECT RELEASE_LOCK causing “Mysql has gone away”
@MatthewHaworth I'm skeptical: the hosting provider's assertion doesn't mean much to me... not only because I'm jaded from experience and don't trust vendors :) but also because in a stateful firewall, timeouts often do not need to be configured. They are there, enabled, often silently and by default. In cheaper hardware, they are there and can't be configured. If this were my issue, I would be attempting to prove or disprove that it is a timeout.
Feb
17
answered SELECT RELEASE_LOCK causing “Mysql has gone away”
Feb
13
comment How to use MySQL stored function argument to refer to column name?
It works, but it shouldn't be done. Objects aren't data and data isn't objects. Crossing that boundary reeks of bad design and introduces potential security vulnerabilities. Try running CALL showUnique('1) UNION ALL SELECT user FROM mysql.user); #');, for example.
Feb
13
comment InnoDB Deadlocks Involving Currency Transactions
Ah, but if I'm reading the deadlocks correctly, you had 23093 row locks and a lock on the primary key all held by trx 2, which suggested a full table scan was being done to find the rows to update, in the absence of that index. Adding that index should result in a lot fewer rows being locked when that "session_lastclick" query runs, reducing the chance of a deadlock. Arguably, there's no need to do that second query, since the value of session_lastclick subtracted from the current time could give you a true/false answer to whether a session is "active" whenever you need to know.