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.

IP Geolocation integration

• [3.0.0] This version is NOT backward compatible to previous versions due to many database and process changes. These are final major changes required for Web interface in development.
• [3.0.0] Integration with MaxMind GeoIP2 Python API to enhance Client IP geolocation data for Log Data Visualization in charts, reports & data analysis interfaces.
• [3.0.0] modify logs2mysql.py to integrate IP data retrieval process and reorganizing encapsulation of all processes within the same "Import Load Process".
• [3.0.0] add TABLES log_client_city, log_client_coordinate, log_client_country, log_client_network, log_client_organization and log_client_subdivision for IP geolocation data.
• [3.0.0] add normalize_client STORED PROCEDURE to normalize IP Address geolocation data into 6 tables.
• [3.0.0] rename TABLES log_clientname to log_client, log_servername to log_server
• [3.0.0] rename COLUMNS clientnameid to clientid, servernameid to serverid throughout application tables and processes.
• [3.0.0] modify process_access_parse and process_error_parse WHERE CLAUSES for server_name UPDATE commands.
• [3.0.0] add 16 stored functions for primary attribute tables to return names for Slice and dice is a data analysis in drill-down Web interface.
• [3.0.0] modify and reworded all console log messages in logs2mysql.py to standardize messages for each process. Added COLORS to coordinate message types for better readability.
• [3.0.0] modify all database INDEX NAMES for standardization and consolidation.
• [3.0.0] tested simultaneously uploading logs from 10 VPS with multiple VirtualHosts on each Server processing thousands of files in different formats and millions of log records.

v2.1.6

• [2.1.6] repository name change - ApacheLogs2MySQL to apache-logs-to-mysql
• [2.1.6] rename files - apachelogs2MySQL.py to logs2mysql.py, apachelogs2MySQL.sql to apache_logs_schema.sql
• [2.1.6] modify logs2mysql.py line if useragent_process == 1: to if useragent_process >= 1:
• [2.1.6] modify all files with refers to repository name. Changed ApacheLogs2MySQL to apache-logs-to-mysql
• [2.1.6] "application name" is still referred to as ApacheLogs2MySQL in README.md, CITATION.md, logs2mysql.py, watch4logs.py, apache_logs_schema.sql, INSTALL.md and settings.env

v2.1.5

• [2.1.5] move platformNode COLUMN from import_client TABLE to import_device TABLE. This attribute should not change once set. import_client are attributes which may change.
• [2.1.5] modify U_import_file_name INDEX on import_file TABLE from UNIQUE (name) to UNIQUE (importdeviceid, name). This was missed in last release and main reason for release.

v2.1.4

• [2.1.4] add import_device TABLE to separate import_client TABLE columns to resolve software login, IP address and software version change information creating new record.
• [2.1.4] add importdeviceid COLUMN to import_file TABLE to resolve Client Modules with identical FILE NAMES and PATHS. Application detected file already imported.
• [2.1.4] modify import_client and import_device COLUMN widths and created UNIQUE INDEX for combined columns for each table.
• [2.1.4] modify importfileexists and importfileID FUNCTIONS with new parameter for importdeviceid.

v2.1.3

• [2.1.3] modify access_log and error_log TABLES reordering COLUMNS to improve database design readability.
• [2.1.3] modify normalize_useragent to removed first parameter restriction. Any string 8 characters are more can be passed.
• [2.1.3] modify apacheLogs2MySQL.py add completed COLUMN to UPDATE statement to fix processing process_access_import and process_error_import with ALL parameter.
• [2.1.3] modify call_processes.sql adding more comments to better describe options and parameters and overall processing.

v2.1.2

• [2.1.2] modify TABLE access_log ADD CONSTRAINT F_access_requestlogid FOREIGN KEY (requestlogid) REFERENCES log_requestlogid(id)
• [2.1.2] modify TABLE error_log ADD index CONSTRAINT F_error_requestlogid FOREIGN KEY (requestlogid) REFERENCES log_requestlogid(id)
• [2.1.2] add TABLE import_format for reporting and filtering features. Allows adjusted File Format names in tables rather than hardcoding. Also enables addtional formats,
• [2.1.2] add COLUMN importformatid to TABLE import_file for reporting and filtering features. This is FOREIGN KEY to import_format TABLE.
• [2.1.2] modify process_access_parse and process_error_parse - WHERE CLAUSE for parameter ALL to select ONLY completed LOAD processes.
• [2.1.2] modify importFileID STORED FUNCTION to add fileformat PARAMETER.
• [2.1.2] modify apacheLog2MySQL.py to pass importFileID parameters 2=common and combined OR 5=error_default and error_vhost. Does not determine format difference here.
• [2.1.2] modify process_access_parse and process_error_parse - to SET 'Import File Format - 1=common,2=combined,3=vhost,4=csv2mysql,5=error_default,6=error_vhost'
• [2.1.2] modify process_access_parse and process_error_parse - WHERE CLAUSE for parameter ALL to select ONLY completed LOAD processes.
• [2.1.2] reformatted SQL statements in all 66 schema views for code standardization in SQL files used to create apacheLogs2MySQL.sql
• [2.1.2] modify all 11 access_ua_ views SQL statements FROM apache_logs.access_log_ua ln INNER JOIN apache_logs.access_log_useragent lua INNER JOIN apache_logs.access_log
• [2.1.2] created new entity_relationship_diagram.png to reflect database changes.

2.1.1

• [2.1.1] rename COLUMN timeStamp to logged in TABLES access_log and error_log.
• [2.1.1] add access_log INDEXES I_access_log_logged and I_access_log_servernameid_logged.
• [2.1.1] add error_log INDEXES I_error_log_logged and I_error_log_servernameid_logged.
• [2.1.1] modify process_access_import and process_error_imort for COLUMN rename timeStamp to logged in TABLES access_log and error_log.
• [2.1.1] add access_log views access_period_year_list, access_period_month_list, access_period_week_list, access_period_day_list, access_period_hour_list.
• [2.1.1] add error_log views error_period_year_list, error_period_month_list, error_period_week_list, error_period_day_list, error_period_hour_list.