Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

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.

0
votes
0answers
23 views

MySQL query for free Date time slot availability

I am trying to find free Date time blocks on current month and db value DB Structure- Event_ID | dt_start | dt_end +----------+---------------------+---------------...
0
votes
1answer
17 views

Subtract values in same column from 2 different dates in same Mysql table

I have a MySql table like: Item Date Likes Book 2012-05-20 402 Book 2012-05-21 432 Book 2012-05-22 450 Pen 2012-05-20 20 Pen 2012-05-...
0
votes
0answers
8 views

Replicate multiple server databases to single slave server

I have several databases in several locations running with MySQL 5.7.25. Now i want to replicate all these databases to single server. Master database servers (All databases are with same name) ...
0
votes
1answer
33 views

MySQL not using the correct index

I have following readings table, it has around 500 million rows. CREATE TABLE readings ( id int(11) NOT NULL AUTO_INCREMENT, value double NOT NULL, created_dt datetime NOT NULL, device_id ...
0
votes
0answers
22 views

Differences of MySQL locking for update with WHERE and IN

So I have a table with id as INT NOT NULL as the PK. When I do query 1 (Trans1) in a transaction I get 3 rows locked, and when I do query 2 (Trans2) also in a transaction, I do also get 3 rows locked. ...
0
votes
1answer
30 views

Innodb cluster setup error

I tried to configure innodb cluster, but its resulting into errors. Below are the listed error: when creating the cluster on the primary node for the first time using cluster=dba.createCluster('...
0
votes
0answers
13 views

Where and how to set the variables for Percona MySQL Audit Variables

I am trying to set the variable values for Percona MySQL Audit Plugin in my.cnf file before installing the audit plugin on MySQL Server like 'audit_log_rotate_on_size','audit_log_rotations' and '...
0
votes
1answer
22 views

Booking Syntax error SQL

I did a trigger but it's giving me an error message, it must be a syntax issue. Does anyone see any problem with this. DELIMITER // CREATE TRIGGER Carperperson BEFORE INSERT ON bookings FOR EACH ROW ...
0
votes
1answer
15 views

InnoDB cluster error: ReferenceError: cluster is not defined

I was setting up the MySQL InnoDB cluster, when I got this error. I created the cluster from the primary node, the cluster.status() was giving output as 'ONLINE' at this point with only one node part ...
0
votes
0answers
16 views

MariaDB: *.mad file causes full disc space

I am using MariaDB 10.1 My hard disk has 200GB space but after a couple of minutes a file xxx.MAD causes full disc space. I don't know how these files were created. How can I solve this? Why causes ...
0
votes
0answers
9 views

Recover file MySQL .IBD [duplicate]

had a problem with a database on a machine that needed to be formatted, and I backed up the MySQL Server folder yesterday containing the .IDB and .FRM data regarding the DB structure and data. When ...
1
vote
1answer
25 views

Proper way to map IDs: By adding additonal columns for each external column or by inserting new rows

I am working on a new database structure in which we get external IDS from a few different sources and map them against our internal ID in one table. Method 1 In the first version the table ...
0
votes
0answers
24 views

Mariadb docker container SQLSTATE[HY000] [2002] No such file or directory error

I have developed an web application that consists of a app, a website and a mariadb database. I put them together in a docker-compose file and tested it on my local computer and it works fine. I tried ...
-3
votes
0answers
13 views

JOINING SCHEDULER TABLES IN OBIEE [on hold]

Hi these are the four tables I want to join all the four tables such that it is grouped by job id and the relative order is grouped into single line by job_id.
0
votes
0answers
18 views

How to find the MySQL query that got rolled back

I want to know if a certain transaction got rolled back. If we analyze SHOW ENGINE INNODB STATUS\G; we get the list rolled back transactions with transactionid , but how to get the actual query ...
0
votes
0answers
12 views

Restoring MySQL database data from ibdata1 and ib_logfile

I am trying to restore a MySQL database. I have the following: ibdata1 ib_logfile0 ib_logfile1 .frm files restored as I have a backup of the structure. Is it possible to restore the data for the ...
0
votes
0answers
17 views

How to remove duplicate records in MySQL?

Below is my SQL syntax to query for a given name Vincent Ho, and returning all the matching records associated with the name. However, I would like to only keep the first 2 rows of the output and ...
0
votes
0answers
22 views

MySQL reporting version mismatch due to picking up previous installation

To solve a mysqldump "Workbench was unable to get mysqldump version. Please verify the log for more information.", I opted for the first solution at https://stackoverflow.com/a/44851129/175241 But ...
0
votes
0answers
24 views

Join 2 subqueries in MySQL [on hold]

May I ask if anyone knows how to join 2 subqueries in MySQL? The following syntax below ran into a syntax error when I executed it on MySQL. SELECT * FROM ( SELECT Staff_ID1 FROM phase_2.same_staff ...
0
votes
2answers
26 views

Poor performance on a 'leaderboard' query

Consider the following 2 tables: players: | rooms: id | id name | type room_id | embed_code_id points | My query needs to fetch the 100 players with the most points from a given ...
-1
votes
0answers
29 views

How to create stored procedure involving wildcards? [on hold]

I have a question, I want to write a stored procedure given a name in the where clause of the query, return all matching identities of the staff members who have staff name that contains the queried ...
-1
votes
0answers
36 views

MySQL Missing Closing Parenthesis

I have DDL below to create a Speech Table, however, there is an error missing 'closing parenthesis'. When I tried to run, the foreign keys of the Speech table, i.e. (Presenter, Job_ID, Post_Date) were ...
0
votes
2answers
20 views

How to set all auto increments to 1 in a Schema

Is it possible to reset all auto increment in a schema in one query? if not even just by per table will do. btw I am using MySQL server 8 thanks in advance :)
0
votes
0answers
33 views

MySQL how to GROUP SUM based on JOIN TABLE

I have Three Table, One Master Table and Other Two Table have relation with it. Below is the Three Table: 1. ball_by_ball table, this table holds full information by ball by ball: CREATE TABLE `...
1
vote
2answers
34 views

Double FOREIGN KEY, i.e. how to create a table with two different FOREIGN KEYs that reference a single PRIMARY KEY in another table - DDL

I have an issue with my SQL syntax when creating a table with a set of Foreign Key (Orig_Dept_ID & New_Dept_ID) that references Dept_ID in the Department Table. Below is my sql syntax: CREATE ...
0
votes
1answer
10 views

duplicate entries from mysql LIKE query

with this sample dataset I have this query SELECT products.id, products.title AS product_title, products.code, products....
0
votes
0answers
10 views

Symfony Quiz Entity Design

Dear Helpful SE Users, In my quest to get more experience of Symfony I thought I'd write a quiz website which I can use at school (I know that moodle does this but it is a bit overkill for my ...
0
votes
1answer
14 views

Import database Mysql structure Only

I have a huge dump.sql Mysql database of about 3GB that has the structure and the data
1
vote
0answers
9 views

MySQL RDS - Unable to Kill Hung Queries

I have a MySQL RDS instance that's got 100+ queries all stuck waiting for a table metadata lock. The good news I have permission to kill all of the sessions, and have 0 concern for data loss as this ...
0
votes
0answers
7 views

Use data from Mysql to ElasticSearch with Logstash

I'm using logstash for use my mysql database in ElasticSearch My conf is the next. input { jdbc { jdbc_connection_string => "jdbc:mysql://[ip]:3306/nextline_dev" jdbc_user =&...
0
votes
0answers
17 views

Unknown column “X” in field list on delete, but the column exists

I'm using MySQL and i'm trying to run a delete statement as follows: DELETE FROM `myTable` WHERE `nincidusua` = 84 and the following error occurs: Unknown column 'nincidusua' in 'field list' My ...
1
vote
1answer
36 views

Why Output is wrong after doing the left join as subquery

I have TWO QUERY which work perfect, but when i join those two query as LEFT JOIN the output become wrong. So what is the problem.? Query 1: select Batsman.innings_no, bowler, sum(...
0
votes
0answers
15 views

Joining multiple (10+) tables IN CASE attribute exists, min 1, max all

I've asked a version of the question here Allow Null Columns vs separate 1-to-many tables and here Multiple JOINs of possible properties in a 'flower' arrangement - acceptable design? but ...
0
votes
1answer
16 views

Access denied for user 'root'@'localhost' What proces is causing this?

I got this connections constantly in logs, how to trace what app is trying to connect? root@localhost is allowed to connect and working for some apps. I think there is some app without changed ...
0
votes
1answer
18 views

MySQL - Split results of count function into columns derived from a separate column

The title seems a bit confusing, but I am struggling to put down what I need this query to do so best to explain it. I have 3 tables in my database (Using MySQL Workbench), but for this query I'm just ...
0
votes
1answer
20 views

Syntax Error on Create Trigger

CREATE TRIGGER new_trx_tmp_on_transaction_update AFTER UPDATE ON transaction FOR EACH ROW BEGIN IF NEW.trx_status = 1 AND ( NEW.cat_id = 'CASHBACK' || NEW.cat_id = 'REFUND') THEN INSERT ...
0
votes
1answer
24 views

Make primary key and unique id as foriegn key

I have two tables as follows: UserProfile CREATE TABLE IF NOT EXISTS USER_PROFILE ( id BIGINT NOT NULL AUTO_INCREMENT, user_id BIGINT NOT NULL, name ...
0
votes
0answers
17 views

ERROR 1794 (HY000): Slave is not configured or failed to initialize properly

This is my my.cnf file: [mysqld] server_id = 1 log_bin = /var/log/mysql/mysql-bin.log log_bin_index = /var/log/mysql/mysql-bin.log.index relay_log = /var/log/...
0
votes
2answers
26 views

Select rows where one row meets a condition

I have a table with 3 columns: UserId ProductId Status 1 100 1 1 1 0 2 100 0 4 100 0 5 1 1 5 100 1 6 ...
2
votes
3answers
40 views

How to group many information une one select

There are my database articles id | art_code | art_name 1 | 5678 | computer 2 | 0987 | display 3 | 8547 | keyboard action id | code | action | qte 1 | 5678 | IN ...
0
votes
0answers
12 views

MySQL slave connect to master error_code 2005

I have a master running MySQL 5.6.17 on Windows 10, and a slave running MySQL 5.7.25 on an Ubuntu 17.10 box on a (Windows) LAN. Replication ran fine until Ubuntu upgrade to 18.04. Now slave ...
0
votes
1answer
23 views

Mysql replication rebuild dump database lock

I need to rebuild master-slave replication of whole instance of mariadb on innodb. What I often see is that people do read lock then dump master then unlock, like: FLUSH TABLES WITH READ LOCK; SHOW ...
0
votes
1answer
28 views

How does MySQL DELETE work internally?

I have two major doubts: Does a DELETE query first identify all rows to be deleted (from the where clause) and then delete them all, or does it delete rows as and when they match the where clause? ...
0
votes
1answer
33 views

How can I reset Aborted_connects in MySQL?

A recent issue in our network caused a large number of aborted connects, which is now being displayed on the health dashboard of our web application as a potential issue. Aside from restarting MySQL, ...
0
votes
1answer
34 views

Complex join 3 tables with sum and group by

I'm trying to generate a report for one of our departments that pulls data from 3 tables. The basic structure is below: CREATE TABLE `evd_us_county_mio_2018q4` ( `state_code` INT(11) NULL DEFAULT ...
0
votes
1answer
29 views

MySQL shuts downs unexpectedly and can't restart

please I need assistance with this error. My MySQL stopped working and each time I start MYSQL on the XAMPP Control Panel, it initiates an error as shown on the screenshot below. I just reinstalled ...
0
votes
0answers
24 views

Error Code: 1172. Result consisted of more than one row [on hold]

create procedure incremental() begin declare id2 int; declare title2 int; select sid,stitle into id2,title2 from stbl; insert into dtbl (did, dtitle) values (id2, title2) on duplicate key update sid = ...
0
votes
1answer
21 views

Planning and Optimizing the table structure InnoDB MySql

I am planning to have an InnoDB MySql table with tens of millions of rows. Each row represents a record that can belong to AT LEAST one of 200+ categories. Most of the records will belong to 2 or ...
0
votes
1answer
24 views

Export data from a select join for testing [on hold]

I have a Select statment that joins two tables. SELECT [Config].[Id], [Version], [ConfigSchemaId], [ConfigType], [ConfigSubType], [ConfigData].[Data] FROM [dbo].[Config] INNER JOIN [dbo].[...
1
vote
1answer
36 views

UNION or conditional JOIN?

I have two queries that I am joining using UNION. See below query. SELECT * FROM ( SELECT so.`so-no`, so.`so-date`, po.`po-no`, po.`po-date`, si.`si-no`, si.`si-date`, pay.`or-no`, pay.`or-date`,...