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

MySQL Replicated table: How can I prevent a trigger chain?

I've written a trigger to log updates on a replicated table. The trigger inserts into a separate table. We use MIXED replication which defaults to statement-based. The trigger spins around and ...
-1
votes
0answers
11 views

Help with correction of writing SQL queries

I have questions, but i never work with SQL before. Can someone help with queries? 1) "How many persons with Title = 'Mr' [1] have 'Purchasing Manager' type [2] of the contract [3]" 2) Find email ...
0
votes
0answers
7 views

Restructure create and delete events on rows into single table

Similar to this problem: Using start and end event logs to create a table/view containing spans between the times of each log. I have table that logs 'create' and 'delete' events for a resource that ...
0
votes
1answer
14 views

How do i get a list of records from two tables that have different values based on a matching PK?

Lets say i have the following two tables: Table1: |IP |Name | Gateway | Version | +------+------+---------+---------+ |1 |A | 1.0.0 | v1 | +------+------+---------+---------+ |2 ...
0
votes
1answer
25 views

How to link customers and suppliers in a database

Some customers can be suppliers at the same time, so: how to design the database so we don't have two tables having the same columns, and, at the same time, we can link a customer and a supplier ...
0
votes
1answer
24 views

how to improve this query performance

I have an application that's running this following query SELECT SQL_NO_CACHE p.*, product_shop.*, stock.out_of_stock, IFNULL(stock....
0
votes
0answers
12 views

Why am I getting this counter-intuitive relevancy result to a MySQL full-text query?

I'm querying for +2019* +sponsor* +BGHDA* and getting this result: 2019 sponsor BGHDA #7 Springfield, WA 2019 03 04 relevancy: 34.139 2019 sponsor BGHDA relevancy: 26.087 Given that my ...
0
votes
2answers
24 views

How can I query 60 timestamp in a SQL?

Suppose I have a table CREATE TABLE `monitor_index_data` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `index_id` bigint(20) NOT NULL, `index_time` bigint(20) NOT NULL, `value` double ...
1
vote
0answers
15 views

How to prevent a record from being affected by the ORDER BY RAND() function?

I am building an online exam site using php and mysql. It's just for MCQ type questions. Here is my database structure: 1) QUESTIONS ( Exam_ID, Questions_ID, Questions_Des) 2) OPTIONS ( Question_ID, ...
0
votes
0answers
13 views

Can we use MariaDB mysqldump CLI to take dump from MySQL Community version?

MySQL server version: mysqld Ver 5.7.26 for Linux on x86_64 (MySQL Community Server (GPL)) MariaDB mysqldump CLI version: mysqldump Ver 10.17 Distrib 10.3.12-MariaDB, for Linux (x86_64) I am ...
1
vote
1answer
11 views

How to make delete duplicates faster?

On a mysql table with about 1.7M rows, I tried to delete duplicates posts: delete a FROM comment a INNER JOIN comment a2 WHERE a.id < a2.id AND a.body = a2.body; The result was: ...
-2
votes
0answers
10 views

Please can anyone tell proper sources to learn how to create ETLs in pentaho? [on hold]

Creating ETL in pentaho tutorials and sources. I havent been able to come across proper sources.
0
votes
0answers
11 views

using Federated tables for different data sources

We are planning to create a standalone MySQL server specifically for Federated tables that points to 4 DB instances. Sample table: federated_db1_users federated_db1_transactions federated_db2_users ...
0
votes
0answers
27 views

How to formulate a query and view for the following structure?

Consider the following table structure: TableA ColumnA ColumnB ColumnC Ro1 A 1 Ro1 A 1 Ro7 A 3 Ro2 C 1 Ro3 B 1 Ro3 B 1 ...
0
votes
1answer
13 views

How to prevent a record from being affected by the ORDER BY RAND statement in MYSQL

I have a series of problems all which I can't put in the title but I will try to explain them as best as I can. I am building an online exam platform with PHP and MySQL. Problem 1: Prevent a record ...
1
vote
1answer
19 views

SQL sort by sum of two different where clauses in a join

table1 ID | Name 1 ID1 2 ID2 3 ID3 table2 ID | table1ID | Values 1 1 3 2 2 3 3 3 2 4 1 3 5 2 1 6 3 2 7 1 ...
1
vote
0answers
10 views

MySQL Percrona 8.0 became suddenly slow until restart mysql

My website today suddenly became slow and all requests were taking about 2 seconds to complete. Usually it takes like 0.2 second I checked available space on disk and I have a lot. Memory and CPU ...
0
votes
0answers
10 views

