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
1answer
15 views

Correlated Subquery can't access column from outer query

Given the following query which contains a subquery: SELECT COUNT(DISTINCT sc.caseID) AS 'TotalCases', (SELECT COUNT(DISTINCT se1.caseID) FROM scEvent AS se1 INNER JOIN scCase ...
1
vote
1answer
17 views

How to TRUNCATE table if the table exists

I want to run a few TRUNCATE statements. But in between some of the database doesn't exists. But SQL stops the rest of the queries. Is there any way to ignore the error and continue to execute the ...
0
votes
1answer
11 views

Mysql not writing to binlog

Mysql is not writing events to binlog, I have tried change the binlog configurations, but can't solve. Conf: server_id = 2 log-bin = master binlog_format = row replicate-do-db = our_db log-slave-...
0
votes
0answers
9 views

Lock wait timeout in error.log

Is there a way to put messages like 'Error Code: 1205. Lock wait timeout exceeded; try restarting transaction' into error.log in MySQL? MySQL: 5.7.26 | log_error | /var/log/mysql/error.lo ...
0
votes
1answer
18 views

In a replication setup, does corrupted data also gets replicated?

Say one of the tables got corrupted, will that corrupted table still get replicated to the other DB? Or, will the replication stop to prevent the other DB from being corrupted too? Note: Question is ...
0
votes
1answer
18 views

SQL substract values from same column different rows

I have above mysql stock table where both inward and outward data are stored and identified each type by type column (i and o). I want to build a query to retrieve balance quantity(qty) per each ...
0
votes
1answer
22 views

MySQL/innodb trying to lock uncommitted row from parallel transaction, deadlock in result

MySQL 8.0.16, 2 simple RC transactions, each trying to insert and delete rows: CREATE TABLE test1 ( id int(11) NOT NULL AUTO_INCREMENT, name varchar(100), PRIMARY KEY (id) ) ...
1
vote
1answer
24 views

MySQL XOR produces incorrect results

Why does the expression conv('9000084e703720f9', 16, 10) ^ conv('801A93A5A18A773F', 16, 10) produces wrong result? In a step by step explanation: SELECT conv('9000084e703720f9', 16, 10); Result: ...
0
votes
1answer
26 views

Dynamic LIMIT based on query time

I'm writing scripts in Python for cleaning up a database. It mainly is finding and deleting billions of rows of data based on some logic. I have it running in a loop with a LIMIT that will run until ...
0
votes
1answer
28 views

How to generate unique and sequential Id values

We have an entity called Task which has properties like id, vendor id, name. So I created the following table to represent it: CREATE TABLE `Task` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, ...
0
votes
0answers
21 views

When does autocommit take place when many rows are inserted as a single statement?

I have some problems with performance of insertion of about 50000 rows into a table. As an optimization it's advised to start a transaction explicitly to avoid committing on each row. However, I ...
0
votes
0answers
19 views

Mysql: Insert into JSON column type with GROUP_CONCAT

I'm trying to insert a list of words into a column with the JSON type: create table my_table ( ... synonyms JSON ... ) My query looks like this: INSERT into my_table (id, synonyms) ...
0
votes
1answer
13 views

Mysql Command line Parameter passing to sql file

In windows mysql(5.7 version), I want to run mysql commmand using command line for .sql file as input . Also i was to pass some variable whose value should get used in .sql file. somehow I am not able ...
0
votes
1answer
17 views

JOIN in 4 tables gets phpmyadmin unresponsive

I can´t manage to create a valid query that shows me 5 columns from these 4 tables: As you can see the relationships that i'm trying to JOIN in the query are highlighted in differents colors. The ...
0
votes
0answers
24 views

Table structure for a revisioning with auditing system

For an assignment I have to build an application that is able to store tests. Every test has to be validated by a a different user when created or updated. When it has been validated by an user, the ...
0
votes
1answer
18 views

Is “--one-database” safe to use for importing from a dump?

If I make a full dump using mysqldump --all-databases > dump.sql and then I want to import just a single database, I can use mysql --one-database db1 < dump.sql. According to the documentation, ...
-3
votes
0answers
17 views

The multi-part identifier “transact_clntab.ACCT_CODE” could not be bound [duplicate]

FROM transact_clntab t, CLNTAB ct LEFT JOIN clntab ON ct.ACCT_CODE = t.ACCT_CODE group BY t.acct_code HAVING MAX(t.trade_date) < '2015-01-01' AND MAX(t.trade_date) >= '2008-01-01' Please,...
-5
votes
0answers
29 views

Joining column to another table [on hold]

I am having trouble joining this query (TRANSACT_CLNTAB) with another column in another table (CLNTAB) The common column is ACCT_CODE FROM transact_clntab t group BY t.acct_code HAVING MAX(t....
-1
votes
0answers
16 views

To check duplicate email ID while new user register in database -php-mysqli [on hold]

I am using the following code so that when it will check the duplicate email ( if already registered in database) it should give email id already registered. But it is not showing that however it go ...
0
votes
0answers
23 views

CSV to SQL Import Script

I have CSV log files generated every 10 minutes automatically. I would like to know the best scripting method to automatically import the CSV file to mysql and put the data in the correct table by ...
-2
votes
1answer
24 views

Join column to multiple columns in another table [on hold]

I want to join this query result TRANSACT_CLNTAB with columns in another table CLNTAB SELECT t.acct_code FROM transact_clntab t group BY t.acct_code HAVING MAX(t.trade_date) < '2015-01-...
0
votes
1answer
16 views

Application performance [on hold]

I need to have users table in which each user has one of few different types (ex. admin, manager, basic user etc.). So my question is: Is it better to just make an attribute in user table and fill ...
0
votes
0answers
11 views

Maximum query length in SQL Phpmyadmin

I am running a very long query in mysql and it is throwing the error It seems that the connection to server has been lost. Please check your network connectivity and server status. I have searched ...
0
votes
1answer
20 views

Create a generated column from the last 100 occurrences for a given user ID [on hold]

I have Three tables, Table1 stores post engagement rate (rate_new_1) for Social media posts (shortcode) Table2 stores post information including taken_at_timestamp the third table Stores information ...
-1
votes
2answers
24 views

What I want is to Find clients that started since 2008 and stopped trading in 2014 but hasn't done any transaction(s) from 2014 till date [on hold]

What I want is to Find clients that started since 2008 and stopped trading in 2014 but hasn't done any transaction(s) from 2014 till date SELECT DISTINCT chn, TRADE_DATE, acct_code FROM ...
0
votes
1answer
12 views

MariaDB - So many threads?

I'm on a test VM based on VMWare with MariaDB + nginx installed; when looking with htop this is the situation: So we have 26 threads under the main process. I know that inactive threads doesn't ...
0
votes
1answer
31 views

Will too many tables slow Mysql down?

I'm making a database which should be able to handle over 1,000,000,000 rows. in order to optimize it's performance i've decided to split tables. so instead of having 1,000,000,000 entries in one ...
0
votes
0answers
10 views

How to get the corrupted table from a log file in hex?

I'm running a Magento 2 site, everyday we have a MySQL shutdown because of some corrupted data, we don't know the table causing the issue, this is what the error logs shows: Version: '5.7.26-...
-4
votes
0answers
21 views

Triggers and procedures [on hold]

Based on the following tables, please answer the questions below:
0
votes
0answers
19 views

how to divide and insert post value integer data in mysql two column using php? [migrated]

<form> Service fee : <input type="text" value="300" name="gst"> <select> <option>CGST 9% AND SGST 9% on service fee</option> <option>IGST 18% on service fee</...
0
votes
1answer
23 views

MySQL: Can not add foreign key into create table statement

The queries below (assigned to php variables) seem to have an issue on the 2nd query, where I set the foreign key to the "couponId" column on the first table. My IDE lints "couponId" (inside the ...
0
votes
0answers
14 views

How can I figure out the root cause of “Out of range value for column” error in MySQL

I have a console app written with C# on the top of .Net Core 2.2 framework with EntityFrameworkCore as ORM. My application runs various tasks that run every 15 minutes. Each task calls an external API ...
0
votes
1answer
26 views

What will be the order of statement execution in this use case?

Say I execute select * from users at t1 which gets finised at t3. At t2(in between t1 and t3) update comes for some of the records in users table say update users set address='xyz' where name = 'abc'....
0
votes
1answer
28 views

Creating a database for a scenario involving students, teachers and courses

I am undertaking a project that involves a student management system, but I am struggling to normalise/create relationships between certain tables. Here is my current setup: Models Users(id, first ...
0
votes
1answer
21 views

Which MySQL shows CPU usage?

I am monitoring MySQL server using phpMyAdmin [Status => Monitor] function. And it shows a number of useful graphs. Which variables (or which mechanism) is phpMyAdmin using to pull this data? ps. ...
1
vote
0answers
22 views

Mariadb master master replication supported by HAPROXY for automatic failover

We have implemented master-master setup in maria-db. Master A is being used for read\write and Master B is used for backups and MIS operations. We have built this setup so that we do not need to ...
-1
votes
0answers
15 views

Booking schema advice

I am starting to put together a database to book students on activities for a week. a tutor needs to log in and have access to all students in their tutor group. there are 4 activity slots per day (...
-1
votes
1answer
12 views

Operand should contain 1 column(s)

Hello I have problem with this query INSERT INTO radacct (acctsessionid,acctuniqueid,username, realm,nasipaddress,nasportid, nasporttype,acctstarttime, acctupdatetime, acctstoptime,...
0
votes
1answer
18 views

How to write a query to get concatenated names of riders with the same weight?

I want to retrieve a table with all Riders of the same Weight from a table named Rider, which is defined in this way: CREATE TABLE Rider( name VARCHAR(30), weight REAL, PRIMARY KEY(name) )...
2
votes
2answers
27 views

Inconsistent performance of WHERE and ORDER BY depending on size of WHERE match

Been working with MySQL for 15 years, and never seen anything quite like this before. MySQL table with 4 million records. I have found a very strange "sour spot" (opposite of "sweet spot") in ...
0
votes
1answer
23 views

Mysql 5.7 - select where json

I have some JSON data stored in a filters column. An example from one row: name | filters ---------+---------------------------------------------------------- John Doe | [{"Year":2004, "SportID":...
2
votes
1answer
18 views

MySQL 8.0 Replication filters lost between restarts

I don't know if I fell on a bug or a feature, I read the documentation several times but haven't found the information. I have two MySQL servers, both on 8.0.15 and hosted on a Windows Server (x64). ...
0
votes
1answer
27 views

How do I calculate the average weight of riders by competition?

I have an assignment where I need to calculate the average Weight of Riders by Competition. The assignment has a lot of different parts but I got stuck on this one so please help my out if you can. I ...
0
votes
1answer
17 views

Analyzing a query plan for a query with TWO predicates, but index only has ONE

Note: This is from an introductory database systems course at my university so I'm not entirely sure if it matters, but for what it's worth we are being taught MySQL. One homework question I'm doing ...
0
votes
0answers
30 views

MySQL 5.1.73 Tuning for PHP5.6 Application [on hold]

I have a PHP5.6 application running Symfony 2.x, backed by MySQL 5.1.73. We are currently experiencing latency in our data tier during peak load. During normal operation we see about 2k queries per ...
1
vote
1answer
22 views

Filtering a MySQL table on multiple columns, including one with spatial data

I have a MySQL table with linestrings: CREATE TABLE edges ( id int(11) unsigned NOT NULL AUTO_INCREMENT, geometry linestring NOT NULL, type_key varchar(255) NOT NULL, created_at datetime NOT ...
0
votes
1answer
20 views

ENUM column auto set

I have two tables "using" foreign keys: Referencing table CREATE TABLE `requests` ( `request_id` int(11) NOT NULL AUTO_INCREMENT, `user_id` int(11) DEFAULT NULL, -- `status` enum('new','...
0
votes
1answer
26 views

MySQL CLustering Options

I have two MySQL Database Servers i.e. One is Active Server --> Master and second is Passive Server --> Slave. And as we know we have 3-4 MySQL HR,DR Solutions / Clustering options.For example 01 : ...
0
votes
1answer
15 views

why does MySQL Trigger that uses cursor only fetch one value [closed]

I have created a Trigger after INSERT on the table AcademicYearTermLevel which uses a cursor value fetched from another query. I want to use each cursor value in a while loop and insert rows into ...
1
vote
0answers
37 views

The GET_LOCK() and RELEASE_LOCK() functions are taking too long

We are trying to use the MySQL's locking functions GET_LOCK() and RELEASE_LOCK() in our Spring Java application. The code we are using is defined in a procedure for both seperately which simply invoke ...