Re: how to replace this data in a complex field?
On 07/31/2012 09:07 AM, Incarus Derp wrote: I need to replace ACRE_PRC119_ID_29 with ACRE_PRC119 and I cannot do this externally. I have PREG_REPLACE but I'm not sure how I would apply this to the database because it only SELECT's as far as I know. You could CREATE a TABLE from the SELECT. Isn't it? -- RMA. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
how to replace this data in a complex field?
I have a complex field named Inventory in a table named Table1 that can be different every time and is not guaranteed to contain what I need to replace. [["Thing1","Thing4","thing8","thing1","thing942","ACRE_PRC119_ID_29"],[["thing1700",2],"datthing4","pizza","water","apples,"beans","coke-a-cola","rice","apples","apples","icecream","pizza",["7things",6],"7things","7things","8things","tophats","tophats","762x39mmRU"]] I need to replace ACRE_PRC119_ID_29 with ACRE_PRC119 and I cannot do this externally. I have PREG_REPLACE but I'm not sure how I would apply this to the database because it only SELECT's as far as I know. Any solutions? -Ikarus
Re: How often the slave to pull data from master?
Hello David, On 7/30/2012 11:46 AM, David Lerer wrote: Thanks Shawn. This is very useful. Could you shed some light on how rolled-back transactions (or not-yet-committed transactions for that matter) are treated as far as the binary logs? Are these updates actually go to the binary logs, thus trigger replication to the salve? Thanks, David. There are two scenarios to worry about 1) This transaction involves no other storage engine except InnoDB. 2) This transaction involves any other storage engine as well as InnoDB. In both cases, they start out the same. A temporary binary log cache is generated to keep track of the transaction as it occurs. All events are recorded to this cache which may become a temporary file if you exceed certain limits. In the event of a COMMIT, this separate cache (or file) is appended to the normal binary log file at the position that corresponds to the moment where the COMMIT occurs. In the event of a ROLLBACK, there is a difference. In scenario 1) where all changes are transacted, the cache is just dropped. As there were no changes to the actual data, nothing needs to enter the binary log. In scenario 2), however, permanent changes to the data exist on the master. In this case, the entire cache is appended (as normal) but the last command is the ROLLBACK. This applies the non-transacted changes to the slave while removing the transacted changes from the slave thus keeping the two systems in sync. Beginning with 5.5.9, we created a separate cache specifically for the non-transacted events to reduce our replication overhead. Only those statements will be transmitted in the event of a ROLLBACK. http://dev.mysql.com/doc/refman/5.5/en/server-status-variables.html#statvar_Binlog_cache_disk_use Regards, -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: YEAR and time types
2012/07/24 13:07 -0700, Hassan Schroeder On Tue, Jul 24, 2012 at 10:36 AM, Roberta Jaskólski wrote: > version(): 5.5.8-log > @@sql_mode: > REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI,NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,NO_ZERO_DATE,ALLOW_INVALID_DATES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION > > What is yours? version 5.5.19 @@sql_mode is empty Well, the answer to my original question is that MySQL is willind with a warning to "narrow" DATE to YEAR, but with my usual setting, with STRICT_TRANS_TABLES, the warning becomes an error. As for widening YEAR to DATE, MySQL never does it: with a warning it is willind to return the 0-date for an attempt at widening, but, with STRICT_TRANS_TABLES, as erst, the warning becomes an error. It would be better if MySQL were willind also to widen: if, say, the YEAR were 1967, it would be widened to 1967-00-00. Too bad. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: How often the slave to pull data from master?
Thanks Shawn. This is very useful. Could you shed some light on how rolled-back transactions (or not-yet-committed transactions for that matter) are treated as far as the binary logs? Are these updates actually go to the binary logs, thus trigger replication to the salve? Thanks, David. -Original Message- From: Shawn Green [mailto:shawn.l.gr...@oracle.com] Sent: Monday, July 30, 2012 11:22 AM To: Zhigang Zhang Cc: mysql@lists.mysql.com Subject: Re: How often the slave to pull data from master? On 7/29/2012 12:52 AM, Zhigang Zhang wrote: > Hi >> If there are additional events beyond that, it > retrieves those in sequence after it writes the current statement into > the relay logs. > > I have a question about this: > > Whether the slave is sent a signal whenever the master generates each event? > Yes, the slave receives a signal but it is not a TCP (networked) signal that the slave receives. Let me add a few more details to the 'retrieval of events from the Binary log' part of the replication process. Each slave that connects to a master will open a 'dump thread' process that keeps track of the end of the binary log. This is a mini-daemon that runs in the context of the overall MySQL server. The slave holds and maintains an open connection with the dump thread and when there are no new events to transmit to the slave, the thread goes to sleep. This does not break the TCP/IP connection to the slave. The 'signal' to the 'slave' is not sent via TCP to the remote server. It is sent to it's proxy, the dump thread, using a pthread_cond_signal event. This wakes the thread and initiates the streaming of new data to the slave. The dump thread is initiated by the slave when it makes its connection. This is considered a 'slave process' as it is the dedicated local listener for the slave process. Each slave gets its own dump thread. When the slave disconnects, the dump thread is destroyed. Using a low-level kernel signal is much more efficient (in terms of network usage and CPU cycles) than to continuously ping one server from the other. This is why we chose this design. For any additional lower-level details than this, I encourage you to review the source as it can tell you much more than most would like to be shared in a general forum like this list. Also, there is a more technical discussion on the internal mechanics of MySQL already in place. The proper place to ask for more details would be the Internals mailing list. -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql The information contained in this e-mail and any attached documents may be privileged, confidential and protected from disclosure. If you are not the intended recipient you may not read, copy, distribute or use this information. If you have received this communication in error, please notify the sender immediately by replying to this message and then delete it from your system. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: How often the slave to pull data from master?
On 7/29/2012 12:52 AM, Zhigang Zhang wrote: Hi If there are additional events beyond that, it retrieves those in sequence after it writes the current statement into the relay logs. I have a question about this: Whether the slave is sent a signal whenever the master generates each event? Yes, the slave receives a signal but it is not a TCP (networked) signal that the slave receives. Let me add a few more details to the 'retrieval of events from the Binary log' part of the replication process. Each slave that connects to a master will open a 'dump thread' process that keeps track of the end of the binary log. This is a mini-daemon that runs in the context of the overall MySQL server. The slave holds and maintains an open connection with the dump thread and when there are no new events to transmit to the slave, the thread goes to sleep. This does not break the TCP/IP connection to the slave. The 'signal' to the 'slave' is not sent via TCP to the remote server. It is sent to it's proxy, the dump thread, using a pthread_cond_signal event. This wakes the thread and initiates the streaming of new data to the slave. The dump thread is initiated by the slave when it makes its connection. This is considered a 'slave process' as it is the dedicated local listener for the slave process. Each slave gets its own dump thread. When the slave disconnects, the dump thread is destroyed. Using a low-level kernel signal is much more efficient (in terms of network usage and CPU cycles) than to continuously ping one server from the other. This is why we chose this design. For any additional lower-level details than this, I encourage you to review the source as it can tell you much more than most would like to be shared in a general forum like this list. Also, there is a more technical discussion on the internal mechanics of MySQL already in place. The proper place to ask for more details would be the Internals mailing list. -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
