BUG: motion won't reconnect to mysql server if it's disconnected

I've had problems with motion getting 'stuck' if the mysql server dies or gets restarted. My expectation is that motion would try to reconnect to it. What it does now is just fill the log with the following message:

Jun 27 09:24:12 surv motion: [2] Mysql query failed: Success

motion.conf:sql_query insert into security(camera, filename, frame, file_type, time_stamp, text_event) values('%t', '%f', '%q', '%n', '%Y-%m-%d %T', '%C')


 describe security;
+------------+-----------+------+-----+-------------------+-------+
| Field      | Type      | Null | Key | Default           | Extra |
+------------+-----------+------+-----+-------------------+-------+
| camera     | int(11)   | YES  |     | NULL              |       |
| filename   | char(80)  | NO   | MUL |                   |       |
| frame      | int(11)   | YES  |     | NULL              |       |
| file_type  | int(11)   | YES  |     | NULL              |       |
| time_stamp | timestamp | NO   | MUL | CURRENT_TIMESTAMP |       |
| text_event | char(40)  | YES  |     | NULL              |       |
| thumbnail  | char(80)  | YES  |     | NULL              |       |
+------------+-----------+------+-----+-------------------+-------+

Environment

Motion version:	3.2.7
ffmpeg version:	unknown, but working dandy finally. :/
Shared libraries:	ffmpeg, mysql, postgresql
Server OS:	linux, gentoo.

-- A3ricJohanson - 27 Jun 2007

Follow up

Please upgrade to http://www.lavrsen.dk/twiki/bin/view/Motion/MotionRelease3x2x8beta1 , and try to reproduce the problem

also could be interesting :

What version of Mysql are you using ?

What client library is linked with motion ?

-- AngelCarpintero - 28 Jun 2007

Fix record

Added a patch for mysql 5.x against svn r195.

This patch uses MYSQL_OPT_RECONNECT flag for mysql connection and changed default value for mysql_host ( replacing Not defined by localhost ).

-- AngelCarpintero - 09 Jul 2007

Why default to localhost?

This means that anyone starting Motion without having setup their MySQL with the right tables and user name and password will have an error.

It is probably very few that ever use MySQL with Motion and it is the host being undefined that disable the use of the MySQL feature. You should not have to build Motion without it. If you install an RPM the MySQL support is compiled in the binary but still most will never use the feature.

So it should be disabled by default.

-- KennethLavrsen - 09 Jul 2007

Just checked the code. I was wrong. It is the mysql_db option being undefined that disables the feature so it is OK to have localhost as default for the host.

Go ahead a apply the patch.

-- KennethLavrsen - 09 Jul 2007

Done !

-- AngelCarpintero - 09 Jul 2007