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

Select Changed Rows in MySQL and Postgres without Timestamp column

I have the following scenario: I have 2 source systems, one running Postgres, the other running MySQL. An ETL process (using Azure DataFactory and Azure Integration runtimes) runs the Extract for a ...
0
votes
2answers
12 views

How can I select just the 'Create Table' column of SHOW CREATE TABLE?

MySQL SHOW CREATE TABLE tablename has two columns of output, 'Table' and 'Create Table'. How can I select just the 'Create Table' data? I've tried mysql> SELECT 'Create Table' FROM (SHOW CREATE ...
1
vote
0answers
12 views

Restore Stored Procedure from Google Cloud MYSQL?

I accidentally overwrote a MYSQL stored procedure through the Workbench client connected to a GCP MYSQL instance. I tried restoring a previous backup to recover it, but that only restored the tables - ...
0
votes
1answer
43 views

Queries of MySQL from Java are executed serially from multiple threads

I have REST API developed in spring + java and hosted on Tomcat server. I have a MySQL database where i store all my data. API makes just simple select from db as descripted below. I failed ...
4
votes
3answers
83 views

(Deadlock) Select Query Causes Insert Query to be Blocking in Transaction

Database: MySQL I have a running app with concurrency transactions in it. I noticed that there are so many errors occur related to deadlock. The error says SQLSTATE[40001]: Serialization failure: 1213 ...
0
votes
0answers
22 views

MySQL result ORDER BY reverse order?

My query looks like .. SELECT uid FROM user WHERE plz IN(24718,32326,32329,32328) ORDER BY FIELD(plz,24718,32326,32329,32328), uid ...
0
votes
0answers
15 views

How to use distinct and non distanct coloumn type in same mysql query

My brain has stuck in this query, I want to know that how to get distinct and non distnct query result in one query. Here is my query: SELECT count(PTPK) as count,PTPK, Name ,`SwitchDate` ,Half , ...
0
votes
0answers
5 views

how to batch update remark records atomatic in multi instance applicatoin in MySQL 5.7

