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.

1
vote
0answers
26 views

Exclusive lock vs atomic(?) increment/decrement in MySQL

We're working on implementing an account with virtual currency in our app and we need to update account balance (classic, yeah?). Let's say we have an accounts table with user_id and balance fields. ...
1
vote
0answers
20 views

Unable to change character_set_server on MySQL

I have converted my database to UTF8 but the character_set_server is still Latin1. Is it compulsory to change it to UTF8 or can I just leave it like that? This is a production server, so it's not ...
1
vote
1answer
26 views

Join 3 tables, aggregate over date field and subtract sum of two columns

I have 3 tables and the they have the following columns. shipments: shipment_id (PK), shipment_date (date) invoices: invoice_id (PK), shipment_id (FK), amount (float) bills: bill_id (PK), ...
0
votes
0answers
40 views

Restore a 140GB - 292 Million entrie MySQL-Database faster?

i need help. I have an old innodb Server and my storage limit reached. I dont use files per table entries because in old times there were no ability at this time. My ibdata grown up fast and there is ...
0
votes
1answer
45 views

Sum between multiple joined tables

I have these tables: DROP TABLE IF EXISTS books; CREATE TABLE `books` ( `bookId` mediumint(8) UNSIGNED NOT NULL, `title` varchar(10) NOT NULL, ) ENGINE=InnoDB DEFAULT CHARSET=utf8; ALTER TABLE `...
0
votes
1answer
34 views

mysql 5.6 replication log-slave-updates doesn't work

We have a setup of master -> replication -> replication the dbs are: mysql 5.7 -> 5.6 -> 5.7 on the replication 5.6 we have the following my.cnf: [mysqld] default-storage-engine=InnoDB ...
0
votes
0answers
53 views

Can't run the newest version of MySQL on phpMyAdmin

I am building a database on phpMyAdmin. However, it doesn't include the latest version of MySQL: I downloaded the latest version of MySQL from this link: MySQL Community Server 8.0.16 However, even ...
0
votes
1answer
22 views

convert date to timestamp and store into table using trigger mysql

Using trigger I convert DateTime into a timestamp and then store into another table. But somehow convert date into timestamp always affected by timezone so that's why I had use convert_tz of date and ...
1
vote
1answer
23 views

How to select rows which are mapped to a junction table in a many to many relationship?

I have three tables: locations (has zipcode, location name); professionals (has name); location_professional. I want to select only those rows from locations table where professional_id is 4. What's ...
0
votes
1answer
21 views

Should i place Registered-Users Info in every table? to Restrict user access to his Own Data only

Database: Relational Database - MySQL I am making a database with multiple tables. In order to see his data from tables, User will need to log in. I only need to display those information(through web-...
0
votes
0answers
36 views

How can i write an update trigger between two tables

i have two tables one is called defaulters and users .defaulters has mypin and debt_amount and users has mypin and debtor .i want a trigger that update debtor in users table if mypin exist in users ...
1
vote
1answer
16 views

mysqldump changes primary key to unique key?

While attempting to dump and restore a WordPress database in MySQL, I noticed that mysqldump seems to be changing the PRIMARY KEY on several tables to a UNIQUE KEY instead. For example, this tables: ...
0
votes
1answer
15 views

MYSQL - Error in After Insert trigger affects the actual insert on the trigger table

I have two tables call it - table_1 and table_2 I have an After insert trigger on table_1 and in the trigger I insert on table_2 which has some fk constraints on another table_3. When the insert on ...
0
votes
1answer
34 views

MySQL Internals: SQL Execution Flow [closed]

This is a descriptive question regarding MySQL SQL flow architecture. I need a flow chart on the complete steps regarding MySQL SQL flow using the following components. eg the sequence an UPDATE query ...
0
votes
0answers
22 views

Blog database, and multiple categories

I have 3 tables. A table called blog_posts which has your traditional blog fields like content, image, .etc. and a table called blog_categories which stores a number of categories a blog can be ...
0
votes
1answer
26 views

How to sync easily between a master database from Google Cloud SQL and a master database from Amazon RDS

I've created a master - slave (replication) in Google Cloud SQL. I've also created a master - slave (replication) in Amazon RDS. I want to sync two master MysQL from Google Cloud SQL and Amazon RDS....
0
votes
0answers
18 views

Help structuring a query with multiple many-to-many relationships

Assume that there are 4 tables, with association tables "between" each of them: Machines Products Option Groups Options Machines may serve many products, and a product may "belong to" many machines. ...
0
votes
1answer
17 views

Return results of two MySQL query in only one

I am running two seperate queries to get statistics from 3 different tables: contacts campaigns sent_messages The first query counts all contacts that are marked as "stop" in the contacts table (...
0
votes
1answer
46 views

How can I write a trigger that can subtract values between two tables

I have a table called transaction that has these fields CREATE TABLE transaction ( transaction_id INT AUTO_INCREMENT, status varchar(255), myPIN varchar(255), amount_paid varchar(255)...
0
votes
1answer
23 views

MySQL replication process parallel with user process?

I have process which executes query like below on Mysql(InnoDB) slave INSERT ignore INTO user_profile_temp (some_fields) select some_fields from user_profile user_profile is bigtable of 8GB ...
-1
votes
1answer
23 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
28 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
48 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
16 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
30 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
22 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
43 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
29 views

MySQL XOR produces incorrect results

Why does the expression conv('9000084e703720f9', 16, 10) ^ conv('801A93A5A18A773F', 16, 10) produce wrong result? In a step by step explanation: SELECT conv('9000084e703720f9', 16, 10); Result: ...
0
votes
1answer
31 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
3answers
47 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
23 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 ...
1
vote
1answer
35 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
15 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 ...
1
vote
1answer
104 views

Table structure for entity versioning

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
21 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, ...
-2
votes
1answer
25 views

Join column to multiple columns in another table [closed]

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
2answers
25 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
43 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 ...
1
vote
0answers
14 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-...
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
24 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
18 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
28 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
33 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
28 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
26 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
17 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
29 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
19 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) )...