Re: optimization strategies based on file-level storage
At 10:47 PM 6/16/2011, Johan De Meersman wrote: - Original Message - From: Bennett Haselton benn...@peacefire.org Do you happen to know the answer to my other problem -- if I have TEXT and BLOB columns but all my other columns are fixed-length, can I still get the benefit of faster lookups resulting from fixed-length rows, if each row just contains a fixed-length reference to the actual TEXT or BLOB data which is stored elsewhere? entirely unsure, but given the single-datafile structure of MyISAM tables, I strongly suspect BLOBS get stored inline. I can't find a source that says for sure. http://dev.mysql.com/doc/refman/5.0/en/storage-requirements.html does say: The maximum size of a row in a MyISAM table is 65,535 bytes. This figure excludes BLOB or TEXT columns, which contribute only 9 to 12 bytes toward this size... For BLOB and TEXT data, the information is stored internally in a different area of memory than the row buffer. But that's talking about memory, not disk. When people talk about performance improvements from using fixed-length rows, are they talking primarily about memory or hard disk? Hold up though, I just got this reply from posting the question in a forum: http://forums.mysql.com/read.php?21,423433,423846 which says Almost always the discussions recommending Fixed length records in MyISAM are myths. The apparent performance improvement is swamped by the performance loss of shoveling around the wasted padding bytes and goes on to give reasons. Actually, that does make sense that it's a myth. I was surprised to hear so many sources claiming that there was a big performance increase from being able to find row N by jumping to position N*rowlength. Because even with variable-length rows, you can just store a table associating row numbers with the position of the row in the file, can't you -- which would mean it would only take one near-instantaneous lookup to be able to jump to the row you're looking for. What I was really trying to figure out was why it takes me 4 hours to add a new column to my 22-million-row table, and whether a different table design can avoid that problem. That reply in the forum says, ALTER TABLE ... ADD COLUMN will always copy the entire table over, and rebuild all the indexes. (And, effectively, do an OPTIMIZE.) Do _not_ leave space for extra columns, it won't help. I'm about to reply and point out the trick that you suggested to me: create dummy columns early and then just rename them later :) -Bennett -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: optimization strategies based on file-level storage
At 11:45 AM 6/14/2011, Johan De Meersman wrote: - Original Message - From: Bennett Haselton benn...@peacefire.org modifications. (For example, the question I asked earlier about whether you can declare extra space at the end of each row that is reserved for future columns.) That question I can answer: you can't reserve space, but if you know what kind of rows you'll want to add later you can pre-add them (and incur the accompanying storage cost), and simply rename them appropriately later. Thanks. It would be more flexible if I could declare, say, 50 bytes, and decide later if I wanted to use them for a datetime, a char(n), or an int, but this is still helpful :) Do you happen to know the answer to my other problem -- if I have TEXT and BLOB columns but all my other columns are fixed-length, can I still get the benefit of faster lookups resulting from fixed-length rows, if each row just contains a fixed-length reference to the actual TEXT or BLOB data which is stored elsewhere? -Bennett -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
optimization strategies based on file-level storage
I'm looking for some tips tricks documentation that explains how different data types in rows are stored at the file level (in MyISAM tables, at least), and how to optimize tables for faster queries, updates, table definition modification, etc. based on this knowledge. For example, I've heard that if all of your columns are fixed-length, that makes it faster to find individual rows since row N is located at position N*rowlength in the file. (And, presumably, having just one variable-length column will slow things down considerably.) But I've also read that having TEXT and BLOB columns will also slow down row-finding operations. This seems to make no sense because I thought TEXT was not actually stored in the row, but the row just stored a constant-length reference to the TEXT whose actual data was stored somewhere else. Is this correct? Then is it incorrect to say that a TEXT column will slow down the locate-row-N operation, assuming all other columns are fixed-length? This is the kind of thing I'm looking for a document to explain. Another example: It sometimes takes me an extremely long time to add new columns to a table. What if I had a table with all fixed-length columns, and I reserved some space at the end of each row to be used for columns to be added in the future. Would it then be possible to add new columns much more quickly? You wouldn't have to move around the existing row data to make room for the new column (although presumably you would still have to *write* to the place in reach row where the new column had just been defined, to fill it in with its default value). In particular, I'm not looking for a list of optimization tricks, so much as a document that explains how the rows are stored at the file level, and thereby explains how the optimization tricks *follow logically from* this information. The reason is that if I just have a grab-bag of optimization hints (of which I've found many on the Web), some of them will be not applicable to my situation, or just plain wrong, and I'll have no way of knowing which ones. But if you know *why* something works, you can more easily figure out if it applies to your situation. -Bennett -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: optimization strategies based on file-level storage
At 05:46 AM 6/14/2011, Carlos Eduardo Caldi wrote: Hello Bennett On the Mysql developer site have a grate documentation, try the links above. http://dev.mysql.com/doc/refman/5.0/en/optimizing-database-structure.html http://dev.mysql.com/doc/refman/5.0/en/data-size.html Thanks, this gets me a little closer to the answer but doesn't really provide the level of detail that I'm looking for. For example, it says: For MyISAM tables, if you do not have any variable-length columns (VARCHAR, TEXT, or BLOB columns), a fixed-size row format is used. This is faster... I still don't understand: If TEXT and BLOB columns are stored not by putting the data in the row but by having the row store a reference to the TEXT/BLOB data stored somewhere else, then can't a row with TEXT and BLOB data types *still* be a fixed-size row, with the resulting increased speed? My main motivation is that I have a table with 22 million records and it takes a few hours for me to add a new column to it. I suspect this would be faster if I designed the table correctly from the beginning, and I want to change it to a smarter design, but I only want to do it once. So I want to understand really thoroughly *why* a different design would make it faster to complete the table modifications. (For example, the question I asked earlier about whether you can declare extra space at the end of each row that is reserved for future columns.) Att. Carlos, Date: Tue, 14 Jun 2011 01:44:47 -0700 To: mysql@lists.mysql.com From: benn...@peacefire.org Subject: optimization strategies based on file-level storage I'm looking for some tips tricks documentation that explains how different data types in rows are stored at the file level (in MyISAM tables, at least), and how to optimize tables for faster queries, updates, table definition modification, etc. based on this knowledge. For example, I've heard that if all of your columns are fixed-length, that makes it faster to find individual rows since row N is located at position N*rowlength in the file. (And, presumably, having just one variable-length column will slow things down considerably.) But I've also read that having TEXT and BLOB columns will also slow down row-finding operations. This seems to make no sense because I thought TEXT was not actually stored in the row, but the row just stored a constant-length reference to the TEXT whose actual data was stored somewhere else. Is this correct? Then is it incorrect to say that a TEXT column will slow down the locate-row-N operation, assuming all other columns are fixed-length? This is the kind of thing I'm looking for a document to explain. Another example: It sometimes takes me an extremely long time to add new columns to a table. What if I had a table with all fixed-length columns, and I reserved some space at the end of each row to be used for columns to be added in the future. Would it then be possible to add new columns much more quickly? You wouldn't have to move around the existing row data to make room for the new column (although presumably you would still have to *write* to the place in reach row where the new column had just been defined, to fill it in with its default value). In particular, I'm not looking for a list of optimization tricks, so much as a document that explains how the rows are stored at the file level, and thereby explains how the optimization tricks *follow logically from* this information. The reason is that if I just have a grab-bag of optimization hints (of which I've found many on the Web), some of them will be not applicable to my situation, or just plain wrong, and I'll have no way of knowing which ones. But if you know *why* something works, you can more easily figure out if it applies to your situation. -Bennett -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=ce_ca...@hotmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: many-inserts go faster the second time
At 02:53 AM 10/2/2009, Joerg Bruehe wrote: Hi Bennett, all! Bennett Haselton wrote: At 08:24 AM 9/25/2009, Dan Nelson wrote: In the last episode (Sep 25), Bennett Haselton said: I have a script that runs several times in the evening, and on each run it adds several thousand entries to a table. On the first run, it adds the entries rather slowly. But then on all subsequent runs (usually about a minute or two later), the many inserts go a lot faster. This is true regardless of how many entries are added by each run -- whether the first and second run both add 50,000 or the first and second run both add 10,000, the first run goes slowly and the second one goes fast. But by the following evening, the first run is back to going slowly again. It's as if in the minute or two following the first run of the script, MySQL catches its breath and realizes, hey, that table is getting a lot of entries added to it, so it waves some magic dust so that the next time I add a lot of entries, it goes a lot faster. (Hope I'm not losing anybody with the technical terminology here.) Then by the next evening the optimization parameter has exp^W^W^W^W the fairy dust has worn off. More likely, this is a relatively unused table, and the first batch of inserts pulls most of the index and some of the table data into RAM, which makes for much faster lookups on the next run. What do top and iostat stats show on both runs? I'd expect heavy disk usage and little CPU on the first run, and light disk and heavier CPU usage on the second. That's interesting, I can look at that next time I try it. But if that's the case, wouldn't the first run go slowly at first, but then pick up speed once all of the indexes etc. have been pulled into memory? Because that's not what I'm seeing -- if I insert 50,000 in the first run, it's slow all the way through, but then the second 50,000 get inserted quickly. Your fairy dust is called access pattern, evaluated by a LRU or similar policy. Don't forget you may have caching on two levels: database and operating system. Both have their own cache aging mechanisms. The details about caching and its effects will vary by the table handler you are using, MyISAM structures and policies definitely from InnoDB ones. Even if MySQL would not cache data and index pages, they would still reside in the operating system's file I/O cache, so the next access to them will be faster than the first one - regardless whether you read them or modify them. However, sooner or later they will be removed from all caches because they are not accessed until the next evening, whereas other pages were accessed and needed space in RAM. (Here, I ignore the case of a RAM which is larger than all data accessed for a day, it is too unlikely.) In the evening, when your job is run again, this starts anew. I understand that, but here's the problem: If the speed increase were just the result of values being recently accessed, then the speed increase should be a function of the number of inserts that I've already done. So if I insert 10,000 rows in one script run and then 10,000 rows in a second script run, and the second script run is a lot faster, then the first 10,000 inserts were enough to optimize everything. So that should mean if I do 50,000 inserts in a single script run, then the first 10,000 inserts should be enough to speed everything up. But that's not what I'm seeing. What I'm seeing is that if I do 10,000 inserts on the first run and 10,000 on the second, then the first run is slow and the second run is fast. On the other hand if I do 50,000 inserts on the first run and 50,000 on the second, then the entire first run is slow and the entire second run is fast. In any case, is there anything I can do to force MySQL to manually pre-optimize the entire table (even if it takes some time to do so, reading indexes into memory or whatever), other than kludgy solutions like doing a dummy insert of several thousand rows and then deleting them? -Bennett -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
many-inserts go faster the second time
I have a script that runs several times in the evening, and on each run it adds several thousand entries to a table. On the first run, it adds the entries rather slowly. But then on all subsequent runs (usually about a minute or two later), the many inserts go a lot faster. This is true regardless of how many entries are added by each run -- whether the first and second run both add 50,000 or the first and second run both add 10,000, the first run goes slowly and the second one goes fast. But by the following evening, the first run is back to going slowly again. It's as if in the minute or two following the first run of the script, MySQL catches its breath and realizes, hey, that table is getting a lot of entries added to it, so it waves some magic dust so that the next time I add a lot of entries, it goes a lot faster. (Hope I'm not losing anybody with the technical terminology here.) Then by the next evening the optimization parameter has exp^W^W^W^W the fairy dust has worn off. Is this a familiar phenomenon to anyone? Know why it's happening? And especially, is there any way I can tell MySQL to optimize that table before the first script run, so that the first run goes fast as well? In general, does anybody have familiarity with the strategies for speeding up the process of inserting a lot of rows at a time, and knows which ones really do work and which ones don't? This page: http://dev.mysql.com/doc/refman/5.0/en/insert-speed.html says that in the case of a single client doing lots of inserts, these would apply: - using insert statements with multiple values lists - change the bulk_insert_buffer_size variable - writing the data to be inserted into a temporary file, and then using the LOAD DATA INFILE syntax -Bennett -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: many-inserts go faster the second time
At 08:24 AM 9/25/2009, Dan Nelson wrote: In the last episode (Sep 25), Bennett Haselton said: I have a script that runs several times in the evening, and on each run it adds several thousand entries to a table. On the first run, it adds the entries rather slowly. But then on all subsequent runs (usually about a minute or two later), the many inserts go a lot faster. This is true regardless of how many entries are added by each run -- whether the first and second run both add 50,000 or the first and second run both add 10,000, the first run goes slowly and the second one goes fast. But by the following evening, the first run is back to going slowly again. It's as if in the minute or two following the first run of the script, MySQL catches its breath and realizes, hey, that table is getting a lot of entries added to it, so it waves some magic dust so that the next time I add a lot of entries, it goes a lot faster. (Hope I'm not losing anybody with the technical terminology here.) Then by the next evening the optimization parameter has exp^W^W^W^W the fairy dust has worn off. More likely, this is a relatively unused table, and the first batch of inserts pulls most of the index and some of the table data into RAM, which makes for much faster lookups on the next run. What do top and iostat stats show on both runs? I'd expect heavy disk usage and little CPU on the first run, and light disk and heavier CPU usage on the second. That's interesting, I can look at that next time I try it. But if that's the case, wouldn't the first run go slowly at first, but then pick up speed once all of the indexes etc. have been pulled into memory? Because that's not what I'm seeing -- if I insert 50,000 in the first run, it's slow all the way through, but then the second 50,000 get inserted quickly. -Bennett -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: error in instructions to set mysql root password?
Is there another, better forum to post this? If even just 10% of new MySQL users are running into this problem, it would save a lot of person-hours around the world to change the instructions that are displayed to new users when starting MySQL for the first time. -Bennett At 12:51 AM 9/15/2009, Bennett Haselton wrote: When I install and start the MySQL server on a new machine, it outputs: PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER ! To do so, start the server, then issue the following commands: /usr/bin/mysqladmin -u root password 'new-password' /usr/bin/mysqladmin -u root -h sls-ce12p12.dca2.superbservers.com password 'new-password' However if I try executing both of these commands as the instructions say, I get an error on the second command (in the text below, I have replaced my sample password with 'america'; I wouldn't really use that as my password): [r...@sls-ce12p12 ~]# /usr/bin/mysqladmin -u root password 'america' [r...@sls-ce12p12 ~]# /usr/bin/mysqladmin -u root -h sls-ce12p12.dca2.superbservers.com password 'america' /usr/bin/mysqladmin: connect to server at 'sls-ce12p12.dca2.superbservers.com' failed error: 'Access denied for user 'root'@'localhost' (using password: NO)' Knowing now what the syntax means, it seems clear why the second command fails. The first command executes with no authentication required because no password has been set for the root user yet. But by the time you execute the second command, the root user password has already been set, so if the MySQL server sees the second command as an attempt to set the 'root'@'localhost' password, it will fail because you're not authenticated. Now, I experimented some more on different machines, and on some machines, the second command gives an error and on other machines it doesn't. On all new mysql installations, there are separate entries created in the mysql 'user' table for 'root'@'localhost' and 'root'@'hostname', where hostname is the hostname of the machine. The deciding factor that determines whether or not the second mysqladmin command gives an error, appears to be, whether hostname on that machine resolves to 127.0.0.1, or resolves to the public IP address of the machine instead. IF hostname RESOLVES TO 127.0.0.1: then when you run the command: /usr/bin/mysqladmin -u root -h sls-ce12p12.dca2.superbservers.com password 'america' the MySQL server sees you connecting to IP address 127.0.0.1. So, rather than trying to update the 'root'@'sls-ce12p12.dca2.superbservers.com' password, it thinks you're trying to update the 'root'@'localhost' password. Of course this password has already been set by the previous command, so the second command fails. IF hostname RESOLVES TO THE PUBLIC IP ADDRESS INSTEAD: then when you run the command: /usr/bin/mysqladmin -u root -h sls-ce12p12.dca2.superbservers.com password 'america' the MySQL server sees you connecting to the public IP address, resolves that to the public hostname, and thus thinks you are trying to update the 'root'@'sls-ce12p12.dca2.superbservers.com' entry in the user table. And it allows that, because that password is still blank. So for all users who are in the former situation (where hostname resolves to 127.0.0.1), it appears that trying to run the second mysqladmin command will give them an error. It's not a catastrophe because at that point the biggest security hole -- the blank root password -- has been fixed. But it's still inadvertently training the user that security instructions are sometimes unreliable, or that sometimes in order to get things working, you have to skips parts of the security instructions and cut corners (which could be a bad habit if they ever end up skipping over something important). Plus, if an unprivileged user ever did figure out how to connect as 'root'@'hostname', they would be granted full MySQL root privileges, since that account is still unprotected. The upshot of all this is: Would it be a good idea for the MySQL installation program, rather than always telling the user: PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER ! To do so, start the server, then issue the following commands: /usr/bin/mysqladmin -u root password 'new-password' /usr/bin/mysqladmin -u root -h sls-ce12p12.dca2.superbservers.com password 'new-password' to instead test whether 'hostname' resolves to 127.0.0.1, and if it does, to skip giving the user the second command? Or to give the user some other command that would set the 'root'@'hostname' password without giving an error? -Bennett -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=benn...@peacefire.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
error in instructions to set mysql root password?
When I install and start the MySQL server on a new machine, it outputs: PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER ! To do so, start the server, then issue the following commands: /usr/bin/mysqladmin -u root password 'new-password' /usr/bin/mysqladmin -u root -h sls-ce12p12.dca2.superbservers.com password 'new-password' However if I try executing both of these commands as the instructions say, I get an error on the second command (in the text below, I have replaced my sample password with 'america'; I wouldn't really use that as my password): [r...@sls-ce12p12 ~]# /usr/bin/mysqladmin -u root password 'america' [r...@sls-ce12p12 ~]# /usr/bin/mysqladmin -u root -h sls-ce12p12.dca2.superbservers.com password 'america' /usr/bin/mysqladmin: connect to server at 'sls-ce12p12.dca2.superbservers.com' failed error: 'Access denied for user 'root'@'localhost' (using password: NO)' Knowing now what the syntax means, it seems clear why the second command fails. The first command executes with no authentication required because no password has been set for the root user yet. But by the time you execute the second command, the root user password has already been set, so if the MySQL server sees the second command as an attempt to set the 'root'@'localhost' password, it will fail because you're not authenticated. Now, I experimented some more on different machines, and on some machines, the second command gives an error and on other machines it doesn't. On all new mysql installations, there are separate entries created in the mysql 'user' table for 'root'@'localhost' and 'root'@'hostname', where hostname is the hostname of the machine. The deciding factor that determines whether or not the second mysqladmin command gives an error, appears to be, whether hostname on that machine resolves to 127.0.0.1, or resolves to the public IP address of the machine instead. IF hostname RESOLVES TO 127.0.0.1: then when you run the command: /usr/bin/mysqladmin -u root -h sls-ce12p12.dca2.superbservers.com password 'america' the MySQL server sees you connecting to IP address 127.0.0.1. So, rather than trying to update the 'root'@'sls-ce12p12.dca2.superbservers.com' password, it thinks you're trying to update the 'root'@'localhost' password. Of course this password has already been set by the previous command, so the second command fails. IF hostname RESOLVES TO THE PUBLIC IP ADDRESS INSTEAD: then when you run the command: /usr/bin/mysqladmin -u root -h sls-ce12p12.dca2.superbservers.com password 'america' the MySQL server sees you connecting to the public IP address, resolves that to the public hostname, and thus thinks you are trying to update the 'root'@'sls-ce12p12.dca2.superbservers.com' entry in the user table. And it allows that, because that password is still blank. So for all users who are in the former situation (where hostname resolves to 127.0.0.1), it appears that trying to run the second mysqladmin command will give them an error. It's not a catastrophe because at that point the biggest security hole -- the blank root password -- has been fixed. But it's still inadvertently training the user that security instructions are sometimes unreliable, or that sometimes in order to get things working, you have to skips parts of the security instructions and cut corners (which could be a bad habit if they ever end up skipping over something important). Plus, if an unprivileged user ever did figure out how to connect as 'root'@'hostname', they would be granted full MySQL root privileges, since that account is still unprotected. The upshot of all this is: Would it be a good idea for the MySQL installation program, rather than always telling the user: PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER ! To do so, start the server, then issue the following commands: /usr/bin/mysqladmin -u root password 'new-password' /usr/bin/mysqladmin -u root -h sls-ce12p12.dca2.superbservers.com password 'new-password' to instead test whether 'hostname' resolves to 127.0.0.1, and if it does, to skip giving the user the second command? Or to give the user some other command that would set the 'root'@'hostname' password without giving an error? -Bennett -- 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 show all locks on a table?
At 03:49 PM 8/25/2003 +0300, Egor Egorov wrote: Bennett Haselton [EMAIL PROTECTED] wrote: I found a way to do this before, but I didn't write down how I did it, so I don't remember it now. And I've searched http://www.mysql.com/doc/ in vain. What's the command to show all current locks on a database table? There is no command to show locks on the table. Ah, OK. I'm trying to think of what I must have seen earlier that made me think there was a command to do this. I think I was thinking of the SHOW PROCESSLIST command, which shows a list of threads and indicates whether they are waiting for a lock. I must have looked at that output and figured that I knew what the locked tables were. -Bennett [EMAIL PROTECTED] http://www.peacefire.org (425) 497 9002 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to show all locks on a table?
At 10:33 AM 8/21/2003 -0700, Bennett Haselton wrote: [already posted to mailing.database.mysql newsgroup but not to list; sorry for cross-post] I found a way to do this before, but I didn't write down how I did it, so I don't remember it now. And I've searched http://www.mysql.com/doc/ in vain. What's the command to show all current locks on a database table? Did I commit some unforgivable faux pas in asking this question? :) I didn't think it was very obscure (even *I* figured it out at one point :) but I didn't write down how I did it, and now I can't figure out how to do it again). Not complaining, since it's free, it's just that usually I've seen much harder questions get answered pretty fast... Did somebody send a reply, and I didn't get it (my Web server, and possibly my email, was down for part of that day)? Or is it not possible to show current locks on a table, and maybe I'm mis-remembering what I saw, when I thought I'd found a way to do it? -Bennett [EMAIL PROTECTED] http://www.peacefire.org (425) 497 9002 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
how to show all locks on a table?
[already posted to mailing.database.mysql newsgroup but not to list; sorry for cross-post] I found a way to do this before, but I didn't write down how I did it, so I don't remember it now. And I've searched http://www.mysql.com/doc/ in vain. What's the command to show all current locks on a database table? -Bennett [EMAIL PROTECTED] http://www.peacefire.org (425) 497 9002 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: table is read only after copying table files to new server
Thanks. Actually, my ISP helped me out and got this fixed. Here's what they wrote in the support log: Due to the method that the databases were copied over, I went into mysql used the grant option on all the databases to allow root access and ability to alter all the databases. Once this was done, I restarted mysql and I was able to run the example above. I had already run the command: grant all on tracerlock.test to bhaselto identified by password and that didn't work when I did it, but I didn't restart mysql after running the command. Probably it was the restart of MySQL that made the difference. By the way, if you're wondering what sort of ISP would have such good support that they would answer a question like this, it was RackSpace.com -- nothing but good things to say about their support process. -Bennett At 11:00 AM 8/17/2003 +0200, Petre Agenbag wrote: Hi Bennett I had a similar problem on my system a while back. With my particular case, the problem was the the default priv's on the new server for it's users did NOT include INSERT rights. Basically what I'm saying is: these are the reasons for a table to be (or appear to be) read-only: 1) Table files belong to someone other than mysql ( not the case here) 2) The permissions on the files are set read-only ( do a chmod to the correct permissions you require) 3)the user for that database has no insert, update, delete etc, rights. For the last case you need to issue the correct grants, or you can edit the mysql user table directly and change the N to Y in the appropriate fields. Then you should probably restart the mysqld daemon. On Sun, 2003-08-17 at 01:16, Bennett Haselton wrote: I'm trying to port my MySQL tables for a database called tracerlock from one server to another. On the old server, in the /var/lib/mysql/tracerlock directory, there was a .MYD, .MYI and .frm file for every table in the database. So after creating a database called tracerlock on the new server, I copied these files over to the /var/lib/mysql/tracerlock directory on the new server. On both servers, all the table files are owned by user mysql in the mysql group. Now, when I connect to MySQL on the new machine and load the tracerlock database, show tables shows all the tables as expected, and I can perform SELECT statements on them and get the correct results. But when I try doing INSERT statements on one of the tables that was copied over, I get: mysql INSERT INTO test VALUES(3); ERROR 1036: Table 'test' is read only However, if I create a new table called test2, I can perform INSERT statements on it with no error: mysql insert into test2 values(3); Query OK, 1 row affected (0.00 sec) The files corresponding to test and test2 have the same permissions and ownership, so that's not it: -rw-rw1 mysqlmysql 20 Aug 15 21:22 test.MYD -rw-rw1 mysqlmysql1024 Aug 15 21:22 test.MYI -rw-rw1 mysqlmysql8556 Aug 15 21:22 test.frm -rw-rw1 mysqlmysql 5 Aug 16 19:13 test2.MYD -rw-rw1 mysqlmysql1024 Aug 16 19:13 test2.MYI -rw-rw1 mysqlmysql8556 Aug 16 19:13 test2.frm So how do I get rid of the error that Table 'test' is read only? -Bennett [EMAIL PROTECTED] http://www.peacefire.org (425) 497 9002 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] [EMAIL PROTECTED] http://www.peacefire.org (425) 497 9002 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
table is read only after copying table files to new server
I'm trying to port my MySQL tables for a database called tracerlock from one server to another. On the old server, in the /var/lib/mysql/tracerlock directory, there was a .MYD, .MYI and .frm file for every table in the database. So after creating a database called tracerlock on the new server, I copied these files over to the /var/lib/mysql/tracerlock directory on the new server. On both servers, all the table files are owned by user mysql in the mysql group. Now, when I connect to MySQL on the new machine and load the tracerlock database, show tables shows all the tables as expected, and I can perform SELECT statements on them and get the correct results. But when I try doing INSERT statements on one of the tables that was copied over, I get: mysql INSERT INTO test VALUES(3); ERROR 1036: Table 'test' is read only However, if I create a new table called test2, I can perform INSERT statements on it with no error: mysql insert into test2 values(3); Query OK, 1 row affected (0.00 sec) The files corresponding to test and test2 have the same permissions and ownership, so that's not it: -rw-rw1 mysqlmysql 20 Aug 15 21:22 test.MYD -rw-rw1 mysqlmysql1024 Aug 15 21:22 test.MYI -rw-rw1 mysqlmysql8556 Aug 15 21:22 test.frm -rw-rw1 mysqlmysql 5 Aug 16 19:13 test2.MYD -rw-rw1 mysqlmysql1024 Aug 16 19:13 test2.MYI -rw-rw1 mysqlmysql8556 Aug 16 19:13 test2.frm So how do I get rid of the error that Table 'test' is read only? -Bennett [EMAIL PROTECTED] http://www.peacefire.org (425) 497 9002 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: table is read only after copying table files to new server
At 05:54 PM 8/16/2003 -0400, Rajesh Kumar wrote: Bennett Haselton wrote: I'm trying to port my MySQL tables for a database called tracerlock from one server to another. On the old server, in the /var/lib/mysql/tracerlock directory, there was a .MYD, .MYI and .frm file for every table in the database. So after creating a database called tracerlock on the new server, I copied these files over to the /var/lib/mysql/tracerlock directory on the new server. On both servers, all the table files are owned by user mysql in the mysql group. Now, when I connect to MySQL on the new machine and load the tracerlock database, show tables shows all the tables as expected, and I can perform SELECT statements on them and get the correct results. But when I try doing INSERT statements on one of the tables that was copied over, I get: Its probably because you did a hard-copy of your tables. Is it advisable to do such a thing? Does MySql keep track of all the tables and databases in another file? Since MySQL listed tables for all the files that I copied, and since I was able to do SELECTs on them, I figured that the tables must have been added to the database in some semi-valid state. If yes, then this additional file was not modified when you copied your tables. So MySql thinks that those tables don't exist..but in reality it does exist physically. Why the hard-copy? Couldn't you have just done a small dump of the table and sourced it back into the other database? Wouldn't have taken much time either. Unfortunately, due to the size of the tables, the dump wouldn't have been that small :) And I'm moving the tables between different machines. At this point, it would be much easier to change the one little thing (it's always one little thing) that's probably making the tables read-only. Also try logging in as root, and try inserting records, and see if that works. Thanks; I tried it, but it didn't work. -Bennett [EMAIL PROTECTED] http://www.peacefire.org (425) 497 9002 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: table is read only after copying table files to new server
I searched on Google and I found this post from 2 years ago: http://www.phpbuilder.com/mail/php-db/2001082/0212.php suggesting that the way to do it would be to connect to MySQL as root and run the command: grant all on tracerlock.test to bhaselto identified by password I tried that, and then did flush privileges, then logged out and logged back in to mysql as bhaselto, but I still got the error table 'test' is read only when trying to insert rows into it. Any idea on how to do something differently with GRANT to make it work? -Bennett At 04:16 PM 8/16/2003 -0700, Bennett Haselton wrote: I'm trying to port my MySQL tables for a database called tracerlock from one server to another. On the old server, in the /var/lib/mysql/tracerlock directory, there was a .MYD, .MYI and .frm file for every table in the database. So after creating a database called tracerlock on the new server, I copied these files over to the /var/lib/mysql/tracerlock directory on the new server. On both servers, all the table files are owned by user mysql in the mysql group. Now, when I connect to MySQL on the new machine and load the tracerlock database, show tables shows all the tables as expected, and I can perform SELECT statements on them and get the correct results. But when I try doing INSERT statements on one of the tables that was copied over, I get: mysql INSERT INTO test VALUES(3); ERROR 1036: Table 'test' is read only However, if I create a new table called test2, I can perform INSERT statements on it with no error: mysql insert into test2 values(3); Query OK, 1 row affected (0.00 sec) The files corresponding to test and test2 have the same permissions and ownership, so that's not it: -rw-rw1 mysqlmysql 20 Aug 15 21:22 test.MYD -rw-rw1 mysqlmysql1024 Aug 15 21:22 test.MYI -rw-rw1 mysqlmysql8556 Aug 15 21:22 test.frm -rw-rw1 mysqlmysql 5 Aug 16 19:13 test2.MYD -rw-rw1 mysqlmysql1024 Aug 16 19:13 test2.MYI -rw-rw1 mysqlmysql8556 Aug 16 19:13 test2.frm So how do I get rid of the error that Table 'test' is read only? -Bennett [EMAIL PROTECTED] http://www.peacefire.org (425) 497 9002 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] [EMAIL PROTECTED] http://www.peacefire.org (425) 497 9002 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: created new user but can't log in as that user
At 10:20 PM 8/12/2003 +0300, Victoria Reznichenko wrote: As far as I can tell from reading http://www.mysql.com/doc/en/GRANT.html I followed the GRANT syntax correctly for creating a new user; why can't I connect to MySQL as that user? Remove from table user entry for ''@'localhost' and then execute FLUSH PRIVILEGES. That worked. Thanks so much! -Bennett [EMAIL PROTECTED] http://www.peacefire.org (425) 497 9002 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
created new user but can't log in as that user
While logged in to my Linux server as root, I went in to MySQL and (with no databases selected, so that the GRANT statement would apply globally), ran the command: mysql grant all privileges on * to bhaselto identified by 'password'; where password is, of course, the password I wanted to use for the user 'bhaselto'. I can see an entry for that user in the 'user' table in the 'mysql' database: ++--+--+-+-+-+-+-+---+-+---+--+---++-+++ | Host | User | Password | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | ++--+--+-+-+-+-+-+---+-+---+--+---++-+++ [...] | % | bhaselto | alphanumerics | Y | Y | Y | Y | Y | Y | N | N | N| N | N | Y | Y | Y | where alphanumerics is an alphanumeric code that presumably represents the hash of the password that I entered. However, if I exit mysql and try logging in with the bhaselto username, it doesn't let me: [EMAIL PROTECTED] bhaselto]$ mysql -u bhaselto -p Enter password: [Here I type the password that I created above] ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) [EMAIL PROTECTED] bhaselto]$ As far as I can tell from reading http://www.mysql.com/doc/en/GRANT.html I followed the GRANT syntax correctly for creating a new user; why can't I connect to MySQL as that user? -Bennett [EMAIL PROTECTED] http://www.peacefire.org (425) 497 9002 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: speeding up lookups on a table
By the way, I tried adding high_priority to the SELECT statement, which according to http://www.mysql.com/doc/en/Internal_locking.html gives it a higher priority, but that didn't fix the problem. This is a desperate, devastating problem that could simply destroy our business if we don't get it fixed soon -- any help is appreciated. -Bennett At 10:27 PM 7/26/2003 -0700, Bennett Haselton wrote: Jackson, Thanks for your help. Two follow-ups: a) Is there a command to show all the table locks, or locks on a particular table? I searched http://www.mysql.com/doc/en/index.html for locking and locks, but I couldn't find anything. b) I'm reading http://www.mysql.com/doc/en/Internal_locking.html and it sounds like if the table doesn't have any write locks, you can have as many concurrent reads on it as you want, right? I know that there were definitely not any write operations on that table going on during some of the times when the SELECT query would randomly run for 10-20 seconds instead of 1 second, so I'm not sure if that's causing it. There could be other read operations going on at that time, though. -Bennett At 11:24 PM 7/26/2003 -0500, Jackson Miller wrote: This could be caused by table locking. If another Mysql process ha a lock on the table then other processes start to queue up. Maybe this query is getting stuck behind other queries or a single slow query. Just a thought. -Jackson On Saturday 26 July 2003 11:00, Bennett Haselton wrote: I have a MySQL query running inside a CGI script on my site that, at random intervals, seems to take 10-20 seconds to complete instead of less than 1 second. I spent so much time trying to track this down that I wrote a script which runs once a minute on the site, which (a) captures the output of ps auwx (listing all processes) so I can see if that has anything to do with the slowdown; (b) times how long it takes to run the query, and; (c) times how long it takes to run a similar query on a much smaller table. (Part (c) is so that I can separate out whether it's the size of the table in part (b) that's making the difference, or the time taken to do something that's common to both queries, like getting a database handle.) 90% of the time, the large-table query takes less than 1 second, but 10% of the time, it takes 15-20 seconds. (The small-table query always takes less than 1 second.) I looked at the output of ps auwx to see if there seemed to be a relationship between the %CPU time used by other processes, or the number of other running processes, and the speed of the query, but there didn't seem to be. So, my questions are: (a) What is the usual cause of this type of problem? (b) The query I'm running is: SELECT * FROM news_feed_item WHERE news_feed_owner_userid = $my_id; on a table whose description is: ++--+--+-+-+--- -+ | Field | Type | Null | Key | Default | Extra | ++--+--+-+-+--- -+ | ID | int(10) unsigned | | PRI | NULL| auto_increment | | news_feed_owner_userid | int(10) unsigned | YES | MUL | NULL|| | URL| varchar(255) | YES | | NULL|| | title | varchar(255) | YES | | NULL|| | description| text | YES | | NULL|| | date_and_time | datetime | YES | | NULL|| | news_site_name | varchar(255) | YES | | NULL|| ++--+--+-+-+--- -+ (if it wraps, widen message window to see it all). Since I've already defined an index on news_feed_owner_userid, is there anything else I can do to make this kind of query run faster on this table? -Bennett [EMAIL PROTECTED] http://www.peacefire.org (425) 497 9002 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] [EMAIL PROTECTED] http://www.peacefire.org (425) 497 9002 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] [EMAIL PROTECTED] http://www.peacefire.org (425) 497 9002 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
do long delete/update and view progress?
Is there any way to do a long update/delete on a table such that you can view the progress as the command runs, so that long before it's over, you have some idea of what the total running time will be? The way I did it was to write a perl script that takes the total range of values for the primary key field in the table, divides that range into chunks, and then runs the update/delete command on each chunk at a time, printing out when it's done that chunk. But that's a bit of a kludge. Is there a built-in way? -Bennett [EMAIL PROTECTED] http://www.peacefire.org (425) 497 9002 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
speeding up lookups on a table
I have a MySQL query running inside a CGI script on my site that, at random intervals, seems to take 10-20 seconds to complete instead of less than 1 second. I spent so much time trying to track this down that I wrote a script which runs once a minute on the site, which (a) captures the output of ps auwx (listing all processes) so I can see if that has anything to do with the slowdown; (b) times how long it takes to run the query, and; (c) times how long it takes to run a similar query on a much smaller table. (Part (c) is so that I can separate out whether it's the size of the table in part (b) that's making the difference, or the time taken to do something that's common to both queries, like getting a database handle.) 90% of the time, the large-table query takes less than 1 second, but 10% of the time, it takes 15-20 seconds. (The small-table query always takes less than 1 second.) I looked at the output of ps auwx to see if there seemed to be a relationship between the %CPU time used by other processes, or the number of other running processes, and the speed of the query, but there didn't seem to be. So, my questions are: (a) What is the usual cause of this type of problem? (b) The query I'm running is: SELECT * FROM news_feed_item WHERE news_feed_owner_userid = $my_id; on a table whose description is: ++--+--+-+-++ | Field | Type | Null | Key | Default | Extra | ++--+--+-+-++ | ID | int(10) unsigned | | PRI | NULL| auto_increment | | news_feed_owner_userid | int(10) unsigned | YES | MUL | NULL|| | URL| varchar(255) | YES | | NULL|| | title | varchar(255) | YES | | NULL|| | description| text | YES | | NULL|| | date_and_time | datetime | YES | | NULL|| | news_site_name | varchar(255) | YES | | NULL|| ++--+--+-+-++ (if it wraps, widen message window to see it all). Since I've already defined an index on news_feed_owner_userid, is there anything else I can do to make this kind of query run faster on this table? -Bennett [EMAIL PROTECTED] http://www.peacefire.org (425) 497 9002 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: speeding up lookups on a table
Jackson, Thanks for your help. Two follow-ups: a) Is there a command to show all the table locks, or locks on a particular table? I searched http://www.mysql.com/doc/en/index.html for locking and locks, but I couldn't find anything. b) I'm reading http://www.mysql.com/doc/en/Internal_locking.html and it sounds like if the table doesn't have any write locks, you can have as many concurrent reads on it as you want, right? I know that there were definitely not any write operations on that table going on during some of the times when the SELECT query would randomly run for 10-20 seconds instead of 1 second, so I'm not sure if that's causing it. There could be other read operations going on at that time, though. -Bennett At 11:24 PM 7/26/2003 -0500, Jackson Miller wrote: This could be caused by table locking. If another Mysql process ha a lock on the table then other processes start to queue up. Maybe this query is getting stuck behind other queries or a single slow query. Just a thought. -Jackson On Saturday 26 July 2003 11:00, Bennett Haselton wrote: I have a MySQL query running inside a CGI script on my site that, at random intervals, seems to take 10-20 seconds to complete instead of less than 1 second. I spent so much time trying to track this down that I wrote a script which runs once a minute on the site, which (a) captures the output of ps auwx (listing all processes) so I can see if that has anything to do with the slowdown; (b) times how long it takes to run the query, and; (c) times how long it takes to run a similar query on a much smaller table. (Part (c) is so that I can separate out whether it's the size of the table in part (b) that's making the difference, or the time taken to do something that's common to both queries, like getting a database handle.) 90% of the time, the large-table query takes less than 1 second, but 10% of the time, it takes 15-20 seconds. (The small-table query always takes less than 1 second.) I looked at the output of ps auwx to see if there seemed to be a relationship between the %CPU time used by other processes, or the number of other running processes, and the speed of the query, but there didn't seem to be. So, my questions are: (a) What is the usual cause of this type of problem? (b) The query I'm running is: SELECT * FROM news_feed_item WHERE news_feed_owner_userid = $my_id; on a table whose description is: ++--+--+-+-+--- -+ | Field | Type | Null | Key | Default | Extra | ++--+--+-+-+--- -+ | ID | int(10) unsigned | | PRI | NULL| auto_increment | | news_feed_owner_userid | int(10) unsigned | YES | MUL | NULL|| | URL| varchar(255) | YES | | NULL|| | title | varchar(255) | YES | | NULL|| | description| text | YES | | NULL|| | date_and_time | datetime | YES | | NULL|| | news_site_name | varchar(255) | YES | | NULL|| ++--+--+-+-+--- -+ (if it wraps, widen message window to see it all). Since I've already defined an index on news_feed_owner_userid, is there anything else I can do to make this kind of query run faster on this table? -Bennett [EMAIL PROTECTED] http://www.peacefire.org (425) 497 9002 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] [EMAIL PROTECTED] http://www.peacefire.org (425) 497 9002 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
how do I fix corrupt .MYD file?
I have a database in which one MYD file has apparently been corrupted: mysql use tracerlock; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Didn't find any fields in table 'news_feed_item' Database changed mysql select count(*) from news_feed_item; ERROR 1016: Can't open file: 'news_feed_item.MYD'. (errno: 145) mysql The other tables in the database still work. How do I fix this one? I have no idea how it happened. If I open the file in a text editor, I can see the data from some of the records that have been entered. I've rebooted the machine to no avail (I figured that wasn't the problem, but I tried). I have a backup of the table dated last Saturday, but I'd prefer to fix this version if possible to preserve the entries entered between Saturday and Wednesday. -Bennett [EMAIL PROTECTED] http://www.peacefire.org (425) 649 9024 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
what causes queries on one particular table to hang?
I have a database called TracerLock. Right now it is in some kind of state where queries involving the user table will hang: SELECT count(*) FROM user; but queries involving any other table run fine: mysql select count(*) from news_article; +--+ | count(*) | +--+ | 1335037 | +--+ 1 row in set (0.00 sec) The /var/lib/mysql/tracerlock/ directory is where the user.MYD, user.MYI and user.frm files are stored, but I don't see a lock file or anything in there. I just rebooted the server and now I can run queries on the user table just fine. What general kind of event would cause all future queries on one particular table to hang, without blocking queries on other types of tables? (I'm guessing that next time I'll be able to unlock the table just by stopping and restarting mysql instead of doing a full reboot.) -Bennett [EMAIL PROTECTED] http://www.peacefire.org (425) 649 9024 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
isn't this join equivalent to this LEFT JOIN?
Isn't the following: SELECT * FROM t1, t2 where tl.ID=t2.foreignKey; logically equivalent to: SELECT * FROM t1 LEFT JOIN t2 ON t1.ID=t2.foreignKey WHERE t2.ID IS NOT NULL; Because I had a query in the first format that took about two minutes to run on MySQL, and then I changed it into the second format and it ran in about two seconds. If LEFT JOINs are so much faster, why doesn't MySQL take queries in the first format and convert them into the second format? The first format is easier to read, and more logical, so it would be the preferred format for actually writing queries. -Bennett [EMAIL PROTECTED] http://www.peacefire.org (425) 649 9024 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
example using temporary tables
http://www.mysql.com/doc/A/N/ANSI_diff_Sub-selects.html says that for some sub-queries you can create temporary tables, but I couldn't find any examples in the tutorial that show the use of a temporary table. Are there any? Or, if you're feeling generous, could you just tell me how to do this :) -- clickable_ad_link is a table with time_sent field of type datetime, and a userid field that is a foreign key referencing the ID field of the table user. I want to select all users for which there is no clickable_ad_link whose time_sent is less than a week ago. If sub-selects were allowed, the query would be: select * from user where ID not in (select user.ID from user, clickable_ad_link where user.ID = clickable_ad_link.userid AND clickable_ad_link.time_sent DATE_SUB(NOW(), interval 1 week)); Using temporary tables, what I'd like to do is create a temporary table MYTEMP of all the clickable_ad_link rows with time_sent more recent than one week, and then do select * from user left join MYTEMP on user.ID = MYTEMP.userid where MYTEMP.userid IS NULL; but I can't find syntax for creating temporary tables anywhere. -Bennett [EMAIL PROTECTED] http://www.peacefire.org (425) 649 9024 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
IN keyword supported?
I'm trying to use the IN operator as described in my databases textbook from college. This query: select count(*) from user where user.ID in (select ID from user); is supposed to evaluate to count the number of rows in the 'user' table where the ID field is in the set of all ID field values in the 'user' table (in other words, just a roundabout way of counting the rows in the 'user' table, written solely to demonstrate the IN keyword). There doesn't seem to be a syntax error in that example, but MySQL gives the error: ERROR 1064: You have an error in your SQL syntax near 'select ID from user)' at line 1 Is the IN keyword not supported in MySQL, or am I using it wrong? -Bennett [EMAIL PROTECTED] http://www.peacefire.org (425) 649 9024 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: [MySQL] can you have a no-default column; error if unspecified?
At 09:36 PM 12/1/2001 -0700, Ashley M. Kirchner wrote: Bennett Haselton wrote: Is it possible to set a column to have no possible default value, so if you try to create a record that doesn't specify a value for that column, you'll get an error? Set it to NOT NULL NOT NULL doesn't mean that the column doesn't have a default, it just means that the default is not null. Then string columns get the default , numeric columns get the default 0, date columns get the default -00-00, etc. I want a column where you get an error if you try to create a record without specifying a value for that column. Can it be done? For example, if I have a table where the date field should always be set. I took out the default NULL value, but that just gave it a default value of -00-00. What I want to ensure is that it's not possible to create a record for which the date value hasn't been specified. Who's going to stop a user from entering -00-00? You'll have to create validation schemes in your code. I'm mainly talking about validation in places where I might try, *in* the code, to create a new record and forget to specify the date value. The database that I'm writing won't have end users as such, it's just used from many different places in code, and when you're writing something that's used by many different code snippets, it can be advantageous to write it in such a way to reduce the likelihood of errors in any of those snippets. Hence, the idea of a column marked such that MySQL gives an error if you don't set it. -Bennett [EMAIL PROTECTED] http://www.peacefire.org (425) 649 9024 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
can you have a no-default column; error if unspecified?
Is it possible to set a column to have no possible default value, so if you try to create a record that doesn't specify a value for that column, you'll get an error? For example, if I have a table where the date field should always be set. I took out the default NULL value, but that just gave it a default value of -00-00. What I want to ensure is that it's not possible to create a record for which the date value hasn't been specified. (Yes, there's a certain amount of responsibility that's up to the user of the table to remember to set these things, but you should always write lower-level constructs to ensure that, to the maximum extent possible, errors will be harder to commit when making usage of those constructs at a higher level. That goes for tables at the lower level and insertions into those tables at the higher level.) -Bennett [EMAIL PROTECTED] http://www.peacefire.org (425) 649 9024 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: does each database connect cause mysqld to fork?
Do you mean processes instead of threads? (ps lists each *process* separately, along with its process ID, but doesn't list separate threads within each process. If mysqld handles each new connection by calling fork(), then I think fork() creates a new process, not a new thread.) -Bennett At 04:39 PM 11/22/2001 +1300, Quentin Bennett wrote: Hi, From previous message (I don't run linux), I believe that linux ps lists all threads, and mysqld does create a thread for each connection. Regards Quentin -Original Message- From: Bennett Haselton [mailto:[EMAIL PROTECTED]] Sent: Thursday, 22 November 2001 4:15 p.m. To: [EMAIL PROTECTED] Subject: does each database connect cause mysqld to fork? Each time I open a database handle (in perl code), does that cause a new process to run, a new instance of mysqld? And each time I disconnect the database handle ($dbh-disconnect() in perl code), does that cause the number of running instances of mysqld to go down by one? The process list on my Linux server includes several instances of mysqld: server1:/home/bhaselto/web/html$ ps auwx | grep mysqld root 1014 0.0 0.0 16720 ?SW Nov05 0:00 [safe_mysqld] mysql 1037 0.0 3.6 15172 9484 ?SNov05 22:10 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql -- mysql 1039 0.0 3.6 15172 9484 ?RNov05 19:02 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql -- mysql 1040 0.0 3.6 15172 9484 ?SNov05 0:04 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql -- mysql 4518 0.0 0.0 00 ?Z21:07 0:00 [mysqld defunct] mysql 4519 0.0 3.6 15172 9484 ?R21:07 0:00 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql -- so I was wondering if that was the explanation -- I know I have multiple running scripts that use the database at the same time, although I thought they generally didn't leave the handles open that long. -Bennett [EMAIL PROTECTED] http://www.peacefire.org (425) 649 9024 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php The information contained in this email is privileged and confidential and intended for the addressee only. If you are not the intended recipient, you are asked to respect that confidentiality and not disclose, copy or make use of its contents. If received in error you are asked to destroy this email and contact the sender immediately. Your assistance is appreciated. [EMAIL PROTECTED] http://www.peacefire.org (425) 649 9024 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
how to get row size for a given table?
Row sizes in MySQL tables can't exceed 65535 bytes (BLOB and, presumably, TEXT fields are not counted towards this total). That means you can't defined a table where the *possible* size of a row might be larger than that -- e.g. a column of type VARCHAR(255) gets counted as taking up 256 bytes towards that total, even though most of the time it will be much smaller. I had tried creating a table with a lot of fields, then ran into problems because of the size limit, so I changed some of the field types from text to integer (which they should have been to begin with, but I was just using a script to create the table quickly so I hadn't bothered with that tweak). Now I'm under the 65535-character limit, but I don't know how close I am. Is there a way to determine the size (or rather, the maximum possible size) of a row in a given table? -Bennett [EMAIL PROTECTED] http://www.peacefire.org (425) 649 9024 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: how do you find out which table fields are foreign keys?
Just wondering if anyone who knew the answer to this might have missed it
before -- sorry to keep nagging but I really need to find out how, or
whether, you can determine which fields in a table are foreign keys
referencing another table.
Is it not possible to determine, after a table is created, what fields in
the table are foreign keys? For a MyISAM table (where referential
integrity is not enforced), is this information even retained?
-Bennett
At 04:53 AM 11/13/2001 -0800, you wrote:
I created the persons and shirts tables as described in the MySQL
tutorial:
http://www.mysql.com/doc/e/x/example-Foreign_keys.html
such that the owner field in shirts is a foreign key referencing the
persons table. However, describe shirts does not indicate that the
field is a foreign key: (may have to widen mail viewer window to view the
following table)
mysql describe shirts;
+---+-+--+-+-++
| Field | Type| Null | Key |
Default | Extra |
+---+-+--+-+-++
| id| smallint(5) unsigned| | PRI |
NULL| auto_increment |
| style | enum('t-shirt','polo','dress') | | |
t-shirt ||
| color | enum('red','blue','orange','white','black') | | |
red ||
| owner | smallint(5) unsigned| | |
0 ||
+---+-+--+-+-++
4 rows in set (0.00 sec)
How do I find out which fields are foreign keys?
For that matter, in a MyISAM table, what difference does it make whether
you specify that a given field is a foreign key referencing another table
-- as opposed to just an integer field with the same data type as the key
field of another table? Since referential integrity is not enforced with
MyISAM tables, does it not make any difference whether I tell it that a
field is a foreign key? Is that information discarded entirely? (Which
would explain why describe doesn't show it.)
The only reason I wanted to know whether a given field was a foreign key,
was because I'm hammering together my own Web-based interface to MySQL
tables, and I was hoping that when the table contents are rendered in an
HTML table and a given field is a foreign key, I can have its value
hyperlinked to the appropriate row in the table that it references.
-Bennett
[EMAIL PROTECTED] http://www.peacefire.org
(425) 649 9024
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail
[EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
[EMAIL PROTECTED] http://www.peacefire.org
(425) 649 9024
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
how to handle circular references if DB enforces ref. integrity
In a database that enforced referential integrity, if you say that a field in one table is a foreign key referencing another table, then any value in the foreign key field in the first table must reference an existing row in the second table. (Right?) But suppose you have a database storing, I dunno, buses, their occupants, and the driver of each bus, so you have a table BUS, and one of the fields, driver, is a foreign key field specifying the SSN of the person who drives the bus. Then you have another table, person, with a foreign key field BUSNUMBER that references the VIN number of the bus that the person is on. Assume that every bus has people on it and every person is on a bus, so neither foreign key field can be NULL. (Stupid example in terms of relation to the real world, but I can't think of anything better.) In general, you have a one-to-many relationship from one group to another, and then a one-to-one relationship from the second group back to the first one, and the constraints of the real world that you're modeling suggest that neither one of these can be NULL. The problem is, how would you add a new bus and a new driver to the database? Whichever one is added first, you're going to get an error because its counterpart doesn't exist yet, violating referential integrity. You could always relax the NOT NULL constraint, but that might not be desirable if you want to be consistent with the real-world situation that your database describes. Is there a way to update two tables at the exact same time so that referential integrity never gets violated? -Bennett [EMAIL PROTECTED] http://www.peacefire.org (425) 649 9024 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
DBMS that integrates well with object-oriented programming
After converting the data in my project from text files to MySQL tables,
things are certainly working more smoothly than they were before, but since
I use objects (in Perl, which barely supports objects, but that's another
story) I have to write a lot of annoyingly repetitive code to create the
object, run a query on the database to get the data that I need, set the
object's member variables equal to the results returned from the query, and
then do all those steps in reverse if the object's member values have
changed later.
Are there any database systems that integrate so smoothly with an
object-oriented programming language that I would be able to write code
like:
my $user = user.CreateFromTableRow(bennett);
$user.sendReminderEmails();
$user.emailaddress = '[EMAIL PROTECTED]';
and the appropriate values would get read and written to the underlying
database table. There's no reason why a language/IDE designed to integrate
databases and objects would require more than three lines of code for that.
An ideal database and language integration system could have the following
features:
(a) there is an automatic one-to-one correspondence between fields in a
database table and member variables of the object
(b) any field that is a unique key
(c) modifications of the object member variable values get written through
directly to the database
(d) queries can be done in-place, in code, with a function that takes a
reference to another function and returns all elements in the table for
which that function returns true, i.e. this example in pseudocode would
return an array of all objects corresponding to rows in the table
automobile where the price is less than 1:
my @user_array = GetMatches('automobile_table',
function myfunc($automobile)
{
if ($automobile.price 1)
{
return true;
}
}
);
*Sigh* I can dream, I guess. What's actually available? :) (I am *not*
averse to using commercial software products which save more money in the
long run than they cost; please don't *not* tell me about something just
because it's closed source, proprietary, comes from the Evil Empire in
Redmond, etc. :))
-Bennett
[EMAIL PROTECTED] http://www.peacefire.org
(425) 649 9024
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: DBMS that integrates well with object-oriented programming
At 10:37 AM 11/15/2001 +, [EMAIL PROTECTED] wrote: Dear Bennett, On Thu, 15 Nov 2001, Bennett Haselton wrote: After converting the data in my project from text files to MySQL tables, things are certainly working more smoothly than they were before, but since I use objects (in Perl, which barely supports objects, but that's another story) I have to write a lot of annoyingly repetitive code to create the object, run a query on the database to get the data that I need, set the object's member variables equal to the results returned from the query, and then do all those steps in reverse if the object's member values have changed later. Hmm... I'm sure plenty of people would disagree strongly with your remark of bad OO support in Perl. But, that is beside the point. You could either search CPAN (http://www.cpan.org/) and see if you find what you need there (Surely somebody had the same problem already), and if not, take a look at the AUTOLOAD magic sub in Perl (that'd be perldoc perlsub, search for 'Autoloading'). I can't do it in Perl. I can't even quantify the amount of money we've already lost by using Perl. By not supporting OO, I mean things like the fact that if you define a member function of a class, then $classname::functionname($arg1, $arg2); and $classname-functionname($arg1, $arg2); will both compile, but one is a static function that passes the class name as the first argument, and the other is a non-static function that passes the arguments as listed. No one on any Perl list that I'm on could make sense of why they did that. If you wanted a static function, you'd make it static; otherwise, you'd make it non-static. The way Perl does it, you have to put some logic at the beginning of your function to check whether the first argument is the class name, and if it is, branch into the static function code, or else branch to the non-static function code. Bizarre. I think that to the maximum extent possible, a programming language should ensure that a typo will cause the program *not to run*, instead of running and doing something unexpected. Has anyone used a beta of Visual Studio .NET enough to know if it can do all of this? i.e. being able to do something along the lines of user myuser = user.GetObjectFromTableRow(bennett); myuser.sendReminders(); myuser.emailaddress = '[EMAIL PROTECTED]'; in three lines -- with Perl and MySQL it takes about 20. I think there are some purposes that free, open-source software is better suited for (security protocols and other well-defined problems that don't change with a constantly changing technological environment), however the question of when and when not to use open source software is clouded by the notion that open-source software is always better. If I'm going to be using an IDE to do rapid application development, I want a **closed-source**, **proprietary** system that **costs money** :) -Bennett [EMAIL PROTECTED] http://www.peacefire.org (425) 649 9024 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
nonstandard meaning of KEY in MySQL?
My databases textbook from college says that specifying an attribute as an index means that the data will be stored in such a way that lookups on that attribute are faster, and specifying an attribute as a key means that its values have to be unique. But MySQL seems to use KEY and INDEX to mean the same thing (e.g. http://www.mysql.com/doc/C/R/CREATE_TABLE.html -- KEY is a synonym for INDEX -- and http://www.mysql.com/doc/S/H/SHOW_DATABASE_INFO.html , which lists some more commands where the two are used interchangeably). In both cases, they mean what my textbook refers to as an index, and MySQL uses unique key to mean what my textbook calls a key. What are the *standard* meanings of these terms? The usage in the textbook makes more sense (and seems to be the terminology used by MS Access as well); how did MySQL end up using them differently? -Bennett [EMAIL PROTECTED] http://www.peacefire.org (425) 649 9024 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
can you call describe on results of a table join?
Is there a way to call describe on the temporary table that's generated when you do a join or other type of query -- e.g. I can get a temporary table by doing the following (useless) query: mysql select * from user,pet where user.id=pet.id; ++--+--+-+--++---++ | ID | username | name | owner | sex | birth | death | ID | ++--+--+-+--++---++ | 13 | 000diva | Snort| Bennett | m| 1986-12-08 | NULL | 13 | | 14 | 01932220 | Fluttersnort | Bennett | f| 1987-05-23 | NULL | 14 | | 15 | 080822 | Oliver2 | Tina| m| 1984-01-01 | NULL | 15 | | 17 | 083048 | kermie | Bennett | m| 1984-01-01 | NULL | 17 | | 18 | 0918 | snort| bennett | m| 1986-12-08 | NULL | 18 | ++--+--+-+--+--+--+ but is there any way I can call describe on that table, to get the data type of each column, and other information? I'm interested in this because in the Web-based MySQL-table manipulation tool that I wrote, the tables are displayed in a nice, readable format, with labels at the top of each column giving their data type etc. I want a way to submit JOINs and other queries, and have the results be formatted just as nicely. If there's a way to call DESCRIBE on those results, I can re-use most of my existing code. -Bennett [EMAIL PROTECTED] http://www.peacefire.org (425) 649 9024 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
how do you find out which table fields are foreign keys?
I created the persons and shirts tables as described in the MySQL
tutorial:
http://www.mysql.com/doc/e/x/example-Foreign_keys.html
such that the owner field in shirts is a foreign key referencing the
persons table. However, describe shirts does not indicate that the
field is a foreign key: (may have to widen mail viewer window to view the
following table)
mysql describe shirts;
+---+-+--+-+-++
| Field | Type| Null | Key |
Default | Extra |
+---+-+--+-+-++
| id| smallint(5) unsigned| | PRI |
NULL| auto_increment |
| style | enum('t-shirt','polo','dress') | | |
t-shirt ||
| color | enum('red','blue','orange','white','black') | | |
red ||
| owner | smallint(5) unsigned| | |
0 ||
+---+-+--+-+-++
4 rows in set (0.00 sec)
How do I find out which fields are foreign keys?
For that matter, in a MyISAM table, what difference does it make whether
you specify that a given field is a foreign key referencing another table
-- as opposed to just an integer field with the same data type as the key
field of another table? Since referential integrity is not enforced with
MyISAM tables, does it not make any difference whether I tell it that a
field is a foreign key? Is that information discarded entirely? (Which
would explain why describe doesn't show it.)
The only reason I wanted to know whether a given field was a foreign key,
was because I'm hammering together my own Web-based interface to MySQL
tables, and I was hoping that when the table contents are rendered in an
HTML table and a given field is a foreign key, I can have its value
hyperlinked to the appropriate row in the table that it references.
-Bennett
[EMAIL PROTECTED] http://www.peacefire.org
(425) 649 9024
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
why ever use TINYBLOB/TEXT -- isn't VARCHAR same size?
http://www.mysql.com/doc/n/o/node_357.html explains the different string data types and the storage requirements: Column Type Storage required [...] VARCHAR(M) L+1 bytes, where L = M and 1 = M = 255 [...] TINYBLOB, TINYTEXT L+1 bytes, where L 2^8 [...] These two storage requirements are exactly the same, so isn't a TINYBLOB almost exactly like a VARCHAR(255) BINARY, and a TINYTEXT almost exactly like a VARCHAR(255)? There are some differences listed at: http://www.mysql.com/doc/B/L/BLOB.html Apparently, in MySQL 3.23.2, the only difference between a TINYTEXT and a VARCHAR(255) is that TEXT/BLOB fields can't have default values and trailing spaces are not removed as they are in VARCHAR values. But are those really the only differences? -Bennett [EMAIL PROTECTED] http://www.peacefire.org (425) 649 9024 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
how to make LOCATE other functions case-insensitive?
MySQL documentation says that CHAR and VARCHAR types are case-insensitive:
http://www.mysql.com/doc/C/H/CHAR.html
But I have a table with a column of type VARCHAR(255), and if I do a query
like
select * from user where LOCATE('Bennett', emailaddress) 0;
then the results are computed in a case-SENSITIVE fasion. The above query
gives no results, but if I do
select * from user where LOCATE('bennett', emailaddress) 0;
then I get a match for each user where the email address contains the
case-sensitive string 'bennett':
+--++
| ID| username | emailaddress |
+---+--+--+-+
| 48459 | benn1000 | [EMAIL PROTECTED] |
| 3827 | benne100 | [EMAIL PROTECTED] |
| 3828 | benne101 | [EMAIL PROTECTED] |
+--++
The output of describe:
mysql describe user;
+--+--+--+-+-++
| Field| Type | Null | Key | Default | Extra |
+--+--+--+-+-++
| ID | int(10) unsigned | | PRI | NULL| auto_increment |
| username | varchar(8) | YES | MUL | NULL||
| emailaddress | varchar(255) | YES | | NULL||
+--+--+--+-+-++
Aren't LOCATE and similar functions supposed to be case-insensitive to
begin with? And how do I make them behave that way?
-Bennett
[EMAIL PROTECTED] http://www.peacefire.org
(425) 649 9024
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
how to do increments in parallel
Say I have two running programs and both of them periodically want to increment a value in a database. How can I do this so that the increments will be performed correctly even if the two programs try to do them at the same time? If I have code like this: $x = read_value_from_database(); ++$x; write_value_to_database($x); then the problem is that both programs might try and do their reads at the same time, then increment their own copies of the number, and then write back the same, incremented number. If the database system queues requests properly, then it won't give any error messages, but the final value of the number in the database will be 1 greater than what it was before, instead of what it should be, which is 2 greater. Is there a single command to increment a numeric value in a database? That way, the database program could queue those requests, run them in order, and always end up with the stored number having the right value. -Bennett [EMAIL PROTECTED] http://www.peacefire.org (425) 649 9024 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: changing column order in MySQL tables?
At 04:26 PM 11/2/2001 -0600, Paul DuBois wrote: At 2:12 PM -0800 11/2/01, Bennett Haselton wrote: How do you change the order of columns in MySQL tables? I assume the tables have a concept of column order, since the DESCRIBE command always lists the columsn in the order in which they were created. I figured that the place to look would be the syntax page for the ALTER TABLE statement: http://www.mysql.com/doc/A/L/ALTER_TABLE.html but there's nothing there about how to change the order of columns. You can specify the position with a FIRST or AFTER col_name clause when you add a column, but not (as far as I know) change the position of an existing column. Do you have some weird application requirement that makes this necessary? You can always name the columns in the order you want them to come out, when you issue a SELECT query. I know it's bad practice for an application to depend on the order of columns within a table; I'm avoiding that. The only reason I cared about order was because, in the Web-based interface that I'm writing for my own use, one of the functions is to dump the contents of a MySQL table into a viewable HTML table. When viewing that output, it's helpful if the columns are organized into some meaningful order. -Bennett [EMAIL PROTECTED] http://www.peacefire.org (425) 649 9024 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
query results cached, or other explanation?
I've observed that if I run a simple SELECT * command on a two-row table, the query takes anywhere from half a second to four seconds: mysql select * from pet; +--+-+-+--++---++ | name | owner | species | sex | birth | death | ID | +--+-+-+--++---++ | snort| bennett | mammal | m| 1986-12-08 | NULL | 1 | | fluttersnort | bennett | mammal | m| NULL | NULL | 2 | +--+-+-+--++---++ 2 rows in set (3.95 sec) but if I enter the same command several times in succession, the time taken is always 0.00 sec. In fact, it looks like if I re-enter the query less than 15 seconds after the last time it was entered, it takes 0.00 sec to rerun it. (After the 15-second cutoff, the query times start going back up again.) What causes this? Does MySQL do any caching of query results? The first alternative explanation that came to mind was that if you re-enter a recent query, then the disk files storing the data will still be cached in memory. This is supported by the observation that if you have recently run the query select * from pet; then the query select * from pet where name=snort; is also very fast -- i.e. the data itself is being cached (either by MySQL or by the file system), not the query results. However, if that's the cache, shouldn't the data still be cached, say, 60 seconds later? I don't think the machine is busy enough that data would be pushed out of virtual memory less than 60 seconds after the last time it was placed there, although I don't know anything about virtual memory on Linux. -Bennett [EMAIL PROTECTED] http://www.peacefire.org (425) 649 9024 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
changing column order in MySQL tables?
How do you change the order of columns in MySQL tables? I assume the tables have a concept of column order, since the DESCRIBE command always lists the columsn in the order in which they were created. I figured that the place to look would be the syntax page for the ALTER TABLE statement: http://www.mysql.com/doc/A/L/ALTER_TABLE.html but there's nothing there about how to change the order of columns. -Bennett [EMAIL PROTECTED] http://www.peacefire.org (425) 649 9024 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
no warning if setting date to value with all letters
I'm doing experiments with the table pet where the field birth is of type date. How come UPDATE pet SET birth = a2b WHERE name = snort; gives: Query OK, 0 rows affected (0.00 sec) Rows matched: 1 Changed: 0 Warnings: 1 but UPDATE pet SET birth = ab WHERE name = snort; gives: Query OK, 0 rows affected (0.00 sec) Rows matched: 1 Changed: 0 Warnings: 0 After a few more tests I determined that I'm getting a warning if I try to set the field to a value consisting of a mixture of letters and numbers, but I get no warning if the new value consists only of letters. Shouldn't I get a warning if I try to set a date field to a value consisting of all letters? -Bennett [EMAIL PROTECTED] http://www.peacefire.org (425) 649 9024 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: ROW_FORMAT=compressed has no effect on db file sizes
At 12:18 PM 10/30/2001 -0600, Dan Nelson wrote: In the last episode (Oct 30), Bennett Haselton said: I created one table with the command: CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20), species VARCHAR(20), sex CHAR(1), birth DATE, death DATE, id INT UNSIGNED NOT NULL); and another one with the command: CREATE TABLE pet2 (name VARCHAR(20), owner VARCHAR(20), species VARCHAR(20), sex CHAR(1), birth DATE, death DATE, id INT UNSIGNED NOT NULL) ROW_FORMAT=compressed; From the manual: :`ROW_FORMAT' Defines how the rows should be stored. Currently : this option only works with MyISAM tables, which : supports the `DYNAMIC' and `FIXED' row formats. : *Note MyISAM table formats::. :When you `CREATE' or `ALTER' a table you can for tables that :doesn't have `BLOB''s force the table format to `DYNAMIC' or :`FIXED' with the `ROW_FORMAT=#' table option. In the future you ^ I guess this is the operative phrase here; it's planned for the future but it doesn't work yet (and presumably the reason MySQL doesn't give you errors when you enter these commands is for compatibility reasons). :will be able to compress/decompress tables by specifying :`ROW_FORMAT=compressed | default' to `ALTER TABLE'. *Note CREATE :TABLE::. -- Dan Nelson [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: bad practice to have a primary key field whose value changes?
At 02:32 PM 10/30/2001 -0700, Steve Meyers wrote: What would be ideal would be to use auto-incremented numeric fields as primary key fields, and then have a special field in each table designated as the user-friendly field. That way, when you want to view the contents of a table, the table viewing algorithm can take each field marked as a foreign key, go to that table, look up the user-friendly string for that row, and display that instead. This would satisfy the requirements in both paragraphs above. Is there already a way to do this, and if not, which of the two options above do people usually use? -Bennett Generally people do not put user-friendly fields in tables. That's what joins are for :) I guess what I wanted was a list of bookmarkable links that I could click on, which would show me a list of all the users in a table, all the news sites, etc. What I probably need is to put the non-user-friendly fields in the tables, and then come up with a way to store join queries, so that I can bookmark the results. I could just create a form that submits a query through GET data, and then bookmark the results of the form submission. Thanks Steve and Gregert :) -Bennett [EMAIL PROTECTED] http://www.peacefire.org (425) 649 9024 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
web-based interface for manipulating MySQL tables?
Is there any pre-written code that allows users, authenticated over the Web, to view MySQL tables in their database as HTML tables? Desirable features for something like that would include: - tables are displayed in row-and-column format, with a few blank rows at the bottom where you can fill in new values and click to add them to the database (assuming that the user viewing the table has the appropriate rights) - values that represent foreign keys are hyperlinked, so that when you click on them, you're taken to the appropriate row in the other table etc. -Bennett [EMAIL PROTECTED] http://www.peacefire.org (425) 649 9024 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
ROW_FORMAT=compressed has no effect on db file sizes
I created one table with the command:
CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20), species VARCHAR(20), sex
CHAR(1), birth DATE, death DATE, id INT UNSIGNED NOT NULL);
and another one with the command:
CREATE TABLE pet2 (name VARCHAR(20), owner VARCHAR(20), species VARCHAR(20), sex
CHAR(1), birth DATE, death DATE, id INT UNSIGNED NOT NULL) ROW_FORMAT=compressed;
and then populated both of them with 10,000 rows of data, from a file pet.txt with
10,000 lines of text, created with the perl script:
open(PET, pet.txt);
for (my $i = 1; $i = 1; ++$i)
{
if ($i%100 == 0) { print $i, \n; }
print PET snortbennett mammal m 1986-12-08 \N $i\n;
}
close(PET);
But the files used to store data for both tables take up the same amount of disk space:
[root@server1 menagerie]# ls -l
total 912
-rw-rw1 mysqlmysql 36 Oct 30 09:14 pet.MYD
-rw-rw1 mysqlmysql 82944 Oct 30 09:14 pet.MYI
-rw-rw1 mysqlmysql8704 Oct 30 09:10 pet.frm
-rw-rw1 mysqlmysql 36 Oct 30 09:20 pet2.MYD
-rw-rw1 mysqlmysql 82944 Oct 30 09:20 pet2.MYI
-rw-rw1 mysqlmysql8704 Oct 30 09:20 pet2.frm
Did I do something wrong?
-Bennett
[EMAIL PROTECTED] http://www.peacefire.org
(425) 649 9024
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: syntax error in SQL query that works when hard-coded in?
Thanks for pushing me in the right direction :) It turns out that it
wasn't the semicolon on the end of $sql_query -- it was the newline. I had
been invoking it as follows:
./rundatabasequery.cgi
sql_query=SELECT+*+FROM+newssites_dynamicdata%3B%0D%0A
and the %0D%0A was a hex-encoded newline that got appended to the end of
$sql_query. Removing that newline made the query work.
-Bennett
At 03:25 PM 8/13/2001 +0200, Ian Barwick wrote:
On Sunday 12 August 2001 01:01, Bennett Haselton wrote:
I've written a CGI script that runs a database query on a MySQL
database (I
know that's risky -- it's password-protected though, for what it's
worth). (...)
It's a free world ;-)
When I run the script with the first of these two lines commented out,
and
the second one uncommented:
#my $sql_query = $q-param('sql_query');
my $sql_query = SELECT * FROM newssites_dynamicdata;;
^
(rest of mail snipped)
Try leaving out the trailing semicolon from all queries.
Semicolons are only really useful when using the mysql client, where they
signify the end of a statement (alternatively you can use \g). In DBI
statements they will, more likely than not, cause errors. And as they
aren't
in any way necessary, save yourself the trouble they might cause...
HTH
Ian Barwick
--
Ian Barwick - Developer - [EMAIL PROTECTED]
akademie.de asp GmbH - http://www.akademie.de
To query tables in a MySQL database is more fun than eating spam
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail
[EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
[EMAIL PROTECTED] http://www.peacefire.org
(425) 649 9024
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
can't find my my.cnf file -- not there by default?
I'm still testing out MySQL and I'm trying to find the my.cnf file referred to at http://www.mysql.com/doc/O/p/Option_files.html The page says to look in: /etc/my.cnf DATADIR/my.cnf defaults-extra-file (The file specified with --defaults-extra-file=# ) ~/.my.cnf but none of these files seem to exist on my machine. (I assume my DATADIR is /var/lib/mysql/, since it contains subdirectories and subfiles like ./var/lib/mysql/menagerie ./var/lib/mysql/menagerie/pet.frm ./var/lib/mysql/menagerie/pet.MYI ./var/lib/mysql/menagerie/pet.MYD which I assume represent the data stored in the menagerie database that I created while going through the MySQL tutorial.) I searched the machine for files with mysql in the path and .cnf in the name (I had to look up how to do that, to give you an idea of my level of experience with UNIX) but found only: ./usr/doc/MySQL-3.23.36/my-huge.cnf ./usr/doc/MySQL-3.23.36/my-large.cnf ./usr/doc/MySQL-3.23.36/my-medium.cnf ./usr/doc/MySQL-3.23.36/my-small.cnf ./usr/share/mysql/my-huge.cnf ./usr/share/mysql/my-large.cnf ./usr/share/mysql/my-medium.cnf ./usr/share/mysql/my-small.cnf which are sample files. MySQL came pre-installed on that machine. Is it possible (or, indeed, normal) that the MySQL installation process didn't create a my.cnf file? (In that case I'd recommending adding that info to http://www.mysql.com/doc/O/p/Option_files.html , if the author of those pages is on the list. I'll submit a comment there anyway based on what the list replies.) -Bennett [EMAIL PROTECTED] http://www.peacefire.org (425) 649 9024 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: local MySQL server authenticating users by machine username
At 06:04 PM 8/8/2001 -0500, Paul DuBois wrote: At 2:50 PM -0700 8/8/01, Bennett Haselton wrote: The first page of the MySQL tutorial at: http://www.mysql.com/doc/C/o/Connecting-disconnecting.html says that when you connect to the MySQL server, it authenticates you based on a username and password that you enter, unless the server is also configured to allow anonymous access, in which case you can log in with no username. Isn't it also true that if you connect to the MySQL server on your local machine, the server authenticates you based on the username that you're already logged in under? Not quite. The server has no idea who you're logged in as. The mysql client program uses your login name as the default MySQL user name if you don't specify any user name explicitly. Oh OK. The behavior makes sense now. However, the page at http://www.mysql.com/doc/C/o/Connecting-disconnecting.html says: Some MySQL installations allow users to connect as the anonymous (unnamed) user to the server running on the local host. If this is the case on your machine, you should be able to connect to that server by invoking mysql without any options: shell mysql which sounds like it's incorrect -- if you invoke mysql without specifying a username, it actually uses your login name as the username. I added a comment on that page. -Bennett [EMAIL PROTECTED] http://www.peacefire.org (425) 649 9024 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: how to erase all data in database (table)
DELETE FROM tablename; where tablename is the name of the table. -Bennett At 05:23 PM 8/9/2001 -0400, Dmitry Kashlev wrote: How can I erase all data except for the fields in mysql table? I just want to fill all data again in the same table. Dmitry - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php [EMAIL PROTECTED] http://www.peacefire.org (425) 649 9024 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
connecting by knowing someone's scrambled password?
http://www.mysql.com/doc/U/s/User_names.html says: MySQL encrypts passwords using a different algorithm than the one used during the Unix login process. See the descriptions of the PASSWORD() and ENCRYPT() functions in section 6.4.12 Miscellaneous Functions. Note that even if the password is stored 'scrambled', and knowing your 'scrambled' password is enough to be able to connect to the MySQL server! How is that possible? Even if you do know someone's scrambled password, when you connect to the MySQL server pretending to be that user, it will ask you for their non-scrambled password. After you type it in, the server will scramble it and check that the scrambled value matches the scrambled value stored in the database -- but you can't intercept that part of the process and insert the known scrambled password to be checked. -Bennett [EMAIL PROTECTED] http://www.peacefire.org (425) 649 9024 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
root/bhaselto same pwd, but mysql thinks one is blank
I gave the same password to user root and user bhaselto, using the
statements:
UPDATE user SET Password=PASSWORD('') WHERE user='root';
UPDATE user SET Password=PASSWORD('') WHERE user='bhaselto';
(using the real password instead of '', of course). The 'user'
table shows them as having the same scrambled password:
mysql select Host, User, Password from user;
+---+--+--+
| Host | User | Password |
+---+--+--+
| localhost | root | |
| localhost.localdomain | root | |
| localhost | | |
| localhost.localdomain | | |
| localhost | bhaselto | |
+---+--+--+
5 rows in set (0.00 sec)
(where '' represents the scrambled password, and is *the
same* in all three places -- I've obfuscated it here even though I still
can't see how you could break into someone's account knowing only the
scrambled password, since it's non-reversible).
However, only user root appears to have a non-blank password. If I try
mysql -u root from the command line, I can't log in, and instead I have
to enter mysql -u root -p and then type the '' password when
prompted. This is expected. But when I try to log in as bhaselto, I can
type
mysql -u bhaselto
and log right in with no password. If I type mysql -u bhaselto -p and
then get prompted for a password, I have to hit Enter (submitting a blank
password) to log in. I tried stopping and starting the mysql service but
it didn't help. Why does mysql not ask for a password for user bhaselto?
Related question: if I try accessing the database using the DBI perl
module, then:
my $dbh = DBI-connect(DBI:mysql:database=menagerie;host=localhost,
'root', # username
'', # password
{'RaiseError' = 1 }
);
connects *succesfully*. However, this code:
my $dbh = DBI-connect(DBI:mysql:database=menagerie;host=localhost,
'bhaselto', # username
'', # password
{'RaiseError' = 1 }
);
fails with the error:
DBI-connect(database=menagerie;host=localhost) failed: Access denied for
user: 'bhaselto@localhost' (Using password: YES) at dbdtest.pl line 5
And this code:
my $dbh = DBI-connect(DBI:mysql:database=menagerie;host=localhost,
'bhaselto', # username
'', # password
{'RaiseError' = 1 }
);
(i.e. using a blank password for user bhaselto) fails with the error:
DBI-connect(database=menagerie;host=localhost) failed: Access denied for
user: '@localhost' to database 'menagerie' at dbdtest.pl line 5
So, if the password for user bhaselto really is blank, why didn't the
second example work? And, for that matter, in the second example, it was
the *password* that was blank, not the *username* -- so why did the error
message refer to user: '@localhost'? It seems like it should have
referred to user: 'bhaselto@localhost' since I did give the username as
bhaselto.
Thanks very much to anyone who can help me sort this out! :)
-Bennett
[EMAIL PROTECTED] http://www.peacefire.org
(425) 649 9024
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
programmatically getting field list from a table
Is there any way -- using either MySQL directly, or (ideally) using the DBI perl module -- to get a list of fields from a (possibly empty) table? (If the table is nonempty, you can just use a perl function to get the first record, and then apply keys() to the returned reference to get the field names.) I'm sure it's a trivial question, but I can't find the answer anywhere in the tutorial at http://www.mysql.com/doc/ -Bennett [EMAIL PROTECTED] http://www.peacefire.org (425) 649 9024 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
local MySQL server authenticating users by machine username
The first page of the MySQL tutorial at: http://www.mysql.com/doc/C/o/Connecting-disconnecting.html says that when you connect to the MySQL server, it authenticates you based on a username and password that you enter, unless the server is also configured to allow anonymous access, in which case you can log in with no username. Isn't it also true that if you connect to the MySQL server on your local machine, the server authenticates you based on the username that you're already logged in under? I'm just trying out the MySQL server on my machine, and when I'm logged to the machine as bhaselto and I invoke mysql (with no username or password on the command line), I get the mysql prompt, however I am not able to create new databases. However, if I quit mysql, then su to become root, and then invoke mysql again with no username/password arguments, when I get the mysql prompt I am able to create new databases. (If this is right, then should I send an update suggestion to the author of the http://www.mysql.com/doc/C/o/Connecting-disconnecting.html page? Is he or she on the list?) -Bennett [EMAIL PROTECTED] http://www.peacefire.org (425) 649 9024 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
