New answers tagged mariadb
0
votes
Database becoming unresponsive after time (4 to 24 hours)
I have probably found the cause and it's a weird edge case.
I have the cts (from proxmox) running on lvm instead of lvm-thin. And had a backup job using snapshot for the database every 4 hours. My ...
- 11
0
votes
How to convert a string year to a DATE
Use string operations instead:
mysql> SELECT CONCAT('2007', '-01-01') + INTERVAL 360 DAY;
+---------------------------------------------+
| CONCAT('2007', '-01-01') + INTERVAL 360 DAY |
+-----------...
- 68.1k
0
votes
Accepted
Auto assign value-message to rows in a table with MySQL trigger
In MySQL backticks are used for table and column identifiers and are necessary when the identifier is a MySQL reserved keyword or when the identifier contains whitespace characters, even though it is ...
- 956
0
votes
Inconsistent results with MySQL, query never ends on MariaDB
Indexes needed:
table1: INDEX(location_id, organization_id, status, id)
link1: PRIMARY KEY(table2_id, table1_id)
link1: INDEX (table1_id, table2_id)
LEFT JOIN ... ON EXISTS (...) is a very ...
- 68.1k
5
votes
Accepted
Is an SSL Certificate Authority Chain "Secret"
You do not need to treat that certificate chain as secret. It is just like the public cert you can view when connecting to a website like stack overflow. The private certificate is being used server ...
2
votes
Accepted
Junction table or denormalize?
A junction table isn't going to affect your performance here, especially with a process that only runs at a frequency of every hour.
NoSQL doesn't buy you any advantage here either, and IMO should ...
- 24.2k
-2
votes
Mysql service down with [ERROR] InnoDB: Page log sequence number is in the future
This issue will usually happen after one of the following changes:
If the InnoDB log files were deleted and one or more of these events preceded that deletion (none of the events can on their own ...
0
votes
prevent a galera node from bootstrapping
The grastate.dat file is where the host finds the information about its current state. This includes the UUID, the seqno, and the safe to bootstrap parameter.
When the grastate.dat file does not exist ...
- 1
0
votes
MySQL's maximum memory usage is dangerously high
Analysis of GLOBAL STATUS and VARIABLES:
Observations:
Version: 10.2.43-MariaDB-log
7.6 GB of RAM
Uptime = 00:49:57; Please rerun SHOW GLOBAL STATUS after several hours.
153 QPS
The More ...
- 68.1k
0
votes
Change a value (just a flag 0/1) depending on an entry in another table and - important - leave everything else unchanged
You can write proper INNER JOIN UPDATE clause, like :
UPDATE job_postmeta jp
INNER JOIN job_term_relationships jtr on jp.meta_id=jtr.object_id
SET meta_value = '1'
WHERE meta_key = '_remote_job'
and ...
- 956
0
votes
MySQL's maximum memory usage is dangerously high
Rate Per Second = RPS
Suggestions to consider for your my.cnf [mysqld] section novaterr.com
innodb_buffer_pool_size=4G # from 128M to reduce innodb_buffer_reads RPS of 802
...
- 1,599
0
votes
Create a event inside a procedure
You cannot create an event procedure within stored procedure.
The reason is because I would like to have a procedure I can manually trigger, and then run a one time event 3-4 hours later.
This is ...
- 17k
0
votes
mariadb: innodb_file_per_table=On and still have a huge ibdata1
Tables built before turning on innodb_file_per_table = 1 still live in ibdata1.
You can set the flag and do an ALTER to move a table out of ibdata1, but...
When space freed is up in ibdata1, it is ...
- 68.1k
1
vote
mariadb: innodb_file_per_table=On and still have a huge ibdata1
Caveat: I don't use MariaDB, so I don't have in my recall which versions of MariaDB have some of the features I describe below regarding the system tablespace. MariaDB started as a fork of MySQL and ...
- 9,338
0
votes
Mariadb Low transaction and query per second
To help other DBAs I wanna add some sentences from the book "O'Reilly High Performance mysql" to Bill's answer:
What Not to Do
You might be expected (or believe that you’re expected) to set ...
- 133
1
vote
Accepted
Create new MySql user with same grants as existing user
Rather than copying the grants over, consider creating a "role" with suitable privileges. Then GRANT a role TO a user.
MySQL:
https://dev.mysql.com/doc/refman/8.0/en/roles.html
https://dev....
- 68.1k
2
votes
Limiting and queueing the database connections
max_connections sets a limit. back_log, in theory, provides a queue of pending connections.
But, in reality, you should change your clients to be less demanding. If you let hundreds of connections ...
- 68.1k
2
votes
Accepted
Limiting and queueing the database connections
In addition to the old resource limits inherited from MySQL, MariaDB grant limits can include MAX_STATEMENT_TIME (in seconds), for each query.
There is also wait_timeout and interactive_timeout to ...
- 6,380
2
votes
Keep procedure running even connection dies?
One solution could be to use the EVENT system:
Events are named database objects containing SQL statements that are to be executed at a later stage, either once off, or at regular intervals.
They ...
- 201
1
vote
Accepted
Mariadb Low transaction and query per second
I notice you have 20 CPU cores, but you're only running sysbench with 2 threads. You probably can get better results with more threads. The guideline often given is 2x the number of CPU cores, so try ...
- 9,338
0
votes
Accepted
Command SHOW MASTER STATUS different in master and slave
No problem.
A server can be a Primary or a Replica or both or neither. Your Replica seems to be both a "Master" (as indicated by SHOW MASTER STATUS) and a "Replica" (as indicated ...
- 68.1k
0
votes
Command SHOW MASTER STATUS different in master and slave
There is a strong likelihood you forgot to configure log-slave-updates on the slave. If you are using MySQL 8.0.26 and above, it would be log_replica_updates.
Please add this line to your my.cnf if ...
- 173k
Top 50 recent answers are included