Questions tagged [mysql-5]
Generic MySQL version 5 tag. Please use a mysql-5.x tag instead. Also tag with mysql for search purposes.
56
questions
111
votes
3
answers
171k
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,328
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
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 ...
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,...
- 1,407
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,289
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
5
votes
1
answer
1k
views
will replication from 5.5.20 to 5.0.XX server work?
I gave two machines which run on two entire different versions of MySQL server. I want to do the replication from 5.5.20 to 5.0 server. Will that be possible?
Because I remember when I transferred ...
- 565
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
150
votes
4
answers
253k
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
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
1k
views
decreased performance of stored procedure when migrated from mysql server 5.0 to 5.5
i recently testing the move of our db from one server to another.
as part of this process i copied all the data to the new db and started testing it.
as a comparison i run a store procedure whose ...
- 113
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
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
3k
views
MySQL Community Edition: Can I replicate my entire server with another server in one go?
I need to migrate all the databases from one server to another. There are about 100 databases, and all databases are used daily and updated/inserted too and read through their respective websites.
...
- 143
63
votes
8
answers
137k
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
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,289
19
votes
1
answer
155k
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
4
votes
1
answer
2k
views
Determining max database and table size supported and present size
One of my MySQL databases has been running on a single PC from the last four years.
Here is my setup:
MySQL 5.5.14
Windows XP on NTFS partition
All of my tables are using InnoDB engine
Here are two ...
- 1,425
3
votes
1
answer
2k
views
Do I have duplicate key indexes?
CREATE TABLE relations (
object_id BIGINT(20) NOT NULL PRIMARY KEY,
object_term BIGINT(20) NOT NULL,
UNIQUE KEY link(object_id, object_term),
CONSTRAINT fk_term FOREIGN KEY(object_term)
...
- 165
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
15
votes
3
answers
3k
views
Speeding up conversion of MyISAM to InnoDB
I've got a mysql 5.1 server with a database of approximately 450 tables, taking up 4GB . The vast majority of these tables (all but 2) are MyIsam. This has been fine for the most part (don't need ...
- 23.5k
8
votes
2
answers
11k
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,541
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
5
votes
2
answers
11k
views
Maximum table size for MySQL database server running on Windows (NTFS) Table type InnoDB
I need to design database to store information contained in millions of log files generated by devices on trial on network.
I have designed 5 different tables to store log file information. But, as ...
- 71
4
votes
5
answers
9k
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
3
votes
2
answers
6k
views
Can we assign mysql Key_buffer_size value for particular table?
In some documents I found that we can assign key_buffer_size to myisam table. This also includes the following steps:
SET GLOBAL myisam_table.key_buffer_size = 4194304;
CACHE INDEX test.myisam_table ...
- 123
2
votes
1
answer
8k
views
mysql db backup excluding specific tables
Are there any mysql scripts for taking a database backup by excluding specified tables?
Also, what is the best way to take backup of a running mysql system?
- 1,536
2
votes
1
answer
1k
views
MySQL : Does 'bytes_sent' and 'bytes_received' include mysqldump data?
I'm trying to estimate my db traffic not including backups (mysql 5.1.41).
I use show global status; to get the parameters bytes_sent and bytes_received.
Questions
Do these numbers include ...
- 207
2
votes
1
answer
2k
views
Is it okay to have different lower_case_table_names value on master and slave?
I have a OSX MySQL master with lower_case_table_names set to 2. I have just setup a Linux MySQL slave with lower_case_table_names=1.
I haven't started replication on the slave, but everything looks ...
- 631
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
13
votes
1
answer
22k
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
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,656
6
votes
3
answers
16k
views
Resolving issue with mysql.proc after upgrading MySQL from 5.0.* to 5.1.*
Like many others, we upgraded our instance of MySQL from 5.0.* to 5.1.49. Since then, we've been running into an error that repeats itself when we perform certain actions:
Attempting to view or run a ...
- 163
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
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
4
votes
1
answer
5k
views
Master and Slave having same id's
I am working as a remote DBA for some of the production servers.
There was replication from Master to Slave.
There is something changed in the scenario they have made one of the Slaves as master ...
- 9,717
4
votes
2
answers
4k
views
How do you copy a table from MySqlServer_A to MySqlServer_B?
I have a daily task that collects data from 3 MySql Servers and then pushes that aggregate data back out to each of the servers deleting the previous days data. All servers are MySql. I would like to ...
- 231
3
votes
1
answer
2k
views
Keeping MySQL staging database on same server updated from production
I just migrated my MySQL 5.5.20 to Amazon RDS from MySQL on EC2. The one thing I'm having difficulty with is how to keep my staging database updated from production. Before, I just ran a nightly ...
- 195
3
votes
1
answer
2k
views
How can I get MySQL to use the value returned by a function as a default?
I have a pre-existing table that has a column to that can hold a UUID.
I'd like to be able to define a default such that new rows coming in (from legacy systems) will still get a UUID defined, via ...
- 557
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
3
votes
1
answer
3k
views
query generate the information one column in two columns in one query
I have a question related with a query in sql. I have the next table:
id name
1 name1
2 country1
3 name2
4 country2
I need to call the results so:
...
- 145
3
votes
2
answers
4k
views
MySQL replication to slave having VPN connection to Master
I am setting up an environment in which the Master and Slave are on different networks at different physical locations (both run on MySQL 5.5.20). I have set up a VPN connection from slave's network ...
- 565
2
votes
1
answer
908
views
Change in datatype?
I have a table on which rows are inserted on hourly basis. The row contains id, installation, product_id and hit_allowed. all of them were mediumint in the beginning. but few days back id reached it ...
- 565
2
votes
1
answer
4k
views
Host Name Resolution in mysql.db and mysql.user tables
We're seeing something peculiar about the way MySQL does name resolution -- or peculiar to us at least (-:
We have two data centers, A and B. A is primary, B is used in case of failover. We have two ...
- 1,367
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
3
answers
1k
views
How to get all non deleted messages from one user
I have two tables one for messages called default_messages and the other for recipients called default_recipient. This is how SQL for those table looks like:
CREATE TABLE IF NOT EXISTS `...
- 1,888
2
votes
1
answer
1k
views
why is mysql still writing data two hours after all transactions have stopped
I have a mysql server used mostly as data warehouse. It loads about 4-5GB of data per day (actual database size, not the size of the source data).
Lately, I noticed something peculiar. After I ...
- 273
2
votes
2
answers
1k
views
How to see what was executed in the database via binlogs for specific date?
I am trying figure out how to "query" binlogs to see what was exectued in the database for a specific date. For example, if I execute mysqlbinlog binlog then the thing just runs forever and it is had ...
- 151
2
votes
1
answer
7k
views
How to know the current log sequence number in 5.0.X
I would like to test the optimal value for iblog files on my mysql which is 5.0.77
I found the below procedure in mysqlperformance blog:
http://www.mysqlperformanceblog.com/2008/11/21/how-to-...
- 814
2
votes
2
answers
3k
views
Should master and slave have same mysql version?
I have a master database running mysql 5.5.11, and I am planning to setup a new slave. I am planning to use the 5.5.30 as it is the latest version. For setting up new slave, I am copying files from ...
- 631