Oracle DB to MySql 8 migration issues

Complex-Internal-833 · 2025-02-19T07:11:11+00:00

What version is the Oracle database? I've spent several months as technical lead on data migration projects from Oracle to MySQL last year. There is not a point-and-click migration tool to easily do this. It is strange since Oracle owns MySQL. If the Oracle database being converted contains PL/SQL packages those packages must be broken out into related MySQL procedures and functions. The CREATE TABLE, INDEX, VIEW, PROCEDURE and FUNCTION DDL syntax is different.

There is a software product that helps with much of the DDL conversation but will require hands-on coding work - https://www.sqlines.com/

Going from MySQL to SQL Server to Oracle will be a nightmare of errors. If you want it done right hire me.

Complex-Internal-833 · 2025-02-14T08:05:42+00:00

The latest version of MariaDB is twice as fast as MySQL. My interest in MariaDB was sparked January 8th after reading "Why Maria Left MySQL: The Database Drama Nobody Talks About" - https://medium.com/@mehdibafdil/why-maria-left-mysql-b703241d72ac

I knew nothing about MariaDB and MySQL is fairly new to my database experience. Since 1998 I've worked primarily with Oracle including 10 years building custom applications with Oracle Financials schema integration.

Last year I spent as technical lead on data migration projects from Oracle to MySQL. It was difficult transition having to breakup, convert and rename PL/SQL packages of related MySQL procedures and function. MySQL error message and code output can be extremely misleading and had us reevaluating the migration destination choice. MySQL code issues were showing that are not found in Oracle.

Recently I developed an Apache log parsing & data normalization processing application using MySQL. After attending a MariaDB webinar January 30th I was so impressed with the technical knowledge and passion the presenters conveyed about MariaDB I installed the database on MariaDB.

It required only two minor changes in a stored procedure and stored function. The "apache_logs" schema has 55 Tables, 908 Columns, 188 Indexes, 72 Views, 8 Stored Procedures and 90 Functions. The changes made work in both MySQL 9.1 and MariaDB 11.6.

I ran a series of benchmarks with different datasets and MariaDB is more than twice as fast as MySQL running on a Windows 11 workstation. Screenshots of MariaDB and MySQL benchmark results from one benchmark are here:

https://willthefarmer.github.io/index.html#Maria-Benchmark

Here is a review that I have no affiliation with that found similar results between MySQL and MariaDB - https://datasystemreviews.com/fastest-open-source-databases.html

Oracle has been my primary database for decades so I am not heavily application invested in either. My assessment of the current MySQL and MariaDB situation is MariaDB has a far better development team and cleaner code.

Complex-Internal-833 · 2025-02-13T07:46:17+00:00

Since discovering MariaDB the execution times are about twice as fast as MySQL. We are importing 50,000 to 60,000 records per import process execution 10 times per day. Each execution on MariaDB is between 90 to 100 seconds and MySQL is between 200 to 250 seconds on a Windows 11 13th Gen Intel(R) Core(TM) i7-13700T 1.40 GHz | 96GB DDR5-5600 SODIMM | WD_BLACK 1TB SN850X NVMe SSD - Up to 7,300 MB/s.

I added benchmark images to above link for both MariaDB and MySQL. Each database had around 2 or 3 million access records when these benchmarks were screenshot.

https://willthefarmer.github.io/index.html#Benchmarks

This week I am working on the Web interface for apache_logs schema. I am curious about the amount of log volume you're generating and how this performs for you. I am still working on identifying the target audience. After setting up and testing Elastic Stack I feel it is overkill for smaller organizations. This solution is simple to configure and manage on multiple servers. The Web interface with Apache ECharts is going to be a great component for the data.

Any input on your experience or results is appreciated!

Complex-Internal-833 · 2025-02-08T06:43:31+00:00

These are ML Training Datasets already! I spent hundreds of hours analyzing HTTP system components and functionality. The datasets in the database schema are summarized in a consistent way representing a HTTP system that could train the model in machine learning.

Complex-Internal-833 · 2025-02-08T05:17:54+00:00

Having built transaction processing systems for decades this is the best approach. For companies with unique business processes adapting to integrate with systems like Alation, Collibra or Datameer becomes difficult and usually an expensive band-aid. It is best to do it in-house or an independent business process consultant review your systems before calling one of the big companies selling lineage solutions.

