SQL question on BETWEEN in MySQL
I am reposting this because it got sent right at the start of all the fun with the mailing list. Best, Kyle - I have a series of data in one table that I need to put into a set of ranges. Here is a simplified version of the tables: data table fields (data): val int(10) not null, row_id int(10) not null auto_increment, primary key Range table fields (range): lo int(10) not null, hi int(10) not null, range_id int(10) not null, primary key (lo,hi), index rng_indx(range_id) What I need to do is to count the number of entries in the data table that fall in the ranges in the range table. Ideally, I would have something like this: SELECT r.range_id as 'Range', count(*) as 'Hits' FROM data d, range r WHERE d.val BETWEEN r.lo AND r.hi GROUP BY r.range_id When I set up a test for this, explain shows that I will be doing a table scan. This is not what I want. I have 5M rows in data and 10k+ in range. The ranges do not overlap, so I will not get multiple rows per value in the data table. Some values may lie outside all ranges. Am I misusing between here? If so, is there a construct in MySQL that I can use to speed this up? I know that comparison queries other than equal and not equal tend to cause table scans, but between seems to work with very different queries. Should I restructure the query somehow? Any help from the guru's is appreciated. Best, Kyle -- Quicknet's MicroTelco fax and voice service has just added another carrier giving MicroTelco users more low rates to choose from. This new carrier is Altair Telecom - a low cost, worldwide voice telephony carrier that is available by up-grading at no additional cost to Internet SwitchBoard v6.0 or MicroTelco Gateway v2.5. http://www.quicknet.net/download/ Cut costs, Fax smart. Use iPrint2Fax worldwide and save! == FREE software download available at www.iPrint2Fax.com == - 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: InnoDB File Size
On Friday 08 February 2002 16:32, Gurupartap Davis wrote: Yes, I saw that before...My filesize was limited to 2GB before, but now with a 2.4 kernel and reiserfs I _should_ be allowed to create a 20GB data file now, right? So something's wrong... as I said before, I have successfully created a 9GB file on this machine before, so the filesystem isn't to blame for a 4GB limitation... If you are using one of the default installations of ReiserFS from SuSE, it will be in the old (3.5) format. This will limit the size of files to 2GB even if you are using a new kernel and Glibc. If you remounted with -o conv, you will be able to create _new_ files that exceed 2GB, but I think that any existing file will be limited to 2GB. One way to test this would be to copy (not rename!) the table files to a new name, remove the old ones and rename the new ones back to the old names. Of course, this will work better if you have MySQL down at the time :-) It is also possible that the table size has been set when you created the tables. Look up MAX_ROWS (MAXROWS?) in the online manual. Best, Kyle -- Quicknet's MicroTelco fax and voice service has just added another carrier giving MicroTelco users more low rates to choose from. This new carrier is Altair Telecom - a low cost, worldwide voice telephony carrier that is available by up-grading at no additional cost to Internet SwitchBoard v6.0 or MicroTelco Gateway v2.5. http://www.quicknet.net/download/ Cut costs, Fax smart. Use iPrint2Fax worldwide and save! == FREE software download available at www.iPrint2Fax.com == - 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: deleting var=0.1
On Tuesday 05 February 2002 13:03, Matthias Hoffmann wrote: dear all. I have a problem using mysql(occured at using phpMyAdmin): i send the query: DELETE FROM test WHERE user = test AND timestamp = 2002-02-05 21:42:53 AND var1=1000.1 LIMIT 1 and 0 effect. but when var1 has a (INT) value there's no problem: DELETE FROM test WHERE user = test AND timestamp = 2002-02-05 21:42:53 AND var1=1000 LIMIT 1 might there be a problem using such values? has this error already occurded? I just joined this list There was just a thread on this topic. See the archives for the thread with the title Problem with where clause on the 1st of February. At the bottom of each e-mail on the list is a set of useful URLs to things like the archives. Best, Kyle - 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: Problem with where clause
On Friday 01 February 2002 14:19, Jim Dickenson wrote: Am I to assume that based on your response that one should never use a float field type if you ever want to select the data? Er, no, 12.3399 float_var 12.3400 works fine. It is the = operation that doesn't. Remember that floating point numbers are binary numbers. What looks like a limited number of digits in base ten is not necessarily in base 2. Equality has a very carefully defined meaning in the IEEE floating point specifications. However, that definition is based on the binary representation of a number, not the decimal representation. Use DECIMAL types if you really want to compare numbers like this. This causes a big problem for the way MyODBC 3.51 has been implemented. I was actually debugging a problem I had in MyODBC when I ran across this. The way MyODBC works is that is generates a native SQL statement. In my case the statement was: I can't speak to this as I do not use ODBC. UPDATE `junk` SET `record`= 91 WHERE record=1 AND title='This is item one' AND num1=12.3 AND num2=134 AND num3=0.100 AND code='abc' AND sdate='1991-11-30' AND stime='17:45:00' LIMIT 1 This'll fail almost certainly. You might have it work if the value of num3 is an exact integer, maybe. If the value was generated by any kind of calculations, you could be one bit off (few floating point libraries get all the rounding right all the time). So, 4 could actually be 3.... Since the float compare did not work (field num1), the record I wanted changed did not get changed. The way our software works is that it fixes the variable number (num1) to the number of decimals in the constant number before the compare is done. There are ways for compares to be programmed so they do work. We are using computers after all. Yep, but anything that relies on floating point having values you can print nicely is going to break. If I had scars for all the times I've been burned by not understanding how floating point really works... If you really need to get a floating point equality, you have to do something like this: let e = a really small number, preferably the smallest number you can express with a floating point number before you hit denormals. Note that this value depends on the magnitude of the floating point values you are comparing. The higher the magnitude, the greater e has to be. equal(a,b) = a-e = b = a+e I.e. you cannot test for exact equality, but need to test for whether a is in a range of numbers around b. e (really epsilon to make the math people happy) is the acceptable level of error that you'll allow in order to declare two numbers equal. Floating point numbers work great for a few applications and are very annoying for others. There are some good papers on floating point and how to really use it correctly on the web. Use google or some search engine. I don't have any links in front of me. If you can take the speed hit, you can use FORMAT on both numbers to make them the same length: UPDATE `junk` SET `record`= 91 WHERE record=1 AND title='This is item one' AND format(num1,2)=format(12.3,2) ... the above tells MySQL to compare the two strings that result when you format the numbers to two decimal places. Those strings should match. Best, Kyle -- Quicknet's MicroTelco fax and voice service has just added another carrier giving MicroTelco users more low rates to choose from. This new carrier is Altair Telecom - a low cost, worldwide voice telephony carrier that is available by up-grading at no additional cost to Internet SwitchBoard v6.0 or MicroTelco Gateway v2.5. http://www.quicknet.net/download/ Cut costs, Fax smart. Use iPrint2Fax worldwide and save! == FREE software download available at www.iPrint2Fax.com == - 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: Memory limit issue with mysql.3.23.41
If you only have 2GB of RAM and you are allocating 5120M (=5GB) for the key_buffer, you have a problem. That should make your system swap like mad and everything slow to a crawl. Also, note that some of these config options may effect per-thread allocation. I don't know which ones off the top of my head, but I think the manual makes it clear. Things to remember: MySQL stores index data in the key_buffer. It does not store data there. MySQL does not cache data. If you tell it to allocate more memory than the system has, you'll use up all memory for the index, allocate swap for it (really slow), and leave no space for the OS to cache data. We usually allocate between 30 and 50% of the available DRAM for the index and leave the remaining things much smaller. This lets the OS do some caching on its own. If you take all the memory for the indexes of MySQL, I am amazed that it was able to run with any speed at all. What it looks like is that you allocated your 2G of memory and then wandered quickly into the weeds. Swap != RAM. Best, Kyle On Friday 18 January 2002 07:37, Franklin, Kevin wrote: [snip] The behavior suggests that we are running out of memory / swap, but we have over 2 gig of memory and 10 gig of swap free. If you hit swap, you hit the wall in performance and go splat. We run PCs with this much RAM. RAM=Performance with MySQL. Even for Sun's RAM is pretty cheap. Our server settings are: key_buffer=5120M Danger Will Robinson! This is larger than your RAM! max_allowed_packet=1M table_cache=1024 sort_buffer=6M This is pretty big and allocated on a thread by thread basis I think. record_buffer=4M This might be allocated on a thread by thread basis too, but I can't remember. Hmm, looks like it is. Do you run absolutely huge queries? Do you really need 10MB _per thread_? thread_cache=12 thread_concurrency=12 You have a lot of processors? If so, you don't have much RAM. myisam_sort_buffer_size=512M This is somewhat high given that you've already use all available RAM for indexes more than twice over. The server tends to crash upon reaching a total memory usage of around 4 GB I am surprised that it responds at all after it uses up RAM and starts to swap. You must have a good disk subsystem. Here is the output from the error log. Of particular interest to us is the negative key_buffer_size quoted. The same value (-4096) appears with each crash. Is there some sort of memory limit imposed on the server or do you have suggestions for debugging this problem? I think that you rolled a 32-bit integer somewhere. Try setting key_buffer to 1G. This could be a bug in MySQL. See below. This might mean that it is a 32-bit executable. mysqld got signal 11; That's not good. Is your ulimit set to 2G for MySQL. Is MySQL actually a 64-bit executable? Perhaps it is a 32-bit executable. key_buffer_size=-4096 This doesn't look good. record_buffer=4190208 sort_buffer=6291448 max_used_connections=308 max_connections=1024 threads_connected=309 It is possible that mysqld could use up to key_buffer_size + (record_buffer + sort_buffer)*max_connections = 2093044 K bytes of memory Read this line carefully. I think the server is trying to tell you something. Note that it is really using -4096 as the key_buffer_size in the calculation above. Note that the math seems to be wrong too since you set record_buffer to 4M and sort_buffer to 6M, you should be allocating 10M per thread. That's a lot of RAM for a system with only 2G. Hope that's ok, if not, decrease some variables in the equation I think it has the right idea here. Best, Kyle -- Cut costs, Fax smart. Use iPrint2Fax worldwide and save! - iPrint2Fax software is FREE, no fax modem required. - FREE iPrint2Fax to a single email address. - iPrint2Fax to PSTN based Fax (Up to 95% Savings) - iPrint2Fax Broadcasting: Send 100s of faxes and fax to emails in the time it takes to send just one! == FREE software download available at www.iPrint2Fax.com == - 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: auto_increment
On Wednesday 16 January 2002 07:28, Hamlin Nicholas-qa568 wrote:
Easier yet, when using DBI::mysql, is:
my $IncrementedID = $dbh-{'mysql_insertid'};
When executed after the INSERT command.
Does this now work with 64-bit auto-increment fields now in DBI? As of
less than a year ago, DBI would incorrectly truncate the result to 32-bits
and we had to use the SELECT LAST_INSERT_ID() method instead. A real pain
to track down all the code that used the old method :-(
We prefix some of our IDs with server IDs and when we tried a server with
a number so high that the ID was outside the range of 2^32, we got garbage
:-(
Best,
Kyle
--
Cut costs, Fax smart. Use iPrint2Fax worldwide and save!
- iPrint2Fax software is FREE, no fax modem required.
- FREE iPrint2Fax to a single email address.
- iPrint2Fax to PSTN based Fax (Up to 95% Savings)
- iPrint2Fax Broadcasting: Send 100s of faxes and fax to emails
in the time it takes to send just one!
==
FREE software download available at www.iPrint2Fax.com
==
-
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: Input Needed: Replication issues...
On Saturday 12 January 2002 20:53, Jeremy Zawodny wrote: On Fri, Jan 11, 2002 at 08:08:51AM -0800, Kyle Hayes wrote: We write our own replication system using our own checkpointing and code. We start with the update logs (we're not happy about the future removal of the update logs because of this). I've looked through the MySQL source to see if I can figure out enough of the format of the binary log to see if I can make some Perl code that will parse it. It is really easy to use the update log since all SQL end with a semicolon as the last character on a line. Since Perl tends to be line-oriented, it is easy to find this. [snip] Several questions: (1) Do you use transactions at all, or is this MyISAM only? (2) Have you considered publishing the code? (3) Do I understand correctly, that you have your own primary keys which have nothing to do with MySQL's unique IDs? 1) MyISAM only (we started on some of this when 3.23 was knee-high to a bullfrog :-) 2) Yes, but we probably won't. The code has a large number of assumptions about our set up. I don't know that management would be too thrilled with it. The system I'm working on now (to replace the old one) is much cleaner, but would still need a work to remove any Quicknet IP from it. I don't have time to do the clean up, but in general I'd like to give it back to the community. 3) We have sets of IDs for each row that are unique per server. They are made with something like a sequence table using LAST_INSERT_ID tricks. These are the IDs that tie everything together. The auto increment fields are for bulk record keeping and things like purging old data efficiently. Honestly, it is not that much code. I wrote the whole thing in about a week full time. I'll check with management to see if I can release it. Best, Kyle - 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: Input Needed: Replication issues...
On Thursday 10 January 2002 17:22, Matthew Walker wrote: Where I work, we're at the point of needing to maintain two database servers, one inhouse, and the other at another physical location. We need to replicate between the servers, to keep them both up to date, as both will be getting updated. This leaves us with the problem of resolving primary key conflicts if the servers should ever lose touch with eachother for a while. We're looking at InnoDB, and wondering if it would be able to resolve those sorts of issues using transactions. Input please? We looked closely at native MySQL replication, but after a few questions about the handling of auto increment fields (we use them like row IDs all over the place), we decided that MySQL's replication was broken for our application. We have more than one live database and they replicate to each other. We write our own replication system using our own checkpointing and code. We start with the update logs (we're not happy about the future removal of the update logs because of this). I've looked through the MySQL source to see if I can figure out enough of the format of the binary log to see if I can make some Perl code that will parse it. It is really easy to use the update log since all SQL end with a semicolon as the last character on a line. Since Perl tends to be line-oriented, it is easy to find this. Here's what we do: - we run a special server process on each database. This replicator server will spool update log files on demand. - on each machine, we run a special client process. This process connects to the replicator server (not MySQL's), gets the update log information and puts it into the local database. If necessary, we can rewrite any SQL (we don't). The server process puts a special checkpoint comment between each SQL statement. The checkpoint comments has a special header and trailer so that we can recognize it. It contains the name of the file that is currently being spooled and the byte offset in that file. This information is stored on the client side in a special checkpoint file. If the client process needs to be restarted or the network drops or something causes replication to quit, it will restart where it left off based on the checkpoint file. This functionality is basically identical to the native MySQL replication. The reasons for using the update log and not native MySQL replication are these: - we can have more that two servers coupled together. - we use auto increment fields all over. With native mySQL replication, these fields are filled in with values on the source side. Thus, we get collisions on the target side. We have no need for the row IDs to match on each machine as every row has another unique ID field. We do need the row IDs for purging old data and other things that do not rely on the unique ID field. - we can do SQL rewriting if we really need to. These problems led us to write our own replication. If you have the luxury of redesigning your database or of designing with MySQL's native replication in mind (we did not), then I would use the native version. Best, Kyle - 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: DBI and MySQL update log question
Comments inline below.
On Wednesday 09 January 2002 18:13, Paul DuBois wrote:
At 17:23 -0800 1/9/02, Kyle Hayes wrote:
[snipped replication explanation]
The program works by getting the contents of the update log and using
the Perl DBI do() function on each statement.
The problem that occurs causes this (debug output):
SQL: use acc;
DBD::mysql::db do failed: You have an error in your SQL syntax near
'; ' at line 1 at /opt/bin/replicator_client line 199, GEN2 line 4.
I have found that this works _IFF_ I strip off the trailing linefeed
after the semicolon at the end of the statement. I could swear that
we create SQL statement in Perl here docs that end with a semicolon
and several empty lines. Is there a DBI guru that can tell me what is
going on? I really notice this especially in USE statements...
Don't add the semicolon. That's for when you use the mysql client,
which you aren't doing.
Hmm, most of our SQL is generated with the semicolon. It definitely works
with the semicolon. I print out what I send to DBI-do() and it has a
semicolon.
The error message is complaining about the semicolon, by the way,
not the linefeed.
As far as I can tell, this is not true. Here's the main loop that works
(changed slightly to protect the innocent :-):
$input_line = $input;
if($input_line =~ m/^\;$/) {
# OK, we have the end of a SQL statement.
# remove trailing newline
chomp($input_line);
# add this to the rest of the SQL
$sql .= $input_line;
# run it through the database
if(length($sql)0 ) {
# use eval because we want to catch errors.
eval { $rc = $dbh-do($sql); };
} else {
$rc = 1;
}
# did we have a problem?
if(!defined($rc) or $@) {
# bad news, we have a crash problem.
... print out stuff for the error log file...
exit(1);
}
# SQL successfully handled, reset the SQL accumulation variable.
$sql = '';
} else {
# no match, still part of the SQL statement. Strip comments.
if($input_line !~ m/^\s*\#/) {
$sql .= $input_line;
}
}
Obviously, you need to wrap this in a loop, but this is what is working
now. I have added debugging printouts at various places above and can
state that the SQL that goes through DBI does have a trailing semicolon,
but not a trailing newline. Really weird.
Best,
Kyle
--
Cut costs, Fax smart. Use iPrint2Fax worldwide and save!
- iPrint2Fax software is FREE, no fax modem required.
- FREE iPrint2Fax to a single email address.
- iPrint2Fax to PSTN based Fax (Up to 95% Savings)
- iPrint2Fax Broadcasting: Send 100s of faxes and fax to emails
in the time it takes to send just one!
==
FREE software download available at www.iPrint2Fax.com
==
-
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
DBI and MySQL update log question
I am putting the finishing touches on a completely reworked replication server/client scheme (we cannot use MySQL's native replication for what we are doing). The whole thing is written in Perl/DBI and runs on Linux (x86). We are using various versions of MySQL, but all 3.23.x series. We are slowly upgrading to the latest version 3.23.47. I don't know the version of the MySQL Perl DBI libraries we use, but they are not too old. We have mixed Red Hat and SuSE systems. The program works by getting the contents of the update log and using the Perl DBI do() function on each statement. The problem that occurs causes this (debug output): SQL: use acc; DBD::mysql::db do failed: You have an error in your SQL syntax near '; ' at line 1 at /opt/bin/replicator_client line 199, GEN2 line 4. I have found that this works _IFF_ I strip off the trailing linefeed after the semicolon at the end of the statement. I could swear that we create SQL statement in Perl here docs that end with a semicolon and several empty lines. Is there a DBI guru that can tell me what is going on? I really notice this especially in USE statements... Anyone have an idea? I haven't found anything particularly relevent in the mailing list archives. Best, Kyle -- Cut costs, Fax smart. Use iPrint2Fax worldwide and save! - iPrint2Fax software is FREE, no fax modem required. - FREE iPrint2Fax to a single email address. - iPrint2Fax to PSTN based Fax (Up to 95% Savings) - iPrint2Fax Broadcasting: Send 100s of faxes and fax to emails in the time it takes to send just one! == FREE software download available at www.iPrint2Fax.com == - 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: Trying to Start Two Servers
On Wednesday 31 October 2001 21:42, Gary Huntress wrote: I'm up to over 1 databases in one data directory and that is becoming a performance problem as users make their connections. I would like to begin running multiple servers on the same box.I made my first attempt tonight and was confused. Before I screw up a production box perhaps someone can tell me what I'm doing wrong. Wow. That's a lot! I always start my server with this script: safe_mysqld --log --datadir=/mnt/db -O wait_timeout=1000 -O max_connections=1000 We make totally separate directories: /database/instance1 /database/instance2 Within each tree, we have everything MySQL needs except the binaries. I.e. /database/instance1/etc config including my.cnf /database/instance1/run pid file, Unix socket /database/instance1/log log files /database/instance1/bin tuned scripts to run _this_ instance only /database/instance1/var database table directories for this instance /database/instance1/tmp temp dir for this instance. We leave the mysqld etc. binaries wherever they normally live. We start the whole thing with a slightly hacked version of safe_mysqld safe_mysqld --defaults-file=/database/instance1/etc/my.cnf That my.cnf file defines the port, socket, log files, data directory, temp directory etc. The scripts in .../instance1/bin are very important for what we do. They have hardcoded data in them to make them only work on that instance of the database. Thus, we can start and stop and instance by going into the instance-specific bin directory and running a script: db_ctrl stop We do _not_ have to pass the instance path. The reason for this is that it helps prevent a whole class of errors that the system admins might make accidentally by typing the wrong instance. They need to actually type the full path to the instance bin dir. That has helped reduce errors a lot. I still have an old data directory in /usr/local/mysql/var so I thought I could just change the pid, socket, and data directory via: safe_mysqld --pid-file=/usr/local/mysql/var/kettlebrook.pid --socket=/tmp/ mysql3307.sock --port=3307 --datadir=/usr/local/mysql/var -O wait_timeout=1000 -O max_connections=1000 to run it on port 3307. But, your Unix socket is in /tmp without any differentiation from the other instance. The primary server starts fine. When I run the second one, I have gotten address already in use errors (is there already a server running? or something to that effect) I thought I had successfully created a second *independent* server that nobody else should be using so I did: The address in use might be from the other socket, not the IP address. I would run mysqld directly on the command line and see what errors it tells you. I've had better luck debugging my my.cnf files that way than trying to use safe_mysqld. mysql -pmypassword -P3307 This will connect to the Unix port on localhost. If you want to connect to the IP side, you'll have to specify a host that is not localhost (the default above). connected ok , and did a processlist. It showed numerous web connections, and it apppeared that I was connected to my production server on 3306 rather than 3307. Use SHOW VARIABLES to make sure that the data dir is where you think it is supposed to be. Then, I issued mysqladmin -pmypassword -P3307 shutdown hoping to shut down the second test server. It shut down the main server on 3306, as evidenced by my webserver indicating connection problems. I was able to repeat this several times. Note above. You are not connecting to the port that you think you are. If you do not pass a hostname other than localhost, you'll connect through the Unix socket. My question is basically thisdid I somehow (probably in my first lame attempts) get some of my socket files mixed up and/or pid files and they were simply out of sync with the proper server?2) Should I be able to properly control two servers using the two safe_mysqld command lines above? We do it all the time. Just make sure that your sockets are differentiated and that you are using a FQDN if you are trying to connect via IP instead of a Unix socket. Best, Kyle bot fodder: sql, mysql, query -- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - MicroTelco Services saves money on every Fax: - Fax to email (FREE) - Fax to PSTN based Fax (Up to 95% Savings) - Fax Broadcasting: Send 100s of faxes to fax machines and email addresses in the time it takes to send just one! === So send a fax today and let us know what you think! For more info. visit: www.internetfaxjack.com === - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/
Re: Uneven replication nodes??
On Wednesday 24 October 2001 23:09, Jeremy Zawodny wrote: On Wed, Oct 24, 2001 at 06:25:58AM -0400, Tony wrote: I am looking for a creative way to optimize my web application (largely reads) with a transaction firehose on the data input side. Batch vs. OLTP, if you will. Is there any technical reason why I could not configure the master database without indices and the slave(s) _with_ indices? The intent being to isolate the inbound transactions from the reads _and_ make the database inserts as simple as possible (e.g. few or no indices). That ought to work. Unless you have _any_ UPDATEs. Then, it'll slow to a crawl. MySQL is really fast if you've got the right indexes. It isn't much faster than grep on a big text file if you don't :-) If all you have are INSERTs and you can prove that they will never try to overwrite old data on the slaves, you should be fine. This is a pretty serious constraint though. Several slaves could be configured to throttle performance on the web side (more reads, move slaves, etc.), which could gain a performance edge by indexing. For disaster recovery of the master, one slave could be untouched and not indexed. I'd be interested to hear comments. That's an interesting idea that I hadn't considered before. I'd be worried about maintaining the strict completely new INSERTs only policy over time. If you are running stock or measurement data into it where it is guaranteed that it is always new INSERTs, you should be OK. Best, Kyle bot fodder: database, query, mysql, lions and tigers and bears, oh my! -- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - MicroTelco Services saves money on every Fax: - Fax to email (FREE) - Fax to PSTN based Fax (Up to 95% Savings) - Fax Broadcasting: Send 100s of faxes to fax machines and email addresses in the time it takes to send just one! === So send a fax today and let us know what you think! For more info. visit: www.internetfaxjack.com === - 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: Indexing Problem
On Friday 26 October 2001 07:18, Wai Lee wrote: I dig through the manual and changed any possible settings set-variable= max_heap_table_size=2000M set-variable= key_buffer=2500M set-variable= max_allowed_packet=1M set-variable= table_cache=512 set-variable= sort_buffer=2500M set-variable= join_buffer=2500M set-variable= record_buffer=2500M set-variable= myisam_sort_buffer_size=2500M set-variable= myisam_max_sort_file_size=2500M set-variable= myisam_max_extra_sort_file_size=2500M set-variable= thread_cache=8 I am not certain, but I think you told MySQL to use 2.5G x 5 or 12.5GB! That is a little more than the 4GB you have in the machine :-) In general, the advice I have seen it to make sure that all allocated buffers take no more than about 50% of the memory in the machine. MySQL allocates buffer space for indexes, but _NOT_ data. It lets the OS do that. Linux does caching of disk data very well, but you need to leave it some room to do this. When you leave at least 50% of the space for the OS, it has a chance to cache disk data and accesses will be much faster. If you have a lot of large indexes, I would drop these settings to something like: set-variable= max_heap_table_size=200M set-variable= key_buffer=1000M set-variable= max_allowed_packet=1M set-variable= table_cache=512 set-variable= sort_buffer=250M set-variable= join_buffer=250M set-variable= record_buffer=250M set-variable= myisam_sort_buffer_size=250M set-variable= myisam_max_sort_file_size=2500M ?? Not sure about this one. Check the manual. set-variable= myisam_max_extra_sort_file_size=2500M ?? Not sure about this one. Check the manual. set-variable= thread_cache=8 Best, Kyle -- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - MicroTelco Services saves money on every Fax: - Fax to email (FREE) - Fax to PSTN based Fax (Up to 95% Savings) - Fax Broadcasting: Send 100s of faxes to fax machines and email addresses in the time it takes to send just one! === So send a fax today and let us know what you think! For more info. visit: www.internetfaxjack.com === - 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: Generate random, unique value...
On Friday 26 October 2001 10:00, Dana Holt wrote: Can I automatically generate a random, unique, integer value in a certain range when inserting data into a column using SQL? If so, how? Random is easy. Just find a good RNG (random number generator) somewhere (there are many available on the 'net, search on Google). Or, use the RAND function that comes with MySQL. You can find RNGs that have extremely long periods. Unique is easy. Just use an auto-increment field in MySQL. Random _and_ unique are not that simple. Grab a handy book on cryptography. There are operations on numeric (bitstring) values that will do what you want starting with a guaranteed unique input (such as from an auto-increment field or a sequence). The key thing to remember is that uniqueness and randomness can be derived in two steps, they need not be done in one. Are you sure it needs to be random? By definition, if it is unique, it can't be totally random since a random number would have a certain probability of being the same value twice after a certain number of samples. Is there a simple SQL statement that will do what you want? Probably not. Best, Kyle -- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - MicroTelco Services saves money on every Fax: - Fax to email (FREE) - Fax to PSTN based Fax (Up to 95% Savings) - Fax Broadcasting: Send 100s of faxes to fax machines and email addresses in the time it takes to send just one! === So send a fax today and let us know what you think! For more info. visit: www.internetfaxjack.com === - 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: EMERGENCY - Our production database is crashed
On Tuesday 23 October 2001 13:50, David Potter wrote: Dear list members, We are running Mysql 3.23 on Redhat Linux 7.1. We have an emergency. This is the first time we have ever had a problem. Our production database suddenly crashed. I have tried to repair the tables with myisamchk commands, -r, -o, etc and nothing works. Here is the output: myisamchk: error: 'journal.frm' doesn't have a correct index definition. You need to recreate it before you can do a repair myisamchk: error: 'journal.MYD' doesn't have a correct index definition. You need to recreate it before you can do a repair Our first choice would be to repair the above tables. But if this is too hard, our next best option would be to just create a new database and restore the data from one of our backups. So then I tried to create a new database. Even that does not work. I enter drop database deptpros and the system just hangs. I then tried create database prod and the system just hangs again. Thank you You might want to try running myisamchk with the -k0 arguments. This turns of all the indexes. I don't know if that will cause it to skip the back indexes, but it might. If you can get the .frm and .MYI files for these tables from a backup, that might work too. The .MYI can be out of sync with the data in the table. That is what myisamchk is for. Also, make sure that you are not running myisamchk while the database is running. Later versions might allow that, I can't remember, but earlier ones do not. Once you overwrite the MYI and frm files (_NOT_ the MYD since that has your data!), you can run myisamchk. I would use the -rv flags if the version of MySQL is rather aged. There was a bug a long time ago that was worked around with the -rv flag combination. Just -r would cause data loss :-( Here's the process: 1) assume that a damaged table is A. Get A.MYI from the backup and A.frm from the backup. Call these A_bak.MYI and A_bak.frm. 2) stop the database. 3) make a backup somewhere of the damaged A.MYI. 4) make a backup somewhere of the damaged A.frm. 5) make a backup somewhere of the damaged A.MYD. 6) copy A_bak.MYI over A.MYI. 7) copy A_bak.frm over A.frm. 8) run myisamchk against A. 9) If it worked, do this with the other damaged tables. I'd pick a smallish table to try this on first so that you can see if it is going to work at all. If it doesn't work, you'll probably have to recover from your original backup tapes. Or... If you cannot get the MYI and frm files back from the backup, then follow this process, it _MIGHT_ work (no warranty, void where prohibited by law, may cause cancer in small laboratory animals, not responsible for anything, anywhere): 1) say the table that is damaged is A. Make a new table A_new that has exactly the same fields and indexes in _exactly_ the same order as the original table. 2) stop the database. 3) copy A_new.MYI over A.MYI (after you've made backups of A.MYI of course!). 4) copy A_new.frm over A.frm (again, make those backups first!). 5) run myisamchk -r again. 6) if that does not work, try -o. I would put -v in there too just so that you can get a feeling for what it is doing. make sure that you can save all the output as it will just keep going. If this works, then do it with the other tables that are missing. This is dangerous. I would only use this if you are certain that the backups cannot be used for a complete rebuild or for the trick I suggested above. I assume that you are using MyISAM tables here since you have *.MYD files. MySQL just doesn't corrupt its database much. If you are seeing this kind of corruption, it usually appears to be disk errors, disk controller errors or bad memory (not necessarily in order). Run the memtest86 program that is floating around out there (SuSE has it as a boot option, don't use Red Hat anymore so I can't say for it). Best, Kyle P.S. buy support from mysql.com. You can get a reasonable level of support (email) for less than $200 per _year_. From experience, they are very responsive and helpful. How much money did this database problem lose your company? That $200 sounds cheap doesn't it? -- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - MicroTelco Services saves money on every Fax: - Fax to email (FREE) - Fax to PSTN based Fax (Up to 95% Savings) - Fax Broadcasting: Send 100s of faxes to fax machines and email addresses in the time it takes to send just one! === So send a fax today and let us know what you think! For more info. visit: www.internetfaxjack.com === - 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
Re: EMERGENCY - Our production database is crashed
On Tuesday 23 October 2001 14:15, Garrett Marone wrote: Chances are, you need to type myisamchk -o *.MYI as you only want to run myisamchk on the Index file, which is typically the .MYI file. you probably typed myisamchk -o * which is wrong. Heh. I think you're right. It does look like this might be his most likely problem... Sigh. Best, Kyle bot fodder: database, SQL, MySQL, whoop whoop whoop -- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - MicroTelco Services saves money on every Fax: - Fax to email (FREE) - Fax to PSTN based Fax (Up to 95% Savings) - Fax Broadcasting: Send 100s of faxes to fax machines and email addresses in the time it takes to send just one! === So send a fax today and let us know what you think! For more info. visit: www.internetfaxjack.com === - 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: I am getting crazy!!!
On Monday 22 October 2001 08:41, jim barchuk wrote: Hi Martin! I have been trying to connect to mySQL for a couple of days now with no result! I try to log in via telnet..to my remote server..I get in the system but when I try to log in to the database, nothing!! I use this: mysql -h localhost -u futureenter -p it then asks for a password, I put it in and the return is: Access denied for user: 'futureenter@localhost' (Using password: YES) I also tried it with phpMyAdmin, but still the same thing! I have the right data for it..Do I have to stand in an certain location? If it is a remote server, 'localhost' is still good? Telnet should work fine. Localhost maybe should work, but not necessarily. Depends on how the host has your permissions installed in their server. Try -h domain.com, or just leave it out altogether. In fact try leaving the -u off, maybe your login name is enough. Only your host admin knows for sure. Ask if they have a FAQ page with the promise that if they do you'll never have to ask them another question again. LOL! How was the security set up? You will definitely need a GRANT of the form: GRANT XXX ON YYY.ZZZ TO futureenter@localhost IDENTIFIED BY 'WWW'; XXX = the actual grants such as SELECT, INSERT, DELETE ... YYY.ZZZ = the database and table on which you have these privileges this could be *.* for everything WWW is your password, unencrypted. Note that the hostname must actually match. If the grant was given such that the FQDN of your host was used instead of localhost, you'll need to use that as Jim noted above. The chapters in the MySQL manual are fairly good. Please read them carefully. Best, Kyle -- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - MicroTelco Services saves money on every Fax: - Fax to email (FREE) - Fax to PSTN based Fax (Up to 95% Savings) - Fax Broadcasting: Send 100s of faxes to fax machines and email addresses in the time it takes to send just one! === So send a fax today and let us know what you think! For more info. visit: www.internetfaxjack.com === - 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: Frequently corrupt tables
On Thursday 18 October 2001 09:45, Bill Adams wrote: Matthew Bloch wrote: I'm running several MySQL installation (all version 3.23.37 under Linux) under what I presume are some fairly harsh conditions, and wondered what circumstances cause tables to be corrupted and need fixing with myisamchk. This is happening once every few days and it's becoming a pain. I have a multithreaded process which is constantly opening and closing connections to the database and trying to increase its concurrency until the load average reaches something comfortable like 15, and the network connection is saturated. I've had to throttle it back to stop it opening more than 32 simultaenous DB connections but otherwise it works fine. Until I start getting errors from the table handler, that is, and the whole thing grinds to a halt until I fix the table manually. Can anybody shed some light on this? I can't believe I'm putting it under more load than something like Slashdot would, and they don't (appear to) have half the troubles I've had. I found yesterday (at the advice of this list) that adding an occasional call to FLUSH TABLES fixed my corruption problems. I would do that right before the disconnect or program exit. What kernel are you using? Some of the 2.4 series have... odd... behavior with regards to caching. Are you using SCSI or IDE. We've run many tests with both and not had any corruption problems unless we did something whacked like pull the power for the machine while it was running the test. What filesystem are you running? Just running FLUSH TABLES sounds like it is only going to make the problem less common, not fix it. Something is corrupting your indexes/data. Is the data getting mangled or the index? If myisamchk can fix the problem, it is likely that the index is the problem. MySQL will cache the index in memory, but not the data. Thus, if you see data mangling problems and possibly index problems, I would look at the kernel, disk etc. If you are only see index problems, but the data looks OK, then the version of MySQL might be a problem or maybe you have a bad build. MySQL builds more cleanly than most OSS projects, but it is a big complex beastie and can build incorrectly without obvious errors sometimes in our experience. Bad library versions can also be a factor. We've run tests with 1000 hits per second on a database on a cheasy IDE drive without a problem. We've run those tests for hours at a time with no problems. SCSI definitely works better than IDE, but the newer IDE drives aren't that bad anymore. They still use a lot of CPU. If your drives to write caching, that can be a problem if you have a power drop. Most IDE drives (all?) will cache writes to allow the disk firmware to reorder the writes to get better speed. This means that your carefully flushed data will be written in an order _DIFFERENT_ from what you and the OS thought it was written in. This can completely screw up filesystems and definitely could have some interesting effects on your database. Best, Kyle -- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - MicroTelco Services saves money on every Fax: - Fax to email (FREE) - Fax to PSTN based Fax (Up to 95% Savings) - Fax Broadcasting: Send 100s of faxes to fax machines and email addresses in the time it takes to send just one! === So send a fax today and let us know what you think! For more info. visit: www.internetfaxjack.com === - 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: Frequently corrupt tables
On Thursday 18 October 2001 12:31, Bill Adams wrote: Kyle Hayes wrote: I found yesterday (at the advice of this list) that adding an occasional call to FLUSH TABLES fixed my corruption problems. I would do that right before the disconnect or program exit. What kernel are you using? Some of the 2.4 series have... odd... behavior with regards to caching. Linux host 2.2.19 #6 SMP Wed Jul 11 10:55:03 PDT 2001 i686 unknown 2GB Memory, 4 CPUs. (It happened on other systems with different kernel versions too.) Hmm, 2.2 doesn't do SMP really well. However, its drawbacks are limited to underuse of the CPUs rather than any kind of corruption or other issue. You would get much better performance with 2.4, but 2.2 is probably a little more stable. Are you using SCSI or IDE. We've run many tests with both and not had any corruption problems unless we did something whacked like pull the power for the machine while it was running the test. SCSI. (Had problem with different controllers on different systems) Three dual channel controllers, all the same: [bill@host ~/dev]$ cat /proc/scsi/aic7xxx/0 Adaptec AIC7xxx driver version: 5.1.33/3.2.4 Compile Options: TCQ Enabled By Default : Disabled AIC7XXX_PROC_STATS : Disabled AIC7XXX_RESET_DELAY: 5 Adapter Configuration: SCSI Adapter: Adaptec AIC-7899 Ultra 160/m SCSI host adapter Ultra-160/m LVD/SE Wide Controller Channel A at Is this a DAC960 or something similar? If so, make sure you have the absolute latest drivers. We have some dual processor machines with those controllers (or something closely related) and had to do many driver updates before it stabilized. And, we're still not totally convinced. If this is a big SCSI RAID card, I would definitely check the drivers and make sure that there isn't something newer/more stable out there. Statistics: (scsi0:0:0:0) Device using Wide/Sync transfers at 80.0 MByte/sec, offset 31 Transinfo settings: current(10/31/1/0), goal(10/127/1/0), user(9/127/1/2) Total transfers 36738885 (18761976 reads and 17976909 writes) Waiter! I'll have two of what that gentleman over there is having. What filesystem are you running? ext2. At least that is what linux sees. The disks are actually hardware raid0 winchester flashdisks. Flash? I.e. these are solid state disks? If that is true, then maybe that is part of the problem. Flash is different from normal disk. Can these disks correct for bad sectors? If so, the usual method to force remapping of bad sectors is to use dd: dd if=/dev/zero of=/dev/XXX bs=1M count=YYY Where XXX is the RAID device and YYY is the number of megabytes of storage. Please make a backup of your data first :-) On a normal disk, this causes a write to each sector on the whole drive. That in turn causes the firmware on the drive to remap any bad sectors found this way. If your disks support this, you might be unpleasantly surprized how many problems go away after this. Most newer drives do this automatically, but it can still trash your data. By doing the line above, you force the issue before you have valid data on the disk. Just running FLUSH TABLES sounds like it is only going to make the problem less common, not fix it. Something is corrupting your indexes/data. I loaded three big tables last night with no problems (after adding the occasional $dbh-do( FLUSH TABLES ). Before it would happen at least once when doing a large (re)load of data. We've done 7M rows in one single input file (just a hair under the 2GB limit for the older ext2 filesystem we have on that particular machine). No problems at all. That was with MySQL 3.23.26 or something close to that. We've done tests much larger than this that were either driver via Perl and DBI, or from a flat file. Is the data getting mangled or the index? If myisamchk can fix the problem, That is the funny thing, I had to do a mysqldump file; mysql file to fix the table. myisamchk would report the table was bad, I would try to repair with -o (and just about every other level). then myisamchk would report it was good (even with -e). When I continued to load the data, it would quickly become corrupted again. Even rebuilding all of the indexes would not fix it. Running the mysqldump, mysql fixed it much better. There was a bug in myisamchk for a while that would cause data loss in certain circumstances unless you used the -v flag with -r. This should have been fixed a while ago (over a year?). If MySQL handled the input file resulting from the original dump then it was probably happy with the data itself. You might want to dump out a flat file (tab delimited data) with mysqldump. Then, go into the MySQL command line and run LOAD DATA INFILE ... to load the data in. This will tell you how many warnings you get. It will silently set date fields to -00-00 etc
Re: Auto Increment Fields
On Thursday 04 October 2001 13:37, Paul DuBois wrote:
At 4:08 PM -0400 10/4/01, Jason Frisvold wrote:
Is there a way to have DBI return the value of the auto-incremented field
upon insert? Or do I have to insert and then do a select afterwards?
$dbh-do (your insert statement);
$auto_inc = $dbh-{mysql_insertid};
Be aware that at least older versions of Perl's DBI (maybe just the MySQL
driver?) have a bug wherein they do not return 64-bit values if your
auto-increment field is a BIGINT type. We spent a few days scratching our
heads on that one.
We got around this by explicitly doing a
SELECT LAST_INSERT_ID()
after each operation where we needed to know the ID. Ugly, but it worked
every time. It was ... fun ... tracking down all the places we'd used DBI
directly :-(
Best,
Kyle
Bot fodder: mysql, query, database
--
Quicknet has just released the following new products:
Internet SwitchBoard v5.5 and MicroTelco Gateway 2.0. We
have also added a new low cost carrier, Blue Star Telecom
to our award winning MicroTelco services. For more
information visit: www.quicknet.net or www.microtelco.com
-
Those who desire to give up freedom in order to gain security
will not have, nor do they deserve, either one.
-Thomas Jefferson
-
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: Features in 4.0
I did not see subselects in this list. Will MySQL support subselects in 4.x? What exactly does the LOAD TABLE FROM MASTER 'automagically' comment mean? Does that command sync the slave with the master at that point? If I have three tables, A, B, and C, and I want to load them at runtime from the master and start replication do I have to lock them before doing LOAD TABLE FROM MASTER? We are searching for a solution to enable us to load a large amount of data without stopping the master for more than a couple of seconds. So far, we have been able to write our own complex code to do this, but it is far from the level of automation that we want. Could someone from the MySQL team please give us some details on subselects and the new load table functionality? Best, Kyle On Tuesday 25 September 2001 14:29, Fournier Jocelyn [Presence-PC] wrote: Take a look at this :) http://www.mysql.com/doc/N/e/News-4.0.0.html I'm currently using MySQL 4.0 on two servers, and it works great :) Union seems also to work really fine now although it remains some features to be added before beeing perfect. Jocelyn Fournier Presence-PC -- Quicknet has just released the following new products: Internet SwitchBoard v5.5 and MicroTelco Gateway 2.0. We have also added a new low cost carrier, Blue Star Telecom to our award winning MicroTelco services. For more information visit: www.quicknet.net or www.microtelco.com - 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 Performance Problem
The queries were... select * from cookies left join users on cookies.uid=users.uid left join sessions on users.uid=sessions.uid where cookies.cookie=e3bd03382561eb3619b66fbea2af217d; select * from cookies left join users on cookies.uid=users.uid left join extended on users.uid=extended.uid where cookies.cookie=5226220e3b62cef71a13524ec7a413ac; (above queries have to be performed at the beginning of every webpage to find the current user. i don´t really think they are slow; they just lock because of something else.) i just tried the above query ten times. result: 7x 0.00sec, 1x0.01, 1x0.02, 1x0.04. does that spell slow? Yes. I wouldn't let queries with times like these near a busy production server I was responsible for unless I was positive they wouldn't and couldn't happen more than a handful of times a day. :-) Uh, we've have a lot of luck when we don't use left joins at all. Left joins seem to often make things slower. Occasionally, much slower. Other points to consider are these: 1) if you don't have an index, inserts will fly. We do some logging in MySQL and we don't have many indexes on the tables. Other places, we need to do a lot of queries on the log, so we have indexes. We will often batch copy data from one table to another where the original table has few or no indexes and the target table has several indexes. 2) as many other people have pointed out, use explain. It will show you an estimated number of rows per table that it will work through. I generally multiply those number by each other. You can quickly determine if you are going to have a really long query this way. When these are locked, not only are they waiting for the selects that came before them to finish, they are blocking selects behind them in the queue from executing simultaneously with selects ahead of them. Selects in the queue for the users table that join to other tables (and you have plenty of them) will block updates/inserts to those other tables. It has taken us a while to tune our database to do what the other post above suggests. This is good advice. It has really made a difference for us. We regularly saw an order of magnitude performance increase for several of these steps. Best, Kyle -- Quicknet has just released the following new products: Internet SwitchBoard v5.5 and MicroTelco Gateway 2.0. We have also added a new low cost carrier, Blue Star Telecom to our award winning MicroTelco services. For more information visit: www.quicknet.net or www.microtelco.com - 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 + dns server?
On Thursday 02 August 2001 17:27, mike cullerton wrote: on 8/2/01 3:31 PM, Ricardo Kleemann at [EMAIL PROTECTED] wrote: Hi, Anyone know if there's a module or plug-in or something that allows BIND to work with a mysql database? from http://www.isc.org/products/BIND/bind9.html A new, simplified database interface and a number of sample drivers based on it; see doc/misc/sdb for details Or are there other dns servers out there that can work together with mysql? http://cr.yp.to/djbdns.html I don't have the BIND 9 docs handy, but there is also a newly released DNS server module set in Perl on CPAN. It could be easily extended to talk to MySQL. I went though the online docs for dbjdns and didn't really see anything that looked like it would talk to MySQL. DBJ likes to use his CDB code for a lot of his projects. Is there a FAQ or page I missed? If people know of any other such servers, I'd love to know! Best, Kyle - 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 and credit cards
On Wednesday 25 July 2001 17:53, Elvis wrote: You can always degrade the credit card. 1) verify AUTH ONLY (not capture) with your CC provider. (if you want to verify they have funds available and the CC is valid) 2) store the CC # in the database 3) ..do your order processing thing or whatever you need to have the CC for... 4) capture funds 5) degrade card value in the DB That seems to work well for non recurring credit card transactions (ie single purchases) One problem with this is that the CC number is still in the clear in the log files from step 2 above. If you have a full log for auditing purposes or debugging, an update log for backups, or a binary log for replication, the CC number will be in cleartext in that log. You need to do any encryption in your app to make this safer. That isn't perfect either. If someone gets root on your machine (assuming it is a Linux machine), then they'll be able to read the memory in which you application is running and might be able to see the CC number in that memory before it is encrypted. However, if you have that problem, this may be the least of your worries. Best, Kyle 'bot bait: mysql database query SQL -- Quicknet has just released the following new products: Internet SwitchBoard v5.5 and MicroTelco Gateway 2.0. We have also added a new low cost carrier, Blue Star Telecom to our award winning MicroTelco services. For more information visit: www.quicknet.net or www.microtelco.com - 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: auto-increment sequence question
On Tuesday 26 June 2001 04:49, Gunnar von Boehn wrote: Hello everybody, I thing, that I have a problem that needs a sequence and that auto-increment can't do the job this time. So, I think the only way to solve this is a sequence table. Lets take a single row, integer value for holding the sequence: CREATE TABLE sequence( id int4; ); My question is this: Is this the best way to get and evalute the sequence? LOCK TABLES sequence WRITE; UPDATE sequence SET id=id+1; SELECT id FROM sequence; UNLOCK TABLES; This is more work than you need. You can use this sequence: UPDATE sequence SET id=LAST_INSERT_ID(id+1) SELECT LAST_INSERT_ID() The first statement updates the sequence table atomically. It sets the id value to the id+1 and sets the last insert ID to id+1. This is done atomically. The next statement gets back the ID that was set in the first one. Notice that the SELECT does not have a FROM clause! The database handles the value of LAST_INSERT_ID on a connection by connection basis so, you'll get the right value. This set of SQL statements does not require table locks. We use it all the time. Best, Kyle - 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: $sth-{mysql_insertid} not working in 3.23.38?
As of a couple of months ago, there is a bug with DBI's implementation of
$dbh-{'mysql_insert'}. It didn't handle 64-bit IDs correctly. We converted
everything to use SELECT LAST_INSERT_ID(). It works. MySQL is fine, the
problem was in DBI.
Don't use SELECT MAX(ID) ... as that will not do what you want and you'll
corrupt your own data!
Best,
Kyle
P.S. Spam bot: database, row, query, mysql.
On Saturday 23 June 2001 11:07, xris wrote:
So if the {mysql_insertid} thing is an issue, is there a fix? Does the
latest DBD/DBI package fix it? Or is it strictly a MySQL issue and just
needs to wait for a new release?
As it is, it seems that:
my ($id) = $dbh-selectrow_array(SELECT MAX(id) FROM tbl;);
Don't use this!!!
-
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: running multiple mysqld's
On Friday 08 June 2001 22:16, Rohit Peyyeti wrote: Hello All: I have two mysqld runnning in ports: 3307 3308 respectively. I started the second one by using 'mysqld_multi start 2'. whereas mysqld running on port 3307 is the default mysqld started at the boot time. when i issue command such as: mysql -u root -p (it connects to my new mysqld running on 3307). Why is that it won't connect to mysqld running on port 3308. I also tried giving in port / socket options to mysql but to no luck! I cannot connect to mysqld running on port 3307 (default mysqld which is started at boot time) (anti-s_p_a_m_b_o_t fodder: database, query) MySQL runs with two methods of connection. The first is via Unix sockets (assuming that you are using Linux here). The second is via Internet sockets. The port numbers you have above apply only to Internet sockets. You should see two instances of MySQL running if you connect from a different machine. If you connect on the machine which is running the two instances, you'll need to specify host and port, or you'll need to make sure that the Unix sockets are in different places. For each instance, make sure that the default Unix socket location (usually something like /var/lib/mysql/mysql.sock or /tmp/mysql.sock) is different! MySQL will happily run with two instances using the same Unix socket. Unfortunately, your results may not be what you want. We run multiple instances all the time. The hard part is not configuring MySQL, it is getting the clients to talk to the right one :-) We set up a main directory where all the instances have subdirectories. You could use something like /databases. Put each instance into a subdirectory. We put the logs, Unix socket, pid file, configuration file and databases into directories of the main subdirectory: /database/instance1/etc/my.cnf config file /database/instance1/log/... log files /database/instance1/run/mysql.pid pid file /database/instance1/run/mysql.sock Unix socket /database/instance1/db_files/...database table files /database/instance2/etc/my.cnf config file ... etc... Then, if we are on the same machine and need to connect with the MySQL command-line client, we can just type this: $ mysql --defaults-file=/database/instance1/etc/my.cnf -u root -p... You could easily make wrapper scripts to make this easier: $ mysql_wrapper --instance=1 -uroot -p ... We run multiple instances for several reasons. The primary reason is that we need to take some down on a nightly basis for various purposes and others need to stay up. We cannot do this with a single instance. We can also control resource usage on a per-type-of-use basis. Some instances just perform dumb data logging, others are used for heavy queries. Best, Kyle - 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: Chroot problems
On Wednesday 06 June 2001 08:53, dariofg wrote: Hello, I'm trying to run mysql in a chroot jail, but the server errors out. I've modified safe_mysqld so it would call MySQL with the following command: nice --5 nohup /usr/local/mysql/libexec/mysqld -- basedir=/ --datadir=/var --user=mysql --pid- file=/var/hayek.pid --skip-locking -- chroot=/usr/local/mysql -- language=/usr/local/mysql/share/mysql/portuguese I would suggest setting up the jail somewhere outside the normal tree such as in /jail/mysql or somewhere else. When I run safe_mysqld, the mysql daemon dies with the following error (log file): 010606 9:57:58 /usr/local/mysql/libexec/mysqld: Table 'mysql.host' doesn't exist Test the chroot jail by making sure that the paths (and ownership!) are correct within the jail. Make sure that the mysql database in in /var/mysql. Make sure that MySQL has read permission at least on the directory and on the table. Note that if you put the chroot jail in /usr/local/mysql and have your tables in /usr/local/mysql/var you'll need to make sure that /usr/local/mysql/var has write privilege for the mysql user. How's that possible if the table's set up just fine? I tried copying the mysql datafile dir to the real /var directory and it still can't find the 'host' table. One of the problems I had when running MySQL in a chroot jail was that the /etc/passwd, /etc/shadow etc. had to be correct in the jail. However, I don't think I am running it in the same configuration that you use. See below. Has anyone got mysql running with the chroot option? There really should be a tutorial for this! I do not run MySQL using its chroot option. I actually build the jail with the executable and libraries in it (more portable that way). Then, I start the executable from outside the jail with a modified (still haven't gotten it quite right yet) safe_mysqld that calls: nohup ... chroot /jail/mysql /usr/sbin/mysqld ... I am working on SuSE, so MySQL lives in a different place than you have it installed. It was difficult to figure out the library dependencies, but after a little careful use of ldd, I figured it out. The libraries depend on the distribution you use. You might find this link useful, the author chroots a whole web set up with Apache, mod_perl, etc. It was quite informative. There is a brief section on MySQL, but he doesn't show how to install it in a chroot dir. http://penguin.epfl.ch/chroot.html Best, Kyle - 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: BACKUP TABLE gives access denied
Check the manual to make sure, but I believe that you need to also make sure that the operating system privileges are set correctly too. Not sure what you'll need in Windows, but in Linux, your hosting company probably runs MySQL under the user mysql. Thus, for the database to write out the data for BACKUP, it needs _unix_ write privileges for user mysql (can be group too). If you are trying to backup a table to a directory in your log-in directory, you need to make sure that the MySQL daemon has write privileges to the backup directory. Best, Kyle On Sunday 18 March 2001 05:24, Tom Bellucco wrote: Hi, My web host just upgraded from a version that did not support the BACKUP TABLE command (3.22.??) to the latest. What I try to execute the BACKUP TABLE command it fails with an access denied error. He says I have all the necessary privileges, but I have the feeling something is missing. When I execute SHOW GRANTS it says I have all privileges, but I wonder if there is a new privilege for backuup that doesn't get turned on by default. Any help would be appreciated, please copy responses to my email as I am a new subscriber and am concerned about missing any replies. Thanks! -- Tom Bellucco TJ Computers Consulting [EMAIL PROTECTED] www.tjcc.com - 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 -- * "HEAR THE DIFFERENCE" with a live MicroTelco Gateway demo at: Voice on the Net (VON), March 20-23, Phoenix AZ. For more information visit: http://www.microtelco.com. - 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: setting simple mysqld options in /etc/my.cnf
I've found that past versions of MySQL ship with a version of safe_mysqld that reads the config file itself and only passes on a few of the parameters. We had to rewrite our own version to allow mysqld to actually parse the stuff on its own. Note that the my.cnf file that ships often seems to put the error log entry in the [mysqld] section which causes mysqld to stop with an error because it doesn't understand it if you run mysqld directly. I am assuming you are using Linux/Unix here. If it is Windows, I can't help :-) If you are using Linux/Unix, look through safe_mysqld and make sure that it will correctly pass these parameters through to mysqld. We had to make changes to get the logs to work the way we wanted them and eventually wrote our own version. Our version is not publically available, sorry. Best, Kyle On Wednesday 14 March 2001 04:16, Bill Marrs wrote: Hi, I like to run mysqld with these 3 options: --log-slow-queries --delay-key-write-for-all-tables --skip-networking But, so far, I've been unable to get mysqld to read them out of /etc/my.cnf. Here's what's inside my /etc/my.cnf: [mysql.server] log-slow-queries delay-key-write-for-all-tables skip-networking Am I doing this wrong? I looked, but I didn't find any documentation of how to format simple options like this in /etc/my.cnf. ...or perhaps, I have them under the wrong header (is "mysql.server" correct?) or perhaps I just can't set these options in /etc/my.cnf. Currently, I set them by editing the mysql start script, adding them to the safe_mysqld line. Any help would be appreciated. -bill - 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 -- * "HEAR THE DIFFERENCE" with a live MicroTelco Gateway demo at: Voice on the Net (VON), March 20-23, Phoenix AZ. For more information visit: http://www.microtelco.com. - 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: am I alone? (scale)
On Saturday 10 March 2001 22:16, Justin wrote: Well thats good to know.. although this is sustained 24x7x365 linux 2.2.14-5.0smp, uptime 170 days but e2fsk ok's the the database partition, which is a mirror. in an attempt to get stability, I've been running on the official 3.22.32 mysql binary for a month now .. it hasn't helped. Typically indexes get corrupted and cause selects to malfunction, OR, cause mysqld to crash, OR cause count(*) or distinct(*) queries to run in infinite loops, but not necessarily on the corrupt table. it seems to me index corruption can poison the server beyond the afflicted table. We've had fairly bad luck with Linux 2.2.X and SMP for any kind of high-end system. The DAC 960 SCSI controllers proved to be fairly problematic in this environment. Sometimes updating the drivers helps. We have a couple of systems that run much better when we do not use one of the CPUs. If e2fsck says the partition is fine, then it might not be this problem. Best, Kyle -- Kyle Hayes Quicknet Technologies t: +1 415 864 5225 520 Townsend St. Suite D f: +1 415 864 8388 San Francisco, CA 94103 w: http://www.quicknet.net USA - 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: More on: mysqld got signal 11
Most of the times I have seen signal 11 in the past, it turned out to be bad memory. Once I saw it with a bad motherboard. Another time, it was a bad stick of RAM. The bad stick was a really hard one to track down as you needed to have something really finicky get put at that address before it would show up. The motherboard problem was diagnosed after I turned off the L2 cache (old motherboard) and everything worked, a little slower. Can you run this on a different machine that is otherwise similarly configured? From the stack dump, it kind of looks like it died in llseek. MySQL does a lot of stuff with 64-bit integers and uses some of the "special" transitional APIs on Linux (I assume BSD too, but I don't know for sure) to handle files. llseek sounds like it could be one of those (though I would expect lseek64 instead). If you are using Linux with a 2.2.x kernel and you are running the LFS patches, go to http://www.scyld.com and look at the patches for Glibc they have there. It turns out that older versions of Glibc have what appear to be fully compliant transitional interfaces for the file functions, but actually drop the upper 32 bits on the floor :-( If you are running BSD, you might have the same problems. Again, I am not very knowlegable about BSD. Best, Kyle -- Kyle Hayes Quicknet Technologies t: +1 415 864 5225 520 Townsend St. Suite D f: +1 415 864 8388 San Francisco, CA 94103 w: http://www.quicknet.net USA *** "HEAR THE DIFFERENCE" with a live MICROTELCO demo at: Computer Telephony EXPO, Mar 6-8, Los Angeles, CA MicroTelco is a revolutionary service that brings multiple Internet Telephony Service Providers (ITSPs) together in a convenient, simple to use account center for greater reliability and flexibility. For more information visit: http://www.microtelco.com. - 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: What's the syntax for using ENCODE()?
(second try, I had some magic words in this that set off the spam filter...)
Just as a general security note, make sure that you are not logging these SQL
commands somewhere... If you have entries like:
INSERT INTO my_table (blah, blah, blah) VALUES
(ENCODE('password','someval'...)...
you can just read the update log to find them. If you have full logging
enabled, you will see the SELECT statements that pull back the encrypted
data. Both will give any casual reader the password for the encryption.
This is generally considered to be a bad thing :-)
Either turn off logging entirely (not really a good idea), do the
encryption/decryption outside the database code (a much better idea), or turn
off logging just for these statements with the SET LOG... stuff. The
privileges are a little tricky to set up for the latter. I don't remember
what they are off the top of my head.
I much prefer doing the encryption somewhere else. That way, an accident
with database configuration won't leave all your nice encryption passwords
sitting around in a plaintext file.
Cr#dit c#rds are a very valuable and easy to use form of money. They are
widely prized by thieves for these reasons. They have strong economic
incentives to hack your database or pay off your people or dig in your
garbage for printouts of log files. Cr#dit c#rd numbers do not belong to
you, so there are all kinds of interesting legal issues that come up if you
are responsible for leaking them.
Best,
Kyle
--
Kyle Hayes
Quicknet Technologies t: +1 415 864 5225
520 Townsend St. Suite D f: +1 415 864 8388
San Francisco, CA 94103 w: http://www.quicknet.net
USA
***
"HEAR THE DIFFERENCE" with a live MICROTELCO demo at:
Computer Telephony EXPO, Mar 6-8, Los Angeles, CA
MicroTelco is a revolutionary service that brings multiple Internet
Telephony Service Providers (ITSPs) together in a convenient,
simple to use account center for greater reliability and flexibility.
For more information visit: http://www.microtelco.com.
-
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.server ignoring my.cnf?
make sure that you set the sock parameter in both the [client] and [mysqld] sections of the configuration file. This is one of my favorite errors :-/ Best, Kyle On Monday 26 February 2001 12:04, Joel Gwynn wrote: I tried /etc/my.cnf and got the same results. As far as $DATADIR or $HOME are concerned, I don't see them when I do 'printenv'. Is there another way to find out if those may be set, and by what? thanks. At 02:33 PM 2/26/01 -0500, Kent Hoover wrote: Sorry. Sent the last message before I was done If you have a stray $DATADIR/my.cnf or a $HOME/.my.cnf, their contents will override what you have set in /etc/my.cnf. Cheers, Kent - 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 - Joel GwynnDesigners' CADD Company, Inc. A bus station is where a bus stops. A train station is where a train stops. So now you know why they call this a workstation... - 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 -- Kyle Hayes Quicknet Technologies t: +1 415 864 5225 520 Townsend St. Suite D f: +1 415 864 8388 San Francisco, CA 94103 w: http://www.quicknet.net USA *** "HEAR THE DIFFERENCE" with a live MICROTELCO demo at: Computer Telephony EXPO, Mar 6-8, Los Angeles, CA MicroTelco is a revolutionary service that brings multiple Internet Telephony Service Providers (ITSPs) together in a convenient, simple to use account center for greater reliability and flexibility. For more information visit: http://www.microtelco.com. - 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
bug?? in DBI/libmysql??
Using Linux 2.2.x, Perl 5.6
MySQL 3.23.x (several versions).
We have been using LAST_INSERT_ID to do sequence tables to generate unique
IDs for use in several other tables.
Sequence table:
CREATE TABLE seq_table ( id_val bigint(20) unsigned );
We use SQL like this: "UPDATE seq_table SET id_val=LAST_INSERT_ID(id_val + 1)"
This works fine.
We then use Perl DBI to get the last_update_id:
$dbh-{'mysql_insertid'}
This works great until the value of id_val is over that allowed by a 32-bit
integer. We use bigint fields for IDs.
We recently did a test where the ID field was more than 4 billion. The $dbh
line above returned a negative number where it should have returned a value
around 6 billion.
We look in the sequence table and see '600051'. We got back a fairly
large negative number (-678294 or something, I don't have it handy) from the
Perl code above.
We changed our Perl code to use SQL to retrieve the ID:
$sql = "SELECT LAST_INSERT_ID()"
$sth = $dbh-prepare($sql);
$sth-execute();
my ($id) = $sth-fetchrow_array();
This works and correctly returns the result of the UPDATE statement above.
It appears that there is a problem in DBI or with the libmysql code. We are
using one of the most recent versions of DBI (the newest has only doc bug
fixes as far as we could see). If the value will fit in 32 bits, it seems to
work. If not, it won't. Actually, it appears that one of these two pieces
of code (DBI or libmysql) uses a signed 32-bit integer somewhere. Alpha
machines may not be effected by this.
Can someone help on this? I have seen Tim Bunce on the list occassionally...
We are in the process of changing our code over to the longer method, but
this seems like it is a bug.
Best,
Kyle
--
Kyle Hayes
Quicknet Technologies t: +1 415 864 5225
520 Townsend St. Suite D f: +1 415 864 8388
San Francisco, CA 94103 w: http://www.quicknet.net
USA
***
"HEAR THE DIFFERENCE" with a live MICROTELCO demo at:
Computer Telephony EXPO, Mar 6-8, Los Angeles, CA
MicroTelco is a revolutionary service that brings multiple Internet
Telephony Service Providers (ITSPs) together in a convenient,
simple to use account center for greater reliability and flexibility.
For more information visit: http://www.microtelco.com.
-
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
questions on BACKUP/RESTORE commands
The manual section on BACKUP does not state what locking behavior the command has. Does it lock all the tables at once that you backup in one statement or does it lock them one at a time? I.e. if I run: BACKUP foo.bar, foo.baz, foo.blah TO '/tmp/mysqlbackups/' Will it lock all three tables and then dump them? I need this kind of behavior. If it locks them one at a time and dumps them one at a time then I'll have to use something else. Additionally, can you RESTORE a backed-up table to a different database. I.e. suppose I did the BACKUP line above, can I do this: RESTORE foo_backup.bar, foo_backup.baz, foo_backup.blah FROM '/tmp/mysqlbackups/' ??? Best, Kyle -- Kyle Hayes Quicknet Technologies t: +1 415 864 5225 520 Townsend St. Suite D f: +1 415 864 8388 San Francisco, CA 94103 w: http://www.quicknet.net USA "HEAR THE DIFFERENCE" with a live MICROTELCO demo at: INTERNET TELEPHONY EXPO, Feb 7-9, Miami, FL MicroTelco is a revolutionary service that brings multiple Internet Telephony Service Providers (ITSPs) together in a convenient, simple to use account center for greater reliability and flexibility. For more information visit: http://www.microtelco.com. - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 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: questions on BACKUP/RESTORE commands
Nevermind. I see that the manual online has this information. Sigh. It doesn't do what I want unfortunately. Best, Kyle On Monday 29 January 2001 09:32, Kyle Hayes wrote: The manual section on BACKUP does not state what locking behavior the command has. Does it lock all the tables at once that you backup in one statement or does it lock them one at a time? I.e. if I run: BACKUP foo.bar, foo.baz, foo.blah TO '/tmp/mysqlbackups/' Will it lock all three tables and then dump them? I need this kind of behavior. If it locks them one at a time and dumps them one at a time then I'll have to use something else. Additionally, can you RESTORE a backed-up table to a different database. I.e. suppose I did the BACKUP line above, can I do this: RESTORE foo_backup.bar, foo_backup.baz, foo_backup.blah FROM '/tmp/mysqlbackups/' ??? Best, Kyle -- Kyle Hayes Quicknet Technologies t: +1 415 864 5225 520 Townsend St. Suite D f: +1 415 864 8388 San Francisco, CA 94103 w: http://www.quicknet.net USA "HEAR THE DIFFERENCE" with a live MICROTELCO demo at: INTERNET TELEPHONY EXPO, Feb 7-9, Miami, FL MicroTelco is a revolutionary service that brings multiple Internet Telephony Service Providers (ITSPs) together in a convenient, simple to use account center for greater reliability and flexibility. For more information visit: http://www.microtelco.com. - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 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
