June 2017 - discuss - lists.mariadb.org

[Maria-discuss] Time Zone help
by Karthick Subramanian 07 Jun '17

07 Jun '17

All, I am facing some strange issue, I believe its due to my ignorant, would appreciate if you can help me out on this: DB Server system OS CentOS. I have loaded the timezone tables in mysql using: mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql After loading, I have restrated the DB using systemctl restart mariadb: Then I have tried below in DB: select @@global.time_zone,@@system_time_zone; [image: Inline image 1] SELECT CONVERT_TZ( NOW(), @@global.time_zone , 'UTC' ), UTC_TIMESTAMP; [image: Inline image 3] SELECT CONVERT_TZ( NOW(), @@system_time_zone , 'UTC' ), UTC_TIMESTAMP; [image: Inline image 4] SELECT CONVERT_TZ( NOW(), 'EDT' , 'UTC' ), UTC_TIMESTAMP; [image: Inline image 2] For some reason, If I use named TZ in CONVERT_TZ function for FROM_TZ, its always showing NULL. Can anyone help me out what else i can do so I can pass the abbreviation or named TZ in FROM_TZ. Regards, Karthick

2 2

[Maria-discuss] Failure upon startup due to changes in /var/lib/mysql/databasename/
by Timothy D. Legg 07 Jun '17

07 Jun '17

