Ignite Realtime

powered by Jive Software

Ignite Realtime Community Forums

MySQL DB openfire OS Debian

Aleksej_Opryshko June 1, 2020, 7:16am 1

MariaDB [openfire]> source /usr/share/openfire/resources/database/openfire_mysql.sql;
ERROR 1071 (42000) at line 119 in file: ‘/usr/share/openfire/resources/database/openfire_mysql.sql’: Specified key was too long; max key length is 767 bytes
ERROR 1071 (42000) at line 127 in file: ‘/usr/share/openfire/resources/database/openfire_mysql.sql’: Specified key was too long; max key length is 767 bytes
ERROR 1071 (42000) at line 143 in file: ‘/usr/share/openfire/resources/database/openfire_mysql.sql’: Specified key was too long; max key length is 767 bytes
ERROR 1071 (42000) at line 163 in file: ‘/usr/share/openfire/resources/database/openfire_mysql.sql’: Specified key was too long; max key length is 767 bytes
ERROR 1146 (42S02) at line 372 in file: ‘/usr/share/openfire/resources/database/openfire_mysql.sql’: Table ‘openfire.ofMucService’ doesn’t exist

MariaDB [openfire]> show tables;
±---------------------+
| Tables_in_openfire |
±---------------------+
| mucextinfo |
| ofBookmark |
| ofConParticipant |
| ofConversation |
| ofExternalServices |
| ofGroup |
| ofGroupProp |
| ofGroupUser |
| ofID |
| ofMessageArchive |
| ofMucAffiliation |
| ofMucConversationLog |
| ofMucMember |
| ofMucRoom |
| ofMucRoomProp |
| ofMucServiceProp |
| ofOffline |
| ofPresence |
| ofPrivacyList |
| ofProperty |
| ofPubsubAffiliation |
| ofPubsubDefaultConf |
| ofPubsubItem |
| ofPubsubNode |
| ofPubsubNodeGroups |
| ofPubsubNodeJIDs |
| ofPubsubSubscription |
| ofPushNotiService |
| ofRRDs |
| ofRoster |
| ofRosterGroups |
| ofSecurityAuditLog |
| ofUser |
| ofUserFlag |
| ofUserProp |
| ofVCard |
| ofVersion |
±---------------------+

Due to the fact that this Table 'openfire.ofMucService’is missing Some errors pop up. For example, you don’t create a service or switch to the room administrator. I think a lot of errors with plugins where this table appears.
Please help me create this table with all its parameters.

guus June 1, 2020, 8:16am 2

I seem to recall that someone posted earlier about using a specific character set or collate that caused this. I can’t find the original message though. What character set are you using? Can you try to use some alternatives?

Aleksej_Opryshko June 1, 2020, 8:36am 3

I am running a script that comes with openfire. decided to check maybe something is missing. and found that at least one table is missing.
/usr/share/openfire/resources/database/openfire_mysql.sql

guus June 1, 2020, 9:15am 4

Is the table missing in the create script, or is the table missing in the database, after running the script?

Aleksej_Opryshko June 1, 2020, 9:26am 5

I wrote in the beginning. I run the script I see errors. checked what tables are. as a result, the table was not created after applying the script

guus June 1, 2020, 9:37am 6

Try creating a database with a different character set or collate. That might help.

Aleksej_Opryshko June 1, 2020, 10:18am 7

correctly I understand you need to create a new database and map

Aleksej_Opryshko June 1, 2020, 12:20pm 8

Created a new openfire1 database.
started the script
here is the result.
MariaDB [(none)]> use openfire1;
Database changed
MariaDB [openfire1]> source /usr/share/openfire/resources/database/openfire_mysq l.sql;
Query OK, 0 rows affected (0.03 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected, 1 warning (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

ERROR 1071 (42000) at line 119 in file: ‘/usr/share/openfire/resources/database/ openfire_mysql.sql’: Specified key was too long; max key length is 767 bytes
ERROR 1071 (42000) at line 127 in file: ‘/usr/share/openfire/resources/database/ openfire_mysql.sql’: Specified key was too long; max key length is 767 bytes
Query OK, 0 rows affected (0.00 sec)

ERROR 1071 (42000) at line 143 in file: ‘/usr/share/openfire/resources/database/ openfire_mysql.sql’: Specified key was too long; max key length is 767 bytes
Query OK, 0 rows affected (0.02 sec)

ERROR 1071 (42000) at line 163 in file: ‘/usr/share/openfire/resources/database/ openfire_mysql.sql’: Specified key was too long; max key length is 767 bytes
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.02 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.02 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

ERROR 1146 (42S02) at line 372 in file: ‘/usr/share/openfire/resources/database/ openfire_mysql.sql’: Table ‘openfire1.ofMucService’ doesn’t exist

Aleksej_Opryshko June 1, 2020, 2:01pm 9

openfire_mysql.sql (12.4 КБ)
/ usr / share / openfire / resources / database / openfire_mysq l.sql

guus June 1, 2020, 2:24pm 10

What character set and collate options does your database use. Try changing it.

Aleksej_Opryshko June 1, 2020, 2:41pm 11

base called openfire. the base is configured on Mandriva OS debian9. I don’t quite understand you.

guus June 1, 2020, 5:09pm 12

They character set and collation combination is defined here https://mariadb.com/kb/en/setting-character-sets-and-collations/

I seem to recall that someone else reported that using a different one made the error that you are reporting go away.

Aleksej_Opryshko June 1, 2020, 5:52pm 13

Yes, right, that’s exactly what happened to me. Messages disappeared. I did on this article
https://www.dmosk.ru/instruktions.php?object=openfire-ubuntu
I tried different java versions until I realized that there was a problem with the database

Aleksej_Opryshko June 1, 2020, 7:09pm 14

MariaDB [(none)]> SHOW CREATE DATABASE openfire;
±---------±---------------------------------------------------------------------+
| Database | Create Database |
±---------±---------------------------------------------------------------------+
| openfire | CREATE DATABASE openfire /*!40100 DEFAULT CHARACTER SET utf8mb4 */ |
±---------±---------------------------------------------------------------------+
1 row in set (0.00 sec)

Database for openfire the default character set should be used in Debian?

I came across an article. Fix it so it doesn’t break everything.
I).
1). CREATE DATABASE openfire CHARACTER SET utf8 COLLATE utf8_general_ci;

2). Or change the encoding of the current database:
use openfire;
alter database character set utf8;
alter database collate utf8_general_ci;
II). openfire.xml add a line

com.mysql.jdbc.Driver
………………

………………

true

III). you must specify it in the MySQL server settings in the [client] and [mysqld sections]

default-character-set = utf8

link to the article truth it is in Russian
http://mtaalamu.ru/blog/admining/1516.html
I’m waiting for you to correct the order of priority maybe you missed something.

Please specify in the documentation the requirements for the Linux operating system. version of Java. Requirements for databases namely encoding and so on.

Thank you for your efforts and your time!!!