{"id":7663,"date":"2017-01-30T11:14:33","date_gmt":"2017-01-30T09:14:33","guid":{"rendered":"http:\/\/code.openark.org\/blog\/?p=7663"},"modified":"2017-01-30T11:16:18","modified_gmt":"2017-01-30T09:16:18","slug":"some-observations-on-mysql-to-sqlite-migration-compatibility","status":"publish","type":"post","link":"https:\/\/code.openark.org\/blog\/mysql\/some-observations-on-mysql-to-sqlite-migration-compatibility","title":{"rendered":"Some observations on MySQL to sqlite migration & compatibility"},"content":{"rendered":"

I’m experimenting with sqlite<\/a> as backend database for orchestrator<\/a>. While orchestrator<\/code>\u00a0manages MySQL replication topologies, it also uses MySQL as backend. For some deployments, and I’m looking into such one, having MySQL as backend is a considerable overhead.<\/p>\n

This sent me to the route of looking into a self contained orchestrator<\/code>\u00a0binary + backend DB. I would have orchestrator<\/code>\u00a0spawn up its own\u00a0backend database instead of connecting to an external one.<\/p>\n

Why even relational?<\/h3>\n

Can’t orchestrator<\/code>\u00a0just use a key-value backend?<\/p>\n

Maybe it could. But frankly I enjoy the power of relational databases, and the versatility they offer has proven itself multiple times with orchestrator<\/code>, being able to answer interesting, new, complex questions about one’s topology by crafting SQL queries.<\/p>\n

Moreover, orchestrator<\/code>\u00a0is already heavily invested in the relational model. At this time, replacing all SQL queries with key-value reads seems to me as a significant investment in time and risk. So I was looking for a relational, SQL accessible embeddable database for orchestrator<\/code>.<\/p>\n

Why sqlite?<\/h3>\n

I\u00a0am in particular looking at two options: sqlite (via the go-sqlite3<\/a> binding) and TiDB<\/a>. sqlite does not need much introduction, and I’ll just say it’s embeddable within the golang-built binary.<\/p>\n

TiDB is a pure-Go, MySQL dialect compatible server which provides relational model on top of key-value store. The store could be local or distributed, and the TiDB project cleverly separates involved layers.<\/p>\n

Of the two, sqlite is mature, alas uses a different SQL dialect and has different behavior. TiDB’s compatibility with MySQL is an impressive feat, but still ongoing.<\/p>\n

Both require adaptations of SQL code. Here are some observations on adaptations required when moving an existing app from MySQL backend to sqlite backend.<\/p>\n

Differences<\/h3>\n

Just to answer an obvious question:\u00a0can’t everything be abstracted away by an ORM that speaks both dialects?<\/p>\n

I don’t think so. I always exploit SQL beyond the standard insert<\/code>\/delete<\/code>\/update<\/code>\/select<\/code>, exploits that ORMs just don’t support.<\/p>\n

Here’s an incomplete list of differences I found. Some purely syntactical, some semantical, some behavioral, and some operational.<\/p>\n

    \n
  • Data types: no CHARACTER SET<\/code>\u00a0clause<\/li>\n
  • Data types: you can’t associate UNSIGNED<\/code>\u00a0to any int type<\/li>\n
  • Data types: no enum<\/code>. However there’s an alternative in the form of:
    \nrace text check (race in ('human', 'dog', 'alien'))<\/code><\/li>\n
  • auto_increment<\/code>\u00a0is called integer<\/code><\/li>\n
  • Data types: timestamps are not a thing. There’s no timezone info.<\/li>\n
  • TIMESTAMP<\/code>\u00a0has no ON UPDATE<\/code>\u00a0clause.<\/li>\n
  • No after<\/code>\u00a0clause for adding columns<\/li>\n
  • Indexes are not part of table creation. Only PRIMARY KEY<\/code>\u00a0is. The rest of indexes are created via CREATE INDEX<\/code>\u00a0statement<\/li>\n
  • Indexes have unique names across the schema. This is unfortunate, since it forces me to use longer names for indexes so as to differentiate them. For example,\u00a0in MySQL I can have an index named name_idx<\/code>\u00a0in two different tables; in sqlite<\/code>\u00a0I append table name for “namespacing”<\/li>\n
  • Temporal values and functions: poor support for time arithmetic.\n
      \n
    • Getting the diff between two datetimes is non-trivial\u00a0(what’s the diff in months for a leap year?)
      \n<\/code><\/li>\n
    • INTERVAL<\/code>\u00a0keyword not respected. Appending\/subtracting dates can be done via:
      \ndatetime('now', '-3 hour')<\/code>
      \nCan you see the problem in the above? What is the number 3<\/code>\u00a0is a positional argument? In MySQL I would use NOW() - INTERVAL ? HOUR<\/code>. To make positional arguments work in sqlite<\/code>, the above gets to be datetime('now', printf('-%d hour', ?))<\/code>. How would you even translate NOW() - INTERVAL (? * 2) SECOND<\/code>?<\/li>\n
    • UNIX_TIMESTAMP<\/code>\u00a0not respected. Instead using strftime('%s', 'now')<\/code>, I dislike the use of string functions to generate times.<\/li>\n<\/ul>\n<\/li>\n
    • insert ignore<\/code>\u00a0turns to insert or ignore<\/code><\/li>\n
    • replace into<\/code>\u00a0remains replace into<\/code>. Yay!<\/li>\n
    • insert on duplicate key update<\/code>\u00a0has no equivalent. It’s worthwhile noting a replace into<\/code>\u00a0does not replace<\/em> (pun intended) an insert ... on duplicate key<\/code>\u00a0as the latter can choose to only update a subset of column in the event of a constraint violation. It’s really very powerful.<\/li>\n
    • IS TRUE<\/code>\u00a0and IS FALSE<\/code>\u00a0are not respected.<\/li>\n
    • ALTER TABLE<\/code>:\n
        \n
      • When adding a not null<\/code> column one must specify the default value (e.g. 0<\/code>\u00a0for an int<\/code>)<\/li>\n
      • You\u00a0cannot add a timestamp column that defaults to CURRENT_TIMESTAMP<\/code>. You can have such column in your CREATE TABLE<\/code>\u00a0definition, but you cannot add such a column. The reason being that CURRENT_TIMESTAMP<\/code>\u00a0is not a constant value. When adding a column to a table, sqlite<\/code>\u00a0does not actually apply the change to all existing rows, but only to newly created ones. It therefore does not know what value to provide to those older rows.<\/li>\n
      • You cannot DROP COLUMN<\/code>. I’ll say it again. You cannot DROP COLUMN<\/code><\/li>\n
      • You cannot modify a PRIMARY KEY<\/code><\/li>\n
      • You cannot rename a column or change its datatype.<\/li>\n
      • In fact, the only supported ALTER TABLE<\/code>\u00a0statements are\u00a0ADD COLUMN\u00a0<\/code>and RENAME<\/code>\u00a0(renaming the table itself)<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n

        Regarding the ALTER TABLE<\/code>\u00a0limitations, the advice for dropping\/changing\/renaming columns or changing the primary key is to “create a new table with the new format, copy the rows, drop the old table, rename”. This is certainly feasible, but requires a substantial overhead from the user. And it’s non atomic. It requires a change in the state of mind but also a change in state of operations, the latter being non-trivial when moving from one DBMS to another, or when wishing to support both.<\/p>\n

        I’m still looking into this, and trying to work my way around differences with cheap regular expressions for as much as possible. I’m\u00a0mainly interested right now in finding all semantic\/logic differences that would require application changes. So far the TIMESTAMP<\/code>\u00a0behavior is such, and so is the INSERT ... ON DUPLICATE KEY<\/code>\u00a0statement.<\/p>\n","protected":false},"excerpt":{"rendered":"

        I’m experimenting with sqlite as backend database for orchestrator. While orchestrator\u00a0manages MySQL replication topologies, it also uses MySQL as backend. For some deployments, and I’m looking into such one, having MySQL as backend is a considerable overhead. This sent me to the route of looking into a self contained orchestrator\u00a0binary + backend DB. I would […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":true,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"enabled":false},"version":2}},"categories":[5],"tags":[21,126],"class_list":["post-7663","post","type-post","status-publish","format-standard","hentry","category-mysql","tag-sql","tag-sqlite"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2bZZp-1ZB","_links":{"self":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/7663","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/comments?post=7663"}],"version-history":[{"count":6,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/7663\/revisions"}],"predecessor-version":[{"id":7669,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/7663\/revisions\/7669"}],"wp:attachment":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/media?parent=7663"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/categories?post=7663"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/tags?post=7663"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}