MySQL Preacher

What makes you think jdbc autoreconnect is needed?
Application is idle for long periods at a time?
Wait_timeout too short?
Network failure or glitches?

Some good suggestions form Mark Matthews - Bug #5020

Having encountered the problem again myself today, trying to make jdbc for mysql reconnect any terminated connections using autoreconnect=true I figured out a way to work it out from the pooling side.

Introduction to the problem:

On the mysql side wait_timeout is set to default 8hrs and any connections idle for longer than that were beomg terminated despite setting the connection string to: url=jdbc:mysql://localhost:3306/dbname?autoReconnect=true. The application was thence throwing an exception.

The solution was to introduce a ping from the pooler which for “Ibatis”, the pooler technology used in this case, was:

This covers eventualities of network glitches or connections exceeding the wait_timeout having the pooler ping the database if the connection was idle for more than 10 seconds (the value is in milliseconds).

The configuration if Ibatis in this case is something like:

Ibatis


For every pooling technology there will be a different “Pool.PingConnectionsNotUsedFor” term, but the concept is still there. It worked like a charm.


JDBC driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/dbname
username=username
password=password

PS … autoreconnect is not recommended when using mysql jdbc!

MySQL, Networking autoreconnect, connector, ibatis, jdbc, keepalive, network, ping, wait_timeout

Are you doing some datetime manipulation or maybe you are migrating from some database technology to MySQL or possibly using milliseconds?
Here is an example on how to go about it:

Say you have the following date: MAR 16 2008 09:12:51:893AM
SELECT DATE_FORMAT(STR_TO_DATE('MAR 16 2008 09:12:51:893AM','%M %d %Y %h:%i:%s:%f%p'),'%Y%m%d%k%i%s.%f'); --> 2008031691251.893000


What if its PM rather than AM
SELECT DATE_FORMAT(STR_TO_DATE('MAR 16 2008 09:12:51:893PM','%M %d %Y %h:%i:%s:%f%p'),'%Y%m%d%k%i%s.%f'); --> 20080316211251.893000


Ok so this is just simple string manipulation where:
%M is the month name
%d is day number
%Y is the year
%h is the hour
%i is the minute
%s is the second
%f is the microsecond
%p is the period: ante or post meridiem
In the DATE_FORMAT part we se a %k which is in 24hr format in order to loose the period.

A more detailed list is found here

Here is a demo:

mysql Tue Jun 15 12:32:37 2010 > CREATE TABLE test.abc(a DECIMAL(17,3)) ENGINE=MYISAM;
Query OK, 0 rows affected (0.03 sec)

mysql Tue Jun 15 12:32:45 2010 > INSERT INTO abc VALUES ( DATE_FORMAT(STR_TO_DATE('MAR 16 2008 09:12:51:893PM','%M %d %Y %h:%i:%s:%f%p'),'%Y%m%d%k%i%s.%f') );
Query OK, 1 row affected (0.01 sec)

mysql Tue Jun 15 12:32:51 2010 > SELECT * FROM abc;
+--------------------+
| a                  |
+--------------------+
| 20080316211251.893 |
+--------------------+
1 row in set (0.00 sec)

mysql Tue Jun 15 12:32:56 2010 > SELECT TIMESTAMP(a) FROM abc;
+----------------------------+
| TIMESTAMP(a)               |
+----------------------------+
| 2008-03-16 21:12:51.893000 |
+----------------------------+
1 row in set (0.00 sec)

Advanced, Intermediate, MySQL datetime, date_format, microsecond, migrating to mysql, migration, millisecond, mysql millisecond, str_to_date, timstamp

What to do when you have too many warnings and would like to check them out.

Say you were trying to run a query which returned a heap of warnings and you want to go through the list ….

mysql> load data infile '/aaa/bbb/ccc' into table xyz LINES TERMINATED BY '\r\n';
Query OK, 0 rows affected, 65535 warnings (4 min 25.12 sec)

mysql> show warnings;
+---------+------+------------------------------------------------+
| Level   | Code | Message                                        |
+---------+------+------------------------------------------------+
| Warning | 1366 | Incorrect value: '' for column 'xx' at row 1   |
...
| Warning | 1366 | Incorrect value: '' for column 'xx' at row 9   |
| Warning | 1366 | Incorrect value: '' for column 'xx' at row 10  |
+---------+------+------------------------------------------------+
64 rows in set (0.00 sec)

