Questions tagged [mariadb-10.1]
MariaDB 10.1 was released as stable (GA) with version 10.1.8 on 17 Oct 2015.
116
questions
0
votes
2answers
14 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, ...
1
vote
1answer
82 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
2answers
24 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
2answers
135 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
0answers
110 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
1answer
54 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
2answers
1k 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
0answers
68 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
0answers
404 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 ...
0
votes
2answers
424 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
1answer
63 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), .* ...
0
votes
2answers
781 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 ...
0
votes
1answer
21 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,
...
0
votes
1answer
50 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
vote
0answers
1k 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
1answer
533 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 ...
0
votes
1answer
1k 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
...
-1
votes
1answer
78 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
1answer
111 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 ...
0
votes
1answer
28 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,
...
1
vote
0answers
513 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:
...
0
votes
1answer
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
0answers
257 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 ...
1
vote
1answer
883 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.
...
2
votes
2answers
3k 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
1answer
670 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 ...
2
votes
2answers
192 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....
0
votes
1answer
379 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
1answer
380 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
1answer
589 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 ...
0
votes
1answer
228 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 ...
2
votes
1answer
178 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
2answers
260 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):
+---------+---------+------------+-------------+-------+-------+
| ...
-1
votes
1answer
113 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
2answers
2k 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
1answer
490 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 ...
0
votes
1answer
965 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
...
0
votes
0answers
27 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
1answer
5k 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 ...
0
votes
2answers
126 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 ...
0
votes
1answer
101 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....
1
vote
3answers
196 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 ...
0
votes
1answer
48 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 ...
0
votes
2answers
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;
...
0
votes
2answers
591 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
3answers
9k 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 = ...
1
vote
0answers
71 views
MariaDB Cluster Cannot Join new Node
This is status on Main node. it's show some seqno as 17210843
when new empty node is join. i'm found rsync process is ok. but i see difference seqno in linux process
after finish this process my ...
1
vote
1answer
134 views
How does STRICT_ALL_TABLES work with multi-row statements and partial updates?
In the docs for STRICT_ALL_TABLES it states,
For STRICT_ALL_TABLES, MySQL returns an error and ignores the rest of the rows. However, because the earlier rows have been inserted or updated, the ...
1
vote
1answer
378 views
greatest n per group with a 3 tables join
Working on a project with following objects:
People
id
Vehicles
id
model: text
and a link table for owners which passes through owners changes
Owner_Changes
old_person_id
new_person_id
...
0
votes
0answers
902 views
WITH clause in Maria DB
I am trying to run below query in Maria DB (10.1.26-MariaDB-0+deb9u1 Debian 9.1)
with ind as
(
select index_name,
table_name,
group_concat(column_name) column_name,
...
