Questions tagged [mysql-5.5]
MySQL version 5.5 - please also tag with mysql for search purposes.
1,186
questions
0
votes
1
answer
50
views
How to join on "replace and substract"?
Using MySQL 5.5, no need to upgrade since it's on an old server and only used for Grafana.
I have two tables, foo, and bar. Foo has id and the title is null. I want to get the titles from bar and ...
- 159
1
vote
0
answers
84
views
MySQL : Huge table truncation in replication environment
We have 2 tables of size 186G and 63G that are no longer in use and needs to be truncated from the database.
I cannot directly run the truncate table statement on the master DB as it has 14 replicas ...
- 11
0
votes
0
answers
236
views
MySQL version Upgrade from 5.5 to 8 without no downtime/very less downtime
May I get instructions from anyone on how to upgrade MySQL from version 5.5 to 8 with no downtime/minimum downtime.
Make a plan like that:
Step01# 5.5 (Master)---> 5.7 (Slave): Slave will be ...
0
votes
1
answer
380
views
Select all rows where table1.column1 = table2.column1 where at least one value table2.column2 = 1
I have two tables in a MySQL database
table1:
column1 column2
table2:
column1 column2
I want to select all rows where table1.column1 = table2.column1 if at least one row table2.column2 = 1
table1
12 ...
- 1
-1
votes
1
answer
164
views
In MySQL query, VARCHAR(100) gets output as "[partnumber]" although in a query with more columns, it is still shown as 'p-12345'
This is a bad question since I only made a mistake in selecting by chance a client that had garbage data and must have changed the where condition during tests, and it was also a problem of a join ...
- 181
0
votes
0
answers
261
views
Order by id is slow
So I've read the same issues here that people experience, I could find at least a few topics talking about how ORDER BY is slow.
However, it looks like I have all the indexes necessary. But still with ...
- 101
-1
votes
1
answer
451
views
How to improve performance of a stored procedure called from a web page?
This is my first SQL stored procedure
$sql = ("CALL Login_Details($ID);");
$result = mysqli_query($conn, $sql);
$row = mysqli_fetch_array($result);
mysqli_next_result($conn);
When I run ...
0
votes
1
answer
73
views
Mysql 5.5 Master > Slave/Master > Slave Not working
I am trying to get a database that is the main database but replicates one database from another server to replicate both the replicated database and the local database to another slave.
SERVER1: ...
0
votes
2
answers
46
views
Does anyone know how to make two two-column foreign keys from a single table?
I tried the following, but I am receiving an error message.
Table goleo
mysql> create table goleo(
-> id int,
-> nombre varchar(30),
-> goles int,
-> primary key (nombre,...
0
votes
1
answer
2k
views
Force kill long running index creation
I have a 192GB table that I started adding an index to over night.
This was still going several hours later.
At the time the alter table started the database was very quiet.
There were other queries ...
- 273
0
votes
2
answers
217
views
Help needed in tuning mysql version 5.5 [closed]
I just migrated a 32 bits MySQL 5.5 to a 64 bit version of MySQL 5.5. I would like to upgrade it to 8.0. But my company has some ancient instruments. I can not do it right now before I check the ...
- 105
0
votes
0
answers
452
views
Mysql query browser not showing schemata (databases)
Hello, people, I'd appreciate it if you could assist me with this problem. I'm using workbench and mysql, and I have a few schemata (databases) in my sql. However, when I log in using Mysql Query ...
3
votes
2
answers
476
views
Apply nth occurrence number on each distinct value in a column
I have a table with a column of values where each value occurs a variable number of times (i.e., one value may occur 1 time, and another value may occur 3 times). I need to add a column that ...
- 133
0
votes
1
answer
66
views
How to speed up query which takes ~5s on Mysql5, but longer than 10mins on Mysql8
My company went through a database upgrade recently, from Mysql5 to Mysql8, and some of our queries have slowed down massively. (Going from taking in the order of a few seconds, to timing out after ...
- 1
0
votes
1
answer
174
views
MySQL Replication from On-Premises to AWS
I have an on-premises DB server running MySQL 5.5.37 with multiple databases.
I created database on the AWS RDS.
The idea is to set the replication from on-premises to the AWS, but for only one ...
- 111
0
votes
1
answer
42
views
MySQL replication username
What is the difference in using these two approaches when setting master-slave replication:
Setting the binlog-do-db option on the master to choose which DBs are going to be replicated;
Creating a ...
- 111
0
votes
1
answer
21
views
Replication setup
Currently, I have a production setup which consists of one Primary server and one Replica server, lets call them Primary1 and Replica1. All databases from Primary1 are replicating to Replica1.
Now, I ...
- 111
1
vote
1
answer
662
views
MySQL indexing on 11 million rows
I have a development and production server, both with different amounts of RAM. Development machine has 8G, while production has 64G.
I'm importing a large table from development to production, but ...
- 111
0
votes
1
answer
112
views
Windows 2008 R2: Unable to upgrade MySQL 5.5.58
We have MySQL 5.5.58 sitting on a Windows 2008 R2 box.
I have downloaded the Installer from https://dev.mysql.com/downloads/mysql/ but nothing happens when I try and upgrade.
At the installer screen, ...
- 194
2
votes
0
answers
65
views
Migrate Mysql to Azure Sqlserver db
I read some articles and also tried SSMA, linked server and other things, but not help. I have one client's mysql Db (later we need other too) in my local pc and have Azure DB which used for SAAS-...
- 121
0
votes
1
answer
75
views
mysql 4 masters in different location
Because internet is not always available i cannot just use one central database.
So there are is a DB in every location.
I need to have them all in sync, but there can be a delay of 30 minutes or ...
- 13
1
vote
0
answers
239
views
mysqldump has structure but no data, only when run from cron
I have a script with a mysqldump command that looks like this:
mysqldump ${mysql_auth_args} \
--force \
--single-transaction \
--quick \
--compact \
--extended-...
- 111
0
votes
0
answers
358
views
Problem with connect mysql databases of remote
I'm getting error with connect remote mysql database. Firstly;
1-) I use windows 10 machine and my remote server also windows 10 machine.
2-) My database at locale at remote windows machine with root.
...
- 1
0
votes
1
answer
71
views
Table design comment_seen vs comment_unseen — migrate data from one to another
I have system that allows comments posted by users and tracks which users seen which comments.
The read/unread state is tracked within comments_seen table. Surely enough, it grow tremendously.
My goal ...
- 115
-2
votes
1
answer
3k
views
your MySQL server version for the right syntax to use near 'IF NOT EXISTS [duplicate]
/*
mysql.sql - mqtt2sql MySQL database objects
Copyright (C) 2020 Norbert Richter <[email protected]>
This program is free software: you can redistribute it and/or modify
it ...
1
vote
0
answers
18
views
Query optimalization - how create proper composite index
I have mysql 5.5 and table item have around 1-3 milions rows.
CREATE TABLE `item` (
`Id_item` int(11) NOT NULL DEFAULT '0',
`item_Kod` varchar(15) DEFAULT NULL,
`Date1` datetime DEFAULT NULL,
`...
- 11
0
votes
1
answer
26
views
multiple count and use subsctract function in one query
I try to do more advance query in mysql but got error in query, i try to count and group by. here is my query:
"select department,
(select count(id) from mcu group by dept) as tot1,
(select count(...
0
votes
1
answer
720
views
WHERE...LIMIT much slower than WHERE...ORDER BY...LIMIT in MySQL 5.5
I'm using MySQL 5.5 (but i see the same behavior on 5.7)
Here is the table schema:
CREATE TABLE `so_table` (
`p` char(16) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`c1` int(10) DEFAULT NULL,
...
- 101
0
votes
1
answer
96
views
Handling the foreign keys in data entry
I have two tables (Order and equipment) and they should be linked together by the order No. the SQL script is as the following:
-- -----------------------------------------------------
-- Table `rr`.`...
- 15
1
vote
2
answers
3k
views
Creating a table with CHECK constraints in MySql workbench
I created a table ORDER using Mysql Workbench as follows:
CREATE TABLE IF NOT EXISTS `rr`.`ORDER` (
`Order No.` INT UNSIGNED NOT NULL COMMENT 'The order of the inspected equipment',
`Order_Type` ...
- 15
1
vote
1
answer
2k
views
How would I sync tables in two separate servers in mysql?
DB: mysql Ver 15.1 Distrib 5.5.60-MariaDB, for Linux (x86_64) using
readline 5.1
I have two separate servers running MariaDB 5.5, both in production.
Both setups are live, and users might access any ...
- 23
0
votes
1
answer
41
views
Optimizing a DB Query
I am using MySQL database with PHP's Yii Framework, It is an old version of MySQL, It can't be updated to latest one due to some reasons.
I have created this query to get Current month records of ...
- 103
0
votes
3
answers
2k
views
How to increase Max_used_connections?
I have a registration application. 10000 people want to access the server but only 3000 quotas available. 3000 users registered successfully online within 3 minutes on the registration day. However, ...
- 1
3
votes
2
answers
137
views
At my wits end trying to create my very 1st MySQL stored procedure
I am trying to create a stored procedure in MySQL 5.5.8. using connector 8.0.13. Once this procedure is debugged, it will have to be embedded into a c#.net application, to be created on the fly every ...
- 39
0
votes
1
answer
411
views
count of rows in table day wise
I need to get a count of rows in a table day wise.
The table looks like below:
Table ABC:
f_id|reg_date
1|2020-09-08
2|2020-09-12
3|2020-10-01
4|2020-09-07
5|2020-09-08
6|2020-09-09
Expected output ...
- 3
0
votes
1
answer
778
views
How to get sum of the count of rows till date from table in MySQL
I have a table as below:
TABLE A:
id| on_date |off_date
1 |2020-09-10|
2 |2020-09-09|
3 |2020-09-08|
4 |2020-09-09|2020-09-10
5 |2020-09-08|
I want to get the count of ids which are in on mode till ...
- 1
1
vote
1
answer
787
views
MySQL binlog format change from MIXED to ROW
One of my customer has MySQL master-slave setup at on-premises, where the binlog format is kept to be MIXED. Now, we are planning to migrate the DB to AWS RDS using DMS, the binlog format should be ...
- 113
0
votes
4
answers
84
views
Performance at finding all foreigns with 3 one-to-many relation
Basically, I have 2 tables : user and object. One user can be associated to one object by three ways (roles). I want to list all users associated at least to one business object by any role.
This ...
- 93
0
votes
2
answers
2k
views
Broken Slave MySQL : How To Fix MySQL Replication (BINLOG) error in the Slave?
Before we start I can give some info about the versions,
Ubuntu - 14.04, MySQL - 5.5
I have successfully configured the Master-Slave replication with two EC2 Ubuntu instances. My master is in Ubuntu ...
- 89
0
votes
0
answers
1k
views
Group by with Order by DESC not working using PHP MySQL codes
I have three tables,
table one name (tbl_brands) with these columns:
and second table name (tbl_loader_attachment) with these columns:
and the third table name is (tbl_images) with these columns:
I ...
- 23
0
votes
1
answer
207
views
MySQL: run a script when logging in?
Is it possible, in MySQL, to run a 'login profile' similar to the bash/ksh profile, when connecting with the mysql client? That is to say, something similar to mysql -e 'select * from a_table', but ...
- 231
0
votes
1
answer
626
views
How to dump / restore mysql.user [closed]
I'm able to dump the table of users from one server with
mysqldump -u root -p -h [host address] mysql user > user_table_dump.sql
but how can I restore these users into my localhost enviroment? I ...
1
vote
1
answer
162
views
MySQL Query optimization: select all duplicates if none expired, or the newest if one expired
I have this query, it works, I just like to optimize, if possible, here's a fully working example where you can test it
Two Items are considered duplicates if they have either the same bundle_id or ...
- 159
0
votes
1
answer
107
views
EAV, multiple left join query optimisation
Any assitance would be greatly welcomed. Pardon my ignorance however I am wondering if anyone else has had to encounter bad EAV performance issues. I have a particular query which takes a long time. ...
2
votes
2
answers
417
views
What is the cause for the error 1610
This morning, I had the following event:
200310 9:54:57 [ERROR] Slave SQL: Could not execute Update_rows event on table Call_Center_Infinit.queue_member_table; Corrupted replication event was ...
- 52
0
votes
0
answers
26
views
Error when creating scheduled event?
I creating scheduled events in MySQL 5.5 (yes, I know, and we do have plans for upgrading, but we're kind of trapped here atm). I have done this before, like:
create event upd_ga_sum_1
on schedule ...
- 231
1
vote
0
answers
45
views
Pair records by grouping on foreign key
I am stuck on a question I got in a coding challenge. Essentially I have 3 tables; ratings, reviewers, and books.
The ratings contain a list of book reviews from reviewers. I need to output a query ...
- 21
0
votes
1
answer
108
views
In MySQL 5.5: can I capture the output of SHOW ERRORS?
Is it possible to capture the messages that can be listed with SHOW ERRORS and store them in a log table? Like, is there a table somewhere, that I can select from?
- 231
1
vote
2
answers
8k
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 ...
- 111
1
vote
1
answer
2k
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, ...
- 159