Re: Regression bug in 4.0.23? FROM_UNIXTIME(0) is now NULL, not "1970-01-01 01:00:00"

Hi, Peter! * Peter Valdemar M?rch <[EMAIL PROTECTED]> [04/12/28 17:33]: > Hi there, > > A bug was fixed in 4.0.23: > >* Fixed bug which caused FROM_UNIXTIME() function to return wrong > > result if the argument was too big. > >http://bugs.mysql.com/6439 > > However, it looks like it has si

Help: Index performance on moderately sized table

Hopefully someone can help me out here :) I have a pretty simple table that is 400k rows and growing. I'm doing, to me, some relatively simple searches but they are taking forever (6 seconds in my case is faaar too long). Anyway, here's the setup: Tested on MySQL 4.0.21 (Windows 2000 Server

value of array to sql statement

Hi list, There is problem when with executing the sql statement in mysql 5.0. I m not sure whether version 4.x has the same problem. The problem is if I use static (hardcoded) values in the SQL statement, mysql returns result for each element in the array. If I use dynamically allocated value tha

Re: SHOW TABLE STATUS: Update_Time Is Wrong?

Robinson, Eric wrote: When I execute SHOW TABLE STATUS on my master replication server, it shows an Update_Time for some tables that is more than 2 hours earlier than the Update_Time for the same tables on the slave server. However the bin log names, positions, and number or records are correct. Wh

SHOW TABLE STATUS: Update_Time Is Wrong?

When I execute SHOW TABLE STATUS on my master replication server, it shows an Update_Time for some tables that is more than 2 hours earlier than the Update_Time for the same tables on the slave server. However the bin log names, positions, and number or records are correct. What's with that? --

MySQL unixsocket to tcp connection

Hi, I have a RADIUS server (radiator), reading and writting intensively in a mysql database. I was timming my scripts, and the biggest time the scripts took are in the DBI->connect process The database is in another server, so I have to connect to the database over tcp. My q

Fixing "the worst InnoDB corruption bug in 3 years" - when

In the MySQL Manual under InnoDB in the "Using Per-Table Tablespace" section it says clearly at the top: NOTE: CRITICAL BUG in 4.1 if you specify innodb_file_per_table in `my.cnf'! If you shut down mysqld, then records may disappear from the secondary indexes of a table. See (Bug #7496) for more i

Re: unique key <-> primary key

Hi, RE: > And columns in primary keys must be NOT NULL. Columns in unique > keys can be NULL (if they are NOT NULL, then the unique key is > functionally the same as a primary key). OK, thanks guys for the explanation. Then the result of mysqldump table definition part: UNIQUE KEY HONstid (HON

Re: Monitoring replication in mysql

We use a monitoring system that does TCP based checks on our various systems and can alerts us based on criteria we define. So we right shell scripts that run locally and return certain values and tie those scripts to specific TCP ports using /etc/inetd.conf and /etc/services - This is the script w

Re: unique key <-> primary key

At 22:27 +0100 12/28/04, Martijn Tonies wrote: Hello, I have two tables, seemigly very similar setup; the primary key is the combination of two columns. With mysqldump, however, the table definition of the two tables looks different. Mysqldump on table 1 says ... UNIQUE KEY HONstid (H

Re: unique key <-> primary key

Hello, > I have two tables, seemigly very similar setup; the primary key is the > combination of two columns. With mysqldump, however, the table > definition of the two tables looks different. > > Mysqldump on table 1 says > ... > UNIQUE KEY HONstid (HONstid,HONname) > whereas on table 2 i

rpms for rhel

Which rpms are the right ones to use for RHEL v3u4? I'm not sure which release of mysql I need, yet, but looking at the site, I'm not sure how to tell which are the ones that would even be appropriate for my system. TIA, -ste -- MySQL General Mailing List For list archives: http://lists.mysql

Re: Multi table update

Zitat von Jay Blanchard > [snip] > And the query: > > UPDATE table1 > INNER JOIN table2 ON table2.ID_table1=table1.ID > SET table2.value=table2.value-IF(table1.condition=7,1,0), > table1.condition=8 > WHERE table1.ID IN (1,2) > > After that the value's in table2 are still 5 and 2. But I would

unique key <-> primary key

Hi, I have two tables, seemigly very similar setup; the primary key is the combination of two columns. With mysqldump, however, the table definition of the two tables looks different. Mysqldump on table 1 says ... UNIQUE KEY HONstid (HONstid,HONname) whereas on table 2 it says ...

Weird server timeout problem

Hello, I've been fighting this problem for a while, and now it's time to ask the experts. Please also CC me on this, I'm only on the list as a digest. I have a dual Xeon 2.8ghz HT server with plenty of ECC ram running Linux 2.6 and MySQL 4.1.8 (4.1.7 also). Anywhere from 0.001% to 0.05% or so o

Help upgrading from 4.1.1 to 4.1.8

I haven't found anything in the documentation that will help me upgrade mysql from 4.1.1 to 4.1.8. We have several databases running under 4.1.1. How do they get "migrated" to run under 4.1.8? How do we migrate our user data over? Can someone point me toward a FAQ that might help me or the docu

RE: Multi table update

"Jay Blanchard" <[EMAIL PROTECTED]> wrote on 12/28/2004 11:38:58 AM: > [snip] > And the query: > > UPDATE table1 > INNER JOIN table2 ON table2.ID_table1=table1.ID > SET table2.value=table2.value-IF(table1.condition=7,1,0), > table1.condition=8 > WHERE table1.ID IN (1,2) > > After that the v

Re: Error in Complex SELECT

I believe your problem is that versions of MySQL prior to 4.1 (i think) do not allow subqueries. You may try rewriting your query as a join. On Tue, 28 Dec 2004 12:14:06 -0600, Adam Ernst <[EMAIL PROTECTED]> wrote: > I'm trying to do the following SELECT statement: > > SELECT * FROM matches WHE

Re: Multi table update

My Testing and results at the bottom. Suspect a buggy implementation of the IF() calculation (see tests and results). Kai Ruhnau <[EMAIL PROTECTED]> wrote on 12/28/2004 11:06:43 AM: > Zitat von [EMAIL PROTECTED]: > > > Can you provide us a test case (sample table definitions and sample data)

Error in Complex SELECT

I'm trying to do the following SELECT statement: SELECT * FROM matches WHERE question_id = ANY (SELECT server_id FROM questions WHERE results_id = 'MyID') I've tried rephrasing it as: SELECT * FROM matches WHERE question_id IN (SELECT server_id FROM questions WHERE questions.results_id = 'MyID')

Re: Comparison SQL always return false

sam wun wrote: Hi, I use MySQL 5.0 with perl (the latest version) in FreeBSD 5.3. I found that if I assign a hard coded string (with double quote) to the string variable which in turns become part of the SQL statement, the SQL does return result. If I assign a value which is return from an array

RE: Multi table update

[snip] And the query: UPDATE table1 INNER JOIN table2 ON table2.ID_table1=table1.ID SET table2.value=table2.value-IF(table1.condition=7,1,0), table1.condition=8 WHERE table1.ID IN (1,2) After that the value's in table2 are still 5 and 2. But I would expect the 5 to be a 4. [/snip] It was con

Re: Multi table update

Zitat von [EMAIL PROTECTED]: > Can you provide us a test case (sample table definitions and sample data) > ? Sure. -- CREATE TABLE table1 ( ID int(10) unsigned NOT NULL default '0', condition int(10) unsigned NOT NULL default '0' ) TYPE=InnoDB; INSERT INTO table1 VALUES (1,7); INSERT INTO

is there a utility like mysqlbinlog but instead processes the query log?

is there a utility like mysqlbinlog but instead processes the query log? I would like to reprocess the queries from the query log. additional features that would be nice would include: listing unique queries the number of times each unique query is executed Thanks, Daniel -- MySQL Gen

RE: Multi table update

[snip] I believe your answer was neither on topic nor very useful. That is an excellent warning you quoted from the manual but I do not believe that it applies to this particular problem. I also humbly disagree with your analysis of the warning as neither triggers nor stored procedures are in pro

Re: Multi table update

Can you provide us a test case (sample table definitions and sample data) ? Shawn Green Database Administrator Unimin Corporation - Spruce Pine Kai Ruhnau <[EMAIL PROTECTED]> wrote on 12/28/2004 10:23:23 AM: > Zitat von [EMAIL PROTECTED]: > > > One reason may be due to your ON condition. > >

RE: Multi table update

"Jay Blanchard" <[EMAIL PROTECTED]> wrote on 12/28/2004 10:21:43 AM: > [snip] > UPDATE table1 > LEFT JOIN table2 ON table2.ID=table2.ID_table1 > SET table2.value=table2.value-IF(table1.condition=7,1,0), > table1.condition=8 > WHERE table1.ID=$id > > Is this behavior correct? > [/snip] > > I

RE: Newbie: Using SELECT to Get Table Status?

I knew about COUNT(*) but thanks. I was mostly looking for a way to get the time stamp of the last update or insert. I did find a way using an ADODB.Command object. I was able to send the command SHOW TABLE STATUS LIKE 'TABLENAME'; The results were returned as a recordset. Have a great day. --

Re: Multi table update

Zitat von [EMAIL PROTECTED]: > One reason may be due to your ON condition. > > ON table2.ID=table2.ID_table1 > > You reference table 2 twice and I doubt that on the table2 table id equals > id_table1 very often. Because table2 is LEFT JOINed to table1, you will > only be able to update the rows th

RE: Multi table update

[snip] UPDATE table1 LEFT JOIN table2 ON table2.ID=table2.ID_table1 SET table2.value=table2.value-IF(table1.condition=7,1,0), table1.condition=8 WHERE table1.ID=$id Is this behavior correct? [/snip] In the sense that it did what was given to it in the query, yes. From http://www.mysql.com/upd

Re: importing data

One reason may be due to your ON condition. ON table2.ID=table2.ID_table1 You reference table 2 twice and I doubt that on the table2 table id equals id_table1 very often. Because table2 is LEFT JOINed to table1, you will only be able to update the rows that match your ON condition. Again, that

Multi table update

Sorry for duplicating this message. But I sent the other one with the wrong subject. Hi, An SQL-statement that is conceptually equal to the following caused some astonishment. UPDATE table1 LEFT JOIN table2 ON table2.ID=table2.ID_table1 SET table2.value=table2.value-IF(table1.condition=7,1,0),

Comparison SQL always return false

Hi, I use MySQL 5.0 with perl (the latest version) in FreeBSD 5.3. I found that if I assign a hard coded string (with double quote) to the string variable which in turns become part of the SQL statement, the SQL does return result. If I assign a value which is return from an array in perl (eg. @

Regression bug in 4.0.23? FROM_UNIXTIME(0) is now NULL, not "1970-01-01 01:00:00"

Hi there, A bug was fixed in 4.0.23: * Fixed bug which caused FROM_UNIXTIME() function to return wrong result if the argument was too big. http://bugs.mysql.com/6439 However, it looks like it has side effects: select FROM_UNIXTIME(0) returns "1970-01-01 01:00:00" in 4.0.22 but NULL in 4.0.2

Re: importing data

OK, now I really feel stupid. It helps to change the ownership of the files to mysql:mysqlduh. Curtis Curtis Maurand wrote: I didn't, but I'll give it a shot and see what happens. Curtis Michael J. Pawlowsky wrote: Curtis Maurand wrote: If I create one of the databases and then put the files

Re: Newbie: Using SELECT to Get Table Status?

Hello. You may use count(*) for counting rows. "Robinson, Eric" <[EMAIL PROTECTED]> wrote: > The mysql>SHOW TABLE STATUS command returns such things as the number or > rows and time of the last update.=20 > > Is it possible to get the same information using a SELECT statement? > > F

Re: MySQL 4.0.23 wrong shared libraries output??

Hello. This is a bug. See: http://bugs.mysql.com/bug.php?id=7401 Neo Wee Teck <[EMAIL PROTECTED]> wrote: > 4.0.22 > > libdbug.a libmyisam.a libmysqlclient.so.12.0.0 libnisam.a > libheap.a libmyisammrg.a libmysqlclient.so > libmystrings.alibvio.a li

Re: myisamchk errors

Hello. Have you stopped MySQL server before executing myisamchk? sirisha gnvg <[EMAIL PROTECTED]> wrote: > [-- text/plain, encoding 8bit, charset: iso-8859-1, 21 lines --] > > hello everybody, > > I used myisamchk /path/to/datadir/mysql/*.* to find memory already in > use and

Re: importing data

I didn't, but I'll give it a shot and see what happens. Curtis Michael J. Pawlowsky wrote: Curtis Maurand wrote: If I create one of the databases and then put the files for that database from the old installation in place of the newly created ones, the database is recognized, but i get errors say

RE: are autoincrement values not always increasing in innodb?

Frank, "my experience MySQL returns the the rows in the order that you inserted them" This is true, if, and only if you have never deleted a record from the table. Like most SQL servers, MySQL leaves deleted records' space in the physical table "unoccupied", but still available. When you ins

Re: Where can I download HPUX11.0 PA-RISC 2.0 (LP64) platform binaries for 4.1.8 mysql?

Hi Vivek, all! Am Die, 2004-12-28 um 00.23 schrieb Rajan, Vivek K: > Hello- > > I am looking for "ELF-64 relocatable object file - PA-RISC 2.0 (LP64)" > platform binaries for 4.1.8 mysql. Please lemme know where I can > download this from? > > Thanx, > Vivek > > PS: I downloaded this from th

Re: importing data

Hi, An SQL-statement that is conceptually equal to the following caused some astonishment. UPDATE table1 LEFT JOIN table2 ON table2.ID=table2.ID_table1 SET table2.value=table2.value-IF(table1.condition=7,1,0), table1.condition=8 WHERE table1.ID=$id My problem is the IF condition, which alway e

Re: Slow as Christmas join

Kevin Cagle wrote: Hello! I have a couple of tables I am doing a join on. Both have 6 fields. Most fields have fewer than 16 characters in them, but at most 75. Neither table is huge: one with 14004 rows and the other with 23677. I created a temp table to insert the data into. When I perform