As per above only 64 rows are show. Why?

mysql> show variables like 'max_error_count';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_error_count | 64    |
+-----------------+-------+
1 row in set (0.00 sec)

Ok so … what is the solution?

mysql> set max_error_count=65535;
Query OK, 0 rows affected (0.00 sec)

mysql> pager more
PAGER set to 'more'

Here I am setting the max_error_count to a high value only for the session and not globally.

Details here

The “pager more” will work only on *nix systems where the output will be “piped” to the `more` command on your OS and displayed in batches so you can pause the scrolling of the long output.

Having said the above, from my experience, when you have that many warnings, it’ll be a repetition of the same error or group of errors.

MySQL max_error_count, pager, pager more, show warnings, warning

If you are attending this year’s O’Reilly MySQL Conference & Expo, it is with great pleasure that I invite you to attend my presentation featuring Securich, the MySQL user administration and security plugin. It is a user friendly tool which will help make user management and general database security much more versatile and fun than ever before.

The session will be held at 10:50am on Wednesday 14th April 2010 at BallRoom A.

http://en.oreilly.com/mysql2010/public/schedule/detail/13351

So come on over and `grant all on *.*` no more :)

Hasta luego mez amis
Haben eine sichere fahrt

Uncategorized

If you have multiple database servers with strange names, or if you have to hop over multiple machines to connect to any mysql database server, then you know what a pain it can be to administer such a setup. Thanks to some scripting, you can automate such tasks as follows:

Create an expect script:
/path/to/sshmysql.exp

#!/usr/bin/expect -f
#script by darren cassar
#mysqlpreacher.com

set machine [lindex $argv 0]

set timeout -1

spawn ssh username@$machine
match_max 100000
expect -exact “assword: ”
send — “password\r”
send — “sudo -k; sudo su – mysql\r”
expect -exact “sudo -k; sudo su – mysql”
expect -exact “assword:”
send — “password\r”
interact

# you should change the word password in ’send — “password\r”‘ to your login password
# if you have the same password for each environment you could also script logging into mysql directly from the same expect script BUT that is not recommended.

Create a bash script:
/path/to/login.sh

#!/bin/bash
#script by darren cassar
#mysqlpreacher.com

sm=’/path/to/sshmysql.exp’

menu() {
echo ” 101 – dev.databaseserver1 ”
echo ” 102 – dev.databaseserver2 ”
echo ” 103 – dev.databaseserver3 ”
echo ” 201 – qa.databaseserver1 ”
echo ” 301 – uat.databaseserver1 ”
echo ” 302 – uat.databaseserver2 ”
echo ” 401 – prod.databaseserver1 ”
echo ” ”
}

ARGUMENT=notmenu

if [ -z "$1" ]
then
ARGUMENT=menu
else
choice=$1
fi

if [ $ARGUMENT = "menu" ]
then
menu
else
case “$choice” in
101|dev.databaseserver1 ) $sm dev.databaseserver1;;
102|dev.databaseserver2 ) $sm dev.databaseserver2;;
103|dev.databaseserver3 ) $sm dev.databaseserver3;;
201|qa.databaseserver1 ) $sm qa.databaseserver1;;
301|uat.databaseserver1 ) $sm uat.databaseserver1;;
302|uat.databaseserver2 ) $sm uat.databaseserver2;;
401|prod.databaseserver1 ) $sm prod.databaseserver1;;
* ) echo “Wrong value passed to script”
menu ;;
esac
fi

alias l=’/path/to/login.sh’

Output:

[darrencassar@mymachine ~ ]$ l
101 – dev.databaseserver1
102 – dev.databaseserver2
103 – dev.databaseserver3
201 – qa.databaseserver1
301 – uat.databaseserver1
302 – uat.databaseserver2
401 – prod.databaseserver1

Output:
The below command would log you into the first development database server as mysql user.

[darrencassar@mymachine ~ ]$ l 101

On each machine place aliases for each instance in the .profile

