- discuss - lists.mariadb.org

[Maria-discuss] How to get rid of foreign keys to non existant temporary tables?
by Bernhard Weller 30 Jul '18

30 Jul '18

Hello, I have a server running MariaDB 10.1.29-MariaDB-6+b1 (on Debian). I recently tried upgrading a concrete5 installation from 8.3.2 to 8.4.1 which failed running a SQL statement: ALTER TABLE AreaLayoutsUsingPresets ADD CONSTRAINT FK_7A9049A1385521EA FOREIGN KEY (arLayoutID) REFERENCES AreaLayouts (arLayoutID) ON UPDATE CASCADE ON DELETE CASCADE With an error: SQLSTATE[HY000]: General error: 1005 Can't create table `concrete5`.`#sql-215_264a4` (errno: 121 "Duplicate key on write or update") Now after investigating that command should probably be done with foreign key checks disabled - but I'm not sure on that one. The problem which I'm having now is, that I have two foreign key entries in INNODB_SYS_FOREIGN which are for temporary tables which don't exist, so I have no idea how to remove them: ID FOR_NAME REF_NAME N_COLS TYPE concrete5/FK_3322FA75FD71026C concrete5/#sql-2a01_48f4 concrete5/Users 1 1 concrete5/FK_7A9049A1385521EA concrete5/#sql-215_26264 concrete5/AreaLayouts 1 5 I've tried creating a table with the corresponding name (#mysql50##sql-2a01_48f4) and referenced colums (uID, int(10)) for the first one. But executing ALTER TABLE `#mysql50##sql-2a01_48f4` DROP FOREIGN KEY FK_3322FA75FD71026C Results in error #1091. I'm a bit lost now - the update will certainly not work while these foreign keys are in the system blocking the unique name. Any ideas? Bernhard Weller

1 0

[Maria-discuss] Galera partitioning question
by Ryan Delgrosso 29 Jul '18

29 Jul '18

Greetings all, I am running MariaDB 10.2.16 on CentOS in AWS and am seeing a sporadic cluster partitioning and rejoining issue with seemingly no explicable cause. * I have elements in 3 different AWS availability zones in a single galera cluster * Monitoring logs I see this message: /Jul 29 05:33:53 server01 mysqld: 2018-07-29 5:33:53 139633883080448 [Note] WSREP: (eabb848a, 'tcp://0.0.0.0:4567') connection to peer 392b9516 with addr tcp://172.31.17.60:4567 timed out, no messages seen in PT3S/ * I have tried forcing a 1500byte MTU as some others sources mentioned jumbo framing could negatively impact galera replication. * Running prolonged packet captures between nodes i cannot seem to find anything else wrong, network connectivity isn't interrupted and no service restarts occur. * These partition events happen multiple times per day. Has anyone seem this sporadic cluster disconnect and re-join issue in a similar env? I did not previously note this behavior on 10.1. Any help is much appreciated. -Ryan

1 0

[Maria-discuss] Mysql replication into galera cluster Errors
by Ton Wittenberg 28 Jul '18

28 Jul '18

Hello All, currently i have one master ( Mariadb 10.0.15) most of oure databases and tables are still myisam. i made a galera cluster with 3 nodes on mariadb 10.3.8 i made a dump the master, imported it into galera cluster, and mysql replication is running but im encoutering Alot of errors: Jul 28 00:30:32 db100 mysqld[7615]: 2018-07-28 0:30:32 3807 [Warning] WSREP: SQL statement was ineffective thd: 3807 buf: 401 Jul 28 00:30:32 db100 mysqld[7615]: schema: mytable Jul 28 00:30:32 db100 mysqld[7615]: QUERY: COMMIT Jul 28 00:30:32 db100 mysqld[7615]: => Skipping replication the idea is to discontinue the 10.0.15 machine and replace it with a 3 node galera cluster. and to setup mysql replication into galera. then start inserting in galera. and then shutdown the old master to provide a smooth migration. what could this be or how can i further debug this and find out why i receive this errors? Kind regards Ton Wittenberg

1 0

[Maria-discuss] community contribution measurement page
by Daniel Black 23 Jul '18

23 Jul '18

Folks, I've prepared a community contribution measurement page: http://measure-mariadb.ozlabs.org/repo/mariadb/server-outside-org.html This is largely the code base of https://github.com/MeasureOSS/Measure known errors: * I've yet to create the organisation aspects of this site. * some top page may be distorted by non-mariadb respositories Insights and suggested improvements welcome (especially with code) :-)

3 4

[Maria-discuss] MariaDB 10.3.8 does not install on Debian 8 and 9?
by Vimal Kumar V. 19 Jul '18

19 Jul '18