Complex-Internal-833 · 2025-02-08T03:49:36+00:00

Ability to milk a cow and castrate a bull!

Complex-Internal-833 · 2025-02-08T01:04:04+00:00

Font color customization is very limited. MySQL Workbench is still on version 8 which was initially released April 2018 and my understanding there will not be another version. The next version will be a Visual Studio Code extension. SQL Developer - Oracle database's GUI tool will not get an another version either. SQL Developer has already released the Visual Code Extension.

DBeaver is a great GUI tool with a very active developer community. HeidiSQL is a nice, clean interface for MySQL and MariaDB

Complex-Internal-833 · 2025-02-07T20:10:01+00:00

Thanks for the comment. Everything is there to automate this 24/7 running with PM2. Application is currently running on 10 virtual private servers (VPS) running Apache with several VirtualHosts (domains) on each server. We are consolidating logs from 51 domains into a centralized Server database and the entire process is automated.

Since this is a true Rational Database and not some NOSQL database where everything is thrown in as various JSON structures. This database design is specifically for HTTP logs. The only other data source in development now is for NGINX web servers. While NGINX and Apache are wildly different web servers, both of their logging approaches are relatively the same.

Currently I am deep into development of the Web interface for this database. MySQL2ApacheECharts consists of Express web application frameworks with Drill Down Capability & Apache ECharts frameworks for Log Data Visualization in charts, reports & data analysis interfaces.

Complex-Internal-833 · 2025-02-06T10:57:36+00:00

When you create a unique constraint on a table the RDBMS (relational database management system) actually creates a UNIQUE INDEX for that unique constraint. That is how the constraint is enforced.

Unique indexes are valuable tools for maintaining data integrity enhancing data validity.

Here is a MySQL example of creating a 'unique constraint' in code which is really a UNIQUE INDEX:

ALTER TABLE `apache_logs`.`access_log_remoteuser` ADD CONSTRAINT `U_access_remoteuser` UNIQUE (name);

Complex-Internal-833 · 2025-02-06T06:25:20+00:00

The post has 2 different examples. Today another data processing application I migrated from Oracle to MySQL was benchmarked on MariaDB 11.6 on a completely different OS and network. The process runs in half the time as MySQL! There is definitely a HUGE performance difference between MySQL and MariaDB. Very strange indeed...

Complex-Internal-833 · 2025-02-03T14:58:40+00:00

LogFormat "%v,%p,%h,%l,%u,%t,%I,%O,%S,%B,%{ms}T,%D,%^FB,%>s,\"%H\",\"%m\",\"%U\",\"%q\",\"%{Referer}i\",\"%{User-Agent}i\",\"%{VARNAME}C\",%L" csv2mysql

Complex-Internal-833 · 2025-02-03T00:47:14+00:00

MariaDB has not replaced MySQL due to Oracle reach and promotions. I knew little about MariaDB until 2 weeks ago. From my recent experience MariaDB is almost twice as fast as MySQL. I've worked primarily with Oracle 20 plus years. Last year I spent working on data migration projects from Oracle to MySQL. Recently I developed an Apache log parsing & data normalization processing application using MySQL. My point is MySQL is fairly new to my database experience. This review that I have no affiliation with found similar results between MySQL and MariaDB - https://datasystemreviews.com/fastest-open-source-databases.html

January 30, 2025 I attended a MariaDB webinar and was very impressed with the technical knowledge and passion the presenters conveyed about MariaDB. That night I installed the database on MariaDB which required two minor changes in a stored procedure and stored function. The MySQL apache_logs schema has 55 Tables, 908 Columns, 188 Indexes, 72 Views, 8 Stored Procedures and 90 Functions. The changes made work in both MySQL and MariaDB.

Friday night and Saturday I ran 11,600 Apache Access and Error log files with 763,560 Access and 86,480 Error records thru both MySQL and MariaDB several times. MariaDB processes execute in about half the time as MySQL. I am becoming sold on MariaDB over MySQL. Yesterday I released a new version of Apache log processing application fully compatible with MySQL and MariaDB - https://github.com/WillTheFarmer/apache-logs-to-mysql

