Questions tagged [mysql-5]

Generic MySQL version 5 tag. Please use a mysql-5.x tag instead. Also tag with mysql for search purposes.

Filter by
Sorted by
Tagged with
1 vote
2 answers
90 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 ...
screaming SiLENCE's user avatar
3 votes
1 answer
86 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, ...
Suzan Aydın's user avatar
-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 ...
sivag's user avatar
  • 1
2 votes
3 answers
2k 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 ...
Shaker Kamal 's user avatar
2 votes
0 answers
117 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 ...
soni's user avatar
  • 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, ...
Berlin's user avatar
  • 179
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 ...
Mike B's user avatar
  • 607
6 votes
3 answers
10k 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 ...
Chris Muench's user avatar
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 (...
Amin's user avatar
  • 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 ...
Sam's user avatar
  • 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 | +------------+------------+----------...
xxx223322's user avatar
1 vote
2 answers
12k 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 ...
Acube's user avatar
  • 99
0 votes
1 answer
585 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. ...
la1ch3's user avatar
  • 101
0 votes
1 answer
114 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 ...
Jeff Michael's user avatar
2 votes
2 answers
883 views

SELECT WHERE CASE

I have the following table (MySQL): -------------------------------------------------------- | id |product_id| store_id | stock_id | priority | --------------------------------------------------...
user3668514's user avatar
4 votes
1 answer
416 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 ...
Milad.K's user avatar
  • 51
1 vote
1 answer
76 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(...
Rob D's user avatar
  • 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 ...
chris's user avatar
  • 1,222
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....
user1805458's user avatar
0 votes
1 answer
59 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 ...
Zsolt János's user avatar
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 ...
Neo's user avatar
  • 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 ...
Jason's user avatar
  • 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 ...
user1805458's user avatar
1 vote
2 answers
749 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 ...
TechFanDan's user avatar
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 ...
developerwjk's user avatar
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....
Jakub Turcovsky's user avatar
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 ...
Deepak M's user avatar
  • 123
0 votes
1 answer
130 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.
user37651's user avatar
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 ...
user41498's user avatar
  • 173
0 votes
1 answer
41 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', ...
mechanicious's user avatar
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 ...
Manish Sapkal's user avatar
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-...
Kaoru's user avatar
  • 123
30 votes
3 answers
40k 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 ...
Yoga's user avatar
  • 549
2 votes
2 answers
682 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 ...
Yekhezkel Yovel's user avatar
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 ...
loganathan's user avatar
2 votes
3 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 ...
ursitesion's user avatar
  • 2,071
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' ...
Rayhan Muktader's user avatar
113 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 ...
Bojan's user avatar
  • 1,133
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 ...
Acube's user avatar
  • 99
2 votes
1 answer
23k 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 ...
user211502's user avatar
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 ...
Question Overflow's user avatar
4 votes
1 answer
131 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'...
rlandster's user avatar
  • 365
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 = ...
YLS's user avatar
  • 101
13 votes
4 answers
51k 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 ...
chepukha's user avatar
  • 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&...
sairam's user avatar
  • 291
45 votes
2 answers
114k 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,...
giorgio79's user avatar
  • 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 ...
Alex's user avatar
  • 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 ...
chrisale's user avatar
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 ...
ivodvb's user avatar
  • 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 ...
Ken Ingram's user avatar