May 2018 - discuss - lists.mariadb.org

[Maria-discuss] why stdout? -> [Note] /usr/libexec/mysqld (mysqld 10.0.20-MariaDB) starting as process 115096
by Reindl Harald 05 Jul '20

05 Jul '20

Aug 6 12:24:24 testserver mysqld: 150806 12:24:24 [Note] /usr/libexec/mysqld (mysqld 10.0.20-MariaDB) starting as process 115096 ... why is this now stdout instead mysql logs? on systemd based distributions that spits now to the global syslog and finally on machines restarting 2 instances every hour for backup reasons it annoys until 10.0.19 or 10.0.20 that was not this way

5 13

[Maria-discuss] MariaDB-10.2.11 Long Time To Start
by Michael Caplan 15 Mar '19

15 Mar '19

Hi, I'm setting up a replication slave based off of MySQL 5.6. There are a large number of tables (400K). The MySQL 5.6 master takes around 10 minutes to start up, where the new MariaDB 10.2 slave takes close to 30 minutes. What is making the startup take soo long? In the below mysql log, you can see in the startup sequence between "Highest supported file format is Barracuda." and "InnoDB: 128 out of 128 rollback segments are active." the startup process takes the majority of its time: 2018-01-07 9:38:15 139980432365760 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins 2018-01-07 9:38:15 139980432365760 [Note] InnoDB: Uses event mutexes 2018-01-07 9:38:15 139980432365760 [Note] InnoDB: Compressed tables use zlib 1.2.8 2018-01-07 9:38:15 139980432365760 [Note] InnoDB: Using Linux native AIO 2018-01-07 9:38:15 139980432365760 [Note] InnoDB: Number of pools: 1 2018-01-07 9:38:15 139980432365760 [Note] InnoDB: Using SSE2 crc32 instructions 2018-01-07 9:38:15 139980432365760 [Note] InnoDB: Initializing buffer pool, total size = 54G, instances = 8, chunk size = 128M 2018-01-07 9:38:18 139980432365760 [Note] InnoDB: Completed initialization of buffer pool 2018-01-07 9:38:18 139918113195776 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority(). 2018-01-07 9:38:18 139980432365760 [Note] InnoDB: Highest supported file format is Barracuda. 2018-01-07 10:11:48 139980432365760 [Note] InnoDB: 128 out of 128 rollback segments are active. 2018-01-07 10:11:48 139980432365760 [Note] InnoDB: Creating shared tablespace for temporary tables 2018-01-07 10:11:48 139980432365760 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ... 2018-01-07 10:11:48 139980432365760 [Note] InnoDB: File './ibtmp1' size is now 12 MB. 2018-01-07 10:11:48 139980432365760 [Note] InnoDB: Waiting for purge to start 2018-01-07 10:11:48 139980432365760 [Note] InnoDB: 5.7.20 started; log sequence number 1394303471760 2018-01-07 10:11:48 139919461500672 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool 2018-01-07 10:11:48 139980432365760 [Note] Server socket created on IP: '::'. 2018-01-07 10:11:49 139980432365760 [Note] Reading of all Master_info entries succeded 2018-01-07 10:11:49 139980432365760 [Note] Added new Master_info '' to hash table 2018-01-07 10:11:49 139980432365760 [Note] /usr/sbin/mysqld: ready for connections. Version: '10.2.11-MariaDB-10.2.11+maria~xenial-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 mariadb.org binary distribution Any ideas why? I've included my my.conf file below. Thanks, Mike # Generated by Percona Configuration Wizard (http://tools.percona.com/) version REL5-20120208 # Configuration name usaddxprdsqlpm3 generated formcaplan(a)labnet.net at 2017-12-22 13:49:58 [mysql] # CLIENT # port = 3306 socket = /var/lib/mysql/mysql.sock [mysqladmin] # CLIENT # port = 3306 socket = /var/lib/mysql/mysql.sock [mysqld] # GENERAL # user = mysql default-storage-engine = InnoDB socket = /var/lib/mysql/mysql.sock pid-file = /var/lib/mysql/mysql.pid character-set-server = utf8 collation_server = utf8_general_ci performance_schema = 0 # MyISAM # key-buffer-size = 32M myisam-recover = FORCE,BACKUP # SAFETY # max-allowed-packet = 20M max-connect-errors = 1000000 skip-name-resolve sysdate-is-now = 1 innodb = FORCE # DATA STORAGE # datadir = /var/lib/mysql/ # BINARY LOGGING # log-bin = /var/lib/mysql/mysql-bin binlog_format = MIXED server-id = 3 expire-logs-days = 7 sync-binlog = 1 # REPLICATION # read-only = 1 skip-slave-start = 1 log-slave-updates = 1 relay-log = /var/lib/mysql/relay-bin slave-net-timeout = 60 sync-master-info = 1 sync-relay-log = 1 sync-relay-log-info = 1 # CACHES AND LIMITS # tmp-table-size = 32M max-heap-table-size = 32M query-cache-type = 0 query-cache-size = 0 max-connections = 500 thread-cache-size = 50 open-files-limit = 65535 table-definition-cache = 4096 table-open-cache = 10240 # INNODB # innodb-flush-method = O_DIRECT innodb-log-files-in-group = 2 innodb-log-file-size = 512M innodb-flush-log-at-trx-commit = 1 innodb-file-per-table = 1 innodb-buffer-pool-size = 54G # LOGGING # log-error = /var/lib/mysql/mysql-error.log log-queries-not-using-indexes = 0 slow-query-log = 0 slow-query-log-file = /var/lib/mysql/mysql-slow.log [mariadb] plugin-load-add=file_key_management file_key_management_filename=/var/lib/mysql/keys.enc file_key_management_filekey=FILE:/var/lib/mysql/mysql.key file_key_management_encryption_algorithm=AES_CTR encrypt_binlog=0

