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

Questions tagged [mariadb]

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
0answers
4 views

MariaDB Remote machine Data at rest encryption

I'm work on setting up a MariaDB local instance on laptops that will spend most of their time disconnected/unable to connect to the internet. One of the requirements of the project is to encrypt ...
0
votes
0answers
11 views

how to remove Mariadb temp files

I have a /mariadb_tmp directory (as specified in my mariadb-server.cnf) which is filled with files dating back to last November. -rw-rw---- 1 mysql mysql 1032192 Nov 19 07:47 #sql899_5d5_0.ibd -rw-...
0
votes
1answer
15 views

Error during mysqldump on performance_schema.query_cache_info

On a production server MariaDB 10.2.13, I actived the plugin query_cache_info. Suddendly for 5 days, during the daily batch doing dumps (with mysqldump), I have this error : mysqldump: Couldn't ...
1
vote
1answer
15 views

Mariadb: Cannot decrease max_connections to 1, ERROR 1231 (42000): Variable 'max_connections' can't be set to the value of '1'

For doing updates on databases I reduce the max_connections variable to 1 (the remaining for root access). I found it the fastest solution to lock the database for other users during updating. This ...
4
votes
4answers
794 views

Length of varchar column - is there an advantage to using 255? [duplicate]

I often come across columns that are varchar(255), so presumably this is a convention of sorts. 255 is 2^8 - 1. So is there something 'magical' about lengths that are 2^n - 1? Or just specifically 255?...
0
votes
0answers
12 views

MariaDB GTID current_pos vs slave_pos

In a standard master-slave setup using MariaDB (10.3 at this time) and GTID (Global Transaction ID), it is unclear wether to use current_pos or slave_pos. Reminder from the documentation: Using ...
0
votes
1answer
22 views

How do you create a Geographic Point with MariaDB and MySQL?

PostGIS provides a simple cast to construct a geography type from floating point lat, and long SELECT ST_Point(1,2)::geography; -- standard version (MM) less precision SELECT ST_MakePoint(1,2)::...
0
votes
1answer
22 views

MySql incremental backup of added rows only

How can I create incremental backups of a MySql database that only includes data that is added and ignores data that has been removed? Background here is that I have an instance of home-assistant ...
0
votes
2answers
38 views

MySQL my.cnf recommended settings MariaDB 10.2

I am a beginner managing a server and I have a problem after upgrading from MySQL 5.7 to MariaDB 10.2. Performance usage is very high: 191%. Can somebody help optimize values in my.cnf for a database ...
0
votes
1answer
14 views

Do I need to `mysql_upgrade --verbose -u root -p` each time when `source backup.sql`?

I am upgrading mysql 5.1 to mysql 5.5 in centos 7 server. As time limit,I only mysqldump and source several small database to mysql 5.5,and then mysql_upgrade --verbose -u root -p,everything is ok. ...
0
votes
1answer
13 views

Running Mariadb on LXD (using proxmox) vs baremetal

I have benchmarked lxd and the difference has not been more than 2-3% of the bare metal performance. I am using fusion sx300 1.6tb to store a 450gb database with 20b rows in a single table. We have ...
1
vote
3answers
70 views

Is it possible to optimize this query? Currently taking days (no sign of stopping) on ~11 million records

