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
1answer
27 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
41 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
12 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
45 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; ...
0
votes
0answers
21 views

MariaDB Galera: the node, that needs to be joined to others, starts as “wsrep-new-cluster”

We have 4 node Galera Cluster (10.1.14-MariaDB, wsrep_sst_method=xtrabackup-v2). The 1st node successfully started with "/usr/bin/galera_new_cluster" command. The 2-nd and the 3-rd nodes are ...
1
vote
1answer
31 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
23 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
75 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
36 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
62 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
94 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. ...
0
votes
0answers
11 views

Access a file on diffferent drive using connect driver on Windows XP

I need to access a *.DBF file on a network drive using CONNECT database engine. When I use a file on the C:/ drive, it works as intended, but when I move the file to a remote drive, it says "no such ...
0
votes
2answers
168 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
177 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
149 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
46 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
110 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
248 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
39 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
109 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
84 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): +---------+---------+------------+-------------+-------+-------+ | ...
0
votes
1answer
77 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 ...
3
votes
2answers
567 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
315 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
479 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
19 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
2answers
97 views

sql 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 12.58 06 ...
0
votes
2answers
57 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
67 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
125 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
45 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
429 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
135 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 ...
1
vote
2answers
3k 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
60 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
93 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
278 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
495 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, ...
0
votes
1answer
36 views

How can I use user defined variables and `USE`?

I have some schemas, all with the same format. The format is: schema name, underscore, single digit (example: database_1). How can I loop through all of these schemas easily? I tried SET @num = 1; USE ...
0
votes
1answer
46 views

JOIN on “Latest Match” in “Order”

Having a hard time explaining my scenario and what I am trying to accomplish. I have two tables like so, I have reduced the values for my scenarios Table1 +----------------------+ | ID | Time ...
3
votes
1answer
837 views

how to get 'Queries per second avg' using SQL (not 'mysqladmin status')

When I start mysqladmin, it prints out several statistics, including "Queries per second avg": # mysqladmin --defaults-file=../mylogin.cnf status Uptime: 4568115 Threads: 859 Questions: 3703806462 ...
2
votes
0answers
885 views

Mariadb connection fails through tunnel when bind address = localhost

I have a MariaDB 10.1.26 virtual server running on Debian 9. It is connected to via ssh tunnel and the bind address = localhost. It has been working without fail untill recently. Now when I connect ...
0
votes
2answers
88 views

WARN max_connections from pt-variable-advisor - what to configure for DBaaS?

Our VM configuration (hosted on Vmware) # cat /proc/cpuinfo |grep "cpu cores" | awk -F: '{ num+=$2 } END{ print "cpu cores", num }' cpu cores 32 # free -h total used free ...
0
votes
1answer
1k views

different settings for max_heap_table_size and tmp_table_size (MariaDB 10.1.x)

I run pt-variable-advisor and got a note about different settings for max_heap_table_size and tmp_table_size. With a web search I found only old articles (around 2007). pt-variable-advisor h=...
1
vote
1answer
256 views

pt-query-digest --type binlog (percona-toolkit) gives empty output with MariaDB/Galera 10.1

My installed versions of MariaDB and Percona Toolkit on MacBook: brew info percona-toolkit percona-toolkit: stable 3.0.10 (bottled), HEAD Percona Toolkit for MySQL https://www.percona.com/software/...
1
vote
1answer
271 views

Mariadb slow_query_log keeps disabling

I'm trying to enable slow_query_log on a Mariadb 10.1.32 server. When slow_query_log is enabled & flushed, logging is successful (slow queries are recorded and written to the file) for a minute ...
5
votes
1answer
3k views

MariaDB - “ERROR 1205 … Lock wait timeout exceeded; …” when doing multiple parallel UPDATEs affecting different rows

We are using MariaDB 10.1.19 hosting a legacy database. We have an lpr table (on InnoDB) where we want to move from a text column gate to a normalized foreign key gate_id. Our code is doing the UPDATE ...
3
votes
3answers
2k views

Can MariaDB or MySQL cast as bigint, getting an error: … right syntax to use near 'bigint)'?

If I want to take a string and create a table (this fails in strict mode), CREATE TABLE g AS SELECT CAST('2147483699' AS int); How do I tell it that I want a bigint type? I'll also get an int(10) ...
5
votes
2answers
1k views

Hex and Binary Literals in MySQL / MariaDB?

Another question I have pertains to just the & over string types. This question is over the binary-string literals as constructed with B'' or X''. MariaDB supports binary and hex literals, ...
1
vote
1answer
98 views

does the gcache.size matter during SST? What if more than gcache.size comes in during SST?

During SST my data dir looks like this (there is a hidden dir .sst with xtrabackup): -rw-rw---- 1 vcap vcap 113 Apr 16 08:13 grastate.dat -rw-rw---- 1 vcap vcap 265 Apr 16 08:13 gvwstate....