Stack Exchange Network

Stack Exchange network consists of 174 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
Join us in building a kind, collaborative learning community via our updated Code of Conduct.

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
8 views

I created a child table but it cant insert data when the parent table inserted data 'Cannot add or update a child row: a foreign key constraint fails

I used a trigger in a register table, when a user registered a new account it will trigger the below query Time: AFTER INSERT INTO users_reward(User_ID,reward_id) VALUES(User_ID,1),(User_ID,2),(...
0
votes
0answers
7 views

Enable GTID on one node in Percona/Galera

Im having 3 node percona cluster. (5.6) Server ID is enabled on each node but all are different IDs. Binlog and log slave updates are enabled. Now I want to enable GTID on the third node alone. I ...
0
votes
0answers
12 views

mysql how to let the foreign key table auto insert row when the primary table inserted new data

I have a user table, profile table, promotion point table etc. all these table has a foreign key refer to the user table in database, my problem is when a user register a new account the user table ...
0
votes
0answers
13 views

Select unique value pairs based on two participants that occur more than once inside a table

We are using MySQL where we have deals table that contains references to two users stored in two columns User_id1 & User_id2. There is no guaranty in which order those are stored. For example the ...
1
vote
1answer
71 views

How to make a table that could add many same number of different item?

I wanted to create a table to store coupons of users. For example, there is 3 coupon which is A, B and C etc. A user can have 10 coupon A, 20 coupon B and 5 coupon C etc. How should I create a table ...
0
votes
1answer
15 views

MySQL/MariaDB: Comparing performance between two queries

I am trying to compare the performance between two queries which give otherwise the same results. The idea is that I want to compare using Windowing Functions available to newer versions to faking ...
1
vote
1answer
19 views

Probable update error to mysql 5.7.21

I have a VM with Ubuntu 16.04.4 LTS. I use it for wordpress sites, so it haves mysql, nginx and php. I migrated from Azure to Vultr. For doing so I backed up all the databases in Azure and restored ...
3
votes
1answer
41 views

Running two MySQL servers that use the same data directory

The situation I have two MySQL instances, deployed on two different remote machines. Both MySQL instances access the same data directory, which is stored on a persistent volume (this volume is ...
-1
votes
1answer
31 views

Database design for a timesheet app?

I'm designing the database schema for a time tracking application and I need a little piece of advice. The application must permit the user: To enter for each day of the week the amount of time he ...
0
votes
1answer
29 views

Calculate Total Minutes of attendance in MySql

I have a design a table for attendance and here are the genral fileds AttendanceMachineLoginId EmpId AttendanceDateTime Whenever user come to office he has to make his attendance. The first ...
2
votes
1answer
27 views

Do MySQL (5.6) triggers support 4 byte emojis?

I'm using MySQL 5.6.34 with the following settings: "character_set_client","utf8mb4" "character_set_connection","utf8mb4" "character_set_database","utf8mb4" "character_set_filesystem","utf8mb4" "...
0
votes
1answer
25 views

mysql update based on select result, How combine?

Here are two SQL statements. One is an select statement and the other is a update statement. I want to combine them. Select SELECT * FROM test WHERE `id`='1' Update UPDATE test SET `select_count`=...
2
votes
1answer
19 views

Access denied error in trying to connect to a MySQL db in Python

I am trying to write a script that connects to a server, then connects to a MySQL db (which I currently can do via Navicat - so I know my username and password for the MySQL connection are correct). ...
-1
votes
2answers
33 views

Displaying data from multiple tables [on hold]

I have employee, job and application tables. I need to display all data from job where a specific employee didn't apply at application table. Employee: e_id pk e_name Job: j_id pk ...
1
vote
2answers
34 views

MySQL on Windows or Linux [on hold]

I am new to the Database region while have to migrate the company's database from AWS RDS to AWS EC2 and subscribe MySQL Enterprise edition. What I am confused now is which OS should I choose, ...
-1
votes
0answers
18 views

Export all related rows in mysql?

I'm downloading a table subset in mysql, and I have to manually export all the rows that are related through foreign keys from the other tables. Is there an automated way to download part of a table ...
2
votes
1answer
24 views

Why does this query not return records where both fields using CASE WHEN are zero?

I'm attempting challenge 13 on this SQLzoo page, but my query returns one fewer row than it's supposed to. SELECT mdate, team1, SUM(CASE WHEN teamid=team1 THEN 1 ELSE 0 END) AS score1, team2, ...
0
votes
1answer
16 views

Optimizing this union of three columns from the same view [on hold]

I have a view with about 10 or so columns, which from I've successfully combined three columns in their entirety using (SELECT Prim.column FROM columnOne AS Prim) UNION ALL (SELECT Sec....
0
votes
2answers
29 views

how to change this dynamic rows to collumns

I have following columns (it is dynamic, more rows in table with same ID).Type can be duplicate. +--------------+---------+------------ | ID | TYPE | VALUE | +--------------+---------...
0
votes
1answer
23 views

MariaDB order by before Group by

I have looked around at other solutions, but none seems to work quite right. What I am trying to do is to get the latest result (sorted by date) from a selection, grouped together by sample. Here is ...
0
votes
1answer
20 views

Organizing and assigning privileges to multiple objects in MySQL

I'm new to MySQL, moving from MS SQL Server, and I'm wondering if there's a way to logically group tables, views, sp's, etc in the same manner as you would with schemas in SQL Server....seeing as how ...
0
votes
2answers
34 views

automatic undo for version control in mysql

I'm not a mysql expert, but it seems like there is no obvious way to do that. I'll explain what I want. Let's say I'm doing a data migration which would update some rows in some table. I'd like to be ...
1
vote
1answer
41 views

Innodb updates records with delay

I have two tables in my database like these: CREATE TABLE `main` ( `id` int(11) NOT NULL AUTO_INCREMENT, `type` int(11) NOT NULL, `status` int(11) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB ...
0
votes
0answers
24 views

Import first few entries of a database [on hold]

How to import first few entries of mysqldb I have gone through answers which mention to execute in command line but my data base resides in remote but my tables are large with lot of entries how can i ...
0
votes
0answers
10 views

How do I connect to a remote Mysql Server which requires SSL and I'm given a Key & Cert?

I've received a Key & Cert to connect to a remote Mysql Server. However, per the documentation, it looks like I need a ca.pem as well? What I'm reading is that since a command line tool like ...
0
votes
2answers
21 views

Is possible to reuse a already calculatesd column into a query in MySQL?

I have a query with complicated column, and I would like to use this column result in another column, for instance: SELECT ( /* Complex query */ ) as myValue, if( myValue > 10, "OK" , "" ) from ...
1
vote
3answers
65 views

Mysql complicated join

I have two tables mysql> select * from emp; +----+-------+ | id | name | +----+-------+ | 1 | John | | 2 | Mari | | 3 | Sami | | 4 | Kate | +----+-------+ mysql> select * from dep; +----+---...
0
votes
1answer
14 views

Get name column instead of ids from two foreign keys

I need to get both group names as a result, although I have no idea how to do this query. Table 1 (groups) +---------+--------+ | id | name | +---------+--------+ | 1 | newb | | 2 ...
-1
votes
1answer
22 views

Selecting from MySQL database [on hold]

I have a table named activities which contain rows such as id, status and PID. In the same database I also have a table named projects which contains a row named id among other rows. So I want to ...
1
vote
3answers
840 views

How to delete duplicate records in MySQL, in a table without IDs?

I need to delete the duplicate records in this table. However, there is no id for each row. Example Data +---------+--------+----------+ | product | amount | quantity | +---------+--------+----------...
0
votes
0answers
14 views

mysql installment insertation kind

for example I have a computer. I want to sell with 3 installments worth $300 with post machine should I insert database like account table +---------------+--------+------------+ | DESCRIPTION | ...
0
votes
2answers
30 views

Calculate ticket sales by using SUM in SELECT clause

In this ticketing setup on dbfiddle, I am trying to calculate the ticket sales for a particular event by multiplying the ticket price and the total number of tickets sold for that event, based on ...
0
votes
0answers
14 views

What is the format of MySQL general log?

When I enable the general_log in MySQL, the file looks like this, with timestamps about once per second, and hundreds of lines in between without timestamps. What do the 8-digit numbers mean after ...
1
vote
1answer
22 views

How to get last and first occurrence of items into separated columns?

I do have the fallowing table: id name value 1 Daniel 3 2 Daniel 7 3 Daniel 2 4 Carol 9 5 Carol 4 6 Carol 9 7 Ray 5 8 Charles 1 I would like to get the first and the ...
0
votes
1answer
13 views

Sort products by image first and by name ASC

I have a table with products, some products have images, some of them don’t. I have to sort these products by name ASC but first products should be these with an image (field isn’t empty string), and ...
0
votes
0answers
22 views

Group 5 most visited locations

I've got a table in a MySQL database with thousands of check-ins from a GPS tracker. Every x seconds, a new location check-in is inserted. This is how the table looks like: +----+-----------+--------...
0
votes
0answers
10 views

Mysql Restore - ERROR 1227 (42000) at line 30908: Access denied DEFINER issue

Im trying to restore the database dump which has the DEFINER=test@%. Backup file is 50GB. OS - CentOS 7.5 While restoring this, I got the below error. ERROR 1227 (42000) at line 30908: ...
0
votes
0answers
23 views

MYSQL - Best way to search for a name or category like in the Yellow Pages

I have a website that is a business directory like YellowPages.com . I have the following table containing all the companies: mysql> describe companies; +-------------+--------------+------+-----+...
0
votes
1answer
11 views

Is it okay to stop my repair on my mysql database if it's not showing in processlist?

I started a repair on a table in my database that seems to still be running. mysql> repair table dicore_search_index; [empty line] It's just been sitting there like that since i started it about ...
1
vote
1answer
14 views

Wrong sort order using utf8_unicode_ci and swedish characters like ö

I am confused about what encoding and collation I should use so my queries returns the correct sort order. Right now my encoding is UTF-8 Unicode and collation utf8_unicode_ci for both firstname and ...
-4
votes
0answers
13 views

variables declaration error [closed]

DELIMITER $$ create procedure Transfer () begin declare ID_temp INT ; declare Name_Temp VARCHAR ; declare stop INT default 0 ; --error here declare Pointer cursor for select ID, ...
0
votes
0answers
10 views

How to write a query or get the desired output using a hierarchical query in PHP and mySql [migrated]

Code written by me is as below: public function index() { $table_name = 'org_chart'; $data['employee_data'] = $this->common_model->get_all_data($table_name); for($...
1
vote
2answers
34 views

MySQL 5.6.27: How to get latest pairs for different points in time in a single query?

I use the following query to get pairs of latest s1.value for given group ids and some fixed date. Now if I want to query pairs for different dates I have to run this query several times with ...
3
votes
2answers
48 views

Creating same unique constraint with different names

In MySQL 5.7 alter table mydb.roles add constraint uk1 unique (role_name); alter table mydb.roles add constraint uk2 unique (role_name); The 2nd alter command threw me a warning stating: ...
-1
votes
0answers
31 views

Why does the timestamp format change after I fetch it from table? [migrated]

My table's structure: +------------+---------------+------+-----+-------------------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+--...
0
votes
1answer
23 views

MySQL NOW() function - whose clock is used?

For the mysql NOW() function, whose system time is used for time calculations? The host of the database or the client executing the statement?
2
votes
1answer
23 views

How to spec a machine for a particular database load?

Our primary authentication database that is used for 100k+ users, and growing is a VM on a shared machine that we want to extract into its own isolated hardware. This will let us isolate resources, ...
1
vote
1answer
10 views

Query duration in MySQL General log

MySQL has feature of logging all sql queries - General log. But this log does not contain queries durations. How to log all sql queries to analyze those durations?
3
votes
1answer
40 views

Complex joins with SUM

I am going to describe this problem as best I can. Hopefully someone can make sense of what I'm dealing with and can help me get out of this mess. This is a multiline, relatively complex query ...
0
votes
1answer
13 views

How to delete previous incarnation in MySQL (/w Group Replication)

There exist almost no mentions whatsoever of MySQL "incarnations", so I'm opening this topic to make a precedent, even if I solve it myself. Feel free to help of course :) 2018-08-10T16:21:34....