Table in question: CREATE TABLE `observations` ( `time_of_observation` datetime NOT NULL, `station` varchar(5) NOT NULL, `number` varchar(20) NOT NULL, `destination` varchar(100) NOT NULL, `...
0
votes
1answer
19 views

Unable to create an user using mysql_embedded

I am creating a docker mariadb image and wanted to create a database and user name before bootstrapping mysqld daemon. So as per the documentation I tried below queries. mysql_embedded -e "CREATE ...
0
votes
1answer
15 views

MariaDB special Characters

I have a database on a hosting service that was recently move and upgraded without much notice which has caused me many problems. This problem, however, seems to arise from an upgrade from MySQL to ...
0
votes
1answer
22 views

Galera cluster node fails with error. Brand new cluster + master / node. Access denied or can't connect

I am having issue. Previous cluster fatally crashed, so I've created a new one with pretty much same settings (changed cluster name only). Here is a galera.cnf for a node: [mysqld] binlog_format=...
0
votes
1answer
26 views

How to enable NULL values in MySQL timestamp fields?

I need to restore backup from one MariaDB server (version 10.1.23) to another MariaDB server (version 10.1.37). But this version has a much stricter mode by default and does not allow NULL values in ...
0
votes
1answer
14 views

How to lock all tables except some specific table?

I am trying to set a stage server which replicate some tables like articles, user etc from master. If I start the slave server as read_only I am not able to my stage site up because there are some ...
0
votes
1answer
22 views

using tmpfs for mariadb tmp tables

My server is writing about 50% of temp tables to disk, even when max_heap_table_size and tmp_table_size are ridiculously high (500M). Yesterday I ran a script all day that monitored the tmp folder and ...
0
votes
0answers
26 views

Dose galera cluster perform read load balancing?

Hi Now I'm doing benchmark test with several type mariadb replication with sysbench. 1 Master - 2 Slave MariaDB on kubernetes with ceph stroage. 3 Master with Galera Cluster on kubernetes with ceph ...
0
votes
5answers
87 views

Managing mariadb database performance with very large tables

I have a joomla-3.9 and apache-2.4.34 system on fedora29 with mariadb-10.2.19 and the tables that contain the search content are very large. Several are larger than 1.2GB and one is larger than 5.5GB. ...
0
votes
1answer
17 views

perform non locking select when updating another table

We need to perform the following (periodic) query in mariadb: update A set status='INACTIVE' where status='ACTIVE' and a.b_id in (select id from B where ... LOCK IN SHARE MODE); Unfortunately it ...
0
votes
1answer
34 views

is vertical scaling good idea for a mysql database server?

I operate a mariadb/mysql database server and i do not want to get into the problems with managing a multi-cluster database server i have access to huge CPU and RAM and STORAGE when needed so my ...
0
votes
1answer
8 views

How to set mariadb read_write for some specific tables while starting as read_only=1

I am trying to set a mariadb slave which is exact replica of master db. As per the suggestion I start my slave with command --relay-log=mysql-relay-bin" "--log-slave-updates=1" "--read-only=1" . But ...
0
votes
2answers
38 views

INNER JOIN is extremly slow on some rows and create temp table

The MySQL query SELECT `p`.*, `p.Author`.* FROM `post` AS `p` INNER JOIN `user` AS `p.Author` ON `p`.`userid` = `p.Author`.`userid` WHERE ( ( `p`.`threadid` = 29780 ) AND ( `...
0
votes
1answer
26 views

MariaDB replication from master to slave

After doing some research around this topic, I should be attempting this task soon. However, I have two questions going around my head - could do with an experienced professional answering them. I am ...
1
vote
0answers
15 views

MariaDB instance hangs [closed]

I have a MariaDB instance which was created from an official docker image. Sometimes when I try to execute many queries [like drop database, mySQL dump sourcing] via a PHP script, then the entire ...
0
votes
1answer
31 views

opened_files large, open_files tiny

on my mariadb server OPENED_FILES is currently 2,775,453 and OPEN_FILES never seems to exceed 65. I understand that opened_files is cumulative and is meaningless without a time period. ...
0
votes
1answer
26 views

MySQL/MariaDB Read only except for a single table

I Have a database with lots of tables and read_only enabled in the config, is it possible to set a single table to be writable?
0
votes
1answer
32 views

MariaDB defragmenting with new option innodb-defragment=1

I try to defragment table using innodb-defragment=1 https://mariadb.com/kb/en/library/defragmenting-innodb-tablespaces/ But it was a bad experience : - locks on table - no a good defragmentation ...
0
votes
0answers
27 views

Set up table for relationship type with both users in a different visitor table

I am a newbie in database driven application, and I just started working on a web app for my brother and parents clinic to manage patient records. So for their clinic I have one table Visitor that ...
0
votes
1answer
21 views

How to active mariadb log?

I want active log file in mariadb for check last query used in php, i find mysql config in internet like as blow: general_log_file = /var/log/mysql/mysql.log general_log = 1 or ...
1
vote
1answer
33 views

Can't create/write to file '/var/log/mariadb/mariadb.log'

I'm troubleshooting a MySQL no-start on CentOS 7 VM. It is a bit unusual to see this since we have not made any configuration changes. The manual start command was taken from systemd logging. The ...
1
vote
1answer
17 views

Audit Plug-in for MYSQL 5.7.24 error

I have tried installing MariaDB audit plugin for MySQL 5.7.24. I have downloaded the 'server_audit.so' from MariaDB-10.3.11 and I think it is compatible with MySQL 5.7.24. But I have encountered this ...
0
votes
1answer
23 views

MariaDB Galera Cluster - first node won't reconnect after restart… Ever

Firewalls disabled, replication worked fine, IP's accessible.. All config copied from here: https://www.digitalocean.com/community/tutorials/how-to-configure-a-galera-cluster-with-mysql-5-6-on-ubuntu-...
3
votes
1answer
62 views

Reuse previous value in window function

Is it possible to use LAG() to get the previous value of the current column? The computation I need to do is the following: value(n) = (value(n-1) + 1) * column(n) meaning I already have a column, ...
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
0answers
16 views

Mariadb / mysql table partitionning column usage

I read mariadb documention :https://mariadb.com/kb/en/library/partition-maintenance/ And it sayed : The partition field should not be the field first in any key But in mysql documentation :https:...
1
vote
2answers
27 views

Do VIEWS need a primary key in Galera?

We have a MariaDB Galera Setup, so do we require primary keys in a MySQL view? It contains a complex select query with joins of tables and the values get updated. so do they replicate in a Galera DB ...
2
votes
1answer
26 views

step interpolation

My data: +------------+-------+ | date | value | +------------+-------+ | 2018-12-01 | 10 | | 2018-12-09 | 15 | | 2018-12-17 | 12 | +------------+-------+ My goal: +------------+-----...
0
votes
1answer
16 views

Multiple columns from single table

My tables: categories: +----+------+ | id | name | +----+------+ | 1 | blue | | 2 | red | +----+------+ data: +----------+----------+-------+ | date | category | price | +----------+-------...
0
votes
0answers
18 views

Is ALTER TABLE … MODIFY column faster than CHANGE column?

In versions of MariaDB or MySQL, is ALTER TABLE mytable MODIFY column faster than ALTER TABLE mytable CHANGE column column? Or is there any other advantage of using MODIFY other than the shorter ...
0
votes
1answer
9 views

Character set warning in PXC logs

I am using PXC 5.6 (Percona-XtraDB-Cluster-56-5.6.41-28.28.1.el7.x86_64) and my log file is flooded with this warning: [Warning] Client failed to provide its character set. 'latin1' will be used ...
0
votes
1answer
29 views

Auto increment gap in percona xtradb cluster master master setup

I was using mariadb 5.5 before but now I have upgraded it and using percona xtradb cluster 5.6 I have 3 nodes cluster. I am only using single node for read and write. I saw some increment gaps in ...
1
vote
1answer
40 views

Prepared statement too slow

have an issue with MariaDB 10.2.12 running in aws rds (single master, no slave) when I run prepared statement using a variable substitution PREPARE stmt1 FROM "select ...quite complex select from ...
1
vote
0answers
15 views

Create a table via an INSERT trigger in MariaDB

I'm trying to make a small database I can use to log changes I make to configuration files on various Linux systems. My thought was have a Database named for the system, and then a table called ...
0
votes
0answers
17 views

Why doesn't my profile of my procedure seem to account for the duration?

I'm running 10.3.10-MariaDB-1:10.3.10+maria~bionic locally in Docker and I'm profiling a procedure call like so via adminer: SET SESSION profiling = 1; CALL `add_user_to_role_in_unit`; SHOW PROFILES; ...
-1
votes
0answers
13 views

mysqldump from MySQL5.1 to MariaDB 5.5 breaks Wordpress site

We have an unstable Windows installation of Wordpress that uses MySQL 5.1.73. I am trying to migrate/upgrade this to MariaDB 5.5.62. I am creating a mysqldump of the Wordpress DB, uninstalling MySQL 5....
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
28 views

For each implementation in joins

deliveries Table has customer_id and the item_id they want to buy. items table has items with item_id as primary as below deliveries items id customer_id item_id ...
1
vote
1answer
63 views

Anyone understand why this query no longer works? [closed]

On Localhost this query works correctly, running normal MySQL: SELECT COUNT(*) as rows, customers. * FROM customers WHERE user_id = 6 AND age = 10 ORDER BY timestamp DESC LIMIT 0 However ...