Questions tagged [mysql-5.7]

MySQL version 5.7 - please also tag with mysql for search purposes.

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

Cannot find record in table after upgrading mysql from 5.6 to 5.7

We recently upgraded our database from mysql 5.6 to 5.7 and started seeing a new error that happens to occur intermittently. The error happens while trying to access results of a query or while ...
0
votes
0answers
16 views

INSERT INTO …ON DUPLICATE KEY UPDATE doesn't work [closed]

I try to using the ON DUPLICATE KEY UPDATE for changer the table's value, but not success, It return CLIENT->OKkkkk and TYPE_CLIENT->null. thank for your help INSERT INTO `table`(`CLIENT`) ...
0
votes
1answer
16 views

calling mysql procedure return empty result

when i call procedure it gives me empty result call hussian('prstate','table_1','column1,column2,column3',1) it run but gives empty result, please help me... DELIMITER // CREATE PROCEDURE ...
0
votes
1answer
11 views

MySql Replication Failure on primary secondary

We have experienced replication failure between primary - secondary databases. I have tried various scenarios to get replication to work again, which included deleting secondary database and ...
-2
votes
0answers
15 views

I ran MySQLTuner and wondered if anyone would go over the results with me [closed]

The mysqltuner report: >> MySQLTuner 1.7.19 - Major Hayden <major@mhtx.net> >> Bug reports, feature requests, and downloads at http://mysqltuner.com/ >> Run with '--help'...
0
votes
2answers
26 views

MySql replication Failure

I have a mysql (Ver 14.14 Distrib 5.7.29, for Linux (x86_64)) primary -> secondary replication setup. Initially the replication was working successfully until we had to restart the primary database. ...
0
votes
1answer
12 views

Upgrade all MySQL columns, tables, and databases from utf8mb3 to utf8mb4

I have thousands of columns across hundreds of tables in about a hundred databases inside a MySQL instance that need to be upgraded from utf8mb3 to utf8mb4. Is there a way to generate ALTER ...
3
votes
1answer
36 views

Why mysql throws “invalid default value for field” only when two or more timestamp fields exist in the create statement?

In mysql 5.7.28 I create a table like this: create table t1 ( id int not null, d1 timestamp) engine=innodb; which works fine and creates d1 as non-null with default value current_timetamp,on update ...
0
votes
0answers
18 views

Alter Table operations blocks replication in MySQL

We are using Amazon Web Service's MySQL 5.7.26 with 4 read-only replicas. After some Alter Table operations we have found that the replication stops working. The symtoms are great replica lag that is ...
0
votes
1answer
36 views

Why doesn't MySQL use indexes for subqueries?

