Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

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.

0
votes
2answers
21 views

Inner join or cross join Confusion

For example select * from a inner join b on a.i=b.i ; is definitely inner join. But can you let me know whether the below statements belongs to cross join or inner join ? select * from a ,b ...
0
votes
2answers
41 views

What kind of SSD to speed up querying a lot of data?

For research purposes I am using a big MariaDB database (~500 GB) with 20 Tables (engine: myISAM) and around 9 Billion rows. Due to missing free storage on my Laptop (16 GB RAM), I put the data on an ...
0
votes
1answer
17 views

Is it possible to only display the largest calculated table column?

Below is a code to sum all rows in each column where the value of each column c(x) equals 1. It gives me six columns containing each calculated sum. Is it possible to only display the largest ...
0
votes
3answers
52 views

MySQL insert into different databases tables based on PK value

Lets assume i have two databases, both having a table 'user_feedback', sharding data between the two databases. My goal is to insert all records where Primary key Id%2=0 goes to db1.user_feedback, and ...
0
votes
1answer
19 views

Count Total Transaction each specific row even if 0

I have a 3 Columns namely: Payment Channel Payment Name Total Transaction The Total Transaction will be the COUNT of each Payment Channel and Payment Name. In the Payment Channel, values have PC01-...
1
vote
1answer
36 views

Error: The user specified as a definer ('Admin'@'%') does not exist

Error de SQL (1449): The user specified as a definer ('Admin'@'%') does not exist I have this error when I'm trying to update a table. I don't know what I must do, because this error only happens ...
0
votes
1answer
16 views

10.3.9-MariaDB can not query equal operator with special character

I have a MariaDB database that has a filed TagName with value ~!@#$%^&*()_+|}{":?><./';[]\=-` I had set mode SET @@SQL_MODE = CONCAT(@@SQL_MODE, ',NO_BACKSLASH_ESCAPES'); When I try to ...
0
votes
1answer
38 views

Are these stats reasonable? If not, where should I start?

The below is the output result of mysqladmin for our mysql server which has been having consistent problems for months since we switched from a dedicated server to the cloud. In the switch, we ...
0
votes
0answers
13 views

Is the result of ST_Union defined?