3 19

[Maria-discuss] Python 3 Handler Socket
by Roberto Spadim 28 May '18

28 May '18

Hi guys! Any link to a working handler socket python module? pyhs is old and working only with python2, we need to update the C lib or use plain python with python3 (i did some changes, don't know if there's a running job on this) -- Roberto Spadim

2 4

[Maria-discuss] MariaDB Compile Size
by Tom Jay 21 May '18

21 May '18

Hello, Has anyone got any idea how to reduce the MariaDB install size, when compiling from source? I've had multiple attempts, all with different options such as 'WITHOUT_EXAMPLE_STORAGE_ENGINE=1' and 'FEATURE_SET:STRING=large', for example, but with no effect. The installation size is usually around 2.1 GB, whereas the official Arch package, which also compiles from source, is about 231.78 MB. The official Arch build script is here [https://git.archlinux.org/svntogit/packages.git/tree/trunk/PKGBUILD?h=packa…], and the only difference I can notice is that they delete a lot of stuff afterwards. Is there any reference for stuff that can be deleted after MariaDB has been installed? Thanks.

4 5

[Maria-discuss] R: MariaDB Compile Size
by Federico Razzoli 19 May '18

19 May '18

You maybe interested in this article: http://datacharmer.blogspot.nl/2016/02/mysql-sandboxes-in-docker.html?m=1 Federico -------------------------------------------- Sab 19/5/18, Tom Jay <tom_jay(a)hotmail.com> ha scritto: Oggetto: [Maria-discuss] MariaDB Compile Size A: "maria-discuss(a)lists.launchpad.net" <maria-discuss(a)lists.launchpad.net> Data: Sabato 19 maggio 2018, 07:33 Hello, Has anyone got any idea how to reduce the MariaDB install size, when compiling from source? I've had multiple attempts, all with different options such as 'WITHOUT_EXAMPLE_STORAGE_ENGINE=1' and 'FEATURE_SET:STRING=large', for example, but with no effect. The installation size is usually around 2.1 GB, whereas the official Arch package, which also compiles from source, is about 231.78 MB. The official Arch build script is here [https://git.archlinux.org/svntogit/packages.git/tree/trunk/PKGBUILD?h=packa…], and the only difference I can notice is that they delete a lot of stuff afterwards. Is there any reference for stuff that can be deleted after MariaDB has been installed? Thanks. _______________________________________________ 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

1 0

[Maria-discuss] Query ambiguity
by Dev C 17 May '18

17 May '18

Hello I have query ambiguity as attached. Please help me on how to solve that.

4 6

[Maria-discuss] Interval in MYsql query
by Dev C 16 May '18

16 May '18

Hello If I use below query (metrics_5.created_at)>'5/10/2018'- 28 ) it gives me some result but if i use below query (metrics_5.created_at)>'5/10/2018'- INTERVAL 28 DAY ) It does not give me any result, any one know why ?

3 2

[Maria-discuss] MySQL Quries and Error
by Dev C 16 May '18

16 May '18

