Questions tagged [mysql-5]
Generic MySQL version 5 tag. Please use a mysql-5.x tag instead. Also tag with mysql for search purposes.
178 questions
1
vote
2
answers
93
views
MySQL with subquery becomes slow
I have 3 MySQL tables: products, orders and users.
I want to show all ordered products where not all products are "pre-orders" or "discounts". Note: one user can have multiple ...
3
votes
1
answer
92
views
Structure of my database design?
I've been developing a C# Bus System app using SQL, and I need some advice. I don't know if asking on here is the right thing to do, but I essentially need someone to analyse the .SQL file I have, ...
- 133
-1
votes
1
answer
1k
views
Mysql Select all Rows after nth Position
I wish to fetch all rows from table after the nth Position as i given offset value
my table like
id | Value
1 | A |
2 | B |
3 | C |
4 | D |
5 | E |
...
N | R |
I wants the 3rd row Data to ...
- 1
2
votes
3
answers
3k
views
Table Creation Order
I have designed my database model with entities and they have relations along with normalization. Now I want to implement my design in my database engine. Before implementing I am puzzled with the ...
- 23
2
votes
0
answers
125
views
Signal handler on mysql connection break
I want to know if there is any signal that I can catch by which I can know that my mysql connection with the server has broken in c programming? I know I can check at query time whether my connection ...
- 21
2
votes
1
answer
7k
views
MySQL Master 5.5 - Slave 5.6 - unknown system variable 'binlog_checksum', Error_code: 1193
I see the below warning on the my slave server:
2016-11-18 22:48:03 12808 [Warning] Slave SQL: If a crash happens this configuration does not guarantee that the relay log info will be consistent, ...
- 189
7
votes
2
answers
3k
views
Will a mysql db import be interrupted if my ssh session times out?
I have a large-ish (several GB) mysql DB that I'm planning to import via
mysql -u root -p mydb < mydb.sql
in a SSH session.
What would happen if my SSH connection drops?
Is it safer to run ...
- 617
6
votes
3
answers
11k
views
MySQL secure random string generator
I am looking to generate a cryptographically secure string for password reset in MySQL. I know I could do this at the application level in PHP; but that requires the OpenSSL extension which many of my ...
- 701
3
votes
3
answers
13k
views
Computed/Generated columns in MySQL 5
How do I create a table with a formula column in MySQL version 5?
For example: I have a table named Product, which has 5 columns:
id int,
amount int,
sold_out int,
faulty int,
remain_amount is (...
- 31
5
votes
1
answer
2k
views
MySQL v5.1.73 - can the binlog_format betweem Master & slave be different?
We are in the process of testing MySQL replication before using it in production. The MySQL is being setup by Puppet in our case and I noticed that the binlog_format setting is different between ...
- 51
0
votes
1
answer
7k
views
Exclude entries from SELECT COUNT
I'm new to MySQL and I have a problem with one task.
I have a table like this:
+------------+------------+-------------+
| code | name | location |
+------------+------------+----------...
1
vote
2
answers
13k
views
Table Design for Student Attendance hourly for each day
I have created a table (attendance_details) for storing every day attendance of Students in MySQL. Need to capture attendance for hourly (1-8hrs) every day. I have the student details in separate ...
- 99
0
votes
1
answer
614
views
MySQL UTF-8 Encoding Problem
I have two MySQL databases that are for Wordpress websites. One db is for a live version of a site, the second is a copy of the original that is used in conjunction with a copy of the original site. ...
- 101
0
votes
1
answer
115
views
Trying to avoid bad MySQL database design - can't figure out how to avoid too many columns
I am pretty much a beginner having only created a few small php web applications relying on a MySQL database. I am now working on a somewhat larger project. I am in the planning stages of setting up ...
2
votes
2
answers
884
views
SELECT WHERE CASE
I have the following table (MySQL):
--------------------------------------------------------
| id |product_id| store_id | stock_id | priority |
--------------------------------------------------...
- 21
4
votes
1
answer
427
views
Find clients who bought more than 1 million units in 3 consecutive months
I want to find the client's code (cCode), who bought more than 1 million units, in three consecutive months.
Here is my data:
gDate cCode buy
2014-10-22 15108164 1653410
2014-12-22 ...
- 51
1
vote
1
answer
78
views
How to properly aggregate?
I have a table for logs with over 6 million records. I want a query that shows me all the occurrences where the src goes to the same destination/port.
I tried this:
SELECT src, dst, dstport, COUNT(...
- 21
3
votes
1
answer
2k
views
Is there a way to run mysqldumpslow against slow queries logged to a table?
AWS RDS by default logs slow queries to a database table.
Is there a way to run mysqldumpslow against that table, without exporting it to a file first? Or is there a better way to get a report of ...
- 1,232
2
votes
3
answers
2k
views
Question on having a MySQL 5.1 replicate from a MySQL 5.6 DB
I have a question in regards of what potential issues that might happen with a MySQL 5.1 DB that replicates from a MySQL 5.6 DB.
I know the risks involved based on resources such as http://dev....
- 281
0
votes
1
answer
62
views
Mysql query performance
We have a database table and we found a weird query from old code. We can't really explain it, so we decided to analyze why it could have been done that way. The weird thing is that the results are ...
- 131
0
votes
0
answers
274
views
MySQL stops unexpectedly on CentOS 6.5 with WHM/Cpanel after migrating server with transfer tool [duplicate]
I recently moved my entire websites from a dedicated CentOS5 server to a CentOS 6.5 on Amazon EC2 with the same cPanel/WHM version. WHM 11.44.1 (build 19) However after angry calls from clients I ...
- 263
0
votes
4
answers
115
views
Why does this query take 32 sec to execute?
I keep my history records in one table and my pertinent data in another. I want to grab only the files from myfiles that currently have a given status recorded in statuses (ie, the latest status is ...
- 131
7
votes
1
answer
14k
views
Any known issues upgrading from MySQL 5.1.73 to 5.6.21?
I am planning my MySQL DB ver5.1.73 running on a CentOS 6.5 machine to MySQL 5.6.21.
I understand the basic procedure for this based on what I have read on StackOverflow, but I was curious if anyone ...
- 281
1
vote
2
answers
753
views
Fourth inner join to the same table increases query duration significantly
As soon as I add a fourth inner join to a specific table, the query duration increases significantly: sub 1sec to 45sec.
The table in question is deployments d4.
The SQL Query:
select
a.id as ...
- 169
3
votes
2
answers
7k
views
How to check/change MySQL named pipe settings?
I'm having a hard time finding anything on this because I don't know what to call it. But when you install MySQL it asks you if you want to allow connections to the database from other machines or ...
- 133
0
votes
3
answers
3k
views
How to rewrite subquery to return only 1 row
I need to somehow check if query result is NULL, MySQL returns error Subquery returns more than 1 row. How should I rewrite it in order to work correctly please?
SELECT DISTINCT user.id, user....
- 175
0
votes
1
answer
3k
views
Importing Data with Single .ibd file
I have one ".ibd" file with me. I have configured my local Database server with FILE_PER_TABLE option. i have two question here.
When i was trying to restore/import the IBD file, the engine expect ...
- 123
0
votes
1
answer
133
views
How to do Mysql migration from 5.0 (320bit windows) to 5.6 (64 bit windows)?
I want migrate My Mysql 5.0 windows 32 bit to Mysql 5.6 Windows 64 bit?
Please suggest me the best way to do migration, i have around 10gb of data present in Mysql 5.0.
- 31
7
votes
2
answers
31k
views
query execution time in mysql
I have run the query in mysql which consists of approximately 33million records when i have executed the query
select population1 < 200000 from city;
it shows like this after execution
34459393 ...
- 173
0
votes
1
answer
43
views
Omitting [INTO] in the INSERT ... SELECT statement
Does the INTO keyword only makes the query more readable, or does it bring another benefits as well? The queries beneath both seem to run equivalently .
insert ignore
tbl (type, details)
values ('1', ...
- 111
0
votes
2
answers
4k
views
MySQL Community Edition Capacity
I am using MySQL Community Edition on Amazon RDS service. It is a very large database, 2/3 tables has about 10 millions records and 4-5 tables has 7-8 millions records.
Questions
How much capacity ...
- 263
1
vote
1
answer
2k
views
How to include a semicolon (";") in a MySQL pager command
I've just discovered the pager command in the MySQL command-line client, and I'm having a tonne of fun. This blog post is great:
http://www.mysqlperformanceblog.com/2008/11/21/how-to-calculate-a-good-...
- 123
30
votes
3
answers
41k
views
Why query_cache_type is disabled by default start from MySQL 5.6?
We've upgraded to MySQL 5.6 and start seeing the loading of db server increased significantly, and finally found out the query_cache_type is defaulted to off start from 5.6.
We enabled it again and ...
- 549
2
votes
2
answers
712
views
Keywords - column or separate table
I am building a website on MySQL with php, and I have 400+ products in a table. I have three columns with comma separated values, each has 1-3 values separated by commas in each row.
I was wondering ...
- 123
0
votes
2
answers
5k
views
How to select the latest record of each hour in a day?
I want to select the latest record of each hour in a given date, based on the datetime column 'reading_on' in my table. What is the best way to do it? I already tried group by HOUR(reading_on) with ...
- 101
2
votes
4
answers
11k
views
MySQL: How to avoid case sensitivity in SQL queries when migrate from Windows to Linux?
I have migrated my Database from Windows to Linux server. In many places, query has been written without focusing their cases (uppercase / lowercase). So, these queries are not getting executed and ...
- 2,061
9
votes
2
answers
8k
views
Why is MySQL not using the index with the higher cardinality?
I have an index called source with cardinality of 1122 and a second index called state with cardinality of 22. MySQL keeps using the state index when when query SELECT C1 FROM tbl WHERE source = 'x' ...
- 197
112
votes
3
answers
392k
views
ALTER TABLE - Rename a column
This is driving me nuts!
I want to rename a column from read-more to read_more in my blog table
I tried all this:
ALTER TABLE blog RENAME COLUMN read-more to read_more;
ALTER TABLE blog CHANGE ...
- 1,123
2
votes
3
answers
2k
views
How to update cust_id from Customer table to order table Cust_id field while inserting customer name using trigger
I have two tables 1. Customer_details and 2. Order_details.
1.Customer_details table -->fields--> Cust_Id(Primary Key) and Customer_Name,
2.Order_details table --> fields-- Ord_Id(Primary ...
- 99
3
votes
1
answer
24k
views
MySQL rebuild after Corrupt ibdata1
I have a MySQL server that serves a couple of databases including a Magento e-commerce store.
We have been suffering with corruption in the ibdata1 file for a while, but it has now reached a critical ...
- 31
3
votes
2
answers
4k
views
Why does MySQL fail to use an index effectively when LIMIT offset is large?
I do not understand when querying 5 rows from a table of 500,000 rows, the following query takes 0.00sec:
SELECT id, username FROM Users FORCE INDEX (PRIMARY) ORDER BY id LIMIT 20,5;
But querying ...
4
votes
1
answer
136
views
Find problematic MySQL users and databases
We have a MySQL 5.1 server that is an "open" service: anyone on campus can request a database. As such, we have several thousand databases on this one server.
As you might imagine, sometimes one user'...
- 375
0
votes
1
answer
92
views
Mysql query in Database
How to get all the tables from database with all values in tables using single query..
I tried using
$sql = "SHOW TABLES FROM $dbname";
$result = mysql_query($sql);
$arrayCount = 0
while ($row = ...
- 101
13
votes
4
answers
52k
views
Can't remote access MySQL server running on Mac OS X
I have a MySQL server running on my work computer, which runs Mac OS X Maverick. I want to access it from home computer, which also has Mac OS Maverick.
From terminal, if in 1 tab, I ssh into my ...
- 231
0
votes
2
answers
3k
views
Getting table definitions on the command line in MySQL [closed]
I am new to MySQL. I am trying to get table definitions from the database server (4.0.26).
I tried below steps to get information, running cmd as an administrator:
c:\> cd mysql\bin\
c:\mysql\bin&...
- 291
45
votes
2
answers
115k
views
InnoDB: Error: Table "mysql"."innodb_table_stats" not found after upgrade to mysql 5.6
I upgraded to mysql 5.6 from 5.5, and now my logs are littered with such messages on startup
I found a possible solution here, but it does not seem official.
http://forums.mysql.com/read.php?22,...
- 1,407
3
votes
1
answer
4k
views
mysql 5.1 -- unknown variable 'basedir=c:/mysql/'
I recently changed the default port(3306) to another for mysql.
I did it by changing the my.ini file and did restart of the server.
No other changes were made whatsoever.
When connecting using a GUI ...
- 33
1
vote
2
answers
14k
views
How to force higher values of open_files_limit or table_open_cache?
I'm having a frustrating problem where setting my.cnf to values higher than I currently have them does not 'take'.
This is MySQL 5.6.14 on MacOS X 10.8.3
I'm trying to address dropped connections ...
- 11
3
votes
1
answer
27k
views
Selecting multiple columns from multiple rows in one column (subquery?)
I'm working with MySQL 5.1.68.
I have a situation where I'd like to select rows from another table into a column.
I'd like to output this:
id # name # citizen_name (multiple citizen names)
1 # The ...
- 125
1
vote
1
answer
2k
views
Two different MYSQl instances on same machine?
I have MySQL 5.6 installed (Linux).
/var/lib/mysql
/etc/my.cnf
I have a cold backup that I am recovering:
/var/lib/mysql_backup
/etc/mybackup.cnf
In April I was able to get the backup started and ...
- 161