mysql-replication: ValueError: year 488143155 is out of range

Alex Gavtadze

unread,

Aug 29, 2018, 11:23:23 PM8/29/18

to Python MySQL replication

Hi, trying to use mysql-replication to read from AWS RDS Mysql binlog but getting following errors. Cannot figure out what is the problem. Can someone please take a look and let me know if there is a quick solution?

here is the full output. I did extra prints so i could see what is going on. I think either decoder is not reading it right value or something going on. Unix time it is producing is in future and it cannot covert it to human readable format. even tried adjust pymysql/protocol.py thinking it is decoders issue but no progress. Many, many thanks in advance!!!

[ec2-user@ip-XXXXXXX ppn]$ python3 new.py

=== RotateEvent ===

Position: 4

Next binlog file: mysql-bin-changelog.038318

=== FormatDescriptionEvent ===

Date: 2018-08-30T03:00:00

Log position: 248

Event size: 225

Read bytes: 0

=== QueryEvent ===

Date: 2018-08-30T03:00:00

Log position: 287

Event size: 20

Read bytes: 20

Schema: b''

Execution time: 0

Query: # Dumm

=== QueryEvent ===

Date: 2018-08-30T03:00:00

Log position: 336

Event size: 30

Read bytes: 30

Schema: b''

Execution time: 0

Query: # Dummy event re

=== QueryEvent ===

Date: 1970-01-01T00:00:00

Log position: 385

Event size: 30

Read bytes: 30

Schema: b''

Execution time: 0

Query: # Dummy event re

=== QueryEvent ===

Date: 2018-08-30T03:00:22

Log position: 423

Event size: 19

Read bytes: 19

Schema: b''

Execution time: 0

Query: BEGIN

=== TableMapEvent ===

Date: 2018-08-30T03:00:22

Log position: 495

Event size: 53

Read bytes: 51

Table id: 830

Schema: conductor

Table: messages

Columns: 14

=== DeleteRowsEvent ===

Date: 2018-08-30T03:00:22

Log position: 2676

Event size: 2162

Read bytes: 11

Table: conductor.messages

Affected columns: 14

COLUMN NAME => {'type': 12, 'name': 'created_at', 'collation_name': None, 'character_set_name': None, 'comment': '', 'unsigned': False, 'type_is_bool': False, 'is_primary': False}

read_datetime:raw input => 4881431553869414657

ALEX ########################

read_datetime(date) => 4881431553869.415

read year: => 488143155

year => 488143155

month => 38

day => 69

Traceback (most recent call last):

File "new.py", line 33, in <module>

main()

File "new.py", line 27, in main

binlogevent.dump()

File "/home/ec2-user/.local/lib/python3.7/site-packages/pymysqlreplication/event.py", line 43, in dump

self._dump()

File "/home/ec2-user/.local/lib/python3.7/site-packages/pymysqlreplication/row_event.py", line 471, in _dump

super(DeleteRowsEvent, self)._dump()

File "/home/ec2-user/.local/lib/python3.7/site-packages/pymysqlreplication/row_event.py", line 433, in _dump

print("Changed rows: %d" % (len(self.rows)))

File "/home/ec2-user/.local/lib/python3.7/site-packages/pymysqlreplication/row_event.py", line 447, in rows

self._fetch_rows()

File "/home/ec2-user/.local/lib/python3.7/site-packages/pymysqlreplication/row_event.py", line 442, in _fetch_rows

self.__rows.append(self._fetch_one_row())

File "/home/ec2-user/.local/lib/python3.7/site-packages/pymysqlreplication/row_event.py", line 467, in _fetch_one_row

row["values"] = self._read_column_data(self.columns_present_bitmap)

File "/home/ec2-user/.local/lib/python3.7/site-packages/pymysqlreplication/row_event.py", line 139, in _read_column_data

values[name] = self.__read_datetime()

File "/home/ec2-user/.local/lib/python3.7/site-packages/pymysqlreplication/row_event.py", line 337, in __read_datetime

second=int(time % 100))

ValueError: year 488143155 is out of range

[ec2-user@ip-xx-xx-xx-xx ppn]$

[ec2-user@ip-xx-xxxx-xx ppn]$ pip list

Package Version

----------------- ---------

asn1crypto 0.24.0

awsebcli 3.14.4

blessed 1.15.0

boto3 1.8.3

botocore 1.11.3

cached-property 1.4.3

cement 2.8.2

certifi 2018.8.24

cffi 1.11.5

chardet 3.0.4

colorama 0.3.9

cryptography 2.3.1

docker 3.5.0

docker-compose 1.21.2

docker-pycreds 0.3.0

dockerpty 0.4.1

docopt 0.6.2

docutils 0.14

idna 2.6

jmespath 0.9.3

jsonschema 2.6.0

mysql-replication 0.18

pathspec 0.5.5

pip 18.0

pycparser 2.18

PyMySQL 0.9.2

python-dateutil 2.7.3

PyYAML 3.13

requests 2.18.4

s3transfer 0.1.13

semantic-version 2.5.0

setuptools 38.4.0

six 1.11.0

tabulate 0.7.5

termcolor 1.1.0

texttable 0.9.1

urllib3 1.23

wcwidth 0.1.7

websocket-client 0.51.0

wheel 0.31.1

[ec2-user@ip-xx-xx-xx-xx ppn]$

Alex Gavtadze

unread,

Aug 30, 2018, 12:41:55 PM8/30/18

to Python MySQL replication

I actually passed this issue but now running into another :( I'm learning python as I go deeper into this project but current issue i am having is, when forwarding to kinesis... i am getting this error when I get a change logged:

TypeError: datetime.datetime(2015, 8, 28, 13, 47, 19) is not JSON serializable

anyone can comment or give direction on that issue?

Chengzhi Zhao

unread,

Aug 30, 2018, 1:38:45 PM8/30/18

to Alex Gavtadze, Python MySQL replication

I guess this is more son question,

Try to create a function like

def datetime_handler(self, x):

if isinstance(x, datetime) or isinstance(x, date):

return x.isoformat()

logging.error(str(x))

raise TypeError("Unknown type”)

Then you can do json.dumps(anObject, default=datetime_handler)

Regards,

Chengzhi

--
You received this message because you are subscribed to the Google Groups "Python MySQL replication" group.
To unsubscribe from this group and stop receiving emails from it, send an email to python-mysql-repli...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Alex Gavtadze

unread,

Aug 30, 2018, 2:25:57 PM8/30/18

to Python MySQL replication

thank you very much. I found this workaround:

https://stackoverflow.com/questions/11875770/how-to-overcome-datetime-datetime-not-json-serializable

many thanks again for the response!

To unsubscribe from this group and stop receiving emails from it, send an email to python-mysql-replication+unsub...@googlegroups.com.

Reply all

Reply to author

Forward