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
151
votes
4
answers
256k
views
Is it safe to delete mysql-bin files?
I have MM Replication in mysql, and I want to squeeze some free space in the box be deleting unnecessary files, I came across these mysql-bin files inside /var/db/mysql/ There are hundreds of those ...
user18530
111
votes
3
answers
172k
views
What is the default order of records for a SELECT statement in MySQL?
Suppose you have the following table and data:
create table t (
k int,
v int,
index k(k)
) engine=memory;
insert into t (k, v)
values (10, 1),
(10, 2),
(10, 3);
When ...
- 1,338
63
votes
8
answers
138k
views
Restore mysql database with different name
How can we restore mysql database with different name from mysqldump file.
I dont't want to open dump file and edit it. Any other better methods?
- 1,536
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
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 ...
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
51
votes
6
answers
46k
views
In MySQL, does the order of the columns in a WHERE clause affect query performance?
I am having performance issues on certain database queries that have large possible result sets.
The query in question, I have three ANDs in the WHERE clause
Does the order of the clauses matter?
...
- 4,299
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
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
19
votes
1
answer
156k
views
How to get rid of "maximum user connections" error?
I am using MySQLi for my webapp but whenever I want to visit some particular page, I get mysqli_connect() [function.mysqli-connect]: (42000/1203): User ***_user already has more than '...
- 293
43
votes
1
answer
147k
views
How long is "too long" for MySQL Connections to sleep?
I logged onto my server to check the status of the database and noticed on the SHOW PROCESSLIST; that there are a large number of connections sleeping that are very old.
What duration should the ...
- 4,299
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
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
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
4
votes
5
answers
10k
views
Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)
I tried restarting the Mysql server but its not working for me. I am sure the server is running and the permissions on the folder /var/lib/mysql/ are correct (777).
When i tried to open the file ...
- 565
-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
5
votes
4
answers
24k
views
mysql duplicate entry error 1062 when restoring backup
Sorry, I seen similar threads but I still couldn't find it addressing my issue plus, I needed some more info on this.
Requirement: To create an exact replica 'db4' of an existing DB 'db3'.
Procedure ...
- 51
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
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
2
answers
15k
views
Performance settings for MyISAM tables - keep everything in memory
I have a 8-core MySQL server with 32 GB of RAM, holding 40 MyISAM tables (total: ~4GB of data).
Since the databases size is several times smaller than the available memory, how can I make full usage ...
- 205
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
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
10
votes
3
answers
6k
views
Users complain that system runs slow when mysqldump is in progress
The MYSQL database (ibdata1) is of size 73 GB and is configured to run as a dedicated database server on Windows 2008 O/S for INNODB tables.
We are running the backup using mysqldump
mysqldump --...
- 176
22
votes
1
answer
41k
views
How soon after updating expire_logs_days param and restarting sql will old binlogs get deleted?
MySQL 5.1.x | InnoDB | Windows
My mysql data directory is starting to fill up with bin logs.
I currently have the following settings configured in my windows mysql server:
[mysqld]
log-bin
server-...
- 617
7
votes
1
answer
9k
views
Is there a way to find my.cnf in shared hosting?
I'm curious is there a way to find my.cnf in shared hosting or is this file can be configured just by DBAs and no one can set his/her own settings?
- 3,666
14
votes
2
answers
27k
views
What is the Best Way to create MySQL Master-Slave Replication Setup and Troubleshoot it?
I am very new to Database Administration.
I face a lot of problems while setting up mysql master-slave replication.
I also face regular mysql replication troubleshooting issues.
Can anybody helps ...
- 9,717
20
votes
3
answers
83k
views
Set "lock wait timeout" in MySQL
Where would I set the maximum time a query will wait for a lock in MySQL 5.0.68 before timing out?
- 11.2k
10
votes
4
answers
26k
views
How to get the most out of MySQL on a QuadCore machine with 16 GB of RAM?
I'm running a MySQL 5.5 server on my workstation for scientific data analysis and wonder how to configure MySQL in order to get the most out of it performance-wise. The types of query that I typically ...
- 285
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
18
votes
3
answers
12k
views
Why does importing a 12 GB .sql file take more than 36 hours?
I've been waiting now for 36 hours for a 12 GB .sql file to be imported with a simple type site.sql | mysql command. I can see the ibdata1 is growing still, currently nearly 40 GB.
Considering the ...
- 365
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
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
8
votes
3
answers
9k
views
Changed max_allowed_packet and still receiving 'Packet Too Large' error
I use mysqldump to create a flat file for backup purposes. I have used this file to recreate the database on an alternate server. I ran the import process through ssh on the command line and I ...
- 4,299
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
1
vote
1
answer
3k
views
How to write signal function in MySQL that can be called from Triggers and Stored Functions?
In my Database I have a table: Employee with recursive association, an employee can be boss of other employee.
The Table Description:
mysql> DESC Employee;
+-------------+--------------+----...
- 581
8
votes
2
answers
12k
views
Are many NULL columns harmful in mysql InnoDB?
As I explored the clustered B-tree index system of InnoDB, I think that the presence of many NULL (or small) columns has no significant effect on InnoDB performance.
Does the presence of excess ...
- 4,531
5
votes
4
answers
16k
views
How do you encrypt data while performing a mysqldump?
My OS is windows 7.
How can I encrypt data while performing a mysqldump?
From the command prompt, can I take a mysqldump with encrypted varchar and texts values?
Please help me.
Thanks
- 163
4
votes
2
answers
24k
views
MySQL Event does not run
i am running mysql 5.1.41 that came bundled with xampp on windows. the problem is that the event doesnt run automatically even when the event scheduler is ON. i have a table named ta_table using ...
- 185
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
1
answer
7k
views
Moving MySQL Cold Backup to Another DB Server
I am new to MySQL and want to know the info whether taking a cold backup from source and copying it onto new server with same version require any additional configurations settings to be changed.
As ...
- 31
6
votes
2
answers
35k
views
MySQL case sensitive table names on Linux
Moving a MySQL database from Windows to Linux I have the problem that on Linux the names of the tables are case sensitive. This is a problem, because the Java application that I am developing can't ...
2
votes
2
answers
472
views
MySQL query slow even with indexes
Structure of my table
mysql> show create table t_group_tag_relation\G
*************************** 1. row ***************************
Table: t_group_tag_relation
Create Table: CREATE TABLE `...
- 707
4
votes
2
answers
9k
views
Setting up MySQL Cluster on Two Systems
I am attempting to setup a MySQL Cluster.
I have the folllowing setup
one management node
two data nodes
another machine on which two more nodes reside
Below is the config.ini file:
[ndbd ...
- 473
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
2
votes
1
answer
4k
views
MySQL: trying to create root user
Upgrading MySQL database to 5.5 and it seems that the root user has disappeared.
I'm trying to create it again using this commands:
javier@javier-mbp:~$ sudo pkill mysqld
[sudo] password for javier: ...
- 142
2
votes
1
answer
6k
views
ERROR 1114 (HY000) at line 6308 in file & The table user_analysis is full
There is a SAN mounted 1TB partition for Mysql backups in our server with version 5.0.45-log. From two days, im seeing the log entries ERROR 1114 (HY000) at line 6308 in file & The table ...
8
votes
4
answers
2k
views
Is splitting a 'users' table for authentication purposes a good idea?
Suppose I have a user table in my site in which there are around 2-3 Million users (records) in the table.
For speeding up my login process, is it a good approach to split my user table, one for ...
- 3,666
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
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
13
votes
1
answer
23k
views
Deleting bin logs in replication environment
I have a question about deleting binary logs in the replication environment:
We have an environment with 1 master and 2 slaves (running mysql 5.5). Sometimes, we run into a space issues during heavy ...
- 151