Ape & MySQL: Error adding ZODB MountPoint
Hi, I want to add a ZODB MountPoint for an ape MySQL-Storage (My environment: MySQL-Server version 4.0.0-alpha-nt, Zope 2.7, WinXP). The MySQL-Server is running and I added a database named 'ape'. Trying to add a MySQL-connection to the Zope root object I always get an "Access denied for user: '@localhost' to database 'ape' " Error if I use just 'ape' as connection string. A connection is possible with a user from the acl_users. So I have some questions: 1. What should be the id for that MySQL-connection? 2. What user have to be added to acl_users? 3. What should be the connection string? Whenever I try to add a ZODB MountPoint I get the below error: Can anybody give some hints how to solve that? -- Thanks, Elena The error traceback: Error adding ZODB MountPoint Error Type: OperationalError Error Value: (1044, "Access denied for user: '@localhost' to database 'ape'") Error error_tb: Traceback (innermost last): * Module ZPublisher.Publish, line 100, in publish * Module ZPublisher.mapply, line 88, in mapply * Module ZPublisher.Publish, line 40, in call_object * Module Products.ZODBMountPoint.MountedObject, line 283, in manage_addMounts * Module Products.ZODBMountPoint.Mount, line 120, in _test * Module Products.ZODBMountPoint.Mount, line 86, in _getOrOpenObject * Module Products.ZODBMountPoint.Mount, line 70, in _getMountedConnection * Module Products.ZODBMountPoint.MountedObject, line 138, in _getDB * Module DBTab.DBTab, line 96, in getDatabase * Module DBTab.DBTab, line 113, in _createDatabase * Module Zope.Startup.datatypes, line 172, in open * Module Products.Ape.datatypes, line 48, in createDB * Module Products.Ape.datatypes, line 36, in open * Module apelib.zodb3.storage, line 44, in __init__ * Module apelib.core.io, line 55, in open_connections * Module apelib.sql.dbapi, line 291, in connect * Module <string>, line 0, in ? * Module MySQLdb, line 63, in Connect * Module MySQLdb.connections, line 115, in __init__ * Module MySQLdb.connections, line 41, in _make_connection OperationalError: (1044, "Access denied for user: '@localhost' to database 'ape'")
Elena Schulz wrote:
I want to add a ZODB MountPoint for an ape MySQL-Storage (My environment: MySQL-Server version 4.0.0-alpha-nt, Zope 2.7, WinXP). The MySQL-Server is running and I added a database named 'ape'.
Trying to add a MySQL-connection to the Zope root object I always get an "Access denied for user: '@localhost' to database 'ape' " Error if I use just 'ape' as connection string. A connection is possible with a user from the acl_users.
I'd sure appreciate it if someone would clear this up. Permissions in MySQL seem impossibly difficult to set up correctly. I found a hack that allows access by anybody, but I don't want to encourage that. I've been over the documentation on GRANT repeatedly, but MySQL still denies access. (In fact, I consistently got myself locked out as root, and my only recourse was to blow away the database.) What are the steps, starting with no /var/lib/mysql at all, that one should take to grant one user access to create, read, and write all tables in one database? Shane
Am 08.04.2004, 19:38 Uhr schrähb Shane Hathaway <shane@zope.com>:
What are the steps, starting with no /var/lib/mysql at all, that one should take to grant one user access to create, read, and write all tables in one database?
Admittedly I never used the GRANT-Syntax, instead I always worked directly with the tables. Assuming you connect to MySQL via localhost, user "myuser" with password "mypasswd" for database "mydb" at the MySQL prompt type: mysql> use mysql mysql> insert into db values -> ("localhost","mydb","myuser", -> "Y","Y","Y","Y","Y","Y","N","N","N","N","N"); mysql> insert into user values -> ("localhost","myuser",password("mypasswd"), -> "Y","Y","Y","Y","Y","N","N","N","N","N","N","N","N"); mysql> select * from host; should result in "Empty set [...]" Of course you'll have to create the database as well: mysql> create database mydb; Now exit from the MySQL prompt and execute mysqladmin reload; (optionally supplying the appropriate -u and -p parameters) and you should ready to go. Hope this helps. Jo. -- internetmanufaktur jo----------------------------- Berlin, Germany |||||||||||||||meder-------------------fon: ++49-30-417 17 63 33 http://www.meder.de/ ------------------- fax: ++49-30-417 17 63 45 Kollwitzstr. 75 ------------------------ mob: ++49-170- 2 98 89 97 10435 Berlin ---------------http://www.meder.de/keys/jo-pubkey.txt
Hi Shane and Jo, thanks for the input about adding users to mysql. But still I cannot get ape running with mysql getting the same error. So my questions are: 1. Do I need to add a Z MySQL Database Connection in the root or somewhere else or does ape add one by default? 2. If so what are the required parameters (id, user, password) if any? An example would be fine. 3. What has to be setup in the zope.conf concerning the mysql database? Something like: connect(db='ape' , user=?, password=?) May be a small explicit example would clear any doubt. With the setup below I can add a Z MySQL Database Connection with connectionstring 'ape myuser mypassword' , connect to the db 'ape' and querying a table 'test'. I do see a sql link in Control Panel -> Database Management but get the previous error when I click on that. The same happens if I try to add a ZODB MountPoint. -- any hint what to do next would be nice, Elena Up to now I have the following setup in the mysql-db: - db 'ape' created - added record in mysql.user with all permissions set to 'Y' Host User Password localhost % <empty> - added record in mysql.db with all permissions set to 'Y' Host Db User localhost ape % - add sql-section to zope.conf: # Sebi: added Ape-MySQL storage <ape-db sql> <ape-storage> mapper-variation sql <ape-dbapi-connection db> connection-class apelib.sql.dbapi.MySQLConnection module-name MySQLdb connect-expression connect(db='ape') </ape-dbapi-connection> </ape-storage> mount-point /sql </ape-db>
On Tue, 13 Apr 2004, Elena Schulz wrote:
thanks for the input about adding users to mysql. But still I cannot get ape running with mysql getting the same error. So my questions are:
1. Do I need to add a Z MySQL Database Connection in the root or somewhere else or does ape add one by default?
No, Ape does not use Zope's database adapters. It uses the Python DB-API module interface. You seem to already have everything you need installed.
2. If so what are the required parameters (id, user, password) if any? An example would be fine. 3. What has to be setup in the zope.conf concerning the mysql database? Something like: connect(db='ape' , user=?, password=?)
Perhaps. The problem has nothing to do with Ape. You need to figure out how to connect to MySQL. I would work on connecting using the "mysql" command-line client first. I'll try to give you pointers, but I don't have much expertise in this area. For testing, I wiped out my MySQL database and started over this way (as root): /etc/init.d/mysql stop rm -rf /var/lib/mysql mysql_install_db /etc/init.d/mysql start Then, still as root, I ran the "mysql" client. I typed this: create database ape; grant all privileges on ape.* to 'shane'@'localhost'; This seemed to give me access. I imagine a real MySQL install would need to lock things down better. Shane
Hi Shane, thanks for your answer ...
No, Ape does not use Zope's database adapters. It uses the Python DB-API module interface. You seem to already have everything you need installed. Fine
Something like: connect(db='ape' , user=?, password=?) I tried: connect(db='ape' , user=myUser) and it worked (means the user was detected) but I didn't find a way to specify the password yet. password, pword or pw were not accepted as params. I'll try to find in the Python DB-API.
Perhaps. The problem has nothing to do with Ape. You need to figure out how to connect to MySQL. I would work on connecting using the "mysql" command-line client first.
Ok, at least I know where to start ...
I'll try to give you pointers, but I don't have much expertise in this area. For testing, I wiped out my MySQL database and started over this way (as root):
/etc/init.d/mysql stop rm -rf /var/lib/mysql mysql_install_db /etc/init.d/mysql start I'm using windows so it'll be different, but I'll try to connect via the mysql_python api.
-- thanks again, Elena
----- Original Message ----- From: "Elena Schulz" <elena.schulz@gmx.net> To: "Shane Hathaway" <shane@zope.com>; <zope@zope.org> Sent: Wednesday, April 14, 2004 5:35 AM Subject: Re: [Zope] Ape & MySQL: Error adding ZODB MountPoint
Hi Shane,
thanks for your answer ...
No, Ape does not use Zope's database adapters. It uses the Python DB-API module interface. You seem to already have everything you need installed. Fine
Something like: connect(db='ape' , user=?, password=?) I tried: connect(db='ape' , user=myUser) and it worked (means the user was detected) but I didn't find a way to specify the password yet. password, pword or pw were not accepted as params. I'll try to find in the Python DB-API.
db0=MySQLdb.connect(host="servername",user="user",passwd="password",db="data base") my 2 cents / hth bobb
Perhaps. The problem has nothing to do with Ape. You need to figure
out
how to connect to MySQL. I would work on connecting using the "mysql" command-line client first. Ok, at least I know where to start ...
I'll try to give you pointers, but I don't have much expertise in this area. For testing, I wiped out my MySQL database and started over this way (as root):
/etc/init.d/mysql stop rm -rf /var/lib/mysql mysql_install_db /etc/init.d/mysql start I'm using windows so it'll be different, but I'll try to connect via the mysql_python api.
-- thanks again, Elena
_______________________________________________ Zope maillist - Zope@zope.org http://mail.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://mail.zope.org/mailman/listinfo/zope-announce http://mail.zope.org/mailman/listinfo/zope-dev )
Hi Bobb, hi Shane,
db0=MySQLdb.connect(host="servername",user="user",passwd="password",db="dat a base") This works well, but Shane there is the below bug in apelib.sql.dbapi.py. With the given patch it works for me :-).
Now the one 'set of problems' remain for me. That is customizing the serializers/deserializers to transfer the data columns in the data and property-tables from blob's to readable text so it can be used by external applications. -- Anyway thanks a lot for your help so far, Elena. Adding this to zope.conf and changing dbapi.py works with mysql (postgresql not tested) <ape-db sql> <ape-storage> mapper-variation sql <ape-dbapi-connection db> connection-class apelib.sql.dbapi.MySQLConnection module-name MySQLdb connect-expression connect(db='ape', user='myUserName', passwd='myPassWord', host="localhost") </ape-dbapi-connection> </ape-storage> mount-point /sql </ape-db> Patch for dbapi.py: def create_table(self, table, column_defs): """Creates a table. """ table_name = self.prefix + table cols = [] pkeys = [] for name, typ, unique in column_defs: col = self.translate_name(name) db_type = self.translate_type(typ) ################Elena: changed, cause mysql needs NOT NULL on primary keys null_constr = '' if unique: null_constr = ' NOT NULL' pkeys.append(col) cols.append("%s %s%s" % (col, db_type, null_constr)) ######################################## if pkeys: cols.append('PRIMARY KEY (%s)' % ', '.join(pkeys)) sql = "CREATE TABLE %s (%s)" % (table_name, ', '.join(cols)) self.execute(sql) Error without the patch while creating a table by ape: Traceback (innermost last): File L:\Zope\Zope-2.7.0\lib\python\ZPublisher\Publish.py, line 163, in publish_module_standard File L:\Zope\ZopeData\Products\Localizer\__init__.py, line 58, in new_publish File L:\Zope\Zope-2.7.0\lib\python\ZPublisher\Publish.py, line 127, in publish File L:\Zope\Zope-2.7.0\lib\python\Zope\App\startup.py, line 203, in zpublisher_exception_hook (Object: Database) File L:\Zope\Zope-2.7.0\lib\python\ZPublisher\Publish.py, line 91, in publish File L:\Zope\Zope-2.7.0\lib\python\ZPublisher\BaseRequest.py, line 302, in traverse File L:\Zope\Zope-2.7.0\lib\python\App\ApplicationManager.py, line 122, in __bobo_traverse__ (Object: Database) File L:\Zope\Zope-2.7.0\lib\python\App\ApplicationManager.py, line 113, in __getitem__ (Object: Database) File L:\Zope\Zope-2.7.0\lib\python\DBTab\DBTab.py, line 96, in getDatabase File L:\Zope\Zope-2.7.0\lib\python\DBTab\DBTab.py, line 113, in _createDatabase File L:\Zope\Zope-2.7.0\lib\python\Zope\Startup\datatypes.py, line 172, in open File L:\Zope\Zope-2.7.0\lib\python\Products\Ape\datatypes.py, line 48, in createDB File L:\Zope\Zope-2.7.0\lib\python\Products\Ape\datatypes.py, line 36, in open File L:\Zope\Zope-2.7.0\lib\python\Products\Ape\lib\apelib\zodb3\storage.py, line 45, in __init__ File L:\Zope\Zope-2.7.0\lib\python\Products\Ape\lib\apelib\core\io.py, line 86, in init_databases File L:\Zope\Zope-2.7.0\lib\python\Products\Ape\lib\apelib\sql\sqlbase.py, line 54, in init File L:\Zope\Zope-2.7.0\lib\python\Products\Ape\lib\apelib\sql\sqlbase.py, line 45, in create File L:\Zope\Zope-2.7.0\lib\python\Products\Ape\lib\apelib\sql\dbapi.py, line 235, in create_table File L:\Zope\Zope-2.7.0\lib\python\Products\Ape\lib\apelib\sql\dbapi.py, line 276, in execute File I:\Python\ActivePython232\Lib\site-packages\MySQLdb\cursors.py, line 95, in execute File I:\Python\ActivePython232\Lib\site-packages\MySQLdb\cursors.py, line 114, in _execute File I:\Python\ActivePython232\Lib\site-packages\MySQLdb\connections.py, line 33, in defaulterrorhandler IntegrityError: (1171, 'All parts of a PRIMARY KEY must be NOT NULL; If you need NULL in a key, use UNIQUE instead')
On Thu, 15 Apr 2004, Elena Schulz wrote:
connect(host="servername",user="user",passwd="password",db="database")
Thanks, I've incorporated that example into component.xml.
This works well, but Shane there is the below bug in apelib.sql.dbapi.py. With the given patch it works for me :-).
The patch has been applied, thanks.
Now the one 'set of problems' remain for me. That is customizing the serializers/deserializers to transfer the data columns in the data and property-tables from blob's to readable text so it can be used by external applications.
Ape chooses a serialization strategy for each object according to the configuration files named apeconf.xml. For example, if you add a standard ZopePageTemplate to a folder in an Ape-managed database, Ape decomposes it for storage using components configured in apeconf.xml. Read outline.txt and apexml.txt to learn how to teach Ape about more object types. Shane
Hi Shane,
Thanks, I've incorporated that example into component.xml. The patch has been applied, thanks. Welcome, I'm happy if it's useful.
Read outline.txt and apexml.txt to learn how to teach Ape about more object types. I'll do that and maybe ask some more questions. But I'd like to change the configuration of existing object types like any properties, Python Scripts, Page Templates etc. Should I change the default apeconf.xml then?
-- Elena
On Sun, 18 Apr 2004, Elena Schulz wrote:
Thanks, I've incorporated that example into component.xml. The patch has been applied, thanks. Welcome, I'm happy if it's useful.
Read outline.txt and apexml.txt to learn how to teach Ape about more object types. I'll do that and maybe ask some more questions. But I'd like to change the configuration of existing object types like any properties, Python Scripts, Page Templates etc. Should I change the default apeconf.xml then?
I'd like to get it to the point that you can augment the configuration in another file, but right now it's probably simpler to just change apeconf.xml. Shane
participants (4)
-
Bobb -
Elena Schulz -
Jo Meder -
Shane Hathaway