Questions tagged [mysql-5.5]
MySQL version 5.5 - please also tag with mysql for search purposes.
1,187
questions
0
votes
1
answer
426
views
Exporting mysql query result to .txt file
I want to export query result into .txt file
The path of .txt file and query is dynamic
DELIMITER //
create procedure test(filepath text , _query text)
BEGIN
SET @sql = CONCAT(_query, ' into ...
0
votes
1
answer
490
views
Users Export From Mysql Version 8.0 to 5.7
I want to exports all users from mysql version 8.0 to 5.7. Can you guide me for this activity. I want same password and privs should migrate.
- 11
0
votes
1
answer
597
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
2
answers
90
views
Why is this SELECT creating temp tables?
When I run the following SELECT, it takes quite long, typically 30 or 40 minutes (I'm on Debian, MySQL 5.5.62):
SELECT ga.currency, o.organisation_name, os.hostname, g.global_name,
SUM(IF(ga....
- 231
0
votes
0
answers
19
views
MySQL: why is the time reported at the end of a query different from real time?
I have recently been running some fairly slow select statements, and what I notice is that the time reported at the end is typically much less than the time it actually ran. Is that to be expected - ...
- 231
0
votes
1
answer
284
views
I have to delete over 90 million records, do I remove or maintain the transaction's SQLWARNING?
SQL Server: mysql Ver 15.1 Distrib 5.5.65-MariaDB, for Linux (x86_64) using readline 5.1
OS: Slackware 14.1
That's 90 million old radius bank records I want to remove, leaving only those this month....
0
votes
1
answer
189
views
How to avoid index_merge in a query?
I have multiple queries using index_merge but for put an example, I show you the following query
EXPLAIN select count(*) From Clientes Where id_campana='1849' and id_empresa='55' and (...
- 52
0
votes
0
answers
36
views
How to get statistics by query in a range of dates
I have the following query:
select SUBSTRING(VARIABLE_NAME, 5) as tipo_consulta,
VARIABLE_VALUE as numero_total_operaciones,
round(VARIABLE_VALUE / ( select VARIABLE_VALUE
...
- 52
0
votes
1
answer
38
views
garbd with proxysql and mysql replication
Can be configured garbd with proxysql + MySQL Replication?, I installed proxysql with 1 master and two slaves using MySQL replication but I was disputed about to connect garb with proxysql how I was ...
- 52
0
votes
2
answers
430
views
How to login into mysql shell from localhost instead of '%'?
I need to grant a permission to a newly created user. I have access to root. But, i can't grant a permission to a new user. When I run:
grant all privileges on 'newuser'.* to 'newuser'@localhost;
I ...
- 141
0
votes
5
answers
4k
views
Is it possible to run multiple versions of MySQL Workbench?
I'm doing an application migration from MySQL 5.5 to workbench v8... 5.5 supports MySQL Workbench up to version 6.3... However, MySQL server 5.5 only supports up to version 6.3 of the workbench.
I ...
- 101
-1
votes
1
answer
458
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,...
- 159
2
votes
2
answers
872
views
After upgrade from MySQL 5.5 to 5.7 queries are more frequently encountering deadlock
Recently we migrated our production DB to Amazon RDS with version upgrade from 5.5 to 5.7 using AWS DMS service. After that, we are frequently getting deadlock issues for our insert...on duplicate key ...
- 121
0
votes
0
answers
345
views
Why query mysql 8.0 response too slow with respect to mysql 5.5
I have the following query which in mysql 5.5, original server runs in approximately 3 seconds but all the opposite with respect to mysql 8.0, which runs in about 3 minutes, a big difference!!!. This ...
- 52
2
votes
2
answers
2k
views
Mysql: Is it possible to handle 1000 concurrent while having low cpu? [closed]
We're running Centos 7, we're in the process Mariadb 5.5, upgrading to 10.3 today. PHP 5.
The queries are bad, and there are far too many of them to optimize, but I'm trying what I can, the database ...
- 159
-1
votes
1
answer
878
views
Unable to declare user-defined variable in MySQL
I'm creating procedure called min_max to find minimum and maximum for every column in a table.
But I'm encountering an error for declaring the variable. See below:
DELIMITER //
DROP PROCEDURE IF ...
- 101
0
votes
0
answers
189
views
How to change datetime system timezone to other zones in SQL Query using CONVERT_TZ() SQL Function
I have two times timeA & timeB stored in database, I am using SQL Query to extract data from DB as:
SELECT ID, timeA, timeB, CREATED_AT, IS_ACTIVE
FROM datatable
WHERE IS_ACTIVE = TRUE AND NOW(...
0
votes
1
answer
48
views
Get 2 counts in single query
I need to get count of 2 columns with group by
This is my sample data
I need to get count of persons from particular state and count of persons from particular country
My output should be like
I ...
- 101
1
vote
1
answer
4k
views
MySQL load data infile ERROR 1064
I am using MySQL version 5.5 and I am trying to use load data infile and I am getting errors. I've looked at the documentation and it looks like I am doing exactly what the docs specify.
Running ...
- 133
3
votes
2
answers
2k
views
What happens when MySQL/InnoDB buffer pool size is small and a large transaction is run causing the buffer pool overflow?
Let's say we have a transaction running 50,000 inserts which dirties approximately 600 MB of the database. If the buffer pool size is 128 MB, there will be dirty pages in the buffer pool while the ...
- 31
0
votes
0
answers
1k
views
can delete manually a .ibd file and .frm file?
I have the following trouble in a mysql server database:
190919 17:35:30 [ERROR] Cannot find or open table abril2017/mexico from
the internal data dictionary of InnoDB though the .frm file for ...
- 52
0
votes
0
answers
19
views
How to select and order by id and get multiple results?
I have one database clients and two tables: business and categories.
The site is a classified ads placement, business contains:
Field Type Collation Null Key ...
- 101
0
votes
1
answer
1k
views
Show Processlist state "Statistics"
Please note that it's not a duplicate thread because I already set variable optimizer_search_depth and didn't have any impact.
Sometimes, our database/application services crashed due to max ...
- 43
1
vote
2
answers
566
views
GRANT permission ON database TO user not working on AWS
I'm having trouble granting a user permission on AWS.
mysql> GRANT CREATE ON *.* TO 'hmrex_db'@'%';
Query OK, 0 rows affected (0.01 sec)
mysql> SHOW GRANTS FOR 'hmrex_db'@'%';
+----------------...
- 141
0
votes
1
answer
606
views
Mysql Query Optimization [Match Against] [closed]
I have tried all the possible option to optimize the query, but no luck.
Query 1:
SELECT
*,
(
3959 * acos (
cos(
radians(51.5073509)
) * cos(
radians(...
- 101
0
votes
1
answer
456
views
Query to select specific columns based on current date
I have a table named 'mrtg' that has four columns:
mysql> select * from mrtg;
+----------+------+------+------+
| city | day1 | day2 | day3 |
+----------+------+------+------+
| Delhi | ...
1
vote
1
answer
1k
views
MariaDB 5.5 -- how to customize password rules
I am using RHEL 7, which includes MariaDB 5.5.
I want to set up minimum requirements for passwords: minimums length 15, 1 lowercase, 1 uppercase, 1 symbol, 1 number.
I figure that I am probably ...
- 113
0
votes
1
answer
68
views
In MySQL, how to execute two different SELECT statements depending on a condition?
SET @input1=123;
SET @input2='foobar';
IF @inout1>0 THEN
SELECT
@result := col12
FROM
table1
WHERE
col11=@input1;
ELSE IF @input2 != ''
SELECT
@result := col21
FROM
table2
...
- 3,377
0
votes
1
answer
2k
views
Endless Query Loop In Mysql
I will delete millions records from a table in mysql dbms, but i don't want to delete all records at one time not to force database server cpu. That's why i want to use my query many times. My whille ...
- 47
0
votes
2
answers
1k
views
How to display the dates without DUPLICATE and ORDER by MAX DATE
I just want to ask that how can I display the null values along with the MAX date.
SELECT
MEMB.MEMB_N,
MEMB.LAST_M,
PrintDate
FROM
MEMB
INNER JOIN tblPrint
ON MEMB.MEMB_N = tblPrint....
- 3
0
votes
1
answer
168
views
How to reduce the response time of a query that yields ~4.9 million rows in ~12-13 seconds
I have the following doubt about the execution performance of a query. This query takes about 12-13 seconds, and brings a total of 4,879,049 rows. Could I lessen this query's response time?
The query ...
- 52
0
votes
0
answers
37
views
Create User in MySQL
Is it possible to create an user without create user command? I mean just by make an entry(framing insert query) in mysql.user table? Will it cause any crash in system tables?
1
vote
0
answers
669
views
Multiple join with self join in MySQL and split column in row
I have four tables "Commission" , "Vendors", "Accounts" and "comsplitconfig" like
commission Table
commissionid vendorid accountid sales commission
1 V1 A2 ...
- 11
0
votes
0
answers
78
views
The USE-Statement breaks on MySQL-Server
running webadmin for years now - and having troubles for months - and posting here for weeks. But the support seems to be not the best...
want to run sql-commands on the newest version - but it fails ...
- 11
0
votes
0
answers
49
views
How can I resolve the `package mysql-server is not installed` issue?
I have just upgraded MySQL from version 5.5.55 to version 5.6, via SSH. I have managed to successfully start the MySQL, however I am experiencing the 500 Internal Server Error when trying to access ...
- 119
1
vote
1
answer
2k
views
Unable to downgrade MySQL from version 8 to 5.6 nor start MySQL (MySQL Error Log Included) [closed]
I run a Plesk VPS (OS: Centos 6/Linux (Final)), whereby I recently tried to upgrade the MySQL from version 5.5.55 to version 5.6.
Being relatively new to this area, I strictly followed this Plesk ...
- 119
0
votes
2
answers
335
views
SQL query to list all student users, add column to show who were logged in at least once
I have a query, which lists those users, which are students:
SELECT *
FROM mdl_user as u
JOIN mdl_role_assignments as ra ON u.id = ra.userid
WHERE
ra.roleid = 5 /* is student */
I have a table ...
- 157
1
vote
1
answer
54
views
Mysql Query Optimisation For Joining a BETWEEN Date and Date query
I have some instagram API data sitting across two tables. One with post_data, and one with profile_data.
The profile_data table has a timestamp of when the account was checked through the API to get ...
- 111
0
votes
2
answers
241
views
Why MySQL is ignoring my indexes in a query?
I have the following query in a production database server(master):
SELECT *
FROM Enlace_llamadas
where id_empresa IN (1,5,33,36,40,44,48,53,55,111,115,125,127,151,167,175,181,185,187,189,195,199,...
- 52
1
vote
1
answer
106
views
executing single query in loop increase time every time
Am executing one query which is the same as below. Now here the problem is as limit increasing its increasing execution of time.
I am adding limit using a PHP script. So anyone likes to suggest to ...
- 149
0
votes
1
answer
668
views
CONCAT_WS causes "Data too long" which works partially?
I've got a simple table with several columns and I'm trying to merge two of them into one. I'm working with MySQL 5.5 and I can reproduce the error one a specific data set by running the following (...
- 103
0
votes
0
answers
62
views
Optimize query for orders with unseen comments
I have following MySQL tables in an application I have to support:
CREATE TABLE orders
(
id int auto_increment primary key,
author int(8) not null,
manager_id int(10) default 0 ...
- 115
0
votes
2
answers
11k
views
Count rows in a table based on a DATE field
I have a table with 3 columns (order_id, client_id, date_added).
The content would look something like this:
order_id | client_id | date_added
-----------------------------------
14152 | NA4156 ...
- 189
0
votes
0
answers
147
views
Where and how to set the variables for Percona MySQL Audit Variables
I am trying to set the variable values for Percona MySQL Audit Plugin in my.cnf file before installing the audit plugin on MySQL Server like 'audit_log_rotate_on_size','audit_log_rotations' and '...
- 1
0
votes
1
answer
30
views
calculate percent change between two entire rows
i have a table of currency data, it's have multiple records in each rows.
| Date | ETH | XRP | LTC | USDT | BTC | etc... |
|----------------|------|-----|-----|------|------|--------|
| ...
0
votes
1
answer
33
views
using view (necessary) with where conditions (dynamically)
Suppose we have two tables like A {a1, a2, a3 ...} and B{b1, b2, b3 ...}
We are using a MySQl view like
Select a.a1, b.b1 from A a Left JOIN B b ON a.a2=b.b2
Now our requirement is that we have ...
- 101
0
votes
1
answer
138
views
MySQL synchronization
I have a cloud-based MySQL instance (slave) and am planning a solution where a remote instance (master) will be based on a ship. The ship will go in and out of international waters and therefore will ...
-1
votes
1
answer
32
views
How to change values from one column with the same values, instead of blank spaces to insert -? [closed]
| project_name | slug ...
2
votes
1
answer
138
views
How to setup Master to Master Replication with Application auto failover
I have a requirement as follows , need to setup a Multi-Master replication with automatic application failover ,if one master goes down, Can anyone share me some information regarding this.
Thanks ...
- 31
0
votes
1
answer
660
views
Would querying from a partitioned table be faster than from multiple tables with the same table definition?
I have a table that contains about 50 million rows that I have partitioned by 5 million rows per partition. I have manually partitioned it into Table_1, Table_2, Table_3, etc., not using MySql's built-...
- 101