Questions tagged [mysql]

All versions of MySQL (not Microsoft SQL Server). Please also add a version-specific tag like mysql-5.7 if that is relevant to the question.

Filter by
Sorted by
Tagged with
0 votes
0 answers
8 views

Troubleshooting why MySQL forcefully shut down

Apologies if this is not the right place to ask this. As of a few days ago my MySQL instance has started shutting down automatically. In the MySQL logs it looks like a graceful shutdown, which makes ...
user avatar
  • 101
1 vote
1 answer
33 views

MySQL - archiving data in tables

I am a developer for an insurance app. We are using AWS RDS with a MySQL implementation. The scenario we would want is to automatically archive data from one table to another. Something like this: Let'...
user avatar
1 vote
0 answers
9 views

Resume MySQL / MariaDB replication from master logs

Master server crashed and slave was running behind. I can not boot the master to resume the replication and retrieve the data lost. Is there a way to resume the replication directly from the log files?...
user avatar
2 votes
1 answer
25 views

Optimize a query multiple tables & Multiple conditions

I need to optimize the query which has multiple joins. And also with multiple OR AND conditions which varies depending upon the user input. The query which gets generated is: SELECT emp.name as ...
user avatar
0 votes
0 answers
13 views

MySQL Lost Connection when Importing a dump file

I am working on a MySQL DB transfer (InnoDB tables) from a production server to a local development machine. The dump file is generated via phpMyAdmin and is about 1.6GB in size. The import process ...
user avatar
  • 11
0 votes
0 answers
13 views

Why is a user randomly created in AWS RDS even though the permissions from any host (%) exist?

I have a MySQL database in RDS. There are separate users which are allowed to connect from anywhere. To be more precise, using MySQL Workbench there are different users listed in "Users and ...
user avatar
  • 1
0 votes
0 answers
15 views

Duration different between mysql workbench and command line

When profiling a simple insert statement, I'm seeing a massive difference in duration reported between mysql workbench and the mysql command line. Test Setup CREATE TABLE test ( id int ) ...
user avatar
2 votes
1 answer
12 views

MySQL result set changes based on select list

So this is a bit weird as I've never had to deal with this issue before. I have a dataset with 4 basic joins to find the user who edited, created the record set. SELECT `tasks`.`id`, `tasks`.`title`, `...
user avatar
1 vote
2 answers
31 views

How to Update and Manage Millions of Databases

We are looking to shard our application, so that each customer has their own database. The data contents of all databases are different, but the structure is the same for all customers. Imagine there ...
user avatar
0 votes
1 answer
14 views

About partition tables with Mysql 5.7 in RDS Amazon

I haven't found anything in the official amazon documentation that tells me at least the pros and cons of partitioning tables in non-IAAS environments. Has anyone of you had this experience, were ...
user avatar
-2 votes
1 answer
23 views

error 1292, Truncated incorrect DOUBLE value: ''

im try to insert data to table order but allows im getting error [22007][1292] Truncated incorrect DOUBLE value: '' INSERT INTO `order` SET `order`.customerId = '9e1c8915-c60c-407e-8d34-9fefdffd5ebd',...
user avatar
1 vote
1 answer
15 views

How is InnoDB recover ? the crash happenning when buffer_pool memory copy to double_write_buffer

I know that double-write is purposed to solve the partial-page-write problem.but how to recover when the crash happenning during buffer_pool memory copy to double_write_buffer
user avatar
  • 13
0 votes
1 answer
66 views

About Transaction knowledge

Am teaching myself SQL. And I never worked in any enterprise company but I have watched and learned some tutorials about web development and I see crud applications used to inject data into a database ...
user avatar
1 vote
1 answer
38 views

Understanding MySQL Locks and Deadlocks

I was just checking the articles regarding the MySQL table locking system. I wanted to learn about how we can avoid the deadlocks in MySQL while UPDATE/DELETE operations. What I have done so far: ...
user avatar
1 vote
0 answers
22 views

How to debug a huge MySQL replication lag?

We have an AWS RDS and a corresponding Replica. Once or twice a month the replication lags and does not recover from its own. Our "solution" is to create a new replica and delete the old one....
user avatar
  • 111
0 votes
1 answer
38 views

MySQL - Optimize select with join to a large table

I have done some digging and found exactly no help at all for questions similar to this one. Basically I have two tables where one stores user posts and the other holds the moderation of the records ...
user avatar
  • 101
0 votes
1 answer
36 views

Approximately how many reads/writes per secons can MySql/MariaDb tolerate?

I'm running a python script that reads some historical data and inserts it into MariaDb. There's a lot of data and I consider splitting it into parts and running mulitple instances of a script. But ...
user avatar
  • 27
0 votes
1 answer
27 views

What is MySQL wait state 'wait/synch/mutex/innodb/lock_mutex'

