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.

Filter by
Sorted by
Tagged with
0
votes
0answers
12 views

MySQL capability become insufficient

My ex-colleague deployed a MySQL database on Alibaba Cloud Elastic compute service (ECS) instance. I have taken over for 2 months. Due to traffic increase, the service capability (concurrent ...
0
votes
0answers
12 views

Fastest way to make full text searches through 2 columns?

I actually have 200 GB of data indexed on MongoDB, spread over 2 columns, each containing one string. The user search for 'cowboy', if this string is found anywhere, it returns the content. i.e: ...
0
votes
0answers
7 views

MySQL Plugin failed to install due to “undefined symbol”

For several years now I have used the MariaDB Audit Plugin on my MySQL databases. I am now looking to start Upgrading my databases to MySQL 8, and so have just tried installing it onto MySQL 8.0.19, ...
0
votes
0answers
11 views

MySQL 8 Master - MySQL 5.7 Slave Replication

I am trying to replicate a MySQL 8 server (master) to MySQL 5.7 (slave) instance. I know it is not officially supported, but the thing is we need to use a service that has only supports MySQL 5.7. I ...
0
votes
0answers
12 views

Specifying MySQL user hosts with IPv6 addresses

Inspired after making the move to IPv6 on my MySQL server, and intrigued by the lack of documentation regarding 'user'@'[IPv6]' nomenclature I proceeded to discover how to limit my users based on ...
0
votes
1answer
11 views

What's the optimal way to query data using another table column as a between filter

sorry for the ambiguous question title. I don't know what's it called. The thing is let's say I've a order table storing uid , order_id and ip looks like this | uid | order_id | ip | 10000 | ...
0
votes
0answers
15 views

Storing type in each table or give item a type

I need to design schema for a project that involves two things. 1. providing services ( realted to vechicles) 2. Also sells insurance and spare parts Below is the schema that I have designed so far. ...
0
votes
1answer
16 views

Select query on information_schema is too slow

SELECT * FROM information_schema.TABLE_CONSTRAINTS WHERE CONSTRAINT_SCHEMA = DATABASE() AND CONSTRAINT_NAME = 'FK_FOLDER_FILTER_CONTRACT_CATEGORY_CONTRACT_CATEGORY' AND CONSTRAINT_TYPE = '...
0
votes
1answer
22 views

SQL 2 Columns of Different Types in Same Table - Working With 'Uknown' Amount

I'm very new to SQL and I've been searching for an answer for a while now, can't seem to find it. I have 1 table of CustOrders which has 3 columns. Table CustOrders Name (varchar) Number_Orders (...
0
votes
1answer
24 views

How to save working hours of multiple stores and then query it without duplication?

I'm want to create database with stores. Each store has their own working hours in a week. Then I want to query the store and sort them by nearest store which is currently open. So far I create this ...
0
votes
1answer
17 views

mysql, select and sorting by group key + with first row that has that key in another column

I'm trying to make a select that outputs everything, but is sorted by a group column with a "header column" that matches said group column on another column. I have no idea how this type of query ...
-4
votes
0answers
22 views

Database Management Question [closed]

Can you please solve this ? Table Names: Restaurant (rid, rmane, rcity, phone, seat-capacity) Dishes (did, dname, dtype) Customer (cid, cname, ccity) Serves (rid, did) Write a SQL ...
1
vote
1answer
19 views

Identifying blocking queries on MySQL 5.5

For an application using MySQL 5.5.60 under Debian, lately we experience transactions with database writes, which do not succeed within the 50s time limit of the innodb_lock_wait_timeout setting. I ...
0
votes
0answers
13 views

Unusually low insert rate on MySQL Slaves

I have 4 MySQL nodes replicating like this: M1 - S1 | M2 - S2 Only the M1 master is writing, the hardware is similar (the slaves are a bit beefier), they all run Percona 5.7. The trouble is that ...
0
votes
0answers
16 views

select column values as fields from multiple tables in MySQL with customized conditions

I am not more familiar with MySQL.i want to select some column fields as values from another table by applying some customized conditions I tried out with some queries but not getting the proper ...
0
votes
1answer
23 views

Delete all data for a Single Vendor in the Dabatase

I have a Vendor table with vendor_Id as the PK and 10 other tables which have reference to Vendor.vendor_Id. Now, if Vendor chooses to close their account, we need to delete all the data in the other ...
-4
votes
0answers
10 views

no connection established mysql workbench Mac [closed]

Lost connection to MySQL server at 'reading initial communication packet', system error: 0
-1
votes
1answer
19 views

Comparing the result of a subquery with LIKE

I need to find all the movies that have all of its actors' names to start with an A and have a 60.000.000 or more of gross. I have this but is not working SELECT title, imdb_score FROM Movie AS m ...
0
votes
0answers
27 views

MariaDB server is consuming all the CPU

I am working with a system that pulls data using API requests, stores it and pushes data using some other API requests. We are using a table to store unprocessed data that is being fetched through ...
1
vote
0answers
25 views

SQL Developer - can't create DB objects in mysql

I have SQL Developer Version 19.2.1.247 on Debian Linux, with Java version "13.0.2" 2020-01-14 (I know, not supported, but it seems to work), and JDBC installed from mysql-connector-java_8.0.19-...
-1
votes
0answers
24 views

MySQL INNER JOIN is slower than LFET JOIN

I have an impression on that Inner join is always faster but in my data set it seems to be very slow compared to LEFT JOIN. Can you please help me to understand why it's so if I use the below query. ...
0
votes
1answer
14 views

It's possible to skip duplicate keys (i.e. MERGE) in a BULK INSERT in MySQL?

I have a INSERT...SELECT statement that I want to run several times from TABLE1 to TABLE2 as the data in TABLE1 will change overtime. I don't expect the past data from select to change overtime, the ...
0
votes
1answer
11 views

Percona restore databases error

I am trying to restore Percona backup but getting the below error. [/]# xtrabackup --copy-back --target-dir=/backup-billingsystem xtrabackup: recognized server arguments: --innodb_file_per_table=1 --...
0
votes
0answers
22 views

thread limit in mysql 5.7 [closed]

We are running with Server version: 5.7.29 MySQL Community Server (GPL) and in same our application needs to select and update data from single table which has 4000 counts. Is there any ...
0
votes
0answers
27 views

converting latin to utf8mb4 causes questionmarks

The original format of the data is unknown The new table is in utf8mb4_general_ci If I do CONVERT(BINARY CONVERT(column USING latin1) USING UTF8) as mentioned here - it fixes all text, but converts ...
1
vote
1answer
43 views

Tuning MySQL on GROUP REPLICATION

I'm close to switch from old version of MySQL 5.6 ( master / slave configuration ) to MySQL 8 ( 3 nodes on multi-master configuration ) in GROUP REPLICATION. The tables are 99% InnoDB. The webfarm ...
1
vote
3answers
27 views

Deleting rows from multiples tables at once

Hello world! I have a doubt on this piece of code that I just wrote, I have a problem withe the syntax, there first query excecute its purpose, the problem its in the second query wich does not. if(...
-1
votes
1answer
36 views

Increment a value if another value exists, if not create a new row [closed]

Below is what my table looks like, how would I go about increasing the "record" value of a row if the "first" and "last" match a given value and if there is no matching "first" and "last" value then ...
0
votes
1answer
22 views

I have account_id on all of the tables in the database that belong to an account. Could this be why our database is using a lot of memory?

The application makes heavy use of data. It is a Real Estate Lead Management System each lead could make our users thousands of dollars, and accidentally returning other people's leads would be a sure-...
1
vote
1answer
23 views

mysql 8 high availability with drbd, corosync and pacemaker

I would like to implement High Availability for MySQL8, is this version supported by drbd, heartbeat, corosync, pacemaker? I've looked around online to that regard but there isn't much information ...
-1
votes
0answers
9 views

MySQL tables_priv corrupted

I have a mysql database and i need to create an user for the login. This user need the privileges to only select on only one table where the login info are. on phpmyadmin when i use the gui i receive ...
0
votes
0answers
21 views

How to trigger Mysql parallel query function to start?

Please help,how to trigger Mysql parallel query function to start? MySQL version is 8.0.19. mysql> show variables like 'innodb_parallel_read_threads'; +------------------------------+-------+ | ...
0
votes
1answer
38 views

Approaching database design

I have 1000 sensors, and I need to hoard the value of each sensor at every second, for a month. This is just theory, the most realistically achievable unit of time will be determined when I start ...
0
votes
1answer
55 views

How is it possible that some people are able to juggle several different SQL variations in their heads at once? [closed]

I have spent the last 20 years dealing with first MySQL (for about 6 years), then 14 years old PostgreSQL. In 2006, I naively thought that it was going to be painless, "because both use SQL"... man, I ...
0
votes
1answer
28 views

How do I know if an index has been successfully created in MySQL?

I use Capistrano to deploy a Rails app. Capistrano handles running database migrations via essentially a shell script that runs from my local computer. About 24 hours ago I started a migration that ...
0
votes
1answer
18 views

Column Terminology

I'm a MySQL student here trying to learn the proper way to call things in the SQL language. CREATE TABLE tbl_name ( id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, width SMALLINT ...
1
vote
0answers
9 views

MySQL 8.0.18 - ST_Area returns invalid value

I have a problem with ST_Area function in MySQL 8.0.16. The result of the procedure call returns a different result than any library used to calculate the area of a polygon. Example: I have a ...
0
votes
1answer
15 views

After change to 13306, Galera logs error: Slave I/O: error connecting to master 'repuser@<master node ID>:3306'

Working with Galera 25.3.23 on RHEL 7.3 Galera works good before. After changing the MySQL server port from 3306 to 13306, one of the node report error, after restart: Slave I/O: error connecting to ...
0
votes
1answer
22 views

MySQL: Should I keep a running total column in one table instead of summing a second table?

I have 3 tables named users, achievements, and userAchievements. The users table contains information like username, password, e-mail, etc... and the achievements table contains a list of possible ...
0
votes
0answers
35 views

How to reconcile databases after split-brain? [closed]

We run three on-premise datacentres with several databases. The network between the three datacentres is not constant and goes down several times a year for lengthy periods of time, but the ...
1
vote
3answers
28 views

mysqldump interrupts always with same file size

mysqldump -h127.0.0.1 -u user -p'password' -t db mail --where 'status=3' > /var/www/project/storage/web/dump2.sql I try it on ubuntu 18.04, mysql 5.7.29. And the file is always 141.8MB. Data just ...
0
votes
0answers
28 views

Cannot truncate a table referenced in a foreign key constraint even with no child data?

With a ON DELETE RESTRICT restriction, I understand that you can't delete a row in the parent table if a reference exist in the child table, but is it expected behavior that even if the child table is ...
0
votes
1answer
13 views

AWS RDS : Setting Create and other grants only for single Database is not working

We are using AWS RDS Mysql, and we are trying to give SELECT access to all databases i.e RO only, but for a single database, we want to give all permissions i.e CRUD. It's not working out ...
0
votes
4answers
41 views

It's possible to order alphabetically all columns in tables of a mysql db?

It's a large db and it's really messy, I would like to order all columns in all tables, without resorting to manual drag and drop... EDIT I know the order doesn't matter. It's a design think. It will ...
0
votes
0answers
11 views

MYSQL :- finding the rank of the player based on score and x? [closed]

column name :-id score x ,return the rank based on the id compare score and value of x
0
votes
1answer
41 views

Select rows from table having more than two specific values

I have a table having details such that Userpin product Time1 contains 4556 23 7:30 A 4557 23 7:30 B 4558 23 7:30 C ...
0
votes
1answer
31 views

Table contains 4 indexes inside InnoDB, which is different from the number of indexes 3 defined in MySQL

The database is an old version of MariaDB, the equivalent of MySQL 5.5. The table contains those columns id auth old_auth device_id_fk This error is showing Table contains 4 indexes inside InnoDB, ...
0
votes
0answers
10 views

Is it possible for a weak entity to be related to another weak entity

In this case, can payment and Fees be related despite being weak entities.
0
votes
1answer
22 views

mysql complex procedure debugging

I have below stored procedure which i would like to debug but its not throwing any errors into error table.This stored procedure is run by event manager on every minute. Here is the process : I ...
0
votes
1answer
17 views

Detect with Left Join duplicates entries and return the first value, also return sum of duplicates entries

¡Hi!, i have a case with two tables than can have a lot of matches entries and the time of fetching increases a lot. This tables can be Table A: Employees ------------------------- | Name | ID | ...

1 2 3 4 5 367