Stack Exchange Network

Stack Exchange network consists of 174 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

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
0answers
2 views

MySQL: What does event `/io/table/sql/handler` mean?

Yesterday I experienced a MySQL incident the load was extremely high All select statements takes more than 3 times time than usual. All insert statements takes 10+ times time than usual. The MySQL ...
3
votes
1answer
22 views

An UPDATE runs unexpectedly on MySQL 5.7 whereas it runs as expected on prior versions

I use a column called sequence on tables where I have to re-sort blocks of rows into an arbitrarily order. CREATE TABLE `tkmemberstage` ( `ID` bigint(22) unsigned NOT NULL AUTO_INCREMENT, `...
0
votes
1answer
14 views

Count all products (with qty > 0) group by all brands. But get aslo count zero for brand which have 0 products

I have table: brands brand_id name 1 Apple 2 Sony 3 Nokia 4 HTC products id brand_id qty 1 1 3 2 3 0 4 1 1 2 2 1 I need to ...
0
votes
0answers
13 views

Type of locks and conflicts between them in MySQL and PostgreSQL

I'm doing some research of the existing type of locks both on MySQL and PostgreSQL databases. Consider reasonabily recent versions. PostgreSQL current and MySQL 5.7, for example. I came accross these ...
0
votes
1answer
19 views

MYSQL sub-query COUNT

What I am trying to achieve is run both queries (below) at the same time. From within one table rvsessions I need to get the count for the total amount of sessions(which is achieved by the number of ...
0
votes
0answers
23 views

data model for C 2 C business

I would like to find out what table relation would be idea for this case a Customer to Customer business transaction where one client provides a service to another for a charge. So this is purely a ...
0
votes
1answer
20 views

MySQL, would like to get both a field and a count inside GROUP_CONCAT

I can get a list of statuses that a tracking ticket has had, as well as the id of those statuses, as follows: SELECT t.id, t.name, GROUP_CONCAT( CONCAT(s.id, ':', s.name) ORDER BY s.id) AS ...
0
votes
0answers
16 views

how to select and calculate time difference

I have mysql table like this: what I want to do is do calculation "jam" between 08:31:31 and 08:30:46 where "aksi_ID" 4 and 9 and "nomor_antrian" 2 and 1. and the result in minute. I know there are ...
0
votes
0answers
11 views

ERROR: please confirm the keyfile is match and keyring plugin is loaded

I want to create a copy of my Production DB to a new instance. There is a innobackupex running on the prod every night looks like this: innobackupex --user=username --password=password --keyring-...
2
votes
1answer
22 views

Mysql temp table insert from json can't handle null values

The MySql-8 JSON_TABLE function allows you to handle JSON data as if it was a table. I'm trying to use it to populate a temporary table using the generated table but I believe I'm hitting a bug. ...
1
vote
1answer
15 views

mysql - calculate traffic for tmp tables

I'm thinking of placing tmpdir to SSD - to increase speed of working with sorting data in tmp tables on disk. Our base creates a lot temporary tables on disk during sorting, merging, joining an so on. ...
0
votes
0answers
12 views

Issue with Window Average function mysql

I am trying to extract data and keep it in temp table using the below query. CREATE TEMPORARY TABLE IF NOT EXISTS table2 AS SELECT avg(Close_Price) over(ORDER BY Date ROWS 19 PRECEDING) as 20_DMA ...
1
vote
1answer
13 views

InnoDB: Getting rid of a phantom table

I was playing around on a test server and i actually removed both "frm" and "innodb" files of particular table. Ok - I tried creating that table again but got information that table already exists and ...
-1
votes
0answers
27 views

Fetch 2 Different columns same data in 2 different rows

Hello Guys i have a issue and got stucked very badly. i have a table product which has 5 cols col1, col2, col3, col4, col5 there are few records with same value repeat in different col like 1 ...
0
votes
0answers
18 views

How to convert the SQL query from MariaDB 10.3 to MariaDB 10.1/MySQL 5.6

Since I need to convert the below query which should work on MariaDB 10.1/MySQL 5.6. When I tried to run the below query I used to get the error as #1064 - You have an error in your SQL syntax; ...
0
votes
1answer
11 views

apparmor prevents mysql from starting

I am trying to start mysql 5.5 service on ubuntu 14.04 but it fails. I noticed in dmesg the following error: I am trying to start mysql 5.5 service on ubuntu 14.04 but it fails. I noticed in dmesg ...
0
votes
2answers
32 views

column name as variable in sql statement

I have a MySQL database with a table that contains 4 columns, A, B, C, and D. I need to access those fields dynamically i.e., instead of saying Table.col_A , I need to say Table.x where x can have ...
0
votes
1answer
21 views

#1242 error my sql

I'm writing a query and msql return me #1242 error; here is the query, everything works fine but the issue comes from the subquery as it always give the same result instead of adapting this result ...
-2
votes
0answers
18 views

Find the database site address [on hold]

I mean, I want to get access to the Target database 2 I have access to a database from one of the sites and I found that the database is MariaDB I searched the internet in general, but I did not get ...
0
votes
0answers
18 views

Create dynamic mysql queries in php safely

I am building a system which is dynamically expandable, new tables are dynamically created in the system based on end user actions, due to this there is no way for me to be able to 'code in' all ...
0
votes
2answers
27 views

MySQL trigger/check on INSERT based on multiple factors (location, date and time)

I have a shift schedule planner for various warehouses. I'm trying to add multiple checks at DB level. If I'm using a composite key (by removing shift_id) to match staff, location,date time it's works ...
1
vote
1answer
46 views

Simplifying the “repetitive” code of a query involving UNION ALL, WHERE and LIMIT

How can I simplify the code of the query shown below? Requirements are always the same as I describe as follows: (select * from questions where points = 3 and type = 1 order by rand() asc limit 6) ...
1
vote
0answers
47 views

Optimize Data Warehouse Design

I am working on a project for school (lab, not homework) and we are asked to fix any design problems that we found in the data warehouse implementation. It seems like we could probably get rid of a ...
-1
votes
0answers
9 views

Need help with counting the number of games which has quantity greater than 10 in php from the database and printing them [on hold]

here is the code i have everything is good except i need to print the quantity of games i have which is greater > 10 in stock and printing it out just the like this picture i have here how can i add ...
2
votes
0answers
33 views

Double Entry Accounting Schema

Setting up a double entry accounting system for personal use and to help manage a really small business. Trying to put a few features that seem relevant now. Business Rules The logic, for those not ...
0
votes
0answers
6 views

Mysql takes up 100% all cpu

I made an application with PHP and MySQL. I use VPS with CPU 6 cores and 16GB RAM. After a while the application is very heavy and 100% cpu is used mysql and RAM is not used at all by mysql. Please ...
1
vote
1answer
37 views

How to copy selected table data from sql server to mysql on a daily basis? [on hold]

I am having a retail pos application with MSSQL database. Now I need to transfer selected table data from SQL server to MYSQL database on a daily basis like job. How to achive this?
0
votes
1answer
68 views

database and table design for billions of rows of data

basically i got 2 tables: header, details tables. CREATE TABLE `header` ( `ID` int(11) NOT NULL AUTO_INCREMENT, `RECORD_DATE` datetime DEFAULT NULL, `TICKER_ID` int(11) DEFAULT NULL, `...
0
votes
1answer
59 views

LEFT JOIN performance for WHERE on secondary table

I've been developing for years and through out the carrier I've been using only LEFT JOIN as it provides a more logical sense of mapping my one to many data. However recently I ran across a ...
1
vote
2answers
20 views

Replace Null values with values from the same column by matching values in a different column

I have a table with some null values in column "date": platform date id --------------------------- web 2018-10-10 1 mob 1 mob 1 web ...
0
votes
0answers
20 views

MySQL not honoring binlog_format setting

I have an application that listens to the MySQL 5.6/5.7 binary log. It requires binlog_format to be set to ROW. This is working perfectly on RDS but I can't get the setting to work in a docker ...
-1
votes
0answers
25 views

Pull the 2 min value - mysql

I need a calculated min column using datediff & min functions but not exactly the min value but the 2nd min value or date, below is the query for your reference: Select r.rfq_Id, datediff(min(q....
2
votes
2answers
39 views

MySQL Performance vs. Memory

We are running MySQL Ver 5.7.18 on an Amazon Lightsail Linux instance (2GHz Single Core CPU, 2GB RAM, 40GB SSD). Besides MySQL, we also run some Java services on this machine, which access the ...
0
votes
0answers
11 views

Backfilling data post migration

relatively inexperienced db admin here, please be kind :) I'm being asked to look into changing customer servers to write new data to a new MariaDB database and then export old data from the old ...
0
votes
0answers
14 views

MySQL slave ip address change

I want to replace my current mysql slave with a newer version of RedHat and MySQL. The replacement server has been running as a slave for about 2 months alongside the other slave server. It is now ...
0
votes
2answers
21 views

How can I select the next available row id of varchar type in MySQL?

I have a table with primary key 'Id' of varchar type. I want to select the next row when user clicks next record, in php, for example 1st row id= 'a' 2nd row id= 'b' 3rd row id='h' 4th row id='z' ...
1
vote
2answers
14 views

Remove default value from NOT NULL column

I have an existing NOT NULL column which has a default value. I want to remove the default value so that the user is forced to enter a value. mysql> SHOW CREATE TABLE items; CREATE TABLE `items` ( ...
0
votes
1answer
12 views

Error 1109 Unkown table 'buy_fruits' in field list

CREATE TABLE buy_fruits(id int not null auto_increment,primary key(id),fruit_name varchar(30),kg int(11),fruit_id int(11)); CREATE TABLE sold_fruits((id int not null auto_increment,primary key(id),...
0
votes
1answer
18 views

Mysql optimization edit my.cnf

Firstly, I am a beginner. I have a server, which has 32 GB RAM and 16 CPUs. I am working on MySQL optimization, but I have a problem. I am trying to set the InnoDB buffer pool instances = 8. But it'...
2
votes
2answers
28 views

Lost connection to server executing ALTER TABLE

I'm trying to execute an ALTER TABLE statement in MySQL 5.6. Every time I run the query, it's creating a file with a name like #sql2-503-918bf.ibd taking full table size. I don't have enough disk ...
0
votes
3answers
19 views

MYSQL 8.0 server not found in Workbench directory in Windows 10?

I have upgraded my workbench to 8.0CE and also removed the previous 5.7 MYSQL server as well as Workbench 6.3. This is the directory: C:\Program Files\MySQL\MySQL Workbench 8.0 CE Also when I typed ...
1
vote
1answer
14 views

innodb_log_file_size not upating even after changing my.cnf

Hello team this is my first post here, Server version: 10.0.31-MariaDB-0ubuntu0.16.04.2 Ubuntu 16.04 I am trying to import large db and half way I get the error to increase Size of InnoDB Redo Log ...
0
votes
0answers
31 views

Calculate daily avg/total user duration that spans over 2 days?

I have a table (lapsed_table) where it stores all a user's login and logout dates and the number of hours a user has spent on the platform. With that, I want to calculate: The daily average ...
-1
votes
0answers
16 views

How can i access to my xampp server database? [closed]

I had a XAMPP system set up, including a database I had created. Unfortunately, the Windows OS became corrupted. I installed a new operating system, and re-installed XAMPP server on that system. ...
0
votes
1answer
27 views

Error log flooding with aborted connections (Aurora 2.03 - MySQL 5.7.12)

Here's some contextual information: I have a multi-tenant, WordPress MU installation running on Elastic Beanstalk, scaled up to 2 instances minimum. These are connected to the RDS database and are ...
0
votes
1answer
24 views

Realtime Update MySQL on every Update occurred on PostgreSQL

I have a PostgreSQL for my accounting system and a MySQL database for my php website, i want to sync these two database for every update may occurred on PostgreSQL it also updates MySQL database, they ...
-2
votes
2answers
46 views

How to UPDATE along with INSERT INTO SELECT [on hold]

I added some entries from table2 to table1 as INSERT INTO table1 (title) SELECT title from table2 WHERE ... how can I UPDATE table2 for the SELECTed or INSERTed entries as UPDATE table2 SET status='...
1
vote
2answers
76 views

Find MySQL InnoDB index corruption

I have large MySQL InnoDB table on my localhost. In this table primary key is id, unique index is set on fields a, b, c. When I run query: SELECT * FROM mytable WHERE a = 1 AND b = 2 AND c = 3; the ...
1
vote
1answer
22 views

How to query closest date and display the specific data

guys, I have a problem displaying data from my query code. I want to display data from the closest date today. Below are my sample data and my output data that I want to display. Landing Area data. |...
0
votes
1answer
22 views

Can select read index page while modify this page in innodb?

When mysql innodb insert or update secondary index page , will at the same time this index page can searched by select ?