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.

learn more… | top users | synonyms

0
votes
1answer
31 views

MySQL BETWEEN command - performance (order)

I want to create a table which will contain approx 20,000 records of three fields each. Two of the fields will contain numeric values. I want to be able to issue a SELECT BETWEEN command. One record ...
0
votes
1answer
19 views

sql update query for two connected tables [closed]

UPDATE rooms set r.vacant=true FROM reservation s,rooms r WHERE r.room_no = s.room_no AND s.cus_id=1 I created above sql queryto update room status.I want to set vacant as true when customer_id ...
0
votes
2answers
49 views

Insert the same time, it is possible to happen?

Good evening. I have a doubt about inclusion in the database. A common example is a user account where the user names must be unique. In the following logic: User enters data into a form. Data are ...
0
votes
0answers
31 views

How to Know Why Foreign Key Constraint Fails in MySQL

I'm using Laravel's Query Builder to put together some tables. I am trying to set up a foreign key, but it's failing with the following error: [PDOException] SQLSTATE[HY000]: General error: 1215 ...
0
votes
2answers
41 views

Searching for a keyword across multiple columns

I have a set of names of schools stored in each row in my schools table. the same table has four different category columns which store the names of the subjects they offer. I want to enable ...
0
votes
0answers
24 views

Why the “use database” command does not seem to work?

I'm trying to clear my eximstats database, which I've done many times before. This time after I type use eximstats; the cursor just drops down and I can type again. I don't get the "Database changed" ...
1
vote
1answer
31 views

Fast DELETE with double subquery on big table for dupe remover

I have a table with 9million rows. At some point in time i changed unique key from one column to two columns(for partitioning). After this i created duplicates of my first column which is unwanted, ...
1
vote
3answers
96 views

Mysql - How number of columns effect the database performance

I have a table to store social media information for users; I have 42 columns to store and will have 100000 entries per day to store. I am planning to have MyIsam engine for it and will use horizontal ...
0
votes
1answer
35 views

What is the difference between CREATE INDEX and ALTER TABLE ADD INDEX? [duplicate]

I have searched the web but I have failed to find a conclusive answer for this question. Is there any difference between CREATE INDEX and ALTER TABLE ADD INDEX? Are there any problems that may ...
0
votes
1answer
32 views

Why does this SQL work on one version of MySql-Server but not another?

I'm trying to run the following very simple SQL command on two different MySQL databases: CREATE TABLE mytable ( created DATETIME(6) NOT NULL, updated DATETIME(6) NOT NULL, my_id ...
1
vote
1answer
40 views

Next quarter without using Quarter function

I have a query which runs on particular dates. Whenever the query runs, it has to pick up the next quarter dates. 1st quarter Jan-March 2nd quarter April-June 3rd quarter July-September 4th quarter ...
1
vote
1answer
22 views

Conditional query between tables

I'm trying to fill a table with the results of a query, however, the results of said query depends on the result of other queries and then it ends up having several thousand selects making the process ...
0
votes
1answer
32 views

How to join values from multiple rows in a single column? [duplicate]

I'm working on a project to find the association rule of items for Market dataset. I have a huge database that contains multi-tables. The output I got from this query select ...
0
votes
0answers
26 views

MYSQL - deleted rows without permission

I have a problem involving MySQL. I had about 3000 records in a table and today most of them disappeared. Today I have 1000 records left. I didn't do anything. Is there an explanation? A MySQL error ...
0
votes
0answers
18 views

MySQL IF Statement works for PIVOT scenario… Until I GROUP BY

I've been trying for days to figure out how to pivot data to give me this appearance: I've written the following code to generate the data requested and it works. But When I use GROUP BY to ...
0
votes
1answer
27 views

SQL Syntax Error - Unsure if it is empty value or backslash

I am getting a syntax error when the following query is being run:- INSERT INTO mw_reward_point_order VALUES(9477,0,0,144,,'100/1') I originally thought it was because there was an empty value ...
0
votes
0answers
8 views

Display dynamic table of checkbox in 3 columns in php [migrated]

I have a database table of subjects. I have a web page that displays the list of subjects in 3 columns so that a user can select subjects using a checkbox. The challenge I have is it takes a long time ...
0
votes
0answers
30 views