Hello, Thanks for the prior suggestions, they ended well. I found a behavior that I found a slight bit confounding. While I tested the server, one of the wikis I host was modified by a friend of mine. When I broke MariaDB, it was no longer feasible to start it, (to the degree that it could have taken days of tinkering), With MariaDB verified to be not running, I tarred the directory /var/lib/mysql/mywiki/ into a file mywiki_with_new_data.tar. I reinstalled MariaDB thoroughly to force the creation of a new /var/lib/mysql/ and /etc/mysql/ . I felt it was safe to recover the databases from the backup SQL file I had created on the currently running machine. Everything now works great, but the mywiki database doesn't reflect the changes anymore. So I stop the mysql process and then for the sake of recoverability, I tar up /var/lib/mysql/mywiki into a file called mywiki_without_new_data.tar. I then rm -rf /var/lib/mysql/mywiki and then untar the mywiki_with_new_data.tar in it's place. MariaDB will no longer start. If I again rm -rf /var/lib/mysql/mywiki and then untar mywiki_without_new_data.tar into the location, MariaDB starts without error. This isn't a code blue situation, my friend can recreate the change he made and that is the easiest solution, but others reading this might not have that luxury. I am also quite curious about what went wrong and how to repair the problem. I didn't think this was possible to fail. There is an interesting except from the errors described below. I'm not certain what exactly this means, or how to correct it. Given that MariaDB could have failed uncleanly when the mysql database was imported, recovery may be infeasible. (the wiki connected to MariaDB as root, it's bad, I know) <excerpt /var/lib/mysql/altdorf.err> InnoDB: Error: tablespace id is 235 in the data dictionary InnoDB: but in file ./mywiki/objectcache.ibd it is 254! 2017-06-07 11:45:08 5c7ff420 InnoDB: Assertion failure in thread 1551889440 in file fil0fil.cc line 638 InnoDB: We intentionally generate a memory trap. </excerpt> # service mysql start Job for mysql.service failed. See 'systemctl status mysql.service' and 'journalctl -xn' for details. # systemctl -l status mysql.service * mysql.service - LSB: Start and stop the mysql database server daemon Loaded: loaded (/etc/init.d/mysql) Active: failed (Result: exit-code) since Wed 2017-06-07 11:45:40 CEST; 1min 16s ago Process: 1692 ExecStop=/etc/init.d/mysql stop (code=exited, status=0/SUCCESS) Process: 1753 ExecStart=/etc/init.d/mysql start (code=exited, status=1/FAILURE) Jun 07 11:45:08 altdorf mysql[1753]: /etc/init.d/mysql: WARNING: /etc/mysql/my.cnf cannot be read. See README.Debian.gz ... (warning). Jun 07 11:45:40 altdorf /etc/init.d/mysql[2152]: 0 processes alive and '/usr/bin/mysqladmin --defaults-file=/etc/mysql/debian.cnf ping' resulted in Jun 07 11:45:40 altdorf /etc/init.d/mysql[2152]: [61B blob data] Jun 07 11:45:40 altdorf /etc/init.d/mysql[2152]: error: 'Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2 "No such file or directory")' Jun 07 11:45:40 altdorf /etc/init.d/mysql[2152]: Check that mysqld is running and that the socket: '/var/run/mysqld/mysqld.sock' exists! Jun 07 11:45:40 altdorf /etc/init.d/mysql[2152]: Jun 07 11:45:40 altdorf mysql[1753]: Starting MariaDB database server: mysqld . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . failed! Jun 07 11:45:40 altdorf systemd[1]: mysql.service: control process exited, code=exited status=1 Jun 07 11:45:40 altdorf systemd[1]: Failed to start LSB: Start and stop the mysql database server daemon. Jun 07 11:45:40 altdorf systemd[1]: Unit mysql.service entered failed state. # journalctl -xn -- Logs begin at Wed 2017-05-24 10:06:00 CEST, end at Wed 2017-06-07 11:45:40 CEST. -- Jun 07 11:45:08 altdorf /etc/init.d/mysql[1764]: WARNING: /etc/mysql/my.cnf cannot be read. See Jun 07 11:45:40 altdorf /etc/init.d/mysql[2152]: 0 processes alive and '/usr/bin/mysqladmin --de Jun 07 11:45:40 altdorf /etc/init.d/mysql[2152]: [61B blob data] Jun 07 11:45:40 altdorf /etc/init.d/mysql[2152]: error: 'Can't connect to local MySQL server thr Jun 07 11:45:40 altdorf /etc/init.d/mysql[2152]: Check that mysqld is running and that the socke Jun 07 11:45:40 altdorf /etc/init.d/mysql[2152]: Jun 07 11:45:40 altdorf mysql[1753]: Starting MariaDB database server: mysqld . . . . . . . . . Jun 07 11:45:40 altdorf systemd[1]: mysql.service: control process exited, code=exited status=1 Jun 07 11:45:40 altdorf systemd[1]: Failed to start LSB: Start and stop the mysql database serve -- Subject: Unit mysql.service has failed -- Defined-By: systemd -- Support: http://lists.freedesktop.org/mailman/listinfo/systemd-devel -- -- Unit mysql.service has failed. -- -- The result is failed. Jun 07 11:45:40 altdorf systemd[1]: Unit mysql.service entered failed state. # cat /var/lib/mysql/aldorf.err (excerpts from file with a database name changed to protect user privacy) 170607 11:45:08 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql 170607 11:45:08 [Note] /usr/sbin/mysqld (mysqld 10.0.30-MariaDB-0+deb8u2) starting as process 1851 ... 170607 11:45:08 [Note] InnoDB: Using mutexes to ref count buffer pool pages 170607 11:45:08 [Note] InnoDB: The InnoDB memory heap is disabled 170607 11:45:08 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins 170607 11:45:08 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier 170607 11:45:08 [Note] InnoDB: Compressed tables use zlib 1.2.8 170607 11:45:08 [Note] InnoDB: Using Linux native AIO 170607 11:45:08 [Note] InnoDB: Not using CPU crc32 instructions 170607 11:45:08 [Note] InnoDB: Initializing buffer pool, size = 128.0M 170607 11:45:08 [Note] InnoDB: Completed initialization of buffer pool 170607 11:45:08 [Note] InnoDB: Highest supported file format is Barracuda. 170607 11:45:08 [Note] InnoDB: 128 rollback segment(s) are active. 170607 11:45:08 [Note] InnoDB: Waiting for purge to start InnoDB: Error: tablespace id is 235 in the data dictionary InnoDB: but in file ./mywiki/objectcache.ibd it is 254! 2017-06-07 11:45:08 5c7ff420 InnoDB: Assertion failure in thread 1551889440 in file fil0fil.cc line 638 InnoDB: We intentionally generate a memory trap. InnoDB: Submit a detailed bug report to http://bugs.mysql.com. InnoDB: If you get repeated assertion failures or crashes, even InnoDB: immediately after the mysqld startup, there may be InnoDB: corruption in the InnoDB tablespace. Please refer to InnoDB: http://dev.mysql.com/doc/refman/5.6/en/forcing-innodb-recovery.html InnoDB: about forcing recovery. 170607 11:45:08 [ERROR] mysqld got signal 6 ; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. To report this bug, see https://mariadb.com/kb/en/reporting-bugs We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. Server version: 10.0.30-MariaDB-0+deb8u2 key_buffer_size=134217728 read_buffer_size=131072 max_used_connections=0 max_threads=153 thread_count=0 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 465934 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. Thread pointer: 0x0 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... stack_bottom = 0x0 thread_stack 0x48000 The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains information that should help you find out what is causing the crash. 170607 11:45:08 mysqld_safe mysqld from pid file /var/lib/mysql/altdorf.pid ended

