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
3
votes
1answer
57 views

Testing MySQL adaptive_hash_index option

We recently migrated our application to an entirely new infrastructure, going from a single server managing everything to a kubernetes cluster and dedicated database server running on AWS RDS. On ...
1
vote
1answer
37 views

MySQL Query running slow when using REPLACE function instead of CASE statement

Is it better to use CASE statements instead of REPLACE functions in MySQL when mapping a comma-separated-string field? The below query runs extremely slow using replace. Note, the underlying ...
0
votes
1answer
31 views

Moving a large MariaDB database between MariaDB servers

Once my DB size is multiple hundred GB in size, it is not practical to use the documented methods for transferring a database to an alternate db server due to sheer amount of time (days to weeks) for ...
0
votes
0answers
24 views

Creating table/columns combinations using Mysql Query

I have an existing scheme for products variations. I want to create a combination of each production time, quantities and variation options. I will create a selection form by accessing the quantities, ...
0
votes
1answer
16 views

Optimize MySQL insertion - insert multiple records with condition (per record)

although not new to SQL, up until now I used the basic stuff - basic crud, joining tables etc. I have a special case now where I'm expecting a lot more insertions that goes something like this (...
1
vote
1answer
47 views

What is the best way to design a database for this specific type of 'offer' that this restaurant chain offers?

I am working on adding a few features in a mobile application of a restaurant chain and the tech stack used is Java for Android and PHP + SQL for backend. Until now I have always used NoSQL, but since ...
0
votes
0answers
20 views

MariaDB 10.5.8 refusing my connection over SSH tunnel once I add “skip-name-resolve”

I've moved from a 10.3 installation to a 10.5 installation, and ported my various my.cnf settings over. One of those is skip-name-resolve. If I leave it in I'm suddenly unable to connect to the ...
0
votes
0answers
19 views

Innodb buffer pool internal

I'm learning the Innodb buffer pool of mysql 5.7. And I have some question about that. What is the relation of buffer pool,buffer block,buffer page and buffer chunk? How to locate a row in the ...
0
votes
1answer
40 views

Rewriting query to move subquery out of FROM clause

Suppose I have an entries table that looks something like this: | id | category | text | user_id | |----|----------|------|---------| | 1 | A | z | 22 | | 2 | B | y | 23 ...
0
votes
0answers
13 views

Restore from ibd files where is no frm files [duplicate]

I had an Ubuntu 18.04 with mysql 5.7. I upgraded it to Ubuntu 20.04 with mysql 8.x. But this 8.x not was compatible with my Drupal 7 and I tried to downgrade. After this I tried with a 5.7 docker ...
0
votes
1answer
23 views

How to find repeating records effectively?

I have a registration application for registering 6 courses. The program will check the repeated register users first. Those users that needed to be checked comes from last year. If users registered ...
0
votes
0answers
14 views

Indexes messed up / slow after running mysqlimport

I'm doing a restore using mysqlimport by iterating over files generated by mysqldump --tab and the import is very fast but performance seems to be lagging after the import. Does mysqlimport neglect to ...
0
votes
2answers
65 views

Order by column with specific adjacent value

Apologies for the vague title. I wasn't really sure how to describe this. I have the following tables: items: id | name ---|---------- 1 | first-item 2 | second-item 3 | third-item fields: id | ...
0
votes
0answers
17 views

InnoDB engine lock tables higher than tables used

