[ZODB-Dev] RelStorage and MySQL wait-timeout
On 01/02/2011 04:11, Shane Hathaway wrote: My guess is that the zap_all took so long that the server had gone away by the time the sql statement had be executed. My guess is MySQL is configured to drop connections when they are idle. Indeed, Rackspace had configured a wait-timeout of 60 second! (why on earth they would do that is beyond me, answers on a post card...) That is a bad idea IMHO, so I think raising that exception is the right thing to do, not a bug. Okay, but, with it at 60s, I was getting the following behaviour when rendering pages: Couldn't load state for 0x11ae03 Traceback (most recent call last): File /var/buildout-eggs/ZODB3-3.9.6-py2.6-linux-i686.egg/ZODB/Connection.py, line 847, in setstate self._setstate(obj) File /var/buildout-eggs/ZODB3-3.9.6-py2.6-linux-i686.egg/ZODB/Connection.py, line 897, in _setstate p, serial = self._storage.load(obj._p_oid, '') File /var/buildout-eggs/RelStorage-1.4.0-py2.6.egg/relstorage/storage.py, line 448, in load state, tid_int = cache.load(cursor, oid_int) File /var/buildout-eggs/RelStorage-1.4.0-py2.6.egg/relstorage/cache.py, line 279, in load state, tid_int = self.adapter.mover.load_current(cursor, oid_int) File /var/buildout-eggs/RelStorage-1.4.0-py2.6.egg/relstorage/adapters/mover.py, line 125, in mysql_load_current cursor.execute(stmt, (oid,)) File /var/buildout-eggs/MySQL_python-1.2.3-py2.6-linux-i686.egg/MySQLdb/cursors.py, line 174, in execute self.errorhandler(self, exc, value) File /var/buildout-eggs/MySQL_python-1.2.3-py2.6-linux-i686.egg/MySQLdb/connections.py, line 36, in defaulterrorhandler raise errorclass, errorvalue OperationalError: (2006, 'MySQL server has gone away') ...which feels a little on the serious side for (what is for MySQL) quite a normal situation to be in. (Mike Bayer could probably shed some light given the connection pool stuff that SQLAlchemy does to deal with MySQL's behaviour...) I also had a segfault trying to do the same conversion which I'm attributing to the MySQL server being restarted by an overeager DBA mid-converstion but still, that shouldn't cause a segfault, right? I don't know why it would. I'll let you know if I see it again... cheers, Chris -- Simplistix - Content Management, Batch Processing Python Consulting - http://www.simplistix.co.uk ___ For more information about ZODB, see the ZODB Wiki: http://www.zope.org/Wikis/ZODB/ ZODB-Dev mailing list - ZODB-Dev@zope.org https://mail.zope.org/mailman/listinfo/zodb-dev
[ZODB-Dev] RelStorage - what tables and indexes should be present?
Hi Shane, I notice that one of my history-free storages only has 'new_oid' and 'object_state' tables while all the others have 'new_oid', 'object_ref', 'object_refs_added', 'object_state' and 'pack_object' tables. What's special about this storage? I also note that while the ones with lots of tables have this schema for object_state: CREATE TABLE `object_state` ( `zoid` bigint(20) NOT NULL, `tid` bigint(20) NOT NULL, `state` longblob, PRIMARY KEY (`zoid`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1$$ ...the one with only two tables has this schema: CREATE TABLE `object_state` ( `zoid` bigint(20) NOT NULL, `tid` bigint(20) NOT NULL, `state` longblob, PRIMARY KEY (`zoid`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1$$ ...which causes the following statement, which gets executed a lot, to be very slow: SELECT tid FROM object_state ORDER BY tid DESC LIMIT 1 cheers, Chris -- Simplistix - Content Management, Batch Processing Python Consulting - http://www.simplistix.co.uk ___ For more information about ZODB, see the ZODB Wiki: http://www.zope.org/Wikis/ZODB/ ZODB-Dev mailing list - ZODB-Dev@zope.org https://mail.zope.org/mailman/listinfo/zodb-dev
[ZODB-Dev] (anecdotal) Restarting MySQL under RelStorage gives segfault
Hi All, I've had a couple of instances now where RelStorage-backed ZODB client processes have crashed/segfaulted. As best I can tell, both times have been the result of the MySQL server being restarted while the client is connected. However, other server restarts haven't caused this problem, so I'm wondering if it's a restart at a particular point in the transaction cycle that causes the segfault? Anyway, just putting this out there in case anyone else has had similar experiences... cheers, Chris -- Simplistix - Content Management, Batch Processing Python Consulting - http://www.simplistix.co.uk ___ For more information about ZODB, see the ZODB Wiki: http://www.zope.org/Wikis/ZODB/ ZODB-Dev mailing list - ZODB-Dev@zope.org https://mail.zope.org/mailman/listinfo/zodb-dev
Re: [ZODB-Dev] Error from MySQL when attempting to run zodbconvert
On 01/02/2011 04:03, Shane Hathaway wrote: On 01/31/2011 06:30 PM, Chris Withers wrote: Hi Shane, I got the following error when trying to run zodbconvert against a clustered MySQL environment running RHCS: File /var/buildout-eggs/MySQL_python-1.2.3-py2.6-linux-i686.egg/MySQLdb/connections.py, line 36, in defaulterrorhandler raise errorclass, errorvalue _mysql_exceptions.OperationalError: (1598, Binary logging not possible. Message: Transaction level 'READ-COMMITTED' in InnoDB is not safe for binlog mode 'STATEMENT') That's why you have to use row-based replication instead of statement-based replication. Is this in the RelStorage docs anywhere? If so, I missed it, but if not, I guess it probably should be... cheers, Chris -- Simplistix - Content Management, Batch Processing Python Consulting - http://www.simplistix.co.uk ___ For more information about ZODB, see the ZODB Wiki: http://www.zope.org/Wikis/ZODB/ ZODB-Dev mailing list - ZODB-Dev@zope.org https://mail.zope.org/mailman/listinfo/zodb-dev
Re: [ZODB-Dev] RelStorage - what tables and indexes should be present?
On 01/02/2011 14:42, Stephan Richter wrote: On Tuesday, February 01, 2011, Chris Withers wrote: I also note that while the ones with lots of tables have this schema for object_state: CREATE TABLE `object_state` ( `zoid` bigint(20) NOT NULL, `tid` bigint(20) NOT NULL, `state` longblob, PRIMARY KEY (`zoid`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1$$ ...the one with only two tables has this schema: CREATE TABLE `object_state` ( `zoid` bigint(20) NOT NULL, `tid` bigint(20) NOT NULL, `state` longblob, PRIMARY KEY (`zoid`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1$$ Did you paste correctly? They seem identical to me. Nope, I phail... but the above is the incorrect version. Try creating a RelStorage and you'll see. The above ends up being really slow as it has no index on tid... cheers, Chris -- Simplistix - Content Management, Batch Processing Python Consulting - http://www.simplistix.co.uk ___ For more information about ZODB, see the ZODB Wiki: http://www.zope.org/Wikis/ZODB/ ZODB-Dev mailing list - ZODB-Dev@zope.org https://mail.zope.org/mailman/listinfo/zodb-dev
Re: [ZODB-Dev] RelStorage - what tables and indexes should be present?
On 02/01/2011 10:16 AM, Chris Withers wrote: I notice that one of my history-free storages only has 'new_oid' and 'object_state' tables while all the others have 'new_oid', 'object_ref', 'object_refs_added', 'object_state' and 'pack_object' tables. What's special about this storage? It sounds like RelStorage didn't get a chance to finish creating the schema. In MySQL, DDL statements are not transactional, so errors during schema creation (such as a timeout) leave a partial schema. Shane ___ For more information about ZODB, see the ZODB Wiki: http://www.zope.org/Wikis/ZODB/ ZODB-Dev mailing list - ZODB-Dev@zope.org https://mail.zope.org/mailman/listinfo/zodb-dev
Re: [ZODB-Dev] RelStorage - what tables and indexes should be present?
On 01/02/2011 17:33, Shane Hathaway wrote: What's special about this storage? It sounds like RelStorage didn't get a chance to finish creating the schema. In MySQL, DDL statements are not transactional, so errors during schema creation (such as a timeout) leave a partial schema. Sounds like something to warn about in the docs for zodbconvert and storage creation... cheers, Chris -- Simplistix - Content Management, Batch Processing Python Consulting - http://www.simplistix.co.uk ___ For more information about ZODB, see the ZODB Wiki: http://www.zope.org/Wikis/ZODB/ ZODB-Dev mailing list - ZODB-Dev@zope.org https://mail.zope.org/mailman/listinfo/zodb-dev
Re: [ZODB-Dev] RelStorage and MySQL wait-timeout
On 02/01/2011 10:01 AM, Chris Withers wrote: OperationalError: (2006, 'MySQL server has gone away') ...which feels a little on the serious side for (what is for MySQL) quite a normal situation to be in. Random disconnects are unacceptable for RelStorage. If MySQL goes away outside transaction boundaries, we *have* to propagate the exception to the application; otherwise consistency is lost. We can only reconnect at the next request or transaction boundary. (Mike Bayer could probably shed some light given the connection pool stuff that SQLAlchemy does to deal with MySQL's behaviour...) Unfortunately, the generic behavior implemented by SQLAlchemy would not work here. It would scramble ZODB. Shane ___ For more information about ZODB, see the ZODB Wiki: http://www.zope.org/Wikis/ZODB/ ZODB-Dev mailing list - ZODB-Dev@zope.org https://mail.zope.org/mailman/listinfo/zodb-dev
Re: [ZODB-Dev] RelStorage and MySQL wait-timeout
Hi Shane, On 01/02/2011 17:45, Shane Hathaway wrote: On 02/01/2011 10:01 AM, Chris Withers wrote: OperationalError: (2006, 'MySQL server has gone away') ...which feels a little on the serious side for (what is for MySQL) quite a normal situation to be in. Random disconnects are unacceptable for RelStorage. If MySQL goes away outside transaction boundaries, we *have* to propagate the exception to the application; Are you sure you mean outside? Surely outside is fine, you just reconnect as part of starting the transaction? otherwise consistency is lost. We can only reconnect at the next request or transaction boundary. Indeed, and am I right in thinking RelStorage does this just fine, along with emitting a warning message to say that it has occurred? I can understand the problem being fairly terminal if there was a disconnect *during* a timeout, and I'd expect an exception, but not a segfault ;-) cheers, Chris -- Simplistix - Content Management, Batch Processing Python Consulting - http://www.simplistix.co.uk ___ For more information about ZODB, see the ZODB Wiki: http://www.zope.org/Wikis/ZODB/ ZODB-Dev mailing list - ZODB-Dev@zope.org https://mail.zope.org/mailman/listinfo/zodb-dev
Re: [ZODB-Dev] RelStorage - what tables and indexes should be present?
On 02/01/2011 07:35 PM, Chris Withers wrote: On 01/02/2011 17:33, Shane Hathaway wrote: What's special about this storage? It sounds like RelStorage didn't get a chance to finish creating the schema. In MySQL, DDL statements are not transactional, so errors during schema creation (such as a timeout) leave a partial schema. Sounds like something to warn about in the docs for zodbconvert and storage creation... Partial schema creation is specific to MySQL and is not specific to RelStorage. MySQL has many pitfalls and there are far too many of them to list in the RelStorage documentation. Shane ___ For more information about ZODB, see the ZODB Wiki: http://www.zope.org/Wikis/ZODB/ ZODB-Dev mailing list - ZODB-Dev@zope.org https://mail.zope.org/mailman/listinfo/zodb-dev
Re: [ZODB-Dev] RelStorage and MySQL wait-timeout
On 02/01/2011 07:51 PM, Chris Withers wrote: I can understand the problem being fairly terminal if there was a disconnect *during* a timeout, and I'd expect an exception, but not a segfault ;-) I haven't seen segfaults except when the dynamic linker used an incorrect library. Use ldd to check the .so files generated during buildout. Shane ___ For more information about ZODB, see the ZODB Wiki: http://www.zope.org/Wikis/ZODB/ ZODB-Dev mailing list - ZODB-Dev@zope.org https://mail.zope.org/mailman/listinfo/zodb-dev
Re: [ZODB-Dev] RelStorage and PosKey errors - is this a risky hotfix?
Only to give some feedback: I had similar issues on past and now I follow some rules: - pack only with zodbpack - pack-gc = false - pack when the database is not been active update or not update at all. But it does not imply RelStorage bug, since MySQL is know to not be so safe. I'm happy that Chris Withers has been some investigation and test to discover possible issues. I have a large database (15G) and I can help running tests to give more feedback. Cheers, On Mon, Jan 24, 2011 at 6:02 PM, Anton Stonor an...@headnet.dk wrote: Hi there, We have recently experienced a couple of PosKey errors with a Plone 4 site running RelStorage 1.4.1 and Mysql 5.1. After digging down we found that the objects that were throwing PosKeyErrors actually existed in the object_state table with pickles etc, however not in the current_object table. After inserting the missing pointers into the current_object table, everything worked fine: mysql SELECT zoid, tid FROM object_state WHERE zoid=561701; +++ | zoid | tid | +++ | 561701 | 255267099158685832 | +++ mysql INSERT INTO current_object(zoid, tid) VALUES('561701', '255267099158685832'); Looks like it works -- but is this a safe way to fix PosKeyErrors? Now, I wonder why these pointers were deleted from the current_object table in the first place. My money is on packing -- and it might fit with the fact that we recently ran a pack that removed an unusual large amount of transactions in a single pack (100.000+ transactions). But I don't know how to investigate the root cause further. Ideas? /Anton ___ For more information about ZODB, see the ZODB Wiki: http://www.zope.org/Wikis/ZODB/ ZODB-Dev mailing list - ZODB-Dev@zope.org https://mail.zope.org/mailman/listinfo/zodb-dev -- Rudá Porto Filgueiras http://python-blog.blogspot.com http://twitter.com/rudaporto ___ For more information about ZODB, see the ZODB Wiki: http://www.zope.org/Wikis/ZODB/ ZODB-Dev mailing list - ZODB-Dev@zope.org https://mail.zope.org/mailman/listinfo/zodb-dev
Re: [ZODB-Dev] RelStorage - what tables and indexes should be present?
On Tue, Feb 1, 2011 at 3:52 PM, Shane Hathaway sh...@hathawaymix.org wrote: On 02/01/2011 07:35 PM, Chris Withers wrote: On 01/02/2011 17:33, Shane Hathaway wrote: What's special about this storage? It sounds like RelStorage didn't get a chance to finish creating the schema. In MySQL, DDL statements are not transactional, so errors during schema creation (such as a timeout) leave a partial schema. Sounds like something to warn about in the docs for zodbconvert and storage creation... Partial schema creation is specific to MySQL and is not specific to RelStorage. MySQL has many pitfalls and there are far too many of them to list in the RelStorage documentation. Just tell people to be aware that mysql has many problems, list some of them and don't use it in all the examples. -- Leonardo Santagada ___ For more information about ZODB, see the ZODB Wiki: http://www.zope.org/Wikis/ZODB/ ZODB-Dev mailing list - ZODB-Dev@zope.org https://mail.zope.org/mailman/listinfo/zodb-dev