- discuss - lists.mariadb.org

[Maria-discuss] Maxscale not changing to other viable masters
by Rodrigo Severo - Fábrica 15 Apr '20

15 Apr '20

Hi, I'm not sure this is the right mailing list to talk about MaxScale. Please let me know if there is a better place. My problem is that MaxScale is not using another server as MaxScale's master when the previous master get inaccessible. More details: I'm running MaxScale 2.4.5 and my MariaDB servers are a mixture of 10.3.21 and 10.3.22. I have three MariaDB servers with a star topology replication GTID based setup: each server is a slave of the other 2. Let's call them S1, S2, and S3. When starting the whole cluster MaxScale chooses S1 as it's master (the one where MaxScale will send the change data INSERT/UPDATE/DELETE) as S1 is the first one listed in MaxScale's config. When I stop S1 I expect MaxScale to choose another server as it's new master but instead I get: 28271 2020-04-15 11:54:32 error : Monitor was unable to connect to server S1 : 'Can't connect to MySQL server on 'S1' (115)' 28272 2020-04-15 11:54:32 warning: [mariadbmon] 'S2' is a better master candidate than the current master 'S1'. Master will change when 'S1' is no longer a valid master. but S2 is never promoted to MaxScale's master, even after ((monitor_interval + backend_connect_timeout) * failcount) seconds as mentioned in https://mariadb.com/kb/en/mariadb-maxscale-24-mariadb-monitor/#failcount As I have default values for monitor_interval, backend_connect_timeout and failcount - 2s, 3s, 5 retries respectively - I would expect that in 25 seconds I would have a new master selected but, unfortunately, MaxScale gets stuck in the above situation until I restart the S1 server, when S1 resumes it's hole as MaxScale's master. I wonder if my problem is related to item 2 in https://mariadb.com/kb/en/mariadb-maxscale-24-mariadb-monitor/#master-selec… which reads: 2. It has been down for more than failcount monitor passes and has no running slaves. Running slaves behind a downed relay count. Does the fact that the slaves of S1 (S2 and S3) are running make MaxScale consider S1 still valid despite not being accessible at all? More important: how can I make MaxScale change it's chosen master in a star topology replication when the current MaxScale master stops? Regards, Rodrigo Severo

1 1

[Maria-discuss] Some records come out after some time
by Armando B. 13 Apr '20

13 Apr '20

Hi all, i have a MariaDB Server (*10.1.35*) with an InnoDB and a table with an Autoincrement ID field and some other data (no foreign keys but only index). This table have few records (~10000) that are increased with a csv import each some weeks. I saw that sometimes, after import process, some records are hidden and come out after some time (also 40 min.) could you help me ? Armando -- Themonospot http://github.com/armando-basile/themonospot/ Monosim http://github.com/armando-basile/monosim/ Comex-Project http://github.com/armando-basile/comex-project/ ViviVasto app https://play.google.com/store/apps/details?id=com.armandobasile.vivivasto vdr-getchannels - https://github.com/armando-basile/vdr-getchannels vdr-scripts - https://github.com/armando-basile/vdr-scripts

2 4

[Maria-discuss] transactional sql update patch system ?
by launchpad＠rupat.de 28 Feb '20

28 Feb '20

