New answers tagged mysql-5.5
1
Normally you want to throw away as many rows as you can, as quickly as you can. That doesn't seem likely here, unfortunately.
All you have is 'deleted = 0' for each table, so MySQL will pick a table to start with. In your explain, it picked user_details, resulting in 1.5M rows being used.
Do the other tables have fewer active rows? If so, you'd want to ...
1
Several points:
1) Store the data in two columns.
2) Normalize 'type' to a separate table, so you'll only put an INT into your index rather than a varchar(255).
3) Switch to UNSIGNED INT for the ids, and maybe TINYINT for the types.
4) Tell the app people to learn about multiple WHERE clauses for their SQL and give them a good index.
3) Once you do ...
1
Errcode: 13 is a file permission issue.
If your datadir is /var/lib/mysql, do the following:
cd /var/lib/
ls -l
Check the file permissions as make sure /var/lib/mysql is owned by mysql:mysql.
If it is not, then do this:
chown -R mysql:mysql /var/lib/mysql
If you ran sudo, you are probably root. Make sure root can write into /var/lib/mysql
Give it a ...
0
Looking in the error log you can see
130513 11:22:24 [ERROR] /backup/mysql-cbt/bin/mysqld: unknown option '--safe- show- database'
130513 11:22:24 [ERROR] Aborting
130513 11:22:24 InnoDB: Starting shutdown...
On documentation for your version of MySQL you can see
This option is deprecated and does not do anything because there is a ...
5
From the documentation:
You can also discard an input value by assigning it to a user variable
and not assigning the variable to a table column:
LOAD DATA INFILE 'file.txt'
INTO TABLE t1
(column1, @dummy, column2, @dummy, column3);
0
You asked for suggestions. Here's one.
If I were trying to analyze this case, the first thing I would do is separate it out into two separate DELETE statements, one to cover the IS NULL case, and the other to cover the value_was < Value_now case. See whether they both run slow. I expect the IS NULL case to do a table scan, unless indexes work ...
0
You could try to use EXISTS clause instead of IN, since its usually less performance expensive:
DELETE FROM TABLE T_delete
WHERE EXISTS (SELECT 1
FROM TABLE t_join
WHERE value_was IS NULL
AND T_delete.ID1 = t_join.ID1)
OR EXISTS (SELECT 1
FROM TABLE t_join
WHERE value_was ...
0
Given that the valid characters for unquoted identifier characters are documented here, you should be able to find non-confirming table names in information_schema using a regular expression match, with this query:
SELECT *
FROM information_schema.tables
WHERE table_name NOT RLIKE '^[0-9a-zA-Z$_]+$';
1
Like @a_horse_with_no_name told on his comment, the differences are documented in here, but here is some information:
Size:
datetime - uses 8 bytes for each field
timestamp - uses 4 bytes for each field (half of the size)
Range:
datetime - 1000-01-01 00:00:00 to 9999-12-31 23:59:59
timestamp - 1970-01-01 00:00:01 UTC to 2038-01-19 03:14:07 UTC
...
0
For time being ensure you have more connections set to max_connections globally.
`SET GLOBAL max_connections = 1024;`
Consider below points before you conclude anything.
For every 5 minutes you have a new thread coming so ensure that you have your application effeciency to complete in seconds unless it is really a time consuming process.
Check what ...
1
According to the documentation, you can use SET statements to transform the data on the way in.
[SET col_name = expr,...]
The expr expression can include the column name, which will be interpreted as the data being read from the file and destined for that column... so, for example, at the end of your LOAD DATA INFILE statement you might use:
SET ...
0
This is not a new problem
Feb 07, 2007 : Upgrading from MySQL 3.23 to 5
Oct 26, 2010 : Upgrading from MySQL 4.1 to 5
On May 11, 2007. someone had "fun" with this upgrading MySQL 4.1 to 5.0.41. Notice his comment (Look for Posted by Phil Collett on May 11 2007 7:11pm in the Post):
I had some fun upgrading from mysql-4.1.15-win32 (mysql 4.1) to
...
0
Each connection carries the load of per-connection buffers as set by these parameters
join_buffer_size
sort_buffer_size
read_buffer_size
read_rnd buffer_size
Changing the number of connections increases the amount of memory each connection can demand to this : (join_buffer_size+sort_buffer_size+read_buffer_size
+read_rnd buffer_size) X max_connections
I ...
1
ANSWER #1
This feels clumsy doing this in Windows but here it goes. If you are able to login to mysql, then run this script
set MYSQL_USER=root
set MYSQL_PASS=rootpassword
set SQLSTMT=SELECT CONCAT('REPAIR TABLE ',table_schema,'.',table_name,';')
set SQLSTMT=%SQLSTMT% FROM information_schema.tables WHERE engine='InnoDB'
set MYSQL_CONN=-u%MYSQL_USER% ...
3
How are you coming to the conclusion that "these files contain some data even after graceful shutdown"?
Even if it's been fully flushed and uncommitted transactions cleared, etc., by using innodb_fast_shutdown = 0, the "flushing" probably means that the file is "empty" as far as MySQL is concerned, but it won't physically be full of zeroes.
If you start ...
3
The steps you outlined are correct.
I have written about this before
Feb 16, 2011 : How to safely change MySQL innodb variable 'innodb_log_file_size'?
May 30, 2012 : mysql wont start after increasing innodb_buffer_pool_size and innodb_log_file_size
Doing innodb_fast_shutdown = 1 is incomplete because ibdata1 still have other transactional tidbits that ...
2
There is a small problem in your aproach, if I understood correctly, you changed the log file size, and restarted the MySQL with innodb_fast_shutdown = 0. The problem here is that you still have some transaction on log files that aren't in the ibdata. What you need to do is tell MySQL to write all changes from log files to ibdata before move the log files, ...
2
Are you running this test between RDS and MySQL workbench on your own machine?
The MySQL client (and MySQL workbench) are including the time it takes for RDS to return the value of "1" to your workstation (from my workstation on the other side of the world, the response from RDS US West takes 280ms).
If you try enabling profiling on the RDS service, you ...
0
I think you need a third table, orders, which connects the items and persons.
Tables
CREATE TABLE IF NOT EXISTS `persons` (
`person_id` int(4) NOT NULL AUTO_INCREMENT,
`name` varchar(100) NOT NULL,
PRIMARY KEY (`person_id`)
);
CREATE TABLE IF NOT EXISTS `items` (
`item_id` int(4) NOT NULL AUTO_INCREMENT,
`item_type` varchar(50) NOT NULL,
...
0
You should find which query causes a transaction lock by executing:
SHOW ENGINE INNODB STATUS
and see a section named TRANSACTION. Later you should kill that query to drop a database.
Reference: How do I find which transaction is causing a “Waiting for table metadata lock” state?
2
As your main concern is to have an incremental backup solution, you can change the follow variables:
Changes:
innodb_max_dirty_pages_pct - set it to 75, then innoDB will cache some changes and flush it to disk at once.
innodb_doublewrite - Disable the innodb double write
sync_binlog - Disable syn binlog
long_query_time - increase the long query time or ...
1
You can try to comment out these two variables on your slave to use default values - they may cause high disk I/O:
max_binlog_size
sync_binlog
2
MySQL lacks common table expressions but you can achieve the same goal here with the multiple-table update syntax:
update activitybooking
cross join ( select sum(pool1_count) p1, sum(pool2_count) p2, sum(pool3_count) p3
from `activitybooking`
where `abt`='12' and (id='958' or `submitted`='1' or ...
1
You can certainly try this;
SELECT DISTINCT msisdn
FROM std_msc_opr_wise
WHERE
call_dt BETWEEN '2013-03-27' AND '2013-04-28'
AND msisdn NOT IN (
select msisdn
from as_treat_pre_usage_30days
group by msisdn
having SUM(std_total_og_mou)>0
)
0
If an InnoDB table is being accessed at all via SELECT or DML (INSERT, UPDATE, DELETE), you should rightly expect a metadata lock.
According to the MySQL Documentation on MetaData Locking:
To ensure transaction serializability, the server must not permit one session to perform a data definition language (DDL) statement on a table that is used in an ...
1
The only option that comes to mind is a BEFORE INSERT trigger. In 5.5 you could use SIGNAL:
DELIMITER $$
CREATE TRIGGER bi_foo BEFORE INSERT on foo
FOR EACH ROW
BEGIN
IF (ISNULL(NEW.col3) AND (SELECT COUNT(*) FROM foo
WHERE col1 = NEW.col1 AND col2 = NEW.col2 AND col3 IS NULL) > 0)
THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = ...
0
You basically need to keep track the new and old id from mo_sms
load all DB2.mo_sms that you want to copy
for each row in this result set
store the actual DB2.mo_sms id
save this row on DB1.mo_sms
store the new DB1.mo_sms id, you can use LAST_INSERT_ID()
load all DB2.mt_sms entries related to id that you stored on step 2.1
save all entries on DB1.mt_sms ...
Top 50 recent answers are included
