Questions tagged [mariadb-10.1]

MariaDB 10.1 was released as stable (GA) with version 10.1.8 on 17 Oct 2015.

Filter by
Sorted by
Tagged with
0 votes
0 answers
6 views

Importing .sql dump file results in "nonexistent" tables despite being visible in sql shell

I recently had to re-install MariaDB on my local machine and the whole process resulted in my making a .sql dump file of my DBs. The problem is that now that I have a fresh installment of MariaDB (I ...
0 votes
1 answer
31 views

Troubleshooting foreign key dependency error when setting up new MariaDB replica

Question: Does anyone have advice on what I can do to successfully get this new replica set up and replicating from our production server just like the old replica? Details: I am trying to set up a ...
0 votes
0 answers
92 views

Can I mix Galera-3 and Galera-4 nodes?

Say I have three servers node1 192.168.100.201 node2 192.168.100.202 node3 192.168.100.203 on node1 and node2 is running mariadb 10.5 and on node3 still 10.1 and I need that version there still, but ...
  • 101
1 vote
0 answers
54 views

Slow Query Log Only Logs Few Queries

Slow query is enabled but only logs few queries when you retrieve the slow log. It’s been working ok all last week, suddenly this week, it only logs just 2 or 3 queries. Long query time = 0.01 and I ...
  • 11
0 votes
0 answers
61 views

Inconsistent query result from mysql workbench and command line client

I have noticed I am getting inconsistent results for the same query when sent via MySQL Workbench and the command line client. The query is executed on a remote server, in which one of the columns ...
1 vote
0 answers
451 views

Very slow MariaDB query when adding ORDER BY/LIMIT but there are no rows

I am facing an issue with MariaDB 10.1 on a log table with the following (simplified) structure: CREATE TABLE logs ( log_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, log_date TIMESTAMP DEFAULT ...
0 votes
1 answer
96 views

InnoDB Buffer Pool and Buffer Log

I have been reading too many information about InnoDB parameters, but I still have a doubt. Should "innodb_log_buffer_size" be larger than "innodb_buffer_pool_size"?
1 vote
2 answers
78 views

How are SELECT queries isolated from each other with MariaDB 10.1?

Given the following tables.. CREATE TABLE `event` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `type` varchar(50) COLLATE utf8_general_ci NOT NULL, `data` longblob NOT NULL, `...
  • 111
0 votes
2 answers
594 views

GROUP CONCAT and GROUP BY using LEFT JOIN If no record exist Must return something

I have 2 tables with below structures. question table id, audio, s_id 1, ad1, 500 2, ad2, 500 3, ad3, 500 answers table where q_id is FK of question table. id, s_id, q_id, number, answer 1, 500, 1, ...
2 votes
1 answer
2k views

MariaDB freezes with no visible reason

MariaDB server experiences some sort of freezes happening sporadically with no visible reason or connection to outside factors (load, etc). After a thorough investigation the common picture for those ...
1 vote
2 answers
30 views

Updating log table in an effective manner

There is a table with compound key: t_count + pageType VARCHAR(32) ascii_bin + pageId VARCHAR(255) utf8_bin + count INT PRIMARY(pageType, pageId) Its purpose is to track how many times each page ...
0 votes
2 answers
758 views

MariaDB: Insert records (list of tuples) into table if not exists

Currently I have several sql queries that look like this: INSERT INTO `test` (col1, col2) SELECT * FROM (SELECT '1','2') AS tmp WHERE NOT EXISTS ( SELECT * FROM `test` WHERE col1='1' AND col2='...
1 vote
1 answer
713 views

MariaDB root password change using Ansible

I had tried to change MariaDB root password using following Ansible script. - name: Dump MySQL root Password debug: msg: "MySQL new Password : {{ mysql_pass }}" ...
0 votes
1 answer
219 views

Connection aborts is happening in MariaDB 10.2 but not in 10.1 even with log_warnings level set to 2

I am noticing different behavior in mariadb 10.1 & 10.2 reg.connection aborts. I noticed connection getting aborted after 7 minutes(wait_timeout) of any query execution. There are few random ...
0 votes
2 answers
11k views

SQL Error(1054): Unknown column in 'where clause' when trying to update. Column is actually available

I have two tables namely table_a and table_b. Table_a has several columns including student_id and course_start_date Table_b has several columns including student_id and fee_pay_date student_id is INT ...
0 votes
0 answers
175 views

What does MariaDB use for an encryption initialisation vector?

I have been using MySQL 5.6 in development environment and MariaDB 5.6 in production until I needed to use encryption. MySQL 5.6 has multiple encryption modes and allows AES-256-CBC. MariaDB 5.6 only ...
0 votes
1 answer
870 views

MariaDB 10.1 shutdown stuck at "Event Scheduler: Purging the queue"

I have read many different questions about slow MariaDB/MySQL shutdown, but I have not found an answer which solves my problem yet. On one of our servers with the highest load, when I issue a stop ...
2 votes
2 answers
1k views

Unable to run mariadb in safe mode

When I run following command mysqld_safe --skip-grant-tables & I get message myuser@myvm:~$ 200523 08:24:41 mysqld_safe Logging to syslog. 200523 08:24:41 mysqld_safe Starting mysqld daemon ...
1 vote
1 answer
85 views

Optimize SELECT statement for mysql database

I have this query: SELECT COUNT(*) AS "Other" FROM events JOIN triggers ON events.objectid = triggers.triggerid WHERE events.name REGEXP "No data from SAM0056(.FTG|.REC|.LEFT|.RIGHT|.BACK), .* ...
  • 45
1 vote
3 answers
2k views

Cannot login as new user on MariaDB

When I create a new user in MariaDB, I cannot login as that user. First I login to the database as root doing mariadb in a bash terminal. I then run the following commands to create a database and ...
  • 163
0 votes
1 answer
30 views

how can I update some table rows using data from existing rows?

I have created a table where it stores some multilingual store data with this structure create table store_locator_store_lang ( id_store int(11) unsigned not null, lang_id int not null, ...
  • 149
0 votes
1 answer
106 views

Mariadb started giving erroneous query results for 1 view

10.1.44-MariaDB-0ubuntu0.18.04.1 Have a view volv_action which does a few joins which has been working for years and was not changed. But last night Mariadb started giving erroneous results if its ...
  • 1
1 vote
0 answers
2k views

Mariadb is not starting

Please help me with this issue MariaDB not starting. Its a production server so not able to reinstall and clean the data at all. (This command executed by me) systemctl -l status mariadb.service ...
0 votes
1 answer
1k views

my.cnf for 16 GB Ram Server [MariaDB]

I am very new to this stuff. I just wanted to ask if somebody has a good my.cnf for my MariaDB database because every time i have more than 1000 connections to my website it starts to have hight load ...
1 vote
1 answer
3k views

Exclude rows with unique values in one column from grouped mysql query

The following table represents interactions form users with different projects: id user_id project_id 17373 6336 1001 17374 6336 1001 17375 6336 1001 ...
  • 115
-1 votes
1 answer
516 views

SQL Variables inside SELECT query in a function

MariaDB 10.3.14 I have a function that generates the root id in a hierarchy: The function is this: CREATE FUNCTION `getRootId`(GivenID INT) RETURNS int(11) DETERMINISTIC BEGIN DECLARE ch INT; ...
1 vote
1 answer
202 views

migrate users from mariadb10.1 to mysql5.7

My company is moving our databases from self-hosted mariadb 10.1 to Google CloudSQL (which is essentially MySQL 5.7). Dumping / restoring our application schemas looks straightforward. However I'm ...
  • 122
0 votes
1 answer
30 views

Set of values of a field of a related entity must be unique: How do I express this constraint in SQL?

Given the following three tables: Type( id int primary key, key varchar ) GroupOfInstances( id int primary key, name varchar ) InstanceOfType( id int primary key, ...
  • 101
1 vote
0 answers
937 views

Connect to MariaDB server fails although port seems to be open

Hello I am trying to connect to an external MariaDB server. The configurations files on this installations are in a different place but I managed to find my way around. The installation im using is: ...
  • 11
0 votes
1 answer
45 views

SELECT query returns legacy data

My program does r/w operations on a MariaDB table. There are circumstances why it has to do it in concurrent processes. That's why it does it with multiple connections. After a write (UPDATE query) ...
0 votes
0 answers
542 views

Implication in Increasing MySQL maximum keys allowed per table

We have a situation where we encounter a MySQL error ERROR 1069: Too many keys specified. Max 64 keys allowed In our database (using Magento E-commerce) we need to add 76 indexed key columns but ...
  • 111
1 vote
1 answer
2k views

Cannot remove orphaned InnoDB Temp Table - missing .frm file

For the second time, I have encountered an issue, where our MySQL server (MariaDB v10.1) suddenly threw an error - Could not remove temporary table: './database_name/#sql-4593_791', error: 120. ...
  • 15
4 votes
2 answers
10k views

Max Connections Maria DB

Every time I reboot Mariadb places me the max_connections in 151 and in the my.cnf I have it with 300. Why does this happen?
0 votes
1 answer
1k views

Multiple mariadb instances - how to access to the not default instances via command line?

I have a TYPO3 and a Moodle installation on one server with Debian stretch. Moodle needs some database configurations (MariaDB 10.1.38) which are different from the ones TYPO3 needs. I made a new ...
  • 3
2 votes
2 answers
224 views

how to improve this query performance

I have an application that's running this following query SELECT SQL_NO_CACHE p.*, product_shop.*, stock.out_of_stock, IFNULL(stock....
  • 149
0 votes
1 answer
734 views

convert date to timestamp and store into table using trigger mysql

Using trigger I convert DateTime into a timestamp and then store into another table. But somehow convert date into timestamp always affected by timezone so that's why I had use convert_tz of date and ...
0 votes
1 answer
502 views

MariaDB Multiline RegEx

I'm running a SQL query in MariaDB under PHPMyAdmin as follows: select REGEXP_REPLACE(post_content,'(?m)<noindex>[.\r\n]*</noindex>','') from wp_posts where ID = 318 I'm trying to ...
0 votes
1 answer
1k views

how to remove/deal with locks in a MariaDB Galera cluster

I have a cluster of 3 nodes running MariaDB Galera cluster, cluster works as expected, I can reboot a single node at a time and in the best case, IST will be used or SST(using rsync) to sync back the ...
  • 149
0 votes
1 answer
458 views

MySQL Conditional Counter Based Other Columns?

I have a query that returns purchases for all customers in a store across a date range. It works fine, but now I've been asked to modify the results so only the first purchase per customer per day is ...
  • 163
2 votes
1 answer
352 views

How to optimize the execution of query that takes too long

I am working with a query that is taking around 53 minutes to execute, which I feel is excessive. Maria DB version: Server version: 10.1.38-MariaDB-1~stretch mariadb.org The declaration of the ...
1 vote
2 answers
400 views

User rank based on cumulative rows

UPDATE: Full table schema and data: https://www.db-fiddle.com/f/jT3Mfwd1BJ8GrsBxxPDC8M/1 Table structure (some fields are hidden): +---------+---------+------------+-------------+-------+-------+ | ...
  • 13
-1 votes
1 answer
125 views

Cannot import table due to zombie constraint

I tried to refresh my copy of the production database by emptying and importing a SQL file. I've done it hundreds of times, only this time the import failed. I tried dropping & recreating the ...
4 votes
2 answers
3k views

Unexpected RAM usage on MySQL

We have a server which hosts about 10 customers in a multi-tenant web application, each customer has its own database which is cloned from a template, and each database is a little less than 3000 ...
2 votes
1 answer
582 views

Using mariabackup/xtrabackup vs block level snapshots

I am running a mariadb 10.1 instance with all innodb tables and a replication slave in a k8s cluster on Google Cloud's GKE. I have a scheduled mysqldump that runs every night. But for additional ...
  • 131
0 votes
1 answer
1k views

How to restore dump from MariaDB 10.1 to MySQL 5.5

I generate backups of my databases by using mysqldump.exe -u root --all-databases > mariaDbBackup.sql. When I try to restore my dump by doing mysql -u root -p < mariaDbBackup.sql, I get an ...
  • 105
0 votes
0 answers
36 views

How do I select this pivot query by specified Month

I need help with my mysql pivot code. Everytime I add WHERE MONTH(attendance_date) = 2 inside @sql_dynamic gets error. pls help I have a code: SET @sql_dynamic:= (SELECT GROUP_CONCAT(DISTINCT ...
2 votes
1 answer
11k views

Select only the last record for multiple values

I have a table called "sessions" with say 4 columns like below id name s_time f_time 01 abc 10.15 10.45 02 abc 11.05 11.55 03 abc 12.18 13.46 04 abc 15.12 16.53 05 def 10.01 ...
  • 23
0 votes
2 answers
682 views

Time taken to drop/add index is lesser than adding a column. Why is that?

I'm using 10.1.25-MariaDB-1~xenial, but engine is innodb though. I'm performing an alter by default it does ONLINE alter. When I add column it takes at least 50 mins for 50G table in our ...
  • 1,497
0 votes
1 answer
172 views

Mariadb 10.1 / Galera - bootstrap node always doesn't lose quorum

I'm using mariadb10.1.37, wsrep_provider_version 25.3.24(r3825). On my dev cluster (2node+1arbitrator) I found the following behaviour: node1: bootstrap new cluster (using galera_new_cluster, with pc....
  • 122
1 vote
3 answers
449 views

How can I speed up this query?

Here is the definition of the relevant table: CREATE TABLE IF NOT EXISTS pricehistory ( id INTEGER(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY, price INTEGER NOT NULL, region ...
  • 111