October 2014 - discuss - lists.mariadb.org

[Maria-discuss] question regarding innodb compression
by Rohan M C 03 Nov '14

03 Nov '14

Hi all, Due to space constraints we are considering the innodb compression option for some of our larger tables. We're not compressing all the tables, but only a select few to begin with. Our current planned process for this is to SET GLOBAL innodb_file_format=Barracuda; create compressed table, transfer data, and switch innodb_file_format back to Antelope. My question is around having this mix of table file formats in the same database. I've tried test queries and they were fine, but is it ok to have a mix of both Antelope and Barracuda file format tables in the db for a production environment? Thanks, Rohan

3 4

[Maria-discuss] MariaDB on Centos 7
by Blake 29 Oct '14

29 Oct '14

Since I had a few issues with it thought I would provide some information for those who are trying to install and help whoever is responsible for the Maria Repository. First, I was successful using the Centos 6 10.1 package via Yum with this repository setting pulled from Mariadb Repository setup. # MariaDB 10.1 CentOS repository list - created 2014-10-29 14:33 UTC # http://mariadb.org/mariadb/repositories/ [mariadb] name = MariaDB baseurl = http://yum.mariadb.org/10.1/centos6-amd64 gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB gpgcheck=1 Using "yum clean all" and yum update the following packages installed successfully. Shown in this pastebin http://pastebin.com/SMqECaju I did find though that with 10.0 using both the Centos 6 & 7 packages there is an issue with the language error files between MariaDB-server-10.0.14-1.el7.centos.x86_64 and dependency mariadb-libs-1:5.5.37-1.el7_0.x86_64 http://pastebin.com/CSri8mq0 I hope this information helps someone else and possibly the listing of a 10.1 Centos 7 package in the MariaDB Repository. ~Blake

1 0

[Maria-discuss] Storage engines enabled by default - MySQL 5.6 vs. MariaDB Galera Cluster 10.0
by Geoff Montee 29 Oct '14

29 Oct '14

I've been building MySQL 5.6 from source for a while. I recently started testing MariaDB Galera Cluster 10.0. When I build MariaDB Galera Cluster from source, I originally used the same cmake flags that I used for MySQL 5.6, along with the additional flags specified in Codership's documentation: http://galeracluster.com/documentation-webpages/installmariadbsrc.html My build process basically comes down to this: cmake . -L -DCMAKE_INSTALL_PREFIX=$MYQL_PREFIX -DMYSQL_DATADIR=$MYSQL_DATA -DWITH_SSL=yes -DWITH_READLINE=ON -DWITH_PIC=ON -DWITH_UNIT_TESTS=0 -DWITH_WSREP=ON -DWITH_INNODB_DISALLOW_WRITES=1 make make install With MariaDB Galera Cluster 10.0.14, the server that gets built does not have the Archive and Blackhole storage engines enabled by default. I do see that "ha_archive.so" and "ha_blackhole.so" were built and placed in plugin_dir, so I could probably enable them manually with INSTALL PLUGIN. This surprised me, because these engines are enabled by default in MySQL 5.6, and I see no mention of this difference here: https://mariadb.com/kb/en/mariadb/mariadb-vs-mysql-compatibility/#incompati… Of course, if I add "-DWITH_ARCHIVE_STORAGE_ENGINE=ON -DWITH_BLACKHOLE_STORAGE_ENGINE=ON" to my cmake arguments, then these engines are enabled in the MariaDB server that gets built. Is this difference intentional? If so, you may want to add a note to the documentation referenced above. If it's an unintentional discrepancy, I can submit a bug report on JIRA. Thanks, Geoff

2 2

[Maria-discuss] Dots in filenames of configuration files - !includedir weirdness
by Geoff Montee 24 Oct '14

24 Oct '14

I've noticed some strange behavior that might be a bug in the current implementation of the !includedir directive for my.cnf files. I wanted to get the opinion of the MariaDB community on whether this is working as intended before reporting it. Let's say I have a line like the following in /etc/my.cnf: !includedir /home/mysql/conf MariaDB should load any configuration variables from files with the extension ".cnf" in that directory. The problem I ran into is that MariaDB appears to determine the extension based on the first dot ('.') in the filename. If I have a file with a full path like this: /home/mysql/conf/mariadb10.0-galera.cnf MariaDB appears to think that the extension of the file is 0-galera.cnf, so it won't load it. If the file is renamed to something like this (with no extra dots): /home/mysql/conf/mariadb10-0-galera.cnf MariaDB properly loads the configuration file. Is this a bug that should be reported? System info: OS: RHEL 6 Arch: x86_64 MariaDB build: MariaDB Galera Cluster 10.0.14, built from source Slightly related note: I don't see any documentation on the !includedir directive in the MariaDB knowledgebase. I would expect to see some mention of it here: https://mariadb.com/kb/en/mariadb/documentation/getting-started/configuring… Thanks, Geoff Montee

2 3

[Maria-discuss] New Question: notifier for mariadb
by AskMonty KB 22 Oct '14

22 Oct '14

