Codimd: Database Issue with setup on MariaDB 10.0.27 (ubuntu default package)

[ccoenen]

I followed the setup instructions in the readme, and it appears I can't get the database into a working state. I am trying to set up from tag 0.4.5, not master. I'm using mariadb (mysql) on ubuntu 16.04.

If I just start node app.js, my database is not fully populated with tables. If I run node_modules/.bin/sequelize db:migrate three tables get created: Authors, Revisions and SequelizeMeta. I'm missing at least a Notes table, at least a long string of error messages in my terminal seems to suggest this.

After node app.js I'm greeted by this error message:

$ node app.js
Unhandled rejection SequelizeDatabaseError: ER_TOO_LONG_KEY: Specified key was too long; max key length is 767 bytes
    at Query.formatError (/var/www/hackmd/node_modules/sequelize/lib/dialects/mysql/query.js:175:14)
    at Query._callback (/var/www/hackmd/node_modules/sequelize/lib/dialects/mysql/query.js:49:21)
    at Query.Sequence.end (/var/www/hackmd/node_modules/mysql/lib/protocol/sequences/Sequence.js:85:24)
    at Query.ErrorPacket (/var/www/hackmd/node_modules/mysql/lib/protocol/sequences/Query.js:94:8)
    at Protocol._parsePacket (/var/www/hackmd/node_modules/mysql/lib/protocol/Protocol.js:280:23)
    at Parser.write (/var/www/hackmd/node_modules/mysql/lib/protocol/Parser.js:74:12)
    at Protocol.write (/var/www/hackmd/node_modules/mysql/lib/protocol/Protocol.js:39:16)
    at Socket.<anonymous> (/var/www/hackmd/node_modules/mysql/lib/Connection.js:109:28)
    at emitOne (events.js:77:13)
    at Socket.emit (events.js:169:7)
    at readableAddChunk (_stream_readable.js:146:16)
    at Socket.Readable.push (_stream_readable.js:110:10)
    at TCP.onread (net.js:523:20)

I'm pretty sure my db credentials are OK, both ways of starting the server (with and without db:migrate) do create at least one table, so access must be alright.

Running sequelize on an empty database yields these errors:

$ node_modules/.bin/sequelize db:migrate

Sequelize [Node: 4.2.6, CLI: 2.4.0, ORM: 3.24.3]

Parsed url mysql://hackmd:*****@localhost:3306/hackmd
== 20160515114000-user-add-tokens: migrating =======
Unhandled rejection SequelizeDatabaseError: ER_NO_SUCH_TABLE: Table 'hackmd.Users' doesn't exist
    at Query.formatError (/var/www/hackmd/node_modules/sequelize/lib/dialects/mysql/query.js:175:14)
    at Query._callback (/var/www/hackmd/node_modules/sequelize/lib/dialects/mysql/query.js:49:21)
    at Query.Sequence.end (/var/www/hackmd/node_modules/mysql/lib/protocol/sequences/Sequence.js:85:24)
    at Query.ErrorPacket (/var/www/hackmd/node_modules/mysql/lib/protocol/sequences/Query.js:94:8)
    at Protocol._parsePacket (/var/www/hackmd/node_modules/mysql/lib/protocol/Protocol.js:280:23)
    at Parser.write (/var/www/hackmd/node_modules/mysql/lib/protocol/Parser.js:74:12)
    at Protocol.write (/var/www/hackmd/node_modules/mysql/lib/protocol/Protocol.js:39:16)
    at Socket.<anonymous> (/var/www/hackmd/node_modules/mysql/lib/Connection.js:109:28)
    at emitOne (events.js:77:13)
    at Socket.emit (events.js:169:7)
    at readableAddChunk (_stream_readable.js:146:16)
    at Socket.Readable.push (_stream_readable.js:110:10)
    at TCP.onread (net.js:523:20)
Unhandled rejection SequelizeDatabaseError: ER_NO_SUCH_TABLE: Table 'hackmd.Users' doesn't exist
    at Query.formatError (/var/www/hackmd/node_modules/sequelize/lib/dialects/mysql/query.js:175:14)
    at Query._callback (/var/www/hackmd/node_modules/sequelize/lib/dialects/mysql/query.js:49:21)
    at Query.Sequence.end (/var/www/hackmd/node_modules/mysql/lib/protocol/sequences/Sequence.js:85:24)
    at Query.ErrorPacket (/var/www/hackmd/node_modules/mysql/lib/protocol/sequences/Query.js:94:8)
    at Protocol._parsePacket (/var/www/hackmd/node_modules/mysql/lib/protocol/Protocol.js:280:23)
    at Parser.write (/var/www/hackmd/node_modules/mysql/lib/protocol/Parser.js:74:12)
    at Protocol.write (/var/www/hackmd/node_modules/mysql/lib/protocol/Protocol.js:39:16)
    at Socket.<anonymous> (/var/www/hackmd/node_modules/mysql/lib/Connection.js:109:28)
    at emitOne (events.js:77:13)
    at Socket.emit (events.js:169:7)
    at readableAddChunk (_stream_readable.js:146:16)
    at Socket.Readable.push (_stream_readable.js:110:10)
    at TCP.onread (net.js:523:20)
