silent data loss for pads above 64KB on MySQL/MariaDB

[ccoenen]

Problem

Pads above 64KB get truncated silently (on MySQL / Mariadb)
The same is true for their revisions.

Desired Solution (Preferred)

The Field should deal with larger Texts. 64KB is not extremely much (though it is quite long). MEDIUMTEXT (16MB) or LONGTEXT (4GB) would give the user more headroom.

Desired Solution (Additionally)

The editor should warn in case of data loss, regardless of where the limit lies.

Problem source

content is Sequelize.TEXT.

This becomes

MariaDB [hackmd]> show fields from Notes;
+------------------+----------------------------------------------+------+-----+---------+-------+
| Field            | Type                                         | Null | Key | Default | Extra |
+------------------+----------------------------------------------+------+-----+---------+-------+
| id               | char(36)                                     | NO   | PRI |         |       |
| shortid          | varchar(255)                                 | NO   | UNI | NULL    |       |
| alias            | varchar(255)                                 | YES  | UNI | NULL    |       |
| permission       | enum('freely','editable','locked','private') | YES  |     | NULL    |       |
| viewcount        | int(11)                                      | NO   |     | 0       |       |
| title            | text                                         | YES  |     | NULL    |       |
| content          | text                                         | YES  |     | NULL    |       | <----
| authorship       | text                                         | YES  |     | NULL    |       |
| lastchangeAt     | datetime                                     | YES  |     | NULL    |       |
| savedAt          | datetime                                     | YES  |     | NULL    |       |
| createdAt        | datetime                                     | NO   |     | NULL    |       |
| updatedAt        | datetime                                     | NO   |     | NULL    |       |
| deletedAt        | datetime                                     | YES  |     | NULL    |       |
| ownerId          | char(36)                                     | YES  |     | NULL    |       |
| lastchangeuserId | char(36)                                     | YES  |     | NULL    |       |
+------------------+----------------------------------------------+------+-----+---------+-------+
15 rows in set (0.00 sec)

MySQL text (no surprise here).

TEXT types in combination with Storage Requirements leads me to believe that TEXT is 2^16 bytes large - or ~64KB.

[ccoenen]

Here's a long document. It appears to be 80K right now (four blocks of about 20K):
https://hackmd.io/GYYwTAJgzALApjAtANgEYENWJugnMxdABgHYkZkBWSGVGGakIA==

Here's the same thing on my (Mariadb) installation:
https://pad.claudiuscoenen.de/KwE2HYCMQFgDgLQCYDGBTAnAmAzHjpg0EBmEyYJcNcFJEuIA

• Try adding something at the bottom.
• It will appear (and sync).
• Close and reopen the tab
• Your text is now gone

The huge problem for me is: I have a large structured document that exceeds 64K. I was making larger amendments in the middle. Losing the end. Without noticing.

[ccoenen]

For Postgres, the upper limit appears to be 1GB for text

The storage requirement for a short string (up to 126 bytes) is 1 byte plus the actual string, which includes the space padding in the case of character. Longer strings have 4 bytes of overhead instead of 1. Long strings are compressed by the system automatically, so the physical requirement on disk might be less. Very long values are also stored in background tables so that they do not interfere with rapid access to shorter column values. In any case, the longest possible character string that can be stored is about 1 GB.

(emphasis mine)

[ccoenen]

I thought the Revisions table might save my day. Patches should be smaller, right? No, it turns out this is not the case. The lastContent, content and patch fields are all also of the type text (with the same limitations as above!)

patch is not helping me in any way, because the content appears to be fed to the diffing algorithm in a weird way, so that it's no longer line-based but one humongous line (\n becomes %0A), so my diffs are all:

@@ -1,4 +1,64818 @@
+all the content of my huge document, blown up by url_encode or similar even more
-whooops, never got around to saving this, because + was huge enough to max out `text` already

It's truncated even earlier.

[jackycute]

Hi @ccoenen
We didn't discover this issue because we mainly use PostgreSQL.
So I found something in below, we could indicate the text type to tiny, medium, long for other text types.
http://docs.sequelizejs.com/manual/tutorial/models-definition.html#data-types

[ccoenen]

I'm not mad, it's a bit of a rare case, I admit.

I don't believe the larger types have any downsides. The space used will only be the amount stored plus a few bytes for organiational data. In short: 1000 bytes of text will be 1000 bytes + X bytes regardless if it's sitting in TEXT, MEDIUMTEXT or LONGTEXT.

I wonder if Sequelize shouldn't have reported some kind of error? Somewhere along the way data gets truncated. I can't believe that there's no way of raising an exception.