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.
20,237
questions
0
votes
1answer
23 views
SELECT JOIN same table prevent duplicates MySQL 8.x
I have a table called 'price' with the following rows
idRequest | idPair | idExchangePlatform | lastPrice
1 1 1 95
1 1 2 ...
-6
votes
1answer
29 views
Write the SQL code to find the average charge by the family medicine physicians
We need to write the script to find the average charge of physicians who specialize in "family medicine.
But i'm confused on how to write this because when we look at the appointment table, where ...
0
votes
0answers
27 views
Unable to Execute Script files (.sql) in MySQL using DBeaver plugin, in Eclipse CDT with macOS Catalina (Shows, “No Active Connection”)
Unable to execute the script files (.sql).
It shows "No Active Connections".
But, test connection is complete, and localhosr has MySQL Icon with connection check mark, and I refreshed the ...
0
votes
1answer
36 views
Table design comment_seen vs comment_unseen — migrate data from one to another
I have system that allows comments posted by users and tracks which users seen which comments.
The read/unread state is tracked within comments_seen table. Surely enough, it grow tremendously.
My goal ...
0
votes
1answer
18 views
Would current configuration be better as a staging environment and how to determine best practices to enhance production environment
Task: Database Revision
Legacy System: LAMP (MySql v5.5)
Current System: Node Express MySql
v8.023
Data is acquired from 14 sources as .txt files with different delimiters and imported to their own ...
2
votes
1answer
51 views
MySQL performance issue when a hash join is used
I'm having an issue where the optimizer seems to be doing a full table scan and exhibiting a big performance hit when a hash join is used.
Schema and sample data:
CREATE TABLE `px_po_status` (
`...
0
votes
1answer
40 views
Mariadb concurrent insert not working for MYISAM tables
I am using mariadb 10.1.13. I have a large MYISAM table ( > 2000000 rows ) which used to have frequent 'INSERT' operations from multiple scripts.
Recently I had to add a new 'SELECT' query to the ...
0
votes
0answers
16 views
How recover Tablespace from table in MySQL
my database fail and I dont have a backup so I have the .ibd file each table in database but when I try recover in other database coping the files there the database show me the structure and all ...
0
votes
1answer
11 views
MySQL - Information_schema Events - How persistent or retention is managed
I'm a MS SQL guy and understand agent job history.
I'm now working with MySQL.
I've searched hi and low but I can't find any solid info on how event data within information_schema.events is persisted, ...
0
votes
0answers
18 views
MySQL keeps ignoring MAX_EXECUTION_TIME
I have an instance running which have mysql8 and a nodejs application server.
This query keeps popping up and freezing.
SELECT /*+ MAX_EXECUTION_TIME(2000) */ COUNT(*) FROM my_table;
Even though this ...
1
vote
1answer
22 views
MySQL Like for Like - 100% accurate Backup/ Restore
I've struggled to find the answer to get a perfect backup and restore for MySQL.
I need to do performance test on a database so I need to make sure that the page files, indexes, fragmentation - the ...
1
vote
1answer
17 views
max_connections set to 500, Max_used_connections never go over 151 [closed]
I have this set in the mysql config file, rebooted, seems to be correct.
SHOW VARIABLES LIKE "max_connections";
+-----------------+-------+
| Variable_name | Value |
+-----------------+----...
1
vote
2answers
11 views
MariaDB - Changing explicit_defaults_to_timestamp
I am trying to check from MariaDB docs online how I can turn ON explicit_defaults_to_timestamp global variable. I encountered this as part of Apache Airflow db setup.
Is there any solution to do this? ...
1
vote
2answers
13 views
Can text fields be not loaded for join memory purposes in MySQL?
I want to use many text fields for a table but they can be very large so doing complex operations with rows of this table, like joins with big tables can become intractable or very slow
I understand ...
0
votes
1answer
12 views
MySQL on Windows Refuses to Start as a Service
I am trying to install MySQL 5.7.33 onto a windows 2012 server. A process I have done many many times.
I have copied the Zip file onto the server and expanded it into G:/mysql57/ (the same zip file I ...
0
votes
2answers
21 views
MariaDb mysqldump configurations
We have extremely large database (mariaDB 10.3) and mysqldump taking 2-3 hours to take the full database dump and also causing downgrade in application performance during the dump process.
After some ...
2
votes
2answers
197 views
MySQL best way to define a couple of columns as both null or both not-null
On a MySQL DB, I would like to find a way to force a couple of columns be both null or both not null at the insert / update time. This means that on each record, both columns are null or both columns ...
3
votes
1answer
251 views
Is it possible to automatically generate a Unix millisecond timestamp in MySQL 5.7
I have created a created_time field with BIGINT in a MySQL 5.7 table, now I want to auto generate a Unix millisecond timestamp when I insert a record. Is it possible to do this? I have tried the code ...
0
votes
0answers
15 views
What is an equivalent MySQL function to match CRYPT of PostgreSQL?
In PostgreSQL there is a function that can be used to encrypt passwords, like so:
crypt('123456', gen_salt('bf'))
What would be an equivalent function for MySQL?
Background
I am amending code that ...
0
votes
0answers
12 views
How should I deal with sub attributes of xml data when loading onto mysql?
I have some xml files (example below). As you can see in the xml, id 4 has 3 entries for tax. When I load it right now, only the last entry gets inserted into the table.
How can I include all 3 of ...
0
votes
1answer
16 views
Mysql throwing table full error when adding new column
I keep getting table full error when adding a new column to 30gb table on innodb mysql database.
ibdata1 size : 844mb
Current Settings
innodb_data_file_path : ibdata1:12M:autoextend
tmp_table_size : ...
0
votes
1answer
32 views
Best way for processing over 150 million rows MySql
I wrote a function on nodejs to SELECT 150 million records, process the data and UPDATE those same 150 million records using
UPDATE 'table' SET value1='somevalue' WHERE idRo2 = 1;
Single Update for ...
0
votes
1answer
17 views
SQL which removes entries with duplicate emails
DELETE
FROM Person
WHERE Id IN (
SELECT Id
FROM Person
GROUP BY Email
HAVING COUNT(*) > 1 AND Id <> MIN(Id)
)
The above query produces the error:
You can't specify target table 'Person' ...
2
votes
0answers
39 views
Got fatal error 1236 from master when reading data from binary log
I am trying to do a MySQL failover, in some situations I endup with broken replication. Any idea what could be the cause of it ?
Background
Needed to perform a host updates on master host. Hence ...
1
vote
0answers
17 views
is there a way to dump schema tables that don't exist and ignore the ones that exist?
I am using this command to dump schema directly to a different server because the existing server don't have space left to dump the tables/schemas and then transfer to the other server:
mysqldump -h ...
0
votes
1answer
63 views
Restoring mysql data from *.IBD file
Unfortunately I have recently lost my VPS and the provider restore it after 1 week, then the only things that I had was the raw files on my server which were accessible in recovery mode.
so by moving ...
0
votes
0answers
7 views
How to increase innodb_log_file_size in Digital Ocean? Ubuntu! [duplicate]
Please do help me on how to increaase my innodb_log_file_size
0
votes
1answer
26 views
Having clause question maybe [closed]
I have 3 tables.
Grades
Disciplines
Students
I want to select only the disciplines that doesn't have grade 2. What am I doing wrong?
SELECT DISTINCT DISC_NAME
FROM STUDENTS, DISCIPLINES, GRADES
...
0
votes
0answers
19 views
Why is it not possible to switch SQL mode on MySQL 8 running on Mac?
I am getting an error in MySQL 8 (local install) running the same queries as on MySQL 5.7 (production) regarding SQL mode. Trying to switch the SQL mode results in restart errors of MySQL. Version: 8....
1
vote
0answers
47 views
MariaDB service fails to start with InnoDB Space id error
I've been running MariaDB on a Raspberry Pi 4 (Raspbian 10 / buster) as the database server for my Webtrees and Owncloud sites. Recently, I noticed that the server wasn't looking right, and after a ...
0
votes
0answers
16 views
MySQL - deleted IBD file, now my DB says 'Tablespace is missing for table`
I tried to free space of a cache table and I removed the IBD file of one of my tables, and I'm unable to recreate it now - 'Tablespace is missing for table ``.output_cache.'
Currently, it's corrupted, ...
0
votes
0answers
44 views
galera_new_cluster hangs with galera_recovery
My cluster crashed and i cannot restart the first node with galera_new_cluster.
The nodes are distributed geographically in France with medium latency of 12ms.
It remains on /usr/bin/galera_recovery ...
1
vote
0answers
62 views
Ranking and use of cube by in an SQL query? [closed]
I am a the MSc student who does not understand this following SQL dilemma and want to be certain from an Academic standpoint.
The entire “Foodmart” database can be downloaded from: http://didawiki.cli....
0
votes
1answer
19 views
How to speed up query with exists clause?
I have requirement to identify customers using multiple emails for the same billing address. There are 414375 records. I let the query run for 20 minutes and it still did not return. How do I speed ...
0
votes
2answers
24 views
MySQL update a record on duplicate key update (merge)
I have a table products with the follow schema:
CREATE TABLE `products` (
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
`user_id` bigint unsigned NOT NULL,
`article_id` bigint unsigned NOT NULL,
...
0
votes
0answers
27 views
How can I tune my MySQL server to bulk import XML file (42 GB)?
I have a 40GB xml file which needs to be loaded into the db. Currently we are not sure about PK/FK relationships or schema details. So I am just loading them up without any indexes or keys. Every time ...
0
votes
1answer
16 views
How to have multiple condition on one column [closed]
I have table with students - name, id
and table with grades - id, grade value, discipline.
Each students has few grades. I want to select only the students with grade = 2 and grade = 6 in the same ...
1
vote
2answers
51 views
Join two tables in MYSQL and avoid duplicate values
I created two tables using MYSQL, tblorder and tblitem.
CREATE TABLE `tblorder` (
`orderId` int NOT NULL,
`orderName` varchar(45) NOT NULL,
PRIMARY KEY (`orderId`)
CREATE TABLE `tblitem` (
`itemId` ...
0
votes
0answers
17 views
MySQL: Grant creators of view DROP and ALTER on those views
Is it possible to automatically grant someone the permission to DROP or ALTER their view that they created?
The problem we've got is that there are some views that we don't want the BI team to be able ...
-1
votes
2answers
13 views
Preferential Select Query
The issue that we are trying to tackle is best shown with the following illustrative example:
CREATE TABLE table_1
(
id INT UNSIGNED AUTO_INCREMENT,
colA INT,
colB VARCHAR(10),
PRIMARY ...
0
votes
1answer
27 views
MySQL table design
I'd like to have a 'records' table (lets say this table is populated with some values I've created before-hand)
I also want a user to be able to create his own records (And only he will be able to ...
0
votes
0answers
11 views
Truncating partition locking whole table in mysql
Novice here.
I have a huge data that is generated every day and are stored in day wise partition. However, I have a cron that automatically deletes the old day (30 days ago). The query for deletion is ...
0
votes
0answers
12 views
MySQL 5.7 32-bit table is full on unix
As per the subject, I'm receiving an error on an InnoDB table: "table is full" when it reached 4GB the filesystem is far from being full so the only thing that I'm thinking right now is that ...
0
votes
2answers
18 views
Is 'desc' a valid name for a column in mysql?
If I try to create a table called test with one column, it works:
mysql> create table test(surname VARCHAR(300));
Query OK, 0 rows affected (0.02 sec)
mysql> drop table test;
Query OK, 0 rows ...
0
votes
0answers
12 views
What is the usual method to backup only specific columns in table rows with condition in MySQL?
We have multiple tables which needed to be updated on datetime columns in each, update by adding 1 hour where the datetime value between A and B.
We usually take full backup of DB, so that would be ...
-1
votes
2answers
54 views
Are junction / join tables always necessary
I have tried looking across other answers for this, so far I have not quite found what I am after. While there are multiple questions regarding junction tables, none seem to address whether a junction ...
1
vote
1answer
9 views
Starting points for creating a mysql database proxy
I'm not actually entirely sure if this is possible, but I'd like to build piece of software that behaves a lot like a proxy between a client and a mysql server.
Essentially, I want this software to ...
-1
votes
1answer
20 views
Query optimization with joins and unions and fulltext boolean search
I have the following base tables with subtables for searchbait. (This is on aws aurora with mysql 5.7 compatibility)
tblA (id, userId, title, ...)
tblB (id, userId, title, ...)
tblA_searchBait (id-&...
0
votes
1answer
15 views
Mysql InnoDB Full-Text Search IN BOOLEAN MODE Sort Rows In Order of Decreasing Relevance
In the official documentation of Boolean Full-Text Searches:
Boolean full-text searches have these characteristics:
They do not automatically sort rows in order of decreasing relevance.
However, ...
1
vote
1answer
40 views
MySQL illegal mix of collations, ASCII to UTF-8
I have a MySQL 8.0 table like this, which is UTF-8 apart from one field, which holds an ASCII UUID that doesn't need UTF-8 overhead:
CREATE TABLE `things` (
`id` bigint unsigned NOT NULL ...
