- 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

[Maria-discuss] MariaDB 10.1.24 and Connector/C 2.3.3 now available
by Daniel Bartholomew 31 May '17

31 May '17

The MariaDB project is pleased to announce the immediate availability of MariaDB 10.1.24 and MariaDB Connector/C 2.3.3. See the Release Notes and Changelogs for details. - - Links - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - MariaDB 10.1.24 - Release Notes: https://mariadb.com/kb/en/mdb-10124-rn/ - Changelog: https://mariadb.com/kb/en/mdb-10124-cl/ - Downloads: https://downloads.mariadb.org/mariadb/10.1.24 About MariaDB 10.1: - https://mariadb.com/kb/en/what-is-mariadb-101/ APT and YUM Repository Configuration Generator: - https://downloads.mariadb.org/mariadb/repositories/ MariaDB Connector/C 2.3.3 - Release Notes: https://mariadb.com/kb/en/mcc-233-rn/ - Changelog: https://mariadb.com/kb/en/mcc-233-cl/ - Downloads: https://downloads.mariadb.org/connector-c/2.3.3/ About MariaDB Connector/ODBC: - https://mariadb.com/kb/en/about-mariadb-connector-c/ - - MariaDB Webinars - - - - - - - - - - - - - - - - - - - - - - - - - There are several upcoming MariaDB-focused webinars and many previously held ones available to watch on an on-demand basis at: - https://mariadb.com/news-events/webinars - - MariaDB Books - - - - - - - - - - - - - - - - - - - - - - - - - - There is an ever-growing library of MariaDB books available to help you get the most out of MariaDB. See the MariaDB Books page for details and links: - https://mariadb.com/kb/en/mariadb/books/ - - User Feedback plugin - - - - - - - - - - - - - - - - - - - - - - - MariaDB includes a User Feedback plugin. This plugin is disabled by default. If enabled, it submits basic, completely anonymous MariaDB usage information. This information is used by the developers to track trends in MariaDB usage to better guide development efforts. If you would like to help make MariaDB better, please add "feedback=ON" to your my.cnf or my.ini file! See http://mariadb.com/kb/en/user-feedback-plugin for more information. - - Quality - - - - - - - - - - - - - - - - - - - - - - - - - - - - - The project always strives for quality, but in reality, nothing is perfect. Please take time to report any issues you encounter at: - http://mariadb.org/jira - - Support MariaDB - - - - - - - - - - - - - - - - - - - - - - - - - If you would like to contribute to the MariaDB Foundation, please see the "contributing" and "donations" pages. We also have merchandise available in a cafepress store. All proceeds go to support the MariaDB Foundation. - https://mariadb.com/kb/en/contributing - https://mariadb.org/donate/ We hope you enjoy MariaDB! -- MariaDB Website - http://mariadb.org Twitter - http://twitter.com/mariadbfdn - http://twitter.com/mariadb Google+ - http://google.com/+mariadb Facebook - http://fb.com/MariaDB.dbms Knowledge Base - http://mariadb.com/kb

1 0

[Maria-discuss] Ubuntu 16.04, Accidentally installed mariadb-server on top of mysql-server
by rich.greder＠hushmail.com 29 May '17

29 May '17

Hello, Guys, I did a bad one. I only had one server remaining that still had mysql installed and thinking I was logged into my laptop's console, I executed apt-get install mariadb-server. I wasn't on my laptop. I was logged into a virtual hosting server and knocked eight sites offline including my wiki of all my HOWTO documents ironically, describing how to install mariadb-server on various platforms. I didn't notice anything was amiss until I saw a comment that it was uninstalling mysql (which I knew wasn't on my laptop) I was able to log in using my old root password, but all my databases were gone. Is there any hope for recovering the databases as they were earlier today? Rich

2 3

[Maria-discuss] thank you for getting MyRocks into MariaDB 10.2
by MARK CALLAGHAN 26 May '17

26 May '17

I look forward to trying it out. -- Mark Callaghan mdcallag(a)gmail.com

5 8

[Maria-discuss] MariaDB 10.2.6 Delay slave question?
by Ljr Yang 24 May '17

24 May '17

