April 2017 - discuss - lists.mariadb.org

[Maria-discuss] Help explain what a SQL line was INTENDING to do?
by David Karr 17 Apr '17

17 Apr '17

I've used Mariadb for a couple of small projects. I prefer to automate as much of the initial creation and config as possible, using SQL scripts executed from JDBC. I can at least do this for the table creation and population. I'm taking over an existing small project that is organized somewhat funky. There are some scripts that are apparently intended to initialize the database, but I'm seeing one line in one of those scripts that I don't understand, and I don't think is valid. I would appreciate some help explaining what you think this line MIGHT be intending to do: ----------------- ALTER TABLE `project` add column (proj_typ_id TINYINT(2) NOT NULL DEFAULT 1, parent_proj_id int(11, has_children BOOLEAN); ----------------- The first problem is that first paren. There's no matching right paren, so I can't imagine this can succeed. Second, I don't understand much past that first comma. Is this intending to add TWO columns, the second being "parent_proj_id"? Assuming that's the case, what exactly does "int(11, has_children BOOLEAN)" mean? I can see that it's declaring an 11-digit int, but I don't understand the "has_children" part.

2 2

Re: [Maria-discuss] "Error: MySQL server has gone away" issue while running mariadb database
by Daniel Black 12 Apr '17

12 Apr '17