I want to update a remark with multi records in MySQL 5.7, in multi instance, I want the workflow like this: update sync field from 0(ready for sync) to 2(syncthing),maybe multi rows(maybe multi ...
1
vote
0answers
21 views

Is it a good practice to store auth session in the database?

I created a login system that in addition to being used on a website, will also be used in mobile applications. As on cell phones I want to keep the user logged in until he chooses to log out, I did ...
-1
votes
1answer
47 views

MYSQL Error: Incorrect usage of UNION and ORDER BY?

Am trying to randomize my first query but ran into an error "Incorrect usage of UNION and ORDER BY" Kindly find below my queries: SELECT * FROM( SELECT deal_page.id, ...
-1
votes
2answers
27 views

How to do left join , same table with different conditions per column

Hi i need information about how to merge both sentences Sentence 1 select idlote from polizamovtos group by idlote having sum(cargo)-sum(abono) > 0 Sentence 2 SELECT SUM(cargo) as saldo FROM ...
2
votes
2answers
13 views

In mysql master-slave Replication which server requests the other?

I want to setup mysql replication between two servers one of them is my localhost and the other is online server. I have all availability to make any one of them the master. But according to that my ...
0
votes
1answer
27 views

How to benchmark Switchable Optimizations in MySQL

I want to benchmark the performance of Switchable Optimizations in MySQL, i.e., comparing the query latency and the result of EXPLAIN with/without using these optimization switches. I have already ...
-1
votes
1answer
14 views

Greater than prompt after invalid command problem [closed]

I am using the MariaDB shell and have an issue with a greater than prompt after I enter an invalid command. At the moment all I can do is to quit the shell and start again which is very time consuming....
0
votes
0answers
37 views

how to create a MySQL View to select the smallest and largest Records?

i have a table contains items units details (st_items_units) like :- ------------------------------------------------------- id ------ stitems_ID ------unit------ stitemsu_UnitNum --- unit price -----...
2
votes
1answer
45 views

MySQL DB design for very long growing row size

My data has the property to grow in the row direction and the column direction at the same time. The size will grow in both directions without limit while I do more research. The number of columns ...
4
votes
1answer
944 views

'Access denied; you need (at least one of) the PROCESS privilege(s) for this operation' when trying to dump tablespaces

I am trying to backup mysql using the command mysqldump -u root -p database_name > backup.sql but it is throwing an error: 'Access denied; you need (at least one of) the PROCESS privilege(s) for ...
0
votes
0answers
23 views

insert or update with summary subquery

i have a problem with follwing query INSERT IGNORE INTO cartellino_anno (iddipendente, mese, ordinarie,effettive,assenze,straordinari,ritardo,anticipo,richiestaassenza,richiestastraordinario,...
0
votes
0answers
13 views

Which tool is best for MySQL Profiling?

I am using Neor Profiler to inspect auto-generated queries for MySQL server. This tool is good but not that flexible. Often it freezes when my application hits with more queries. So I am looking for ...
0
votes
2answers
24 views

MySQL Call Stored Procedure along with an UPDATE statement

I am pretty much convinced that this is not possible but again I wanted to see if there is a possibility. I have an UPDATE statement and I want to call a SP for each row updated. Is it possible ...
0
votes
1answer
17 views

Conditional multi table insert

My application parses a web resource for data constantly. The data is then used to build a domain object. The domain object is represented in my database across several tables. The domain object is ...
0
votes
1answer
22 views

Mysql 8 - Count(*) - performance issue

I have this table with 2 million records CREATE TABLE `my_table` ( `ID` int(11) NOT NULL AUTO_INCREMENT, `ID_USER` int(11) DEFAULT NULL, `BODY` mediumtext CHARACTER SET utf8 COLLATE ...
2
votes
2answers
15 views

#1194 - table 'user' is marked as crashed and should be repaired

I have problems getting into Mysql on phpmyadmin (mysqli_real_connect()(HY000/1130)Host 'localhost'is not allowed to connect to this MariaDB server ) I have found out its a crashed file in mysql ...
1
vote
0answers
27 views

2020-07-29T07:44:29.817503Z 108716 [Note] Aborted connection 108716 to db:

We are getting continuously logs in mysql error logs 2020-07-29T07:46:56.534666Z 111536 [Note] Aborted connection 111536 to db: '' user: '' host: '' (Got an error reading communication packets) we ...
0
votes
2answers
54 views

LEFT JOIN not working as intended

I'm using mysql 8.0.21. I'm trying to join two tables such that I can find the index name of foreign keys. This is my query: SELECT KEY_COLUMN_USAGE.CONSTRAINT_SCHEMA, KEY_COLUMN_USAGE....
0
votes
1answer
8 views

How to Fetch A Record If Shipping And billing Address are Different

Show New Orders with Address If shipping and billing are different. I am stuck with this query. Can someone guide me a bit. Thanks
0
votes
1answer
20 views

count distinct column without null value in MySQL 5.7

how to distinct not contain null in MySQL, if I have a dataset like this: a b 1 null 2 1 I want to using sql like this: select count(distinct b) fromt table the expect result may output 2, but I ...
-1
votes
1answer
31 views

MYSQL EAV JOIN QUERY OPTIMISATION [closed]

I am creating a e-commerce website, which consist of product and its variants. So I decided to go with EAV for attributes mapping as I may have indefinite data like (size, color, material). But Now, I ...
0
votes
0answers
9 views

Disable auto-rehash when connecting to mysql via jdbc?

Is there a way to specify equivalent of no-auto-rehash when connecting to mysql via jdbc? I am trying to connect to a large db and the initialization is slow. If I connect without jdbc and specify -A,...
0
votes
1answer
27 views

Difference in MYSQL Master Slave DISK SPACE

I am using percona_mysql_5.6, both master and slave are in sync as terms of replication and table wise records are also same. However there is a difference is the disk space consumed. The slave is ...
0
votes
0answers
12 views

MySQL: create ER diagram, but in text only

With MySQL Workbench it is possible to create a graphical ER diagram; however, this can become very messy when there are many tables and relations, so it might be good to have something like one or ...
0
votes
0answers
8 views

is it possible to except some expect word when using distinct in MySQL 5.7

I am using this SQL to caculate transin user count : select sum(transin_num) as transinNum, case when count(distinct transin_user_num) -1 < 0 then 0 else count(distinct ...
0
votes
2answers
27 views

Mysql 8 - % CPU Wait

Is it normal in Mysql 8 to have a high percentage of CPU wait? (see image)
0
votes
0answers
17 views

High-performance query of two tables with count from second table?

I am searching for a high-performance query using two tables to output all locations (name) from country (adm0 = 'DE') in the geodb_locations table, sorted in descending order by the number of users ...
1
vote
0answers
29 views

Unexpected result by MySQL Optimizer for identical queries?

Following query is working like expected and uses index Query takes 0,0481 sec SELECT geodb_locations.name, geodb_locations.name_url, COUNT(user.uid) AS useranzahl FROM user LEFT ...
0
votes
1answer
10 views

Data sync between two local Mysql databases in different system in same netwrok

I am using mysql as database for my application and for the purpose of fail over and I need to sync the data from one local mysql database in system one to other local database in system 2 connected ...
3
votes
2answers
26 views

information_schema is full of NULLs after database import

I am using MySQL workbench to dump 4 databases with tables that reference each other. As a note, there are no current problems with the databases at all - everything works as expected. When I try ...
0
votes
1answer
24 views

How to determine actual memory occupied by Query Cache in MySQL?

How can we check if the MySQL server is utilizing high memory/RAM ? How much memory is occupied in total by query cache ? How much memory is fragmented ? Basically what is occupying xGB off memory by ...
0
votes
0answers
13 views

RDS - Backup Strategy MySQL [closed]

I'm looking for an approach to start my Backup strategy using RDS MySQL database. I need to implement a recovery point time strategy if something goes wrong with my database I would restore this ...
0
votes
1answer
38 views

Is the following index is good?

What is the proper indexing plan for the below query? Query 1 : select id from room_participants where roomId=1 and userId=1 and status=1 and isMuted=0 Query 2 : select group_concat(b....
0
votes
1answer
21 views

MYSQL+MyISAM+Need information regarding check table statement

The MySQL CHECK TABLE statement checks a table or tables for errors. Does the statement put an auto lock on a table before checking table for errors? Does the statement perform a READ ONLY operation? ...
1
vote
0answers
20 views

Apply function in UNIQUE constraint in MySQL 5.7

I would like to add a multi column unique constraint to my MySQL 5.7 database table like this: ALTER TABLE report_summary ADD UNIQUE ( tenant_id, app_id, date_format(FROM_UNIXTIME(...
1
vote
1answer
11 views

Create A Pivot With No SUM() in MySQL

I've seen a lot of examples using MySQL to create a PIVOT but they all use SUM() - using this DDL how can I create a PIVOT in MySQL Create Table `data` ( callDate text ,callerName text ,...
2
votes
2answers
19 views

Formatting Text Field As HH:MM:SS

I am having to Cast() a Text datatype to a TIME datatype. THe issue I'm running into is when the results display i'm loosing the hh:mm:ss format. The syntax I am using is Select SUM(CAST(salestime ...
0
votes
1answer
27 views

MySQL Many to Many with additional data in intermediary table

I need to create a Many to Many relationship and understand the need for an intermediate table to act as the link between the two. The understanding I have is the Group of ID's in the intermediate ...
0
votes
1answer
19 views

covering index mystery?

So has anyone else applied a covering index in MySQL and gotten improved performance but when you run explain, it shows using where, using temporary under the extra column? Any thoughts why this is ...
-1
votes
3answers
21 views

Set The Order Query Results Display

I need to display my emailType in DESC order. Now it is a varchar field so I need it to be reverse alphabet order Z - A. When I run my query it is displayed in ASC order, and since I am using a ...
0
votes
1answer
30 views

Why are CASE-expressions in the list documenting operator precedence?

In the list here: https://mariadb.com/kb/en/operator-precedence/ case-expressions are put at the same precedence level as BETWEEN, between the NOT operator and the comparison operators. However, case-...
0
votes
0answers
16 views

Once a foreign key is set to Null after deleting the related parent record, can you then amend the foreign key to a new value?

My question is say you ran a daycare and had a table for the children and another for their related school (with the FK in the child table), if the school of some of the children, for some reason, ...
5
votes
2answers
3k views

Why is this MySQL query taking so long?

I'm looking to do a relatively simply filter from one table called prices. prices is a very large table (~2G records). Sample data, query and indexes included below. I realize the table being ...