New answers tagged mysql
0
votes
Merging old backup in current database mysql
There is a solution to this, but you may not be able to employ it.
The solution is to use the binary logs, which contain serial events that updated data. The idea is that you would restore your backup,...
- 13.9k
1
vote
Accepted
MySQL - Is a user with the "REQUIRE SSL" option still able to log in without providing any SSL or CA certificate?
Specifying a user REQUIRE SSL does not mean the user requires certificates. SSL is for encrypted transport, but certificates are for authentication.
You can create a user with REQUIRE X509 option to ...
- 13.9k
0
votes
MySQL - Is a user with the "REQUIRE SSL" option still able to log in without providing any SSL or CA certificate?
You can configure mysqld using require_secure_transport
Whether client connections to the server are required to use some form
of secure transport. When this variable is enabled, the server permits
...
- 3,780
0
votes
Mysql query - Event to delete rows by its value
Basically, i want to create an event that every day delete rows where
check_in value in older than today.
Using MySQL 8.0.34:
CREATE TABLE ts (
c1 INT AUTO_INCREMENT PRIMARY KEY,
c2 TIMESTAMP ...
- 418
0
votes
Why does mysql binlog use different isolation level?
The code that outputs that line in the mysqlbinlog output is preceded by this comment:
https://github.com/mysql/mysql-server/blob/trunk/sql/log_event.cc#L12999-L13011
/*
The applier thread can ...
- 13.9k
0
votes
MySQL Drop Federated Table When Remote Server is Down
After additional troubleshooting, we discovered the following that resulted in us fixing the issue:
The process list had no information about our federated table.
Running the following query showed a ...
- 101
0
votes
How to find if my innodb row uses overflow/off pages?
Please try hexdump utility. This will help you to see if there are any 20 bytes row pointer in the ibd file and if this does exist then yes, your row is being stored as off page or overflow ( as says ...
- 669
1
vote
cpulimit/iolimit per mysql/mariadb user
As there's a pool of MariaDB threads, there's no fixed allocation to a user that could be used a cgroup limit.
Internal resource limits on users can apply and achieve CPU based limits in some crude ...
- 7,649
0
votes
Should I write to MySQL slave (replica) for reporting?
Replication in MySQL 8:
Source to Replica Replication:
The source is the primary, and there are one or more replicas, which
are secondaries. The source applies transactions, commits them and
then ...
- 418
0
votes
Accepted
No privileges to create databases
Login to phpmyadmin, and follow "Home" -> "Privileges" -> "Reload the Privileges".
- 69
1
vote
Accepted
how to speed up and optimize mysql search with indexes
Possible solution:
FULLTEXT(codfabrica, codoriginal, aplicacao)
and
MATCH(codfabrica, codoriginal, aplicacao)
AGAINST("various possible values" IN BOOLEAN MODE)
The reason for ...
- 76.9k
0
votes
select user with least amount of tiquets between all other users
Expanding on Akina's answer
select user, tickets from tablename
order by tickets limit 1
This selects both the user and the tickets for display. The query orders it in ascending order and then ...
- 1,482
0
votes
Error on observer while running replication hook 'before_commit'. On 200k rows update
Plan A: What "Error"? Probably a timelimit. That is a setting that can be changed.
Plan B: I prefer to do massive updates 1000 rows at a time.
The following discusses how
to walk through ...
- 76.9k
0
votes
MySQL SSL error: Unable to get private key
I got the same error.
But instead of converting the private key from PKCS#8 to #1, it was enough to change the owner of the ssl folder and its content (even though the rest of /etc/mysql/ was owned by ...
- 101
1
vote
Accepted
Reset root password in MySQL
ALTER USER 'root'@'%' IDENTIFIED WITH caching_sha2_password BY 'password';
This worked for me.
2
votes
innodb_buffer_pool_size complications
You don't need to calculate the pool size. Set innodb_buffer_pool_size to the total RAM you want to allocate to the buffer pool.
Let MySQL calculate the pool instances and chunk size. MySQL does this ...
- 13.9k
0
votes
MySQL query optimisation
UPDATEing a column in the PRIMARY KEY requires [essentially] deleting the row and inserting it somewhere else, plus updating all the indexes.
What does jid look like? If it is "chat.com" ...
- 76.9k
1
vote
MySQL query optimisation
To speed the query up a little bit you could add a where clause based on your comment
Is "chat.com" always at the beginning of jid? Yes
The jid column is already indexed and the following ...
- 3,780
1
vote
how to generate dummy data in mysql using stored procedure in relatable table?
Your schema is wrong.
You use FOREIGN KEY (country_id) REFERENCES country(country_phone_code). country_id is INTEGER whereas country_phone_code is CHAR(12). So the creation of the states table will ...
- 19.4k
0
votes
MySQL query optimisation
Your query is not sorting, so there's no way an index can help LIMIT. Your query is just using applying the update to the first 1000 rows that are read, as the table is scanned.
MySQL does quit ...
- 13.9k
1
vote
Accepted
Query for converting a column of numbers matching a regex in MySQL
If you need to change those things in place:
UPDATE projects
SET os_grid_ref = CONCAT(
SUBSTR(os_grid_ref,1,2), '5',
SUBSTR(os_grid_ref,3,4), '5',
SUBSTR(os_grid_ref,7,4)...
- 76.9k
0
votes
MySql binlog file getting truncated/shrinked during replication
There couldn't be a case where some logs are deleted due to crash, right?
No, I've never seen files be truncated. Binary log files are append-only.
It is normal for old binary log files to be removed ...
- 13.9k
0
votes
MySql binlog file getting truncated/shrinked during replication
It sounds like the length of the binlog was not stored in a timely manner.
Add confguration:
sync_binlog = 1
is probably the solution -- if this problem occurs after a crash and server restart.
- 76.9k
0
votes
Cannot import JSON file into MySQL
You can troubleshoot it.
Make sure MySQL version is newer than 5.7.8.
Check the validity of the JSON file
Check the field format
- 26
0
votes
Cannot import JSON file into MySQL
JSON data import using MySQL Workbench
I am using MySQL and the Workbench, both with versions 8.0.34 and an example JSON document in a file, eg., "myfile.json". This has one object in an ...
- 418
2
votes
CURDATE() is not valid at this position expecting an identifier
https://dev.mysql.com/doc/refman/8.0/en/create-table.html
Probably just missing parentheses around the expression:
HireDate date DEFAULT (CURDATE( )),
- 76.9k
0
votes
MySQL and PostgreSQL database table schema comparison
The only way I know of how to do this in a more automated way is by using a comparison tool, such as SQL Examiner. Note, I'm not affiliated with this tool in any way other than being a satisfied user ...
- 35.1k
0
votes
Why does MySQL puts ANALYZE queries into `System Lock` state, when there is intense I/O
Beginning with 5.6.6, innodb_stats_auto_recalc and Persistent stats virtually eliminated the need for ANALYZE TABLE.
I suggest you avoid doing the ANALYZE. If you still feel the need for such, please ...
- 76.9k
1
vote
Accepted
how percona hot backup works / innodb crash recovery works?
The LSN (Log Sequence Number) is analogous to binary log position, but the redo log is a different log. It applies only to InnoDB pages, and it contains physical diffs of the pages affected. During ...
- 13.9k
3
votes
Accepted
Why does MySQL puts ANALYZE queries into `System Lock` state, when there is intense I/O
https://dev.mysql.com/doc/refman/8.0/en/analyze-table.html says:
During the analysis, the table is locked with a read lock for InnoDB and MyISAM.
ANALYZE TABLE removes the table from the table ...
- 13.9k
0
votes
MySQL Stored Function - How to get @@warning_count?
I had basically the same problem with the same function: the check against @@warning_count just wasn't reliable. I don't know if it was a race condition or what, but that was my best guess.
I changed ...
- 101
-2
votes
How to design database system of an online toy shop?
There are essentially 3 types of Relationship:
1-to-1 -- This should usually be avoided. Simply combine tht two tables together.
1-to-many -- This is imply implemented by having the "id" of ...
- 76.9k
0
votes
Why does MySQL need double-write buffer if there is redo log and undo log?
A page can contain multiple records. In fact, it's required to contain at least two records.
The Redo Log isn't a copy of pages, it's a log of changes to specific records.
So if the original page is ...
- 13.9k
1
vote
Accepted
Database design, one to one relation split into multiple tables
Fifth Normal Form is about defining the set of valid combinations, if you have a need to enforce that in the database design.
It's not about requiring that a row exist in a referencing table. I don't ...
- 13.9k
1
vote
mysqldump on a 35GB table to a server in another region
No one can answer this accurately for you. The size of the table can affect the speed of restoration, but the definition of the table can also be a factor. For example, it's slower to load data into a ...
- 13.9k
4
votes
Insert gives error code 1366 even though charset is utf8mb4
Actually I needed to set the charset for the connection too. I thought that because I was using the mariadb command, there was no connection, but that was very naïve.
mariadb -uMYUSER -pMYPASSWORD --...
- 171
0
votes
Does the logical query processing change when indexes are involved?
SELECTs have a "standard" processing model that dates back half a century. Different vendors deviate from the standard, mostly in failing to implement all the pieces of the standard and/or ...
- 76.9k
0
votes
Query to compare the structure of two tables in MySQL
to only get the column names that exists in the first table and not the second:
select column_name from information_schema.columns where TABLE_SCHEMA='your_database_name' and TABLE_NAME='first_table' ...
- 101
0
votes
Sudden spike in BinLogDiskUsage
I have created a Python tool, which extracts and counts the statements from the binary logs and creates a nice report. This report can help during the investigation of such cases. Especially UPDATE ...
- 101
0
votes
Seeking Guidance on Migrating MySQL/MariaDB Data to Supabase
to proceed what i did is to keep it simple like this:
load database
from mysql://user:pass@host/source_db
into postgres://postgres:[email protected]:XXXX/postgres;
Notes
mysql ...
2
votes
Does the logical query processing change when indexes are involved?
Indexes would indeed affect the physical operations, altering access paths, I/O CPU etc.
However your diagram is logical. Indexes do not change the nature of these operations, only how they might be ...
- 833
1
vote
Should i care about field names in JSON datatypes?
For relational databases (RDBMS) like MySQL, MariaDB, PostgreSql it matters a lot, because they store the JSON as text. See the other answers.
For a NoSQL database like MongoDB it looks a bit ...
- 2,923
1
vote
Should i care about field names in JSON datatypes?
Yes, the field names are stored in every JSON document. If you use long field names in the JSON document, the document uses more storage on every row of the table.
I showed this in my presentation How ...
- 13.9k
1
vote
Should i care about field names in JSON datatypes?
MySQL/MariaDB:
The string is stored as a string. This takes space and slows down looking into the string.
So, you should, in general, pull the useful columns out of the JSON and put them into columns ...
- 76.9k
2
votes
Accepted
mariadb table partition and index tables
Think of it this way... A Partitioned table is a set of separate tables, based on the partition key. Each "sub-table" has the partition's data in a BTree, plus separate BTree for each ...
- 76.9k
0
votes
mariadb table partition and index tables
My question is: Will the index tables also be partitioned accordingly?
Yes, indexes "partition" the data in an exponential way when compared to regular partitioning which only divides the ...
- 35.1k
0
votes
mysql memory usage keeps growing without visible reason
First thing to check is what the approximate theoretical maximum memory usage of your database is. As discussed in my MySQL tuning article, the equation you should be using to approximate maximum ...
- 1,024
0
votes
Accepted
Two queries to retrieve user profile and tweets, or join and get user data from first column?
Two queries to retrieve user profile and tweets, or join and get user data from first column?
Is it better to fetch them in two separate, possibly concurrent/parallel calls?
OR
make a one query, ...
- 35.1k
0
votes
How would you implement Rollback when you need to make sure that 2 updates and 2 inserts either all happen or are all rolled back?
Add FOR UPDATE to the end of that SELECT (assuming it feeds info to the subsequent statements).
Let's see the declaration of row_affected_counter.
- 76.9k
1
vote
MySQL SSL settings for ODBC
To connect using SSL from the client side. Use the following mapping
ssl key = \\folderpath\client-key.pem
ssl certificate = \\folderpath\client-cert.pem
ssl ca = \\folderpath\ca.pem
Reference: ...
Top 50 recent answers are included