On 11/04/17 15:15, Geunsik Lim wrote: > For reference, We are running web-based front service > with nodejs package + pm2 (http://pm2.keymetrics.io/) on Ubuntu 16.04.1 > server. > > It seems that this issue results from the too big packet size and too > long waiting time > between mariadb server and our nodejs-based web-service. its probably one or the other but not both.. MySQL document is still applicable https://dev.mysql.com/doc/refman/5.7/en/gone-away.html

2 4

[Maria-discuss] "Error: MySQL server has gone away" issue while running mariadb database
by Geunsik Lim 11 Apr '17

11 Apr '17

Recently, I have often been getting the "Error:MySQL server has gone away" message after I enabled the multiple-user access in front web service. Here is logs. . . . Upper Omission . . . 2017-04-10 21:15:22.881 [INFO][socketio.js] socket disconnect 2017-04-10 21:15:23.858 [INFO][socketio.js] socket connection 2017-04-10 21:36:07.635 [INFO][socketio.js] socket disconnect 2017-04-10 21:36:16.635 [INFO][socketio.js] socket disconnect 2017-04-10 21:46:12.254 [INFO][socketio.js] socket disconnect 2017-04-10 21:46:12.255 [INFO][socketio.js] socket disconnect events.js:160 throw er; // Unhandled 'error' event ^ Error: MySQL server has gone away at Error (native) At this time, I have checked the status of mariadb server. Please, see a time stampe information from the below mariadb's status. As we can see, the mariadb server was not stopped or not restarted. root@mytizen:~# systemctl status mysql * mysql.service - LSB: Start and stop the mysql database server daemon Loaded: loaded (/etc/init.d/mysql; bad; vendor preset: enabled) Active: active (running) since 2017-04-10 15:35:34 KST; 15h ago Docs: man:systemd-sysv-generator(8) Process: 1982 ExecStart=/etc/init.d/mysql start (code=exited, status=0/SUCCESS) Tasks: 28 Memory: 131.6M CPU: 29.563s CGroup: /system.slice/mysql.service ├─2544 /bin/bash /usr/bin/mysqld_safe ├─2691 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=my └─2692 logger -t mysqld -p daemon error 4월 10 15:35:31 mytizen mysqld[2692]: 170410 15:35:31 [Note] InnoDB: 128 rollback segment(s) are active. 4월 10 15:35:31 mytizen mysqld[2692]: 170410 15:35:31 [Note] InnoDB: Waiting for purge to start 4월 10 15:35:31 mytizen mysqld[2692]: 170410 15:35:31 [Note] InnoDB: Percona XtraDB (http://www.percona.com) 5.6.34-79 4월 10 15:35:31 mytizen mysqld[2692]: 170410 15:35:31 [Note] Plugin 'FEEDBACK' is disabled. 4월 10 15:35:31 mytizen mysqld[2692]: 170410 15:35:31 [Note] Server socket created on IP: '127.0.0.1'. 4월 10 15:35:33 mytizen mysqld[2692]: 170410 15:35:33 [Note] /usr/sbin/mysqld: ready for connections. 4월 10 15:35:33 mytizen mysqld[2692]: Version: '10.0.29-MariaDB-0ubuntu0.16.04.1' socket: '/var/run/mysqld/mysqld.sock 4월 10 15:35:34 mytizen mysql[1982]: ...done. 4월 10 15:35:34 mytizen systemd[1]: Started LSB: Start and stop the mysql database server daemon. 4월 10 15:35:34 mytizen mysql[1982]: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO) root@mytizen:~# root@mytizen:~# ------------------- root@mytizen:~# ls -al /var/run/mysqld/mysqld.sock srwxrwxrwx 1 mysql mysql 0 4 10 15:35 /var/run/mysqld/mysqld.sock After digging the operation functions from the mariadb source ( https://github.com/MariaDB/server), I could get some hints where the issue was generated. For example, a) storage/spider/spd_trx.cc (!conn->disable_reconnect) https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar… b) storage/cassandra/ha_cassandra.cc Anyone who experience a similar issue such as "Error: MySQL server has gone away" message? -- http://leemgs.fedorapeople.org Don't try to avoid pain if you fail. If you decided to face the challenges in life, you can gain a lot by giving your best. Cheolsang Jeong's Book & life --

1 1

[Maria-discuss] Page Compression in production and backup strategies
by Tim Kersten 10 Apr '17

10 Apr '17

Hi folks, Does anyone here use page compression ( https://mariadb.com/kb/en/mariadb/compression/ ) in production? (Or know folks who do?) What prompted my question was seeing that XtraBackup is not supported when using page compression. Given the popularity of the XtraBackup, I'm left wondering if there's some other backup strategy people use (especially since it's also used to sync and provision slaves), or if page compression is simply not used by folks who have master/slave setups, or perhaps don't require backups? Searching has not been helpful in my finding the answer, other than a pull request ( https://github.com/percona/percona-xtrabackup/pull/200 ) by Jan Lindstrom from last year. Hoping you can help, best regards, Tim

3 2

[Maria-discuss] Maraidb 10.1, Dependent Subqueries, much slower than 5.5
by Sales 08 Apr '17

08 Apr '17

We are noticing many dependent subqueries not working as well as they did on Mariadb 5.5, we’re having to add a force index. Relatively simple example: explain update Product_Master pm set Total_Inventory = (select sum(ifnull(pi.Quantity_On_Hand, pi.Other_Quantity)) from Product_Inventory_Summed pi where pi.Product_ID = pm.Product_ID group by pi.Product_ID); +------+--------------------+-------+-------+---------------+---------+---------+------+---------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+--------------------+-------+-------+---------------+---------+---------+------+---------+-------------+ | 1 | PRIMARY | pm | index | NULL | PRIMARY | 22 | NULL | 5001829 | | | 2 | DEPENDENT SUBQUERY | pi | ALL | PRIMARY | NULL | NULL | NULL | 1 | Using where | +------+--------------------+-------+-------+---------------+---------+---------+------+---------+——————+ This query runs in a couple of minutes on 5.5, runs for many many hours on 10.1 By adding the force index, it is back to “normal”. explain update Product_Master pm set Total_Inventory = (select sum(ifnull(pi.Quantity_On_Hand, pi.Other_Quantity)) from Product_Inventory_Summed pi force index (PRIMARY) where pi.Product_ID = pm.Product_ID group by pi.Product_ID); +------+--------------------+-------+-------+---------------+---------+---------+---------------------+---------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+--------------------+-------+-------+---------------+---------+---------+---------------------+---------+-------+ | 1 | PRIMARY | pm | index | NULL | PRIMARY | 22 | NULL | 5001829 | | | 2 | DEPENDENT SUBQUERY | pi | ref | PRIMARY | PRIMARY | 22 | feeds.pm.Product_ID | 1 | | +------+--------------------+-------+-------+---------------+---------+---------+---------------------+---------+———+ We are using persistent statistics on both tables, so, it knows there are a lot of rows in each. I don’t see any optimizer switches that are turned off. Any hints as to why or is this a current limitation of 10.1? Steve

2 3

Re: [Maria-discuss] mysqladmin - flush failed: Unknown error
by Daniel Black 06 Apr '17

06 Apr '17

On 30/03/17 20:46, Matthew Pash wrote: > Morning Daniel, > > Thanks for your reply, I've been doing some more testing. The logrotate > script (below) currently runs fine when executed manually (sudo > logrotate -v -f /etc/logrotate.d/mysql) but fails when run overnight by > cron. > > /var/log/mysqld.log { > compress > daily > missingok > notifempty > rotate 3 > postrotate > if test -x /usr/bin/mysqladmin && /usr/bin/mysqladmin ping &>/dev/null > then > /usr/bin/mysqladmin flush-logs > fi > endscript > } > > I tweaked it last night to capture the output of the mysqladmin command > and it did this: > > /usr/bin/mysqladmin: connect to server at 'localhost' failed > error: 'Access denied for user 'root'@'localhost' (using password: NO)' > > Which makes me think the problem is more about cron than MariaDB. No, MariaDB doesn't have a passwordless root user that mysqladmin needs to issue the flush-logs command. I suspect that there was originally a root user with socket authentication this system. Even after fixing this the mysqld may not be able to create new logs in /var/log/. I'd normally expect /var/log/mysql to be owned by the mysql user and its configuration to log the error to a file in this directory. > I've > added some extra information gathering, so we'll see how it runs tonight. > > > On 27 March 2017 at 22:34, Daniel Black <daniel.black(a)au1.ibm.com > <mailto:daniel.black@au1.ibm.com>> wrote: > > I think you'll find that the mysql user don't have permissions to create > a new file in /var/log/. As such logrotate renames the file which is > still open by mysqld. As such it is now writing to mysqld.log.1. > > On 28/03/17 00:13, Matthew Pash wrote: > > Hi, > > > > We've got some database servers running MariaDB 10.1.22, and are having > > issues with logrotate. Specifically, after it runs, it leaves a 0 byte > > file and continues writing log data to mysqld.log.1 > > > > Manually running logrotate in verbose mode shows this error from > > mysqladmin when it tries to run -flush_logs: > > > > /usr/bin/mysqladmin: flush failed; error: 'Unknown error' > > error: error running non-shared postrotate script for > > /var/log/mysqld.log of '/var/log/mysqld.log ' > > set default create context > > What does the logrotate script look like? It really should be a "shared" > (in a logrotate way) script. > > > > > Has anyone seen this before? It doesn't look like a permissions error - > > the logs are created fine if you run systemctl restart mariadb. > > Is the log actually writing to /var/log/mysqld.log using systemd? It > normally writes to the journal because with systemd redirects stderr > there > > > From lsof, it looks like /var/log/mysqld.log is being written to by the > mysql process itself: > $ sudo lsof | grep mysqld.log > mysqld 38803 mysql 1w REG 8,3 > 31781 537216904 /var/log/mysqld.log.1 > mysqld 38803 mysql 2w REG 8,3 > 31781 537216904 /var/log/mysqld.log.1 > ... > > > > > > Thanks, > > ~Matt > > > > -- > > *Matthew* > > Senior Systems Administrator, IT Services > > University of Bristol > > > > > > _______________________________________________ > > Mailing list: https://launchpad.net/~maria-discuss > <https://launchpad.net/%7Emaria-discuss> > > Post to : maria-discuss(a)lists.launchpad.net > <mailto:maria-discuss@lists.launchpad.net> > > Unsubscribe : https://launchpad.net/~maria-discuss > <https://launchpad.net/%7Emaria-discuss> > > More help : https://help.launchpad.net/ListHelp > <https://help.launchpad.net/ListHelp> > > > > > > > -- > *Matthew Pash* > Senior Systems Administrator, IT Services > University of Bristol

2 1

[Maria-discuss] MariaDB 10.2.5 RC now available
by Daniel Bartholomew 05 Apr '17

05 Apr '17

The MariaDB project is pleased to announce the immediate availability of MariaDB 10.2.5 Release Candidate (RC). See the Release Notes and Changelog for details. - - Links - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - MariaDB 10.2.5 - Release Notes: https://mariadb.com/kb/en/mdb-1025-rn/ - Changelog: https://mariadb.com/kb/en/mdb-1025-cl/ - Downloads: https://downloads.mariadb.org/mariadb/10.2.5 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 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] NOT AGAIN PLEASE
by Federico Razzoli 05 Apr '17

05 Apr '17

Periodically this list becomes an early 90's fashion BBS. For days. It is very interesting to discuss how every single human being except one is an idiot who don't reply messages correctly to emails but 1- After some years, if you didn't convince anyone, maybe you are not totally right. 2- It is a bit off topic here. Maybe setup a list like use-reply-correctly-or-die(a)lists.launchpad.net and use this list to talk about MariaDB? 3- Yes, I wrote the subject in uppercase and killing me is still illegal. What a nasty world. Thanks. Federico -------------------------------------------- Mer 5/4/17, Reindl Harald <h.reindl(a)thelounge.net> ha scritto: Oggetto: Re: [Maria-discuss] searching for etc/my.cnf and CMAKE_INSTALL_PREFIX A: "Maria Discuss" <maria-discuss(a)lists.launchpad.net> Data: Mercoledì 5 Aprile 2017, 19:23 Am 05.04.2017 um 19:14 schrieb MARK CALLAGHAN: > Why do you need to shout about this? why don't you bring ANY specific reasoining for your sloppy "Maybe someone should edit the too strong claims" - anyways - if someone is too dumb to respond just to the list instead reply-all and convert plaintext mails in the quote to HTML all is said.... > On Wed, Apr 5, 2017 at 10:04 AM, Reindl Harald <h.reindl(a)thelounge.net > <mailto:h.reindl@thelounge.net>> wrote: > > > > Am 05.04.2017 um 18:57 schrieb MARK CALLAGHAN: > > It is different from upstream MySQL in many ways as that page > explains. > > > bla - if it woul dbe just a recompile from the mysql sources it > would not be a fork - in 99.9% of all cases IT IS a > drop-in-replacement - if you have REAL issues name them - period > > On Wed, Apr 5, 2017 at 8:03 AM, Reindl Harald > <h.reindl(a)thelounge.net <mailto:h.reindl@thelounge.net> > <mailto:h.reindl@thelounge.net <mailto:h.reindl@thelounge.net>>> > wrote: > > and why? > > it is and where it isn't unintentional report it and help to > get it > fixed, that said from a admin which dozens of MariaDB > servers where > the datadir was originally created on Windows under MySQL > 3.x, got > moved in prduction for some years to MacOS, later to Linux > and now > is running MariaDB 10.1 just fine with the same datadirs and the > help of "mysql_upgrade" over the years > > > Am 05.04.2017 um 16:50 schrieb MARK CALLAGHAN: > > Maybe someone should edit the too strong claims > on > https://mariadb.com/kb/en/mariadb/mariadb-vs-mysql-compatibility/ > <https://mariadb.com/kb/en/mariadb/mariadb-vs-mysql-compatibility/> > > <https://mariadb.com/kb/en/mariadb/mariadb-vs-mysql-compatibility/ > <https://mariadb.com/kb/en/mariadb/mariadb-vs-mysql-compatibility/>> > > In bold at the top of the page... > > > MariaDB is a binary drop in replacement for MySQL > > > On Tue, Apr 4, 2017 at 9:58 PM, Sergei Golubchik > <serg(a)mariadb.org <mailto:serg@mariadb.org> > <mailto:serg@mariadb.org <mailto:serg@mariadb.org>>> wrote: > > Hi, Mark! > > On Apr 04, Mark Callaghan wrote: > > With upstream (5.6 and 5.7) MySQL when I use cmake > > -DCMAKE_INSTALL_PREFIX=$X, then after I install > the build, > mysqld searches > > in $X/etc for my.cnf. That is not true for MariaDB > Server > 10.2. Why can't > > this match upstream? > > > It certainly can. It just doesn't. Because we merge > from 5.6 into > MariaDB feature by feature, and nobody has requested > this > particular > feature so far. At least I couldn't find it in Jira. > > Regards, > Sergei > Chief Architect MariaDB > and security(a)mariadb.org > <mailto:security@mariadb.org> <mailto:security@mariadb.org > <mailto:security@mariadb.org>> _______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss(a)lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp

2 1

[Maria-discuss] restore db from dump?
by l vic 05 Apr '17

05 Apr '17

Hello, I have "mydump.sql" with dump of all databases in cluster. I am trying to selectively restore one database only from the scenario below: Have created 2 databases: test1 and test2 with table "person": +-------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+-------+ | name | varchar(255) | YES | | NULL | | +-------+--------------+------+-----+---------+-------+ Insert name "one" into table "person" for both databases: insert into person values ('name'); Backup all data into "mysqldump.sql": mysqldump -P3306 -uroot -pdbpass --all-databases >mysqldump.sql drop database test2 (my mistake); and insert additional data into "test1": insert into person values ('name1'); insert into person values ('name2'); Now, i'd like to restore database test2 only: mysql -uroot -pdbpass -e "create database if not exists test2;" mysql -uroot -pdbpass test2 < mysqldump.sql Success !! test2 is restored with all the data, but when I'checked "test1" i only see one record "name" - two records that I've added after backup are lost It looks as mysql just restore all data in dump file, regardless of specifying the "target" database. Is that expected, or do i have some error in my backup/restore procedure? If this is expected, is there some comprehensive method to restore one database selectively? Thanks, vl

3 3

[Maria-discuss] searching for etc/my.cnf and CMAKE_INSTALL_PREFIX
by MARK CALLAGHAN 05 Apr '17

05 Apr '17

With upstream (5.6 and 5.7) MySQL when I use cmake -DCMAKE_INSTALL_PREFIX=$X, then after I install the build, mysqld searches in $X/etc for my.cnf. That is not true for MariaDB Server 10.2. Why can't this match upstream? -- Mark Callaghan mdcallag(a)gmail.com

3 8