I am running the SQL statement CREATE OR REPLACE TEMPORARY TABLE my_schema.tmpx_test AS SELECT col1 , SUBSTR(col2, 1, 5) AS col3 FROM my_schema.my_table WHERE col4 = 'my_string' AND POSITION('...
0
votes
0answers
19 views

Will Federated engine work with pxc cluster

I'm a bit new to MySQL specific settings and I can't find any hint/insight to this question. I have several Federated tables that work great for their current purpose, the rest of the database is in ...
0
votes
1answer
28 views

MySqldump Error 2013: Lost Connection MySQL During create database backup

I am use Prestashop. am create a script for auto backup of whole database of my site but from last few given this error and backup terminate at this table. mysqldump: Error 2013: Lost connection to ...
-1
votes
1answer
25 views

How to set default value as empty string for all rows at once in MySQL while creating the table

In MySQL, while creating a table, I'like to have empty string '' as value for all needed columns instead of 'NULL'. Is it possible, is it safe and how can I do to do that at once? I have the following ...
0
votes
0answers
14 views

What is the cause of sleeping connection?

What is the possible cause of sleeping connections? Can I say one of the causes is because of the uncommitted transactions? How does this related?
0
votes
1answer
25 views

Azure Elastic Pool - is it supported for MySQL?

I know that Elastic Pool in Azure is supported for SQL Server. But I could not find any information if it is supported for MySQL. I could not find any information in documentation at all what types of ...
0
votes
0answers
25 views

Insert into junction table and one of the many-to-many tables

I have these two tables, exercises and workouts, each just for this example having only id and name and a junction table between them workout_exercises, since an exercise can belong to many exercises ...
1
vote
2answers
64 views

How to optimize Mysql database of 250 million rows for bulk inserts and selects

I am a student, who have been given the task of designing a database for sensor data. My university currently has a large database which is being filled with these data, but a lot of what is being ...
1
vote
0answers
30 views

How to Sync and Consolidate Data from Multiple MYSQL local database to MYSQL live server database in PHP?

What I want to accomplish is that the web app will still work without internet connections, which means that the branch will still operate without internet connections. If there is an internet ...
1
vote
1answer
38 views

Running sum total over 14 days for each date

In a database of transactions spanning about 50 customers over a period of 8 months, I need to find which customers have 0 total transactions over a consecutive 14-day period. The customer table (...
0
votes
2answers
27 views

(Beginner) MYSQL: What can I do about this where statement?

Suppose I have some table named tableA and column1 in tableA has some null values and strings. Also suppose I had created some temporary table named tempTable that has only 1 column. And that 1 column ...
1
vote
2answers
47 views

Troubleshooting high memory usage on MariaDB 10.5.4

I am experiencing very high memory usage on MariaDB server. I am not sure if it is due to misconfiguration or a bug. Server: MariaDB Server version: 10.5.4-MariaDB MariaDB ...
1
vote
2answers
30 views

Mysql: Query which orders by default and rank (pin/hold up entries?)

I have a products table which contains all my products. Those products table gets filled permanently with new products. However, I want to have the possibility to "hold up"/"pin" ...
0
votes
1answer
16 views

Preserve CASE WHEN output as new column in MySQL

I am new to MySQL and this question may look silly, but: I have a dogs table and I categorize their size by their weight: SELECT *, CASE WHEN weight < 15 THEN 'Small' WHEN ...
0
votes
0answers
43 views

socket fail to connect to host - mariadb [closed]

I am getting the error Could not connect to address(host = 127.0.0.1, port:3306) Socket failed to connect to host:127.0.0.1,port:3306, Connection refused using maria jdbc driver. But I can connect ...
0
votes
1answer
38 views

Storing multiple tables by type vs one table

I want to store a lot of rows in a table, but I will often want to select only some of them, depending of their type. So I could just add a column type to each row, but then, if I want to select only ...
0
votes
0answers
8 views

Understand the underlying logic of Sakila DB

I'm working with Sakila database, trying to find which film is instock in store 1. However, before going to the syntax, I have basic question about the database first. If a movie is rented out, I ...
0
votes
0answers
30 views

MySQL - how to optimize select within a range bound by values in 2 different columns - innodb

How do I optimize select within a range bound by values in 2 different columns with MySQL innodb? I'm matching words to the pages on which they were printed. Each instance of a word points to only 1 ...
0
votes
1answer
30 views

Sql Group by on subquery rows

I have this table : ArretProductionJournee(Id, DateArret,HeureDebut,HeureFin,EnumArret) Example : DateArret ||HeureDebut ||HeureFin ||EnumArret 2020-11-30 ||2020-11-...
0
votes
0answers
14 views

Cannot rejoin or add instance to MySQL InnoDB cluster

I have a MySQL InnoDB cluster running where I had added three nodes, one is having the Primary role and the others are Secondary. Today morning, the two secondary nodes were removed from the cluster ...
0
votes
2answers
16 views

MySQL - Get counts of `car models` by `car make`

I have a table with cars data, i.e. Makes, Models, & Year. I want to get the count for each Car with how many models it has... something like the image below: This is the SQL query I am using ...
0
votes
0answers
13 views

Choose the index for default sort in MySQL [duplicate]

I have a table in MySQL with 3 columns a, b, c. Each has an index but none is primary key. When I run query SELECT * FROM table LIMIT 50; I am trying to have the result sorted by column b ascending, ...
-1
votes
2answers
28 views

MySQL - multiple counts on relations based on conditions - JOIN VS. SUBQUERY

I don't want to share my exact DB structure, so let's assume this analogy : --categories-- id name --products-- id name cat_id I then have SQL like this : SELECT categories.*, count( ...
0
votes
1answer
19 views

MySQL no longer starting “An attempt was made to access a socket in a way forbidden by its access permissions.” (Windows 10)

I don't think this is another "some other process is using port 3306 rtfm" kinda question. I have been running MySQL 8.0.x fine for a couple of months on Windows 10 for some local python ...
0
votes
2answers
20 views

Debugging mode in MySQL Workbench

When running queries in MySQL workbench, some of them are captured to the log file at either: MySQL/Workbench/logs MySQL/Workbench/sql_history However, I'd like to see ALL the possible queries that ...
0
votes
1answer
11 views

How to get the port of a connection in mysql

I can do: select * from information_schema.processlist plist where id=connection_id(); To get the data from the current connection I am on, this will give me something that looks like: # ID, USER, ...
0
votes
1answer
26 views

Why are there no indexes contained in the information_schema database?

For the information schema.processlist: Why are there no indexes on any of the fields (for example, a pk on ID)? Is this something that is not written through 'via sql' and is only reading directly ...
1
vote
2answers
28 views

SHOW connection_id()

In mysql, I can inspect the current Connection ID by using: SELECT connection_id(); And it returns the id: # connection_id() '6558851' Or: SELECT * from information_schema.processlist WHERE id = ...
-3
votes
1answer
43 views

What is “ENGINE=InnoDB DEFAULT CHARSET=utf8mb4” for? [closed]

I'm exploring Sakila database. After CREATE TABLE, I saw this syntax. Could any one help to let me know what is this and what it is for? how and when to use it? (I didn't see this in my SQL course) ...
0
votes
1answer
12 views

How to pull high-level server information from Mysql

MySQL workbench shows the following under server stats: I know it can grab the Connection Name and the Port from the data that I'm using to connect, but where does it grab the Host, socket, Version, ...
0
votes
0answers
12 views

MySQL workbench Client Connection info

I have two mysql installations, one local and one on Amazon RDS. On my local, I can view info on the queries in the Client Connections section: However, in the RDS connection it does not allow me to ...
0
votes
1answer
20 views

Is it safe to use the Windows “Compress directory to save space” feature on the directory that contains my MySQL general log file?

I have changed MySQL 8 on my Windows 10 development machine to write logfiles (general log and slow queries log) to "E:\mysql logfiles". I'd like to compress this directory using the Windows ...
-1
votes
1answer
35 views

Are there any performance downsides in using a view to filter a huge table and use it as the main entrypoint to query the table?

I have a table with a lot of records, and I want to restrict the data access to the last 3 years only, but I still want to keep the data there. I saw a solution where is it possible to create an ...
1
vote
1answer
14 views

A average Mark overall for all of one student subjects

Lets say i have a student table , a enlistment table that shows SubjectId and Results for each student how would i get the overall average mark for a student that has more than 3 subjects Create ...
0
votes
1answer
35 views

How to delete Specific row

http://sqlfiddle.com/#!9/df6245 How to delete all rows Where brand not equal to 107 for example in above fiddle GRPCD 44111 have 107 so it should not delete both rows which have GRPCD 44111 but it ...
0
votes
0answers
15 views

Mariadb (v5.5.65) replication unsuccessful. Slave_IO_State stuck at “Waiting for master to send event”

I am trying to configure a master-to-master replication of Mariadb. When I make changes to the master, the slave does not reflect the new changes. This is the guide that I referred to when setting up ...
0
votes
0answers
18 views

Descriptive Financial Statement - SQL [duplicate]

Consider a financial system, where the customer demands a financial statement consisting of | opening_balance | amount_credited | amount_debited | closing_balance | remarks. Two approaches to doing ...