We have an application (on Amazon RDS MySQL 5.7; db.m5.24xlarge; 96 cores) that takes in a lot of data, in parallel processors+queues. It mostly goes to one table, with primary keys not spreading over ...
user avatar
  • 141
1 vote
0 answers
38 views

Can a foreign key be a primary key?

I want to know the relationship takes, teaches, advisors tables underlined attributes are foreign key or primary key? or both?
user avatar
0 votes
1 answer
24 views

MySQL how do I save a count into a variable to update another column?

I have a table with columns: IdNum, NameEmployee, IdInsurance, NameInsurance, num_employees_not_insured Some employees don't have insurance so IdInsurance and NameInsurance are null. I want to count ...
user avatar
0 votes
1 answer
15 views

What's the point of using client certificate if mysql server doesn't verify the clients?

By default mysql server creates these 6 certificates in /var/mysql/lib: -rw------- 1 mysql mysql 1676 Aug 3 00:16 ca-key.pem -rw-r--r-- 1 mysql mysql 1112 Aug 3 00:16 ca.pem -rw-r--r-- 1 mysql mysql ...
user avatar
  • 115
0 votes
1 answer
15 views

Why do I keep getting column name is ambiguous for a left join on MySQL?

CREATE DATABASE IF NOT EXISTS EdocodeDB2; USE EdocodeDB2; -- create CREATE TABLE EMPLOYEE ( IdNum int, Name varchar(255), PRIMARY KEY(IdNum) ); -- insert INSERT INTO EMPLOYEE VALUES (1, '...
user avatar
1 vote
0 answers
24 views

Return back only top N rows for each ID - SQL

I have written this SQL after browsing different topics. The topic I got the solution from -> Limit Records MySQL version: 5.6 SELECT op.* FROM (select op.*, (@rn := if(@s = order_id, @...
user avatar
1 vote
1 answer
24 views

Get all children for a set of parents that has a specific child

I have a parent and child table linked by a linked/associative table (since the parent - child is many to many relationships. I am looking to get a list of all parents that has a specific child BUT ...
user avatar
  • 11
0 votes
0 answers
35 views

How to flatten/pivot "inner join" into a single row in a certain order?

Let's say I have 2 tables in MySql/MariaDb: competisions and participants. There can be multiple participants in one competisions. I want to a) do an inner join b) sort the participants by score c) ...
user avatar
  • 27
0 votes
0 answers
75 views

MYSQL 8 | Ubuntu 20 | Failed to start error

Here is the error I get. Can't start MySQL server 8 on ubuntu 20. I haven't backed up my db so can't purge all data as well. I tried reinstalling MySQL, and didn't work. >/var/log/mysql/error.log ...
user avatar
0 votes
1 answer
17 views

Is it safe to manually delete binary logs after disabling it?

After disabling log-bin by skip-log-bin option, I cannot use PURGE command to remove binary logs. Refer to the documentation PURGE BINARY LOGS requires the BINLOG_ADMIN privilege. This statement has ...
user avatar
  • 101
0 votes
1 answer
10 views

Mysql: sorting rows of a subquery generated table not working working

I have a subquery (generated by an ORM for pagination purposes) that generates a table that is then sorted by the parent query. SELECT DISTINCT `distinctAlias`.`invoice_id` AS 'ids_invoice_id', ...
user avatar
  • 7
1 vote
3 answers
36 views

Is having a boolean column to store whether there are values for a multivalued attribute a bad micro-optimization?

This question is more theoretical. Let's say there is an entity in a one-to-many relationship. For example, a blog post can have zero or more tags. Those tags are stored in another table (that ...
user avatar
  • 23
1 vote
1 answer
17 views

How to change the engline type from InnoDb to MyISAM of an existing db?

I have a MariaDb database which consists of 2 tables and which is around 10Gb of the size. One is dependendant on the other via a foreign key. How can I change the engine type of that particular db ...
user avatar
  • 27
0 votes
1 answer
17 views

Searching and deleting MySQL records after insert with stored procedure

I have a quandary with which I am struggling to answer, and looking for some advice on how it can be done. We have a table, that is basically comprised of the following fields. I have used Info x to ...
user avatar
  • 33
0 votes
0 answers
7 views

Unable to find variable : mysql-monitor_username in global_variables table in information_schema database in MySQL

I am trying to perform the initial configuration in proxysql and I found that while doing the Configure monitoring, I am unable to find the variable mysql-monitor_username in the global_variables ...
user avatar
0 votes
0 answers
13 views

How to monitor(look which server made what changes) the master slave replication

i have a master slave setup on different ec2 instance and they are perfectly replicating i just wanna know how to check the changes made by the other instances to the database we replicate
user avatar
0 votes
1 answer
42 views

How many keys in mySql are too many?

I am currently designing a MySQL database that has 212 tables. Some of the tables has 5-7 foreign keys. Is this going to hamper database query performance in the long run? How many foreign keys are ...
user avatar
1 vote
0 answers
33 views

speeding up mysql select's

I've a coldfusion application that currently contains just under 5,000 records on one table. I've a query that selects around 3800 records from that table. There are several queries that retrieve over ...
user avatar
0 votes
1 answer
27 views

MariaDB memory usage much higher than innodb_buffer_pool_size

I have a backend server with 1G RAM for my HTTP server and my MariaDB. I noticed the database keeps getting killed by OOM once or twice a day. Most of the time the OOM is triggered by the HTTP server, ...
user avatar
1 vote
1 answer
19 views

Define a function on the fly as with CTE in MySQL

With CTE it is possible to define 'on he fly' custom tables that exists only during the execution of the current query: is it possible to do the same for functions? Example use case I have a query ...
user avatar
  • 111
0 votes
0 answers
11 views

phpmyadmin cannot upload and read the longblob bin file

I need to upload 20-30 MB longblob bin file to my table. I have changed the PHPMyAdmin config file: memory_limit = 128M upload_max_filesize = 100M post_max_size = 128M The first issue is after I ...
user avatar
  • 111
0 votes
1 answer
18 views

MySQL thread states: update vs updating

This should be a simple question, but surprisingly, I can't find much about it: the difference in MySQL thread states update vs updating. The MySQL docs say: update: "The thread is getting ready ...
user avatar
  • 141
1 vote
1 answer
42 views

ERROR 1045 (28000) & access denied for user 'root'@'localhost'

UBUNTU 22.04.1, mysql ver 8.0.30 (1) I get sudo apt install mysql-server then sudo mysql_secure_installation Get in loop this error for each complicated passwords I tried: (contained: '<>&(*...
user avatar
  • 131
1 vote
3 answers
53 views

Forcing InnoDB to build the secondary index

When INSERTing, InnoDB defers the building of secondary indexes. For example, CREATE TABLE item_creators ( item_id int(11) unsigned NOT NULL, creator_id int(11) unsigned NOT NULL, INDEX(creator_id), ...
user avatar
  • 4,261
0 votes
0 answers
14 views

speeding up JOIN by LIKE or REGEXP

I have two tables of CREATE TABLE titles ( title_id int(11) unsigned NOT NULL AUTO_INCREMENT, title varchar(255), INDEX(title), PRIMARY KEY(title_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE ...
user avatar
  • 4,261
0 votes
1 answer
16 views

faster INSERT INTO InnoDB table with composite PRIMARY KEY

I have a simple tag_map CREATE TABLE tag_map ( article_id int(11) unsigned NOT NULL, tag_id int(11) unsigned NOT NULL, INDEX(tag_id), PRIMARY KEY(article_id,tag_id) ) ENGINE=InnoDB The table is quite ...
user avatar
  • 4,261
0 votes
1 answer
11 views

Alternative/Substitute to Partial index in MySQL?

MySQL does not allow partial index creation. How can you achieve partial indexing in My SQL without using triggers. For example: TABLE: STUDENT NAME STATUS XYZ FAIL XYZ FAIL XYZ PASS ABC PASS ...
user avatar
0 votes
1 answer
24 views

MySQL - Nested loops count too high resulting in query taking too long

I am seeing high response time for a MySQL query. I have two similar queries: SELECT `orders`.`id` FROM `orders` WHERE (`orders`.line_count = 0 OR `orders`.account_type_id IS NULL OR ...
user avatar
0 votes
1 answer
10 views

Inserting a "date-time" into TIMESTAMP column -- "Incorrect datetime value"

I have a date-time data that comes in this format: 2016-02-10T12:00:00.000Z I've created a MariaDb database and a column for that date of type TIMESTAMP However, when inserting the data into the ...
user avatar
  • 27
0 votes
0 answers
22 views

how to print message 'included' along with limit_1 value, when limit_1 is selected with condition

select max(Contract_Num) as contract_num, max(Lic_Plate_Num) as lic_plate_num, max(cast(Revision_Eff_Date as date)) as rev_eff_date, max(contract_name) as cn_name, ...
user avatar
1 vote
1 answer
15 views

Mysql point in time restore using event position from 100 binary log files

i stuck on one question! the question is that i hava a large database in production environment and i have a full, incremental backup like till 2pm and aftre 1 hour i suddenly run the "drop ...
user avatar
0 votes
1 answer
13 views

Error Code: 3548. There's no spatial reference system with SRID 1498173264 - MySQL

I am trying to run a mysql query below: select * from geo where st_distance_sphere(Point(-118.2842557, 34.0676834), `geometry`) <= 1609 where geometry is of type Polygon. and get an error: Error ...
user avatar
  • 129
0 votes
0 answers
9 views

Execute SQL file from S3 in MySQL RDS

I have a large (~10Gb) .sql file that I need to run against a MySQL RDS instance. I have placed the file in a bucket in S3. How can I execute that large file against my RDS instance? This is a one-off ...
user avatar

1
2 3 4 5
431