Dear Friends, I am trying to install MariaDB 10.3.8 on Debian 8&9. When I try to add following repo key, apt-key adv --recv-keys --keyserver SKS key server <http://keyserver.ubuntu.com/> 0xF1656F24C74CD1D8 I got following error message, Executing: /tmp/apt-key-gpghome.YltUOLRxyc/gpg.1.sh --recv-keys --keyserver SKS key server <http://keyserver.ubuntu.com/> 0xF1656F24C74CD1D8 gpg: keyserver receive failed: Server indicated a failure Please guide me to how to bypass the problem. -- Vimal Kumar V. Technical Assistant Mahatma Gandhi University Kottayam, Kerala, India-686 560 My Home Page: http://www.vimalkumar.info My Blogs: http://linuxhalwa.blogspot.com http://kohageek.blogspot.in http://dspacegeek.blogspot.in http://moovandan.blogspot.in --------------------------------------------------------------------------- "I forget what I was taught. I only remember what I have learnt" -Patrick White

2 1

Re: [Maria-discuss] Replication Problem
by Kristian Nielsen 05 Jul '18

05 Jul '18

Thomas Plant <thomas(a)plant.systems> writes: > Your tip with 'MASTER_USE_GTID=slave_pos' fixed it.Will have to look > better in the documentation next time, never found the 'slave_pos' > mentioned or 'SET sql_log_bin=0'. > > Thank you very much for your help. Welcome, glad that you solved your problem. The slave_pos/current_pos is confusing a lot of users. It would probably have been better if current_pos had never been introduced. > so you mean that disabling the binary log on the slave would be > indicated? Can I do it while it is online? SET sql_log_bin=0 disables binlogging only for the following queries done in that connection (not in general). So yes, it can be done online. You would do it for queries that you will not want replicated to other servers. For example, if you later make this slave the master, and put the old master to replicate from the old slave (now new master), you probably do not want to replicate your earlier slave-fixup-query. Hence the suggestion to SET sql_log_bin=0 to avoid having this query in the slave binlog. But if using MASTER_USE_GTID=slave_pos, in most cases it won't matter one way or the other. - Kristian.

1 0

[Maria-discuss] Replication Problem
by Thomas Plant 04 Jul '18

04 Jul '18

Hello, I'd have a question about MariaDB 10.1 replication, which has been interrupted by a power outage in our datacenter. We started the master server and had a duplicate id in a table on the slave, so replication stopped. We did not have time to adjust this at the moment, there were a lot of other systems to start and check if all was alright. Today I had time to look at the error, removed the duplicate ID from the table and started the slave thread again using 'start slave;'. But now I get another error: Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Error: connecting slave requested to start from GTID 0-2-2948175468, which is not in the master's binlog. Since the master's binlog contains GTIDs with higher sequence numbers, it probably means that the slave has diverged due to executing extra erroneous transactions' Can this be resolved? Or will I have to start replication from scratch? Greetings, Thomas

5 4

[Maria-discuss] MariaDB embedded, prepared statements
by Julien Muchembled 02 Jul '18

02 Jul '18