error occurs when starting mysql group replication

With MySQL 8.0.15, I tried to set up the group replication according to the manual(not all the same steps), but the errors occurred. Maybe I missed some details. I was trying to set up the first ...
0
votes
1answer
20 views

How to get dates from a table while the date range is from the date belonging to another table?

I have a table datedim which has all the dates of a month in it. I have another table attendance that has dates on which attendance were taken. I need to write a query where i can get all the dates ...
0
votes
0answers
21 views

Mysql ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

I installed mysql-5.7 on solaris 11. Installation went successfully, but I'm not able to find the file which contains the temporary password. I'm getting this error, when I try to login as mysql -u ...
0
votes
1answer
29 views

Efficient partition pruning with ORDER BY on same column as PARTITION BY RANGE + LIMIT?

I've set up a table in MariaDB (10.4.5, currently RC) with InnoDB using partitioning by a column of which its value is incrementing-only and new data is always inserted at the end. For this case, ...
1
vote
1answer
72 views

How to optimize a MySQL joining to itself?

I need to optimize a MySQL (5.7.12) query. I've been reading the docs about optimization in MySQL docs but It's been hard for me to comprehend it. I have two tables: vehicles CREATE TABLE `vehicles` ...
0
votes
0answers
22 views

Mysql 5.7.25 crash on first query to corrupted innodb table

Setup: Ubuntu 18.04.1 LTS 4.15.0-34-generic #37-Ubuntu Docker: 18.06.1-ce build e68fc7a mysql:5@sha256:e47e309f72c831cf880cc0e1990b9c5ac427016acdc71346a36c83806ca79bb4 mysql --version = Ver 14.14 ...
0
votes
1answer
24 views

MySQL (Percona): Multiple simultaneous long-running connections

I'm running a distributed app in the cloud where clients keep open multiple long-running connections to a MySQL (Percona) server. After updating to the latest Percona release, the maximum number of ...
1
vote
1answer
20 views

Way to check where the MySQL connection was initialized?

Is there a way to identify who created the MySQL connection? For example: in file a.php $conn = mysqli_connect($dbhost, $dbuser, $dbpwd, null, $port, $socket); I would like to know that this ...
0
votes
2answers
31 views

distinct select from 2 databases

I have 2 databases with the same structure massfacturation and massfacturation_old and I want to simply combine 2 queries to extract a count like: > select count(distinct(sciper)) as usercount, ...
0
votes
2answers
19 views

MySQL compare values of two large tables

On MySQL 5.7 I want to get the records from table 1 that are not present in table 2. I'm using this simple LEFT JOIN query that is very slow (more than 2 minutes), I don't know why. SELECT * FROM t1 ...
1
vote
1answer
15 views

MySQL change user login from one plugin type to another

I'm in the process of testing MySQL 8.0. I have set up a test database, and the first thing I want to do is import my user account from MySQL 5.7. So to do this I run: /*ON MySQL 5.7*/ SHOW CREATE ...
0
votes
2answers
35 views

Select users that are in the same team

We have these 3 tables: USERS userid name email TEAMS teamid name options (0,1,2 or 3) TEAMUSER id teamid userid isadmin (0 or 1, if they are administrator of team) Let's say we have user 1, 2 ...
0
votes
1answer
43 views

How to store tax for a Product when tax rate for a Product can change and tax-exempted Products

Product id name price Tax id name rate Product_Tax Product.id Tax.id effective date expiry date Is this a good design? How to store Products that are tax-exempted? Also, how to handle the ...
0
votes
1answer
47 views

How to store tax in the database when tax consists of multiple taxes

We need to design Tax table for Billing System. Lets say we have a SGST (State Tax) and CGST(Central Tax) and Final Intra-State Tax = SGST + CGST Tax table id name rate Tax table id | name ...
0
votes
1answer
34 views

What's the best way to migrate from mariadb to mysql on ubuntu 18.04

I've got a server running on ubuntu 18.04.1 with mariadb 10.1. and I have to change to mysql. The reason is that mariadb isn't supported by a product I want to buy. Searching around the internet I've ...
0
votes
0answers
23 views

The USE-Statement breaks on MySQL-Server

running webadmin for years now - and having troubles for months - and posting here for weeks. But the support seems to be not the best... want to run sql-commands on the newest version - but it fails ...
-7
votes
0answers
33 views