This query takes forever to run (30+m - infinity). select date, sc, ( select count(fingerprint_id) from stats where hit_date >= t.date and hit_date ...
0
votes
1answer
62 views

MySQL - [Note] Got an error reading communication packets

I have deployed MySQL on OpenShift (kubernetes) with GlusterFS as persistent storage and i am getting following error after every 2 seconds in MySQL Container logs 2020-01-17T18:00:04.787586Z 1541 [...
0
votes
1answer
47 views

MySQL: I need to return rows according to certain conditions

I have a 3 tables: work, recording, release 1 work can have multiple recordings and 1 recording appears in only 1 release and 1 release can be in m recordings. TABLE: work +---------+-----------+ | ...
1
vote
2answers
37 views

Filtering down specific criteria for all rows and return only one row for each column with the same value

I have house_leases and house_lease_terms (see table schemas below). A house_lease can have multiple house_lease_terms however there can only be one "active" term at a time. Table Definitions: ...
0
votes
1answer
40 views

Best strategy to correctly index complex join query

This is the query running on MySQL 5.7 with INNODB: SELECT SQL_CALC_FOUND_ROWS calendar.id_calendar AS calendar_id, calendar.event_datetime AS calendar_event_datetime FROM users INNER ...
-2
votes
1answer
19 views

Can't manage to work with comparing days

In a table with persons and their birthdays, the ones whose birthday is in less than 7 days need to be displayed. I found some answers on the Internet but they all are at least 5 years ago with ...
0
votes
1answer
21 views

MySQL DB Causing Spike-up on Windows Server CPU

This is a Magento application connected to MySQL Db. When I ran show engine innodb status\GI get a list of TRANSACTIONSand it looks like some of them are pending and others are in a DEADLOCKand it ...
0
votes
0answers
121 views

I can't start mysqld.service -> status : Data Dictionary upgrade from MySQL 5.7 in progress in centos 7

I've installed mysql 8 on centos 7 and when i want to start the mysqld service , it says : Job for mysqld.service failed because the control process exited with error code. See "systemctl status ...
0
votes
1answer
35 views

Unable to Join phpMyAdmin Using MySQL Defined User

I can login to mysql via the command line just fine, but when I try to log on to phpMyAdmin with the same password that worked in the cli, it won't allow me to connect and gives me: ...
0
votes
1answer
24 views

Everything is ok and x is not disappearing

Since some time ago, I've started with MySQL so I don't know Workbench very well. That little red x next to the number of the line that indicates an error does not disappear, even though everything ...
2
votes
1answer
34 views

Primary Key At the End of Foreign Key index in MySQL is in order for sure?

I am aware that any Index in MySQL Innodb contains primary key value in the end of it. If there was a table as follows. create table BaseTbl ( base_id int not null primary key auto_increment, ...
0
votes
0answers
16 views

Very strange Mysql query optimization

This is the initial query: SELECT DISTINCT clienti.id_cliente AS cliente_id , clienti.cognome AS cognome,clienti.nome AS nome FROM clienti WHERE clienti.id_agenzia = 5782 AND ...
0
votes
0answers
19 views

create backup on mysql master-slave

I have MySQL version 5.7 with a master-slave configuration. but I'm confused to make a backup cron. Is there a way to do a backup while remaining safe to master-slave? I heard that using mysqldump is ...
2
votes
3answers
164 views

Something in MySQL is causing progressively high load which consumes almost all memory

I have this server that has only one site with one database on it. And yet, I cannot tweak it to make the load go back down to normal. If I restart mysql service, memory and load go down to normal, ...
0
votes
1answer
28 views

Gereralise the query for all user_id

I have a mysql query select sum(duration),app_name,app_id,user_id from timesheet where user_id=164 group by app_id order by sum(duration) desc limit 5; The result is sum(duration), app_name, ...
0
votes
2answers
21 views

If I need to restrict access to certain databases?

So I've just created a MySQL server. I also created some users only having specific access to certain databases, but do I need to restrict access to default databases to the users I've created? If so, ...
0
votes
1answer
12 views

Make a list of stations by checking next_station column value

How to list all station between selected two stations based on next_station_id For instance, if the user selects origin A station and destination to J than I want to list all stations in ORDER that ...
0
votes
0answers
22 views

Is there a way to create tablespace in MySQL RDS

I'm using AWS RDS MySQL instance and using it. Now I want to create table space and assign table space to tables. Is it possible in MySQL RDS
1
vote
1answer
12 views

DB locks being created on temp tables. Can percona ignore temp tables for replication

I have a stored procedure that I am using that creates a temp table. Recently I've noticed the logs are filling up with locks. I think the temp table is trying to be replicated but I am unsure. *** ...
0
votes
1answer
31 views

Find employees latest activity is slow when adding ORDER BY

I am working on a legacy system in Laravel and I am trying to pull the latest action of some specific types of actions an employee has done. Performance is good when I don't add ORDER BY. When adding ...
0
votes
0answers
11 views

Upgrading MySQL takes forever

I am upgrading a MySQL database from 5.6 to 5.7; it contains a few tables that are obscenely big, >500m rows in one case. I started the mysql_upgrade yesterday, and it has been chewing its way through ...
2
votes
0answers
66 views

MySQL performance degradation with heavy inserts

I have an application that does heavy inserts into 5.7 MySQL database. The performance is really good for normal workloads, however, when there's an intense activity in the database the queries (...
0
votes
2answers
120 views

High RAM memory usage

I'm currently testing a new MySQL server, but I'm new in this so I'm not sure about my parameters, because the RAM is always above 90% of usage. The server will be setup for a CRM environment of 100 ...
0
votes
0answers
15 views

Re-Generate cumalative totals for double entry accounting records stored in a table

I have a customer_transaction table as below. CREATE TABLE `customer_transactions` ( `id` int(11) NOT NULL, `customer_id` int(11) NOT NULL, `transaction_type` varchar(25) NOT NULL, `...
-1
votes
2answers
39 views

Compare grouped totals using two tables

I have two tables: charges id booking_no room_id guest_id product_id unit_price quantity net_total tax_total gross_total payments id booking_no room_id guest_id payment_method payment_amount I ...
-1
votes
1answer
31 views

Alternative for creating Database and inside Database having schemes like Oracle in MySQL

In oracle we have database inside database we have schemes and inside schemas we have tables. For Example consider the following scenario I have 2 databases like Database1 and Database2. In ...
0
votes
2answers
76 views

MySQL DELETE statement doesn't use index although the same SELECT query does

I've got a table with ~30 million rows (and soon twice/triple times more) where I have to do quite regular updates. The table structure is like the following: id, cookie_id VARCHAR(45), country ...
0
votes
1answer
26 views

Procedure MySQL cursor taking a long time to execute

I have a MySQL procedure, with a cursor in it. The sample cursor query is given below DECLARE cs CURSOR FOR (SELECT a.mag_id FROM A a WHERE a.creation_date BETWEEN (...
1
vote
0answers
61 views

Slow MySql with left join and Group by

Below is the Mysql 5.7 Table query which has a left join with the group by. When I execute it takes more execution time. line_items have around 35 Lakhs (3.5M) data and users have 1000 rows. Below is ...
0
votes
1answer
27 views

Optimizing the repeated use of the same if condition inside a VIEW?

CREATE OR REPLACE VIEW VIEW_NAME AS ( SELECT table0.fieldBlah "FieldBlah", --more fields IF(table1.foo or table2.foo IN ('State1', 'State2'), 0, table3.uniqueField) "uniqueFieldName") ...
-1
votes
1answer
58 views

Can't get this query to work properly

I've been trying to get the following query working for days but I can't seem to solve my issue. I'm using the MySQL version 5.7.9 I have the following database: CREATE TABLE user ( id INT NOT ...
1
vote
2answers
48 views

how to get the marks in ‘Math’ of ‘English’ topper for each class in mysql for student result data

I need to find the marks in ‘Math’, of ‘English’ topper for each class in MySQL for student result data. Output schema: class, roll_no, math_marks (Here expected result image not exact answer) ...
0
votes
1answer
16 views

Incomplete MysqlDump Files - Mysqldump Inside For Loop

I am running a Windows 10 Machine with WAMP installed. I have also a Git Bash Installed. I was trying to backup all my databases in each separate file using the code below. for DB in $(mysql -uroot ...
1
vote
1answer
31 views

migrate users from mariadb10.1 to mysql5.7

My company is moving our databases from self-hosted mariadb 10.1 to Google CloudSQL (which is essentially MySQL 5.7). Dumping / restoring our application schemas looks straightforward. However I'm ...
-1
votes
1answer
54 views

What are the right ways to analyse and tune up innodb_thread_concurrency in mysql 5.7?

I'm in a plan to increase thread concurrency in my production MySQL server 5.7 but not sure, before what are the params/configurations that need to be measured and configured to increase the thread ...
-1
votes
1answer
26 views

MYSQL Trigger: Update before insert

I want to increment the order of every video by one every time a video gets inserted so that the new video takes its place UPDATE services SET orders = orders + 1 This doesn't work, I get this error,...
0
votes
0answers
34 views

Trimming copy/delete mysql table rows using Trigger

I am using Freeradius AAA server on Ubuntu 18 server which uses mySQL ver 5.7.27 as DB to store entries. INNODB is being used. It have a table called radacct which stores users accounting /session ...
0
votes
1answer
34 views

Intentionally slow down importing of (Percona) MySQL 5.7 dump

I need to move a very large MySQL table (250M rows, 120 columns. 120GB of data, 310GB of index) from one database instance to another (on AWS). For reasons beyond the scope of this question, this is ...
0
votes
0answers
14 views

MySQL 5.7 on W2K12 essentials - starts fine and then fails after restart

I have a W2K12 Essentials server that I'm trying to get MySQL 5.7 running on for in house development. It installs just fine - I change install and data directories to two separate directories on the ...
0
votes
0answers
17 views

MySQL 5.7.26 data exists in .ibd file but 0 records exist in table

We found an old mysql database that we're trying to restore. What does it mean when there's clearly data in the .ibd file when opening in notepad++ (the .ibd file is 288 kb) but the table contains no ...
0
votes
1answer
12 views

Update value by column/field in INSERT SQL

I want to update the column value in multi-rows insert SQL. For example: INSERT INTO `product_price` (`product_id`, `store_id`,`price`) VALUES (20001, 1, `price`+10), (4442, 1, `price`-5), (2442, 1, ...