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
1answer
12 views

LEFT JOIN based on content of a field in matched rows

Generically, I'm attempting to take a table and join it with another table but only on rows that have a specific value in one of the fields. Specifically, I have a users table and a contact table. ...
0
votes
1answer
23 views

Do the type-specific WKT- constructors function differently from ST_GeomFromText?

From the docs on MariaDB, for ST_LineStringFromText Constructs a LINESTRING value using its WKT representation and SRID. ST_LineFromText(), ST_LineStringFromText(), ST_LineFromText() and ...
0
votes
0answers
25 views

Error #1064 when executing a SELECT query

I'm new to PHP and MariaDB and ran into a little trouble with a learning project I'm working on. Whenever I try to execute the query SELECT c.complaint_id, GROUP_CONCAT (DISTINCT ...
0
votes
1answer
14 views

Native MariaDb encryption and clustering

Is native (table/Column/tablespace) database encryption possible in a clustered environment. The success criteria would be: Encrypted Logs Encryption in transit for all data protocols Native DB ...
0
votes
1answer
13 views

How do I construct a LINESTRING by aggregating Points (ST_MakeLine) in MariaDB?

Let's say I have a table with 3 points, CREATE TABLE foo ( id int PRIMARY KEY, geog point ); INSERT INTO foo (id, geog) VALUES ( 1, Point(0,0) ), ( 2, Point(1,1) ), ( 3, Point(2,2) ); How can ...
0
votes
1answer
10 views

Move data with schema change between MariaDB servers

I am tasked with the challenge to move data between two MariaDB servers. The source database is a MariaDB 5.x, and the destination database is a MariaDB 10.x. The problem is that I need to add a ...
0
votes
1answer
25 views

Can you update invisible columns

I'm trying to update the row_start column on a table with Versioning set. The row_start column is invisible. UPDATE pgwebp.t_prov a INNER JOIN pgwebp.t_prov b ON a.prov_id_a = b.prov_id SET a....
0
votes
0answers
10 views

MariaDB 10.2 crash on innodb table corruption - trying to restore the database

I'm running MariaDB 10.2 on Windows and had some hard drive corruption on my server that messed up some innodb tables. Unfortunately this also affected backups, so I'm trying to dump the tables and ...
0
votes
0answers
15 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
1answer
15 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
19 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
16 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
801 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
17 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
25 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
24 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
49 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
17 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
14 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
16 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
24 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
23 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
28 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
97 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
35 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
28 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
35 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
22 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
38 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
26 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
64 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
27 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 ...