Visual Explain Not working in MySQL Workbench

Is there some trick to getting the Visual Explain feature to work in MySQL Workbench? I am running version 6.3.6 on Windows 7. I've tried simple queries with just one join. I've tried it with complex ...
0
votes
1answer
32 views

Which isolation level do I need?

I've read a lot of articles about Isolation levels, but I'm confused which one to choose for my MySQL 5.6 database. This is my situation: Let's say I have two group of users: Group A ...
0
votes
0answers
24 views

MySQL user variables vs repeated parameter literals. - performance

I am currently designing a large query which uses a number of parameters numerous times throughout the query and subqueries. After checking out some posts on multiple repeated params, I decided to ...
3
votes
2answers
61 views

Slow MySQL query

I have 3 tables: Name Engine Rows Data Size Index Size Total Size product MyISAM 11.06M 859.59M 861.20M 1.68G product_manufacturer MyISAM 3.09K ...
0
votes
2answers
49 views

How to get data everyday's eight AM.?

MySQL database, I have a table which contains some column. First column is datatime type, like "2016-03-01 08:00:00", "2016-03-02 08:00:00". What should i do to get everyday's 8 AM. from this table? ...
1
vote
1answer
28 views

MySQL on idle low-end server unresponsive

I have a small virtual Server: 2 Cores, 2 GB RAM, Windows Server 2012 R2, Plesk, MySQL 5.6 (my.ini see below). Pagefile is disabled. Taskmanager says 1,3 GB RAM used. Only a single Wordpress ...
1
vote
2answers
63 views

Select the minimum of a calculated distance value without sorting

I have a query dealing with geo distances. The query is super fast, returning in roughly .1175 seconds on my 2.24 million row table. However, I only need the lowest distance, and using the built-in ...
0
votes
0answers
24 views

Is there a way to calculate the running min of a calculated value [duplicate]

I have a query dealing with geo distances. The query is super fast, roughly 0.1175 seconds on 2.24 million rows; however, I only need the shortest distance, and using the built in order by is way too ...
0
votes
1answer
25 views

MySQL Using Group By, Need to add fields where answer is reliant on another fields data

I've asked a the same question before, but didn't get an answer and I believe I made the question too confusing to understand. BEFORE CODE: SELECT inventory.WHSE AS `Warehouse`, inventory.`CODE` AS ...
1
vote
1answer
25 views

Does a read-only MySQL slave need log-bin in addition to relay-bin logs?

I'm running out of disk space on a newly initiated MySQL slave server. The bulk of disk space is in /var/log/mysql where I see both mysql-bin.000XXX and mysql-relay-bin.000XXX logs, each 1.1G. Does a ...
0
votes
1answer
180 views

MySQL code to show specific rows based on another…?

I'm having a hard time figuring out how to do this, and I can't explain it well. Since I don’t know how to explain it I can’t seem to locate an answer online by simple research. So, I have to draw it ...
0
votes
1answer
15 views

Issues regarding phpMyAdmin - character set

I'm trying to migrate a Wordpress site from one server to another. Everything is going fine apart from the database migration. I have my database 'A', and using phpMyAdmin I am attempting to export ...
0
votes
2answers
53 views

MySQL -Query with Union doesnt use index

