I have a python script that deals with MySQL through the mysqldb connector. The same script runs perfectly fine on the my OSX machine while it breaks throwing a MySql error exception when run on an Ubuntu machine.
Details of both the systems below:
OSX -
python --version => Python 2.7.11 (installed though Homebrew)
which python => /usr/local/bin/python
mysql --version => mysql Ver 14.14 Distrib 5.7.10, for osx10.9 (x86_64) using EditLine wrapper
Ubuntu -
python --version => Python 2.7.6 (default system Python)
which python => /usr/bin/python
mysql --version => mysql Ver 14.14 Distrib 5.5.50, for debian-linux-gnu (x86_64) using readline 6.3
The error I receive while running the script on the Ubuntu box is :
Traceback (most recent call last):
File "my_program.py", line 364, in <module>
dao = DataAccessObject(debugger)
File "/home/path_to_file/project/handle_storage.py", line 42, in __init__
self.cur.execute(create_restaurant_table)
File "/usr/local/lib/python2.7/dist-packages/MySQLdb/cursors.py", line 205, in execute
self.errorhandler(self, exc, value)
File "/usr/local/lib/python2.7/dist-packages/MySQLdb/connections.py", line 36, in defaulterrorhandler
raise errorclass, errorvalue
_mysql_exceptions.ProgrammingError: (1064, "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 '(6) NOT NULL, some_time_column DATETIME NOT NULL, record_hash TEXT NOT NULL, PRIMARY K' at line 1")
Please help me understand what is it that is going wrong here? If it's really an issue with my code, as is being suggested by the error message above, why is the script running all fine in case of the OSX machine?
The line on which the query breaks as per the exception above is :
CREATE TABLE IF NOT EXISTS my_table (id int NOT NULL AUTO_INCREMENT, name TEXT, area TEXT, address TEXT, locality TEXT, fee_type TEXT, charge TEXT, cost TEXT, stuff TEXT, rating TEXT, later TEXT, type TEXT, cityCharge TEXT, record_entry_at DATETIME(6) NOT NULL, as_on DATETIME NOT NULL, record_hash TEXT NOT NULL, PRIMARY KEY (id));
Some quick research into the official documentation for the DATETIME data type reveals that the fractional seconds specifier was not introduced until 5.6.4.
MySQL 5.6.4 and up expands fractional seconds support...
This was the path of documentation I followed: