New answers tagged

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 ...
user avatar
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 | +-----------...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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....
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar

Top 50 recent answers are included