HI, Today, we upgrade a group MariaDB 10.1.22 to MariaDB 10.2.6, after upgade, start delay slave : change master to master_delay=86400 start slave and execute show all slaves status , Seconds_Behind_Master continue grow and Exec_Master_Log_Pos not change, please help confirm what cause lead this ? thanks. Slave_IO_Running: Yes Slave_SQL_Running: Yes * Exec_Master_Log_Pos: 367* Seconds_Behind_Master: 116072 Gtid_IO_Pos: 0-227-72846307321 Parallel_Mode: conservative SQL_Delay: 86400 SQL_Remaining_Delay: 83164 Slave_SQL_Running_State: Waiting until MASTER_DELAY seconds after master executed event after 5 minutes, Slave_IO_Running: Yes Slave_SQL_Running: Yes * Exec_Master_Log_Pos: 367* Seconds_Behind_Master: 116550 Gtid_IO_Pos: 0-227-72846546131 Parallel_Mode: conservative SQL_Delay: 86400 SQL_Remaining_Delay: 82686 Slave_SQL_Running_State: Waiting until MASTER_DELAY seconds after master executed event

1 0

[Maria-discuss] MariaDB 10.2.6 Stable (GA) now available
by Daniel Bartholomew 23 May '17

23 May '17

The MariaDB project is pleased to announce the immediate availability of MariaDB 10.2.6 Stable (GA). This is the first stable release in the MariaDB 10.2 series. MariaDB 10.0.31 and MariaDB Connector/ODBC 2.0.15 have also been released. See the Release Notes and Changelogs for details. - - Links - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - MariaDB 10.2.6 - Release Notes: https://mariadb.com/kb/en/mdb-1026-rn/ - Changelog: https://mariadb.com/kb/en/mdb-1026-cl/ - Downloads: https://downloads.mariadb.org/mariadb/10.2.6 About MariaDB 10.2: - https://mariadb.com/kb/en/what-is-mariadb-102/ APT and YUM Repository Configuration Generator: - https://downloads.mariadb.org/mariadb/repositories/ MariaDB 10.0.31 - Release Notes: https://mariadb.com/kb/en/mdb-10031-rn/ - Changelog: https://mariadb.com/kb/en/mdb-10031-cl/ - Downloads: https://downloads.mariadb.org/mariadb/10.0.31 About MariaDB 10.0: - https://mariadb.com/kb/en/what-is-mariadb-100/ APT and YUM Repository Configuration Generator: - https://downloads.mariadb.org/mariadb/repositories/ MariaDB Connector/ODBC 2.0.15 - Release Notes: https://mariadb.com/kb/en/mco-2015-rn/ - Changelog: https://mariadb.com/kb/en/mco-2015-cl/ - Downloads: https://downloads.mariadb.org/connector-odbc/2.0.15/ About MariaDB Connector/ODBC: - https://mariadb.com/kb/en/about-mariadb-connector-odbc/ - - MariaDB Webinars - - - - - - - - - - - - - - - - - - - - - - - - - There are several upcoming MariaDB-focused webinars and many previously held ones available to watch on an on-demand basis at: - https://mariadb.com/news-events/webinars - - MariaDB Books - - - - - - - - - - - - - - - - - - - - - - - - - - There is an ever-growing library of MariaDB books available to help you get the most out of MariaDB. See the MariaDB Books page for details and links: - https://mariadb.com/kb/en/mariadb/books/ - - User Feedback plugin - - - - - - - - - - - - - - - - - - - - - - - MariaDB includes a User Feedback plugin. This plugin is disabled by default. If enabled, it submits basic, completely anonymous MariaDB usage information. This information is used by the developers to track trends in MariaDB usage to better guide development efforts. If you would like to help make MariaDB better, please add "feedback=ON" to your my.cnf or my.ini file! See http://mariadb.com/kb/en/user-feedback-plugin for more information. - - Quality - - - - - - - - - - - - - - - - - - - - - - - - - - - - - The project always strives for quality, but in reality, nothing is perfect. Please take time to report any issues you encounter at: - http://mariadb.org/jira - - Support MariaDB - - - - - - - - - - - - - - - - - - - - - - - - - If you would like to contribute to the MariaDB Foundation, please see the "contributing" and "donations" pages. We also have merchandise available in a cafepress store. All proceeds go to support the MariaDB Foundation. - https://mariadb.com/kb/en/contributing - https://mariadb.org/donate/ We hope you enjoy MariaDB! -- MariaDB Website - http://mariadb.org Twitter - http://twitter.com/mariadbfdn - http://twitter.com/mariadb Google+ - http://google.com/+mariadb Facebook - http://fb.com/MariaDB.dbms Knowledge Base - http://mariadb.com/kb

1 0