Re: Understanding mysql NULL handling ...
On Wed, Jun 13, 2007 at 12:50:03PM -0700, Mufaddal Khumri wrote: mysql select * from t1 where sid != 2; As you can see, the rows that had sid = NULL did not get returned in the results when i did ... where sid != ; Question: Is this behaviour correct and is in accordance to the SQL standard or is it specific to MySQL ? It is in accordance to the standard. NULL is not equal to anything, and it is not unequal to anything. NULL is just the magic ineffable NULL. If you want your NULL rows too you must use: SELECT * FROM t1 WHERE sid !=2 OR sid IS NULL; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: maximum number of records in a table
On Tue, Jun 12, 2007 at 11:09:41AM -0400, kalin mintchev wrote: I guess a lot of that depends what an acceptable query execution time for you is. well... i don't really know. 30 secs maximum?! i've never worked with such huge tables. 3 - 5 million records is fine but i've never worked on a db with a table with 100 000 000 records. I wouldn't worry about it. I have a table here with nearly a billion rows, and it fetches based on the primary key in 0.00 sec. my calculation was mostly based on resources - like ram. like i mentioned earlier the .MYD and .MYI files together on the current one that i have - which has about 1.2 million records - are 90 mgb. are the .MYI files kept in ram or both .MYD and .MYI? Neither of them are kept in RAM. You want a reasonable amount of memory for buffers, query cacheing, etc, but you don't need to hold any of the files in memory. My main concern with large tables is disk space, especially if you're ever going to want to do ALTER TABLE, when you will need enough free space to hold the existing and new copies of the table on disk at the same time. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: only select privilege
On Fri, Jun 08, 2007 at 03:14:18PM +0530, Ananda Kumar wrote: How do i give only select privilege to a specific database. GRANT SELECT ON abc.* to 'ab'@'%' identified by 'ab'; Like that. mysql select select_priv from user where user='qa'; +-+ | select_priv | +-+ | N | +-+ But is shows as N. Can you please help me. If that was 'Y' then the user would have select privileges on *all* databases. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: only select privilege
On Fri, Jun 08, 2007 at 04:25:00PM +0530, Ananda Kumar wrote: So, what ever i did is right? Yes. User 'ab' can SELECT on all tables in database 'abc'. -- 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 Wed, Jun 06, 2007 at 05:56:44PM -0700, Peter Rosenthal wrote: On 04/06/07, Jon Ribbens [EMAIL PROTECTED] wrote: Um, what? Both that and the methods described in the magazine are completely wrong. You use mysql_real_ecape_string(), that's it. I would disagree on the use of mysql_real_escape_string(). The use of placeholders is much safer from a maintenance and 'oops look I typoed it' perspective. That's not a disagreement - you're just talking about a different abstraction layer. Behind the scenes your 'placeholder' API will be using mysql_real_escape_string(). -- 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 Mon, Jun 04, 2007 at 02:44:25PM -0700, 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); Um, what? Both that and the methods described in the magazine are completely wrong. You use mysql_real_ecape_string(), that's it. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB dropping records / MyISAM working as it should
On Tue, May 15, 2007 at 04:13:33PM -0500, Kenneth Loafman wrote: Can't tell. The docs are somewhat lacking in detail, however, if I do a db.autocommit(True) it works as it should. Will have to dig into the API code and see if that is where the semantic discontinuity lies. The MySQL-python-1.2.2/doc/FAQ.txt says: | Starting with 1.2.0, MySQLdb disables autocommit by default, as | required by the DB-API standard (PEP-249). If you are using InnoDB | tables or some other type of transactional table type, you'll need | to do connection.commit() before closing the connection, or else | none of your changes will be written to the database. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB dropping records / MyISAM working as it should
On Tue, May 15, 2007 at 06:39:21PM -0500, Kenneth Loafman wrote: Interesting... guess the intent was a disconnect that would break code trying to work on MySQL, regardless of engine selected. That decision makes it two products, MySQL/MyISAM and MySQL/InnoDB with different semantics. Yes, you can work around it, but its not transparent. I think the purist's response to that would be that it is a bug in MySQL that MySQL does not support transactions always. But anyone for whom that is a problem probably isn't on this list ;-) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysterious 'Lost connection' errors
On Thu, May 10, 2007 at 05:17:12PM +0100, Jon Ribbens wrote: I suspect some sort of bug in the MySQLd authentication code. I've managed to discover using --debug that it's due to MySQLd failing to handle EINTR from read() in the authentication stage. I've filed a bug report: http://bugs.mysql.com/bug.php?id=28359 Does anyone know how/where to best attract the attention of a MySQL developer to this bug? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysterious 'Lost connection' errors
On Thu, May 10, 2007 at 03:37:26AM +0100, Jon Ribbens wrote: I've just upgraded all the clients and servers to 5.0.41 (which looks like it just came out); I'll see what happens. It hasn't solved the problem, but it has changed the error message to: OperationalError: (2013, Lost connection to MySQL server at 'reading authorization packet', system error: 0) (still nothing in the logfile). Does this ring any bells for anyone? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysterious 'Lost connection' errors
On Thu, May 10, 2007 at 08:58:37AM -0600, mos wrote: If you do a google search: http://www.google.ca/search?q=lost+mysql+connection+%22reading+authorization+packet%22hl=enstart=90sa=N you'll find about a hundred web sites encountering the exact same error. Indeed, I noticed that ;-) Maybe the problem is with the hardware, like your network card disconnecting from the MySQL server machine? Or TCP/IP? The only comment I could find from a MySQL employee suggested that. But I don't believe them. There are no other network problems visible, and the 'authorization packet' is mid-way through the connection setup (i.e. the TCP/IP connection has already been successfully used to both send and receive data if we get as far as the 'authorization packet'). I suspect some sort of bug in the MySQLd authentication code. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Mysterious 'Lost connection' errors
We are using MySQL 5.0.27 on RedHat Enterprise Linux ES release 4, and the MySQL-python-1.2.1_p2 connector. We are getting intermittent mysterious errors as follows: OperationalError: (2013, 'Lost connection to MySQL server during query') when attempting to connect to the MySQL server (note: on the actual connection attempt, this is before even trying a query). There doesn't appear to be any particular pattern to when these errors occur. The client and server are on different machines, communicating via TCP, but I have not managed to find any networking problems. Does anyone have any suggestions as to what the problem might be, or how we might go about trying to solve it? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysterious 'Lost connection' errors
On Wed, May 09, 2007 at 09:22:34AM -0400, Michael Dykman wrote: I realize that's quite unlikely that you have performed a similar sloppy import but there is likely some edge condition on your server (wierd permissions in the data directory, corruoted tables, etc.) but I still recommend that you scrutinize your server logs for evidence of a spontaneous restart. If that turns up nothing, you might try a fresh install of mysql on a separate host to see if the problem persists. Thanks for your suggestions. The hostname.err log contains absolutely nothing however, just the usual 'mysqld started' and 'Version:' lines. Certainly there's nothing about any restart. Worst case, there is an upgrade patch available which might magically raise you above the problem. Indeed, I see there is 5.0.37 now. I'd rather not go through an upgrade though unless I knew it was likely to fix the problem. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysterious 'Lost connection' errors
On Wed, May 09, 2007 at 11:17:59AM -0400, Michael Dykman wrote: When we first examined our server logs, we saw the same.. in our case (again) it was only when we slowed down and examined the timestamps on the start/stop messages that we realized that the server was restarting at unexpected intervals. The last restart, according to both the content of the log, and the operating system timestamp on the log file itself, was 6 days ago. The 'lost connection' message has happened several times today, however. (how did you get your binaries? pre-compiled from the archive or build your own? and for what OS?) We used the official RPMs from www.mysql.com for our exact version of RedHat Linux (RedHat Enterprise Linux ES Release 4, 64-bit). When you do find the cause let me know; Im thinking of starting a collection :-) I'll keep you informed ;-) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query problem
On Wed, May 09, 2007 at 07:14:38PM +0200, Martijn Tonies wrote: I have a table of properties that is linked to a table f images with a one property to many images relationship. I have manged this with nested queries but want to try and do it on one line. My current query $query = SELECT * FROM images, properties WHERE images.property_id = properties.property_id; As you can see from the query this returns a row for every image so if a property has 3 images associated with it it will be returned 3 times. What exactly is your question? I think he somehow wants to return each property once only but still have every image returned in the result. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysterious 'Lost connection' errors
On Wed, May 09, 2007 at 10:07:41PM -0400, Mathieu Bruneau wrote: We found a similar issue because we were using persistent connection in php and had a firewall between the mysql and the webserver. The problem is that our persistent connection were setup for lasting up to something like 8 hours but the firewall was keeping state of the connection only for up to 1 hour when no data was transferred. After the firewall had flushed the state of the connection and that the webserver were trying to communicate through it, he was reporting Lost connection to mysql server during query It's a good suggestion, but I'm pretty sure there's no firewall acting between the client and the server, and that the connection is not being persisted (i.e. all connection attempts are genuine new connections). I've just upgraded all the clients and servers to 5.0.41 (which looks like it just came out); I'll see what happens. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MyISAM vs InnoDB
Miles Thompson [EMAIL PROTECTED] wrote: At 07:56 AM 11/1/2006, Leandro Guimarães Faria Corcete DUTRA wrote: snip .. further notices; and SolidDB, which is still β. Help this poor English-speaker - what's the symbol you use to describe SolidDB? I assume it is a beta character, since solidDB for MySQL is indeed in beta. See http://dev.soliddb.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How can I do a SELECT without locking the table against updates?
I have a simple single-table SELECT query that takes of several minutes to complete, due to a very large number of result rows being involed. I don't think there is any way to optimise the query - MySQL is already using the appropriate index etc, it's just a huge table and the query has a large result set. While the SELECT is executing, any INSERT/UPDATE queries on the table are blocked. Is there any way I can tell MySQL *not* to lock the table while the SELECT is executing? I know this could lead to ambiguities, but in this application it doesn't matter - if, for example, a new matching row is added after the SELECT starts but before it finishes, it simply doesn't matter whether that row is returned in the results or not. If there is no way to do this in MySQL, does anyone have any innovative suggestions as to how I could work around the problem? The table is very large, so solutions involving multiple copies are tricky to implement. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: No more room in index file (136) - but index is not full?
Does nobody have any ideas on this? It's quite perplexing. It's MySQL Ver 14.6 Distrib 4.1.5-gamma, if it makes a difference. Jon Ribbens [EMAIL PROTECTED] wrote: I am getting Error 136 No more room in index file on a table, but the table does not appear to be full in any way. As you can see from the myisamchk -dv output below, both the datafile length and the keyfile length are much smaller than their respective maximums. The table is on a Linux ext3 partition and the partition is nowhere near full. Does anyone know how to fix this problem? I have already tried 'alter table foo max_rows=38807046144', which took over 30 hours to complete and hasn't had any noticeable effect. $ uname -a Linux fooserver 2.4.21-20.EL #1 Wed Aug 18 20:46:27 EDT 2004 i686 athlon i386 GNU/Linux $ ls -l foo.* -rw-rw1 mysqlmysql23990598560 Feb 5 09:19 foo.MYD -rw-rw1 mysqlmysql18353124352 Feb 5 09:19 foo.MYI -rw-rw1 mysqlmysql9746 Feb 4 03:17 foo.frm # myisamchk -dv foo MyISAM file: foo Record format: Fixed length Character set: latin1_swedish_ci (8) File-version:1 Creation time: 2005-02-04 3:17:26 Recover time:2005-02-05 8:59:54 Status: open,changed Data records:749706205 Deleted blocks: 0 Datafile parts: 749706205 Deleted data: 0 Datafile pointer (bytes):4 Keyfile pointer (bytes):4 Datafile length: 23990598560 Keyfile length: 18353124352 Max datafile length: 137438953470 Max keyfile length: 4398046510079 Recordlength: 32 table description: Key Start Len Index Type Rec/key Root Blocksize 1 2 4 unique unsigned long 0 6117707776 1024 2 7 4 multip. unsigned long 0 12235415552 1024 3 114 multip. unsigned long 0 18353123328 1024 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
No more room in index file (136) - but index is not full?
I am getting Error 136 No more room in index file on a table, but the table does not appear to be full in any way. As you can see from the myisamchk -dv output below, both the datafile length and the keyfile length are much smaller than their respective maximums. The table is on a Linux ext3 partition and the partition is nowhere near full. Does anyone know how to fix this problem? I have already tried 'alter table diagnostics max_rows=38807046144', which took over 30 hours to complete and hasn't had any noticeable effect. $ uname -a Linux fooserver 2.4.21-20.EL #1 Wed Aug 18 20:46:27 EDT 2004 i686 athlon i386 GNU/Linux $ ls -l foo.* -rw-rw1 mysqlmysql23990598560 Feb 5 09:19 foo.MYD -rw-rw1 mysqlmysql18353124352 Feb 5 09:19 foo.MYI -rw-rw1 mysqlmysql9746 Feb 4 03:17 foo.frm # myisamchk -dv foo MyISAM file: foo Record format: Fixed length Character set: latin1_swedish_ci (8) File-version:1 Creation time: 2005-02-04 3:17:26 Recover time:2005-02-05 8:59:54 Status: open,changed Data records:749706205 Deleted blocks: 0 Datafile parts: 749706205 Deleted data: 0 Datafile pointer (bytes):4 Keyfile pointer (bytes):4 Datafile length: 23990598560 Keyfile length: 18353124352 Max datafile length: 137438953470 Max keyfile length: 4398046510079 Recordlength: 32 table description: Key Start Len Index Type Rec/key Root Blocksize 1 2 4 unique unsigned long 0 6117707776 1024 2 7 4 multip. unsigned long 0 12235415552 1024 3 114 multip. unsigned long 0 18353123328 1024 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
