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 ...
