Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Open source RDBMS designed to be a "drop-in" replacement for MySQL. Add a version-specific tag like mariadb-10.2 if that context is important.

0
votes
1answer
12 views

MariaDB instance in cloud hit 95% CPU utilisation for 30 mins

I've got MariaDB instance in the cloud which I put live about 2 months ago. I'm new to the DBA business and I got the impression the people at the company who I run it through are learning on the hoof ...
0
votes
1answer
10 views

Audit MariaDB Which User Account Privileges Are Being Used

I'm looking to improve the security of my MariaDB installation by better auditing the user accounts and the privileges of each user account. This database is being provided as a service out to many ...
-1
votes
0answers
14 views

why is disk space used is different in mysql and shell? [duplicate]

here is MySQL query MariaDB [(none)]> select table_schema, sum((data_length+index_length)/1024/1024) AS MB from information_schema.tables group by 1; +--------------------+----------------+ | ...
1
vote
0answers
19 views

mysql_upgrade on fresh MySQL/MariaDB installation

What I've noticed lately is that whenever I install MariaDB or MySQL on a clean machine, and if I run mysql_upgrade it does the upgrading, so I figured since it's not showing it's already upgraded, I ...
0
votes
2answers
13 views

MariaDB “EXECUTE AS” or User Impersonation

With MariaDB, is there a way to impersonate a user execution context when connected with a higher privileged account? For instance, when connected with a "MasterUser" execute a SELECT using the more ...
0
votes
0answers
35 views

Error accessing Mariadb