4 3

[Maria-discuss] Error when switching to MariaDB
by Timothy D. Legg 07 Jun '17

07 Jun '17

Hello, First of all, here is what I'm working with: Original machine: MySQL Server 5.7.18-0ubuntu0.16.04.1 Destination machine: MariaDB 10.0.30-MariaDB-0+deb8u2 These two machines have no filesystem or communication with with other. I am moving to MariaDB with about 10 to 15 databases that were created in MySQL and I have had a very odd problem with the destination machine running MariaDB where the applications using these databases were having connection errors with their databases. I looked very carefully at what I had done. I exported the databases from MySQL using: $ mysqldump --all-databases > all.sql and imported the collection on the new machine with $ mysql -u root -p < all.sql When I log in as root, all appears well when I inspect the databases, but all is not well. The applications the use these databases cannot connect. I enter the command line to connect using the SQL user accounts, and guess what, they don't log in with their respective passwords. I can log in on the MySQL server using the same account/password pairs. I think it might be a permissions issue. I log into the MariaDB server as root and flush privileges, just to make sure, and I get a terribly vague error message (with no details dropped off in /var/log/mysql/ either). MariaDB [(none)]> flush privileges; ERROR 1105 (HY000): Unknown error When I reach this point, I perform something akin to these steps: sudo su service stop mysql apt-get purge mariadb-server mariadb-server-10.0 mariabd-comon mariadb-client mariadb-client-10.0 mariadb-client-core-10.0 mariadb-server-core-10.0 rm -rf /etc/mysql rm -rf /var/lib/mysql apt-get install mariadb-server mariadb-client exit and I'm asked for root password, etc and also run mysql_secure_installation with all suggested values. Voila, flush privileges works again on the MariaDB server, at least until I attempt to reinsert the databases. Are these problems somewhat unique? Is there a way to pry additional detail from mariadb, such as launching the server in a debug mode? What scenarios have people seen cause this in the past? Where do I look for detailed symptoms? Timothy D. Legg

2 2

[Maria-discuss] Maxscale: qlafilter
by Atif Ghaffar 06 Jun '17

06 Jun '17

Hi all, I have setup a maxscale instance to proxy to the local mysql server running on a different port. All this is working fine. I have also setup a filter to catch deletes on a single table. I can see the deletes in the filter but only when I restart maxscale service. Is there something that I can set so that the log is flushed immediately to the flle. The filter definition is the following [WaCountriesDomainsDeleteFilter] type=filter module=qlafilter options=ignorecase,extended filebase=/tmp/maxscale.wa_countries_domains.delete match=DELETE.*wa_countries_domains.* log_type=unified append=true kind regards -- Atif Ghaffar

2 2

[Maria-discuss] MyRocks:recommended configuration for DB of several TB ?
by Julien Muchembled 01 Jun '17

01 Jun '17

Hello, We have started to evaluate MyRocks 3 months ago, our DBs were initially empty and now they have reached 500 GB on-disk. We experience severe performance issues, and we have the feelings it could be better. https://github.com/facebook/rocksdb/wiki/RocksDB-Tuning-Guide is interesting, especially the "Universal Compaction" section: « Universal compaction has a size limitation. Please be careful when your DB (or column family) size is over 100GB. » I don't think that write amplification is a bottleneck for our application, so my obvious first question is: What compaction style is used in MariaDB and how to make sure that Level Style Compaction is used ? Also, we haven't set any 'rocksdb_*' option. The default configuration may not be appropriate in our case. For example, rocksdb_block_cache_size (default: 512MB) seems to be one of the most important options, similarly to innodb_buffer_pool_size and tokudb_cache_size, although I have never understood why (see my unanswered question at https://lists.launchpad.net/maria-discuss/msg02683.html ). Although the DB is much bigger than RAM (128 GB), RAM should be big enough to contain the active working set. Julien

2 1