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
15 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
174 views

changes on multimaster replication and activate GTID

I've been reading a lot about the changes I planned to do to our DB landscape but I need some confirmation or recommendation to be sure the work I'll do goes without causing a disaster. Here is what ...
1
vote
1answer
16 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: ...
1
vote
2answers
282 views

Partial copy of huge database

I have started working in a project which has a big database, around 300GB. For security reasons I can not access database from my local web app. So I need to copy last 100,000 from each table. To ...
0
votes
1answer
9 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
0answers
4 views

Need some help, mysql won't start and I have no idea how to recover

I had a scheduled cron job that would delete 6 million records. At some point during that cron job the server went down. Now mysql won't restart. I get this error in the log file /var/log/mysql/error....
0
votes
1answer
27 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
2answers
163 views

MySQL get values from the other table by using inner joins

In mysql I have 3 tables called as ps_product_lang ps_order_detail and ps_image here are the tables sample data === ps_product_lang === CREATE TABLE IF NOT EXISTS `ps_product_lang` ( `...
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 ...
0
votes
0answers
4 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 ...
0
votes
1answer
16 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 ...
-1
votes
0answers
13 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
1answer
27 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
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
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
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
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 ...
1
vote
2answers
969 views

MySQL / DRBD / Pacemaker on EC2

I am trying to setup DRBD/MySQL/Corosync/Pacemaker on AWS EC2 instances, though I am not sure if it will work since how to integrate VIP is a problem for me. Anyone have any thoughts on what to do ?
-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
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 ...
3
votes
4answers
8k views

Automatically purging binary logs

I'm currently trying to create an scheduled event on MySQL to purge binary logs every week. The create event statement is as follows: CREATE EVENT purgebinlogs ON SCHEDULE EVERY 1 WEEK STARTS CONCAT(...
0
votes
1answer
224 views

mysql diff and add diff records into new database

I have two database on two different hosts: host A has database A and table A and host B has database B and table B and now "host A/database A/table A" table A contains records with primary key 1 ...
0
votes
1answer
254 views

Fragmentation in InnoDB tables: dump all data together reduced size, but fragmentation got worse

I have a question about tables and fragmentation in MySQL My starting point is this question: How find and fix fragmented MySQL tables I have a table with 1 million of rows and 128MB of space. This ...
30
votes
6answers
58k views

Disable MySQL binary logging with log_bin variable

Default MySQL config file /etc/mysql/my.cnf installed by some debian package using APT often set log_bin variable, so binlog are enabled: log_bin = /var/log/mysql/mysql-bin.log When I want to ...
0
votes
1answer
190 views

Can't determine cause of MySQL Deadlock

It appears to me that (2) TRANSACTION is attempting to acquire a lock it already holds. Here is the information from "show engine innodb status": ------------------------ LATEST DETECTED DEADLOCK ---...
1
vote
1answer
202 views

Replication not updating all tables

Master db MySQL db Server 2012 Slave db MySQL Win7 XAMPP DB size 500MB Table count 42 I have setup the replication successfully however it stopped last week and my slave was showing the error ...
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 ...
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
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
2answers
71 views
+200

Why are the system tables in MySQL and MariaDB utf8 and not utf8mb4?

I know the general advice is never to use utf8, but instead to use utf8mb4. If that's the advice, then why do the system tables themselves use utf8? You can see this in the MariaDB test suite, and ...
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 ...
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
1answer
164 views

MySQL multiple table joins and multiple data levels - best practice

I have multiple tables i wish to join up to produce one big query with specific columns summed. I was having a problem getting the correct summed columns in the final query (i believe this was ...
5
votes
1answer
420 views

Mysqlfailover command - No slave is listed in health status

I have successfully created replication using GTID_MODE. It works perfectly. Now I need to setup automatic failover feature in it. I have run the following command. mysqlfailover --master=root:abc@10....
0
votes
1answer
94 views

Set InnoDB as default engine and set a default collation

What should I do to set InnoDB as the default database engine? And is possible to set a default collation when I create a schema and tables (e.g. utf8mb4)? I don't want to have to choose these ...
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
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
219 views

MYSQL Patching -Roll back

MYSQL patching. I took all database backups before starting patching. If we patch mysql 5.6.30 to 5.6.32 and if we want to rollback to 5.6.30 due to some issues. Can we do that? if so should we have ...
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, ...
7
votes
1answer
41k views

Enabling LOAD DATA LOCAL INFILE in mysql

LOAD DATA LOCAL INFILE is not enabled by default. Normally, it should be enabled by placing local-infile=1 in my.cnf. But it does not work for all installations. In my experience, it worked for Debian ...
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 ...
0
votes
1answer
153 views

Index Strategy for MySQL Query that matches on one column and between two others

I have a table that contains a set of measurements for a continuous stream of processes. Although each process is individual, they are categorised into groups. The events have a start and end ...
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
2answers
220 views

Local and cloud database sync missing data only

We have set up an IOT device which records a lot of data. This data always makes it into the local gateway mysql database, and if there is an internet connection then it also goes into the cloud ...
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 ...
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 ...
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 ...