Hello, we want, that a sql patch file can ONLY be applied if all commands in it can applied successful ! so our first try was: START TRANSACTION; statement1 statement2 statement3 statement4 .... COMMIT; problem: this is not working because an statement can also create table, which is an ddl command and which do an commit ! (https://mariadb.com/kb/en/start-transaction/#autocommit) .... DDL statements (CREATE, ALTER, DROP) and administrative statements (FLUSH, RESET, OPTIMIZE, ANALYZE, CHECK, REPAIR, CACHE INDEX), and LOAD DATA INFILE, cause an implicit COMMIT and start a new transaction. the result is that all before an failing create table statement will be applied ! so this is no solution. so how can i garantee that only whole files can be applied ?! Here is an sample: CREATE TABLE test (number int); START TRANSACTION; INSERT INTO test (number) VALUES (1); COMMIT; START TRANSACTION; INSERT INTO test (number) VALUES (2); COMMIT; START TRANSACTION; INSERT INTO test (number) VALUES (3); CREATE TABLE test (thiswillfail int); COMMIT; And the logs from mariadb: 8 Connect root@localhost as anonymous on arcus 8 Query CREATE TABLE test (number int) 8 Query START TRANSACTION 8 Query INSERT INTO test (number) VALUES (1) 8 Query COMMIT 8 Query START TRANSACTION 8 Query INSERT INTO test (number) VALUES (2) 8 Query COMMIT 8 Query START TRANSACTION 8 Query INSERT INTO test (number) VALUES (3) 8 Query CREATE TABLE test (thiswillfail int) 8 Quit And with another connection the table test SELECT * FROM test; 1 2 3 So we can see value 3 were persistet although there was no COMMIT !!!! because of autocommit when do an create table. But i want that nothing is applied from my sql file when something fail ! Greetings Robert

3 3

[Maria-discuss] Re-configuring BIND DNS Servers for CentOS Web Panel Web Hosting Control Panel on Amazon AWS Cloud
by Turritopsis Dohrnii Teo En Ming 27 Feb '20

27 Feb '20

Subject: Re-configuring BIND DNS Servers for CentOS Web Panel Web Hosting Control Panel on Amazon AWS Cloud Author: Mr. Turritopsis Dohrnii Teo En Ming, Singapore Date: 27 Feb 2020 Thursday Rationale for Re-configuration of BIND DNS Servers for CentOS Web Panel ======================================================================= I have originally followed the approach for cPanel where there are 2 DNS-ONLY servers and one or more cPanel webservers. However, CentOS Web Panel implements DNS Clusters differently. Hence I have to re-configure BIND DNS Servers for CentOS Web Panel web hosting control panel. PREREQUISITES ============= Part 1 of the series: Mr. Teo En Ming's Guide to Deploying CentOS Web Panel (CWP) Web Hosting Control Panel on Amazon AWS Cloud Redundant Blogger and Wordpress blog links: [1] https://tdtemcerts.blogspot.com/2020/02/mr-teo-en-mings-guide-to-deploying.… [2] https://tdtemcerts.wordpress.com/2020/02/23/mr-teo-en-mings-guide-to-deploy… Part 2 of the series: Setting Up Mail Server Operation for CentOS Web Panel Web Hosting Control Panel on Amazon AWS Cloud Redundant Blogger and Wordpress blog links: [1] https://tdtemcerts.blogspot.com/2020/02/setting-up-mail-server-operation-fo… [2] https://tdtemcerts.wordpress.com/2020/02/25/setting-up-mail-server-operatio… THIS guide is Part 3 of the series. EXTREMELY DETAILED INSTRUCTIONS OF TEO EN MING'S GUIDE ====================================================== Login to Amazon AWS Console. Setting Up Secondary/Slave DNS Server ===================================== On the EC2 Dashboard, click Instances. Click Launch Instance. Search for centos in the AWS Markpetplace. Select CentOS 7 (x86_64) - with Updates HVM (free tier eligible). Click Continue. Select t2.micro (free tier eligible). Click Next: Configure Instance Details. Network: Teo En Ming VPC Subnet: Public subnet | us-east-2a Click Protect against accidental termination. Click Next: Add Storage Size (GiB): 8 Click Next: Add Tags Key = Name Value = slave Click Next: Configure Security Group Click Select an existing security group Select NameServers Click Review and Launch. Click Launch. Select Choose an existing key pair. Key pair name: cwp Click Launch Instances. Click Instances. Select slave, right click and select Networking > Manage IP Addresses. Click Allocate an elastic IP to this instance. Click Allocate. Click Associate this Elastic IP Address. Instance: slave Click Associate. IPv4 address of Secondary/Slave DNS server is 3.12.224.179 $ ssh -i cwp.pem centos(a)3.12.224.179 $ sudo passwd $ su - # yum -y update && yum -y install wget # hostnamectl set-hostname ns2.teo-en-ming.com # cd /usr/local/src && wget http://centos-webpanel.com/cwp-el7-latest && sh cwp-el7-latest Started installing CentOS Web Panel at 9:00 PM on 26 Feb 2020 Wed. Completed installing CentOS Web Panel at 9:05 PM on 26 Feb 2020 Wed. Total duration: 5 mins. ############################# # CWP Installed # ############################# Go to CentOS WebPanel Admin GUI at http://SERVER_IP:2030/ http://3.12.224.179:2030 SSL: https://3.12.224.179:2031 --------------------- Username: root Password: ssh server root password MySQL root Password: ######################################################### CentOS Web Panel MailServer Installer ######################################################### SSL Cert name (hostname): ns2.teo-en-ming.com SSL Cert file location /etc/pki/tls/ private|certs ######################################################### Visit for help: www.centos-webpanel.com Write down login details and press ENTER for server reboot! Please reboot the server! Reboot command: shutdown -r now # reboot REFERENCE ========= Guide: Slave DNS Server & Manager - DNS Cluster Link: https://wiki.centos-webpanel.com/slave-dns-server-manager-dns-cluster REFERENCE ========= Guide: Slave DNS Server & Manager download version Link: https://wiki.centos-webpanel.com/slave-dns-server-manager-download-version Login to the CentOS Web Panel Admin Panel on the Slave. >From the left menu, click on CWP Settings, then select Edit Settings. Admin Email: ceo(a)teo-en-ming-corp.com Check Activate NAT-ed network configuration. Click Save Changes. Create a New Account on the Secondary/Slave DNS Server ====================================================== >From the left menu, click User Accounts, then click New Account. Domain name: teo-en-ming.com Username: slave Package: default Click Create. Download Slave DNS Manager and upload it to public_html folder on the Secondary/Slave DNS Server ================================================================================================ ssh -i cwp.pem centos(a)3.12.224.179 su - cd /home/slave/public_html wget http://dl1.centos-webpanel.com/files/cwp/addons/cwp-slave_dns.zip unzip cwp-slave_dns.zip mv slave_dns/* . rm -f index.html Fix file permissions on the Slave DNS Server ============================================ chown -R slave.slave /home/slave/public_html/* MySQL: Create User and Database on the Slave DNS Server ======================================================= >From the left menu, click SQL Services, then click MySQL Manager. Click Create Database and User. New Database for user: slave Database Name: slave Click Create Database. Edit file /home/slave/public_html/inc/db_conn.php.sample and enter your database connection details # nano /home/slave/public_html/inc/db_conn.php.sample # mv inc/db_conn.php.sample inc/db_conn.php # mysql slave_slave < sql/slave_dns.sql # nano /etc/sudoers.d/slave slave ALL= NOPASSWD: /bin/systemctl start named slave ALL= NOPASSWD: /bin/systemctl stop named slave ALL= NOPASSWD: /bin/systemctl restart named slave ALL= NOPASSWD: /bin/systemctl reload named slave ALL= NOPASSWD: /bin/systemctl status named slave ALL= NOPASSWD: /bin/systemctl is-active named # touch /etc/named/slave.conf # chmod 771 /etc/named # usermod -a -G named slave # chown slave.named /etc/named/slave.conf # mkdir /var/named/slave # chown named.named /var/named/slave Edit File: /etc/named.conf and add this in options section before closing } masterfile-format text; Add after options{} where other include lines are specifed //Slave dns configuration include "/etc/named/slave.conf"; Now you can login to Slave DNS Manager GUI by using a domain link of the account you have created. Go to http://3.12.224.179/~slave/index.php?login Default login for Slave DNS Manager GUI admin/root Username: root Password: FX8QKxvQ * Please change the default password after the first login Adding WebServers to Slave DNS Manager ====================================== - On Slave DNS manager GUI create a new user for each server or use a single for all webservers if you plan to transfer accounts from one to another webserver. Click Add New User. Username: Password: Email: API Key: API Secret: Domain Limit: 1000000 Click Save. - On Master CentOS Web Panel WebServer go to DNS Functions -> Slave DNS Manager Buy CWPPro license first. It is only USD$1.49 compared to USD$20 for the license of cPanel. I can't afford to buy the license for cPanel, hence I have to settle for CentOS Web Panel. Then ssh into your Master CentOS Web Panel Webserver. # sh /scripts/update_cwp You have successfully upgraded to CWPpro. On the Master CentOS Web Panel, Go to DNS Functions -> Slave DNS Manager API Key: Secret Key: Base URL: http://3.12.224.179/~slave Master Server IP's: 3.21.30.127 Click Submit. Master CentOS Web Panel WebServer configuration =============================================== Edit File: /etc/named.conf and add this in options section before closing } //Slave dns configuration allow-transfer {3.12.224.179;}; allow-recursion {3.12.224.179;}; also-notify {3.12.224.179;}; masterfile-format text; # named-checkconf # systemctl restart named REFERENCE ========= Guide: How to setup DNS cluster on CWP 1 – Install DNS Manager Link: https://opentechy.com/how-to-setup-dns-cluster-on-cwp/ REFERENCE ========= Guide: How to setup DNS cluster on CWP 2 – Add webservers to DNS cluster Link: https://opentechy.com/how-to-setup-dns-cluster-on-cwp-2-add-webservers-to-d… CONFIGURING CUSTOM NAME SERVERS AT YOUR DOMAIN REGISTRAR ======================================================== Go to Advanced Features > Hostnames Host: ns1 IP Addresses: 3.21.30.127 Host: ns2 IP Addresses: 3.12.224.179 Nameservers: Using custom nameservers ns1.teo-en-ming.com ns2.teo-en-ming.com CONFIGURING NAME SERVERS ON THE MASTER CENTOS WEB PANEL WEBSERVER ================================================================== >From the left menu, click DNS Functions, then click Edit Nameservers IPs. Name Server 1: ns1.teo-en-ming.com 3.21.30.127 Name Server 2: ns2.teo-en-ming.com 3.12.224.179 Click Save changes. # systemctl restart named BIND DNS CONFIGURATION ON THE MASTER CENTOS WEB PANEL WEBSERVER =============================================================== File /etc/named.conf: // // named.conf // // Provided by Red Hat bind package to configure the ISC BIND named(8) DNS // server as a caching only nameserver (as a any DNS resolver only). // // See /usr/share/doc/bind*/sample/ for example named configuration files. // // See the BIND Administrator's Reference Manual (ARM) for details about the // configuration located in /usr/share/doc/bind-{version}/Bv9ARM.html options { listen-on port 53 { any; }; listen-on-v6 port 53 { ::1; }; directory "/var/named"; dump-file "/var/named/data/cache_dump.db"; statistics-file "/var/named/data/named_stats.txt"; memstatistics-file "/var/named/data/named_mem_stats.txt"; recursing-file "/var/named/data/named.recursing"; secroots-file "/var/named/data/named.secroots"; allow-query { any; }; /* - If you are building an AUTHORITATIVE DNS server, do NOT enable recursion. - If you are building a RECURSIVE (caching) DNS server, you need to enable recursion. - If your recursive DNS server has a public IP address, you MUST enable access control to limit queries to your legitimate users. Failing to do so will cause your server to become part of large scale DNS amplification attacks. Implementing BCP38 within your network would greatly reduce such attack surface */ // recursion no; dnssec-enable yes; dnssec-validation yes; /* Path to ISC DLV key */ bindkeys-file "/etc/named.root.key"; managed-keys-directory "/var/named/dynamic"; pid-file "/run/named/named.pid"; session-keyfile "/run/named/session.key"; //Slave dns configuration allow-transfer {3.12.224.179;}; allow-recursion {3.12.224.179;}; also-notify {3.12.224.179;}; masterfile-format text; }; logging { channel default_debug { file "data/named.run"; severity dynamic; }; }; zone "." IN { type hint; file "named.ca"; }; include "/etc/named.rfc1912.zones"; include "/etc/named.root.key"; zone "ns1.teo-en-ming.com" {type master;file "/var/named/ns1.teo-en-ming.com.db";}; zone "ns2.teo-en-ming.com" {type master;file "/var/named/ns2.teo-en-ming.com.db";}; // zone teo-en-ming.com zone "teo-en-ming.com" {type master; file "/var/named/teo-en-ming.com.db";}; // zone_end teo-en-ming.com File /var/named/ns1.teo-en-ming.com.db: ; Panel %version% ; Zone file for ns1.teo-en-ming.com $TTL 14400 ns1.teo-en-ming.com. 86400 IN SOA ns1.teo-en-ming.com. info.centos-webpanel.com. ( 2013071600 ;serial, todays date+todays 86400 ;refresh, seconds 7200 ;retry, seconds 3600000 ;expire, seconds 86400 ;minimum, seconds ) ns1.teo-en-ming.com. 86400 IN NS ns1.teo-en-ming.com. ns1.teo-en-ming.com. 86400 IN NS ns2.teo-en-ming.com. ns1.teo-en-ming.com. 14400 IN A 3.21.30.127 File /var/named/ns2.teo-en-ming.com.db: ; Panel %version% ; Zone file for ns2.teo-en-ming.com $TTL 14400 ns2.teo-en-ming.com. 86400 IN SOA ns1.teo-en-ming.com. info.centos-webpanel.com. ( 2013071600 ;serial, todays date+todays 86400 ;refresh, seconds 7200 ;retry, seconds 3600000 ;expire, seconds 86400 ;minimum, seconds ) ns2.teo-en-ming.com. 86400 IN NS ns1.teo-en-ming.com. ns2.teo-en-ming.com. 86400 IN NS ns2.teo-en-ming.com. ns2.teo-en-ming.com. 14400 IN A 3.12.224.179 File /var/named/teo-en-ming.com.db: ; Generated by CWP ; Zone file for teo-en-ming.com $TTL 14400 @ 86400 IN SOA ns1.teo-en-ming.com. ceo.teo-en-ming-corp.com. ( 2020022453 ; serial, todays date+todays 3600 ; refresh, seconds 7200 ; retry, seconds 1209600 ; expire, seconds 86400 ) ; minimum, seconds @ 86400 IN NS ns1.teo-en-ming.com. @ 86400 IN NS ns2.teo-en-ming.com. @ IN A 3.21.30.127 localhost.teo-en-ming.com. IN A 127.0.0.1 @ IN MX 0 teo-en-ming.com. mail 14400 IN CNAME teo-en-ming.com. smtp 14400 IN CNAME teo-en-ming.com. pop 14400 IN CNAME teo-en-ming.com. pop3 14400 IN CNAME teo-en-ming.com. imap 14400 IN CNAME teo-en-ming.com. webmail 14400 IN A 3.21.30.127 cpanel 14400 IN A 3.21.30.127 cwp 14400 IN A 3.21.30.127 www 14400 IN CNAME teo-en-ming.com. ftp 14400 IN CNAME teo-en-ming.com. _dmarc 14400 IN TXT "v=DMARC1; p=none" @ 14400 IN TXT "v=spf1 +a +mx +ip4:3.21.30.127 ~all" ns1.teo-en-ming.com. 14400 IN A 3.21.30.127 ns2.teo-en-ming.com. 14400 IN A 3.12.224.179 default._domainkey 14400 IN TXT "v=DKIM1; k=rsa; p=MIGfMA0GCSqGSIb3DQEBAQUAA4GNADCBiQKBgQDEXk5wQIfKJPjTkkj0yHGX8yIJOOrsOsvAqbqVaEBFBWhRlF7YxyGzchaAdWEVQkozcsWPIL5DgJ7vWBoJIGqfNOT7vO/lStqNcXC+2hYVIF7MTB8i6tBW1/UDEuL8oammKWDq8P9Fpduk6JppV7rtKXeFzrYj35ydIhDIKUABcwIDAQAB" BIND DNS CONFIGURATION ON THE SLAVE DNS MANAGER =============================================== File /etc/named.conf: // // named.conf // // Provided by Red Hat bind package to configure the ISC BIND named(8) DNS // server as a caching only nameserver (as a any DNS resolver only). // // See /usr/share/doc/bind*/sample/ for example named configuration files. // // See the BIND Administrator's Reference Manual (ARM) for details about the // configuration located in /usr/share/doc/bind-{version}/Bv9ARM.html options { listen-on port 53 { any; }; listen-on-v6 port 53 { ::1; }; directory "/var/named"; dump-file "/var/named/data/cache_dump.db"; statistics-file "/var/named/data/named_stats.txt"; memstatistics-file "/var/named/data/named_mem_stats.txt"; recursing-file "/var/named/data/named.recursing"; secroots-file "/var/named/data/named.secroots"; allow-query { any; }; /* - If you are building an AUTHORITATIVE DNS server, do NOT enable recursion. - If you are building a RECURSIVE (caching) DNS server, you need to enable recursion. - If your recursive DNS server has a public IP address, you MUST enable access control to limit queries to your legitimate users. Failing to do so will cause your server to become part of large scale DNS amplification attacks. Implementing BCP38 within your network would greatly reduce such attack surface */ recursion no; dnssec-enable yes; dnssec-validation yes; /* Path to ISC DLV key */ bindkeys-file "/etc/named.root.key"; managed-keys-directory "/var/named/dynamic"; pid-file "/run/named/named.pid"; session-keyfile "/run/named/session.key"; masterfile-format text; }; logging { channel default_debug { file "data/named.run"; severity dynamic; }; }; zone "." IN { type hint; file "named.ca"; }; include "/etc/named.rfc1912.zones"; include "/etc/named.root.key"; // zone teo-en-ming.com // zone "teo-en-ming.com" {type master; file "/var/named/teo-en-ming.com.db";}; // zone_end teo-en-ming.com //Slave dns configuration include "/etc/named/slave.conf"; File /var/named/teo-en-ming.com.db (WRONG, DON'T USE): ; Generated by CWP ; Zone file for teo-en-ming.com $TTL 14400 @ 86400 IN SOA ns1.centos-webpanel.com. ceo.teo-en-ming-corp.com. ( 2020022660 ; serial, todays date+todays 3600 ; refresh, seconds 7200 ; retry, seconds 1209600 ; expire, seconds 86400 ) ; minimum, seconds @ 86400 IN NS ns1.centos-webpanel.com. @ 86400 IN NS ns2.centos-webpanel.com. @ IN A 3.12.224.179 localhost.teo-en-ming.com. IN A 127.0.0.1 @ IN MX 0 teo-en-ming.com. mail 14400 IN CNAME teo-en-ming.com. smtp 14400 IN CNAME teo-en-ming.com. pop 14400 IN CNAME teo-en-ming.com. pop3 14400 IN CNAME teo-en-ming.com. imap 14400 IN CNAME teo-en-ming.com. webmail 14400 IN A 3.12.224.179 cpanel 14400 IN A 3.12.224.179 cwp 14400 IN A 3.12.224.179 www 14400 IN CNAME teo-en-ming.com. ftp 14400 IN CNAME teo-en-ming.com. _dmarc 14400 IN TXT "v=DMARC1; p=none" @ 14400 IN TXT "v=spf1 +a +mx +ip4:3.12.224.179 ~all" default._domainkey 14400 IN TXT "v=DKIM1; k=rsa; p=MIGfMA0GCSqGSIb3DQEBAQUAA4GNADCBiQKBgQCktDYpEtFO7dyJeErMbjHvyfJYU8RqDeS7WVqQnjH4fP42JSqPmxEFX+QytFTlGqd6ndlz9Tjqi1iZsD0ajB/+0Pkwq/KtL6NVo2TIqnXj8VebV9+FEcx+FGvLA/b5zz+Hfn0Bf+w/2T2bSwUm+tJoHilmANCFGlcGmpO9/lXvAwIDAQAB" File /etc/named/slave.conf: zone "teo-en-ming.com" { type slave; file "slave/db.teo-en-ming.com"; masters { 3.21.30.127; };}; //username:enming That's all folks! -----BEGIN EMAIL SIGNATURE----- The Gospel for all Targeted Individuals (TIs): [The New York Times] Microwave Weapons Are Prime Suspect in Ills of U.S. Embassy Workers Link: https://www.nytimes.com/2018/09/01/science/sonic-attack-cuba-microwave.html ******************************************************************************************** Singaporean Mr. Turritopsis Dohrnii Teo En Ming's Academic Qualifications as at 14 Feb 2019 and refugee seeking attempts at the United Nations Refugee Agency Bangkok (21 Mar 2017), in Taiwan (5 Aug 2019) and Australia (25 Dec 2019 to 9 Jan 2020): [1] https://tdtemcerts.wordpress.com/ [2] https://tdtemcerts.blogspot.sg/ [3] https://www.scribd.com/user/270125049/Teo-En-Ming -----END EMAIL SIGNATURE-----

1 0

[Maria-discuss] How to manage partitions with InnoDB tables and custom data directories?
by Roland Ziegler 25 Feb '20

25 Feb '20

Hi all, I'd like to set up partitioning for the "time series" use case as in https://mariadb.com/kb/en/partition-maintenance/. However, I'd also like to control data directories. And I want the "max" or "future" partition as a catch all fallback. So I start with (minimalist example): CREATE TABLE partitioned ( timestamp INT(11) NOT NULL DEFAULT 0, PRIMARY KEY (timestamp) ) ENGINE=InnoDB PARTITION BY RANGE (timestamp) ( PARTITION pstart VALUES LESS THAN (1) DATA DIRECTORY 'G:/MariaDB/partitions/start', PARTITION p1 VALUES LESS THAN (1577833200) DATA DIRECTORY 'G:/MariaDB/partitions/1', PARTITION pmax VALUES LESS THAN MAXVALUE DATA DIRECTORY 'G:/MariaDB/partitions/max' ); Works as expected. Now I want to add a new partition, splitting the "pmax" partition: ALTER TABLE partitioned REORGANIZE PARTITION pmax INTO ( PARTITION p2 VALUES LESS THAN (1609455600) DATA DIRECTORY 'G:/MariaDB/partitions/2', PARTITION pmax VALUES LESS THAN MAXVALUE ); This only kind of succeeds, as the DATA DIRECTORY clause is ignored, with a warning. So the result is useless. The "pmax" partition is now in the default data directory and the "p2" partition is in G:\MariaDB\partitions\max. Specifying both data directories: ALTER TABLE partitioned REORGANIZE PARTITION pmax INTO ( PARTITION p2 VALUES LESS THAN (1609455600) DATA DIRECTORY 'G:/MariaDB/partitions/2', PARTITION pmax VALUES LESS THAN MAXVALUE DATA DIRECTORY 'G:/MariaDB/partitions/max' ); gives two warnings, the "pmax" stays in its place now, but the "p2" partition is still created in that same directory. ADD PARTITION doesn't work either, as it collides with MAXVALUE condition. Any way around this? MariaDB version 10.4.12 Thank you.

1 0

[Maria-discuss] How to give user permission to create a trigger?
by Thomas Plant 24 Feb '20

24 Feb '20

Hello, we have a MariaDB 10.1 installation used for our shared hosting with a Master/Slave Replication to another MariaDB 10.1. One of our customers has a CMS that needs to create a trigger during installation and errors out about missing permission to create the trigger. I've 'googled' around and found only that granting 'SUPER' privileges does resolve this, but we can't give the customer so far reaching privileges. He is not the only using this server. Tried to do a 'grant trigger ....' for this user, the command returns without error, but the grant trigger does not show up in 'show grants....'. Probably because he as already 'all privileges' granted? Is there a way to resolve this? Kind Regards, Thomas

2 4

[Maria-discuss] Mr. Teo En Ming's Guide to Deploying CentOS Web Panel (CWP) Web Hosting Control Panel on Amazon AWS Cloud
by Turritopsis Dohrnii Teo En Ming 23 Feb '20

23 Feb '20

Subject: Mr. Teo En Ming's Guide to Deploying CentOS Web Panel (CWP) Web Hosting Control Panel on Amazon AWS Cloud ===FIRST DRAFT=== PUBLISHED 23 FEB 2020 SUNDAY, SINGAPORE, SINGAPORE I chose CentOS Web Panel because the graphical user interface is a bit like cPanel and it is free/open source. EXTREMELY DETAILED INSTRUCTIONS OF TEO EN MING'S GUIDE ====================================================== REFERENCE ========= Guide: Part 1: How I Built a cPanel Hosting Environment on Amazon AWS Link: https://wiredgorilla.com/part-1-built-cpanel-hosting-environment-amazon-aws/ Setup the Amazon AWS VPC (Virtual Private Cloud) ================================================ Go to https://us-east-2.console.aws.amazon.com/vpc/home?region=us-east-2#dashboard: Click Launch VPC Wizard. Select VPC with a Single Public Subnet. IPv4 CIDR block: 10.0.0.0/16 VPC Name: Teo En Ming VPC Public subnet's IPv4 CIDR: 10.0.0.0/24 Availability Zone: No Preference Subnet name: Public subnet Click Create VPC. Create Security Groups in Amazon AWS Cloud ========================================== Click Security Groups in the VPC Dashboard. Sub-Part 1 ========== Click Create Security Group. Security Group Name: NameServers Description: Allows access to DNS servers VPC: Teo En Ming VPC Click Create. Sub-Part 2 ========== Click Create Security Group. Security Group Name: CentOSWebPanel Description: Allows access to CentOS Web Panel VPC: Teo En Ming VPC Click Create. Sub-Part 3 ========== Select the NameServers Security Group. On the Inbound tab, click Edit. Under Type, select All Traffic. Protocol: All Port Range: 0 - 65535 Source: Anywhere Click Save. Sub-Part 4 ========== Select the CentOSWebPanel Security Group. On the Inbound tab, click Edit. Under Type, select All Traffic. Protocol: All Port Range: 0 - 65535 Source: Anywhere Click Save. Provisioning the Primary DNS Server =================================== On the EC2 Dashboard, click Instances. Click Launch Instance. Search for centos in the AWS Markpetplace. Select CentOS 7 (x86_64) - with Updates HVM (free tier eligible). Click Continue. Select t2.micro (free tier eligible). Click Next: Configure Instance Details. Network: Teo En Ming VPC Subnet: Public subnet | us-east-2a Click Protect against accidental termination. Click Next: Add Storage Size (GiB): 8 Click Next: Add Tags Key = Name Value = ns1 Click Next: Configure Security Group Click Select an existing security group Select NameServers Click Review and Launch. Click Launch. Select Create a new key pair. Key pair name: cwp Click Download key pair. Click Launch Instances. Click Instances. Select ns1, right click and select Networking > Manage IP Addresses. Click Allocate an elastic IP to this instance. Click Allocate. Click Associate this Elastic IP Address. Instance: ns1 Click Associate. IPv4 address of Primary DNS server is 13.58.253.162 Provisioning the Secondary DNS Server ===================================== On the EC2 Dashboard, click Instances. Click Launch Instance. Search for centos in the AWS Markpetplace. Select CentOS 7 (x86_64) - with Updates HVM (free tier eligible). Click Continue. Select t2.micro (free tier eligible). Click Next: Configure Instance Details. Network: Teo En Ming VPC Subnet: Public subnet | us-east-2a Click Protect against accidental termination. Click Next: Add Storage Size (GiB): 8 Click Next: Add Tags Key = Name Value = ns2 Click Next: Configure Security Group Click Select an existing security group Select NameServers Click Review and Launch. Click Launch. Select Choose an existing key pair. Key pair name: cwp Click Launch Instances. Click Instances. Select ns2, right click and select Networking > Manage IP Addresses. Click Allocate an elastic IP to this instance. Click Allocate. Click Associate this Elastic IP Address. Instance: ns2 Click Associate. IPv4 address of Secondary DNS server is 3.20.186.205 Provisioning CentOS 7 to Install CentOS Web Panel Later ======================================================= On the EC2 Dashboard, click Instances. Click Launch Instance. Search for centos in the AWS Markpetplace. Select CentOS 7 (x86_64) - with Updates HVM (free tier eligible). Click Continue. Select t2.micro (free tier eligible). Click Next: Configure Instance Details. Network: Teo En Ming VPC Subnet: Public subnet | us-east-2a Click Protect against accidental termination. Click Next: Add Storage Size (GiB): 30 Click Next: Add Tags Key = Name Value = CentOSWebPanel Click Next: Configure Security Group Click Select an existing security group Select CentOSWebPanel Click Review and Launch. Click Launch. Select Choose an existing key pair. Key pair name: cwp Click Launch Instances. Click Instances. Select CentOSWebPanel, right click and select Networking > Manage IP Addresses. Click Allocate an elastic IP to this instance. Click Allocate. Click Associate this Elastic IP Address. Instance: CentOSWebPanel Click Associate. IPv4 address of CentOS Web Panel is 3.21.30.127 RFERENCE ======== Guide: Connecting to Your Linux Instance Using SSH Link: https://docs.aws.amazon.com/AWSEC2/latest/UserGuide/AccessingInstancesLinux… How to SSH into Linux Instances in Amazon AWS Cloud =================================================== $ chmod 600 cwp.pem For Primary DNS Server: $ ssh -i cwp.pem centos(a)13.58.253.162 For Secondary DNS Server: $ ssh -i cwp.pem centos(a)3.20.186.205 For CentOS Web Panel: $ ssh -i cwp.pem centos(a)3.21.30.127 REFERENCE ========= Guide: How To Configure BIND as a Private Network DNS Server on CentOS 7 Link: https://www.digitalocean.com/community/tutorials/how-to-configure-bind-as-a… Configuring the Primary DNS Server ================================== $ sudo passwd $ su - # yum install bind bind-utils # yum install nano # nano /etc/named.conf // // named.conf // // Provided by Red Hat bind package to configure the ISC BIND named(8) DNS // server as a caching only nameserver (as a localhost DNS resolver only). // // See /usr/share/doc/bind*/sample/ for example named configuration files. // // See the BIND Administrator's Reference Manual (ARM) for details about the // configuration located in /usr/share/doc/bind-{version}/Bv9ARM.html options { listen-on port 53 { 127.0.0.1; 10.0.0.99; }; // listen-on-v6 port 53 { ::1; }; directory "/var/named"; dump-file "/var/named/data/cache_dump.db"; statistics-file "/var/named/data/named_stats.txt"; memstatistics-file "/var/named/data/named_mem_stats.txt"; recursing-file "/var/named/data/named.recursing"; secroots-file "/var/named/data/named.secroots"; allow-transfer { 3.20.186.205; }; allow-query { any; }; /* - If you are building an AUTHORITATIVE DNS server, do NOT enable recursion. - If you are building a RECURSIVE (caching) DNS server, you need to enable recursion. - If your recursive DNS server has a public IP address, you MUST enable access control to limit queries to your legitimate users. Failing to do so will cause your server to become part of large scale DNS amplification attacks. Implementing BCP38 within your network would greatly reduce such attack surface */ recursion yes; dnssec-enable yes; dnssec-validation yes; /* Path to ISC DLV key */ bindkeys-file "/etc/named.root.key"; managed-keys-directory "/var/named/dynamic"; pid-file "/run/named/named.pid"; session-keyfile "/run/named/session.key"; }; logging { channel default_debug { file "data/named.run"; severity dynamic; }; }; zone "." IN { type hint; file "named.ca"; }; include "/etc/named.rfc1912.zones"; include "/etc/named.root.key"; include "/etc/named/named.conf.local"; # nano /etc/named/named.conf.local zone "teo-en-ming.com" { type master; file "/etc/named/zones/db.teo-en-ming.com"; # zone file path }; # chmod 755 /etc/named # mkdir /etc/named/zones # nano /etc/named/zones/db.teo-en-ming.com $TTL 604800 @ IN SOA ns1.teo-en-ming.com. ceo.teo-en-ming.com. ( 2020022301 ; Serial 604800 ; Refresh 86400 ; Retry 2419200 ; Expire 604800 ) ; Negative Cache TTL ; ; name servers - NS records IN NS ns1.teo-en-ming.com. IN NS ns2.teo-en-ming.com. ; name servers - A records ns1.teo-en-ming.com. IN A 13.58.253.162 ns2.teo-en-ming.com. IN A 3.20.186.205 ; Additional A records www.teo-en-ming.com. IN A 3.21.30.127 # named-checkconf # systemctl start named # systemctl enable named Testing the Primary DNS Server ============================== $ dig @13.58.253.162 teo-en-ming.com Configuring the Secondary DNS Server ==================================== $ sudo passwd $ su - # yum install nano # yum install bind bind-utils # nano /etc/named.conf // // named.conf // // Provided by Red Hat bind package to configure the ISC BIND named(8) DNS // server as a caching only nameserver (as a localhost DNS resolver only). // // See /usr/share/doc/bind*/sample/ for example named configuration files. // // See the BIND Administrator's Reference Manual (ARM) for details about the // configuration located in /usr/share/doc/bind-{version}/Bv9ARM.html options { listen-on port 53 { 127.0.0.1; 10.0.0.76; }; // listen-on-v6 port 53 { ::1; }; directory "/var/named"; dump-file "/var/named/data/cache_dump.db"; statistics-file "/var/named/data/named_stats.txt"; memstatistics-file "/var/named/data/named_mem_stats.txt"; recursing-file "/var/named/data/named.recursing"; secroots-file "/var/named/data/named.secroots"; allow-query { any; }; /* - If you are building an AUTHORITATIVE DNS server, do NOT enable recursion. - If you are building a RECURSIVE (caching) DNS server, you need to enable recursion. - If your recursive DNS server has a public IP address, you MUST enable access control to limit queries to your legitimate users. Failing to do so will cause your server to become part of large scale DNS amplification attacks. Implementing BCP38 within your network would greatly reduce such attack surface */ recursion yes; dnssec-enable yes; dnssec-validation yes; /* Path to ISC DLV key */ bindkeys-file "/etc/named.root.key"; managed-keys-directory "/var/named/dynamic"; pid-file "/run/named/named.pid"; session-keyfile "/run/named/session.key"; }; logging { channel default_debug { file "data/named.run"; severity dynamic; }; }; zone "." IN { type hint; file "named.ca"; }; include "/etc/named.rfc1912.zones"; include "/etc/named.root.key"; include "/etc/named/named.conf.local"; # chmod 755 /etc/named # nano /etc/named/named.conf.local zone "teo-en-ming.com" { type slave; file "slaves/db.teo-en-ming.com"; masters { 13.58.253.162; }; # ns1 private IP }; # named-checkconf # systemctl start named # systemctl enable named Testing the Secondary DNS Server ================================ $ dig @3.20.186.205 teo-en-ming.com Configuring Custom Name Servers At Your Domain Registrar ======================================================== Go to DNS management. Under host names, Set ns1 to 13.58.253.162 Set ns2 to 3.20.186.205 Set custom name servers to: ns1.teo-en-ming.com ns2.teo-en-ming.com REFERENCE ========= Guide: How to Set up a CentOS Web Panel Link: https://www.alibabacloud.com/blog/how-to-set-up-a-centos-web-panel_595183 Setting Up CentOS Web Panel =========================== $ sudo passwd $ su - # yum -y update && yum -y install wget # hostnamectl set-hostname www.teo-en-ming.com # cd /usr/local/src && wget http://centos-webpanel.com/cwp-el7-latest && sh cwp-el7-latest Started installing CentOS Web Panel at 6.24 PM on 23 Feb 2020 Sunday. Completed installing CentOS Web Panel at 6.30 PM on 23 Feb 2020 Sunday. Total duration: 6 mins ############################# # CWP Installed # ############################# Go to CentOS WebPanel Admin GUI at http://SERVER_IP:2030/ http://3.21.30.127:2030 SSL: https://3.21.30.127:2031 --------------------- Username: root Password: ssh server root password MySQL root Password: ######################################################### CentOS Web Panel MailServer Installer ######################################################### SSL Cert name (hostname): www.teo-en-ming.com SSL Cert file location /etc/pki/tls/ private|certs ######################################################### Visit for help: www.centos-webpanel.com Write down login details and press ENTER for server reboot! Please reboot the server! Reboot command: shutdown -r now # shutdown -r now Configuring CentOS Web Panel Web Hosting Control Panel ====================================================== Go to https://3.21.30.127:2031 >From the left menu, click on CWP Settings, then select Edit Settings. Admin Email: ceo(a)teo-en-ming-corp.com Check Activate NAT-ed network configuration. Click Save Changes. >From the left menu, click DNS Functions, then select Edit Nameservers IPs. Name Server 1: ns1.teo-en-ming.com 13.58.253.162 Name Server 2: ns2.teo-en-ming.com 3.20.186.205 Click Save Changes. That's all. In future, go to https://www.teo-en-ming.com:2031 It works! AUTHOR: MR. TURRITOPSIS DOHRNII TEO EN MING, SINGAPORE -----BEGIN EMAIL SIGNATURE----- The Gospel for all Targeted Individuals (TIs): [The New York Times] Microwave Weapons Are Prime Suspect in Ills of U.S. Embassy Workers Link: https://www.nytimes.com/2018/09/01/science/sonic-attack-cuba-microwave.html ******************************************************************************************** Singaporean Mr. Turritopsis Dohrnii Teo En Ming's Academic Qualifications as at 14 Feb 2019 and refugee seeking attempts at the United Nations Refugee Agency Bangkok (21 Mar 2017), in Taiwan (5 Aug 2019) and Australia (25 Dec 2019 to 9 Jan 2020): [1] https://tdtemcerts.wordpress.com/ [2] https://tdtemcerts.blogspot.sg/ [3] https://www.scribd.com/user/270125049/Teo-En-Ming -----END EMAIL SIGNATURE-----

1 0

[Maria-discuss] Some questions about the Aarch64 CI
by Zheng Zhenyu 21 Jan '20

21 Jan '20

Hi MariaDB, Thanks for the greate unconference in Shanghai this week, it is a really useful event for MariaDB newbie like me. We have mentioned that we are willing to donate some ARM resource to the fundation for ARM testing and other purpose in the future. And I have done donated one for POC on 20th Nov, and I can now see a new builder(worker) on: https://buildbot.mariadb.org/#/builders named as ``aarch-fedora-30``, and the version is 2019.11.20, so I guess this could be running on server that I've donated? And seems it is successfully running for few rounds. So I have some questions about the Aarch64 CI: 1. So the jobs are running through docker, so jobs for different OS could be run on this host, right? Is it possible to also enable a CentOS7 job now? 2. As I mentioned in the unconference, we will have an OpenSource OS released soon, and we are looking for possiblities to also make it tested in the upstream, If the answer to the first question is True, we will have to prepare a base docker image for our OS, right? 3. I saw that there is a sponsors site for buildbot: https://buildbot.mariadb.org/#/sponsor , are we able to be on that page too? BR, Kevin Zheng

5 16

[Maria-discuss] [MariaDB 10.3.7] Hello, everyone. My process core dumped while calling mysql_ping().
by Ma Allen 15 Jan '20

15 Jan '20

Environment: MariaDB 10.3.7 database server & related headers and libraries CentOS 7.3 or 7.4 ./configure --prefix=/home/spiderflow --localstatedir=/home/spiderflow --enable-unix-socket --with-libnss-libraries=/usr/lib64 --with-libnss-includes=/usr/include/nss3 --with-libnspr-libraries=/usr/lib64 --with-libnspr-includes=/usr/include/nspr4 --enable-netmap --enable-rust --disable-gccmarch-native --enable-mysql --with-libmysql-includes=/usr/local/mariadb-10.3.7-linux-systemd-x86_64/include/mysql --with-libmysql-libraries=/usr/local/mariadb-10.3.7-linux-systemd-x86_64/lib Phenomenon: (gdb) #0 ma_pvio_write (pvio=0x10101010101b2e0, buffer=buffer@entry=0x2510101 "\001", length=length@entry=5) at /home/buildbot/buildbot/build/libmariadb/libmariadb/ma_pvio.c:350 #1 0x00007f7100249dea in ma_net_real_write (net=net@entry=0xee9de0 <g_mysql_info>, packet=0x2510101 "\001", len=5) at /home/buildbot/buildbot/build/libmariadb/libmariadb/ma_net.c:335 #2 0x00007f7100249f60 in ma_net_flush (net=net@entry=0xee9de0 <g_mysql_info>) at /home/buildbot/buildbot/build/libmariadb/libmariadb/ma_net.c:166 #3 0x00007f710024a71a in ma_net_write_command (net=net@entry=0xee9de0 <g_mysql_info>, command=command@entry=14 '\016', packet=packet@entry=0x7f71003d6c67 "", len=0, disable_flush=disable_flush@entry=0 '\000') at /home/buildbot/buildbot/build/libmariadb/libmariadb/ma_net.c:244 #4 0x00007f7100252ca4 in mthd_my_send_cmd (mysql=0xee9de0 <g_mysql_info>, command=<optimized out>, arg=0x7f71003d6c67 "", length=0, skipp_check=<optimized out>, opt_arg=<optimized out>) at /home/buildbot/buildbot/build/libmariadb/libmariadb/mariadb_lib.c:394 #5 0x00007f71002518d0 in mysql_ping (mysql=0xee9de0 <g_mysql_info>) at /home/buildbot/buildbot/build/libmariadb/libmariadb/mariadb_lib.c:2552 #6 0x00000000006651b2 in MySqlWaitConnected (p_mysql_data=p_mysql_data@entry=0xee9de0 <g_mysql_info>) at ../srcSF/spiderFlow-proto-baseline.c:676 #7 0x0000000000668819 in LoadProtoConfInfos (pInfo=pInfo@entry=0xee9de0 <g_mysql_info>) at ../srcSF/spiderFlow-proto-baseline.c:835 #8 0x000000000066c53a in InitBaseLine () at ../srcSF/spiderFlow-proto-baseline.c:1069 #9 0x00000000005f308b in PostConfLoadedSetup (suri=0x10dbb80 <suricata>) at suricata.c:2900 #10 0x0000000000425ce8 in main (argc=5, argv=0x7fff1cdca258) at suricata.c:3072 (gdb) Analyzation: The core dump always exists after running for a while or maybe after the sever restarts. It occurs at the initialization and involves with ONLY ONE thread, which is the main thread of the whole process. The related code is two functions, which both checks the connection and read data from MariaDB. The first function works fine, however, the second one's checking connection leads to core dump. The code to check connection is as follows: void MySqlWaitConnected(MYSQL * p_mysql_data) { while (mysql_ping(p_mysql_data) != 0) { SCLogInfo("Mysql ping error:%s", mysql_error(p_mysql_data)); #ifdef WIN32 Sleep(3000); #else sleep(3); #endif } } Previously, I solved a likely similar problem as follows: Multiple threads shared ONE MySQL handler, which is the MYSQL data structure. Before inserting data into MariaDB without mutex, MySqlWaitConnected() is called. So while multiple threads do the same operation, inserting data is interrupted by mysql_ping() that leads to core dump. Afterwards, I comment MySqlWaitConnected() and add mutex for mysql_real_query() and mysql_commit(). Does anyone meet the same problem before? Any suggestion is appreciated. Thanks in advance. Best Regards, Allen Ma

2 4

[Maria-discuss] Windows C connector 3.1.6
by Kenneth Penza 14 Jan '20

14 Jan '20

Hi, I am trying to download the Windows C connector 3.1.6 build to test an SSL issue, however the Windows packages are not available from Connector-c-3.1.6 <https://downloads.mariadb.com/Connectors/c/connector-c-3.1.6/>. On checking the Connector-c-3.1.5 release, the required files are available. Will the 3.16 Windows build be available ? Thanks Kenneth

1 1