== 20160515114000-user-add-tokens: migrated (0.114s)
== 20160607060246-support-revision: migrating =======
Unhandled rejection SequelizeDatabaseError: ER_NO_SUCH_TABLE: Table 'hackmd.Notes' doesn't exist
    at Query.formatError (/var/www/hackmd/node_modules/sequelize/lib/dialects/mysql/query.js:175:14)
    at Query._callback (/var/www/hackmd/node_modules/sequelize/lib/dialects/mysql/query.js:49:21)
    at Query.Sequence.end (/var/www/hackmd/node_modules/mysql/lib/protocol/sequences/Sequence.js:85:24)
    at Query.ErrorPacket (/var/www/hackmd/node_modules/mysql/lib/protocol/sequences/Query.js:94:8)
    at Protocol._parsePacket (/var/www/hackmd/node_modules/mysql/lib/protocol/Protocol.js:280:23)
    at Parser.write (/var/www/hackmd/node_modules/mysql/lib/protocol/Parser.js:74:12)
    at Protocol.write (/var/www/hackmd/node_modules/mysql/lib/protocol/Protocol.js:39:16)
    at Socket.<anonymous> (/var/www/hackmd/node_modules/mysql/lib/Connection.js:109:28)
    at emitOne (events.js:77:13)
    at Socket.emit (events.js:169:7)
    at readableAddChunk (_stream_readable.js:146:16)
    at Socket.Readable.push (_stream_readable.js:110:10)
    at TCP.onread (net.js:523:20)
== 20160607060246-support-revision: migrated (0.253s)
== 20160703062241-support-authorship: migrating =======
Unhandled rejection SequelizeDatabaseError: ER_NO_SUCH_TABLE: Table 'hackmd.Notes' doesn't exist
    at Query.formatError (/var/www/hackmd/node_modules/sequelize/lib/dialects/mysql/query.js:175:14)
    at Query._callback (/var/www/hackmd/node_modules/sequelize/lib/dialects/mysql/query.js:49:21)
    at Query.Sequence.end (/var/www/hackmd/node_modules/mysql/lib/protocol/sequences/Sequence.js:85:24)
    at Query.ErrorPacket (/var/www/hackmd/node_modules/mysql/lib/protocol/sequences/Query.js:94:8)
    at Protocol._parsePacket (/var/www/hackmd/node_modules/mysql/lib/protocol/Protocol.js:280:23)
    at Parser.write (/var/www/hackmd/node_modules/mysql/lib/protocol/Parser.js:74:12)
    at Protocol.write (/var/www/hackmd/node_modules/mysql/lib/protocol/Protocol.js:39:16)
    at Socket.<anonymous> (/var/www/hackmd/node_modules/mysql/lib/Connection.js:109:28)
    at emitOne (events.js:77:13)
    at Socket.emit (events.js:169:7)
    at readableAddChunk (_stream_readable.js:146:16)
    at Socket.Readable.push (_stream_readable.js:110:10)
    at TCP.onread (net.js:523:20)
== 20160703062241-support-authorship: migrated (0.096s)
== 20161009040430-support-delete-note: migrating =======
Unhandled rejection SequelizeDatabaseError: ER_NO_SUCH_TABLE: Table 'hackmd.Notes' doesn't exist
    at Query.formatError (/var/www/hackmd/node_modules/sequelize/lib/dialects/mysql/query.js:175:14)
    at Query._callback (/var/www/hackmd/node_modules/sequelize/lib/dialects/mysql/query.js:49:21)
    at Query.Sequence.end (/var/www/hackmd/node_modules/mysql/lib/protocol/sequences/Sequence.js:85:24)
    at Query.ErrorPacket (/var/www/hackmd/node_modules/mysql/lib/protocol/sequences/Query.js:94:8)
    at Protocol._parsePacket (/var/www/hackmd/node_modules/mysql/lib/protocol/Protocol.js:280:23)
    at Parser.write (/var/www/hackmd/node_modules/mysql/lib/protocol/Parser.js:74:12)
    at Protocol.write (/var/www/hackmd/node_modules/mysql/lib/protocol/Protocol.js:39:16)
    at Socket.<anonymous> (/var/www/hackmd/node_modules/mysql/lib/Connection.js:109:28)
    at emitOne (events.js:77:13)
    at Socket.emit (events.js:169:7)
    at readableAddChunk (_stream_readable.js:146:16)
    at Socket.Readable.push (_stream_readable.js:110:10)
    at TCP.onread (net.js:523:20)
