- discuss - lists.mariadb.org

[Maria-discuss] innodb table single column index max length
by butler 18 Apr '17

18 Apr '17

we know the mysql have a limit that the max index length on a single column is 767.I want know how the 767 come from ? why not 799 or 800 etc.

2 2

[Maria-discuss] MariaDB 10.3.0 Alpha and 5.5.55 Stable now available
by Daniel Bartholomew 18 Apr '17

18 Apr '17

The MariaDB project is pleased to announce the immediate availability of MariaDB 10.3.0 Alpha and MariaDB 5.5.55 Stable (GA). See the Release Notes and Changelog for details. - - Links - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - MariaDB 10.3.0 - Release Notes: https://mariadb.com/kb/en/mdb-1030-rn/ - Changelog: https://mariadb.com/kb/en/mdb-1030-cl/ - Downloads: https://downloads.mariadb.org/mariadb/10.3.0 About MariaDB 10.3: - https://mariadb.com/kb/en/what-is-mariadb-103/ APT and YUM Repository Configuration Generator: - https://downloads.mariadb.org/mariadb/repositories/ MariaDB 5.5.55 - Release Notes: https://mariadb.com/kb/en/mdb-5555-rn/ - Changelog: https://mariadb.com/kb/en/mdb-5555-cl/ - Downloads: https://downloads.mariadb.org/mariadb/5.5.55 About MariaDB 5.5: - https://mariadb.com/kb/en/what-is-mariadb-55/ 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] logrotate
by Daniel Black 18 Apr '17

18 Apr '17

Quick proof of concept logrotate that hasn't really been changed in a while. The aim is to get this closer to a state for distro maintainers to use directly. By using a dedicated SQL user this shouldn't conflict with an existing user root user (which users always use despite the ability create other users with SUPER privs). As users will occasional change the password on the root without taking into account that logrotate typically uses the same user. Relying on users to update /root/.my.cnf is unreliable. Giving selinux permissions to allow logrotate read files under /root is also a little excessive. Using a dedicated mysqladmin.logrotate this won't conflict with existing mysqladmin group. Adds slow query log and general log to the logrotate script. Binary logs are deliberately omitted (MDEV-11610). https://mariadb.com/kb/en/mariadb/sql_error_log-plugin/ seems to have its own rotation) Proposed changes: https://github.com/MariaDB/server/compare/10.2...grooverdan:10.2-logrotate?… mysqladmin --local uses SET SESSION SQL_LOG_BIN=0 so I'll change its implementation to use FLUSH LOCAL ... which would preserve the need to use RELOAD only privs for logrotate. Thoughts? Difficulties? Suggestions? ref: https://jira.mariadb.org/browse/MDEV-11610 https://jira.mariadb.org/browse/MDEV-5662

3 4

[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