Groundhog

MySQL stored procedures restructured. Each log format has a Stored Procedure and fixes problems with the Python/SQL separation release.

Simplifies code and easier to examine for non-MySQL coders. Releases are done as milestones of the timeline.

Non-stop on this project entire month of January.

I will be off the farm tomorrow for a few days for onsite meetings but will be checking Discussions.

The non-profit I volunteer at and the reason I built this does not have HUGE traffic. The old script version has run since March 2025 automated with no problem but it Data Volume does stress it.

Please give it a try. I really interested to see BIG DATA run through it. Cheers!

Proper Python & NGINX formats

This is a re-write of the Monolithic Python Script that got the database built. It has been feeding the database from several servers since March 2025 without error. This version is the same core code just restructured. This rewrite started slowly on January 1, 2026 and finishes with a very extended weekend. There are still refining to do but Python restructure had be released while it is all still fresh. Next few weeks minor releases will follow. Homestead projects are stacking up and extreme cold weather is coming this weekend. All SQL DDL code has also been released today as a new repository. Cheers!

MySQL & MariaDB compatible

Minimized, Cleaned, Formatted (spaces/no tabs) and removed schema name from all database object names in each of the 27 development scripts. MySQL/MariaDB specific code and version improvements. Processing is twice as fast in MariaDB on both Ubuntu and Windows. MySQL screams on MacOS with Apple silicon ARM64 processors. Did not install MariaDB on MacOS.

• [3.3.1] stripped schema name from all qualified database object names in apache_logs_schema.sql to minimize code and make transition to different schema name if required easier.
• [3.3.1] MariaDB and MySQL version-specific code implementation using - /M!100500 and /!50700 for index creation and adding system variables MySQL @@server_uuid and MariaDB @@server_uid.
• [3.3.1] increased column widths for LOAD TABLES - first_line_request, req_uri, req_query and decreased useragent to handle LimitRequestLine 8190 - https://httpd.apache.org/docs/2.2/mod/core.html#limitrequestline'
• [3.3.1] increased column width from 2000 to 5000 for TABLE access_log_reqquery and modified FUNCTION access_reqQueryID increased in_ReqQuery VARCHAR(2000) to VARCHAR(5000)

v3.3.0

This was missed during major revamp of two import processes. Does not effect processed data from last version. One of the main reasons for the revamp was to limit calls to importFileCheck to one per file. This should have been changed in version 3.0.0 when the process changed completely incorporating INNER JOIN with import_file TABLE. Version 3.2.9 called importFileCheck for each record in file instead of called once for each file.

• [3.3.0] modify of process_access_import changed l.importfileid to DISTINCT(l.importfileid) for cursors csv2mysqlStatusFile, csv2mysqlLoadIDFile, vhostStatusFile, vhostLoadIDFile, combinedStatusFile, combinedLoadIDFile
• [3.3.0] modify of process_error_import changed l.importfileid to DISTINCT(l.importfileid) for cursors defaultByLoadIDFile and defaultByStatusFile

v3.2.9

• [3.2.9] fix mistake in made in last version of process_access_import and process_error_import. importfileid was incorrectly changed causing records not to be related to files.
• [3.2.9] add views - access_client_city_list, access_client_country_code_list, access_client_country_list, access_client_subdivision_list, access_client_organization_list, access_client_network_list

v3.2.8

• [3.2.8] @@server_uuid and UUID() - these 2 are not the same - changed in version 3.2.0 on 02/01/2025 release - since then records are added to import_server TABLE as different servers each execution
• [3.2.8] add comments to importProcessID explain changes - scraped using server_uuid, UUID() and server_uid.
• [3.2.8] alter TABLE import_server rename COLUMN serveruuid to dbcomment.
• [3.2.8] modify process_access_parse, process_error_parse, process_access_import and process_error_import add file LOOP for CALL to importFileCheck. Less CALLS, cleaner code for changes made in version 3.0.
• [3.2.8] add FOR UPDATE clause to SELECTS for process_access_parse, process_error_parse, process_access_import and process_error_import to LOCK RECORDS.

v3.2.7