Below is my queries SELECT AdData4w.`Campaign Name`, AdData4w.`Adgroup Name`, AdData4w.`4w Clicks`, AdData1w.`1w Clicks`, AdData4w.`4w Orders`, AdData1w.`1w Orders`, AdData4w.`4w Spend`, AdData1w.`1w Spend`, AdData4w.`4w Sales`, AdData1w.`1w Sales`, AdData4w.`4w Conv Rate`, AdData1w.`1w Conv Rate`, AdData4w.`4w ACOS`, AdData1w.`1w ACOS`, AdData4w.`4w CPC`, AdData1w.`1w CPC`,AdData4w.`4w Bid`, AdData1w.`1w Bid` FROM AdData4w INNER JOIN AdData1w ON (AdData4w.`Adgroup Name` = AdData1w.`Adgroup Name`) AND (AdData4w.`Campaign Name` = AdData1w.`Campaign Name`) GROUP BY AdData4w.`Campaign Name`, AdData4w.`Adgroup Name`, AdData4w.`4w Clicks`, AdData1w.`1w Clicks`, AdData4w.`4w Orders`, AdData1w.`1w Orders`, AdData4w.`4w Spend`, AdData1w.`1w Spend`, AdData4w.`4w Sales`, AdData1w.`1w Sales`, AdData4w.`4w Conv Rate`, AdData1w.`1w Conv Rate`, AdData4w.`4w ACOS`, AdData1w.`1w ACOS`, AdData4w.`4w CPC`, AdData1w.`1w CPC`, AdData4w.`4w Bid`, AdData1w.`1w Bid` From (SELECT campaigns_5.name AS `Campaign Name`, adgroups_5.name AS `Adgroup Name`, Sum(metrics_5.clicks) AS `1w Clicks`, Sum(metrics_5.orders) AS `1w Orders`, Sum(metrics_5.costs) AS `1w Spend`, Sum(metrics_5.sales) AS `1w Sales`, If(Sum(clicks)=0,0,Sum(orders)/Sum(clicks)) AS `1w Conv Rate`, If(Sum(metrics_5.sales)=0,0,Sum(metrics_5.costs)/Sum(metrics_5.sales)) AS `1w ACOS`, IF (Sum(clicks)=0,0,Sum(costs)/Sum(clicks)) AS `1w CPC`, Avg(bid_info_5.value) AS `1w Bid` FROM ((adgroups_5 INNER JOIN campaigns_5 ON adgroups_5.campaignId = campaigns_5.campaignId) INNER JOIN metrics_5 ON adgroups_5.adGroupId = metrics_5.type_id) INNER JOIN bid_info_5 ON adgroups_5.adGroupId = bid_info_5.type_id WHERE (((metrics_5.created_at)>'5/10/2018'-7)) GROUP BY campaigns_5.name, adgroups_5.name, campaigns_5.user_market_id, RIGHT(campaigns_5.name,4) HAVING (((campaigns_5.user_market_id)=12) AND ((Right(campaigns_5.name,4))="PFUS") AND ((Sum(metrics_5.impressions))>0)) ORDER BY campaigns_5.name, adgroups_5.name) AS AdData1w INNER JOIN (SELECT campaigns_5.name AS `Campaign Name`, adgroups_5.name AS `Adgroup Name`, Sum(metrics_5.clicks) AS `4w Clicks`, Sum(metrics_5.orders) AS `4w Orders`, Sum(metrics_5.costs) AS `4w Spend`, Sum(metrics_5.sales) AS `4w Sales`, If(Sum(clicks)=0,0,Sum(orders)/Sum(clicks)) AS `4w Conv Rate`, If(Sum(metrics_5.sales)=0,0,Sum(metrics_5.costs)/Sum(metrics_5.sales)) AS `4w ACOS`, If(Sum(clicks)=0,0,Sum(costs)/Sum(clicks)) AS `4w CPC`, Avg(bid_info_5.value) AS `4w Bid` FROM ((adgroups_5 INNER JOIN campaigns_5 ON adgroups_5.campaignId = campaigns_5.campaignId) INNER JOIN metrics_5 ON adgroups_5.adGroupId = metrics_5.type_id) INNER JOIN bid_info_5 ON adgroups_5.adGroupId = bid_info_5.type_id WHERE (((metrics_5.created_at)>'5/10/2018'-28)) GROUP BY campaigns_5.name, adgroups_5.name, campaigns_5.user_market_id, RIGHT(campaigns_5.name,4) HAVING (((campaigns_5.user_market_id)=12) AND ((Right(campaigns_5.name,4))="PFUS") AND ((Sum(metrics_5.impressions))>0)) ORDER BY campaigns_5.name, adgroups_5.name) AS AdData4w it gives error on as below 4 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'From (SELECT campaigns_5.name AS `Campaign Name`, adgroups_5.name AS `Adgroup N' at line 4 Query12.sql 1 1 Please assist.

2 1

[Maria-discuss] [question] password input length
by Michal Schorm 16 May '18

16 May '18

Hello everyone, 1) I found out, that passwords longer than 79 characters aren't accepted via password prompt. Is that expected? Morie info and reproducer in RH bugzilla: https://bugzilla.redhat.com/show_bug.cgi?id=1566179 -- 2) Another password length cap at 213 characters can be found when using sha256_password authentification plugin. Is that expected too? Morie info and reproducer in RH bugzilla: https://bugzilla.redhat.com/show_bug.cgi?id=1566219 -- 3) Different theme: why aren't tests for mariadb-connector-odbc, that can be found on GitHub, packged to the source tarball ? -- Michal Schorm Associate Software Engineer Core Services - Databases Team Red Hat

2 2

[Maria-discuss] Installing MariaDB Alongside MySQL
by Muhammad Bashir Al-Noimi 15 May '18

15 May '18

4 27