Questions tagged [mariadb-10.1]
MariaDB 10.1 was released as stable (GA) with version 10.1.8 on 17 Oct 2015.
120
questions
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 ...
- 135
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"?
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,
`...
- 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, ...
- 103
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 ...
- 121
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 ...
- 121
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='...
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 }}"
...
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 ...
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 ...
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 ...
- 171
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 ...
- 135
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 ...
- 441
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), .* ...
- 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 ...
- 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,
...
- 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 ...
- 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
...
- 11
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 ...
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
...
- 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;
...
- 1
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 ...
- 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
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:
...
- 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) ...
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 ...
- 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.
...
- 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?
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 ...
- 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....
- 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 ...
- 149
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 ...
- 537
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 ...
- 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 ...
- 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 ...
- 149
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):
+---------+---------+------------+-------------+-------+-------+
| ...
- 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 ...
- 99
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 ...
- 135
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 ...
- 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
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 ...
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 ...
- 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 ...
- 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....
- 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 ...
- 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 ...
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;
...
- 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....
- 73
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 ...
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 = ...
- 21