Long story short, the following query runs immediately: SELECT COUNT(*) FROM indicators.fileso WHERE sha256 IN ( SELECT a.sha256 FROM temp.ananifilesinfo a JOIN indicators.filesi fi ON ...
0
votes
0answers
34 views

Registration Schema Implementation

I'm building an online app that is going to handle reservations. Individuals making reservations can make one for a single day or multiple days. I have come up with two schema and cannot see much of a ...
0
votes
1answer
28 views

MySQL Explain gives different result for same query, query performance changes

I've been having some performance issues on my production server. The main issue, I think is the table pricelist_entries, which has approximately 6 million rows. Basically its holds all of the ...
0
votes
1answer
37 views

mysql Query Columns to Rows

unitid | row1 | row2 | row3 | row4 | row5 | row6 ----------------------------------------------------------------- 200 | sample1 |sample2 | sample3| sample4 | sample5 | sample6 ...
0
votes
0answers
26 views

Getting the most recent date in a table with good performance?

I have been monitoring my server and have noticed a few slow queries. One is getting the most recent date in a table with about 2691225 records. I am using the following: SELECT dt FROM dly WHERE dt ...
0
votes
1answer
30 views

How to store Alternate Names for Places or City in Table

What is best way to store Alternate Names of the Places (City or Locality) in MySQL database columns which user will be searching? Examples: In India user may search for "Chennai" or "Madras" and ...
1
vote
1answer
33 views

Galera MariaDB not joining with IST

Until now, I have a single MariaDB 10.1 node running as Galera Cluster. So, now I want to join the next one. systemctl start mysql initiates the snapshot state transfer (which takes about 10 hours ...
1
vote
0answers
26 views

Missing DDL and table

I encounter the following errors after reinstalling MySQL Server. These seem to affect only one out of the several tables in the schema. The following errors are generated from MySQL Workbench. When ...
3
votes
1answer
39 views

backup/replication over inconsistent network

I need to setup a MySQL database server for a small number of local (write) users (~6) in a remote area with somewhat irregular internet access via WiMAX. Outages are not at predictable times or of ...
0
votes
1answer
34 views

How to put the below data in 3NF?

I need to put the data from the diagram in 3NF (third normal form)
1
vote
3answers
54 views

Display two digits after decimal point in SQL from average value

This is my code SELECT AVG(`page_rate` ) FROM `pages` INNER JOIN `languages` ON `pages`.`lang_id`=`languages`.`id` WHERE `abbr` IN ("EN-UK", "EN-US", "EN-AU") How can I display only 2 digits ...
0
votes
0answers
28 views

Restore database tables from mysql binary files

I upgraded my xampp stack and in order to do it I had to delete my xampp folder. Before I did this I copied my database files in the data folder to a flash drive, but I only copied the database folder ...
0
votes
1answer
37 views

mysql: unknown variable 'table_definition_cache=400'

I added lines to ~/.my.cnf which worked fine for weeks. Now I receive complaints when I run myqsl CLI. System specs: MAC OS X - El Capitan 10.11.13 Mysql Version: mysql Ver 14.14 Distrib ...
2
votes
1answer
9 views

Inserting Items Bought by the Customer into the database

I'm having a hard time conceptualizing on how to store the items purchased by the customer into the database while taking note of who was in charge(the cashier in charge of the customer's products) of ...
3
votes
3answers
267 views

Listing rows with a WHERE condition

I am struggling with a SQL query that lists hosts that are offline. Problem The problem occurs when I need to list hosts that don't have any ports open (status = 0), so looking at the table at the ...
0
votes
1answer
21 views

How to get when MySQL instance went down last

Is it possible to determine when a MySQL instance went down last? Is there a log file or literally anything at all that logs the server status in MySQL? I have trolled through countless articles ...
3
votes
1answer
27 views

how to “revoke” a “GRANT OPTION” from an mysql user/database?

i have a mysql installation with lots of users and grants. But one grant is unique, since its supposed to be a simple DB user and usually does not need GRANT privileges. However someone added it when ...
0
votes
1answer
19 views

Mysql locking - How to acquire a lock in the DB for each userId

Hi I have a J2EE Server(clustered) that executes requests from multiple users. I would like that each request of a given user will be handled like so: { acquireLockForUser(userId); // i.e. insert ...
0
votes
1answer
34 views

Create Domain Fails to work in MySQL [closed]

I am trying to finish my homework. In almost all of the questions, I have to use "Create Domain". It seems like MySQL does not support that. What can I use instead?
0
votes
1answer
21 views

complex mysql constraints over foreign keys

It seems rational to me to stop users or bad codes from inserting invalid data, but I don't remember to see this anywhere! Consider the following tables How I can make sure an order is always ...
2
votes
1answer
36 views

Stored procedure unable to compare

Hi I tried implementing the below stored procedure which always takes my if condition as true and implements . CREATE DEFINER=`root`@`localhost` PROCEDURE `InsertOpen`(in uid varchar(11),in S_ID ...