alias use3306=’mysql -u root -p -h 127.0.0.1 -P 3306 –prompt=”mysql \D> “‘

The above setup can be used using any client/server OS: Linux, Solaris, MAC OS or Windows(running Cygwin)

NOTE: If you store the password in clear text inside the expect script, you should at least save the scripts inside an encrypted partition on your machine and make sure that folder is not shared or accessible by anyone. Another way of doing it would be to use either SSHKeys OR save the password inside a file and encrypt it using OpenSSL

Enjoy!

Databases, Intermediate, OS, Uncategorized access, automation, bash, expect, MySQL

Once every so often something bugs me enough to look up and here is today’s bugger! Sorting files and folders in Mac’s Finder by type, folders first!

In essence:
1. Select Finder, click on Go from the menu bar and select Go to Folder
2. From the dialog box that opens, type:
/System/Library/CoreServices/
and press Return
3. Locate Finder application, right click on it, and select Show Package Contents
4. Navigate to Contents>Resources>English.lproj and open InfoPlist.strings in a text editor
5. Navigate to the line which says
“Folder” = “Folder”;
at around line 7 of the file, and simply add an empty space or a symbol (such as “.” or “~”) in front of the second “Folder”.
6. Save the text file, type in the Administrator password when asked, and close the text file.
7. The final step would be to restart finder, which you can achieve by holding in the ALT or OPTION key and right clicking on the Finder icon in Dock, then selecting Relaunch.

Full detailed tutorial can be found here: thanks to “Budding”

Mac OS, OS Finder, folders, Mac, sort

A MySQL is running happily on a machine situated in a land far far away. I grant access to a user@machine_aaaaaa (grant select on db.* to ‘user’@'machine_aaaaa’ identified by ‘password’; flush privileges;), send an email to the user saying it should run fine and happily go off my way. Mistake!

It seems this user can’t connect to the mysql gets access denied:
Access denied for user ‘user’@'machine_bbbbb’ (using password: YES)

Note that the machine the user is being seen from is totally different from the one I set up in the grant!! WHY?

run a reverse lookup on the ip of machine_aaaaa, turns out it shows machine_bbbbb. So I figure a big bad guy messed up /etc/hosts, I was right! `cat /etc/hosts` just to find an entry for machine_aaaaa blehh

Ok, solution is to remove the entry from /etc/hosts (after finding out it wasn’t even necessary and wasn’t even supposed to be there in the first place), restart nscd.

Retry
AGAIN – Access denied for user ‘user’@'machine_bbbbb’ (using password: YES)

What the ….

What’s wrong now? — yeah silly me forgot to `flush hosts` :)

Retry
YOHOO I’m in!

I’ve seen quite a few blogs about disabling name resolve in mysql with skip-name-resolve and granting privileges using IPs – something which would also have avoided the above (but still not found the root of the problem):

http://jeremy.zawodny.com/blog/archives/011421.html
http://www.mysqlperformanceblog.com/2008/05/31/dns-achilles-heel-mysql-installation/

Databases, MySQL, Networking, OS, Solaris cache, dns, grants, hosts, Networking, skip-name-resolve

WIKIPEDIA -

Irony (from the Ancient Greek εἰρωνεία eirōneía, meaning hypocrisy, deception, or feigned ignorance) is a situation, literary technique or rhetorical device, in which there is an incongruity, discordance or unintended connection that goes beyond the most evident meaning. It is the expression of one’s meaning by using language that normally signifies the opposite.

Types of irony
Modern theories of rhetoric distinguish among verbal, dramatic and situational irony.
▪ Verbal irony is a disparity of expression and intention: when a speaker says one thing but means another, or when a literal meaning is contrary to its intended effect. An example of this is sarcasm.
▪ Dramatic irony is a disparity of expression and awareness: when words and actions possess a significance that the listener or audience understands, but the speaker or character does not.
▪ Situational irony is the disparity of intention and result: when the result of an action is contrary to the desired or expected effect. Likewise, cosmic irony is disparity between human desires and the harsh realities of the outside world (or the whims of the gods). By some definitions, situational irony and cosmic irony are not irony at all.

Databases, MySQL mysql database connection failed