Hello, A new question has been asked in "Moving from MySQL" by noirefuse: -------------------------------- One thing I miss from MySQL is their windows tray app, MySQL notifier. I am currently using it with MariaDB instead, but would like to see an open source alternative to it. I Haven't been able to find one. Are there any plans to include something like that with the windows install in the future? -------------------------------- To view or answer this question please visit: http://mariadb.com/kb/en/notifier-for-mariadb/

1 0

[Maria-discuss] New Question: sporadic issue with opening connection and Mariadb
by AskMonty KB 22 Oct '14

22 Oct '14

Hello, A new question has been asked in "Moving from MySQL" by helenfeder: -------------------------------- I am using jpa with Spring and Hibernate and testing my application with MariaDB. It is mostly working but then every once in awhile (cannot detect any pattern) I am seeing sporadic Errors like the one below. I am going against a single database machine and we have made sure that there are no firewall preventing access between my application server and the database server. Most of the time the database calls are successful - just periodically they are not. I have been using jmeter to simulate load to help me reproduce the issue. What is causing this problem and how can I correct it? I am using the following properties in my connection (removed our hostname, dbname, port,etc for security reasons): dataSource.url=jdbc:mariadb://<our db hostname: port>/<db-name>?trustServerCertificate=true&useSSL=true&dumpQueriesOnException=true&ssl-cert=/home/hrf/credMgmtDbCerts/client-cert.pem&ssl-key=/home/hrf/credMgmtDbCerts/client-key.pem dataSource.driverClassName=org.mariadb.jdbc.Driver dataSource.dialect=org.hibernate.dialect.MySQL5Dialect dataSource.user=<username> dataSource.password=<password> Here is the relevant part of the stack trace I am seeing --> Caused by: javax.persistence.PersistenceException: org.hibernate.exception.JDBCConnectionException: Could not open connection at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1387) at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1310) at org.hibernate.ejb.AbstractEntityManagerImpl.throwPersistenceException(AbstractEntityManagerImpl.java:1397) at org.hibernate.ejb.TransactionImpl.begin(TransactionImpl.java:62) at org.springframework.orm.jpa.DefaultJpaDialect.beginTransaction(DefaultJpaDialect.java:67) at org.springframework.orm.jpa.JpaTransactionManager.doBegin(JpaTransactionManager.java:380) ... 39 more Caused by: org.hibernate.exception.JDBCConnectionException: Could not open connection at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:67) at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49) at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:125) at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:110) at org.hibernate.engine.jdbc.internal.LogicalConnectionImpl.obtainConnection(LogicalConnectionImpl.java:221) at org.hibernate.engine.jdbc.internal.LogicalConnectionImpl.getConnection(LogicalConnectionImpl.java:157) at org.hibernate.engine.transaction.internal.jdbc.JdbcTransaction.doBegin(JdbcTransaction.java:67) at org.hibernate.engine.transaction.spi.AbstractTransactionImpl.begin(AbstractTransactionImpl.java:160) at org.hibernate.internal.SessionImpl.beginTransaction(SessionImpl.java:1432) at org.hibernate.ejb.TransactionImpl.begin(TransactionImpl.java:59) ... 41 more Caused by: java.sql.SQLNonTransientConnectionException: Could not connect to <hostname:port>: Unsupported record version Unknown-0.0 at org.mariadb.jdbc.internal.SQLExceptionMapper.get(SQLExceptionMapper.java:136) at org.mariadb.jdbc.internal.SQLExceptionMapper.throwException(SQLExceptionMapper.java:106) at org.mariadb.jdbc.MySQLDataSource.getConnection(MySQLDataSource.java:291) at org.hibernate.ejb.connection.InjectedDataSourceConnectionProvider.getConnection(InjectedDataSourceConnectionProvider.java:70) at org.hibernate.internal.AbstractSessionImpl$NonContextualJdbcConnectionAccess.obtainConnection(AbstractSessionImpl.java:292) at org.hibernate.engine.jdbc.internal.LogicalConnectionImpl.obtainConnection(LogicalConnectionImpl.java:214) ... 46 more Caused by: org.mariadb.jdbc.internal.common.QueryException: Could not connect to <hostname:port>: Unsupported record version Unknown-0.0 at org.mariadb.jdbc.internal.mysql.MySQLProtocol.connect(MySQLProtocol.java:513) at org.mariadb.jdbc.internal.mysql.MySQLProtocol.connect(MySQLProtocol.java:673) at org.mariadb.jdbc.internal.mysql.MySQLProtocol.<init>(MySQLProtocol.java:266) at org.mariadb.jdbc.MySQLDataSource.getConnection(MySQLDataSource.java:289) ... 49 more Caused by: javax.net.ssl.SSLException: Unsupported record version Unknown-0.0 at sun.security.ssl.InputRecord.readV3Record(InputRecord.java:524) at sun.security.ssl.InputRecord.read(InputRecord.java:509) at sun.security.ssl.SSLSocketImpl.readRecord(SSLSocketImpl.java:927) at sun.security.ssl.SSLSocketImpl.performInitialHandshake(SSLSocketImpl.java:1312) at sun.security.ssl.SSLSocketImpl.startHandshake(SSLSocketImpl.java:1339) at sun.security.ssl.SSLSocketImpl.startHandshake(SSLSocketImpl.java:1323) at org.mariadb.jdbc.internal.mysql.MySQLProtocol.connect(MySQLProtocol.java:444) ... 52 more Your help is greatly appreciated. Thank you. Helen Feder Principal Systems Software Engineer Carnegie Mellon University -------------------------------- To view or answer this question please visit: http://mariadb.com/kb/en/sporadic-issue-with-opening-connection-and-mariadb/

