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
1 vote
0 answers
28 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 ...
user avatar
0 votes
1 answer
36 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"?
user avatar
1 vote
2 answers
64 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, `...
user avatar
  • 111
0 votes
2 answers
19 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, ...
user avatar
1 vote
1 answer
721 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 ...
user avatar
1 vote
2 answers
27 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 ...
user avatar
0 votes
2 answers
416 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='...
user avatar
1 vote
1 answer
284 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 }}" ...
user avatar
0 votes
1 answer
115 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 ...
user avatar
0 votes
2 answers
5k 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 ...
user avatar
0 votes
0 answers
118 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 ...
user avatar
0 votes
0 answers
619 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 ...
user avatar
2 votes
2 answers
811 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 ...
user avatar
1 vote
1 answer
72 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), .* ...
user avatar
  • 45
0 votes
2 answers
1k 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 ...
user avatar
  • 153
0 votes
1 answer
25 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, ...
user avatar
  • 147
0 votes
1 answer
83 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 ...
user avatar
  • 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 ...
user avatar
0 votes
1 answer
744 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 ...
user avatar
1 vote
1 answer
2k 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 ...
user avatar
  • 115
-1 votes
1 answer
136 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; ...
user avatar
1 vote
1 answer
156 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 ...
user avatar
  • 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, ...
user avatar
  • 101
1 vote
0 answers
699 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: ...
user avatar
  • 11
0 votes
1 answer
41 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) ...
user avatar
0 votes
0 answers
363 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 ...
user avatar
  • 101
1 vote
1 answer
1k 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. ...
user avatar
  • 15
3 votes
2 answers
6k 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?
user avatar
0 votes
1 answer
878 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 ...
user avatar
  • 3
2 votes
2 answers
201 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....
user avatar
  • 147
0 votes
1 answer
528 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 ...
user avatar
0 votes
1 answer
458 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 ...
user avatar
0 votes
1 answer
817 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 ...
user avatar
  • 139
0 votes
1 answer
345 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 ...
user avatar
  • 163
2 votes
1 answer
231 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 ...
user avatar
1 vote
2 answers
334 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): +---------+---------+------------+-------------+-------+-------+ | ...
user avatar
  • 13
-1 votes
1 answer
120 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 ...
user avatar
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 ...
user avatar
2 votes
1 answer
521 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 ...
user avatar
  • 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 ...
user avatar
  • 105
0 votes
0 answers
30 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 ...
user avatar
0 votes
1 answer
8k 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 ...
user avatar
  • 3
0 votes
2 answers
250 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 ...
user avatar
  • 1,479
0 votes
1 answer
115 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....
user avatar
  • 122
1 vote
3 answers
312 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 ...
user avatar
  • 111
0 votes
1 answer
49 views

Processes dissappear from processlist (MariaDB)

In my application I need to monitor list of process ids for some database user, so I execute SHOW PROCESSLIST command to get information. The problem is that sometimes some Id dissapears from command ...
user avatar
1 vote
2 answers
2k views

DELETE inside INSERT Trigger

I wanted to make rotation inside my logs table to store last 100 rows only, and decided to use triggers, as in create trigger rotate_log after insert on logs for each row begin declare cnt int; ...
user avatar
  • 113
7 votes
4 answers
1k views

Why the query optimizer choose this bad execution plan?

We have a MariaDB table (stories) with more than 1TB of data, periodically running a query that fetches recently added rows for indexing somewhere else. innodb_version: 5.6.36-82.1 version : 10....
user avatar
0 votes
2 answers
876 views

Create users identified by the same password as another user in MariaDB 10.0.X

How do I create users with the same password as another user in MariaDB 10.x? I do not know the password of the existing user , so i want to use same hashed password as existing, also do need to ...
user avatar
2 votes
3 answers
10k views

Which file do I need to modify my.cnf MariaDB on Ubuntu 18.04

I want to modify these values: [mysqld] key_buffer_size = 256M max_allowed_packet = 128M thread_stack = 256K query_cache_limit = 64M query_cache_size = 384M innodb_buffer_pool_size = ...
user avatar