After running the truncate command on a table with 4 GB, the database stopped working and I can no longer connect to the database. 2018-11-12 11:28:18 8748 [ERROR] [FATAL] InnoDB: The page [page id:...
0
votes
0answers
56 views

What does MariaDB do at midnight?

Does MariaDB do anything in particular at midnight and lasts for 1.5 hrs? I've noticed this over the past week that mysqld uses 100% CPU and 75% RAM usage slowing down the rest of my applications. ...
0
votes
2answers
36 views

Less traffic, But Mysql Still Hit upto 200%

I Run WordPress On my with with Less traffic, 5 or 6 online, but mysql till hits upto 200%, though sometimes it come down to 100 or less, but it still goes up to 200% again, the funniest thing is that ...
0
votes
1answer
16 views

How to track changes between nightly MySQL database dumps

I work with three MySQL databases (a, b & c) on a single server, that are nightly dumps of production databases. I have a fourth database (d) on that same server, that I have been given for ...
0
votes
1answer
12 views

Seconds_Behind_Master alternates between 0 and a number x

I have a slave server with mariadb 10.3.10 on debian 9. For several days the server has been working correctly, Seconds_Behind_Master was always kept at 0 indicating that it was properly synchronized. ...
0
votes
1answer
22 views

How to LOCK/BLOCK selected row in table to be ReadOnly?

Is it possible to LOCK completely or make READ-ONLY where id = value in MariaDB? or add some trigger locking if someone is trying to update selected row ? Some people says: "Trigger is not a good way ...
1
vote
1answer
66 views

MariaDB writes a lot on disk [on hold]

Configuration 10.2.15-MariaDB 20 GB RAM Total DB size - 16 GB, almost all tables are MyISAM. Some tables have rather big index files, about 400-700 Mb. Observations Writes about 4 GB per hour ...
0
votes
0answers
14 views

MariaDB Galera Cluster max_allowed_packet reset

We use a dockerized image for Galera Cluster, where initially I set the max_allowed_packet variable to 256m in the my.cnf file. This configuration is accepted by the database and validated by a later ...
0
votes
1answer
19 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
3answers
23 views

Reducing I/O operations for often-updating data in MariaDB

I have a big table where I update something like 10k rows per second on average, but just one column. Currently it looks to me that MariaDB writes those changes rather instantly onto the disk creating ...
2
votes
1answer
22 views

'kill <query id>' gives ERROR 1095 (HY000): You are not owner of thread … even though I am

I did an ALTER TABLE on a huge table on a MariaDB Galera Cluster 10.2, but after a while of waiting I changed my mind and tried to kill the connection. However, MariaDB said ERROR 1095 (HY000): You ...
0
votes
2answers
17 views

Find weighted random row. Or split 1 row into many based on column value

I want to query the DB and pick a random item weighted by the count. Something like so: SELECT * FROM (-- SUBQUERY THAT GENERATES WEIGHTED TABLE --) ORDER BY ct * RAND() LIMIT 1 I'm thinking the ...
0
votes
1answer
22 views

Slave Timeouts/Stopping (Networking?), Seconds_Behind_Master jumping

I have 2 slaves (10.1.34-MariaDB) which have been running smoothly for months. Suddenly, my lightsail/ec2 slave is behaving strangely. While SHOW SLAVE STATUS\G shows both IO/SQL are running, ...
0
votes
1answer
52 views

SQL optimization

I am trying to optimaze a query on mysql. Here is the table structure: TABLE FILE: id: int(8) primary key auto_increment filename: varchar(1024) The ideia is to create a navigation struture like ...
0
votes
1answer
30 views

MySQL consuming too much memory

Version : mysql Ver 15.1 Distrib 10.3.9-MariaDB OS : CentOS Linux release 7.5.1804 (Core) Specification : 384GB RAM Context : I am using this server only for database (with no other processes ...
0
votes
2answers
38 views

Log full of [Note] InnoDB: ft_end() after upgrade to MariaDB 10.3.10

My upgrade path was MariaDB 10.2.18 to MariaDB 10.3.10. There are no bugs reported on mariadb or mysql bug tracker (at least that I could find). ... Oct 24 17:45:33 host1 mysqld[12196]: 2018-10-24 ...
1
vote
1answer
20 views

Finding temporary table name in MariaDB

As I reported some days ago to MariaDB bug tracker the following bug: https://jira.mariadb.org/browse/MDEV-17420, slave replicas with version 10.2.x are leaking temporary tables on MyISAM files. Our ...
1
vote
1answer
26 views

WSREP: Writeset deserialization failed: Unsupported RecordSet version: 2: 71 (Protocol error)

We are running a Mariadb galera cluster with 3 data nodes. Recently one of them crashed and I had to do a SST (State Snapshot Transfer). Nothing I haven't done or seen before. However, after the SST ...
0
votes
0answers
20 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
1answer
36 views

mysql query to get time difference

I have query like this SELECT user_ID, tanggal, kode_layanan, nomor_antrian, aksi_ID,jam FROM `transaksi` WHERE tanggal = '2018-10-11' AND kode_layanan = 'A' AND aksi_ID BETWEEN 4 AND 9 and ...
0
votes
0answers
23 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; ...
0
votes
0answers
14 views

Backfilling data post migration

relatively inexperienced db admin here, please be kind :) I'm being asked to look into changing customer servers to write new data to a new MariaDB database and then export old data from the old ...
1
vote
3answers
66 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 ...
1
vote
1answer
16 views

innodb_log_file_size not upating even after changing my.cnf

Hello team this is my first post here, Server version: 10.0.31-MariaDB-0ubuntu0.16.04.2 Ubuntu 16.04 I am trying to import large db and half way I get the error to increase Size of InnoDB Redo Log ...
1
vote
0answers
42 views

MariaDB 10.3 slave fails to replicate from a MariaDB 10.2 master

We have a 10.2.18 master and 4 slaves with 10.2.18. Because of this bug that I've reported last week (https://jira.mariadb.org/browse/MDEV-17420) we tried to upgrade one of the replicas to 10.3.10 to ...
0
votes
2answers
69 views

Migration of MariaDB/MySQL to another server without downtime

System : MariaDB (MySQL 5.5) Parameters : innodb_file_per_table=1 The database I have now is approaching its full capacity (94% full now, 6.4TB out of 6.9TB) I can't run OPTIMIZE TABLE to reclaim ...
1
vote
0answers
28 views

Don't repeat COLUMN_GET in MariaDB dynamic columns WHERE clause

I have a MariaDB query like this: select id, column_get(params, 'Variant' as char) as variant from runs where column_get(params, 'Variant' as char) in ('Whatever', 'Wherever'); Is there a way to ...
0
votes
0answers
46 views

Quoting database names, usernames, passwords in MariaDB (“MySQL”) queries?

I am somewhat confused about quoting-characters (the characters which are used to ensure that query values are "quoted") in MariaDB. (Background: I am trying to programmatically create a short ...
0
votes
1answer
21 views

MariaDB Galera Cluster - Cert Deps Distance issue

I have mariaDB Galera 10.2 Cluster running with 3 nodes; in google cloud VMs with 8 cpu ; 15Gigs Ram / node; My slave_thread setting = 24 ; currently there is no much write happening and ...
1
vote
1answer
32 views

How do I add a partition to an existing table in mariadb / mysql?

I have the following table. I want to add partitions too. CREATE TABLE `app_log_Test` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `dateCreated` datetime NOT NULL, `host` varchar(512) COLLATE ...
0
votes
0answers
24 views

Automate merging of DB Changes, after successful Execution in Dataabase

I am trying to automate the DB Release for MariaDB. Our code repository is in Bitbucket. Is the following possible, Code added to the repository. Teamcity executes the changes in DB based on the ...
0
votes
1answer
8 views

Does mariadb `NO_TABLE_OPTIONS`, `NO_FIELD_OPTIONS`, `NO_KEY_OPTIONS SQL_MODE` change database content?

From https://mariadb.com/kb/en/library/show-create-table/, I read MariaDB and MySQL-specific table options, column options, and index options are not included in the output of this statement if ...
1
vote
1answer
21 views

MariaDB Query slower after mysqldump

I have a MariaDB server with several databases. On my MariaDB server, some queries (with joins between tables of different databases) are slower after the daily mysqdump (one file sql by database). ...
-3
votes
2answers
30 views

LEFT JOIN returns NULL in middle of query [closed]

Started working recently with joins again and I am trying to get the server name from a different table with matching ID's (Foreign Key). SELECT Website.`services`.`port`, Website.`services`.`...
3
votes
1answer
44 views

MYSQL database failover

Current status I have a MySQL (MariaDB 10.2) server with various databases, to which a lot of people have access to. Which means that users can create MyISAM or InnoDB tables/databases. Problem ...
0
votes
1answer
23 views

What's the disadvantage of encrypt mariadb?

I know if I encrypt mariadb, no one can steal my data. What's the disadvantage of encrypt mariadb?
1
vote
1answer
75 views

Status shows InnoDB: 128 rollback segment(s) are active waiting for purge to start

MySQL process was killed due to memory issues. When start command was issued, server started and is working but a status command shows the following: InnoDB: 128 rollback segment(s) are active. ...
1
vote
1answer
104 views

Mysql (MariaDB 10.3.9) can not start

I updated my mysql and I can't start it now. I try: sudo service mysql start Result: Job for mariadb.service failed because a timeout was exceeded. See "systemctl status mariadb.service" and "...
0
votes
2answers
49 views

Mariadb is giving warning for this query and table

Please create the following table, CREATE TABLE test3 ( meta_key varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL, meta_value varchar(500) COLLATE utf8mb4_unicode_ci DEFAULT NULL ) ENGINE=InnoDB ...
-1
votes
1answer
20 views

MariaDB query optimizer not using correct index

I got the following query and it was supposed to use index to speed up but it is using the wrong index image_likes and takes around 60 seconds to execute. If I add use index(image_user_id) then it ...
0
votes
0answers
83 views

InnoDB: Warning: a long semaphore wait

We are running Drupal 8.5.6 on a SLES12 SP3 guest VM with MariaDB 10.0.35. We recently cut over to a new server with a similar setup (same version of Drupal and MySQL but latest OS patches) and ...
1
vote
1answer
17 views

Get history of queries on MariaDB

I'm trying to get a table of recent slow queries on a MariaDB/MySQL server. MariaDB [(none)]> SELECT TRUNCATE(TIMER_WAIT/1000000000000,6) as Duration,SQL_TEXT FROM performance_schema....
1
vote
0answers
16 views

Is there a generic way to determine if a row can be deleted, i.e. doesn't have child rows?

Is there a generic way to determine whether a row in table parent has rows in "child" tables that are referencing it through their foreign keys? I want to be able to tell ahead of time if it can be ...
1
vote
1answer
31 views

Importing a mysqldump from MySQL 5.0.95 to MariaDB 10.0.36: Some tables incomplete

I have an ancient MySQL setup (5.0.95) that I'm trying to shut down. We've mysqldumped the databases we need, and we're trying to import them into MariaDB (10.0.36). We're running into some odd quirks,...
0
votes
1answer
22 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 ...