TheTicketDepartment.com is using a third party system that ties Ticket Brokers together from all over Canada and the U.S. This system was developed years ago using a MySQL 3.23 backend, and has never been upgraded to a newer version of MySQL because it was reliant on legacy functionality in MySQL 3.23. They needed to create a dynamic application using this application.
There are about 5500 headliners with about 101,500 events at approximately 9100 venues. The system wasn’t what they needed, but it would work. The challenges were the categorization hierarchy was wrong; there were false headliners, events, and venues in the system, and a great deal of copy to be written. The initial proposal was to create a 3.23 master to 5.1 slave replication scenario.
The 3.23 DB was untouched with the exception of setting up a slave user account, giving it an ID, and implementing Bin-logging. The Master database was used with their POS system. The Slave on the other had would be the Production DB for the web site so there were a lot of changes to make. This is a special case because both the master and slave database are production database for the same system, but each provide a different service. Taillieu did a MySQL dump from the slave for all of the tables from the master, and created his own table structure that would allow the database to function the way it needed to. He created triggers on the slave tables to update the new structure. These triggers used stored procedures to generate the page copy that would be used in the web pages. This was generated based on templates in the CopyTemplate table. These triggers were also used to automate most of the recategorization of the headliners. Basically if its in x category, update it to n category, then insert it into a new table. While all of this was happening it would check the id’s against the banned ids to insure that ‘bad’ headliners, venues, and events weren’t added to the system. The rest of the recategorization was managed by a .NET system that lets them manually go through headliners. It also keeps banned headliners, events, and venues from appearing in the system again.
This all worked fine in development but once he started fulfilling the orders using the POS there was a problem with replication because there were temporary tables being used. The create statement didn’t replicate but the drop table did. This caused the replication to fail. Taillieu’s solution was a quick one. He created a .NET Windows Service that would use mysqlbinlog.exe to read the commands from the bin-log and execute the statements, basically his own replication engine. This has worked great, and should be fine until MySQL releases Row Based Replication, or corrects the bug.
Taillieu says it’s the most interesting system he’s ever worked with. The dataflow is unique because of the third party system. From the web site it works like this:
Master data replicated to slave > Slave data manipulated for web system > web system serves data > Tickets are purchased through web system > Order is processed through third party remotely using .net web service > Order is sent to our master through third party system > Master data is replicated to slave > … and it continues.
This is a very interesting case study that can be applied to a variety of situations.
Marc is the Lead Software Developer for Roadtrips Inc. developing web based applications using .NET and MySQL.
View a complete list of MySQL contacts.