1 0

[Maria-discuss] New Question: Cluster Overall Memory Requirements (compared to multiple stand-alone nodes)
by AskMonty KB 21 Oct '14

21 Oct '14

Hello, A new question has been asked in "Galera" by getreal: -------------------------------- Maybe I'm just missing it somewhere on the site, but I'm trying to find a way to contrast overall memory requirements for a Galera cluster compared to that of stand-alone mySQL instances. In my profession we build servers that are typically 32GB overall memory running an instance of mySQL within, in which we hand 16GB to innodb_buffer_pool_size (the actually size of the mySQL database files is upwards of four times that). The server does other things by the way (a 6 GB JVM is also loaded). Our buffer pool hit rate is typically a nice high number (over 99%) with that 16GB allocated. So let's take a simple scenario - 6 of my stand-alone servers are going to evolve to MariaDB + Galera cluster. I completely realize we need 3 nodes minimum on the Galera side, and I realize we now are accommodating about 6 x 64GB of former mySQL database files (as long as we presume the database files overall size in mariaDB is about the same as that of mySQL). So I've got 384 GB of database f iles, and let's suppose I want to replace this with a 3 node mariaDB+Galera cluster. I know that 384 GB has to be replicated amongst those three nodes, storage sizing is not so complex, and I know I can probably get enough CPU under these node with 4 vCPUs. But my question is more so around innodb_buffer_pool size for each node. Do I need to run 6 x 16 GB = 96 GB for innodb_buffer_pool_size in each node of that Galera cluster (essentially the same ratio 1:4 as I did in mySQL instances with my stand-alone servers) ? Any help appreciated. -------------------------------- To view or answer this question please visit: http://mariadb.com/kb/en/cluster-overall-memory-requirements-compared-to-mu…

1 0

[Maria-discuss] New Question: field looks weird using select *
by AskMonty KB 19 Oct '14

19 Oct '14

Hello, A new question has been asked in "MariaDB Documentation" by martyheitzmann: -------------------------------- I just started using mariadb. I created two tables so far : the first one - no issues. But, when I try to select * from the 2nd table, the primary key field looks incomplete: +--------+--------+---------------+----------------+ | teamid | league | teamname1 | teamname2 | +--------+--------+---------------+----------------+ | | A | Anaheim | Angels |ARI | N | Arizona | Diamondbacks | | N | Atlanta | Braves | | A | Baltimore | Orioles | | A | Boston | Red Sox | | A | Chicago | White Sox | N | Chicago | Cubs ... (look past the copy/paste formatting issues please) The teamid column is not printed out for every teamid, and based on the '|' separator, it looks like the field has different lengths, but it's just a char(3) fixed-length. I tried defining the field as nvarchar(3), and char(3), with the same results. I thought it had to do with the file from which the data was imported, but it contains no control characters. It's a ':' separated field that looks like: ANA:A:Anaheim:Angels BAL:A:Baltimore:Orioles BOS:A:Boston:Red Sox CHA:A:Chicago:White Sox ... file imported using : LOAD DATA INFILE '<file>' INTO TABLE Tteams FIELDS TERMINATED BY ':'; thoughts? -------------------------------- To view or answer this question please visit: http://mariadb.com/kb/en/field-looks-weird-using-select/

1 0

[Maria-discuss] New Question: How to use SQL Workbench to connect to MariaDB?
by AskMonty KB 19 Oct '14

19 Oct '14

Hello, A new question has been asked in "Does MariaDB support..." by aaaaaa: -------------------------------- How to use SQL Workbench to connect to MariaDB? -------------------------------- To view or answer this question please visit: http://mariadb.com/kb/en/how-to-use-sql-workbench-to-connect-to-mariadb/

1 0

[Maria-discuss] New Question: Historical data, nightly snapshots
by AskMonty KB 18 Oct '14

18 Oct '14

Hello, A new question has been asked in "MariaDB Documentation" by bjquinn: -------------------------------- I have an application where I need to take nightly snapshots of a large InnoDB database (10s of GB) and then have them available to be queried alongside the current data. My underlying filesystem is (or at least can be) ZFS, so actually creating the snapshots is easy enough. But how do I connect to the files contained in the snapshot so that I can query the tables via MariaDB? If it's easier through another storage engine, I could consider that, though I do need row locking in the current data. The historical data would be read only (obviously) and wouldn't need to be changed. -------------------------------- To view or answer this question please visit: http://mariadb.com/kb/en/historical-data-nightly-snapshots/

1 0