Questions tagged [mariadb-10.1]
MariaDB 10.1 was released as stable (GA) with version 10.1.8 on 17 Oct 2015.
-1
votes
0answers
24 views
Attendance Report Pivot Table - Mysql
I have 4 tables in my database:
students (studentid,studentname)
subjects (subjectid, subjectcode,subjecttitle)
attendance (id, studentid, subjectid, attendance_date)
student_subjects (studentid, ...
0
votes
2answers
47 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
31 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
27 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....
0
votes
0answers
49 views
Stuck processes SHOW GLOBAL STATUS in MariaDB Galera cluster after writing data and reboot
There are 3 nodes in MariaDB Galera cluster running on 3 VMs, one per Vm.
Some transactions were applied on one of the nodes, then one VM was rebooted.
Automatic script starts mysqld process after VM ...
0
votes
0answers
32 views
How to convert the SQL query from MariaDB 10.3 to MariaDB 10.1/MySQL 5.6
Since I need to convert the below query which should work on MariaDB 10.1/MySQL 5.6.
When I tried to run the below query I used to get the error as
#1064 - You have an error in your SQL syntax; ...
1
vote
3answers
69 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
25 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
44 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
43 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
0answers
211 views
Error with GaleraCluster> [ERROR] WSREP: gcs / src / gcs_group.cpp: gcs_group_handle_join_msg (): 736: Will never receive state. Need to abort [closed]
I made a cluster with GaleraCluster using 3 virtual machines with Debian 9 and mariadb 10.1 database. The replication works well using rsync method, even if a node is dropped when it recovers, it ...
1
vote
1answer
526 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
29 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
54 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 ...
0
votes
0answers
165 views
MariaDB : Unable to lock ./ibdata1, error: 11
I'm using mariadb 10.1.26. I have added disk space and RAM to my server. When I tried to start the SQL service it is starting, but the error log shows the below error.
InnoDB: Can't open './ibdata1'...
1
vote
1answer
128 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
110 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
22 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
34 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 ...
0
votes
0answers
452 views
MariaDB mysql service failing to start (core-dump)
I have Fedora 25 (not a server), mariadb was working fine and all of sudden it can't start.
When I try to start the service I get:
Job for mariadb.service failed because a fatal signal was delivered ...
3
votes
1answer
288 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
126 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
36 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
264 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
75 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
140 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 ...
0
votes
1answer
135 views
mariadb, add 4th galera node failed
I have three nodes setup and running perfectly for the past months.
Recently I want to add another node in a different location but somehow I keep on getting errors.
At first, I was just following ...
4
votes
1answer
1k 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
532 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
499 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
57 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....
1
vote
1answer
352 views
What are the options for archiving old data of mariadb tables if partitioning can not be implemented due to a restriction
We are planning to archive some old data of our MariaDB tables to some other volume(or mount point) of the linux server apart from the one the tables are on.
We are using partitioning for this and ...
2
votes
2answers
290 views
why I see only COMMIT or commit in SHOW FULL PROCESSLIST - No SQL statement
I see in lots of output from SHOW FULL PROCESSLIST only COMMIT or commit (lowercase and upercase mix).
What are those transactions? Why no SQL statement? We are running MariaDB 10.1.x and Galera ...
0
votes
0answers
153 views
Thousands of MySQL connections with no visitors
I'm running a vps (Ubuntu 14.04+NGINX+Mysql) with 8GB memory with a couple of wordpress sites.
Since several days ago the Mariadb-bin log is growing at 150 MB per hour rate. I discovered it because ...
1
vote
1answer
78 views
how to install a second server of mariadb
I have a server where cpanel is installed (it includes mysql 5, php). What I need is to install a second database server mariadb 10.1.20. I tried through:
yum install MariaDB-10.1.20-centos6-x86_64-...
2
votes
1answer
348 views
MariaDB audit plugin logs all queries instead of DML only
I was testing the "server_audit" plugin of MariaDB, which I configured like this:
plugin-load-add = server_audit
server_audit_logging = ON
server_audit_events = QUERY_DML
server_audit_output_type = ...
0
votes
1answer
47 views
I can't add extra node after reset root password mariadb 10.1.20
I have a mariadb 10.1.20 cluster with 5 nodes.
I don't know root password so I did the following steps:
On second node, I stopped mysql service
I set wsrep = OFF on /etc/my.cnf.d/server.cnf
I ...
3
votes
2answers
237 views
Changing master for channel gives syntax error
I'm trying to set up a multi-source replication
When trying to set a master for a specific channel using this query
CHANGE MASTER TO MASTER_HOST='192.168.0.203', MASTER_USER='rep', MASTER_PORT=3306, ...
2
votes
2answers
7k views
Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2 “No such file or directory”) when configuring cluster
I have 3 MariaDB nodes.
Want to configure cluster.
Specified in gcomm:// in my.cnf ips of all members in cluster according to this.
And added ?pc.wait_prim=no at then of gcomm://ip1,ip2,ip3
Then ...
0
votes
1answer
106 views
I can't add an extra node to mariadb cluster 10.1
I was installing a test mariadb cluster but I have a problem when I want to add an extra node. Both nodes use Red Hat Enterprise Linux Server release 7.3 (Maipo)
These were the following steps:
On ...
1
vote
0answers
23 views
Best setting of MadiaDB database Server (version: 10.1.29-MariaDB) for data about 400GB
I have a single server with 128GB and 64 core CPU.
I need to compare multiple tables (about 40 tables) having ~400GB data.
The maximum size of a table has ~60GB data for upload as CSV file.
With the ...
0
votes
1answer
674 views
InnoDB Write Log efficiency above 100%
After running mysqltuner.pl, I get an Innodb write log efficiency way above 100 %. Why? What does it mean?
There is no specific reccomendation from mysqltuner.
DB is MariaDB 10.1.26 on Debian 9.x ...
1
vote
0answers
32 views
Query doesn't end if limit is not added - AWS RDS MariaDB [closed]
I am currently migrating some databases from a physical server running MySQl over to AWS RDS. Current server uses MySQL 5.5 and the new RDS one uses MariaDB 10.1. Everything has been going smoothly ...
0
votes
0answers
74 views
Move a node from cluster to be a slave of the cluster in Galera
I have 5 Node in Galera cluster. MariaDB Server 10.1.21-MariaDB-1~trusty.
I will remove 2 nodes from it but one of them will be a slave. So from 5 nodes 5th one will be completely removed, 4th one ...
3
votes
1answer
612 views
Define calculated column of BOOLEAN/TINYINT(1) type in a View? [duplicate]
I'm configuring a View which contains calculated columns that should be returning Boolean results. Here's an anonymised version of the view:
CREATE VIEW `ItemView` AS
SELECT Item.ID
,...
4
votes
1answer
49 views
Add Primary Key column at the end of an Index
If I have a table with the following primary key:
PRIMARY KEY (id)
And I want to query documents written by someone (e.g. display a preview of the latest 20), sorted by their "id", so I created an ...
0
votes
1answer
231 views
Internal MariaDB error code: 1927 ('Connection was killed')
Edit: filed a bug report at https://jira.mariadb.org/browse/MDEV-14493
Mariadb nodes freeze occasionally with the following error in the logs, any clues?
(drupal app, db freezes on clearing ...
-1
votes
2answers
983 views
Mariadb minimum CPU requirements (Edit: Still crashing, new question with log linked)
EDIT3: Read-write splitting seems the answer as pointed by out the user dbdemon in the linked question.
Internal MariaDB error code: 1927 ('Connection was killed')
EDIT2: https://drupal....
0
votes
1answer
43 views
Questions for 'Tips on Converting to Galera' on MariaDB KB [closed]
Some questions about the excellent writing Tips on Converting to Galera.
Is "cross-colo writing" cross connect datacenters write?
Also, you should use "START TRANSACTION READONLY" wherever
...
0
votes
1answer
71 views
mysqldump to a hostname which is proxy/router (not MySQL server)
I do a mysqldump with those options --single-transaction --routines --events --max_allowed_packet=1G --flush-logs --master-data=2. The desination hostname is not a MySQL server, it's a proxy/router. I ...
