CODEWITHSUNDEEP
pythonmysql
gatorback
gatorback

Reputation: 1537

Python MySQL Connector Error Diagnosis

CONTEXT

Ubuntu 16.04: Python MySQL Connector

OBSERVATIONS

The cursor.execute statement appends the target record to a MySQL 5.5.8 database. Attempts to append to MySQL 8.0.3 result in the error below:

>>> cursor.execute(add_scan, data_scan)
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/usr/local/lib/python2.7/dist-packages/mysql/connector/cursor.py", line 551, in execute
    self._handle_result(self._connection.cmd_query(stmt))
  File "/usr/local/lib/python2.7/dist-packages/mysql/connector/connection.py", line 490, in cmd_query
    result = self._handle_result(self._send_cmd(ServerCmd.QUERY, query))
  File "/usr/local/lib/python2.7/dist-packages/mysql/connector/connection.py", line 395, in _handle_result
    raise errors.get_exception(packet)
mysql.connector.errors.DatabaseError: 1364 (HY000): Field 'scantime' doesn't have a default value

Then this line is commented out, then the .py script completes execution without any sign of error.

QUESTIONS

Upvotes: 0

Views: 909

Answers (1)

Robert I
Robert I

Reputation: 1527

One of your servers is running in strict mode by default and the other not. If a server runs in strict mode (or you set it in your connection) and you try to insert a NULL value into a column defined as NOT NULL you will get #1364 error. Without strict mode your NULL value will be replaced with empty string or 0.

Example:

CREATE TABLE `test_tbl` (
 `id` int(11) NOT NULL,
 `someint` int(11) NOT NULL,
 `sometext` varchar(255) NOT NULL,
 `somedate` datetime NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

SET sql_mode = '';
INSERT INTO test_tbl(id) VALUES(1);
SELECT * FROM test_tbl;
+----+---------+----------+---------------------+
| id | someint | sometext | somedate            |
+----+---------+----------+---------------------+
|  1 |       0 |          | 0000-00-00 00:00:00 |
+----+---------+----------+---------------------+
SET sql_mode = 'STRICT_ALL_TABLES';
INSERT INTO test_tbl(id) VALUES(2);
#1364 - Field 'someint' doesn't have a default value

Source : MySQL INSERT without having to specify every non-default field (#1067 - Invalid default value for 'table')

Fix: change that field to remove not null or set it to a default value such as CURRENT_TIMESTAMP

Upvotes: 3

Related Questions