• [3.2.7] add except Exception as e: to all previous except: statements. e is printed with error message to console and logged to import_error TABLE.
• [3.2.7] fixed MacOS and Linux platforms issue with double seperators in paths stored in import_file TABLE. This was a result of fixing the double separator on Windows platform. Issue now fixed on all 3 platforms.
• [3.2.7] modify apache_logs_schema.sql generation script to comment out DROP statements and add comment to start of each merged file.
• [3.2.7] add two indexes for companion Web Interface - mysql-to-apache-echarts which is due to be released mid-March.

Raw & Refined schema script

The first I heard about this problem was by email at 4PM today. Clearly no one else has attempted to install on MariaDB or fixed the issue themselves. This is a much better approach since I now have a python script to consolidate the 25 source code scripts into a much smaller SQL file without any of the TABLE LOCKS and SET environment variables. The file is now just raw, bare bones script. Please email or create issue for any problems. I am working on the Web interface for this and will have initial release first week of March.

• [3.2.6] created new Python script to generate apache_logs_schema.sql from the 25 refined development source code scripts. Prior to this version file was generated by MySQL Workbench.
• [3.2.6] repository had MySQL Workbench generated script that added CHARSET and COLLATE. The raw source code scripts did not specify CHARSET or COLLATE. Raw source code scripts worked fine on MySQL and MariaDB testing.
• [3.2.6] MySQL 9.1 default is CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci. MariaDB 11.6 default is CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci. COLLATE=utf8mb4_0900_ai_ci does not exist in MariaDB causing script errors.
• [3.2.6] The solution that works for both MySQL and MariaDB is only specify CHARSET=utf8mb4. If CHARACTER SET charset_name is specified without COLLATE, character set charset_name and its default collation are used.

Stress Test Improvements

Log Generator created data with many values outside of "real-life" logs. All prior testing was done with "real" logs. Some values outside of "real-life" values causing unseen issues that required changing some table columns from TINYINT to INT. The data also caused error routines to be truly tested. Ran 5 million Combined Access records and 1 Million Error records through MySQL and MariaDB. Used environment variables ERROR_SERVER, ERROR_SERVERPORT, COMBINED_SERVER, COMBINED_SERVERPORT to assign domains. This should be the last of the database structure changes. I did think I was done last version but the Log Generator shook things up!

MariaDB 11.6 is consistently twice as fast as MySQL 9.1 in every benchmark on Windows 11. I have not run benchmarks on Ubuntu or MacOS.

• [3.2.5] modify process_access_import to correct remoteLogName and remoteUser column processing. Values were switched in tables stored. Running log generator stress tests flushed this out.
• [3.2.5] modify logs2mysql.py to add timing variables for all child processes to display in logs and store to import_load TABLE.
• [3.2.5] modify TABLE import_load added six columns for process execution durations in seconds.
• [3.2.5] modify logs2mysql.py reworked all process message logging verbiage to provide child process summary information at each phase. Running log generator stress tests flushed this out.
• [3.2.5] modify logs2mysql.py reduced number of cursor objects created by reusing only two cursor objects. Reduced all variables for import_file TABLE processing using same variables for all.
• [3.2.5] modify TABLES access_log_useragent and log_client added indexes for use in logs2mysql.py processing.

MariaDB compatible

1/30/2025 I attended a MariaDB webinar and was very impressed with technical knowledge and passion the four presenters conveyed about MariaDB. I spent that night installing database on MariaDB. Last night I ran 11,600 Apache Access and Error log files with 763,560 Access and 86,480 Error records thru both MySQL and MariaDB. MariaDB processes execute in about half the time as MySQL. I am liking MariaDB over MySQL. The Log Rotation functionality is very cool as well! Summary of changes below.

• [3.2.0] Database function and procedure modifications required for compatibility with MariaDB. Application processes have been tested with version 11.6. MariaDB tests twice as fast as MYSQL.
• [3.2.0] Major reworking of logs2mysql.py logging process messaging and incorporate Log File Rotation functionality with environment variables - BACKUP_DAYS and BACKUP_PATH
• [3.2.0] modify Store Function apache_logs.importFileExists for Log File Rotation functionality.
• [3.2.0] modify Store Function apache_logs.importProcessID for compatibility with MariaDB.
• [3.2.0] add Python function def copy_backup_file(log_path_file, log_days) to reuse log file copy and delete functionality,
• [3.2.0] add log summary to end of Python processLogs in logs2mysql.py to provide more process information to PM2 logs.