Hot answers tagged mysql-5.1
11
Oracle are obliged to fix security bugs and updates for MySQL 5.1 until the Premium support period has ended - remember that there are paying customers out there using the product in production.
See page 13 of this document for support end dates by MySQL version.
MySQL 5.5 is more than ready for production use & I'd recommend using that for any new ...
9
I assume you are asking about columns with the AUTO_INCREMENT property.
No, deleted rows is not the only reason, there are several others:
inserts that did not succeed (due to duplicate unique or primary key violations or other reasons)
inserts inside transactions that did not commit and were rolled back.
inserts where the id was explicitely defined (and ...
5
Remove the HAVING COUNT(*) > 0. It's useless, no row will have a count of 0 after a group by.
Change the GROUP BY to: GROUP BY client_id. Grouping by institution_id is not needed, you already have a WHERE condition that narrows it one value.
As @HLGEM suggested, remove the select * and use a list of fields that you need. Right now you are repeating data ...
5
Here are some of my recommendations for InnoDB. In my experience, the buffer pool size is the most important because the more data you can keep in cache, the less time your system will spend using disk IOs.
http://dev.mysql.com/doc/refman/5.5/en/innodb-buffer-pool.html
This is the buffer pool, where data and index are cached
innodb_buffer_pool_size=(In ...
4
To rotate out logs more than 7 days old, add this to my.cnf
[mysqld]
expire-logs-days=7
then restart mysql.
To perform this manually, run this command:
mysql> PURGE BINARY LOGS BEFORE DATE(NOW() - INTERVAL 7 DAY) + INTERVAL 0 SECOND;
This will erase binary logs back to Midnight 7 days ago.
WARNING !!!
Do not erase binary logs from the OS level ...
3
Seconds_Behind_Master is based on the difference between UNIX_TIMESTAMP() and the timestamp logged for the query within the binary log of the master, or the relay log on the slave. Seconds_Behind_Master actually gets you lost in context of realtime if replication processes a series of long running queries. The lag could grow astronomically until all relay ...
3
Make sure that you have at least 2 unused API slots ([mysqld] sections) in your configi.ini file (and perform a rolling restart of your Cluster after adding them). These slots will then be used by memcached.
Note that you can choose to run mysqld processes on the host as well and have them part of the cluster (can access the same data through SQL and NoSQL) ...
3
While I would normally not recommend this
To download and install MySQL 5.1.34, run these commands at your Linux prompt AS IS...
MYSQL_VERSION=5.1
MYSQL_VERSION_RELNUM=34
MYSQL_VERSION_RELEASE=${MYSQL_VERSION}.${MYSQL_VERSION_RELNUM}
REDHAT_RELEASE=5
BINARY_VERSION=x86_64
PERL_LOCATION=ftp://ftp.de.netclusive.de/pub/parallels/Plesk/Expand/2.3.1/packages
...
3
The problem is, when you GROUP on user,foo,bar you are not able to get a list of each of those times to compare.
Something like this might work, but no idea on the performance on a large table. You might want to only do this on a subset (ie tuples just from the last 30 days)
SELECT A.*,B.time FROM reqs A
INNER JOIN (SELECT user,foo,bar,time FROM reqs) B
...
3
It doesn't come down because it isn't supposed to come down.
Max_used_connections is a status variable, and is defined as "the maximum number of connections that have been in use simultaneously since the server started."
It turns out, you can also reset the value to the current number of connections using FLUSH STATUS.
But that isn't a "partial ...
3
I'm guessing you're getting varying row count in the table_row column? This is expected behaviour for InnoDB-tables.
"For InnoDB tables, the row count is only a rough estimate used in SQL optimization. (This is also true if the InnoDB table is partitioned."
http://dev.mysql.com/doc/refman/5.1/en/tables-table.html
There is more information here: ...
3
Conditions with OR are harder for the optimizer than conditions with AND only.
Two or more range conditions (>, >=, <, <=, BETWEEN, LIKE 'search%') are harder than conditions with equality only or with only one range.
Your query has both the above difficulties. Noticing that it is equivalent to this rewriting:
WHERE ( languageId = 3 AND
...
2
Two servers are not allowed to have the same server_id. Also, If you omit --server-id from a master, the default ID is 0, in which case a master refuses connections from all slaves, and a slave refuses to connect to a master.
For more information, see Section 15.1.1.2, “Setting the Replication Slave Configuration”.
Even if both servers have different ...
2
If you cannot free up or add any disk space, you could copy the table_data.* files (MYD,MYI,frm,etc) to another machine with plenty of free disk, run the repair there with myisamchk, then copy the files back to the original machine. If the server needs to stay running, do a FLUSH TABLES WITH READ LOCK table_data before copying the data to the other server ...
2
Your grants are not replicating because you have an (unnecessary) replication filter on INFORMATION_SCHEMA. There is no reason to exclude this, as it is not replicated anyhow. When replication filters are in place, you have to USE a database or your statements are not replicated.
For example...not replicated
Grant all privileges on *.* to foo@localhost ...
2
Just altering the lower_case_table_names setting isn't enough. It needs to be done before you import your database(s).
The MySQL 5.1 documentation lists a procedure for moving between Windows and Linux/UNIX. This will ensure that your desired rules for enforcing case sensitivity are followed. Take a look and verify that you did these steps in the correct ...
2
As you need to perform a read,search operation faster you can use the MyISAM as the table engine if the table will not have the High write's in future.
When you will use the MyISAM Engine for fast read/search for this table ,you need to set the Key_Buffer_size to some appropriate value depending upon the index size of your MyISAM tables and also the amount ...
2
You can't give this value for a particular table this value indicates Index blocks for MyISAM tables are buffered and are shared by all threads. key_buffer_size is the size of the buffer used for index blocks. The key buffer is also known as the key cache.
http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_key_buffer_size
InnoDB ...
2
This seems to be an elusive bug nobody wants to fix
http://bugs.mysql.com/bug.php?id=24761 (2006-12-01 : Status Triaged)
http://bugs.mysql.com/bug.php?id=41602 (2008-12-18 : Status Closed)
http://serverfault.com/questions/235369/how-can-i-get-mysql-5-5-to-log-warnings-to-one-of-the-log-files (2011-02-14)
According to ...
2
Opinion
It's probably better to throw an exception during your applications input checking and not pass the buck to the database.
Workaround
There is a "workaround" but your mileage may vary:
http://forge.mysql.com/worklog/task.php?id=3780
Brute Force?
You could convert your front end table VARCHAR field to a BLOB and store as binary data to cure the ...
2
Yes, it will overwrite the data on the Slave. However, you can setup replication to pick up from the point-in-time of the dump and make it roll all changes since the dump was loaded on the Slave
For this Example, let's assume
IP of the Master is 10.1.1.20
IP of the Slave is 10.1.1.30
Here is what you do
STEP01 : Activate Binary Logging on the Old ...
2
From your paths, I can safely assume the following:
You are running MySQL in Windows
You used the MySQL MSI to install
What you need to do is establish the file my.ini
When installing MySQL for Windows using the MSI, the location of my.ini is expected to be C:/ProgramData/MySQL/MySQL Server 5.1.
Please run the following in a DOS Window:
cd ...
2
POLITICALLY CORRECT ANSWER
You cannot do MySQL Replication from a New Master to an Old Slave
I have written many posts about this:
Nov 26, 2012 : Replicate MySQL 5.0 Master to 5.5 Slave?
Feb 08, 2012 : will replication from 5.5.20 to 5.0.XX server work?
Dec 22, 2011 : What does the base64 BINLOG statements in mysqlbinlog output mean?
Feb 04, 2011 : MySQL ...
2
According to the docs if you ommit the @'hostname' (that is CREATE USER 'name') MySQL will interpret it as it had a @'%'. The error message you provided suggests that there is already a user 'name'@'%' in the system:
mysql> CREATE USER 'name'@'%' IDENTIFIED BY 'test';
Query OK, 0 rows affected (0.04 sec)
mysql> CREATE USER 'name'@'%' IDENTIFIED BY ...
2
Please keep in mind
MySQL 5.1.7
released March 06, 2006
still beta
MySQL 5.1.30
released Dec 01, 2008
First GA Release
Bugs Cleaned Up in this version still exists in MySQL 5.1.7
Here are the MySQL / SkySQL Archives
http://downloads.mysql.com/archives.php?p=mysql-5.1
http://downloads.skysql.com/archive/index/p/mysql/v/5.1
...
2
I suspect your slow UPDATES occur due to your high innodb_max_dirty_pages_pct. This is a very good article on how InnoDB handles checkpoints and dirty page flushing, but the gist of my recommendation is to lower innodb_max_dirty_pages_pct to 60 or 70 and see if that helps.
Unfortunately, I suspect you are running native InnoDB in 5.1 and not the InnoDB ...
2
No, you can't. In most DBMS (Postgres, SQL-Server, Oracle, DB2 and many others), you can just add a CHECK constraint:
ALTER TABLE foo
ADD CONSTRAINT aa_cannot_be_equal_to_bb_CHK
CHECK (aa <> bb) ;
I don't see any way to have this in MySQL, using only referential constraints. Besides triggers, you could allow the two columns to have equal ...
1
In your question, you gave the correct syntax for setting up a dedicated key cache (See LOAD INDEX INTO CACHE)
I wrote about this before
Less RAM than Index_length MyISAM
optimal table design mysql with primay key and varchar value
Once you assign one or more tables to a dedicated key cache and run LOAD INDEX INTO CACHE:
the cached indexes will
get ...
1
My guess would be that there are three(3) aspects to look over:
ASPECT #1
I would be concerned with InnoDB settings because there are new features in InnoDB (starting with the plugin as of MySQL 5.1.38) that allow for accessing multiple CPUs. If you do not set the parameters, InnoDB will behave like it did before the enhancements. In fact, there are cases ...
1
You'll have to decide what you want to happen when a duplicate is found, because you can:
ignore it (insert ignore ...)
do something (insert ... on duplicate key update)
Since mysqldump usually dumps each table in a single insert statement, you can easily just add those keywords in the dump file.
See this SO post for a more in-depth discussion on the ...
Only top voted, non community-wiki answers of a minimum length are eligible
