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.
18,989
questions
0
votes
0answers
15 views
Inserting data to a table which isn't present in another table
I am having the below portion of the database to deal with. I am making an attendance system. I have to insert the record of students who haven't marked their attendance. If a student marks his/her ...
0
votes
2answers
29 views
Selecting distinct rows from two tables, using multiple conditions
I'm trying to figure out a query. I have an offices and meetings table in my DB. Offices can have multiple meetings (office ID is a FK to the offices table). Some meetings allow lunch, some do not. ...
1
vote
2answers
29 views
Non-deterministic functions and row-based binary logging (MySQL)
Newbie questions. I'm working on a MySQL project that will use only row-level binary logging. There will be no replication. Since logging is row-level, does it even matter if a function is ...
2
votes
1answer
31 views
construct a sql , assign it to a variable and execute inside shell script
I am trying to execute a mysql query inside my shell script, which is not executing .
Here is what i am trying to do
query="\"select count(*) from information_schema.tables where TABLE_SCHEMA not in (...
0
votes
0answers
12 views
MySQL: Storing calculation results that can be updated
I am currently saving the results of a costly operation in a database for later retrieval within an API. The operation itself takes around 0.5-1s depending on the input data. To be able to shortcut ...
0
votes
1answer
9 views
DR For MySQL multi master nodes
I’ve planned to build MySQL master-master replication and need DR for this setup. I was thinking for replication from any of the node as a slave.
Please help, which option is best and how to switch ...
0
votes
0answers
11 views
Alter table executes despite Lost connection to Mysql server
mysql Ver 8.0.19 for Linux on x86_64 (MySQL Community Server - GPL)
InnoDB engine
I executed 'ALTER table ... drop column' on a mysql table.
Despite the client losing connection to the server, I ...
0
votes
0answers
7 views
Spawn SQL server only to serve binlogs
I have a static binlog file to be consumed by downstream application ( Debezium ), however the application only consumes from a MySQL server.
Is it possible to spin up a MySQL server only with the ...
0
votes
0answers
10 views
The MySQL server is running with the --super-read-only option
I am installing a MySQL HA cluster in kubernetes v1.16.0 like this:
./helm install report incubator/mysqlha -n middleware
and now I connect to the MySQL using this connection:
jdbc:mysql://report-...
-1
votes
1answer
50 views
Mysql server not working, I tried everything
I tried everything but always get this. I reinstalled mysql ~10 times now and always the same thing happens.
Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
...
1
vote
5answers
129 views
Slow fetch time for MySQL query with large result set
I'm aware I should expect slower fetch times for large result sets generally, but I don't understand why fetching should be this slow. The workload fetches a large number of rows (1-10M) from a large ...
0
votes
2answers
48 views
write “ENGINE=INNODB DEFAULT CHARSET=UTF8MB4” once for the whole script
When exporting my database structure there is this ENGINE=INNODB DEFAULT CHARSET=UTF8MB4 after every table creation. Do you have to write it for every table? Or is there a way to write it once for ...
1
vote
0answers
15 views
MacOs MySQL service starts then stops immediately [closed]
Recently the problem occurred that I couldn't connect to my MySQL database anymore. I traced the problem to the point that the MySQL service starts and then immediately stops again. I opened the MySQL ...
0
votes
1answer
38 views
Trying to find a way fit an extremely large index in to memory
Description:
I am having trouble with the on-growing data size in my MySQL database.
I am using Ejabberd and MAM function which will make use of an archive table to store messages sent between ...
0
votes
1answer
13 views
What settings to use to export mysql table rows that I'll be importing to another table with data using phpmyadmin
If I'm using phpmyadmin to manage mysql database, what settings should I use to export only two rows of data that I'll be importing to another table with the same name that contains many other rows?
...
0
votes
1answer
19 views
Create table using select statement Truncated incorrect datetime value:
I have looked up different answers on similar question before posting it here.
So i have a table with user id and date column
I would like to store the results of select query to a temporary table.
...
0
votes
2answers
43 views
Size of a MyISAM table doubles after OPTIMIZE
I have this table:
CREATE TABLE `foo` (
`CalculatedResultsId` int NOT NULL,
`Md5Hash` char(32) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL,
`SectionData` json NOT NULL
) ENGINE=...
8
votes
1answer
56 views
MySQL deadlock: upsert query acquiring gap lock twice?
I am trying to debug a deadlock scenario which has been puzzling me for quite some time now. I am wondering if someone can shed any light into how this can happen.
Here is the deadlock output:
------...
0
votes
0answers
7 views
Storing data for machine learning in MySQL
I am currently in the process of developing a machine learning application, where my users can upload their own data to train models. Furthermore, the users must be able to extend their uploaded data ...
1
vote
2answers
30 views
MYSQL Query Index Check
I have a table with the following structure;
CREATE TABLE `operations_copy` (
`id` bigint(22) NOT NULL AUTO_INCREMENT,
`date` datetime NOT NULL,
`zoneId` int(11) unsigned DEFAULT NULL,
`...
0
votes
1answer
16 views
When should run Analyze table statement in mysql
Hello DBA's i have also some question
Why should we never run ANALYZE TABLE?
How do I know for sure this table/db needs to be analyzed ? It seems that MySQL doesn't store when the last time "stat" ...
1
vote
2answers
36 views
MySQL - Flush Memory after loading files
I am loading a great amount of data which are in CSV files using LOAD FILE command. It is working fine and after loading that files, it executes some data transformation...
The problem is that, after ...
0
votes
1answer
20 views
MySQL query - how to remove variable data
I wonder if someone can help me please? I recently migrated from PHPBB forum software to MyBB software, but when the conversion finished I was left with lots of weird characters in many posts, such as ...
1
vote
1answer
15 views
mysqldump locks all tables even when specifically grabbing a few?
I'm using a mysqldump command pulling data from 3 tables of 7. Are the other 4 locked as well?
0
votes
1answer
17 views
pt-online-schema-change error creating triggers
I am trying to add an index with pt-online-schema-change. The dry-run succeeds, but I am stuck at creating triggers. Any help ?
pt-online-schema-change --alter "ADD INDEX domain_index (domain) USING ...
0
votes
0answers
16 views
Access Oracle DB from AWS RSD Mysql DB
I have two databases. One is in Oracle and the other one is in AWS RDS MySQL. How can I access my Oracle Database from AWS RDS MySQL Database?
0
votes
1answer
43 views
Can filtering be done more efficiently with/without sql joins?
I have a simple problem that has a simple solution with SQL, but would like to explore alternative ways to solve it if they turn out to be more efficient on large scale.
Let's assume that we have a ...
0
votes
1answer
22 views
How to access the error log [closed]
Is there any other ways to access the error log in MySQL on ubuntu other than using SSH? I really need to access the error log, but I do not have any privileged to connect via SSH. Please help me. ...
0
votes
2answers
24 views
Change the binlog format MIXED to ROW
I have a database that is running on AWS RDS (MySQl 5.7.22).
Currently, I have one master and 2 replicas running. My binlog format is MIXED. I have plans to change it to ROW based.
I want to ...
1
vote
0answers
34 views
MariaDB stops responding unexpectedly [closed]
I have noticed for the second time this week that the MySQL (MariaDB 5.5) stopped responding unexpectedly. Once I do restart, it starts working normally. The database is for a Moodle (LMS) server.
...
1
vote
1answer
33 views
MySQL InnoDB Insertion Faster Than MyISAM
I wrote code that inserts about 15 millions rows into tables in MyISAM and InnoDB for comparsion.
MyISAM and InnoDB are set up with no optimization configuration in /etc/mysql/my.cnf.
And tables ...
1
vote
1answer
51 views
Why does MySQL consecutive JOIN give an unexpected result?
I had 3 tables which were Category, Product and Vendor. Then I tried to make a consecutive join 3 tables in 1 query to count the product for each category.
If a product or it's vendor is deleted, it ...
1
vote
1answer
32 views
MySQL server keeps crashing every few minutes
I am having the hardest time tracking down this problem.
Every few minutes or so my database server crashes so all 3 WordPress sites on my WHM cPanels all crash at the same time. When it does, I get ...
1
vote
1answer
23 views
How can I ONLY get the query execution time on the command line without waiting till the data is displayed?
I am testing and comparing execution times of queries with big result sets using the command line.
It takes too long to display the result set! Is there any way to see only the query execution time ...
1
vote
3answers
107 views
Slow performance when joining a small table and filtering out on a non-key column in MariaDB (MySQL)
I am fairly new to MariaDB and I am struggling with one issue that I cannot get to the bottom of it. This is the query:
SELECT SQL_NO_CACHE STRAIGHT_JOIN
`c`.`Name` AS `CategoryName`,
`c`.`...
0
votes
1answer
21 views
total drinks per day column [closed]
Attempting to select a table with a column where the customers total drinks ordered per day are calculated by the odate and drink number.
EG.
Claude orders 10 drinks total on the 2020-1-12
drinker | ...
-1
votes
1answer
34 views
SQL merging 2 rows [closed]
Please see the attached screenshot.
If the code field and the des both match then select the latest date for the vendor and retail price fields and then I need to merge NULL values in the other ...
1
vote
1answer
12 views
MySQL Store Procedure - Setting AUTO_INCREMENT value
I am in trouble coding a store procedure as shown below
CREATE DEFINER=`dbcomexuser`@`%` PROCEDURE `clearance_imp`(IN CAPIfilename VARCHAR(12))
BEGIN
DECLARE `_rollback` BOOL DEFAULT 0;
DECLARE ...
1
vote
3answers
67 views
Table design - one table or hundreds of small ones
I have the following problem:
I have measuring devices, each one measure always the same quantities from the list of five different ones. Each device can measure different quantities from this list. ...
0
votes
2answers
48 views
#1235 - This version of MariaDB doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
I have a table with several "categories", called collection (e.g. A, B, C, ...).
I would like to create a SQL query to select N records by each collection.
For example:
I would like to select 2 ...
1
vote
2answers
29 views
How can I get this code to work? It's in mysql, I keep getting an error 1822' failed to add foreign key constraint. Missing index for constraint' [closed]
CREATE TABLE EMPLOYEE (
employeeNumber VARCHAR (15) NOT NULL,
employeeName VARCHAR (20) NOT NULL,
department VARCHAR (10) NOT NULL,
phone INT NOT NULL,
PRIMARY KEY (employeeName, department)
...
1
vote
1answer
17 views
How to JOIN a table with itself while using a sub-query?
I JOIN a table to itself as
SELECT t1.TagID AS TagID1
,t2.TagID AS TagID2
,COUNT(1)
FROM TagMap AS t1
JOIN TagMap AS t2 ON t1.ArticleID = t2.ArticleID AND t1.TagID <> t2.TagID
GROUP BY ...
1
vote
1answer
25 views
MySQL cpu at 400% on c5.xlarge box
Box is running only mysql and CPU is at 400% constantly. I can't seem to find anything that would be causing it. I've changed a few values in my.cnf based on some research, but had no luck reducing ...
0
votes
0answers
37 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 ...
0
votes
0answers
26 views
Designing a (many to many) to many relationship
Currently I have the following situation. I have a plant_species table with the following attributes.
plant_species
id(PK), name, latin_name, description, synonym
A plant species can have one or ...
0
votes
1answer
34 views
How to collapse contiguous single day start/end date ranges in MySQL
(edited - I oversimplified the original problem)
I'm trying to figure out a MySQL query that will collapse one-day long contiguous start/end date ranges. Here is my sample input data:
INSERT INTO ...
1
vote
2answers
22 views
Hash IDs (such as base64 YouTube video Ids): store as int/bigint, or as string, and why?
I want to generate "unguessable" IDs, rather than /record/1, /record/2, /record/3 etc.
YouTube's style of using base 64 allows for 16.7 million IDs to be stored in just 4 characters (64 * 64 * 64 * ...
0
votes
0answers
13 views
MySQL - Joining table with different charset in a view
I want to join tables from two different schemas. But the problem is both schemas follow different charset utf8_general_ci & utf8mb4_unicode_ci.
When i do a join on both tables, it works. But, ...
0
votes
1answer
14 views
Query to display 3 metrics with corresponding date
I need some help writing a query
Let me first explain what I need to achieve. So we have 3 tables in our database, one that records quotes, another sales and lastly welcome calls. Each of these 3 ...
1
vote
1answer
33 views
Unexpected query execution plan on MySQL 8 when using Datetime range
I have a MySQL 8 database with about 900 millions of rows. When I run the following query the response is very slow because table B doesn't seem to be using the right index:
SELECT B.name
FROM A ...
