I think it must be a MySQL version issue – I was using v8.0.19 of the CE, and
the googling I mentioned earlier indicated that the key length may have been a
warning rather than an error in earlier editions.
From: Darryl L. Pierce
Sent: Friday, 10 April 2020 2:22 AM
To: comixed-dev@xxxxxxxxxxxxx
Subject: [comixed-dev] Re: MySQL database.
We should add it to the root of the repository, called it FAQ.md so we
can use markdown in it.
We can use any version of any JDBC driver, TBH. The user would just
have to add them to a directory and include it in the classpath when
launching the app. Which inspired me to create this issue to enhance
the scripts [1]. The version of mysql-connector-java that gets bundled
with the JAR file is v8.0.13 currently, and H2 is v1.4.97. The MySQL
instance I'm running is in a Docker container is MySQL 5.
[1] https://github.com/comixed/comixed/issues/237
On Wed, Apr 8, 2020 at 4:51 PM bareheiny <dmarc-noreply@xxxxxxxxxxxxx> wrote:
Where’s the best place to put / update a FAQ?
So far we need to mention MySQL and the H2 driver as far as I’m aware.
Also, what version of MySQL are you running? It looks like (based on one bug
from 2015) that the index length may have changed from a warning to an error.
That same bug report indicates only the first 3072 bytes are used for the
index...which could cause issues if the last few bytes are where the
difference is in a file path and name.
On 8/04/2020, at 23:48, Darryl L. Pierce <mcpierce@xxxxxxxxx> wrote:
Okay, though it's an issue we should probably add to a FAQ in case
others run across it.
On Mon, Apr 6, 2020 at 7:36 PM bareheiny <dmarc-noreply@xxxxxxxxxxxxx>
wrote:
It seems to be a MySQL limit - rather than something in CX.
The generated sql is the same as you’ve got - if I change the length Of
the file name to around 780 it’s fine, 1024 is a no go.
I’ve put this aside now - I don’t think I want a server service running
unnecessarily, so will either stick to H2 or look at another local dB.
On 7/04/2020, at 01:04, Darryl L. Pierce <mcpierce@xxxxxxxxx> wrote:
Nope, I use defaults for things like that.
The comics table requires that the filename column be unique, and
limits the column to 1024 bytes. The only things that are potentially
longer than that are the summary, description and notes columns, which
are all longtext columns and can be up to to 4G in length. But those
columns aren't used in any keys.
And looking through the code I can't find anyplace where this limit of
3072 bytes is define. Can you post the generated SQL here please so I
can look it over? Here's what was generated on my MBP:
CREATE TABLE comixed.comics (id BIGINT NOT NULL, filename
VARCHAR(1024) NOT NULL, archive_type VARCHAR(4) NOT NULL, added_date
datetime NOT NULL, last_updated_date datetime NOT NULL, scan_type_id
BIGINT NULL, format_id BIGINT NULL, imprint VARCHAR(64) NULL,
sort_name VARCHAR(128) NULL, comic_vine_id VARCHAR(16) NULL, publisher
VARCHAR(128) NULL, series VARCHAR(128) NULL, volume VARCHAR(4) NULL,
issue_number VARCHAR(16) NULL, cover_date date NULL, title
VARCHAR(256) NULL, summary LONGTEXT NULL, `description` LONGTEXT NULL,
notes LONGTEXT NULL, CONSTRAINT PK_COMICS PRIMARY KEY (id), UNIQUE
(id), UNIQUE (filename))
On Sun, Apr 5, 2020 at 4:57 PM Guy Incognito
<dmarc-noreply@xxxxxxxxxxxxx> wrote:
I was wondering if you’d specified a character set in your own MySQL
database – from what I’ve read, Latin1 avoids this issue...but likely
causes others.
Anyway, CX is trying to create the COMICS table – the problem seems to
be the inclusion FILENAME in the key.
I copied the create table SQL, and put it into a MySQL editor....if I
remove the filename from the key, the table creates with no issues.
On Sat, Apr 4, 2020 at 6:47 PM Guy Incognito
<dmarc-noreply@xxxxxxxxxxxxx> wrote:
Right, so I’m looking at this again – although this time I’m much
further than I got last time.
CX is connecting to the MySQL database, and is trying to create the
tables and such – however I’m getting this error: Error: Specified key
was too long; max key length is 3072 bytes [
Google indicates it’s a Character Set issue – so my question is what
character set are you using in your MySQL db?
--
Darryl L. Pierce <mcpierce@xxxxxxxxx>
"Le centre du monde est partout." - Blaise Pascal
"Let's try and find some point of transcendence and leap together." -
Gord Downie
--
Darryl L. Pierce <mcpierce@xxxxxxxxx>
"Le centre du monde est partout." - Blaise Pascal
"Let's try and find some point of transcendence and leap together." -
Gord Downie
--
Darryl L. Pierce <mcpierce@xxxxxxxxx>
"Le centre du monde est partout." - Blaise Pascal
"Let's try and find some point of transcendence and leap together." -
Gord Downie
