Rational Relational

Thank you very much for this command line :for PID...

2008-07-23T12:29:00.000+03:00

Thank you very much for this command line :

for PID in `mysql -u user -ppass -e "show processlist" | grep Sleep | cut -f1`
do
mysql -u user -ppass -e "kill $PID"
done

Before I did all manually, thank you !!

Fatiha

Didn't get in-depth into what you've written here,...

2008-07-12T17:53:00.000+03:00

Didn't get in-depth into what you've written here, but regarding this:
There is probably some linux command that enables buffering the result in between the commands.

The pipe buffer in Linux, defined by the PIPE_SIZE constant, is 4K. It's hardcoded into the kernel. I guess you could solve this with a mediator, such as this (found through Google, didn't test):
http://www.glines.org/wiki/bfr

As a side note, turns out enlarging your pipe buffer does not improve performance:
http://ussg.iu.edu/hypermail/linux/kernel/0001.1/0401.html

thanks, this is great!

2008-07-09T04:37:00.000+03:00

thanks, this is great!

no doubt great post, try sqlyog, it offers cool db...

2008-06-29T20:52:00.000+03:00

no doubt great post, try sqlyog, it offers cool db management tool & is under open source which has made backing up MySQL databases far easier than using the CLI interface. Its layout and UI is also a tremendous improvement over the Administrator GUI from MySQL AB themselves. i was suggested by few of my collegues. u should also go with this. highly recommendable.

I probably should.. It needs a bit of clean-up & e...

2008-06-19T11:59:00.000+03:00

I probably should.. It needs a bit of clean-up & error handling etc before that...

And thanks!
I've got your book here on my bookshelf at work, by the way :)

Hi Shlomo! Feel free to add your code as a code s...

2008-06-18T18:03:00.000+03:00

Hi Shlomo! Feel free to add your code as a code snippet on forge.mysql.com!

good stuff!

Another way to do it atomically for an entire data...

2008-06-18T01:20:00.000+03:00

Another way to do it atomically for an entire database is:

create database dummy_db;

for each table in db:
create table x_new like x;
..

rename table x to dummy_db.x_old, x_new to x,
y to dummy_db.y_old, y_new to y,
...;

drop database dummy_db;

Tomer: According to the manual, the mysqldump does...

2008-06-18T00:42:00.000+03:00

Tomer: According to the manual, the mysqldump does not buffer contents in memory by default, when dumping table data. There is a config option "--skip-quick" that enables buffering. Then again, that's only for table data, not for DDL.
That's an interesting point tho. It doesn't happen in reality (at least it didn't to me) but it could happen. There is probably some linux command that enables buffering the result in between the commands.
Using a temporary file is also an option though.

Tim: This is also a good point. I've wrote this script to truncate tables in a database that has no foreign keys at all, but a lot of tables.
Truncating a lot of interconnected tables could be a problem, since AFAIK you can't truncate a table when it has records pointing to it (you can only DELETE from it). I'll need to think of a solution for that case then. Maybe somehow find out who are child tables, and truncate them before others...

Using FOREIGN KEYs also helps since the TRUNCATE o...

2008-06-18T00:16:00.000+03:00

Using FOREIGN KEYs also helps since the TRUNCATE on the parent table also nukes all the children.

If the mysql-dump command isn't buffered this coul...

2008-06-17T22:50:00.000+03:00

If the mysql-dump command isn't buffered this could cause huge problems (imagine the drop command executed by MySQL while the dump is still underway). This probably isn't a problem because the dump program loads all data before rendering the output, but it still seems needlessly risky to me. I'd split those into two separate commands.

תודה, אני מניח? :)

2008-06-17T01:48:00.000+03:00

תודה, אני מניח? :)

שנייה פריימק, אז הבלוג הזה בעצם אומר שעכשיו אתה "מ...

2008-06-16T23:57:00.000+03:00

שנייה פריימק, אז הבלוג הזה בעצם אומר שעכשיו אתה "מומחה" כזה? :)

סתאאם, אחלה מאמרים!

חברך ל-"חוליית" ה-RDBMS

There is a snippet on the Forge site that uses a S...

2008-06-13T20:18:00.000+03:00

There is a snippet on the Forge site that uses a Stored Proc & Cursor to kill long running queries (contributed by Matt Montegomery). To make this a fully automated query killer all you need to do is setup an Event :)

http://forge.mysql.com/tools/tool.php?id=106

was not only ugly, but wrong... corrected:mysql -B...

2008-06-13T05:21:00.000+03:00

was not only ugly, but wrong... corrected:

