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