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
1answer
5 views
MYSQL: used variable on IN condition
I had this query below inside a procedure which I would like to change for optimization.
SELECT * FROM request WHERE facility_id IN (SELECT facility_id FROM facility);
What I would like to do was ...
0
votes
0answers
10 views
What's the best way to store and search through many product specifications
Let's say I have a lot of products and all of those products have a lot of specifications. These specifications can change at any time (e.g. "colour" could suddenly be available for new products) and ...
-1
votes
0answers
16 views
How to restore database safely?
So here is the thing. I took a MySQL backup. Then I performed some actions. I then imported backup. But then the number of rows in one table (or maybe more) doubled.
How can I safely restore my ...
2
votes
1answer
16 views
Average value of a row in MySQL
I have a row which primary key value is 4 and there are another 8 values.
Those are 45 78 89 54 98 14 19 70. I want to select the average value, ie 58.37 from this row. How do I do this ?
The ...
2
votes
1answer
21 views
Highest value of a row in MySQL
I searched and found many results using "Grep highest value of a row in MySQL" but I don't understand the results.
I have a row which primary key value is 4 and there are another 8 values.
Those are ...
0
votes
1answer
13 views
Global Read Lock for Snapshot of MySQL Data
I have a database that is entirely comprised of MyISAM tables(yes, I did not create it). I need to create a slave and start replication so we could point read-only queries there. Here is my idea: ...
1
vote
1answer
13 views
Indirect constraints (foreign keys amongst three/four tables) [duplicate]
I have the next layout of tables:
table COMPANY:
- primary key pk_company
table USERS:
- primary key pk_users,
- foreign key (fk_company) references COMPANY (pk_company)
table GROUPS: <br>
- ...
0
votes
1answer
27 views
mysql user host field in slow query log
Mysql shows the following value in the slow query log
# User@Host: user[user] @ [127.0.0.1]
I'm assuming one if the "user" entries is a proxied login. An admittedly very quick glance of the code ...
2
votes
1answer
59 views
Is there any reason not to use foreign keys in MySQL?
I've never really used foreign keys before but I am building a project where they could come in handy. I have looked at some other large software such as some forum systems and they don't use foreign ...
1
vote
1answer
11 views
Do queries called from within a transaction but found within another method act the same?
Imagine the following code:
class foo {
public function bar() {
// Start the transaction
$transaction = $this->db->beginTransaction();
try {
...
-1
votes
0answers
29 views
Change MYSQL database to REDIS for permance in a loop of dates with math calculations
I work in a start-up and I'm facing a serious database performance problem in my system.
I have one table to control units, which is updated many times a day. There is an operation that calculates ...
0
votes
0answers
32 views
Slow mysql query full text search tag database
In the database are ~700k images. Each image has its own tag(s). I want to be able to search images by the tags with a full text search query. The query does exactly what I want but it's very slow. ...
3
votes
3answers
121 views
How do I show the number of tables in all databases?
I am looking for a query that return all the databases in my MySQL database (i.e. like SHOW DATABASES; but includes the number of tables in the tabular result.)
Something that outputs:
...
2
votes
1answer
34 views
what happens when I try to insert data into mysql while table is locked?
Simple question but somehow impossible to find a clear answer:
What happens when I try to insert data into mysql while the table is locked?
1) INSERT query fails
2) INSERT query waits until lock is ...
0
votes
0answers
11 views
Is there any “integration between the tables” besides table structure and data? [on hold]
I was trying to copy data from one of the tables in a database to a new empty table in another databse. Both databases are from a same application, but the new one has higher version. So the two ...
-2
votes
0answers
25 views
How to draw an entity relationship diagram? [on hold]
I am not sure how to draw ERD for an election problem with entities ridings, parties, candidates and # of votes:
Constraint: A candidate can only run for election in 1 riding, and a party can only ...
0
votes
2answers
23 views
Connecting to a new RDS instance from an EC2 instance
I'm trying to use MediaWiki and WP to connect to two MySQL databases.
The databases are working properly and so are the forms but
when I try to establish a connection to the database with MediaWiki ...
0
votes
0answers
22 views
Loop gets terminated in case of many SQL Insert Commands
I am executing SQL Insert commands inside a loop that runs for 1000+ iterations. The loop gets aborted without any error/exception in between after executing 700+ inserts.
If I comment the DB ...
0
votes
0answers
24 views
How to compact mysql database size?
My folder mysql/data is huge. I have dropped some databases that have a huge amount of records, removed some logs tables but.. mysql/data size remains unchanged.
What is the best way to compact it? ...
0
votes
0answers
20 views
How to optimize database creation for very few threads [on hold]
We have integration tests running on a MySQL on our CI server. To ensure consistent environment, we need to empty and refill the database after each test group. We have set up an 8-core virtual ...
0
votes
2answers
24 views
How can I defragment tables in MySQL?
I have a database named COUNTY. COUNTY has a fragmented table GEO.
I executed OPTIMIZE TABLE GEO against the GEO table, but this returned the error
"Table does not support optimize, doing ...
0
votes
0answers
21 views
Insert and select same table
When I run the sql gives this error.
Table 'question' is specified twice, both as a target for 'INSERT' and as a separate source for data.
Someone help me.
$sql = "INSERT INTO question (id ...
4
votes
1answer
47 views
Subtract time difference between two columns in a table
Problem: How can I subtract the TIMEDIFF between two sensors (sensor 1 and sensor 2 in the example below) and return the the value in mili seconds?
How do I get with the resolution of milliseconds?
...
1
vote
1answer
63 views
What is the optimum way to store, update, and analyze integer data for 100+ attributes?
I need to store the count of all individual characters (A-Z, a-z, 0-9, spaces, punctuation marks, all symbols...) that occur in different texts which will be frequently changing.
I will need to ...
0
votes
2answers
39 views
Lock mysql table to prevent select and insert at the same time
I know this question has been asked earlier and I have also read the documentation. But I guess I'm stupid not understand.
I have a code that inserts a row and the row should be unique in several ...
1
vote
0answers
23 views
Mariadb alongside MySQL Can't start the MariaDB
I'm running MySQL on ubuntu 14.04 and it works great.
For development and testing before switching between the two I want to install a separate Mariadb server.
I followed this (old) tutorial: ...
0
votes
0answers
15 views
MySQL Multi-source - Single Slave Setup. Adding Unique Master ID on Master's tables definition
this is a design question.
I understand that Replication, is like mirroring. So this would mean, whats on the master, should be mirrored on the slave. (Is this always the case, like strictly same ...
1
vote
0answers
11 views
What does 'locks rec but not gap waiting' in MySQL latest detected deadlock report?
About the meaning of locks rec but not gap waitingin TRANSACTION(1), Which one is correct?
Already granted gap lock, waiting clustered index X lock?
Or, already granted clustered index X lock, ...
2
votes
3answers
59 views
Relation Subtraction wrong result
I have 2 queries:
Query1:
SELECT DISTINCT maker FROM Product WHERE type='laptop'
Gives result {A, B, E, F, G}
and
Query2:
SELECT DISTINCT maker FROM Product WHERE type='pc'
Gives result {A, ...
0
votes
0answers
5 views
MySQL Connector C++ tinyint data type [migrated]
can I use the setInt method for a preparedStatement in the c++ connector, to input data when a tinyint type is expected? tinyint is only 1 byte in size, I'm wondering if the int will just be ...
1
vote
1answer
30 views
MySQL UPDATE query stuck on “Sending Data” State
I am having difficulty getting an UPDATE query to successfully execute, due to the process getting stuck on "Sending Data" state.
I feel this differs from similar questions about the "Sending Data" ...
1
vote
1answer
9 views
MySQL Drive Storage Allocation
In our production servers for SQL Server, we usually split our drives by Software, Logs, Data, and Backups. I've been assigned to help with a MySQL installation (On Windows Server) and I'm not as ...
1
vote
2answers
58 views
How to find a word in SQL? [on hold]
Lets say you are filtering on rows in a column that contains the word Engineer in a phrase. How can I exclude words like 'Engineering' or 'Engineered' or 'Engineers' when I wouldn't know all the ...
3
votes
1answer
28 views
mysqldump: How to include index type?
When I dump the schema of my database
mysqldump --no-data my_db > schema.sql
I get lines like this
KEY `index_tbl_on_col` (`col`)
but I want lines like this
KEY `index_tbl_on_col` (`col`) ...
2
votes
0answers
45 views
How to start MySQL after the year 2038?
I am using MySQL 5.5. For some reason, I need to change the date of my system to after 2038. Unfortunately, the MySQL service won't start after the year 2038:
root@sd-84962:~# date
Sat Sep 24 ...
0
votes
1answer
27 views
Restore MySQL Database with only .frm files
One of our servers (running Ubuntu 12.04) recently crashed during a power outage, and MySQL refused to start back up. After a lot of trial and error, I decided to completely reinstall it. Since I ...
0
votes
0answers
35 views
MySQL - SELECT Columns from results of another Query [duplicate]
I'm trying to use a dynamic range of columns fetched from another query, specifically I want the results of this query:
SELECT COLUMN_NAME as DYNAMIC_COLUMN FROM information_schema.columns
WHERE ...
0
votes
0answers
20 views
Restore a TokuDB table from main and key files only. (Status file missing)
I have a TokuDB table that for some reason has a missing _status.tokudb file.
I am not yet sure, whether the file is missing due to a TokuDB crash or not.
Question is:
Is there a way to recover ...
-3
votes
0answers
23 views
Call to undefined method mysqli_stmt::get_result() [on hold]
I am getting error during fetching email & password:
Call to undefined method mysqli_stmt::get_result()
I am using 000webhost.com
PHP version 5.2.*
MySQL ver. 5.1
How to solve this ...
0
votes
0answers
36 views
How to intersect two query results?
I want to intersect two query results. But since my database is MySQL, I cannot use INTERSECT. How can I work around it?
This is the INTERSECT query I have that wouldn't work in MySQL:
(
SELECT ...
0
votes
0answers
22 views
Create user to view part of data only
I would like to be able to create new users so that they can view part of the data in a table I defined without having to create a new table and pull the data for them every time. Let's say I need to ...
1
vote
0answers
45 views
MySQL database column to Store Phone numberss
What is the best approach to store Phone Numbers (Mobile Number and Office Number) in MySQL table?
Users may have MobileNumber (CountryCode and MobileNumber) and Office Number (CountryCode, ...
0
votes
0answers
40 views
Inserting unrelated data while a transaction is occuring
I'm thinking about implementing transactions to my app to help with consistency issues. I'm using MySQL.
I'm wondering what might happen if while inserting several rows as a transaction, the server ...
1
vote
1answer
28 views
Making a very simple product hierarchy in MySQL
I'm importing a list of products from an XML file into a XMLimport landing table in MySQL.
A check takes place against the ActiveProducts table to see if there are any new products in XMLimport ...
0
votes
0answers
20 views
Which hosting technology is suitable for large data with limited index
I have a scenario where I have to host very large amount of data in database type solution. The amount of data is very very large. When I try to host it in mysql database the size increases as mysql ...
1
vote
2answers
33 views
How do you achieve a physically sorted table file organization?
Do I just declare an index on a column in the schema like ?
ALTER TABLE `table` ADD INDEX `index_name` (`colDate`)
I want the table records to be physically sorted not just when doing an ORDER BY ...
0
votes
1answer
43 views
Updating rows in tables concurrently using Multithreading in Java
I have an excel file with 50000 rows and I'm updating a MySQL table using hibernate by uploading that excel file from a Java Program. It is taking a huge time to update all the rows. Can it be ...
-1
votes
0answers
22 views
Select most popular categories based on records assigned to them [migrated]
Problem: I want to know what are the most popular categories based on their records which are assigned to each category
Database structure:
categories.id
categories.title
records.id
...
-7
votes
0answers
21 views
How can i connect to database server using PHP [closed]
I m making an database management tool in which i want to connect to database server using PHP how this can be done?
-1
votes
0answers
21 views
Why is my procedure for inserting into a table creating three rows instead of one? [closed]
So I have this procedure for inserting a row into a table that is returning three rows instead of one and I can't figure out why.
Here's the procedure:
delimiter //
create procedure ...
