BUG: mySQL not working

I having problems running motion with mySQL. First with motion 3.2.7 the error I was receiving was [2] Mysql query failed[2]: Success but after I tried the svn version I was getting a 1064 error that suggest that I'm running a version of mySQL that isn't compatible.

My box is Fedora Core 5 and mySQL version is 5.0.27

[2] Mysql query failed You have an error in your SQL syntax; check the manual that corresponds to your
 MySQL server version for the right syntax to use near 'sql_query insert into security(camera, filename, 
frame, file_type, time_stamp, e' at line 1 error code 1064: Success

Environment

Motion version:	SVN
ffmpeg version:
Shared libraries:	ffmpeg, mysql, postgresql
Server OS:

-- KevinMifflin - 06 Jan 2007

Follow up

Please be sure that the query in motion.conf and the table you created are "compatible" .

Could you paste the result of describe security from mysql console ?

Could you paste the related config setting of mysql ?

-- AngelCarpintero - 06 Jan 2007

Dump

mysql> describe security;+------------+-----------+------+-----+---------------------+-------+
| Field      | Type      | Null | Key | Default             | Extra |
+------------+-----------+------+-----+---------------------+-------+
| camera     | int(11)   | YES  |     | NULL                |       | 
| filename   | char(80)  | NO   |     |                     |       | 
| frame      | int(11)   | YES  |     | NULL                |       | 
| file_type  | int(11)   | YES  |     | NULL                |       | 
| time_stamp | timestamp | NO   |     | CURRENT_TIMESTAMP   |       | 
| text_event | timestamp | NO   |     | 0000-00-00 00:00:00 |       | 
+------------+-----------+------+-----+---------------------+-------+
6 rows in set (0.00 sec)


############################################################
# Common Options For MySQL and PostgreSQL database features.
# Options require the MySQL/PostgreSQL options to be active also.
############################################################

#Log to the database when creating motion triggered image file  (default: on)
sql_log_image off

#Log to the database when creating a snapshot image file (default: on)
sql_log_snapshot off

#Log to the database when creating motion triggered mpeg file (default: off)
sql_log_mpeg on

#Log to the database when creating timelapse mpeg file (default: off)
sql_log_timelapse off


############################################################
# Database Options For MySQL
############################################################

#Mysql database to log to (default: not defined)
mysql_db 'motion'

#The host on which the database is located (default: not defined)
mysql_host localhost

#User account name for MySQL database (default: not defined)
mysql_user root

#User password for MySQL database (default: not defined)
mysql_password

---

You forgot to paste sql_query , the default in value is motion-dist.conf :

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

So be sure that you are using as sql_query

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

-- AngelCarpintero - 06 Jan 2007

---

I found the same error occurs if I start motion (interactively) in a directory to which I do not have write permission.

(update 11 Feb 2007) Re-reading this bug report though, I ought to mention that the version I'm using is motion_3.2.7-0.ubuntu.edgy_i386.deb not the SVN version.

-- JimAvery - 10 Feb 2007

Fix record

That bug should be fixed in SVN and also in latest snapshot 3.2.8 beta1.

Reopen if still remains.

-- AngelCarpintero - 29 Apr 2007