== 20161009040430-support-delete-note: migrated (0.050s)

tables directly after the command:

MariaDB [(none)]> show tables from hackmd;
+------------------+
| Tables_in_hackmd |
+------------------+
| Authors          |
| Revisions        |
| SequelizeMeta    |
+------------------+
3 rows in set (0.00 sec)

(database was empty before)

[jackycute]

Hi @ccoenen !
Could you please tell me your mariadb version? Seems like we met some InnoDB limitation.
And you're missing both Notes and Users tables. I think that's because Sequelize break on the first failure.
Also please paste your db config without real password, thanks!

[ccoenen]

$ cat config.json
{
    "development": {
        "domain": "localhost",
        "db": {
            "username": "hackmd",
            "password": "---redacted---",
            "database": "hackmd",
            "host": "localhost",
            "port": "3306",
            "dialect": "mysql"
        }
    },
    "production": {
        "domain": "localhost",
        "db": {
            "username": "hackmd",
            "password": "---redacted---",
            "database": "hackmd",
            "host": "localhost",
            "port": "3306",
            "dialect": "mysql"
        }
    }
}

$ cat .sequelizerc
var path = require('path');

module.exports = {
    'config':          path.resolve('config.json'),
    'migrations-path': path.resolve('lib', 'migrations'),
    'models-path':     path.resolve('lib', 'models'),
    'url':             'mysql://hackmd:---redacted---@localhost:3306/hackmd'
}

$ env | grep NODE
NODE_ENV=production

$ mysql -uhackmd -p
Enter password: ---redacted---
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2221
Server version: 10.0.27-MariaDB-0ubuntu0.16.04.1 Ubuntu 16.04 <--

10.0.27 is my Mariadb-Version.

I did not configure the hostname, yet, but that was intentional.

[jackycute]

Thanks, very detail, I will check it up.

[ccoenen]

If I can be of any assistance I'd gladly lend a hand :)

[jackycute]

According to this issue https://github.com/sequelize/sequelize/issues/4171
Is your DB charset is utf8mb4?

[ccoenen]

MariaDB [hackmd]> select * from INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = 'hackmd';
+--------------+-------------+----------------------------+------------------------+----------+
| CATALOG_NAME | SCHEMA_NAME | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME | SQL_PATH |
+--------------+-------------+----------------------------+------------------------+----------+
| def          | hackmd      | utf8mb4                    | utf8mb4_general_ci     | NULL     |
+--------------+-------------+----------------------------+------------------------+----------+
1 row in set (0.00 sec)

Yes, it is.

[jackycute]

How about change your db charset to uft8?
Since we encoded all text content so emoji will be fine.

Still curious about what SQL query cause that error.

[jackycute]

Huh another solution is run this before create tables

SET @@global.innodb_large_prefix = 1;

http://mechanics.flite.com/blog/2014/07/29/using-innodb-large-prefix-to-avoid-error-1071/

[jackycute]

I figure it out, because we're making multiple column as constrains here:
https://github.com/hackmdio/hackmd/blob/master/lib/models/author.js#L23
And both of them are UUID, so the index will be more than 767 bytes.

[ccoenen]

for an efficient index, it would probably be best to limit the number of character anyway. Not sure if sequelize supports this. I'll try one of the workarounds in the meantime.

[jackycute]

We use the UUID as the datatype of the index which should be optimized by DB.
But in "Authors" table, because the userId and noteId should be unique at same time, so I made them as indexes.
There is another approach, don't make them as indexes, instead check the data is unique before insertion via scripts in which will cause some loops.

I'm not sure about the performance issue, not a DB pro.

[ccoenen]

regarding performance: as long as it's not broken, don't fix it :) If nobody ever complained, it's probably quite alright the way it is.

To

alter database hackmd CHARACTER SET = 'utf8' COLLATE = 'utf8_general_ci';

and now after starting node app.js I get a very promising "listening on port 3000" response :)

So it seems to have helped for now. Not entirely what this will break. For me (european languages) probably nothing or not much.

[ccoenen]

Depending on how you would like to go forward, I'm fine with closing this ticket, as my immediate problem has been resolved. You can, of course, also use this to track any further changes you'd like to make, if any.

[jackycute]

I'm using Chinese which is totally good here 😄
If you do like use utf8mb4 charset, maybe try to SET @@global.innodb_large_prefix = 1; next time.
Anyway, thanks for bringing me up this issue, I will try add charset warning in README.md.

[jackycute]

Add in 432106b7c62e82be1d8837c63a00ded9b4da5768

Thanks to @ccoenen