[SBDS] Fixes for Out-of-Range Errors

Introduction

SBDS (Steem Blockchain Data Service) is a set of tool for querying the data of the Steem Blockchain. It's a great tool that allows running your own blockchain MySQL database for faster queries for your applications.

I followed Hive account@good-karma's guide How to save Steem blockchain to MySQL?, with some personal modifications to the docker setup.

Issues

During the synchronization, two error types occur in several tables:
Out of range value for column ... at row 1
Data too long for column ... at row 1

For example:

{"levelname": "INFO", "asctime": "2017-11-13T08:50:04.1510563004GMT", "filename": "init.py", "module": "init", "funcName": "adaptive_insert", "lineno": 168, "msecs": 786.0825061798096, "message": "safe_merge_insert failed (raised as a result of Query-invoked autoflush; consider using a session.no_autoflush block if this flush is occurring prematurely) (_mysql_exceptions.DataError) (1406, "Data too long for column 'data' at row 1") [SQL: 'INSERT INTO sbds_tx_customs (block_num, transaction_num, operation_num, timestamp, tid, required_auths, data, operation_type) VALUES (%s, %s, %s, %s, %s, %s, %s, %s)'] [parameters: (17181523, 4, 1, datetime.datetime(2017, 11, 13, 8, 49), 1, ['reggaemuffin'], '0069006e0020006d006f006c006500730074006900650020006d00610067006e0061002000730065006d007000650072002000750074002e0045007400690061006d002000740069006e0 ... (123058 characters truncated) ... 90069006900690069006900690069006900690069006900690069006900690069006900690069006900690069006900690069006900690069006900690069006900690069006900690069', 'custom')]", "name": "sbds.storages.db", "pathname": "/usr/local/lib/python3.5/dist-packages/sbds/storages/db/init.py", "process": 19, "processName": "MainProcess", "threadName": "MainThread"}

Such errors are caused by values too large for the field size. They can be monitored with the dockerized setup using:
docker logs sbds -f | grep DataError

Solution

One workaround is to stop the synchronization and modify the tables in MySQL. This can take several minutes if the table is already populated and is big.

For example:

ALTER TABLE sbds_tx_customs CHANGE COLUMN data data MEDIUMTEXT NULL DEFAULT NULL AFTER required_auths;

or

ALTER TABLE sbds_core_blocks CHANGE COLUMN raw raw MEDIUMTEXT NULL FIRST;

However, that doesn't fix the truncated values that were already added to the database. The best solution is to increase the field length in the python scripts that populate the database. Then, wipe the database and restart the synch (rebuild the docker container if using docker). A full resynch requires ~8GB of memory and takes around 24h on a multi-core system. After the resynch, the memory usage is very low.

One of the fixes was suggested by john-g-g in https://github.com/steemit/sbds/issues/63.
I implemented that solution and extended the size of the other problematic fields.

Links

SBDS Issue #63
SBDS Issue #79
SBDS Pull Request #81

---

Posted on Utopian.io - Rewarding Open Source Contributors

---