
Writing a MySQL storage engine from scratch (2016) - yinso
https://www.codeproject.com/articles/1107279/writing-a-mysql-storage-engine-from-scratch
======
willvarfar
Somewhat related, I recently started trying to reverse-engineer the MySQL wire
protocol. My idea is that if this is better documented then it will be easier
for people to make databases that interoperate with MySQL clients but aren't
MySQL that much easier (personally I want a custom Shard-Query-ish thing).

This is obstinately documented ([http://dev.mysql.com/doc/dev/mysql-
server/latest/PAGE_PROTOC...](http://dev.mysql.com/doc/dev/mysql-
server/latest/PAGE_PROTOCOL.html)), but I can't find a nice cheat-sheet saying
"to send a query, you send this packet and you receive these packets" etc.
I've taken to looking at e.g. the pure-Python PyMySQL package source-code
[https://github.com/PyMySQL/PyMySQL/blob/master/pymysql/conne...](https://github.com/PyMySQL/PyMySQL/blob/master/pymysql/connections.py#L226)

Here's my progress so far:
[https://gist.github.com/williame/ebf003dd85d5949f551a72331ae...](https://gist.github.com/williame/ebf003dd85d5949f551a72331aefec11)

Anyone know what commands you can send, and what the expected responses are?

~~~
ccommsxx
Take a closer look at the MySQL documentation. You dont have to reverse
engineer anything - it's all there and documented (source: wrote a client impl
based on the docs)

[http://imysql.com/mysql-internal-manual/client-server-
protoc...](http://imysql.com/mysql-internal-manual/client-server-
protocol.html)

[http://imysql.com/mysql-internal-manual/connection-
phase.htm...](http://imysql.com/mysql-internal-manual/connection-phase.html)

[http://imysql.com/mysql-internal-manual/com-
query.html](http://imysql.com/mysql-internal-manual/com-query.html)

etc

~~~
morgo
This is not the MySQL manual (dev.mysql.com) but a third party copy that is at
least a few years old.

We're working on moving the internals documentation into the server code with
Doxygen: [http://mysqlserverteam.com/mysql-8-0-now-documented-with-
dox...](http://mysqlserverteam.com/mysql-8-0-now-documented-with-doxygen/)

~~~
ccommsxx
I can see what parents issue was now :) I had a look at the doxygen and all
the goold old protocol documentation seems to be deleted/missing from the
"official" documentation website. How come -- I found it really useful?

\- [https://dev.mysql.com/doc/dev/mysql-
server/8.0.0/page_protoc...](https://dev.mysql.com/doc/dev/mysql-
server/8.0.0/page_protocol_connection_phase.html)

\- [https://dev.mysql.com/doc/dev/mysql-
server/8.0.0/page_protoc...](https://dev.mysql.com/doc/dev/mysql-
server/8.0.0/page_protocol_command_phase.html)

~~~
ccommsxx
Actually I remembered now that the last time I worked with the MySQL protocol
(~2014) I had a similar problem: Some of our servers were still running an old
version of MySQL so the client had to be backwards compatible. However, this
old version of MySQL also used the "old mysql password" authentication which
was found to be unsafe/broken at some point. So the MySQL guys deleted the
documentation on how the old scheme worked and replaced it with a notice
saying that it is insecure. Tough luck if you still had to support/implement
it. The same thing has apparently happened here. Not only is the documentation
gone, it's actually gone without a trace on the official website.

------
michaelmior
Am I the only one who likes the "C with classes" approach over C++?

~~~
pmelendez
Depends on what you mean by C++. C++11, C++14, C++17 have features that are
must have to me.

~~~
paulddraper
Well thank goodness we live in 2017.

------
mappu
Great article, makes it very clear what parts of the database are- and are
not- provided by the storage engine. I wrote a C wrapper for BoltDB recently,
i wonder how MySQL would perform linked to that?

I have some memories of looking at VB6 code on codeproject.com over a decade
ago, interesting to see that the site is still around and useful.

------
tyingq
Sqlite has a fairly straightforward way to implement your own storage engine
as well, see the VFS documentation:
[https://sqlite.org/vfs.html](https://sqlite.org/vfs.html)

Virtual tables are also interesting, but higher level, somewhat like a fuse
filesystem. [https://sqlite.org/vtab.html](https://sqlite.org/vtab.html)

------
nullnilvoid
Interesting article. Not many people work on low-level storage engines
nowadays.

------
anton_gogolev
Oh, and it becomes painfully obvious why MySQL cannot use more than one index
while executing the query.

~~~
hoschicz
Can PostgreSQL use more than one index per table while executing a query?

~~~
anton_gogolev
Absolutely: [https://www.postgresql.org/docs/8.3/static/indexes-bitmap-
sc...](https://www.postgresql.org/docs/8.3/static/indexes-bitmap-scans.html)

