mySQL: Testing connection with query?

Question

I am writing some unit tests to ensure that everything is working as supposed in my application and thought it would be an good idea to write a short test script to ensure that the mySQL connection is working as intended.

Is there any query I can run that will always output something sweet that I can verify the connection upon, without having to think about eventual stored data in the mySQL database?

score 35 · Accepted Answer · answered Feb 10 '11 at 12:38

35

is there any query I can run that will always output something sweet

This should do it

SELECT 'Something sweet'

Edit
If you don't want something sweet you can always use the built-in functions:

SELECT version()

for more ideas check out the manual:
http://dev.mysql.com/doc/refman/5.1/en/information-functions.html

answered Feb 10 '11 at 12:38

Thanks, that's great, really :) I was however more looking for something like mysql version or so. – Industrial Feb 10 '11 at 12:40
If you want to test this from the command line (e.g. Bash shell): `echo "SELECT version()" | mysql`; then as usual test with `echo $?`, `&&` or `||` concatenation. – Peterino Jun 20 '13 at 12:48

score 1 · Answer 2 · answered Jul 21 '15 at 17:29

1

Most db drivers have a ping() method where they have a mechanism that does exactly what your peers are suggesting.

However show variables and selecting from nothing doesnt expose anything health wise except the database engine, storage could be down, indexes could be corrupt, errors everywhere.

answered Jul 21 '15 at 17:29

hpavc

1,336
7
7

The question was clear and simple. `it would be an good idea to write a short test script to ensure that the mySQL connection is working as intended`. The question was about checking the connection and nothing more. Why did you come up with all this? – mezoni Oct 03 '22 at 10:16

score 1 · Answer 3 · answered Feb 10 '11 at 12:54

To get more details you can also use SHOW statement:

SHOW VARIABLES LIKE 'version%';

+---------------------------------+---------------------------+
| Variable_name                   | Value                     |
+---------------------------------+---------------------------+
| version                         | 5.1.6-alpha-log           |
| version_comment                 | Source distribution       |
| version_compile_machine         | i686                      |
| version_compile_os              | suse-linux                |
+---------------------------------+---------------------------+

http://dev.mysql.com/doc/refman/5.1/en/show-variables.html

mySQL: Testing connection with query?

3 Answers3