MySQL problem with max key length

[lsdfr]

Hi
I've been running Vueminder on a Freenas+Mysql for ages - no real problems.
I've now switched over to OpenMediaVault , same PC.
I can connect to the Mysql with the test connection and a VueMinder database gets created - 1 table for transactions.
Clicking on the "Server is offline, click to reconnect" I get an error window with

"A problem occurred while attempting to connect to the server
Specified key was too long: max key length is 767 bytes"

I've created a user with full global/database privileges just to make sure but still same problems


Help Please

[VueSoft]

Hi lsdfr, do you only see the Transactions table, no other tables? The next table VueMinder will try creating after the Transactions table is the Calendars table. If you don't see a Calendars table, then something is failing when VueMinder attempts to create that table. I don't see anything in the table creation SQL which would explain the error.

Can you connect to the server using MySQL Workbench and then run the following SQL? This is the same thing VueMinder is doing when trying to create the Calendars table:

Code: Select all

CREATE TABLE IF NOT EXISTS Calendars (
  CalendarId VARCHAR(250) UNIQUE NOT NULL,
  CalendarName TEXT NULL,
  Data MEDIUMTEXT NULL,
  Sequence BIGINT NULL,
  CalendarType INTEGER DEFAULT '0' NULL)

Do you see the same error VueMinder is reporting? There might be something weird going on with OpenMediaVault and MySQL Server.

[lsdfr]

Hi Dan

Pasted the SQL commands in HeidiSQL and got the same

CREATE TABLE IF NOT EXISTS Calendars (
CalendarId VARCHAR(250) UNIQUE NOT NULL,
CalendarName TEXT NULL,
Data MEDIUMTEXT NULL,
Sequence BIGINT NULL,
CalendarType INTEGER DEFAULT '0' NULL)
;
/* SQL Error (1071): Specified key was too long; max key length is 767 bytes */
/* Affected rows: 0 Found rows: 0 Warnings: 0 Duration for 0 of 1 query: 0.000 sec. */

The database is MariaDB V10.1.37 running on OpenMediaVault
Found this on the web https://stackoverflow.com/questions/181 ... -767-bytes

Leigh

[lsdfr]

Just to try out what someone said in the link I posted I set VARCHAR to (191) and that works so will Vueminder work OK with a smaller VARCHAR ?

Few more bits of info here https://stackoverflow.com/questions/227 ... in-mariadb
and here https://github.com/privacyidea/privacyidea/issues/967

They talk about "innodb_large_prefix=on" as a possible fix, my SQL has innodb_large_prefix=off
Not sure where I can change it or even if I should

Just to show everyone I know nothing about SQL

Leigh

[VueSoft]

Hi Leigh, thank you for the feedback. These VARCHAR(250) fields are used for uniquely identifying values in VueMinder. When items are created in VueMinder, the unique identifiers are much smaller than 250 characters. However, when VueMinder imports data from external sources or syncs with other calendars, the identifiers might be longer because they are created outside of VueMinder. That's why the value is currently 250. That's probably overkill, but I wanted to make sure any identifier would properly fit.

I'll see about updating the next version of VueMinder to use smaller maximum field sizes when storing the unique identifiers in the database. In the meantime, there's a version of MySQL Server which can be installed on Windows and doesn't have these weird limitations. You can download it here:
https://dev.mysql.com/get/Downloads/MyS ... 7.22.1.msi

[lsdfr]

Thanks Dan

I need to stick with the Openmedia/Linux as this runs 24/7 so that my 2 PC's will be in sync. PC's are not always on - Home office enviroment
I'll try turning on the flag above, once I've found out where its stored
If that does not work I'll install a newer MYSQL version (non innodb mode) under Docker, just need to understand how Docker works next

Leigh

[lsdfr]

After a number of goes I have sync working, it appears anyway

Under /etc/MySQL edited mariadb.cnf
added

[client-server]
innodb_large_prefix=1
innodb_file_format=barracuda
innodb_file_per_table=true
innodb_file_format_max=barracuda
innodb_default_row_format=DYNAMIC

You need all the flags set for it to work.

Leigh