With MariaDB 10.1, I get a simplified LINESTRING when I provide two lines can be joined. SELECT ST_AsText(geom) FROM ( SELECT ST_Union( LineString(Point(0,0),Point(1,1)), LineString(Point(1,...
0
votes
1answer
16 views

Does MySQL or MariaDB support a LineString constructor that takes points?

It seems with MySQL and MariaDB I'm getting the following when I try to use the LineString constructor. In PostGIS I can do this with ST_MakeLine(pt1,pt2) > SELECT ST_MakeLine(Point(0,0), Point(0,...
1
vote
1answer
41 views

MySQL utf8mb4 Migration - Replication Considerations

We're planning to migrate a MySQL database with a mix utf8 and latin1 tables to the new utf8mb4 character set per this article: https://mathiasbynens.be/notes/mysql-utf8mb4. Our server is 5.7.22 and ...
1
vote
0answers
23 views

How to exclude query results based on a blacklist

I have defined two tables: CREATE TABLE `devices` ( `uuid` varchar(256) NOT NULL, `token` varchar(256) NOT NULL, `last_connected` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, ...
1
vote
1answer
21 views

Logs from Syslog-ng to MySQL

Syslog-NG ver 3.18 MySQL ver 8.0.15 I configured my Syslog-ng as follows: source s_network { udp(); tcp(); }; destination d_file { file("/var/log/app_network.log"); }; destination ...
0
votes
0answers
29 views

“Can't find any matching row in the user table” when attempting “SET PASSWORD”

When I do SELECT User, Host FROM mysql.user I see that I have a user named myuser on %. I tried setting that users password thusly: SET PASSWORD FOR 'myuser'@'%' = 'mypassword'; Doing so, however, ...
1
vote
1answer
23 views

MySQL - how to dynamically add columns with subquery in Select clause based on date range

I have the following statetment: Select a.Data AS Data_A,b.Data AS Data_B, (Select Round(Sum(C.Value)) From Values c Where (Year(c.TimeStamp) = x) and (Month(c.TimeStamp) = y) ) AS ...
0
votes
0answers
32 views

slow select query mysql table

I have a table with 8 million of records which has 10 columns and one of the column is 'text' type while other are mainly timestamps or varchar(10). I have all the necessary index created . Table size ...
0
votes
1answer
18 views

select document based on date range

Structure CREATE TABLE `template` ( `name` VARCHAR(16) NOT NULL COLLATE 'utf8_unicode_ci', `valid` DATE NULL DEFAULT NULL, UNIQUE INDEX `name` (`name`) ) COLLATE='utf8_unicode_ci' ENGINE=...
-1
votes
1answer
41 views

MySQL - Optimize a table which has mostly text data type

I recently faced some performance issue on a CMS based application. I can somehow fix other tables. But Im not able to optimize the table which has 40+ columns and 10 columns are having text as the ...
0
votes
1answer
26 views

Galera Arbitrator system requirements compared with the actual Galera MariaDB node requirements

Right now our setup is CloudFlare > 1x HAProxy (Dallas,TX), 3x App servers (Dallas,TX), 3x MariaDB nodes in a Galera multi-master cluster (Dallas,TX). The App servers auto-scale horizontally to handle ...
0
votes
1answer
16 views

mysql trigger not allowing to select from another table

I have the following snippet in a trigger on a table named bugs: SELECT uat_reopened_stats.release_name (uat_reopened_stats.rate_bugs + qa_reopened_stats.rate_bugs + ...
0
votes
0answers
28 views

Restoration in MySQL 8 is slower than MySQL 5.6

Recently I have installed mysql 8.0.12 version and started observing that its taking too long to restore database. So I verified the same with using a backup file which I restored to MySQL 8.0.12 as ...
0
votes
0answers
24 views

It's possible to avoid overload on a Mysql slave by slowing down replication?

I have a slave on a machine less powerful than the the master (8CPU/8GB on the master, 2CPU/4GB on the slave, MySQL 5.7). Replication is binlog ROW based As the db is increasing on the master, the ...
0
votes
1answer
23 views

How to remotely manage (with PhpMyAdmin) a MySQL RDS on a load-balanced AWS Elastic Beanstalk setup?

Although there are similar questions and topics, I can't find a solution that currently works. I specifically wrote remotely manage because I'd like to use my home computer's PhpMyAdmin to directly ...
0
votes
1answer
12 views

Possible to update rows with data only accessible via API call?

I'm not good at what I'm working on so apologies if some of my terminology is off. Also not sure if this belongs here, or in a more programming oriented area. I am looking for options to update ...
0
votes
1answer
31 views

How to get useful error information from mysql command?

I'm trying to script the creation of a database with tables. I'm encountering errors but the error messages are not helpful to me because I'm a novice. I'm finding the mysql tool is providing mostly ...
2
votes
1answer
60 views

RDS MySQL read replica larger than master

We have a Multi AZ AWS MySQL 5.6.40 server with a matching single AZ read replica. Both have 200GB of storage available. However, the read replica is rapidly running out of free storage space, while ...
1
vote
2answers
26 views

Using CONVERT TO CHARACTER SET for changing only collation?

I think the answer to this is an obvious yes, but I'm having trouble finding confirmation on it. All the questions related to charsets seems to be about changing the charset and not only collations. ...
0
votes
1answer
22 views

Pass parameter to trigger dynamically

I have a table on a mysql 5.7 db, containing say athletes with their mean, max, avg times in a specific sport. I have another table that lists some calculated statistics based on those values. I ...
0
votes
1answer
39 views

Query with HAVING clause is taking too long

I have query like this: SELECT msg.id, CONTENTS, UNIX_TIMESTAMP(msg.created_at) 'created_at', category, msg.`uid`,name, user_name, profile_pic, STATUS, isNotificationOn, ...
2
votes
0answers
29 views

Database Design for tracking user flow

I want to design a user tracking system between different tables/models. I want to track the user's progress. There is a Module table which is related to sections and sections are related to sub-...
1
vote
1answer
18 views

get average of number of likes in mysql from two tables

let's say I have a posts table: post_id user_id text 1 2 abcd 2 1 wert 3 3 yhj and I have a ranks table: post_id liker_user_id ...
0
votes
2answers
25 views

LOAD DATA mysql command and use only one column

I have table t1 with three columns, and a file per line a text. my tables contains id,sura and aya fields. I need to fill only aya from File. I use the following command: load data infile '/home/...
0
votes
1answer
18 views

How can I have intensive stored procedures run on a different machine to where tables are stored?

For a home air quality monitoring project I am building, I am collecting sensor readings and storing them on my Synology server, which includes a MariaDB instance. The Synology is great for managing ...
0
votes
1answer
25 views

Can MySQL show the respective timestamps in each INSERT output message?

For example, after an INSERT, I see: Query OK, 296 rows affected (0.02 sec) Records: 296 Duplicates: 0 Warnings: 0 I would like to see timestamps with these lines, if possible, e.g.: 2019-...
0
votes
0answers
12 views

What's the formula for calculating Key Efficiency in MySql? [duplicate]

How to calculate Key Efficiency percentage in MySql
0
votes
1answer
16 views

Aws RDS cpu Spike to 100% because of running set isolation command and set

My application running with the AWS RDS as a backend. currently i am doing a performance test. While doing that i am seeing RDS cpu usage goes 100% after 20min. While seeing the performance insights ...
0
votes
1answer
25 views

Mariadb (MySQL) On Windows- problem entering non-ASCII characters in a query

MariaDB: mysql Ver 15.1 Distrib 10.3.8-MariaDB OS: Windows 7 Pro 64 bit I have my server database and client all set up (I believe) to support utf8 end to end- (**my.cnf**) # # Set everything to ...
0
votes
0answers
30 views

MySQL automatic import of (huge) csv files fails using mysqlimport

For testing purposes, I have to insert large Time Series data on a MySQL database, running on a Windows remote server. Firstly, the (very basic) table: CREATE TABLE 'sensor_data' ( '...
0
votes
0answers
28 views

Improving/ Optimizing Performance Query in MySQL PHP

I have a invoices, invoices_items, order, order_items. Invoices and Orders tables contains around 1 Millions records. Invoices_items and Orders_items tables contains more than 2 Millions records. ...
0
votes
0answers
15 views

mysql: make the server fail instead of just giving warnings

I have a mysql 5.7 server. I am trying to import some .csv files. Sometimes it may so happen that these are malformed. Here is a sample case: - 'mysql: [Warning] Using a password on the command ...
1
vote
2answers
46 views

How to cast (a) a date value recorded as a VARCHAR to (b) a specific DATE format for (c) use in an ORDER BY clause

How would I cast a column value (a "non-MySQL" DATE string retained as a VARCHAR) to a "MySQL date" so it can be used for sorting? I have dates in the format of 06-02-2019 (6 feb) but MySQL itself ...
1
vote
2answers
33 views

How to implement a dynamic referral program?

Example: Currently, we will provide 100 credits each to both the referrer and referred. Then in the future, we might want to implement a different referral program. Such as change credits to 200 ...
0
votes
1answer
20 views

csv import fails without throwing an error

Ι am trying to import a csv file into an existing mysql 5.7 table. Here is the command (the db is in docker with bind mount for persistence) mysql -h 127.0.0.1 -uroot -prootpass --local_infile=1 ...
0
votes
1answer
30 views

Is possible to use ORDER BY with IF ELSE condition?

Here I have some data. I want to show the records which have fav=1 at fast, then I want to show the data where the date is greater than the current date in ascending order(date) and at last the ...
0
votes
1answer
25 views

How to delete all but the latest entry per customer in the fastest way?

I have a table containing around 4 million unique customers (with customerId each), but each customer can have multiple entries. There is no primary key. Each row has a creation dateTime saved as ...
0
votes
0answers
28 views

add index and change int data type to bigint in mysql 5.7 with 1.3 billion records the fastest way

how do i do this without downtime? or at least minimal downtime (3hrs max) Is there any way to tweak my.cnf config to at least make this process of indexing and change data type faster? Below is the ...
0
votes
0answers
25 views

Left join with same table making query too slow — MySQL

We have a table server_user_disk_usage which tracks the disk usage of users from different servers. Query is to find sum of last disk usage of each user from different servers SELECT m1.user_name, ...
-1
votes
0answers
21 views

Inconsistent MySQL backup duration

Im having a problem with my database. The issue is my backup time using mysqldump is inconsistent. On average, the duration is only for 1hour 30minutes but sometimes it goes to 4, 6, and even 10 Hours ...
0
votes
2answers
39 views

MySQL Using AND Statement

I am having issues with this question: Find the cities that have direct (non-stop) flights to both Honolulu and Newark. The answer should be Dallas, since its the only location that has ...