Anyone can help me suggest or give a sample query to generate this sample output in sql server or mysql database [on hold]

Anyone can help me suggest or give a sample query to generate this sample output in sql server or mysql database
0
votes
1answer
26 views

Can you restore purged MySQL binary logs?

I have two situations I need some clarification. 1) Let's say I made a backup of all the MySQL binary logs before I purged a single binary log file (the oldest one, possibly the very first one). ...
0
votes
1answer
35 views

Why does this MySQL query take 25+ seconds?

The query is: SELECT T.ID, T.A, T.T FROM table1 AS T WHERE (userID='333335') AND (category LIKE '1%') AND (T.T='You Ain\'t Much Fun' AND T.A='KEITH, TOBY') GROUP BY T.A, T.T I have a BTREE index ...
-2
votes
0answers
28 views

Aggregate function by date

I have a table of highscores achieved at tournaments. I select on highscores for each tournament using ID. (Each player can score more than one highscore in a tournament.) The query I have: SELECT m....
-4
votes
0answers
21 views

Make a MySQL DB modifiable on different computers in same time - HELP REQUEST [on hold]

I need your help. I created a Java app in netbeans that registers some data in a MySQL db. The program works fine and correctly, but the problem is: If I put the app on another computer, and i run ...
0
votes
0answers
15 views

syntax error in mysql join problem [on hold]

my frist query is working fine select w.id, p.age, w.coins_needed, w.power from Wands as w join Wands_Property as p on (w.code = p.code) where p.is_evil = 0 and w.coins_needed = (select ...
0
votes
0answers
8 views

innodb file per table

We are running with shared tablespace on MySQL database server in our environment. We are looking to enable file per table on database. We want to know What would be ideal plan to enable file per ...
0
votes
0answers
17 views

How to show dynamic data row to data column in mysql or within php?

I have problem with query mysql. So I have database table and I've tried with my query like this SELECT GROUP_CONCAT( 'MAX(IF(day(tanggal) = ', day(tanggal), ', presensi, NULL)) ...
0
votes
0answers
18 views

How can I resolve the `package mysql-server is not installed` issue?

I have just upgraded MySQL from version 5.5.55 to version 5.6, via SSH. I have managed to successfully start the MySQL, however I am experiencing the 500 Internal Server Error when trying to access ...
0
votes
1answer
22 views

Big performance difference between two Mysql db with similar workload [on hold]

I'm puzzled by the behaviour of two Mysql db configured in the same way (I compared global variables and variables). They are use roughly by the same number of customers, or at least, I don't ...
0
votes
0answers
11 views

Mysql 8.0.16 crash due to informatiin_schema.Keywords table

How to resolve issue of mysql 8.0.16 crash?mysql 8.0.16 crash many times and shown server logs due to invalid argument.and in error log query is select WORD from information_schema.KEYWORDS. how to ...
2
votes
1answer
31 views

How to clear the InnoDB redo log before starting mysqld for crash recovery

System: Windows Server 2012 R2, MariaDB 10.2.8 We are currently trying to recover from a quasi-crash (it was a normal shutdown, but mysqld won't start up again) and are having problems with the redo ...
1
vote
2answers
22 views

MySQL - Can we use the same temporary table name in different stored procedure?

In our database, we generate statistics by periodically calling a large number of stored procedures in the event. Considering there are two SP auto_update_daily_users auto_update_daily_orders Is ...
1
vote
3answers
32 views

SQL join multiple “id” columns from a single row to another table [on hold]

Apologies if this has been asked but I've tried searching and haven't come across anything similar. I have a two tables in a MySQL database. Athletes and Teams The athletes table contains the ...
1
vote
1answer
22 views

Unable to downgrade MySQL from version 8 to 5.6 nor start MySQL (MySQL Error Log Included)

I run a Plesk VPS (OS: Centos 6/Linux (Final)), whereby I recently tried to upgrade the MySQL from version 5.5.55 to version 5.6. Being relatively new to this area, I strictly followed this Plesk ...
0
votes
2answers
35 views

How to trim a string by finding multiple words

I want to trim a string if it contains certain words or characters. This is just part of a big stored procedure. I want if v_selection_name contains any of those characters .eg: ' (RES)' or ' (R)' ...
0
votes
1answer
23 views

I need to run a procedure that goes through all schemas tables in MySQL server [on hold]

I am new in creating procedures ,Actually I need to run a procedure that will run on all schemas tables in my server when am calling the procedure it gives me this error ERROR 1046 (3D000): No ...