Re: Restore data from MySQL data files
I found that the database I was primarily interested in only had a MyISAM table (.myd and .myi file), nothing in InnoDB. So I first copied over that database directory to my other MySQL instance and restarted it. MySQL Workbench hang a bit when expanding its tree, so I dropped the database, recreated it and then copied the table in and restarted. Works fine now. I have restored the database in this way on the new server machine and the application works again. :-) Next time we'll have a more reliable backup... -- Yves Goergen LonelyPixel nospam.l...@unclassified.de Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Restore data from MySQL data files
Hi, Recently a home server has crashed and would not boot anymore into Windows. We decided to replace it altogether and I managed to repair the file system more or less so that I could copy some directories from the broken disk. One of them is the MySQL data directory of that XAMPP installation. I have now set up a new computer and tried to use the old data files but the MySQL service doesn't start. The old machine was an unknown MySQL version on Windows XP, the new is running MySQL 5.5.16 on Windows 7 (32 bit). The error log is attached to this mail. It all sounds like real bugs in MySQL, at least it's standing in the file. I already ran mysql_upgrade.exe but it didn't change anything. What can I do now to restore that data? -- Yves Goergen LonelyPixel nospam.l...@unclassified.de Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Restore data from MySQL data files
On 09.11.2011 21:58 CE(S)T, Reindl Harald wrote: Am 09.11.2011 21:46, schrieb Yves Goergen: The old machine was an unknown MySQL version on Windows XP pfff - unknown version? how comes? The old MySQL installation doesn't run anymore because the OS is gone. I could only backup the files. The error log is attached to this mail. It all sounds like real bugs in MySQL, at least it's standing in the file there is no attachment so please CLEAR the log, try again restart and post the log here inline Done, here it is: 09 22:49:59 [Note] Plugin 'FEDERATED' is disabled. c:\xampp\mysql\bin\mysqld.exe: Table 'mysql.plugin' doesn't exist 09 22:49:59 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it. (That's what I did.) 09 22:49:59 InnoDB: The InnoDB memory heap is disabled 09 22:49:59 InnoDB: Mutexes and rw_locks use Windows interlocked functions 09 22:49:59 InnoDB: Compressed tables use zlib 1.2.3 09 22:49:59 InnoDB: Initializing buffer pool, size = 16.0M 09 22:49:59 InnoDB: Completed initialization of buffer pool 09 22:49:59 InnoDB: highest supported file format is Barracuda. InnoDB: Resetting space id's in the doublewrite buffer 09 22:49:59 InnoDB: Error: trying to access tablespace 1 page no. 2, InnoDB: but the tablespace does not exist or is just being dropped. 09 22:49:59 InnoDB: Error: trying to access tablespace 1 page no. 2, InnoDB: but the tablespace does not exist or is just being dropped. 09 22:49:59 InnoDB: Error: trying to access tablespace 1 page no. 2, InnoDB: but the tablespace does not exist or is just being dropped. 09 22:49:59 InnoDB: Error: trying to access tablespace 1 page no. 2, InnoDB: but the tablespace does not exist or is just being dropped. 09 22:49:59 InnoDB: Error: trying to access tablespace 1 page no. 2, InnoDB: but the tablespace does not exist or is just being dropped. 09 22:49:59 InnoDB: Error: trying to access tablespace 1 page no. 2, InnoDB: but the tablespace does not exist or is just being dropped. 09 22:49:59 InnoDB: Error: trying to access tablespace 1 page no. 2, InnoDB: but the tablespace does not exist or is just being dropped. 09 22:49:59 InnoDB: Error: trying to access tablespace 1 page no. 2, InnoDB: but the tablespace does not exist or is just being dropped. 09 22:49:59 InnoDB: Error: trying to access tablespace 1 page no. 2, InnoDB: but the tablespace does not exist or is just being dropped. 09 22:49:59 InnoDB: Error: trying to access tablespace 1 page no. 2, InnoDB: but the tablespace does not exist or is just being dropped. 09 22:49:59 InnoDB: Error: trying to access tablespace 1 page no. 2, InnoDB: but the tablespace does not exist or is just being dropped. 09 22:49:59 InnoDB: Error: trying to access tablespace 1 page no. 2, InnoDB: but the tablespace does not exist or is just being dropped. 09 22:49:59 InnoDB: Error: trying to access tablespace 1 page no. 2, InnoDB: but the tablespace does not exist or is just being dropped. 09 22:49:59 InnoDB: Error: trying to access tablespace 1 page no. 2, InnoDB: but the tablespace does not exist or is just being dropped. 09 22:49:59 InnoDB: Error: trying to access tablespace 1 page no. 2, InnoDB: but the tablespace does not exist or is just being dropped. 09 22:49:59 InnoDB: Error: trying to access tablespace 1 page no. 2, InnoDB: but the tablespace does not exist or is just being dropped. 09 22:49:59 InnoDB: Error: trying to access tablespace 1 page no. 2, InnoDB: but the tablespace does not exist or is just being dropped. 09 22:49:59 InnoDB: Error: trying to access tablespace 1 page no. 2, InnoDB: but the tablespace does not exist or is just being dropped. 09 22:49:59 InnoDB: Error: trying to access tablespace 1 page no. 2, InnoDB: but the tablespace does not exist or is just being dropped. 09 22:49:59 InnoDB: Error: trying to access tablespace 1 page no. 2, InnoDB: but the tablespace does not exist or is just being dropped. 09 22:49:59 InnoDB: Error: trying to access tablespace 1 page no. 2, InnoDB: but the tablespace does not exist or is just being dropped. 09 22:49:59 InnoDB: Error: trying to access tablespace 1 page no. 2, InnoDB: but the tablespace does not exist or is just being dropped. 09 22:49:59 InnoDB: Error: trying to access tablespace 1 page no. 2, InnoDB: but the tablespace does not exist or is just being dropped. 09 22:49:59 InnoDB: Error: trying to access tablespace 1 page no. 2, InnoDB: but the tablespace does not exist or is just being dropped. 09 22:49:59 InnoDB: Error: trying to access tablespace 1 page no. 2, InnoDB: but the tablespace does not exist or is just being dropped. 09 22:49:59 InnoDB: Error: trying to access tablespace 1 page no. 2, InnoDB: but the tablespace does not exist or is just being dropped. 09 22:49:59 InnoDB
Slow MySQL Workbench startup
Hello, Why does it take ages for MySQL Workbench to startup? When I start it (5.2.31 CE) on Windows (XP), the splash screen sits there around 15-30 seconds until the main window appears. There's moderate to high CPU usage in this time and the hard disk is active. I believe this has been much faster in the past. -- Yves Goergen LonelyPixel nospam.l...@unclassified.de Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Select from the table being updated
Hi, why doesn't MySQL support selecting from the table being updated? The following and any variations of it doesn't work: UPDATE t1 (c1) VALUES (SELECT MAX(c1) FROM t1); This restriction is documented here: http://dev.mysql.com/doc/refman/5.5/en/update.html (at the very bottom) Wouldn't it be extremely useful to do that? If I want to copy a row and only do minor changes to a single column, I don't want to read the other huge column out to the client, only put write it back to the database again unchanged. Or in the above example, I could find the next value, like here: UPDATE t1 (c1, c2) VALUES ((SELECT MAX(c1) + 1 FROM t1 WHERE c2 = 5), 5); Is it planned at all to add that? Is it really so complicated to do it that it hasn't been done all those years? The MySQL bug tracker was unable to filter the bugs down to less than a few thousands, so I'm posting it here. -- Yves Goergen LonelyPixel nospam.l...@unclassified.de Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
On 21.12.2010 11:27 CE(S)T, 杨涛涛 wrote: Maybe you should give a password to MySQL's root. And I think this note will disappear. I would be extremely dumb not to set any password on MySQL's root account at all! Of course (I hope I made that clear before) a password has been set while installing the MySQL package. The point is that this message is plain wrong and should be removed altogether until it works as it claims. -- Yves Goergen LonelyPixel nospam.l...@unclassified.de Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
On 14.12.2010 20:36 CE(S)T, Alejandro Bednarik wrote: Are you using ubuntu deb's or mysql bin? Do you get that message when the service start? Look init script to see what it does. I get the message when MySQL is started. I've installed the Ubuntu standard package mysql-server-5.1. It has already asked for a root password during package configuration and I did enter some password there. I just tried to grep my whole filesystem for parts of this message but either grep failed allocating memory (there's plenty left!) or it didn't find the text in a file I could start something with. So I still don't know where the message comes from! -- Yves Goergen LonelyPixel nospam.l...@unclassified.de Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
On 13.12.2010 15:26 CE(S)T, who.cat wrote: try /mysql_bin_path/mysql_secure_installation ! Hope it helpfu! I already followed that path and it doesn't quite help. I've done the checks that the install script does and my installation is secure by those means. But I don't want to install things. The server is already up and running in production. I only want to get rid of that whole load of messages posted to syslog. I don't know where they come from to do further analysis. -- Yves Goergen LonelyPixel nospam.l...@unclassified.de Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
Hi, I find the following line in my syslog events: Daemon Error mysqld PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER ! followed by a whole lot of notes, advice and web links. I do have set a custom password for root, root is not accessible from remote, the anonymous user does not exist and a database named test also does not exist. Yet still I see this message when starting the MySQL server. How can I get rid of it? MySQL 5.1 on Ubuntu 10.04 -- Yves Goergen LonelyPixel nospam.l...@unclassified.de Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: How to use SSL? (SSL is enabled but not used)
On 18.08.2010 20:42 CE(S)T, Mark Matthews wrote: For what it's worth, the MySQL JDBC driver has had client-side SSL require (i.e. requireSSL=true) since 2003 and the ADO.Net driver has had SSL Mode=Required since 2009. Cool, so would it be possible to also have this in the MySQL Workbench client which seems to be .NET-based? -- Yves Goergen LonelyPixel nospam.l...@unclassified.de Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: How to use SSL? (SSL is enabled but not used)
On 18.08.2010 00:02 CE(S)T, Anders Kaseorg wrote: This same issue was reported back in 2004 and ignored: http://bugs.mysql.com/bug.php?id=3138 Oh dear, 2004... I think this is a serious security problem that demands more attention than dismissal as documented behavior. To solve it, there needs to be a way to force the use of SSL from the client side. I have another suggestion: remove SSL support from MySQL alltogether and declare the protocol as unsafe and only use it over secure networks like VPN. Since MySQL is now Oracle and it's not Oracle's main business, regarding recent bad news about Oracle, we can imagine what will happen this time. Exactly! Nothing. (Oh look, the MySQL guy already has an oracle.com e-mail address...) -- Yves Goergen LonelyPixel nospam.l...@unclassified.de Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: How to use SSL? (SSL is enabled but not used)
Does anybody know how to use SSL-secured connections to a MySQL server? Has anybody done that at all? In the manual I have now found the following statement: http://dev.mysql.com/doc/refman/5.1/en/ssl-options.html Note that use of --ssl does not require an SSL connection. For example, if the server or client is compiled without SSL support, a normal unencrypted connection is used. What's that supposed to mean? If there's no way to force the connection into SSL, it is entirely useless. Anyone on the wire could simply pretend that the server doesn't support SSL and so deny the encryption and the client wouldn't even care... I don't want to use REQUIRE SSL for an account that is regularly used locally and doesn't need SSL. SSL should really be selected by the client per connection when connecting from some other untrusted network. The whole SSL thing looks pretty unfinished like that. -- Yves Goergen LonelyPixel nospam.l...@unclassified.de Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
How to use SSL? (SSL is enabled but not used)
Hello, I have setup a MySQL 5.1 server on Ubuntu Linux 10.4 and created an SSL certificate and key. I updated the MySQL configuration to point to the SSL files. There's no error message at startup in MySQL's error log. (Before I granted the process access to the SSL files through AppArmor, there was an error message that it couldn't get the SSL files, so I assume MySQL really reads the file now.) In MySQL Workbench 5.2.25 I enabled the use of SSL for the connection, but the statement show variables like '%ssl%' doesn't show a value for ssl_cipher. Here's the entire output: have_opensslYES have_sslYES ssl_ca ssl_capath ssl_cert/etc/ssl/private/cert-.de ssl_cipher ssl_key /etc/ssl/private/cert-.de From the wording in the client, I believe that the SSL option is pretty much useless. It reads that it will use SSL if it's available [in the client library]. It probably wouldn't use it too if the server didn't support it. So in the end, it may or may not use SSL to its own liking. Where's the switch where I can force the use of SSL? I don't want to send my authentication data in plain text over the network before I can even verify that SSL is in use? And still why doesn't it use SSL in my case? -- Yves Goergen LonelyPixel nospam.l...@unclassified.de Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Slow query, unknown why
Hi, I'm still stuck with my SQL query that is slow but really shouldn't be. The problem is that I cannot create a simple test case. I could only provide you a whole lot of pages of PHP code and SQL queries to explain the problem. I have now three versions of my query. One with a sub select, which takes 40 ms and works. One with a left join instead, which takes 40 ms and works. And one with an inner join instead, which takes 3 ms and doesn't work. The number of left-joined rows should be around 5, so what can make it take 35 ms to join those handful of rows? MySQL server version is 5.0.67 and 5.1.41 (just updated). Here's a small impression of my query: SELECT t.TagId, t.TagName, tk.UserId FROM message_revision_tag mrt JOIN tag t USING (TagId) LEFT JOIN keylist tk ON -- Here's the left join (tk.KeylistId = t.ReadAccessKeylistId AND tk.UserId IN (22943, 10899)) WHERE mrt.MessageId = 72 AND mrt.RevisionNumber = 1 AND t.ReadAccessKeylistId IS NOT NULL; This is only a sub-query of a larger search query in my PHP application. MySQL workbench can't show query timings so I can'T say how long this part of the query takes. It's probably fast, but it is applied to ~600 other rows to determine whether they should be included in the results or not. -- Yves Goergen LonelyPixel nospam.l...@unclassified.de Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Oracle , what else ?
On 21.04.2009 18:40 CE(S)T, mos wrote: At 08:06 AM 4/21/2009, Gilles MISSONNIER wrote: what are we gonna run as RDBMS ? It seems like the little fish are getting eaten by the bigger fish. I understand Microsoft is now going to buy Oracle. :-) (Sorry, just kidding) No, that would be funny. Microsoft buying Oracle - the new world software company name would be Miracle then! :-D Of course, Oracle will have bought IBM for their DB2 system and Java affinity before, and Microsoft will as well have bought Adobe for their PDF and Flash technologies. Then, MySQL is going to be abandoned by Miracle (they still have MSSQL, which may be a re-labelled DB2 with full PL/SQL compatibility then...) and a new small company is taking over the Open Source MySQL development... At least my crystal ball home oracle says so. But maybe I should clean it again to see things more accurately. ;-) -- Yves Goergen LonelyPixel nospam.l...@unclassified.de Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: MySQL GUI Tools for 5.0 Release 16 for Windows available for download
On 31.01.2009 14:34 CE(S)T, Mike Lischke wrote: we are pleased to announce a new maintenance release of the legacy GUI Tools Bundle. Why is this called legacy? What's the newer/better alternative? I could not find anything like this on the MySQL website. Workbench is an E/R modeling tool. The only screenshot of it shows a database diagram. No users, tables or instances management. Enterprise Monitor seems to be a monitor only, and it's not free. -- Yves Goergen LonelyPixel nospam.l...@unclassified.de Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Inno Setup script for MySQL and MySQL ODBC?
On 20.12.2008 00:51 CE(S)T, Steve Holmes wrote: 2008/12/19 Menachem Bazian gro...@bcconsultingservices.com Does anyone have an inno setup script so I can automatically install MySQL with an application? http://lists.mysql.com/mysql?unsub=sholme...@gmail.com I don't know about anyone else, but I don't know what you mean by inno setup script. Please be more specific. Steve. I assume he means the files to be copied and actions to be performed to install the MySQL server and ODBC driver, in InnoSetup language. Use your favourite web search engine to find http://www.jrsoftware.org/isinfo.php. If you've never heard of InnoSetup before, you're not likely to have the requested script handy. To the OP: Maybe you can find further assistence at the XAMPP project, they include the MySQL server in their installer. The ODBC driver setup could be packaged and run as external application with like /silent. Maybe even the entire MySQLd setup works this way, so you only need to put those two installer EXEs in your application setup and run them in a more or less unattended mode. -- Yves Goergen LonelyPixel nospam.l...@unclassified.de Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: MySQL server statistics
On 21.07.2008 22:52 CE(S)T, Benjamin Wiechman wrote: With 5.1 you have more control over general query log and the slow query log - enable or disable at runtime, output to file or DB table. Okay, now that sounds a lot better. Waiting for 5.1 GA then. Although it only provides the very basics. I still have to postprocess/aggregate the log to find who did what and how long that all took. Or - looking at your original question it may be able to narrow down the source of the queries if you can graph your data more often - maybe every 10-15 seconds instead of a longer interval to help you profile which applications are hammering your db server. This is a webhosting services machine. I don't know who of the users is doing what and what applications they have installed. Recently I found one of my applications being vulnerable to senseless recursive bot requests that took quite a bit of the time. That was by chance because I had also looked into the web server requests diagramm, which I can separate by users. And in that case, my colour was clearly visible... ;) So maybe it will already help a lot to see for which user account the database server is working most in a certain time range. -- Yves Goergen LonelyPixel [EMAIL PROTECTED] Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL server statistics
On 20.07.2008 23:49 CE(S)T, Rob Wultsch wrote: On Sun, Jul 20, 2008 at 1:33 PM, Yves Goergen [EMAIL PROTECTED] wrote: Hello, I've installed MySQL server 5.0 and have written a small statistics script that regularly checks the number of connections and queries to the server, which I can then view in a diagram. But sometimes it just says that at a time, unusually many connections or queries have been made to the server. I cannot see what causes them. Neither the user nor the actual queries. At work I got in touch with the Oracle Enterprise Manager recently. I haven't looked at it too closely yet, but I think it could give useful information about each session, what it does and more importantly what it did. I have no idea what to search for to get this information from the MySQL server. So I had to ask here first. Is there any method to get those statistics? I don't mean the SHOW PROCESSES list, it only contains a snapshot of the very moment when MySQL got to execute my command. I mean information about recent activity, like 15 minutes, 2 hours or so. Check out the general query log: http://dev.mysql.com/doc/refman/5.0/en/query-log.html As far as I have understood that, there is only one log that can be turned on or off. When turned on, it logs every single query sent to the server. I hope it also logs some other data, like the username and maybe the client address (local/remote). But this has one major drawback: To enable or disable the log (which, I can imagine, takes quite a bit performance) the whole server must be stopped and restarted. This isn't really an option for a production database server, just to know what's going on from time to time. -- Yves Goergen LonelyPixel [EMAIL PROTECTED] Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL server statistics
Hello, I've installed MySQL server 5.0 and have written a small statistics script that regularly checks the number of connections and queries to the server, which I can then view in a diagram. But sometimes it just says that at a time, unusually many connections or queries have been made to the server. I cannot see what causes them. Neither the user nor the actual queries. At work I got in touch with the Oracle Enterprise Manager recently. I haven't looked at it too closely yet, but I think it could give useful information about each session, what it does and more importantly what it did. I have no idea what to search for to get this information from the MySQL server. So I had to ask here first. Is there any method to get those statistics? I don't mean the SHOW PROCESSES list, it only contains a snapshot of the very moment when MySQL got to execute my command. I mean information about recent activity, like 15 minutes, 2 hours or so. -- Yves Goergen LonelyPixel [EMAIL PROTECTED] Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: #1005 - Can't create table '.\unb2test\#sql-770_2.frm' (errno: 150)
On 14.03.2008 00:55 CE(S)T, Rob Wultsch wrote: I am guessing it is an issue with SearchRevision being an INTEGER, and RevisionNumber being a SMALLINT. Thank you, that was the problem. My design was incorrect anyway to use different types here... Now that's fixed, too. :) http://www.google.com/search?q=150+error+mysql yields http://bugs.mysql.com/bug.php?id=6188 as it's first result. Not so in Germany. ;) Also, I've searched for a more specific message. Must have missed it. -- Yves Goergen LonelyPixel [EMAIL PROTECTED] Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
#1005 - Can't create table '.\unb2test\#sql-770_2.frm' (errno: 150)
Hello, I'm using MySQL 5.0 on Windows XP, with a few InnoDB tables and would like to create a new foreign key constraint to one table. Here's a simplified structure: CREATE TABLE message ( MessageId INTEGER UNSIGNED NOT NULL PRIMARY KEY, Owner INTEGER UNSIGNED NOT NULL, SearchRevision INTEGER UNSIGNED) ENGINE 'InnoDB' CHARACTER SET 'utf8' COLLATE 'utf8_bin'; CREATE TABLE message_revision ( MessageId INTEGER UNSIGNED NOT NULL, RevisionNumber SMALLINT UNSIGNED NOT NULL, Author INTEGER UNSIGNED NOT NULL, PRIMARY KEY (MessageId, RevisionNumber)) ENGINE 'InnoDB' CHARACTER SET 'utf8' COLLATE 'utf8_bin'; ALTER TABLE message_revision ADD FOREIGN KEY (MessageId) REFERENCES message (MessageId) ON DELETE CASCADE; This is all fine and I've put some data in the tables already. But all data is valid and won't interfer with the following new constraint: ALTER TABLE message ADD FOREIGN KEY (MessageId, SearchRevision) REFERENCES message_revision (MessageId, RevisionNumber) ON DELETE CASCADE; Now the last command produces this error: #1005 - Can't create table '.\unb2test\#sql-770_2.frm' (errno: 150) I've restarted the MySQL service but it doesn't help. Is it broken? Why is it trying to create some random table and why does that fail? -- Yves Goergen LonelyPixel [EMAIL PROTECTED] Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Unicode sorting and binary comparison, please!
On 06.03.2008 15:15 CE(S)T, Paul DuBois wrote: Here's some advice from Alexander Barkov: You might be able to use a particular collation to achieve what you want. For example, latin1_general_ci. You can take a look at its collation chart here: http://www.collation-charts.org/mysql60/mysql604.latin1_general_ci.html As you can see, all accented letters are considered as separate letters. So when you do: SELECT ... WHERE a='a' you only get 'a' and 'A'. But you wan't get any other variants of the letter 'a', That doesn't support Unicode, right? So it could sort a, ä and à but not ā, ă and α. Unicode-capability is a must for my application. -- Yves Goergen LonelyPixel [EMAIL PROTECTED] Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Unicode sorting and binary comparison, please!
On 03.03.2008 10:27 CE(S)T, Anders Karlsson wrote: [a lot about why sorting unicode is complicated] If you want to accknowledge exact matching, and say any character, accented / unlauted etc, is different from any other character, specifiy a binary comparison: SELECT * FROM phonebook WHERE BINARY name = 'Handel'; Hm, not quite compatible. The solution I found is using this: SELECT * FROM table WHERE column = 'value' COLLATE ...; But still there binary collation has a different name on MySQL and SQLite. PostgreSQL doesn't support the COLLATE clause, although part of the SQL-92 standard. But you din't quite get my actual problem. You said that sorting Unicode things is complicated. I agree. I can live with a trade-off for sorting. But I cannot accept incorrect selection of records. When I want something that I can specify exactly, I only want to get that back, nothing else. The same counts for uniqueness constrains. I've asked a freind who could test the matter with PostgreSQL. He said, it works exactly as expected. Sorting is unicode-like, selection is precise. Why can't MySQL do that, too? Is it so hard to distinguish sorting and selecting? -- Yves Goergen LonelyPixel [EMAIL PROTECTED] Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Unicode sorting and binary comparison, please!
On 03.03.2008 23:17 CE(S)T, Anders Karlsson wrote: And you are right of course, you may use the COLLATE keyword also, to enforce a certain collation, although if you want BINARY, I think using BINARY might be slightly more effective. I was also considering compatibility with other DBMS. At least SQLite only supports the COLLATE syntax. I'm unable to find out whether the BINARY keyword is part of SQL-92, because it appears too often in it. But since MySQL seems to be the only system (of those I have tested now) that requires such special care anyway, using BINARY only here could also work. What about a feature request to allow WHERE clauses to use a different collations than the one used for ORDER BY. So collation_connection controls the ORDER BY collation, and then I could say SET collation_connection_comparison = 'utf8_bin'. That would do what you want basically, and I think there might possibly be a need for this. That would effectively be what I originally wanted. Use Unicode for sorting things, but do not use Unicode for comparing with the = operator. LIKE may work with Unicode, as its name already implies a level of fuzzyness. I'd expect LIKE to return more than one record on a unique column. But I always expect = to work as in other programming languages, as in maths and anywhere else: absolute equality, not just something similar. So I'd be happy with such an option. Where can I vote for it? :) Is there a chance to see it in a MySQL 5.0 version? Meanwhile, I have chosen to use utf8_bin for all my tables. This breaks sorting for some few cases (but it hasn't really been a problem back in the non-Unicode-MySQL days) but in exchange finds only what I want to find. -- Yves Goergen LonelyPixel [EMAIL PROTECTED] Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Unicode sorting and binary comparison, please!
Hello,
I've just read through the MySQL documentation about Unicode support,
collations and how it affects sorting and comparison of strings. And I
find it horrible, at least. I feel like I'm back in the MySQL 3.x days
where I used UTF-8 in my application and MySQL treated it binary. The
only problem was incorrect sorting of things. Today we have UTF-8
support in MySQL, which brings correct sorting (for whatever definition
of correct) but has taken correct comparison again.
When I have three strings, e.g. Handel, Händel and Hendel, I'd
like to have them sorted correctly. Using the utf8_{general,unicode}_ci
collation seems the only way. Now when I want the row with Handel in
it, I'll get two rows back. One of them is not what I wanted. So
strictly, the result is incorrect. The only way to get this right is
using the utf8_bin collation. But this again makes correct sorting
impossible.
It's a nightmare. Why can't I get correct sorting *and* correct (i.e.
precise) comparison in one?
If I cannot even rely on the = operator, what good is a text-storing
database? There even isn't a case-sensitive unicode collation other than
utf8_bin. This means that in every database application that uses
unicode, I cannot separate lower from uppercase when retrieving stuff.
MySQL is simply blind for that. Not to mention different characters that
Unicode, MySQL, DIN, ISO or whoever think are the same, but they aren't.
If they were the same, you wouldn't need both of them.
Finally, my application should really be portable. I haven't looked into
how other DBMS handle it and whether the SQL syntax would be the same,
should there be any method on the language layer to do it right. I only
know that SQLite stores in UTF-8 but otherwise doesn't care about
Unicode, i.e. sorting should be broken, comparison is correct.
PostgreSQL didn't find its own columns again, so I cancelled the test.
--
Yves Goergen LonelyPixel [EMAIL PROTECTED]
Visit my web laboratory at http://beta.unclassified.de
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
INTERSECT
Hello, are there any plans for MySQL to support the INTERSECT command like most other DBMS (Oracle, PostgreSQL, SQLite) do? I've found a work-around to use an inner join, but I'm not sure how easy it is to adapt it to my situation. I'm intersecting rows from a single table, doing a lot of iterations. And the INTERSECT keyword is a much nicer and easier to read way of doing it. I'm using MySQL 5.0. -- Yves Goergen LonelyPixel [EMAIL PROTECTED] Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Inefficient query processing?
On 11.02.2008 19:51 CE(S)T, Perrin Harkins wrote: On Feb 10, 2008 5:30 PM, Yves Goergen [EMAIL PROTECTED] wrote: My problem is that the sub-select in line 7 (SELECT 1) takes a rather long time. (When I remove it, it's much faster.) This is a known issue with EXISTS/NOT EXISTS subqueries in MySQL, which has some fixes slated for MySQL 6. In the meantime, there are ways to rewrite most of these queries using JOIN/LEFT JOIN. Read some of the articles on http://xaprb.com/ about subqueries for a more detailed explanation and examples of rewrites. Thank you for the link. Is there some way to get only the headlines and a summary for all entries? Reading through the entire contents by month and finding the misleading captions is hard work for such masses of content. The search function didn't give me the desired results. -- Yves Goergen LonelyPixel [EMAIL PROTECTED] Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Inefficient query processing?
On 11.02.2008 20:13 CE(S)T, Peter Brawley wrote: If user.additionalkeylist and tag.readaccesskeylist are not lists, naming them `...list` misleads distracts. Well, these fields contain KeylistId values from the keylist table, so I thought naming them *Keylist would be good enough. But on (i), how user.additionalkeylist and tag.readaccesskeylist work remains confusing. You appear to say access may come from ... (i) message-message_revision-message_revision_tag.readaccesskeylist, or (ii) message_revision-user.additionalkeylist which implies there are positive values which provide access, but your original query used the condition readaccesskeylist /is not null/ as a test for access /refusal/, which seems to contradict what you now say. message.ReadAccessKeylist and message.SearchRevision- message_revision_tag.ReadAccessKeylist are a list of keys of which *one* is required to get in. user.AdditionalKeylist is a list of keys that the user possesses and of which *one* can be used to get in. One list contains the keys that can be used, the other two lists contain keys that are allowed. One gives keys, the other accept keys. Imagine it like the user coming along with a keyring, trying to open a door with multiple keyholes. One of his keys must fit in one of the keyholes to get in. And the problem here is that I need to test whether there is not a single tag for a (known) revision of a message that has an associated keylist to which no keys of the session user fits. If there was such a tag, access would be denied. To grant access, there must only be tags that either have ReadAccessKeylist IS NULL or that contain a key to which one of the session user's additional keys fits. I'm still wondering if there's a way to explain all this better with some graphics. -- Yves Goergen LonelyPixel [EMAIL PROTECTED] Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Inefficient query processing?
On 11.02.2008 17:32 CE(S)T, Peter Brawley wrote: 1. user.additionalkeylist and tag.readaccesskeylist are atomic despite their names? Yes, I forgot the types. Everything is scalar, varchar or integer. There are not set or otherwise complex data types. 2. You have reciprocal foreign keys, keylist.key referencing user(userID) and user.additionalkeylist referencing keylist.keylistID? Basically, yes. Although there is a contraint in my application that is not visible in the database structure: I distinguish between personal and virtual keys. Personal keys must not be part of a user's additional keys list. Virtual keys must not have an additional keys list on their own. (And they must not have logon information.) So there cannot be a cyclic reference. This is documented in the source code and will be enforced on the application layer later. -- Yves Goergen LonelyPixel [EMAIL PROTECTED] Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Inefficient query processing?
On 11.02.2008 00:29 CE(S)T, Peter Brawley wrote: message (messageID) keylist (keylistID) tag ( tagID, readaccesskeylist references keylist(keylistID) ) message_revision_tag ( ???, messageID references message(messageID), tagID references tag(tagID)) Another table: message_revision(MessageId references message, RevisionNumber) Correction: message_revision_tag(MessageId, RevisionNumber, TagId) (MessageId, RevisionNumber) references message_revision (i) Finding messages which have a deny-access tag looks like a simple join: SELECT DISTINCT messageID FROM message_revision_tag AS mrt JOIN tag AS t ON mrt.tagID=t.tagID WHERE t.readaccesskeylist IS NOT NULL; (ii) We get the messages not in the above result with a simple exclusion join: SELECT messageID FROM message m LEFT JOIN ( SELECT DISTINCT messageID FROM message_revision_tag AS mrt JOIN tag AS t ON mrt.tagID=t.tagID WHERE t.readaccesskeylist IS NOT NULL ) AS banned USING (messageID) WHERE banned.messageID IS NULL, Or did I miss something? I'm afraid I cannot integrate this in my large query. It looks too simple and I don't know where to put its parts. Maybe I'll really have to show the full schema and the complete query... It's not only that my entire query will find messages that have no tag with a ReadAccessKeylist assigned; it will rather find messages that have no tag with a keylist which does not include the currently logged in user's UserId or one of this user's additional keys, which are again stored in a keylist. -- Yves Goergen LonelyPixel [EMAIL PROTECTED] Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Inefficient query processing?
because they need to be able to see it to alter access. :sessionUserId is the current session user's UserId. For anonymous guests, this is 0. The partial query that I have quoted last time is mainly the main query's last condition in the WHERE clause. It handles access coming from tags. The first half of the condition handles access coming from the message's own ReadAccessKeylist. (This is not a closed-source commercial thing. It is a web application that will be available on my website under the GPL when it's ready. It basically already works fine, just a little slow under some conditions.) -- Yves Goergen LonelyPixel [EMAIL PROTECTED] Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Inefficient query processing?
Hi, I have a performance problem with one of my SQL queries. It's a rather complex one so I'll spare you the details. This is the situation: In my system, there's messages, tags and keylists. Each message has message_revisions, each message_revision can be assigned tags (stored in message_revision_tag). Each tag points to a keylist that contains all keys that grant access to messages with that tag. If a tag has no keylist assigned (ReadAccessKeylist IS NULL), then everybody may access the messages. This query finds all messages that don't have a tag assigned that would deny access to it. (Assume every message has only a single revision with the number 1, for now. The actual user comparison with another sub-select is hidden in some more.) SELECT m.MessageId FROM message m WHERE NOT EXISTS (SELECT EXISTS (SELECT 1 FROM keylist tk WHERE tk.KeylistId = t.ReadAccessKeylist AND some more) AS Allowed FROM message_revision_tag mrt JOIN tag t USING (TagId) WHERE mrt.MessageId = m.MessageId AND mrt.RevisionNumber = 1 AND t.ReadAccessKeylist IS NOT NULL HAVING NOT Allowed) My problem is that the sub-select in line 7 (SELECT 1) takes a rather long time. (When I remove it, it's much faster.) I'm not sure why, because there's not a single keylist in that table, however. Another issue is that this query should actually never be regarded. The condition in the second-last line is always false. A simple test confirms that: SELECT COUNT(*) FROM tag WHERE ReadAccessKeylist IS NOT NULL - 0 So there should never be a reason why the FROM in line 11 would result in a row (filtering out with the conditions, of course). But it still gets executed. When I make sure that the condition is always false, by adding a AND 0 just before the HAVING clause, the whole thing runs much faster. I have no separate timings, but it's in the magnitude of 1 vs. 5-10 milliseconds for the query. Run a hundred times makes a noticeable delay. My understanding of it all was that first the FROM clause is regarded to see what rows there are. Then WHERE filters them, then SELECT will pick some columns (and thereby execute my sub-select expression) and finally HAVING filters again. Since in my theory there is no single row, SELECT has nothing to do. But obviously it has. Some suggestion what's going on? -- Yves Goergen LonelyPixel [EMAIL PROTECTED] Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Error: You can't specify target table '...' for update in FROM clause
On 07.02.2008 03:52 CE(S)T, Chris wrote: If you don't mind a mysql-specific fix, and can get the data you want from a select query you could: insert into table (select goes here) on duplicate key update; or maybe a replace into ? INSERT/REPLACE ... SELECT will always overwrite the entire row, but I only want to copy a single column of it. The rest of the record must remain intact. So I can't use that, too. I also try to avoid DBMS-specific workarounds where I can in this project. So maybe one day MySQL will drop the above mentioned restriction. :) -- Yves Goergen LonelyPixel [EMAIL PROTECTED] Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Error: You can't specify target table '...' for update in FROM clause
On 06.02.2008 08:12 CE(S)T, Chris wrote: Yves Goergen wrote: My goal was to copy some potentially large BLOB from one record to another in the same table Update table set blob2_field=blob1_field; This does something totally different. ;) See my first posting why. -- Yves Goergen LonelyPixel [EMAIL PROTECTED] Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Error: You can't specify target table '...' for update in FROM clause
Hi, I've got an error message from MySQL 5.0 that I don't understand. UPDATE message_revision SET HasData = 1, Data = (SELECT Data FROM message_revision WHERE MessageId = 7 AND RevisionNumber = 5) WHERE MessageId = 7 AND RevisionNumber = 6 SQL error: [SQLSTATE:HY000, 1093] You can't specify target table 'message_revision' for update in FROM clause What went wrong? -- Yves Goergen LonelyPixel [EMAIL PROTECTED] Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Error: You can't specify target table '...' for update in FROM clause
On 05.02.2008 23:25 CE(S)T, Baron Schwartz wrote: You can't select from a table you're updating at the same time. What at the same time means is a bit unclear unless you're one of the MySQL developers ;-) Yes, Paul DuBois already replied to me off-list. Now I found that documentation part and understand that MySQL cannot do this. (Haven't tested whether other DBMS can, would be pointless anyway.) My goal was to copy some potentially large BLOB from one record to another in the same table, nothing more. I have now chosen the way to fetch it from the database and have my application just write it back again. I wanted to avoid this unnecessary copying around. However, you can do multi-table updates like this: UPDATE tbl AS a INNER JOIN tbl AS b ON SET a.col = b.col That sounds interesting, however, I couldn't find it in PostgreSQL's and SQLite's reference. Is this a MySQL extension over the SQL standard? -- Yves Goergen LonelyPixel [EMAIL PROTECTED] Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Bug: Different data for different connections
Hi, today I have noticed a strange bug with MySQL and PHP. I'm developing a PHP application, using the MySQL database server 5.0.45 on Windows XP and the PDO connection objects (PHP Data Objects). The PHP application works on InnoDB tables and uses transactions and persistent connections. One of the tasks is to check whether a cached field is set and if not, the value is generated from another field and then stored in the database for later use. To validate my application's work, I've watched the database with phpMyAdmin in the second browser tab. PMA doesn't use persistent connections, I assume. Now when that field, HtmlContent is NULL, my application reads the value from the field Content, converts it to HTML and writes it back to HtmlContent. The next time this page is requested, the data is already there and doesn't need to be converted again. I have enough debug output in my PHP application to see what it's doing and what queries it's running. The strange behaviour is the following: Initially the field HtmlContent is NULL and I have restarted both MySQL and Apache services. My application now converts the data and writes it to the database in the first request. The next time(s), it won't do that again because the data is already there. But when I set that column to NULL with phpMyAdmin, my application still reads the old data from the database. phpMyAdmin keeps telling me that the value is actually NULL, which I just entered. Whereas the persistent PHP connection doesn't see the new data and keeps reading the previous one. My application will only get back to the truth when I restart the Apache or MySQL services which effectively closes the connection. Also, not using persistent database connections in my application helps to always read current data. I could not find any transaction that was left open. But executing a ROLLBACK query at the very beginning of my application also helps to read current data. Now what can be the reason for that inconsistency? How can I find the problem that is causing this bug? -- Yves Goergen LonelyPixel [EMAIL PROTECTED] Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Bug: Different data for different connections
On 20.12.2007 19:42 CE(S)T, Yves Goergen wrote: But when I set that column to NULL with phpMyAdmin, my application still reads the old data from the database. phpMyAdmin keeps telling me that the value is actually NULL, which I just entered. Whereas the persistent PHP connection doesn't see the new data and keeps reading the previous one. Here's more facts: My application disables autocommit mode right at the beginning. I thought this would be a good compatibility measure to make MySQL more similar to the big DBMS where my app should also run later. And each of my writing operations is done in a separate transaction that is started and commited (or rolled back) with PDO's methods which should not be too much different from the corresponding SQL statements. I thought that disabling autocommit mode makes no difference at all, when I only write to the DB inside of transactions, but when I remove that line from my code, the bug seems to go away. Here's my theory: * Disabling autocommit starts a new transaction, according to the MySQL manual. When I then start my own transaction, I'm at level 2. MySQL needs to support nested transactions for this to work out. A COMMIT statement will only commit the innermost transaction. * When I start a transaction and then write something to a table which is later overwritten from another thread, I still see my own data as long as the outermost transaction is not finished. Also, data that I write in a transaction must not be locked and can be overwritten from another thread. If this is both true, I see that this is my fault. I write the new HtmlContent, then phpMyAdmin sets it NULL again but my app still sees what it has just written (because of the persistent transaction over multiple requests). However, if MySQL doesn't support nested transactions or data written in a transaction will be locked, this is not an explanation for what I experience. -- Yves Goergen LonelyPixel [EMAIL PROTECTED] Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Bug: Different data for different connections
On 20.12.2007 20:34 CE(S)T, Martijn Tonies wrote: Are your tables InnoDB? If so, the snapshot transaction is giving you a static view on the data and your own changes, while your PHPMyAdmin commits the NULL write. Your application keeps on seeing your own changes, cause it did not end the snapshot transaction. Yes, all tables are InnoDB. So MySQL does support nested transaction and both SET AUTOCOMMIT = 0 and START TRANSACTION start a new transaction level, is that true? -- Yves Goergen LonelyPixel [EMAIL PROTECTED] Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Bug: Different data for different connections
On 20.12.2007 21:14 CE(S)T, Baron Schwartz wrote: It doesn't support nested transactions. What you're seeing is the effects of MVCC. The InnoDB section of the MySQL manual explains it. I wasn't able to find MVCC-related information (I assume it means Multi Version Concurrency Control, not sure whether that's correct) in the MySQL manual. But Martijn's explanation gives me an idea what it could be about. But then again, if MySQL doesn't support nested transactions, I don't see how any information can be frozen until a point when the transaction has already been commited. -- Yves Goergen LonelyPixel [EMAIL PROTECTED] Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Bug: Different data for different connections
On 20.12.2007 22:18 CE(S)T, Baron Schwartz wrote: On Dec 20, 2007 3:33 PM, Yves Goergen [EMAIL PROTECTED] wrote: I wasn't able to find MVCC-related information (I assume it means Multi Version Concurrency Control, not sure whether that's correct) in the MySQL manual. But Martijn's explanation gives me an idea what it could be about. But then again, if MySQL doesn't support nested transactions, I don't see how any information can be frozen until a point when the transaction has already been commited. http://dev.mysql.com/doc/refman/5.0/en/innodb-transaction-model.html Yes, this is where I was looking. -- Yves Goergen LonelyPixel [EMAIL PROTECTED] Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Bug: Different data for different connections
On 20.12.2007 21:34 CE(S)T, Martijn Tonies wrote: So MySQL does support nested transaction and both SET AUTOCOMMIT = 0 and START TRANSACTION start a new transaction level, is that true? I didn't say it supports nested transactions, I said that if your application starts a single transaction and does not finish it, it will continue seeing the same data despite other transactions (PHPMyAdmin) changing your data. Okay, I got that. So a COMMIT statement after disabling autocommit mode and another START TRANSACTION does not finish my transaction. (But then, what does?) Interesting view, I didn't know that. But now all's clear: I won't touch autocommit mode anymore and everything works as expected. Thanks for your help. -- Yves Goergen LonelyPixel [EMAIL PROTECTED] Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Bug: Different data for different connections
On 20.12.2007 22:46 CE(S)T, Martijn Tonies wrote: Okay, I got that. So a COMMIT statement after disabling autocommit mode and another START TRANSACTION does not finish my transaction. (But then, what does?) Interesting view, I didn't know that. But now all's clear: I won't touch autocommit mode anymore and everything works as expected. Thanks for your help. A COMMIT _should_ finish your transaction yes. Are you sure you're executing the COMMIT on the same connection? Yes, I am. I open up only a single connection in my application, so it must be that one. Most of the time, further HTTP requests even get back the same connection again. I can see the sleeping connection on my application database from phpMyAdmin between the requests, with reasonable sleep time values. -- Yves Goergen LonelyPixel [EMAIL PROTECTED] Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: German collation for UTF8 missing
On 21.11.2007 15:18 CE(S)T, Marten Lehmann wrote: If I recall that correctly, utf8_swedish_ci is the collation to use for european/western european languages. Those Swedish people think they can stand for whole Europe... ;) Not tested my reply, though. and doesn't work either. Okay. This is the closest match I've found in the MySQL manual. http://dev.mysql.com/doc/refman/5.0/en/charset-unicode-sets.html So German is not supported with Unicode in MySQL 5.0, it seems. SQLite supports adding user collations through code, which enables you to use a custom function to sort strings, like .NET or PHP offer one. This way, I can for example use natural sorting in SQLite from .NET applications. Does MySQL also have support for this? -- Yves Goergen LonelyPixel [EMAIL PROTECTED] Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Transactions and locking
On 14.11.2007 12:50 CE(S)T, Martijn Tonies wrote: Yves, Did you read this reply I send earlier? I think it does what you want without needing to lock anything, thus making it portable. I would suggest the following -- create a table called SEQUENCES: Yes, I've read it and actually put a flag on that message, but then I decided to go for the other flagged message that explained SELECT ... FOR UPDATE. I did some tests with multiple client windows and found that the locking is good enough. I use it for finding new ID values and for telling whether a new value is unique where UNIQUE constraints won't help me (because I want the values to be caseless unique e.g.). SELECT ... FOR UPDATE works fine when I always use the same function to access that table. It is supported by MySQL, PostgreSQL and Oracle. I only need a small workaround for SQLite (which gets the FOR UPDATE stripped off and instead requires the programmer to have started an EXCLUSIVE transaction before; else - Exception). Sequences, if I got that right, need the new value to be stored immediately, i.e. outside of an active transaction. This requires a second connection to the database which probably causes more implementation work for my web application. I don't need LOCK TABLES anymore now. And at last, I can say that this is indeed not a simple topic as I've thought and maybe I've read most of the related documentation now anyway... -- Yves Goergen LonelyPixel [EMAIL PROTECTED] Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: German collation for UTF8 missing
On 14.11.2007 21:43 CE(S)T, Marten Lehmann wrote: I want to store my data with UTF8, thus I'm using the utf8 charset for my tables. But which collcation shall I use? I cannot find anything appropriate. If I recall that correctly, utf8_swedish_ci is the collation to use for european/western european languages. Those Swedish people think they can stand for whole Europe... ;) Not tested my reply, though. -- Yves Goergen LonelyPixel [EMAIL PROTECTED] Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Transactions and locking
On 13.11.2007 01:04 CE(S)T, Perrin Harkins wrote: On Nov 12, 2007 6:47 PM, Yves Goergen [EMAIL PROTECTED] wrote: From what I've read about MySQL's table locks and InnoDB, you cannot use LOCK TABLES with transactions. Either of them deactivates the other one. Beginning a transaction unlockes all tables, locking tables ends a transaction. I don't think that's correct. At least that's not how I read this: http://dev.mysql.com/doc/refman/5.0/en/innodb-restrictions.html It sounds like you issue a LOCK TABLES at the beginning of your transaction, and doing a COMMIT unlocks the tables at the end. From that page: Sometimes it would be useful to lock further tables in the course of a transaction. Unfortunately, LOCK TABLES in MySQL performs an implicit COMMIT and UNLOCK TABLES. An InnoDB variant of LOCK TABLES has been planned that can be executed in the middle of a transaction. In any case, you only need to do a table lock long enough to insert a row into your first table. After that, you can release the lock. And when I insert the row in the first table but cannot do so in the second because of some invalid data, I need to also remove the first row again because it doesn't make sense alone. This is what transactions are for. I think I'll go for transactions and check the error code in most cases. Only where a custom check is needed, I'll lock the tables without using a transaction. I'll see how far I get with it. Oh, I see from that page above: All InnoDB locks held by a transaction are released when the transaction is committed or aborted. Thus, it does not make much sense to invoke LOCK TABLES on InnoDB tables in AUTOCOMMIT=1 mode, because the acquired InnoDB table locks would be released immediately. So, it seems that locking tables is *impossible* with InnoDB. Bad. The only thing I can do then is write the data and afterwards count if there are two of them. But this still isn't safe, in concurrency means. Any solution? May be a bug report? -- Yves Goergen LonelyPixel [EMAIL PROTECTED] Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Transactions and locking
(For the record... I missed the mailing list recipient - again!!) On 13.11.2007 00:30 CE(S)T, Perrin Harkins wrote: On Nov 12, 2007 5:58 PM, Yves Goergen [EMAIL PROTECTED] wrote: First I find a new id value, then I do several INSERTs that need to be atomic, and especially roll back completely if a later one fails. If you use a table lock on the first table where you get the ID, you know that ID is safe to use. Using a table lock when you get the ID and then trusting transactions to roll back all the inserts in the event of a later failure should work fine. From what I've read about MySQL's table locks and InnoDB, you cannot use LOCK TABLES with transactions. Either of them deactivates the other one. Beginning a transaction unlockes all tables, locking tables ends a transaction. That Perl module uses the exact technique I described to you with updates and LAST_INSERT_ID(). AUTO_INCREMENT isn't portable. You're misunderstanding. The LAST_INSERT_ID() function doesn't use AUTO_INCREMENT. That's why the perl module uses it. It just copies the value you pass to it and makes that available without another select. I don't understand what you mean. It's not portable to SQLite, but you can use a sequence there instead. To my knowledge, SQLite doesn't support sequences either, only auto_increment. I've began to convert my code to evaluate error codes now, but I see the next problem already: At one place, I insert a row where two columns could potentially violate a uniqueness constraint. With just reading the error code, I can't figure out which of them caused the problem. The error message I can present to the user will be somewhat generic then. (Either this or that of your input already exists. Find out which one. Haha!) Maybe I'll use error codes or table locks depending on the situation. It's all a big hack, but so is databases (and portability) it seems. I'm not sure yet. It's late. -- Yves Goergen LonelyPixel [EMAIL PROTECTED] Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Transactions and locking
On 13.11.2007 16:37 CE(S)T, mark addison wrote: As your using InnoDB, which has row level locking a SELECT ... FOR UPDATE should work. http://dev.mysql.com/doc/refman/4.1/en/innodb-locking-reads.html e.g. BEGIN TRANSACTION new_id := (SELECT MAX(id) FROM table FOR UPDATE) + 1 -- some more work here INSERT INTO table (id, ...) VALUES (new_id, ...) COMMIT Row level locking can only lock rows that exist. Creating new rows (that would have an influence on my MAX value) are still possible and thus row level locking is not what I need. I really need locking an entire table for every other read or write access. -- Yves Goergen LonelyPixel [EMAIL PROTECTED] Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Transactions and locking
(Damn I hate those lists that don't come with a Reply-To to the list! Resending...) On 13.11.2007 17:39 CE(S)T, Baron Schwartz wrote: Yves Goergen wrote: Row level locking can only lock rows that exist. Creating new rows (that would have an influence on my MAX value) are still possible and thus row level locking is not what I need. I really need locking an entire table for every other read or write access. InnoDB can also lock the gap, which will prevent new rows that would have been returned by the SELECT. The manual has more info on this in the section on consistent reads in InnoDB. FOR UPDATE will do what you need. I've read about that gap but it sounded like the place [somewhere] before a record where one could insert a new record into. Not sure what that should be. I'm not aware of the InnoDB internals. I know that usually (?) when a new record is stored, it is written to where is enough space for it, linked from a free pointer index. If one is locked, another one might be used. Order doesn't matter in relational databases. -- Yves Goergen LonelyPixel [EMAIL PROTECTED] Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Transactions and locking
On 13.11.2007 14:01 CE(S)T, Baron Schwartz wrote: It's more complicated than that. You can use them together, you just have to do it like this: set autocommit = 0; begin; lock tables; -- you are now in a transaction automatically begun by LOCK TABLES . I assume that at this point, any SELECT on the table I have locked should block. But guess what, it doesn't. So it doesn't really lock. commit; -- your tables are now unlocked. In fact, you *must* use a transaction for LOCK TABLES to be safe, at least in MySQL 5. Even if you're using non-transactional tables. Otherwise, you can get nasty behavior. See http://bugs.mysql.com/bug.php?id=31479 -- Yves Goergen LonelyPixel [EMAIL PROTECTED] Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Transactions and locking
On 13.11.2007 19:19 CE(S)T, Perrin Harkins wrote: You can use next-key locking to implement a uniqueness check in your application: (...) http://dev.mysql.com/doc/refman/5.0/en/innodb-next-key-locking.html This doesn't help my problem either. It may lock new INSERTs to the table, but it won't lock SELECTs so any other concurrent user can still find its own (same) MAX(id) value and then do an insert. Or any other process can still check for uniqueness and then fail with its insert. The insert of the first process may succeed guaranteed, but the second will fail at a point where it should not. (Actually, it should never fail when I found a new id value / found that my new value is unique.) I have tested the SELECT ... FOR UPDATE and the LOCK TABLES with autocommit = 0 thing. Both don't lock anything (at least not for reading by others which is what I need). May I now conclude that exclusive full table locking is not possible with InnoDB? Or is there another way that I don't know yet? -- Yves Goergen LonelyPixel [EMAIL PROTECTED] Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Transactions and locking
On 13.11.2007 20:43 CE(S)T, Baron Schwartz wrote: Yves Goergen wrote: I assume that at this point, any SELECT on the table I have locked should block. But guess what, it doesn't. So it doesn't really lock. What kind of lock are you using? -- cxn 1 set autocommit=0; begin; lock tables t1 write; Query OK, 0 rows affected (6.29 sec) -- cxn 2 set autocommit=0; begin; select * from t1; -- hangs Not for me. This is what I was doing here. (FYI: MySQL 5.0.45-community-nt, Windows XP, mysql command line client, InnoDB tables) -- Yves Goergen LonelyPixel [EMAIL PROTECTED] Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Transactions and locking
On 13.11.2007 20:57 CE(S)T, Baron Schwartz wrote: It will absolutely lock SELECTs. Are you sure autocommit is set to 0 and you have an open transaction? Are you sure your table is InnoDB? I'm doing this right now: -- cxn 1 mysql set autocommit=0; mysql begin; mysql select * from t1 for update; +--+ | a| +--+ |1 | +--+ 1 row in set (0.00 sec) -- cxn 2 mysql set autocommit=0; mysql begin; mysql select * from t1 for update; Okay, my fault, I didn't use the FOR UPDATE in the second connection. If I do (which is likely to be the case in an application because there, the same code is run concurrently), the second SELECT locks. (The same is true when I select MAX(id) instead of *.) If I don't, it still works. Okay, so we have some table locking, tested, working. Very nice. Thank you for this one. :) ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction -- Yves Goergen LonelyPixel [EMAIL PROTECTED] Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Transactions and locking
On 13.11.2007 20:43 CE(S)T, Baron Schwartz wrote: -- cxn 2 set autocommit=0; begin; select * from t1; -- hangs Delete my last message. I just did it again and now it works, too. I have no idea what I did a couple of minutes ago, but it must have been wrong. Okay. Works, too. I was doubting that it was possible at all. Meanwhile, I found the Oracle reference and it says that locks can never lock queries, so reading a table is possible in any case. Thank you for all your patience you had with me. I think my problems are now solved... I'll see it when I test my application the next time. ;) -- Yves Goergen LonelyPixel [EMAIL PROTECTED] Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Transactions and locking
Hi, there's very much information about how transactions and locking works in InnoDB, but maybe there's also a simple and understandable answer to my simple question: When I start a transaction, then find the maximum value of a column and use that + 1 to write a new row into the table, how do transactions protect me from somebody else doing the same thing so that we'd both end up writing a new row with the same value? Here's a description: BEGIN TRANSACTION new_id := (SELECT MAX(id) FROM table) + 1 -- some more work here INSERT INTO table (id, ...) VALUES (new_id, ...) COMMIT What happens if another user does the same in that more work region? (Of course, this example is pseudocode, I really have a PHP application that does this.) -- Yves Goergen LonelyPixel [EMAIL PROTECTED] Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Transactions and locking
Okay, I feel like I need to clarify some things. I do have a UNIQUE INDEX constraint on those columns, so the other user won't actually write the same value another time, but it will fail at a level which it should not. I don't want to use AUTO_INCREMENT because it's not portable. My application should work on MySQL and SQLite (and maybe someday it will also run on many other systems - today, incompatibilities are just too big). Here's another example: SELECT COUNT(*) FROM table WHERE name = ? -- a short delay which is long enough for a concurrent request :( UPDATE table SET name = ? WHERE id = ? I do the first query to find out whether my new name is already assigned. Each name can only appear one time. If I just try and update the row, the query will fail, but I don't know why. All I could do is try and parse the error message, but this will by DBMS-dependent. I'd like to do it in a way so that I can tell the user whether the name was not unique or there was another error. But this case should be detected separately. I'll have a look at those isolation levels though. Maybe it's what I'm looking for. -- Yves Goergen LonelyPixel [EMAIL PROTECTED] Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Transactions and locking
On 12.11.2007 20:43 CE(S)T, Yves Goergen wrote: I'll have a look at those isolation levels though. Maybe it's what I'm looking for. Not quite. But I'm going the LOCK TABLES way now. Locking a single table exclusively for those rare moments seems to be the best solution. I could also implement an abstraction for that, because other DBMS have different syntax to do the same thing. Since I only need these locks for a very short time and a single table with no transaction support, this works fine for me. -- Yves Goergen LonelyPixel [EMAIL PROTECTED] Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Transactions and locking
On 12.11.2007 22:16 CE(S)T, Yves Goergen wrote: Since I only need these locks for a very short time and a single table with no transaction support, this works fine for me. Damn, I found out that I need table locking *and* transactions. I'm lost... Maybe I'm really better off using a sequence (like the one PostgreSQL offers and like it is available as an add-on for Perl [1]). But then again, I need queries outside of a transaction so that the sequence's next number is immediately commited and visible to other users. I have the impression that it all doesn't work. [1] http://search.cpan.org/~adamk/DBIx-MySQLSequence-1.00/lib/DBIx/MySQLSequence.pm -- Yves Goergen LonelyPixel [EMAIL PROTECTED] Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Transactions and locking
On 12.11.2007 23:31 CE(S)T, Perrin Harkins wrote: On Nov 12, 2007 5:24 PM, Yves Goergen [EMAIL PROTECTED] wrote: Damn, I found out that I need table locking *and* transactions. What makes you say that? BEGIN TRANSACTION SELECT MAX(id) FROM table INSERT INTO table (id) VALUES (?) INSERT INTO othertable (id) VALUES (?) COMMIT First I find a new id value, then I do several INSERTs that need to be atomic, and especially roll back completely if a later one fails. That Perl module uses the exact technique I described to you with updates and LAST_INSERT_ID(). AUTO_INCREMENT isn't portable. Now I only support MySQL and SQLite. But I also did PostgreSQL (until it failed one of the more complex queries, maybe it comes back one day) and maybe Oracle or whatever will be compatible, too, so that I then stand there with my AUTO_INCREMENT and can't use it. Frankly, doing the insert and checking for an error seems like a pretty reasonable solution to me, since you only have two databases to care about at this point. I wonder if I can safely use an error code to determine this error condition and then just retry. Here's an interesting page: http://dev.mysql.com/doc/refman/5.0/en/error-messages-server.html Error: 1022 SQLSTATE: 23000 (ER_DUP_KEY) Message: Can't write; duplicate key in table '%s' No documentation for SQLite. PostgreSQL uses several SQLSTATE codes for this situation. (http://www.postgresql.org/docs/8.2/interactive/errcodes-appendix.html) Something must be wrong with SQL-92 because the two reference tables have no common SQLSTATE values for related error conditions. But generally I think that an SQLSTATE beginning with 23 is close enough for a match. -- Yves Goergen LonelyPixel [EMAIL PROTECTED] Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Foreign keys on non-unique columns (problem)
On 04.11.2007 21:10 CE(S)T, Martijn Tonies wrote: Now I have added this foreign key constraint: ALTER TABLE user ADD FOREIGN KEY (AdditionalKeylist) REFERENCES keylist (KeylistId) ON DELETE SET NULL; This cannot work. The column in KEYLIST to which you are pointing should have a unique value, that means either a primary key or unique constraint. I know, how I've written further down. Given that the constraint on KEYLIST means that you can have multiple KEYLIST entries for each USERID value, how is a foreign key constraint supposed to be pointing to a single entry in KEYLIST? It cannot, unless you're referencing a unique (pair) value. That's an interesting point. Actually, I'm not referencing a single row, but a single value which can occur multiple times. What is it exactly that you want to store? What I want to store is the reference on a key list ID that really exists. And as soon as the key list ID does not exist any more (i.e. because of the last occurence has been deleted), the reference on that value (not row!) shall be set to NULL. Regarding it this way, my hope to get this done with RDBMS means shrinks... (Sorry for double sending, I hit the Reply button first...) -- Yves Goergen LonelyPixel [EMAIL PROTECTED] Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Foreign keys on non-unique columns (problem)
Hi, I have a problem with my foreign keys. I have the following two tables: CREATE TABLE keylist ( KeylistId INTEGER NOT NULL, UserId INTEGER NOT NULL, PRIMARY KEY (KeylistId, UserId)); CREATE TABLE user ( UserId INTEGER NOT NULL PRIMARY KEY, AdditionalKeylist INTEGER); A keylist stores multiple user IDs for each keylist ID. A user has a reference to one keylist to keep multiple additional keys. (My key is the same as a user ID.) Now I have added this foreign key constraint: ALTER TABLE user ADD FOREIGN KEY (AdditionalKeylist) REFERENCES keylist (KeylistId) ON DELETE SET NULL; Which is supposed to mean the following: When I delete a keylist and there's no remaining row with this keylist ID, then find the users that are referencing it and set their AdditionalKeylist value to NULL so that they doesn't keep an invalid reference. The problem: When a keylist ID exists twice and I delete one of them, the user's AdditionalKeylist value is set to NULL immediately, although another keylist ID instance exists. I have read through the MySQL documentation about foreign keys and understand that referencing a non-unique column (i.e. not a candidate key) is not standard SQL and that InnoDB doesn't exactly do what I want (it ignores the remaining relevant rows). From SQLite (which doesn't currently enforce foreign keys) I have learned a trigger that can do the same (haven't tested it yet, though), but to extend it to that look for other instances check, I need it to understand the WHEN part of my trigger, which seems to be commonly known but unsupported (and undocumented) by MySQL 5.0. Also, MySQL requires uncommonly high privileges to create a trigger which is not an option in the field (I'm planning to release my application for use on common web space). I hope you understand my problem. There's two potential solutions which both don't work for me. Is there a third? Can I create this kind of referential integrity on the DBMS level at all? Is my design bad? -- Yves Goergen LonelyPixel [EMAIL PROTECTED] Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Foreign keys on non-unique columns (problem)
On 03.11.2007 22:52 CE(S)T, Yves Goergen wrote: Is my design bad? I should explain why I do it this way at all. There's some other tables in my system that need to keep a list of keys (i.e. user IDs) for several actions. A message (one of the tables) has one keylist for read access, one for alter access and a third one for reply access. The lists (if not NULL = empty) contain a list of authorised users to perform the respective action. (If NULL, everybody's allowed.) In an older design, I had a separate table for each list type, which was 6 tables altogether. Instead of a keylist.KeylistId, there was e.g. a MessageReadAccessKeys.MessageId referencing message.MessageId. First, this makes 5 more tables and second, I doubt that it would solve my non-unique foreign key problem. What I need is to store those keys for several tasks, object types and instances of them. What I would like to have is the DBMS keeping those references valid. I guess my last chance is implementing this check in my application (which I already had before I chose to entirely rely on referential integrity and then deleted these few lines). Please tell me if there's a better way. PS: I searched a little more and found out that PostgreSQL also forbids foreign keys referencing non-unique columns (like in SQL92) due to serious bugs in the past, which is one more reason why I don't want to keep it this way. (The whole thing should be somewhat portable...) -- Yves Goergen LonelyPixel [EMAIL PROTECTED] Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Magazine - Issue 1 available NOW!!!!
On 04.06.2007 23:44 CE(S)T, Daevid Vincent wrote: Thanks for the magazine. I already incorporated a little extra SQL injection checking into my db.inc.php wrapper... //[dv] added to remove all comments (which may help with SQL injections as well. $sql = preg_replace(/#.*?[\r\n]/s, '', $sql); $sql = preg_replace(/--.*?[\r\n]/s, '', $sql); $sql = preg_replace(@/\*(.*?)\*/@s, '', $sql); I'm not aware of the context, but I guess you can imagine that this will corrupt any SQL queries that contain # or -- or /* ... */ inside a string. So I would highly recommend not using those. -- Yves Goergen LonelyPixel [EMAIL PROTECTED] Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table compression with write (append) support
On 28.05.2007 09:06 CE(S)T, Kevin Hunter wrote: At 12:31a -0400 on 28 May 2007, Dan Nelson wrote: You want the ARCHIVE storage engine. http://dev.mysql.com/doc/refman/5.0/en/archive-storage-engine.html Hm, it doesn't support deleting rows and it cannot use indexes. So doing statistics on them (which can be a little more complex than counting rows within a timespan, which is why I wanted to use an SQL database) could get quite resource demanding. In particular, I imagine a lot of the HTTP requests would be the same, so you could create a table to store the requested URLs, and then have a second table with the timestamp and foreign key relationship into the first. Interesting idea. Inserting would be more work to find the already present dictionary rows. Also, URLs sometimes contain things like session IDs. They're probably not of interest for my use but it's not always easy to detect them for removal. I could also parse user agent strings for easier evaluation, but this takes me the possibility to add support for newer browsers at a later time. (Well, I could update the database from the original access log files when I've updated the UA parser.) IP addresses (IPv4) and especially return codes (which can be mapped to a 1-byte value) are probably not worth the reference. Data size values should be too distributed for this. How large is a row reference? 4 bytes? -- Yves Goergen LonelyPixel [EMAIL PROTECTED] Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table compression with write (append) support
On 28.05.2007 18:34 CE(S)T, Kevin Hunter wrote: At 5:45a -0400 on 28 May 2007, Yves Goergen wrote: Also, URLs sometimes contain things like session IDs. They're probably not of interest for my use but it's not always easy to detect them for removal. Really? Why wouldn't it be easy to detect them? You presumably know what variable you're looking for in the URL string, and applying a simple regex search-and-replace . . . ? I don't control what applications run on that web server. Same thought. If you've only a known set of UA strings, you could normalize them with the dictionary table as well. Well, I don't know (in advance) what's all running around out there... -- Yves Goergen LonelyPixel [EMAIL PROTECTED] Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Table compression with write (append) support
Hi, I'm thinking about using a MySQL table to store an Apache access log and do statistics on it. Currently all access log files are stored as files and compressed by day. Older log files are compressed by month, with bzip2. This gives a very good compression ratio, since there's a lot of repetition in those files. If I store all that in a regular table, it would be several gigabytes large. So I'm looking for a way to compress the database table but still be able to append new rows. As the nature of a log file, it is not required to alter previous data. It could only be useful to delete older rows. Do you know something for that? -- Yves Goergen LonelyPixel [EMAIL PROTECTED] Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Confusing backslash issues with LIKE
Hello, I'm having some trouble with the LIKE operator on MySQL 5.0. Here's my transcript: mysql select 'abc\\def'; +-+ | abc\def | +-+ | abc\def | +-+ 1 row in set (0.00 sec) mysql select 'abc\\def' like '%\\%'; ++ | 'abc\\def' like '%\\%' | ++ | 0 | ++ 1 row in set (0.00 sec) mysql select 'abc\\def' like '%%'; +--+ | 'abc\\def' like '%%' | +--+ |1 | +--+ 1 row in set (0.00 sec) The last two show my problem. When I search for a backslash, I need to escape it *twice*. Why that? I can't see that from the manual [1]. [1] http://dev.mysql.com/doc/refman/5.0/en/string-comparison-functions.html#operator_like -- Yves Goergen LonelyPixel [EMAIL PROTECTED] Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Confusing backslash issues with LIKE
On 23.05.2007 14:49 CE(S)T, Yves Goergen wrote: [1] http://dev.mysql.com/doc/refman/5.0/en/string-comparison-functions.html#operator_like Never mind. This very page says why it is like it is. It's definitely too hot in here today. :( -- Yves Goergen LonelyPixel [EMAIL PROTECTED] Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Int(4) or int(8)
On 09.03.2007 17:12 CE(S)T, Bruno Rodrigues Silva wrote: I understand that theses values are unrelated to the range of datatype values, however i did not saw any diference when i use int(4) or int(8) Some idea? Isn't it a bit useless to specify the visual, decimal length of a numeric data type that's internally stored in bits? I never specify a length of a number, but use SMALLINT, INT etc instead. If my INT field holds a value that takes 6 digits in decimal, what should be the output if the column was declared INT(3)? Is it allowed to drop some data? (Resending because the list wasn't added to the recipients.) -- Yves Goergen LonelyPixel [EMAIL PROTECTED] Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql_upgrade shows errors
On 14.02.2007 08:00 CE(S)T, Colin Charles wrote: The MySQL 5.0 release is available as a binary and is currently at 5.0.27. Grab it at: http://dev.mysql.com/downloads/mysql/5.0.html#downloads I read in the news some time ago that MySQL 5.0 is only going to be available as source version in the future, which it currently looks like. (Can't currently find that news.) But a more important fact is that the MySQL pre-built binary doesn't work correctly on my machine, anything that links to the mysqlclient crashes when using SSL. Since I build MySQL from source, that's gone. MySQL server is version 5.0.33, OS is Debian Linux 3.1, previous MySQL version was 5.0.17, installed from the binary release. Whats wrong with using the version via apt-get? Debian has very sensible packaging, and its currently at version 5.0.32 afaik Not quite... I can choose from 4.0.24 and 4.1.11a. While 4.0 seems to be unsupported by MySQL AB for a while. What do the above error messages mean? The upgrade script picked up on the fact that you had duplicate column names I cannot already have duplicate column names, relational tables don't allow that. But as you see, I already found out that the error messages are normal and don't mean a thing. -- Yves Goergen LonelyPixel [EMAIL PROTECTED] Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql_upgrade shows errors
On 10.02.2007 17:39 CE(S)T, Yves Goergen wrote: ERROR 1060 (42S21) at line 22: Duplicate column name 'File_priv' (and some more similar stuff) As I found out I already asked that on a previous upgrade. Other sources make me think that this is not an actual error but intended if the tables are already up-to-date. Maybe there should just be a note at the end of the script that tells that it has completed and that tose error messages can be ignored (as is the case for several build systems, e.g. PHP's). I believe this would save you from a lot of asking faces of those that aren't so much into MySQL. -- Yves Goergen LonelyPixel [EMAIL PROTECTED] Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql_upgrade shows errors
Hello, I noticed that the current MySQL 5.0 release is not available as binary, so I downloaded the source and compiled it on my testing machine. Compilation went fine and I can connect to the new MySQL server version. But then I tried to run the mysql_upgrade script to fix possible issues and here's what it gave me: ERROR 1060 (42S21) at line 22: Duplicate column name 'File_priv' @hadGrantPriv:=1 1 1 ERROR 1060 (42S21) at line 28: Duplicate column name 'Grant_priv' ERROR 1060 (42S21) at line 29: Duplicate column name 'Grant_priv' ERROR 1060 (42S21) at line 30: Duplicate column name 'Grant_priv' ERROR 1060 (42S21) at line 41: Duplicate column name 'ssl_type' ERROR 1061 (42000) at line 66: Duplicate key name 'Grantor' ERROR 1054 (42S22) at line 102: Unknown column 'Type' in 'columns_priv' ERROR 1060 (42S21) at line 124: Duplicate column name 'type' @hadShowDbPriv:=1 1 1 (and some more similar stuff) I tried it twice. At the first time, all other tables had an OK besides them, at the second time, those lines didn't show up anymore. MySQL server is version 5.0.33, OS is Debian Linux 3.1, previous MySQL version was 5.0.17, installed from the binary release. What do the above error messages mean? -- Yves Goergen LonelyPixel [EMAIL PROTECTED] Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql_upgrade shows error
Hello, I've just upgraded my MySQL server to version 5.0.26 and ran the mysql_upgrade script with basedir, datadir, password and socket arguments. The first time, I ran it, it listed a bunch of tables (database.tablename) with an OK after them. In the end, it said: ERROR 1060 (42S21) at line 22: Duplicate column name 'File_priv' This is the only message it prints out when I run it again. Does anybody know what that means? Is mysql_upgrade broken? Is a table broken? Which one? Do I need to care about it? My OS is Debian 3.1, I downloaded the glibc 2.3 linux max version of MySQL. -- Yves Goergen LonelyPixel [EMAIL PROTECTED] http://beta.unclassified.de – My web laboratory. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql_upgrade doesn't find its own stuff
Hello, I was just trying out the mysql_upgrade script on my MySQL 5.0 server but it shows an error message that makes me believe it doesn't know where to find its own data. Here's what I did: mysql_upgrade --basedir=/usr/local/mysql5 --datadir=/var/mysql5/data --password And this is what I got: Enter password: [so did I] /usr/local/mysql5/bin/mysqlcheck: Got error: 2002: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2) when trying to connect Could not find MySQL command-line client (mysql). Please use --basedir to specify the directory where MySQL is installed. The correct socket to connect to would be /var/tmp/mysql5.sock which is written in the my.cnf file in the given datadir. And 'mysql' is in the $PATH so my shell finds it without a problem. What can I do to make that programme work? The MySQL version is 5.0.21 on a Debian 3.1 Linux system. The server is running and works perfectly. I'm not sure if I should upgrade to 5.0.24 if this script doesn't work. -- Yves Goergen LonelyPixel [EMAIL PROTECTED] http://beta.unclassified.de – My web laboratory. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql_upgrade doesn't find its own stuff
On 08.08.2006 13:16 CE(S)T, chris smith wrote: Are you sure about that ? Well, I'm sure about what I've seen. Notice that you're installing into /usr/local/mysql5 which is NOT a standard path. Maybe it's finding an old version, check it: mysql --version The 'mysql' binary it finds (located using 'which') is from the mysql5 directory, I've changed my PATH so that it works. There's a MySQL 4.0 in /usr/local/mysql4 and the 5.0 in /usr/local/mysql5 on that machine, no old versions, it was a clean OS image before it has seen any MySQL. 'mysql --version' says: mysql Ver 14.12 Distrib 5.0.21, for pc-linux-gnu (i686) using readline 5.0 What for do I specify the basedir and datadir (which should be enough) if mysql_upgrade can't use it to find the files? -- Yves Goergen LonelyPixel [EMAIL PROTECTED] http://beta.unclassified.de – My web laboratory. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to find out about SSL connection?
On 31.05.2006 10:21 (+0100), Joerg Bruehe wrote: According to your description below, where you got Could not connect ..., you are given this warning if establishing the connection fails. So the remaining case is a client trying to connect to a server which does not support SSL, or does not have it switched on (lacks a certificate). Below was *after* I enabled SSL in the server. *before* there was no warning but an unencrypted connection. Please check the bugs database for this, and submit a feature request if none such is present yet. So I need yet another account for your bug tracker... I am no SSL expert, but AIUI you need client and server to use the same (or at least somehow related) certificates. The client needs what? Since when is it that a client needs a certificate, too, to use an SSL-encrypted connection to a server?! -- Yves Goergen LonelyPixel [EMAIL PROTECTED] http://beta.unclassified.de – My web laboratory. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to find out about SSL connection?
On 31.05.2006 12:47 (+0100), Joerg Bruehe wrote: I said I am no SSL expert, I just go by this quote from the manual: Yes, I saw that. But it doesn't apply on Query Browser because there is no such option available in the UI. Btw, I cannot connect to MySQL5+SSL with Query Browser/Win but I can connect to it with mysql/Linux with the --ssl parameter (through a hostname with a different IP, not a named pipe). But it also doesn't use any SSL, although explicitly specified and offered by the server. Seems like SSL isn't really a production level thing yet. -- Yves Goergen LonelyPixel [EMAIL PROTECTED] http://beta.unclassified.de – My web laboratory. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to find out about SSL connection?
On 31.05.2006 14:46 (+0100), Paul DuBois wrote: --ssl on the client side (mysql) isn't sufficient to enable an SSL connection, as stated in the manual. That's why Joerg indicated the use of the other options. Why do I need to provide the client with a certificate? I don't have one and I also don't want to create one and distribute it to any computer I might want to connect from using SSL. No browser, no FTP client, no MUA needs an SSL cert, so what's up with MySQL? (At least they all don't bug the user with supplying one. I'm no SSL developer either.) And what is that CA thing at all? I don't have anything like that. I only have a certificate (public and private key). All other SSL-enabled services work fine with that. Is there no easy way to say MySQL here's your certificate, and now go do something useful? At least it looks like the MySQL server doesn't require a CA, as opposed to what the doc says. -- Yves Goergen LonelyPixel [EMAIL PROTECTED] http://beta.unclassified.de – My web laboratory. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to find out about SSL connection?
On 21.05.2006 00:16 (+0100), Yves Goergen wrote: Hello, I'm using MySQL Query Browser on Windows XP to connect to a remote MySQL 4.0 and 5.0 database server, both on Linux. In Query Browser, I can check the options Use SSL if available but how do I know if it is available and used? I couldn't find any SQL command to show me the encryption status of connections. What use has an option SSL if you can if I can't find out whether it actually does SSL or not... I simply require it to do and to not connect at all if it can't. So do I see this right that MySQL Query Browser for Windows does *not* support SSL and keeps me confused about whether it does? I really think this is a bug that needs to be fixed, either way. Remove that SSL button or make it work. When I see a feature, I expect it to work. But definitely not to not work *and* not tell me so. Is there any free alternative to Query Browser that does support SSL meanwhile? -- Yves Goergen LonelyPixel [EMAIL PROTECTED] http://beta.unclassified.de – My web laboratory. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to find out about SSL connection?
On 30.05.2006 16:28 (+0100), Joerg Bruehe wrote: To find out whether your server(s) support(s) or not, you need to check the corresponding variables. All this is described in the manual, section 5.9.7. Using Secure Connections: I still think that the client should actually inform me when I select use SSL and it doesn't. Now I think I managed to make MySQL 5 accept SSL connections, at least that have_openssl variable has turned from DISABLED to YES. I added an SSL certificate to the my.cnf file. But now I can only connect to the server from Query Browser without the SSL checkbox. When it's checked, it says Could not connect to the specified instance. MySQL Error Number 0 The ping works fine, without SSL I can connect to the server. -- Yves Goergen LonelyPixel [EMAIL PROTECTED] http://beta.unclassified.de – My web laboratory. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to find out about SSL connection?
On 21.05.2006 07:35 (+0100), paul rivers wrote: Are you certain? Which version are you running? I don't have it on MySQL 4.0 and on MySQL 5.0 it has the value 0 so I guess it's not connecting through SSL. -- Yves Goergen LonelyPixel [EMAIL PROTECTED] http://beta.unclassified.de – My web laboratory. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to find out about SSL connection?
Hello, I'm using MySQL Query Browser on Windows XP to connect to a remote MySQL 4.0 and 5.0 database server, both on Linux. In Query Browser, I can check the options Use SSL if available but how do I know if it is available and used? I couldn't find any SQL command to show me the encryption status of connections. What use has an option SSL if you can if I can't find out whether it actually does SSL or not... I simply require it to do and to not connect at all if it can't. -- Yves Goergen LonelyPixel [EMAIL PROTECTED] http://beta.unclassified.de – My web laboratory. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to find out about SSL connection?
On 21.05.2006 00:38 (+0100), paul rivers wrote: - Inspect the 'show status' variable of Ssl_accepts after a connection attempt on an otherwise quiet mysql instance. I have no such status variable in my server. -- Yves Goergen LonelyPixel [EMAIL PROTECTED] http://beta.unclassified.de – My web laboratory. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to move the MySQL data directory?
On 03.05.2006 01:21 (+0100), paul rivers wrote:
Specify the data dir in the local my.cnf and be sure your instance uses it
by starting it with the --defaults-file parameter set to that instance's
local copy.
Okay, since hacking seems to be required anyway, I hacked it the
straight-forward and least-change way. I already had datadir=... changed
in the init script to the correct location. Now I also insert some
variables corrections in bin/mysqld_safe:
# here are the lines where ledir is set totally wrong...
MY_BASEDIR_VERSION=`pwd`
ledir=${MY_BASEDIR_VERSION}/bin
DATADIR=`pwd | sed -r s;/usr/local/;/var/;`/data
defaults=--defaults-file=${DATADIR}/my.cnf
# user=... and so on
This does the job pretty well for MySQL 4.0. Need to do it with every
upgrade, but I think I can automate it.
MySQL 5.0 required a less invasive hack though. I saw that setting
datadir= in the proposed init script is for nothing at the very
beginning since it's overwritten again right below. So moving that line
further down helped. Then the mysqld_safe call in the 'start' section
required an additional parameter --defaults-file=$datadir/my.cnf to make
it read my socket name, IP port etc.
Now both servers are up and running fine again, side by side, with the
*entire* data directory moved somewhere else, saving me from handling
that with every upgrade. Thanks for your help, I thought it could be
done an easy way but it seems nobody has thought about doing that
before. At least I don't have the impression, from reading the scripts.
--
Yves Goergen LonelyPixel [EMAIL PROTECTED]
http://beta.unclassified.de – My web laboratory.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to move the MySQL data directory?
On 03.05.2006 12:34 (+0100), Logan, David (SST - Adelaide) wrote: You could also have changed the directory in the global /etc/my.cnf file by setting datadir=/path/to/mysql/data This is pretty simple and works a lot easier than hacking the init scripts. As I said, there are two MySQL servers and there is no such global config file which all scripts seem to assume. There is one for each server and they are located in the datadir to make it easy (following the default setup). -- Yves Goergen LonelyPixel [EMAIL PROTECTED] http://beta.unclassified.de – My web laboratory. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to move the MySQL data directory?
Hello, I've just wanted to update MySQL 5.0 on my test machine that will be a productive server very soon. But upgrading MySQL like installing it after MySQL's guide brings a problem: I'd need to move the data directory to the new programme directory every time. So I want to move the datadir outside the application's directory, from /usr/local/mysql5/data (with mysql5 being a symlink to mysql-5.0.xx-...) to /var/mysql5/data. So I moved the entire data directory to the new location and tried to start the MySQL server. After a while printing out dots, it says ERROR! and that's it. When I have tried it with MySQL 4.0 and after adding some more of the suppressed output to the scripts, it seems like the startup script expects at least the mysql database to be located inside $basedir/data/mysql which of course is not what I meant to do. Is this true that MySQL wants all its databases to be inside the programme directory? Is there any other way to move the datadir out there without hacking all the scripts - over and over with each update? (Then I could just as well move the datadir each time...) -- Yves Goergen LonelyPixel [EMAIL PROTECTED] http://beta.unclassified.de – My web laboratory. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to move the MySQL data directory?
Well, moving the datadir to the new MySQL 5.0.21 directory, messing around with all the stupid symlinks, it seems I have finally managed to delete that datadir... It was empty anyway, yet, but this is an extremely dangerous task. So I really need to move the datadir to a safe place, outside all that symlinked chaos. With that done, what is the proposed upgrade method anyway? 1. unpack the tarball to /usr/local/mysql-$version 2. chown root:mysql it all (what for, actually?) 3. stop the server 4. update the symlink /usr/local/mysql5 to the new directory 5. start the server Would that be okay? -- Yves Goergen LonelyPixel [EMAIL PROTECTED] http://beta.unclassified.de – My web laboratory. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to move the MySQL data directory?
On 02.05.2006 22:24 (+0100), Dan Buettner wrote: The easiest way might be to tell mysql in the config file where to look for the data directory. In your my.cnf file (typically /etc/my.cnf) under the [mysqld] section: datadir = /Volumes/mysql-data/data One of the main problems with a global config file is that I am running two servers on the same machine (MySQL 4.0 and 5.0, each with separate data directories of course). Currently I am using config files (my.cnf) in each server's data directory, but telling the server about another datadir inside that other datadir is obiously not reasonable. -- Yves Goergen LonelyPixel [EMAIL PROTECTED] http://beta.unclassified.de – My web laboratory. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Restrict MySQL server 4/5 to single IP
On 11.04.2006 09:35 (+0100), Jorrit Kronjee wrote: Hostnames resolve into IP adresses, which are used to connect to the MySQL server. MySQL doesn't care if you connect via a hostname or via an IP address. It's not virtual hosting like Apache does. I know that. But to access different MySQL servers through different hostnames, you need different IP addresses for them to listen on. -- Yves Goergen LonelyPixel [EMAIL PROTECTED] This message represents the official view of the voices in my head. http://newsboard.unclassified.de - Unclassified NewsBoard Forum -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Restrict MySQL server 4/5 to single IP
On 09.04.2006 23:40 (+0100), Jorrit Kronjee wrote: You seem to be best off with a setup where you've got the MySQL5 UNIX socket disabled, MySQL5 bound to one specific IP address, MySQL4 listening on 127.0.0.1 and a simple port forwarding rule to MySQL4. I'm missing the part to connect to MySQL 4.0 via mysql4.mydomain and to MySQL 5.0 via mysql5.mydomain... But I'll try to do it by restricting access to the primary hostname/IP for now. -- Yves Goergen LonelyPixel [EMAIL PROTECTED] This message represents the official view of the voices in my head. http://newsboard.unclassified.de - Unclassified NewsBoard Forum -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Restrict MySQL server 4/5 to single IP
On 10.04.2006 18:32 (+0100), Jorrit Kronjee wrote: I'm not entirely sure what you mean. Are `mysql4.mydomain' and `mysql5.mydomain' hostnames? Yes, as I have explained earlier in this thread. -- Yves Goergen LonelyPixel [EMAIL PROTECTED] This message represents the official view of the voices in my head. http://newsboard.unclassified.de - Unclassified NewsBoard Forum -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Restrict MySQL server 4/5 to single IP
On 09.04.2006 01:03 (+0100), Eric Braswell wrote: Does that make sense? Did I misunderstand? That's exactly what I'm doing right now. In my test network: MySQL 4.0 - 192.168.0.32 (mysql4.myhost) MySQL 5.0 - 192.168.0.33 (mysql5.myhost) But what I wanted to do is: MySQL 4.0 - 192.168.0.32 and 127.0.0.1 MySQL 5.0 - 192.168.0.33 to a) keep both servers on different IPs with DNS names and b) let system applications connect via the localhost interface which lets me put additional security into it by only allowing access from localhost for these applications, while all other users may connect from everywhere. And of course, connecting to 127.0.0.1 is the obvious way in a small webhosting environment, but that's not so important, I can tell my users to change their database connection to the new name (mysql4) when I migrate to the new server. Or is there another way to only allow certain users to connect from localhost? As I think about it, a local TCP forwarder would accept connections on localhost, but MySQL won't see that, so this wouldn't work anyway. -- Yves Goergen LonelyPixel [EMAIL PROTECTED] This message represents the official view of the voices in my head. http://newsboard.unclassified.de - Unclassified NewsBoard Forum -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Restrict MySQL server 4/5 to single IP
On 09.04.2006 20:02 (+0100), Eric Braswell wrote: I'm going to assume you are using some kind of Unix-like platform. Correct, it's a Debian Linux x86. When you connect to localhost, you are actually connecting by default through a Unix socket file, not TCP/IP, because it is much faster. Thus it is perfectly possible to do what you outline without having to specify multiple IPs in the bind-address option. External connections will use TCP/IP, internal will use a unix socket file. I know this strange behaviour, but it's not working for me. Because I'm running two servers, I needed to alter the socket names so that in the end, clients won't find the default socket location anymore. So I'm not connecting to localhost but to 127.0.0.1, which works again. If you wanted to use -only- the unix socket file to connect to a particular instance, thus completely disallowing external connections, you can use the skip-networking option. None of the MySQL servers should not be reachable by TCP/IP at all, so skip-networking is not what I'm looking for. Or is there another way to only allow certain users to connect from localhost? Of course. Grant privileges only to connect to localhost. You mean say localhost as hostname for the GRANT command? From my experience (which is actually not so clear in this) this doesn't work. Granting access only for connections from localhost when connecting to the server actually from the same host but through its external IP/hostname, it won't let me in. Need to test it further. I guess the correct way would be to allow access from the external IP of the server? -- Yves Goergen LonelyPixel [EMAIL PROTECTED] This message represents the official view of the voices in my head. http://newsboard.unclassified.de - Unclassified NewsBoard Forum -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Restrict MySQL server 4/5 to single IP
On 04.04.2006 23:17 (+0100), Eric Braswell wrote: my.cnf: bind-address = ip Will probably do the trick. How can I enter multiple IP addresses there? This isn't documented online. I need to bind it to one specific external address and additionally to localhost (127.0.0.1). The other server is only bound to another external address. -- Yves Goergen LonelyPixel [EMAIL PROTECTED] Does the movement of the trees make the wind blow? http://newsboard.unclassified.de - Unclassified NewsBoard Forum -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Restrict MySQL server 4/5 to single IP
On 08.04.2006 18:31 (+0100), Kishore Jalleda wrote: bind-address = Comma seperated list of IP's Doesn't work. MySQL binds to address 255.255.255.255 instead. Also a colon-separated list does this. bind-address = IP1 bind-address = IP2 This always takes the last option, so if I add 127.0.0.1 after the other IP, it will only bind to this one. Any more suggestions? Maybe someone from the dev team? Should I install a local port forwarder? -- Yves Goergen LonelyPixel [EMAIL PROTECTED] Does the movement of the trees make the wind blow? http://newsboard.unclassified.de - Unclassified NewsBoard Forum -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Restrict MySQL server 4/5 to single IP
On 08.04.2006 23:14 (+0100), Eric Braswell wrote: Yves Goergen wrote: How can I enter multiple IP addresses there? This isn't documented online. I need to bind it to one specific external address and additionally to localhost (127.0.0.1). The other server is only bound to another external address. Why do you want to do this? Currently, I have one IP address on my server, with one MySQL server. In the near future, I'll have a server with multiple IP addresses and I'm going to install MySQL 4.0 and 5.0 in parallel. My first design was to use different ports for both servers, but that's always a little complicated to configure for the clients. Now I want to use one IP for one MySQL server. Both are external addresses, which can be assigned with a DNS name for simple access. But the 4.0 server still has some system tasks and also for legacy reasons, I'd like to keep the 4.0 server listening on the localhost interface. This also allows me to assign more strict access rights for these system-related tasks. They can be limited to the local host instead of any host. -- Yves Goergen LonelyPixel [EMAIL PROTECTED] Does the movement of the trees make the wind blow? http://newsboard.unclassified.de - Unclassified NewsBoard Forum -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Restrict MySQL server 4/5 to single IP
Hi, I have a machine with multiple IP addresses on my network interface and I have setup multiple MySQL servers on the machine, version 4.0 and 5.0. Currently, they're all listening on all IP addresses on different ports (3306 and 3307) but I'd like to make use of the second IP to make it easier to connect to each MySQL instance with different DNS names (mysql4.mydomain and mysql5.mydomain) on the default port. Only I couldn't find any hint on how to tell the MySQL server to listen only on a single IP address. I can change the port, the UNIX socket and disable IP networking entirely, but no idea how to specify a custom IP. Any hints? -- Yves Goergen LonelyPixel [EMAIL PROTECTED] Does the movement of the trees make the wind blow? http://newsboard.unclassified.de - Unclassified NewsBoard Forum -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Restrict MySQL server 4/5 to single IP
On 04.04.2006 23:17 (+0100), Eric Braswell wrote: my.cnf: bind-address = ip Will probably do the trick. Thank you, that's what I was looking for. -- Yves Goergen LonelyPixel [EMAIL PROTECTED] Does the movement of the trees make the wind blow? http://newsboard.unclassified.de - Unclassified NewsBoard Forum -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
