When executing some SQL queries on the user table in my ETL I get an error:
(venv) C:\Users\antoi\Documents\Programming\Work\data-tools>python -m etl.main
2021-06-29 15:34:53.515286 - Connecting to database hozana_data...
2021-06-29 15:34:53.523283 - Connecting to archive database hozana_archive...
2021-06-29 15:34:53.755949 - Start ETL main process
2021-06-29 15:34:53.755949 - `users` table:
2021-06-29 15:34:53.755949 - Hashing column `users`.`email:`table_name: users
c.execute("SELECT 15+10 FROM users"): 0
done.
2021-06-29 15:34:53.763899 - Hashing column `users`.`email_notification:`table_name: users
Traceback (most recent call last):
File "C:\Users\antoi\Documents\Programming\Work\data-tools\etl\task\anonymization.py", line 18, in hash_column
print("c.execute(\"""SELECT 15+10 FROM users\"""): ", c.execute("""SELECT 15+10 FROM users"""))
File "C:\Users\antoi\Documents\Programming\Work\data-tools\venv\lib\site-packages\MySQLdb\cursors.py", line 183, in execute
while self.nextset():
File "C:\Users\antoi\Documents\Programming\Work\data-tools\venv\lib\site-packages\MySQLdb\cursors.py", line 137, in nextset
nr = db.next_result()
MySQLdb._exceptions.OperationalError: (2006, '')
During handling of the above exception, another exception occurred:
Traceback (most recent call last):
File "C:\Users\antoi\AppData\Local\Programs\Python\Python39\lib\runpy.py", line 197, in _run_module_as_main
return _run_code(code, main_globals, None,
File "C:\Users\antoi\AppData\Local\Programs\Python\Python39\lib\runpy.py", line 87, in _run_code
exec(code, run_globals)
File "C:\Users\antoi\Documents\Programming\Work\data-tools\etl\main.py", line 52, in <module>
main()
File "C:\Users\antoi\Documents\Programming\Work\data-tools\etl\main.py", line 24, in main
anonymization.main()
File "C:\Users\antoi\Documents\Programming\Work\data-tools\etl\task\anonymization.py", line 61, in main
hash_column('users', 'email_notification', 'user_id', True)
File "C:\Users\antoi\Documents\Programming\Work\data-tools\etl\task\anonymization.py", line 52, in hash_column
print('.', end='', flush=True)
File "C:\Users\antoi\Documents\Programming\Work\data-tools\venv\lib\site-packages\MySQLdb\connections.py", line 239, in __exit__
self.close()
MySQLdb._exceptions.OperationalError: (2006, '')
I don't get it. This error seems to be because of a closed connection or too much data ... But I only have the schema, the database is empty. And even when I do a simple math operation I get this error:
Here is part of the ETL:
from etl.mysql.operations import drop_column_if_exists
from etl.utils.logging import info
from etl.mysql.connect import db, db_name
from etl.utils.array import chunks
def hash_column(table_name, column_name, pk_name, email_mode=False):
# Executed a query that will overwrite a column with an hashed version of its content.
# With email mode, the domain name will be kept:
# aba@stoacj.com -> 379ac32fe8f576c4c63b17cd576e6c40c7dcd[...]b04c7a4695a7baa54ad5ce44528a0b30ab@stoacj.com
info('Hashing column `{table_name}`.`{column_name}:`'
.format(table_name=table_name, column_name=column_name), end='', flush=True)
with db as c:
# Execute the update in batches, to avoid "Lock wait timeout exceeded"
c = c.cursor()
print("table_name: ", table_name)
print("c.execute(\"""SELECT 15+10 FROM users\"""): ", c.execute("""SELECT 15+10 FROM users"""))
c.execute("""
SELECT {pk_name} as row_id
FROM {table_name}
WHERE {column_name} IS NOT NULL AND {column_name} NOT LIKE 'hash_%'
ORDER BY row_id ASC
""".format(table_name=table_name, column_name=column_name, pk_name=pk_name))
ids = []
...
Here is the code in from etl.mysql.connect import db:
import os
import MySQLdb
from etl.utils.logging import info
db_host = os.environ['DATA_DB_HOST']
db_port = int(os.environ['DATA_DB_PORT'])
db_user = os.environ['DATA_DB_USER']
db_password = os.environ['DATA_DB_PASSWORD']
db_name = os.environ['DATA_DB_NAME']
db_name_archive = os.environ['DATA_DB_ARCHIVE_NAME']
info("Connecting to database {}...".format(db_name))
db = MySQLdb.connect(host=db_host,
port=db_port,
db=db_name,
user=db_user,
passwd=db_password)
The problem isn't because of the connection, I have it and it's okay.
I tried to increment max_allowed_packet:
mysql> select @@max_allowed_packet;
+----------------------+
| @@max_allowed_packet |
+----------------------+
| 4194304 |
+----------------------+
1 row in set (0.00 sec)
mysql> set global max_allowed_packet=10485760;
Query OK, 0 rows affected (0.01 sec)
But it is still the same space:
mysql> select @@max_allowed_packet;
+----------------------+
| @@max_allowed_packet |
+----------------------+
| 4194304 |
+----------------------+
1 row in set (0.00 sec)
mysql> show global variables like 'max_allowed_packet';
+--------------------+---------+
| Variable_name | Value |
+--------------------+---------+
| max_allowed_packet | 4194304 |
+--------------------+---------+
1 row in set (0.01 sec)
Logs
Here are today's log
2021-07-06T09:23:01.193756Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-07-06T09:23:01.195707Z 0 [Warning] 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.
2021-07-06T09:23:01.198594Z 0 [Note] C:\Program Files\MySQL\MySQL Server 5.7\bin\mysqld.exe (mysqld 5.7.34-log) starting as process 18804 ...
2021-07-06T09:23:01.223999Z 0 [Note] InnoDB: Mutexes and rw_locks use Windows interlocked functions
2021-07-06T09:23:01.224707Z 0 [Note] InnoDB: Uses event mutexes
2021-07-06T09:23:01.225130Z 0 [Note] InnoDB: _mm_lfence() and _mm_sfence() are used for memory barrier
2021-07-06T09:23:01.225747Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2021-07-06T09:23:01.226251Z 0 [Note] InnoDB: Adjusting innodb_buffer_pool_instances from 8 to 1 since innodb_buffer_pool_size is less than 1024 MiB
2021-07-06T09:23:01.230045Z 0 [Note] InnoDB: Number of pools: 1
2021-07-06T09:23:01.232177Z 0 [Note] InnoDB: Not using CPU crc32 instructions
2021-07-06T09:23:01.237646Z 0 [Note] InnoDB: Initializing buffer pool, total size = 8M, instances = 1, chunk size = 8M
2021-07-06T09:23:01.239234Z 0 [Note] InnoDB: Completed initialization of buffer pool
2021-07-06T09:23:01.284231Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
2021-07-06T09:23:01.476328Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2021-07-06T09:23:01.477358Z 0 [Note] InnoDB: Setting file '.\ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2021-07-06T09:23:01.512811Z 0 [Note] InnoDB: File '.\ibtmp1' size is now 12 MB.
2021-07-06T09:23:01.515027Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
2021-07-06T09:23:01.516117Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
2021-07-06T09:23:01.517118Z 0 [Note] InnoDB: Waiting for purge to start
2021-07-06T09:23:01.578331Z 0 [Note] InnoDB: 5.7.34 started; log sequence number 3981806
2021-07-06T09:23:01.579551Z 0 [Note] InnoDB: Loading buffer pool(s) from C:\ProgramData\MySQL\MySQL Server 5.7\Data\ib_buffer_pool
2021-07-06T09:23:01.581274Z 0 [Note] Plugin 'FEDERATED' is disabled.
2021-07-06T09:23:01.599250Z 0 [Note] InnoDB: Buffer pool(s) load completed at 210706 11:23:01
2021-07-06T09:23:01.616013Z 0 [Note] Found ca.pem, server-cert.pem and server-key.pem in data directory. Trying to enable SSL support using them.
2021-07-06T09:23:01.616947Z 0 [Note] Skipping generation of SSL certificates as certificate files are present in data directory.
2021-07-06T09:23:01.621001Z 0 [Warning] CA certificate ca.pem is self signed.
2021-07-06T09:23:01.621875Z 0 [Note] Skipping generation of RSA key pair as key files are present in data directory.
2021-07-06T09:23:01.623118Z 0 [Note] Server hostname (bind-address): '*'; port: 3306
2021-07-06T09:23:01.623813Z 0 [Note] IPv6 is available.
2021-07-06T09:23:01.624196Z 0 [Note] - '::' resolves to '::';
2021-07-06T09:23:01.624656Z 0 [Note] Server socket created on IP: '::'.
2021-07-06T09:23:01.675585Z 0 [Note] Failed to start slave threads for channel ''
2021-07-06T09:23:01.737678Z 0 [Note] Event Scheduler: Loaded 0 events
2021-07-06T09:23:01.738400Z 0 [Note] C:\Program Files\MySQL\MySQL Server 5.7\bin\mysqld.exe: ready for connections.
Version: '5.7.34-log' socket: '' port: 3306 MySQL Community Server (GPL)
2021-07-06T09:24:16.925420Z 3 [Note] Access denied for user 'hozana'@'localhost' (using password: YES)
Advise
my experience with mysqlclient is a real disaster:
With Fedora:
- With mysqlclient==1.3.7 (which we are using on Debian):
mysql_configmissing, even when reinstalling connectors such asmariadb-connector-c-develorcommunity-mysql-devel - With 2.0.3: MySQL-python is missing, or not recognized
With Windows:
- With
mysqlclient==1.3.7, I can't install it: tells me to use Visual C++ but nothing changes. So I've tried a pre-built version from Christoph Gohlke's site but it's not compatible. - With >2.* I have [this question]:
OperationalError: (2006, '')in a large part of SQL queries like the following one:
c.execute("""
UPDATE `{table_name}`
SET `{column_name}` = CONCAT('hash_', {expression})
WHERE {pk_name} IN ({ids})
""".format(
table_name=table_name, column_name=column_name, expression=expression, pk_name=pk_name,
ids=','.join(ids)
))
Start of a solution
I don't have this issue anymore when upgrading the code to enable it to use mysqlclient 2.0.3 and containerizing MySQL:
(venv) [ac@localmachine data-tools]$ cat docker-compose.yml
version: '3.1'
services:
db:
image: mysql:5.7
command: --default-authentication-plugin=mysql_native_password
restart: always
ports:
- "3307:3306"
volumes:
- .data/mysql:/var/lib/mysql
environment:
MYSQL_ROOT_PASSWORD: root
volumes:
schema_database_metabase.sql:
I know, it looks crazy it works. I think I need to add a few more details.