This article focuses on the database storage engines for MySQL that ensure appropriate performance and manage SQL operations for multiple table types. The article examines the differences between the most widely used MySQL storage engines. MySQL is the second most popular Relational Database Management Systems (RDBMS) in the world. Countless services and applications have MySQL […]
4 Older Entries »
Engine condition pushdown is a MySQL internal mechanism that is intended to avoid to send non matching rows from the storage engine to the SQL layer. This is very important when the storage engine involves traffic over the network. This mechanism was initially created to optimize MySQL Cluster (NDB) behavior. For the NDB storage engine [...]
Recently, someone emailed me:I have a requirement in MYSQL as
we have a table
EMP and we have to restrict the
users not delete employees with
DEPT_ID = 10. If
user executes a
DELETE statement without giving any
WHERE condition all the rows should be deleted
except those with
DEPT_ID = 10.
We are trying to write a
BEFORE DELETE trigger but
we are not able to get this functionality.
I have seen your blog where you explained about Using an UDF to Raise Errors from inside MySQL Procedures and/or Triggers. Will it helps me to get this functionality? Could you suggest if we have any other alternatives to do this as well?Frankly, I usually refer people that write me these things to a public forum, but this time I felt like …
Your searching for how to create a join across two databases on two different servers and it can’t be done directly. select d1.a, d2.b from db1@server1 join db2@server2 where db1.c = db2.c; does not work.
You learn about federated databases. The federated storage engine allows accesses data in tables of remote databases. Now how do you make it work?
1) Check if the federated storage engine is supported. Federation is OFF by default!
mysql> show engines; +------------+---------+----------------------------------------------------------------+ | Engine | Support | Comment | +------------+---------+----------------------------------------------------------------+ | InnoDB | YES | Supports transactions, row-level locking, and foreign keys | | MyISAM | DEFAULT | Default engine as of …[Read more]
The NoSQL/relational database debate has been going on for quite some time. MariaDB, like MySQL is relational. And if you read these series of blog posts, you’ll realise that if you use MySQL correctly, you can achieve quite a lot.
- It all starts with Kellan Elliott-McCrea with his introductory post on Using, Abusing and Scaling MySQL at Flickr. Follow the entire series.
- He starts of the series with Ticket Servers: Distributed Unique Primary Keys on …
(note: give the mirrors some hours to sync up)
- See the Release Notes for relevant bugfixes.
- Drop-in replacement for stock MySQL 5.0 or 5.1 (If you’re coming from stock MySQL, see the MariaDB extras/differences overview). …
I'm extremely happy to announce that the MariaDB project has
integrated FederatedX into the main tree and has replaced the
old, un-maintained Federated storage engine! This means that from
now on, I will target any FederatedX changes and enhancements for
MariaDB since it will be easier than having to try to make it run
as a pluggable storage engine. This also means I will have
another reason why I should keep this project moving forward. I
will still provide the ability for anyone who wants to use it as
a pluggable engine (not that difficult) so this can be loaded
with MySQL as well.
Also changed: I changed to using the BSD license for FederatedX.
For Drizzle, I will need to rewrite a good part of the code base to use libdrizzle as well as the new storage engine interface, so they will be somewhat separate projects -- though I'll do my best to keep them the same single project.
I want to thank Monty …
I have been meaning to, for months, get working on the FederatedX
storage engine. There are a lot of great features it has -- such
as better support for transactions as well as some architectural
changes, thanks to the work of Antony Curtis. It now is designed
so that the connection is abstracted into a federated_io_ class.
This allows you to be able to subclass different connection
schemes/drivers. For instance, I have the code for Federated ODBC
that I need to take the code from and port to a federated_io_odbc
FederatedX has been pulled into an older MariaDB repository (thanks Antony!) that I need to merge with the latest, as well as figure out how to get the test suite to load the plugable storage engine. I've tried to add 'INSTALL PLUGIN federated SONAME 'ha_federatedx.so'' to the test, but the server expects ha_federated.so to be in a particular file location. That needs to be solved. I looked at PBXT's test suite but …
If you’re using the Federated engine, here’s something important to remember (apart from the usual advice of “please don’t”). If you need to change the structure of the remote table, always remember to update the Federated table. If not, when you try to use the table, you’ll get this error:
mysql> SELECT * FROM foo; ERROR 1030 (HY000): Got error 1 from storage engine
This error isn’t really helpful. The problem is, the Federated engine only checks that the remote table structure is correct when it initially connects. Once it has connected, no more checks. When you restart the server, you get a much more helpful message:
mysql SELECT * FROM foo; ERROR 1431 (HY000): The foreign data source you are trying to reference does not exist. Data source error: error: 1054 'Unknown column 'b' in 'field list''
Also, keep your eye on the …[Read more]
4 Older Entries »