Re: Mesaure query speed and InnoDB pool
Hi Rick, I thought you have to dedicate 70-80% of available RAM not a total RAM. Saying if I have 2 gig of RAM on my exclusively innodb box, and I dedicate 1.4Gig to innodb pool, my 64-bit linux machine will start swapping heavily. If I set it to 800-900M, it just fine and I have like 100M of RAM left for some occasional process. I did try it. Thanks, Igor On 16/04/13 16:21, Rick James wrote: Run your query twice; take the second time. For most queries the first run brings everything into cache, then the second gives you a repeatable, though cached, timing. Please provide EXPLAIN SELECT, SHOW CREATE TABLE, and we will critique your indexes and query plan. Handler* is another way to get consistent values. These numbers are unaffected by caching. 1GB buffer_pool? You have only 2GB of available RAM? Normally, if you are running only InnoDB, the buffer_pool should be set to about 70% of available RAM. -Original Message- From: Ananda Kumar [mailto:anan...@gmail.com] Sent: Tuesday, April 16, 2013 2:06 AM To: Ilya Kazakevich Cc: MySQL Subject: Re: Mesaure query speed and InnoDB pool Does your query use proper indexes. Does your query scan less number blocks/rows can you share the explain plan of the sql On Tue, Apr 16, 2013 at 2:23 PM, Ilya Kazakevich ilya.kazakev...@jetbrains.com wrote: Hello, I have 12Gb DB and 1Gb InnoDB pool. My query takes 50 seconds when it reads data from disk and about 2 seconds when data already exists in pool. And it may take 10 seconds when _some_ pages are on disk and some are in pool. So, what is the best way to test query performance? I have several ideas: * Count 'Innodb_rows_read' or 'Innodb_pages_read' instead of actual time * Set pool as small as possible to reduce its effect on query speed * Set pool larger than my db and run query to load all data into pool and measure speed then How do you measure your queries' speed? Ilya Kazakevich -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: How to change max simultaneous connection parameter in mysql.
Hi Kevin, In your MySQL client pls execute: SET GLOBAL max_connections = 200; To Make the change permanent you can edit /etc/my.cnf or whatever MySQL config file you you've got in your system Look for this line max_connections under [mysqld] secction, add it if it's not in the config. make sure it looks like: max_connections = 200 No MySQL restart required Cheers, Igor On 16/03/13 07:39, Manuel Arostegui wrote: 2013/3/16 Kevin Peterson qh.res...@gmail.com I am using PHP along with mysql. Mysql default configuration allows to have 100 simultaneous connection which I want to chane to 200. Please help. If you're reaching too many connections quite often, this change can imply memory problems in your server. If you are close to get your server to swap...be careful with this parameter as any swapping will affect your performance. Manuel. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Multi-Master Replication Manager - monitor works but mmm_control show executes very long and sometimes returns no output.
Hi, I think MMM's proper functionality depends a lot on network interface configuration and sometimes it behaves odd. I moved to MySQL or in my case to MariaDB + Galera + HaProxy configuration. It's not difficult to setup. There is no SPO as in case with monitor host in MMM, much more robust solution. There are limitations though such as you InnoDB is the only one being supported. Cheers, Igor On 11/03/13 09:22, Manuel Arostegui wrote: 2013/3/11 Rafał Radecki radecki.ra...@gmail.com Hi All. I use: cat /etc/redhat-release CentOS release 6.3 (Final) uname -a Linux prod1.local 2.6.32-279.14.1.el6.x86_64 #1 SMP Tue Nov 6 23:43:09 UTC 2012 x86_64 x86_64 x86_64 GNU/Linux on db host: rpm -qa | grep mmm mysql-mmm-2.2.1-1.el6.noarch bmysql-mmm-agent-2.2.1-1.el6.noarch on monitor host: rpm -qa | grep mmm mysql-mmm-monitor-2.2.1-1.el6.noarch mysql-mmm-2.2.1-1.el6.noarch mysql-mmm-tools-2.2.1-1.el6.noarch When I make mmm_control show/checks the command executes for example for ~15 seconds and sometimes returns no output. In logs there is no info about problems and overall the monitor performs well. But I cannot use mmm_control to check its status. The servers are not over loaded. I have restarted agents and monitor but that has not resolved the problem. When I have rebooted the monitor host first use of mmm_control show was ok but then the problem was active again. Any advice? Have you had any similar problems? Hello, Have you tried to set debug 1 in your mmm_common.conf and start the monitor? You might find useful output in there. Manuel. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: access denied to non-root@localhost null-string user in USER_PRIVILEGES
You mysql.user table might be corrupted. If you have access to it as a root user, try check table mysql.user, and repair table mysql.user if table corruption was detected. Alternatively, shut down mysql server, cd /var/lib/mysql/mysql (to your $datadir/mysql directory) and run mysqlcheck -r mysql user On 26/12/12 18:00, Round Square wrote: Hi all: Suddenly, after a long, functioning run of the mysql server, all the non-root accounts went bad, with: Access denied for user 'non_root_user'@'localhost' (using password: YES) Authenticating with non_root_u...@server.ip.address still works ( the bind-address in my.cnf is mapped to server.ip.address ) Poking around in puzzlement and comparing the current, broken state with the functioning state (from backup) I discovered that in the broken version there is this extra line in the information_schema.USER_PRIVILEGES table: | ''@'localhost' | NULL | USAGE | NO | (Note the null-string user prepended to @localhost) Again: the functional, non-broken state does NOT have this entry. Thus, my current theory is that this line is the culprit. Prior to the failure I had a surge of experimental installations, installing third-party software that created mysql tables, and can't clearly retrace everything I did, at this point, to pinpoint the installation that may have caused it. Be that as it may... (1) Is my theory correct? (2) If that line should not be there... (a) How do I remove it, properly? I don't have debian-sys-maint privileges to delete the line. (Or do I?) (b) Are there other tables, besides USER_PRIVILEGES, that would need to be updated/purged My version: mysql Ver 14.14 Distrib 5.1.41, for debian-linux-gnu (i486) using readline 6.1
Re: MySQL Replication Error
Yes that's correct. it needs to be added on the slave side under [mysqld] section usually close to the place where you define your replication settings (for convenience only). The reason why server failed to start difficult to guess without error log. this will prevent all tables under parallax db with watchdog bit in the name to be replicated. another option *|replicate-ignore-table=/|parallax.watchdog|/| http://dev.mysql.com/doc/refman/5.1/en/replication-options-slave.html#option_mysqld_replicate-ignore-table* and restart server On 07/12/12 00:56, Néstor wrote: I added those line to the slave's my.cnf and mysql would not start replicate-wild-ignore-table=parallax%.watchdog% replicate-wild-ignore-table=parallax%.cache% Nestor On Thu, Dec 6, 2012 at 4:39 PM, Néstor rot...@gmail.com wrote: HI Igor, Are you saying to add these lines to the my.cnf file: replicate-wild-ignore-table=dbname%.watchdog% replicate-wild-ignore-table=dbname%.cache% Correct? Thanks, Nestor :-) On Thu, Dec 6, 2012 at 3:07 PM, Igor Shevtsov nixofort...@gmail.comwrote: In regards to the second part of your email. You don't have errors in your mysql.err log. Those are notes saying that you started IO and SQL replication threads after you skipped an replication error and run start slave command. I wouldn't run SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; command either as it makes your tables inconsistent. I'd rather deleted offending row on the slave and started replication. In this case insert would've succeed and tables would become consistent again. On 06/12/12 21:59, Néstor wrote: I spoke to soon!!! Here is the error about 1.5 hours after replication has started. 121205 16:39:51 [ERROR] Slave: Error 'Duplicate entry '3468897' for key 1' on query. Default database: 'parallax'. Query: 'INSERT INTO watchdog (uid, type, message, variables, severity, link, location, referer, hostname, timestamp) VALUES (0, 'page not found', 'images/internet_explorer/** borderTopLeft.png', 'N;', 4, '',' http://www.sdcwa.org/es/**images/internet_explorer/**borderTopLeft.pnghttp://www.sdcwa.org/es/images/internet_explorer/borderTopLeft.png', '', '10.20.141.24', 1354754352)', Error_code: 1062 121205 16:39:51 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with SLAVE START. We stopped at log 'mysql-bin.000289' position 86451409 --**-- Sorry for the long message, below are the steps use to create my replication. When I created the replication, I followed the steps here : http://crazytoon.com/2008/01/**29/mysql-how-do-you-set-up-** masterslave-replication-in-**mysql-centos-rhel-fedora/http://crazytoon.com/2008/01/29/mysql-how-do-you-set-up-masterslave-replication-in-mysql-centos-rhel-fedora/ I am on a red hat server. --- I set my firewall to accept info from server1 on server2 SERVER1: tcp0 0 wahoo.sdcwa.org:mysql wahooesc.sdcwa.org:52131 ESTABLISHED 30145/mysqld SERVER2: tcp0 0 wahooesc.sdcwa.org:52131www.sdcwa.org:mysql ESTABLISHED 30875/mysqld - I have try using the SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; I have also done these steps where in SERVER1 FLUSH TABLES WITH READ LOCK; SHOW MASTER STATUS; +--+--**+--+--**+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +--+--**+--+--**+ | mysql-bin.000289 | 42394063 | | | +--+--**+--+--**+ Then Dump SERVER1 database then you unlock SERVER1 database and copy it to SERVER2 then FLUSH TABLES WITH READ LOCK; I proceed to STOP SLAVE then add the database to SERVER2 then CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.**000289', MASTER_LOG_POS=42394063; to synch with the SERVER1 Then I start SERVER2 and the output of slave status is: mysql SHOW SLAVE STATUS\G *** 1. row *** Slave_IO_State: Waiting for master to send event Master_Host: 10.168.1.21 Master_User: sdcwa_slave Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000289 Read_Master_Log_Pos: 55848766 Relay_Log_File: mysql-relay-bin.02 Relay_Log_Pos: 13454938 Relay_Master_Log_File: mysql-bin.000289 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 55848766 Relay_Log_Space: 13454938
Re: MySQL Replication Error
Hi Néstor, You might want to put those lines into your my.cnf under replication section and restart the slave: replicate-wild-ignore-table=dbname%.watchdog% and may be this as well: replicate-wild-ignore-table=dbname%.cache% Do you really need to replicate cache and session tables? Cheers, Igor On 06/12/12 21:59, Néstor wrote: I spoke to soon!!! Here is the error about 1.5 hours after replication has started. 121205 16:39:51 [ERROR] Slave: Error 'Duplicate entry '3468897' for key 1' on query. Default database: 'parallax'. Query: 'INSERT INTO watchdog (uid, type, message, variables, severity, link, location, referer, hostname, timestamp) VALUES (0, 'page not found', 'images/internet_explorer/borderTopLeft.png', 'N;', 4, '',' http://www.sdcwa.org/es/images/internet_explorer/borderTopLeft.png', '', '10.20.141.24', 1354754352)', Error_code: 1062 121205 16:39:51 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with SLAVE START. We stopped at log 'mysql-bin.000289' position 86451409 Sorry for the long message, below are the steps use to create my replication. When I created the replication, I followed the steps here : http://crazytoon.com/2008/01/29/mysql-how-do-you-set-up-masterslave-replication-in-mysql-centos-rhel-fedora/ I am on a red hat server. --- I set my firewall to accept info from server1 on server2 SERVER1: tcp0 0 wahoo.sdcwa.org:mysql wahooesc.sdcwa.org:52131 ESTABLISHED 30145/mysqld SERVER2: tcp0 0 wahooesc.sdcwa.org:52131www.sdcwa.org:mysql ESTABLISHED 30875/mysqld - I have try using the SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; I have also done these steps where in SERVER1 FLUSH TABLES WITH READ LOCK; SHOW MASTER STATUS; +--+--+--+--+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +--+--+--+--+ | mysql-bin.000289 | 42394063 | | | +--+--+--+--+ Then Dump SERVER1 database then you unlock SERVER1 database and copy it to SERVER2 then FLUSH TABLES WITH READ LOCK; I proceed to STOP SLAVE then add the database to SERVER2 then CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000289', MASTER_LOG_POS=42394063; to synch with the SERVER1 Then I start SERVER2 and the output of slave status is: mysql SHOW SLAVE STATUS\G *** 1. row *** Slave_IO_State: Waiting for master to send event Master_Host: 10.168.1.21 Master_User: sdcwa_slave Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000289 Read_Master_Log_Pos: 55848766 Relay_Log_File: mysql-relay-bin.02 Relay_Log_Pos: 13454938 Relay_Master_Log_File: mysql-bin.000289 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 55848766 Relay_Log_Space: 13454938 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 1 row in set (0.00 sec) --- I get the folllowing on my mysql.err: 121205 15:09:56 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.000289' at position 42394063, relay log '/var/log/mysql/mysql-relay-bin.01' position: 4 121205 15:09:56 [Note] Slave I/O thread: connected to master ' sdcwa_slave@192.168.1.21:3306', replication started in log 'mysql-bin.000289' at position 42394063 Everything is GOOD, for a little while and then I get an error on mysql.err: - Now if I do the SKIP FLAG many times, I will error after error... I do not see how replication works so well for others if I am using the steps in the link on top of this message. THANKS!!! On Wed, Dec 5, 2012 at 3:42 AM, Manuel Arostegui man...@tuenti.com wrote: 2012/11/30 Néstor rot...@gmail.com I am trying to set up mysql replication on 2 systems but Once I get it going, I get the following an error 1062, skip I have re-installed the database on the slave also to see if this fixes the problem but after a few minutes I get the same error. I have repeated the replication from the beginning but I get the same problem after a while. Does anyone know a
Re: MySQL Replication Error
In regards to the second part of your email. You don't have errors in your mysql.err log. Those are notes saying that you started IO and SQL replication threads after you skipped an replication error and run start slave command. I wouldn't run SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; command either as it makes your tables inconsistent. I'd rather deleted offending row on the slave and started replication. In this case insert would've succeed and tables would become consistent again. On 06/12/12 21:59, Néstor wrote: I spoke to soon!!! Here is the error about 1.5 hours after replication has started. 121205 16:39:51 [ERROR] Slave: Error 'Duplicate entry '3468897' for key 1' on query. Default database: 'parallax'. Query: 'INSERT INTO watchdog (uid, type, message, variables, severity, link, location, referer, hostname, timestamp) VALUES (0, 'page not found', 'images/internet_explorer/borderTopLeft.png', 'N;', 4, '',' http://www.sdcwa.org/es/images/internet_explorer/borderTopLeft.png', '', '10.20.141.24', 1354754352)', Error_code: 1062 121205 16:39:51 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with SLAVE START. We stopped at log 'mysql-bin.000289' position 86451409 Sorry for the long message, below are the steps use to create my replication. When I created the replication, I followed the steps here : http://crazytoon.com/2008/01/29/mysql-how-do-you-set-up-masterslave-replication-in-mysql-centos-rhel-fedora/ I am on a red hat server. --- I set my firewall to accept info from server1 on server2 SERVER1: tcp0 0 wahoo.sdcwa.org:mysql wahooesc.sdcwa.org:52131 ESTABLISHED 30145/mysqld SERVER2: tcp0 0 wahooesc.sdcwa.org:52131www.sdcwa.org:mysql ESTABLISHED 30875/mysqld - I have try using the SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; I have also done these steps where in SERVER1 FLUSH TABLES WITH READ LOCK; SHOW MASTER STATUS; +--+--+--+--+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +--+--+--+--+ | mysql-bin.000289 | 42394063 | | | +--+--+--+--+ Then Dump SERVER1 database then you unlock SERVER1 database and copy it to SERVER2 then FLUSH TABLES WITH READ LOCK; I proceed to STOP SLAVE then add the database to SERVER2 then CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000289', MASTER_LOG_POS=42394063; to synch with the SERVER1 Then I start SERVER2 and the output of slave status is: mysql SHOW SLAVE STATUS\G *** 1. row *** Slave_IO_State: Waiting for master to send event Master_Host: 10.168.1.21 Master_User: sdcwa_slave Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000289 Read_Master_Log_Pos: 55848766 Relay_Log_File: mysql-relay-bin.02 Relay_Log_Pos: 13454938 Relay_Master_Log_File: mysql-bin.000289 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 55848766 Relay_Log_Space: 13454938 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 1 row in set (0.00 sec) --- I get the folllowing on my mysql.err: 121205 15:09:56 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.000289' at position 42394063, relay log '/var/log/mysql/mysql-relay-bin.01' position: 4 121205 15:09:56 [Note] Slave I/O thread: connected to master ' sdcwa_slave@192.168.1.21:3306', replication started in log 'mysql-bin.000289' at position 42394063 Everything is GOOD, for a little while and then I get an error on mysql.err: - Now if I do the SKIP FLAG many times, I will error after error... I do not see how replication works so well for others if I am using the steps in the link on top of this message. THANKS!!! On Wed, Dec 5, 2012 at 3:42 AM, Manuel Arostegui man...@tuenti.com wrote: 2012/11/30 Néstor rot...@gmail.com I am trying to set up mysql replication on 2 systems but Once I get it going, I get the following an error 1062, skip I have re-installed the database on the slave
Re: Assistance with replication
Hi Michael, Is your binary log running on the slaves? I have the same issue when forgotten enabling log_bin on SLAVE host Thanks, Igor On 12/11/12 13:04, Machiel Richards - Gmail wrote: Hi Guys We need some assistance with a master slave setup we are experiencing some issues with. We have one master with 2 slaves replicating from it. Both the slaves stopped working this morning in the early hours at exactly the same bin_log file and position. We have since restarted the slave servers and when you run a show slave status the seconds behind master stays 0. Upon closer investigation, the Relay_Master_Log_File and Exec_Master_Log_Pos does not change, although all other values are changing. No errors are being logged, and there is enough disk space on the server so it can't be space related. Can anyone suggest some things we can check or do in order to get replication sorted and to avoid future issues like this? regards Machiel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
optimize DB - bad experience
Hi Guys, Just curious if any of you had a bad experience running optimize table procedure. ERRORS, corrupted databases or tables caused by this. We have MySQL 5.1.46 server. I feel to run optimize of the full DB is quite safe Example: shell mysqlcheck -o --skip-write-binlog database What's your stories on this one? Many thanks, Igor -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: A Simple Query Help
Hi Rafael, You can try using correlated subquery instead of outer join. This can be slow with big tables though: SELECT * FROM users WHERE accept_email = 1 and email not in (SELECT email FROM sent_emails WHERE sent_emails .email = users.email AND messageID NOT LIKE = ‘XX’) OR OUTER JOIN as a better option: SELECT u.* FROM users AS u OUTER LEFT JOIN sent_emails AS se USING (email) where u.accept_email = 1 AND se.messageID NOT LIKE = ‘XX’ AND se.email IS NULL Thanks, Egor SELECT * FROM sent_emails WHERE email in (SELECT email FROM users WHERE accept_email = 1) AND messageID NOT LIKE = ‘XX’ On 04/22/2012 09:30 PM, Rafael Ribeiro wrote: Dear Friends, I m new on this list, and I m trying to learn more about mysql. After perform a lot of searchs in the Internet, I have no answer to my question and would like to ask your help. I wanna a perform a query that depends of the result from another (query) table inside the same database. On this scenario: I have 02 tables: Table 1 = users Table 2 = sent_emails I wanna select ONLY the users that are NOT inside the table SENT_emails Example: $query1 = SELECT * FROM users WHERE accept_email = ‘1’ The results from query above SHOULD depends of the query Bellow: $query2 = SELECT * FROM sent_emails WHERE email = $email_from_query_above AND messageID NOT LIKE = ‘XX’ The results of the first query, should display only the users that are NOT inside the condition of query 2. I read about INNER JOIN LEFT ... but I can´t understand ... Can help me? With Regards, Rafael Ribeiro -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: FULL mysqldump
Hi Reindl, what do you delete by rm -f /Volumes/dune/mysql_data/bin* and why? Many thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
stored procedure insert statement
Hi all, I can't explain strange behaviour of the INSERT statement in the stored procedure. The idea is to generate a list based on the output of 3 INNER JOIN of regularly updated tables. Something like : INSERT INTO storage (column list) SELECT column list FROM t1 JOIN t2 ON t1.x=t2.y JOIN t3 ON t2.z=t3.w WHERE CONDITIONS; The procedure runs daily by crontask and it inserts correct number of output rows. But after It runs and populated a storage table, I added new entries and expect to find them in the storage table. Even though they were picked up by SELECT statement, they haven't been INSERTed into the storage table. If I DELETE or TRUNCATE from the storage table and run the procedure all newly added entries and existed entries are their, but if I add new rows and run the procedure again It doesn't update the table. All tables have a unique identifier, so duplicate errors are impossible. I use INNODB engine for all tables. I understand that stored procedure is a precompiled thing and I believe it could be something to do with cache but I couldn't find proper explanation or similar case online. I found when I use INSERT IGNORE INTO or INSERT REPLACE INTO, I receive the proper result with newly entries added to the storage table. Any ideas guys? Have a nice weekend ALL. Cheers, Igor -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: stored procedure insert statement
Thanks Johnny, In this case I wouldn't be able to insert a completely new row but replace the existent one, so row count would stay the same. This is a storage table with the only unique constraints on: dda_debits_id column. the test data is very small, so I would've noticed any duplicates and they wouldn't make it to the table anyway with or without INSERT IGNORE. +---+-+--+-+-++ | Field | Type| Null | Key | Default | Extra | +---+-+--+-+-++ | dda_debits_id | int(11) | NO | PRI |0 || | created_on| datetime| YES | | NULL || | reference_number | varchar(18) | YES | | NULL || | user_format_debit_ref | varchar(18) | YES | | NULL || | amount| int(11) | YES | | NULL || | debit_date| datetime| YES | | NULL || | status| tinyint(1) | YES | | NULL || | debit_type| tinyint(1) | YES | | NULL || | recharge_for_id | int(11) | YES | | NULL || | processed_on | datetime| YES | | NULL || | service_user_id | int(11) | YES | | NULL || +---+-+--+-+-++ Claudio, good point. Unfortunately, didn't work. I tried it before but no luck. Thanks, Igor On 07/09/2011 02:43 PM, Johnny Withers wrote: It seems to me that your insert statement is trying to insert duplicate rows into the storage table. This is why insert ignore and replace work. On Jul 9, 2011 3:49 AM, Igor Shevtsov nixofort...@googlemail.com mailto:nixofort...@googlemail.com wrote: Hi all, I can't explain strange behaviour of the INSERT statement in the stored procedure. The idea is to generate a list based on the output of 3 INNER JOIN of regularly updated tables. Something like : INSERT INTO storage (column list) SELECT column list FROM t1 JOIN t2 ON t1.x=t2.y JOIN t3 ON t2.z=t3.w WHERE CONDITIONS; The procedure runs daily by crontask and it inserts correct number of output rows. But after It runs and populated a storage table, I added new entries and expect to find them in the storage table. Even though they were picked up by SELECT statement, they haven't been INSERTed into the storage table. If I DELETE or TRUNCATE from the storage table and run the procedure all newly added entries and existed entries are their, but if I add new rows and run the procedure again It doesn't update the table. All tables have a unique identifier, so duplicate errors are impossible. I use INNODB engine for all tables. I understand that stored procedure is a precompiled thing and I believe it could be something to do with cache but I couldn't find proper explanation or similar case online. I found when I use INSERT IGNORE INTO or INSERT REPLACE INTO, I receive the proper result with newly entries added to the storage table. Any ideas guys? Have a nice weekend ALL. Cheers, Igor -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=joh...@pixelated.net