mysql -B -e "SHOW FULL PROCESSLIST;"| tee /tmp/mysqlprocesslist`date "+%Y-%m-%dT%H:%M:%S"` | grep -Ewv "Sleep|INSERT|UPDATE|REPLACE|Binlog Dump|NULL|Command" | grep SELECT | sort -n -r -k 6 | head -n 100 | awk -F \\t '{print $1,$6}' | grep -v " [1-2][0-9]"$ | grep -v " [0-9]"$ | awk '{print $1}' | xargs | sed 's/ /,/g' | xargs mysqladmin kill

It's not pretty and it's only for the 'nix-minded,...

2008-06-13T04:36:00.000+03:00

It's not pretty and it's only for the 'nix-minded, but I've found this useful when caught in the precarious position of needing to kill a number of uncooperative selects (in the case below batches of longest running 100, running >=30s)...

mysql -B -e "SHOW FULL PROCESSLIST;"| tee /tmp/mysqlprocesslist`date "+%Y-%m-%dT%H:%M:%S"` | grep -Ewv "Sleep|INSERT|UPDATE|REPLACE|Binlog Dump|NULL|Command" | grep SELECT | sort -n -r -k 6 | head -n 100 | awk -F \\t '{print $1,$6}' | grep -v [1-2][0-9]$ | grep -v " [0-9]"$ | xargs mysqladmin kill

Anonymous: Yes, it actually is a new addition in 5...

2008-06-13T03:01:00.000+03:00

Anonymous: Yes, it actually is a new addition in 5.1. Look at this manual page, in both 5.0 and 5.1 versions:
http://dev.mysql.com/doc/refman/5.1/en/information-schema.html
http://dev.mysql.com/doc/refman/5.0/en/information-schema.html
There are a lot more tables in the new soon-to-be-GA-nearly-almost-no-critical-bugs version.

Sheeri: Well, nothing beats a good linux command line :) Unfortunately, If you're working with windows as your client side, most of these tools are not available by default.

Arjen: Yes, I agree. I've added the "Sleep" condition as just an example to emphasize the fact this is a regular table which can be queried against - maybe not the best example at that.

The sleeping connections is just a bad example. It...

2008-06-13T02:35:00.000+03:00

The sleeping connections is just a bad example. It means either persistent connections have been used where they shouldn't have, or the app is behaving silly. Easy MySQL-side workaround for this is
SET GLOBAL wait_timeout=10;

No need to mess with scripts of any kind, anyway ;-)

(that example is for killing queries in the Sleep ...

2008-06-12T23:26:00.001+03:00

(that example is for killing queries in the Sleep mode)

For older systems, you can always use the commandl...

2008-06-12T23:26:00.000+03:00

For older systems, you can always use the commandline, combining mysql -e "show processlist" with grep for filtering and cut for getting the processid. I've done this often, with a shell script like:

for PID in `mysql -u user -ppass -e "show processlist" | grep Sleep | cut -f1`
do
mysql -u user -ppass -e "kill $PID"
done

Is that exclusive to 5.1? I'm not seeing it in 5....

2008-06-12T21:07:00.000+03:00

Is that exclusive to 5.1? I'm not seeing it in 5.0.51a.

mysql> SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST;
ERROR 1109 (42S02): Unknown table 'PROCESSLIST' in information_schema

It was only a minor version upgrade, actually, no ...

2008-06-01T20:18:00.000+03:00

It was only a minor version upgrade, actually, no code changes were needed.

For validation and testing, we have our wonderful QA department.

So upgrading the database server was quick, how lo...

2008-06-01T13:59:00.000+03:00

So upgrading the database server was quick, how long did it take you to plan it and test your application against the new version?

What method did you use to validate that your queries would all work on the new version?

How many changes did you need to make in the application code?

The future is 6.0+, and it's all too far away... :...

2008-04-27T11:50:00.000+03:00

The future is 6.0+, and it's all too far away... :)
I'd use the built-in InnoDB for the mysql schema if I were to decide, but MySQL AB probably avoids depending on it internally due to the fact it's owned by Oracle.

Hi again,MYISAM is the only built-in engine. In th...

2008-04-27T11:29:00.000+03:00

Hi again,
MYISAM is the only built-in engine. In the future MMARlA will be the standard engine.
Maria will offer ACID.
You are right about the locks , but probably you can poll using different lock names. For instance, every event puts its lock name includinga tx table. Or just when finishing inserting a row in a tx table.

Andrey

GET_LOCK() is a great tool, but I don't see how yo...

2008-04-27T10:48:00.000+03:00

GET_LOCK() is a great tool, but I don't see how you can use it in this case. Each connection can only get one lock. You can test for existence of many locks, but you can't assure the order in which the events started. What was your idea?

Regarding the non transactional nature: if you want to do something like:
START TRANSACTION;
CREATE event...
CREATE event...
COMMIT;
And you need to be certain all of the events were added, you can't. Moreover, since it's MyISAM, a crash in the middle of the event creation can even corrupt the table.

Honestly, I really don't understand why MySQL uses MyISAM for the meta-data database.