Hello, In our application (NEO - https://neo.nexedi.com/ ), we have 2 backends: SQLite and MySQL/MariaDB We also have a benchmarking tool working with a quite small dataset and showing that SQLite is ~30% than MariaDB. For years, we supposed that SQLite performed better in some cases for 2 reasons: - everything processed in same thread (i.e. no socket or context switches) - precompiled queries And by combining both, skip any kind of string serialization (i.e. do not format a query on one side, and parse on the other side). Now, we are trying to optimize our MariaDB backend by trying to do like SQLite. = MariaDB embedded I could make it work, but only with InnoDB for the moment. I kept using the same binding, MySQLdb, which I rebuilt against libmariadbd-dev_10.1.29-6+b1_amd64.deb Currently, we only use TokuDB in production and I have the same issue as: https://forums.mysql.com/read.php?94,629158,629158 ("embedded server cannot accept a plugin engine") This user solved it by embedding his engine inside libmysqld. I wonder if it will work with TokuDB. Anyway, I haven't seen any speed improvement. = Prepared statements I couldn't find anything else that's closer to SQLite precompiled queries. Which means that there's still some formatting/parsing. | Com_stmt_execute | 96528 | | Com_stmt_prepare | 40 | But again, not faster. Well, I do my best to have stable results, like having the DB in tmpfs or disabling C-states, but it's hard to measure small changes of performance. With 10.1.29, prepared statements look slightly slower. I also tried the new EXECUTE syntax with 10.2.16 to avoid SET queries, and it may be 1% faster (< error margin). I only turned the most often used queries into prepared statements. Here is one of the most complex queries we have: SELECT tid, compression, data.hash, value, value_tid FROM obj FORCE INDEX(PRIMARY) LEFT JOIN data ON (obj.data_id = data.id) WHERE `partition`=? AND oid=? AND tid < ? ORDER BY tid DESC LIMIT 1 https://mariadb.com/kb/en/library/prepared-statements/ should explain the purpose of prepared statements. The "In addition to using prepared statements from the libmysqld" clause seems to tell there's a specific kind of prepared statements within MariaDB embedded but I haven't found anything about that. We're disappointed by all these results. Did I miss something ? If not, I'll have to do some profiling of MariaDB. Julien

1 0

Re: [Maria-discuss] [MariaDB Announce] MariaDB 10.2.16 now available
by Reindl Harald 27 Jun '18

27 Jun '18

is there any QA on your side? mysql-test-run: *** ERROR: Could not find any of /usr/sql/mysql_tzinfo_to_sql /usr/bin/mysql_tzinfo_to_sql /usr/sql/mysql_tzinfo_to_sql where the hell is "mysql_tzinfo_to_sql" gone? that it's here in "mariadb-manpages" is a packaging detail but in 10.2.16 it seems to be missing completly [root@testserver:~]$ rpm -q --file /usr/bin/mysql_tzinfo_to_sql mariadb-manpages-10.2.15-1.0.fc27.20180613.rh.sandybridge.x86_64 Am 26.06.2018 um 17:45 schrieb MariaDB Announce List: > The MariaDB project is pleased to announce the availability > of MariaDB 10.2.16, the latest stable release in the > MariaDB 10.2 series. > > See the Release Notes and Changelogs for details. > > > - - Links - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - > > MariaDB 10.2.16 > - Release Notes: https://mariadb.com/kb/en/mdb-10216-rn/ > - Changelog: https://mariadb.com/kb/en/mdb-10216-cl/ > - Downloads: https://downloads.mariadb.org/mariadb/10.2.16 > > 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 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://jira.mariadb.org > > > - - 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!

2 3

[Maria-discuss] MariaDB crashes because of "long semaphore wait" after migrating from 10.1 to 10.3
by TD 22 Jun '18

22 Jun '18

Hi, I have a Windows server that has been running MariaDB 10.1 successfully for over a year. The server remains mostly idle for long times with some read access, but occasionally there are transactions that add data (about 500k rows per commit). There can be up to 10 such transactions (one per database) at the same time and during those times the server is under quite some load (the code processing the data resides on the same server as the database). We're using an 8 core AMD Ryzen, 32 GB RAM and a Samsung SSD. Like I said, this worked fine with MariaDB 10.1 all the time. We're currently in the process of setting up a new server (specs similar to the ones above) and because MariaDB 10.3 has been released recently, I was curious and installed it. The database now constantly shuts down because of "[FATAL] InnoDB: Semaphore wait has lasted > 600 seconds. We intentionally crash the server because it appears to be hung." whenever there is one of the load situations described above. If I install MariaDB 10.1 on the new server, it runs as fine as the old one. The crashes also happen when I start with a clean database (no data migrated from 10.1 databases). I'm not a huge MariaDB config pro, as it's only a tool to store the data, it just needs to run with reasonable performance and has always worked until now. As far as I know and can judge from comments in the file, the my.ini in use is (mostly?) based on my-innodb-heavy-4G.cnf, but coworkers might have made modifications. I've attached the contents below. I don't know how I can debug this issue further. Windows event viewer contains several log warnings with the text "InnoDB: A long semaphore wait:" but no further information why this wait happens. I couldn't find any other error logs. I guess it is some configuration error (I read about MariaDB switching from XtraDB to InnoDB, so I guess that the issue might be related). Migrating to 10.3 is not an absolute must, but it would be nice to be able to fix the problem. Any help would be greatly appreciated and if you need more information, I'll gladly try to provide it. Thanks, Tom. my.ini: [mysqld] max_connections = 100 max_allowed_packet = 16M binlog_cache_size = 1M max_heap_table_size = 64M read_buffer_size = 2M read_rnd_buffer_size = 16M sort_buffer_size = 8M join_buffer_size = 8M thread_cache_size = 10 query_cache_size = 64M query_cache_limit = 2M thread_stack = 240K tmp_table_size = 64M slow_query_log expire_logs_days = 14 log-bin = mysql-bin character-set-server=utf8 collation-server=utf8_general_ci #*** MyISAM Specific options key_buffer_size = 32M bulk_insert_buffer_size = 64M myisam_sort_buffer_size = 128M myisam_max_sort_file_size = 10G myisam_repair_threads = 1 myisam_recover # *** INNODB Specific options *** #innodb_fatal_semaphore_wait_threshold = 6000 #tried that, didn't help #innodb_adaptive_hash_index = 0 #tried that (suggested in some stack overflow post, didn't help) innodb_buffer_pool_size = 2G innodb_data_file_path = ibdata1:10M:autoextend innodb_write_io_threads = 8 innodb_read_io_threads = 8 innodb_thread_concurrency = 16 innodb_log_buffer_size = 8M innodb_log_file_size = 256M innodb_log_files_in_group = 3 innodb_max_dirty_pages_pct = 10 innodb_io_capacity = 2000 innodb_lock_wait_timeout = 120 [mysql] no-auto-rehash [myisamchk] key_buffer_size = 512M sort_buffer_size = 512M read_buffer = 8M write_buffer = 8M [mysqld_safe] open-files-limit = 8192

4 4