cluster_nodes table produces an exception due to the schema problem in mysql (#404)

Dathan Pattishall opened 1 decade ago

Due Date
2015-01-15

tigase.cluster.repo.CliConSQLRepository defines cluster_nodes

the create table statement is

"create table " + TABLE_NAME + " ("

				+ "  " + HOSTNAME_COLUMN + " varchar(255) not null,"

				+ "  " + PASSWORD_COLUMN + " varchar(255) not null,"

				+ "  " + LASTUPDATE_COLUMN

				+ " TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,"

				+ "  " + PORT_COLUMN + " int,"

				+ "  " + CPU_USAGE_COLUMN + " double precision unsigned not null,"

				+ "  " + MEM_USAGE_COLUMN + " double precision unsigned not null,"

				+ "  primary key(" + HOSTNAME_COLUMN + "))";

The code assumes that the charset is latin1 but since many mysql installs sets a default character set and a default storage engine errors such as

2014-12-17 21:43:02.714 [main] ClConSQLRepository.initRepository() WARNING: Problem initializing database:

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Specified key was too long; max key length is 767 bytes

at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)

at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)

at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)

at java.lang.reflect.Constructor.newInstance(Constructor.java:526)

at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)

at com.mysql.jdbc.Util.getInstance(Util.java:381)

at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1030)

at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)

at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3491)

at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3423)

at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1936)

at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2060)

at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2536)

at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1564)

at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1485)

at tigase.cluster.repo.ClConSQLRepository.checkDB(ClConSQLRepository.java:326)

at tigase.cluster.repo.ClConSQLRepository.initRepository(ClConSQLRepository.java:176)

at tigase.cluster.repo.ClConSQLRepository.setProperties(ClConSQLRepository.java:278)

at tigase.cluster.ClusterConnectionManager.setProperties(ClusterConnectionManager.java:917)

at tigase.conf.ConfiguratorAbstract.setup(ConfiguratorAbstract.java:550)

at tigase.conf.ConfiguratorAbstract.componentAdded(ConfiguratorAbstract.java:182)

at tigase.conf.Configurator.componentAdded(Configurator.java:50)

at tigase.conf.Configurator.componentAdded(Configurator.java:33)

at tigase.server.AbstractComponentRegistrator.addComponent(AbstractComponentRegistrator.java:115)

at tigase.server.MessageRouter.addComponent(MessageRouter.java:118)

at tigase.server.MessageRouter.addRouter(MessageRouter.java:155)

at tigase.server.MessageRouter.setProperties(MessageRouter.java:797)

at tigase.conf.ConfiguratorAbstract.setup(ConfiguratorAbstract.java:550)

at tigase.conf.ConfiguratorAbstract.componentAdded(ConfiguratorAbstract.java:182)

at tigase.conf.Configurator.componentAdded(Configurator.java:50)

at tigase.conf.Configurator.componentAdded(Configurator.java:33)

at tigase.server.AbstractComponentRegistrator.addComponent(AbstractComponentRegistrator.java:115)

at tigase.server.MessageRouter.addRegistrator(MessageRouter.java:141)

at tigase.server.MessageRouter.setConfig(MessageRouter.java:696)

at tigase.server.XMPPServer.start(XMPPServer.java:142)

at tigase.server.XMPPServer.main(XMPPServer.java:112)

I suggest adding the following to the create table statement

private static final String CREATE_TABLE_QUERY_MYSQL =

				"create table " + TABLE_NAME + " ("

				+ "  " + HOSTNAME_COLUMN + " varchar(255) not null,"

				+ "  " + PASSWORD_COLUMN + " varchar(255) not null,"

				+ "  " + LASTUPDATE_COLUMN

				+ " TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,"

				+ "  " + PORT_COLUMN + " int,"

				+ "  " + CPU_USAGE_COLUMN + " double precision unsigned not null,"

				+ "  " + MEM_USAGE_COLUMN + " double precision unsigned not null,"

				+ "  primary key(" + HOSTNAME_COLUMN + ")) DEFAULT CHARSET=latin1 ENGINE=INNODB";

create table cluster_nodes (

hostname varchar(255) not null,

password varchar(255) not null,

last_update TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

port int,

cpu_usage double precision unsigned not null,

mem_usage double precision unsigned not null,

primary key(hostname)

) ENGINE=INNODB DEFAULT CHARSET=latin1;

Activities

Artur Hefczyc commented 1 decade ago
Thank you for the bug report. I have a few questions though:

What mysql version do you use?

What JDBC driver do you use, the one provided with our binary distribution?
Dathan Pattishall commented 1 decade ago

I use mysql 5.5, 5.6 super customized and tweaked out (my specialty) and I also use the JDBC driver provided with the distribution.

The specific problem is the following and affects 5.1

my default charset is utf8 and my default storage engine is innodb, thus without any specific directives in the table create statement all columns default to utf8

varchar(255) is the MAX number of characters 255 characters in this case

BUT a utf8 character is 2 - 4 bytes instead of 1 byte for latin1 which is the default config for mysql

The MAX index size for a PRIMARY key cannot exceed a few hundred bytes, in fact any primary key with 50 bytes or more takes a penalty.

http://www.xaprb.com/blog/2006/04/17/max-key-length-in-mysql/ http://www.xaprb.com/blog/2006/04/17/max-key-length-in-mysql/

The MAX secondary index length of all columns in the index is 3072 bytes, (innodb is 3400 bytes but mysql limits the index length)

Yet UNIQUE indexes such as PRIMARY and UNIQUE is MUCH less then that.

On Dec 17, 2014, at 4:52 PM, support@tigase.org wrote:
Artur Hefczyc commented 1 decade ago

Hm, we usually use utf8 encoding by default in our databases, so I wonder why this wasn't discovered sooner.

Thank you very much for the bug report and all the details, we will look at it as soon as possible.
Dathan Pattishall commented 1 decade ago

The messaging table is also affected.

On Dec 17, 2014, at 5:24 PM, support@tigase.org wrote:
Dathan Pattishall commented 1 decade ago

in your my.cnf under the mysql section add

character-set-server = utf8mb4

collation-server = utf8mb4_unicode_ci

utf8mb4 is an extension of utf8 to support emojis

On Dec 17, 2014, at 5:24 PM, support@tigase.org wrote:
Artur Hefczyc commented 1 decade ago

Dathan,

Thank you for additional comments and information.
Login to comment

Type	Bug
Priority	Normal
Assignee	Artur Hefczyc
RedmineID	2544
Version	tigase-server-5.2.3
Spent time	0

Issue Votes (0)

Watchers (0)

Reference

tigase/_server/server-core#404