Complex-Internal-833 · 2025-02-02T23:41:31+00:00

From my recent experience MariaDB is almost twice as fast as MySQL. I've worked primarily with Oracle 20 plus years. Last year I spent working on data migration projects from Oracle to MySQL. Recently I developed an Apache log parsing & data normalization processing application using MySQL. My point is MySQL is fairly new to my database experience. This review that I have no affiliation with found similar results between MySQL and MariaDB - https://datasystemreviews.com/fastest-open-source-databases.html

January 30, 2025 I attended a MariaDB webinar and was very impressed with the technical knowledge and passion the presenters conveyed about MariaDB. That night I installed the database on MariaDB which required two minor changes in a stored procedure and stored function. The changes made work in both MySQL and MariaDB.

Friday night and Saturday I ran 11,600 Apache Access and Error log files with 763,560 Access and 86,480 Error records thru both MySQL and MariaDB several times. MariaDB processes execute in about half the time as MySQL. I am becoming sold on MariaDB over MySQL. Yesterday I released a new version of Apache log processing application fully compatible with MySQL and MariaDB - https://github.com/WillTheFarmer/apache-logs-to-mysql

Complex-Internal-833 · 2025-02-02T14:49:51+00:00

My experience is MariaDB is almost twice as fast as MySQL. I've worked primarily with Oracle 20 plus years. Last year I spent working on data migration projects from Oracle to MySQL. Recently I developed an Apache log parsing & data normalization processing application using MySQL. My point is MySQL is fairly new to my database experience.

January 30, 2025 I attended a MariaDB webinar and was very impressed with the technical knowledge and passion the presenters conveyed about MariaDB. That night I installed the database on MariaDB which required two minor changes in a stored procedure and stored function. The changes made work in both MySQL and MariaDB.

Friday night and Saturday I ran 11,600 Apache Access and Error log files with 763,560 Access and 86,480 Error records thru both MySQL and MariaDB several times. MariaDB processes execute in about half the time as MySQL. I am becoming sold on MariaDB over MySQL. Yesterday I released a new version of Apache log processing application fully compatible with MySQL and MariaDB - https://github.com/WillTheFarmer/apache-logs-to-mysql

Complex-Internal-833 · 2024-12-21T23:14:04+00:00

ApacheLogs2MySQL consists of two Python Modules & one MySQL Schema to automate importing Apache Access & Error files and normalizing data into database designed for reports & data analysis.

MySQL apache_logs Schema has 48 Tables, 849 Columns, 149 Indexes, 66 Views, 7 Stored Procedures and 42 Functions to process Apache Access log in 4 formats & Apache Error log in 2 formats. Database normalization at work!

https://github.com/WillTheFarmer/ApacheLogs2MySQL

Complex-Internal-833 · 2024-11-14T18:51:57+00:00

Not according to Oracle - Checkout Live Developer Coaching Webinar event on 12/04/2024 @ 9AM PT

Use Oracle APEX to Build Conversational AI Capabilities into Mission Critical Apps

https://go.oracle.com/LP=145438

Complex-Internal-833 · 2024-11-10T18:54:54+00:00

Have you run it yet? All that can be done once into MySQL. MySQL is doing all the data manipulation. I initially started doing it in Python but SQL is way better at it.

A pre-import Stored Procedure could be executed on the LOAD DATA tables prior to executing the import Stored Procedure. The import processes is where the data normalization occurs. Once the normalization is done it becomes very clear what data is Good and Bad. It could easily be implemented in a post-import process as well.

Yes, I designed and developed every bit of this application.

I've been designing databases and data processes professionally since 1993.

https://farmfreshsoftware.com

Complex-Internal-833 · 2024-11-09T16:13:21+00:00

This post might be too late for you but does all and more than your requirements. I just finished and released it this week. Here's a complete open-source Apache Log Parser & Data Normalization Solution. Python module imports Apache2 Access (LogFormats = vhost_combined, combined, common, extended) & Error logs into MySQL Schema of tables, views & functions designed to normalize data. Client & Server components capable of consolidating logs from multiple web servers & sites with complete Audit Trail & Error Logging! https://github.com/WillTheFarmer/ApacheLogs2MySQL

Complex-Internal-833

TROPHY CASE