Re: multiple foreign key references on one column

2003-10-02 Thread dobbo 
Quoting Roman Neuhauser [EMAIL PROTECTED]:

 # [EMAIL PROTECTED] / 2003-09-25
 14:59:33 +0100:
  CREATE TABLE `pheno` (
`id` smallint(5) unsigned NOT NULL
 auto_increment,
`relevant` enum('y','n') default NULL,
`phenotype` varchar(50) NOT NULL default
 '',
PRIMARY KEY  (`id`,`phenotype`),
KEY `id` (`id`),
CONSTRAINT `0_125` FOREIGN KEY (`id`)
 REFERENCES
  `monogenic` (`phenotype_ID`) ON DELETE
 CASCADE,
CONSTRAINT `0_127` FOREIGN KEY (`id`)
 REFERENCES
  `knockout` (`phenotype_ID`) ON DELETE
 CASCADE
  ) TYPE=InnoDB
  
  
  The problem is that I can't insert a record
 into this
  table unless the value of 'id' is present in
 both the
  mongenic and knockout tables. I receive the
 following error:
  
  Cannot add or update a child row: a foreign
 key
  constraint fails
 
 what did you expect to happen instead, or,
 what did you expect the
 two FK's to do instead?
 
 -- 
 If you cc me or remove the list(s) completely
 I'll most likely ignore
 your message.see
 http://www.eyrie.org./~eagle/faqs/questions.html
 



I wanted the foreign key in the pheno table to reference
table monogenic OR knockout. Otherwise I have to
duplicate the pheno table - one copy with a foreign key
referencing knockout and the other referencing the
monogenic table. I'd rather not have to duplicate the
tables, but it looks like this is the only way as this
is how foreign keys work

cheers

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Fw: Problem deleting data

2003-10-02 Thread Victoria Reznichenko 
Andrew Pattison [EMAIL PROTECTED] wrote:
 
 I have a strange problem with MySQL 4.0.15 . When I delete an entire
 table,
 then run a repair on that table, it recovers all the rows which I just
 deleted! Also, if I do a check table I can see that the files on disk do
 not appear to have been altered in any way by the delete. Does anyone have
 any clues as to what is going on here?

What OS do you use? Windows?


-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Victoria Reznichenko
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.net
   ___/   www.mysql.com





-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

SQL query help

2003-10-02 Thread Svein E. Seldal 
Hi,

I have this table where the columns and rows are organized like this:

+--+--+
| a| b|
+--+--+
|1 |1 |
|2 |1 |
|2 |2 |
|2 |3 |
|2 |4 |
|3 |1 |
|3 |2 |
+--+--+
I want to run a select that gives me one row for each unique value of
'a'. And in the cases where several rows exists for one single value of
'a', I require the rows with the largest values of 'b'. In SQL lingo 
that would be DISTINCT A and MAX(B), but I dont know how to write this 
into one select...

I.e. if I would apply these rules on the set above, I should get the
following result:
+--+--+
| a| b|
+--+--+
|1 |1 |
|2 |4 |
|3 |2 |
+--+--+
Does anyone know how to formalize this request into a single SELECT
statement (using mysql 4.0.13), please?
Regards,
Svein Seldal


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: SQL query help

2003-10-02 Thread Svein E. Seldal 
Hi,

I forgot to mention that the table contains more information, it has 
more columns than just a and b. These extra columns contains the actual
information that I'm looking for.

I.e. the mentioned table could be looking like this:

 +--+--+--+--+-+---
 | a| b| data | user | comment | ...
 +--+--+--+--+-+---
And I still want those entire rows with DISTINCT A and MAX(B).

Regards
Svein
I have this table where the columns and rows are organized like this:

+--+--+
| a| b|
+--+--+
|1 |1 |
|2 |1 |
|2 |2 |
|2 |3 |
|2 |4 |
|3 |1 |
|3 |2 |
+--+--+
I want to run a select that gives me one row for each unique value of
'a'. And in the cases where several rows exists for one single value of
'a', I require the rows with the largest values of 'b'. In SQL lingo 
that would be DISTINCT A and MAX(B), but I dont know how to write this 
into one select...

I.e. if I would apply these rules on the set above, I should get the
following result:
+--+--+
| a| b|
+--+--+
|1 |1 |
|2 |4 |
|3 |2 |
+--+--+
Does anyone know how to formalize this request into a single SELECT
statement (using mysql 4.0.13), please?


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

PHP/MySQL/MyODBC drivers

2003-10-02 Thread Terry . L . Long 
I need to know the syntax to link two tables together that are in two
seperate DB's using the ODBC drivers in PHP.  Can anyone help with an
example?

Thanks
Terry L.


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Can't find file: 'XXX.MYI' (errno: 2)

2003-10-02 Thread shahanawaz lakhani 
Hi ! I am facing a real strange problem, this is the
third time some of my .MYI files are missing from the
database in the past 10 days. I restored the backup
but after a couple of days i observed that again some
of them were missing. This way i am really loosing 
very crucial data, i would really appreciate if any
one can throw some light towards this problem.

Our MySql version :  mysql  Ver 11.12 Distrib
3.23.32, for pc-linux-gnu (i686)

Thanking you in anticipation..





__
Do you Yahoo!?
The New Yahoo! Shopping - with improved product search
http://shopping.yahoo.com

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Can't find file: 'XXX.MYI' (errno: 2)

2003-10-02 Thread Antony Dovgal 
On Thu, 2 Oct 2003 07:33:55 -0700 (PDT)
shahanawaz lakhani [EMAIL PROTECTED] wrote:

 Our MySql version :  mysql  Ver 11.12 Distrib
 3.23.32, for pc-linux-gnu (i686)

upgrade first.
3.23.32 is _very_ old version.

---
WBR,
Antony Dovgal aka tony2001
[EMAIL PROTECTED]

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Creating InnoDB tables with ansi option fails

2003-10-02 Thread M . Dee 
Description:
I have a mysql server running with ansi option.

When I run my database create script I get:
  ERROR 1005: Can't create table './pinball/B.frm' (errno: 150)

This seems to indicate that that:
  150 = Foreign key constraint is incorrectly formed

I checked everything about the foreign key constraint but could find
no errors in the syntax. I seems that it has something to do with the
double-quotes () when using InnoDB tables.
How-To-Repeat:
Running the following script reproduces the problem:
CREATE TABLE A
(
A_IDinteger NOT NULL,
PRIMARY KEY (A_ID)
) TYPE=INNODB;

CREATE TABLE B
(
A_IDinteger,
INDEX(A_ID),
FOREIGN KEY (A_ID) REFERENCES A (A_ID)
) TYPE=INNODB;

I get no errors when running it like:
CREATE TABLE A
(
A_IDinteger NOT NULL,
PRIMARY KEY (A_ID)
) TYPE=INNODB;

CREATE TABLE B
(
A_IDinteger,
INDEX(A_ID),
FOREIGN KEY (A_ID) REFERENCES A (A_ID)
) TYPE=INNODB;

or:

CREATE TABLE A
(
A_IDinteger NOT NULL,
PRIMARY KEY (A_ID)
);

CREATE TABLE B
(
A_IDinteger,
INDEX(A_ID),
FOREIGN KEY (A_ID) REFERENCES A (A_ID)
);

Fix:
Unknown

Submitter-Id:  Marijn Dee
Originator:
Organization:
  Marijn DeeData Distilleries B.V.
  e-mail: [EMAIL PROTECTED]Kruislaan 402
1098 SM Amsterdam
The Netherlands

MySQL support: none
Synopsis:  Creating foreign key on InnoDB table with ansi option fails
Severity:  serious
Priority:  
Category:  mysql
Class: sw-bug
Release:   mysql-4.0.13 (Gentoo Linux mysql-4.0.13-r4)

C compiler:gcc (GCC) 3.2.3 20030422 (Gentoo Linux 1.4 3.2.3-r1, propolice)
C++ compiler:  g++ (GCC) 3.2.3 20030422 (Gentoo Linux 1.4 3.2.3-r1, propolice)
Environment:

System: Linux massicot 2.6.0-test5-mm4 #1 Tue Sep 23 14:49:07 CEST 2003 i686 Pentium 
III (Coppermine) GenuineIntel GNU/Linux
Architecture: i686

Some paths:  /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc
GCC: Reading specs from /usr/lib/gcc-lib/i686-pc-linux-gnu/3.2.3/specs
Configured with: /tmp/portage/gcc-3.2.3-r1/work/gcc-3.2.3/configure --prefix=/usr 
--bindir=/usr/i686-pc-linux-gnu/gcc-bin/3.2 
--includedir=/usr/lib/gcc-lib/i686-pc-linux-gnu/3.2.3/include 
--datadir=/usr/share/gcc-data/i686-pc-linux-gnu/3.2 
--mandir=/usr/share/gcc-data/i686-pc-linux-gnu/3.2/man 
--infodir=/usr/share/gcc-data/i686-pc-linux-gnu/3.2/info --enable-shared 
--host=i686-pc-linux-gnu --target=i686-pc-linux-gnu --with-system-zlib 
--enable-languages=c,c++,ada,f77,objc,java --enable-threads=posix --enable-long-long 
--disable-checking --enable-cstdio=stdio --enable-clocale=generic 
--enable-__cxa_atexit --enable-version-specific-runtime-libs 
--with-gxx-include-dir=/usr/lib/gcc-lib/i686-pc-linux-gnu/3.2.3/include/g++-v3 
--with-local-prefix=/usr/local --enable-shared --enable-nls --without-included-gettext
Thread model: posix
gcc version 3.2.3 20030422 (Gentoo Linux 1.4 3.2.3-r1, propolice)
Compilation info: CC='gcc'  CFLAGS='-march=pentium3 -O3 -pipe -mfpmath=sse -msse -mmmx 
-fomit-frame-pointer -DHAVE_ERRNO_AS_DEFINE=1 -DUSE_OLD_FUNCTIONS'  CXX='g++'  
CXXFLAGS='-march=pentium3 -O3 -pipe -mfpmath=sse -msse -mmmx -fomit-frame-pointer 
-felide-constructors -fno-exceptions -fno-rtti'  LDFLAGS=''  ASFLAGS=''
LIBC: 
lrwxrwxrwx1 root root   13 Jul 17 12:30 /lib/libc.so.6 - libc-2.3.2.so
-rwxr-xr-x1 root root  1466302 Jul 17 12:30 /lib/libc-2.3.2.so
-rw-r--r--1 root root  2663206 Jul 17 12:30 /usr/lib/libc.a
-rwxr-xr-x1 root root  204 Jul 17 12:30 /usr/lib/libc.so
-rwxr-xr-x1 root root   580404 Sep  9 10:41 /usr/lib/libc.so.5
Configure command: ./configure '--prefix=/usr' '--host=i686-pc-linux-gnu' 
'--mandir=/usr/share/man' '--infodir=/usr/share/info' '--datadir=/usr/share' 
'--sysconfdir=/etc' '--localstatedir=/var/lib' '--libexecdir=/usr/sbin' 
'--sysconfdir=/etc/mysql' '--localstatedir=/var/lib/mysql' '--with-raid' 
'--with-low-memory' '--enable-assembler' '--with-charset=latin1' 
'--enable-local-infile' '--with-mysqld-user=mysql' '--with-extra-charsets=all' 
'--enable-thread-safe-client' '--with-client-ldflags=-lstdc++' '--with-comment=Gentoo 
Linux mysql-4.0.13-r4' '--with-unix-socket-path=/var/run/mysqld/mysqld.sock' 
'--with-embedded-server' '--with-berkeley-db=./bdb' '--without-readline' 
'--enable-shared' '--enable-static' '--with-libwrap' '--with-innodb' '--with-vio' 
'--with-openssl' '--without-debug' 'CC=gcc' 'CFLAGS=-march=pentium3

Re: Is something like this even possible? (Result set ordering of grouped data)

2003-10-02 Thread Andrew Quap 
On 2 Oct 2003 at 11:41, Harald Fuchs wrote about Re: Is something like this even 
possible? (Result
 
 I think I found a solution.  For 4.1:
 
   SELECT r1.*
   FROM mytbl r1, (
   SELECT x1.category, x1.itemid, COUNT(x2.category) AS rank
   FROM mytbl x1
   LEFT JOIN mytbl x2 ON x2.category = x1.category
 AND x2.itemid = x1.itemid
 AND x2.timemodified  x1.timemodified
   GROUP BY x1.category, x1.itemid
 ) AS r2
   WHERE r2.category = r1.category
 AND r2.itemid = r1.itemid
   ORDER BY r1.category, r2.rank DESC, r1.timemodified DESC;
 
 For 4.0.x you'd need a temporary table instead of the subselect:
 
   CREATE TEMPORARY TABLE tmp AS
   SELECT x1.category, x1.itemid, COUNT(x2.category) AS rank
   FROM mytbl x1
   LEFT JOIN mytbl x2 ON x2.category = x1.category
 AND x2.itemid = x1.itemid
 AND x2.timemodified  x1.timemodified
   GROUP BY x1.category, x1.itemid;
 
   SELECT r1.*
   FROM mytbl r1
   JOIN tmp r2 ON r2.category = r1.category AND r2.itemid = r1.itemid
   ORDER BY r1.category, r2.rank DESC, r1.timemodified DESC;
 

That is close to what I was looking for thank you.  I want to sort via 
the max timemodified for the group not the count, it is possible and 
item would be created later after the updates I listed in the example 
and I would want that at the top.  Also, the tmp table was taking some 
20+ seconds to complete and the result was taking about 6 so I modified 
the query as follows:

CREATE TEMPORARY TABLE tmp AS
SELECT x1.category, x1.itemid, max(x1.timemodified) AS rank
FROM mytbl x1
GROUP BY x1.category, x1.itemid;

SELECT r1.*
FROM mytbl r1
JOIN tmp r2 ON r2.category = r1.category AND r2.itemid = r1.itemid
ORDER BY r1.category, r2.rank DESC, r1.timemodified DESC;

The complete query still takes about 10 seconds. with about 2 seconds 
for the tmp table and 8 for the results.  Maybe I still need to rethink 
my design

-Andrew





-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

mod_auth_mysql help !!!

2003-10-02 Thread Shun Xu 
 Hi,

 I am working on set up the new apache server on Linux using Mysql
databases,
 and moving all the files from old server to new one.

 Problem I have is how to set the mod_auth_mysql  on the new one?
 I need to have MySQl authentication when users to get in the website.
 Old machine Apache version: 1.3.20 , new machine Apache version is 2.0.

 The Mysql data database name is Safety, Safety has SUsers table,
 which has susername and spassword ... fields.

 on old server httpd.conf, there are follwing two sentences doing the
authentication:
 Auth_MYSQL_Info . webadmin  password(for webadmin)
 Auth_MySQL_General_db Safety

 webadmin is in the user table of mysql, but not in the Safety database,
 webadmin has all the provileges for Safety database.
 I have set user and db tables of mysql according the mysql database of old
one.

 on the new one:
 In the auth_mysql.conf file, I added the following line:
 Directory /var/www/html
AuthName MySQL authenticated zone
AuthType Basic

AuthMySQLUser webadmin
AuthMySQLPassword password for webadmin
AuthMySQLDB   Safety
AuthMySQLUserTable SUsers
AuthMySQLNameField  susername
AuthMySQLPasswordField spassword

require valid-user
/Directory

 After stop and start http, I get the authenticated login and password
prompt,
 that is what I need, but I can not login use  my susername and spassword in
 Safety database, which I do not have problem to get in on the old server.

 I have tried to reset my spassword in Safety database, it will not let me
in either,
 and I tried to use password('') function to reset my spassword, I can't
get in either,
 when I tried  use encrypt('') function to reset my spassword,
 and click on the new serve, it even does not give me the authenticated
login and password
 prompt and get nothing from Safety database.

 I add webadmin to the Safety database and grant all the privileges, it
happened the same
 as I use encrypt function to reset my password without prompt for login and
password.

 the mysql version on the old machine is 3.23.38, on the new one is 3.26.59
 and the spassword field is defined as varchar(16).

Thanks in advance for all your help and I am looking forward to hearing from
you !!!

 Shirly Xu
 TAMU


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

MySQL crash (replication?)

2003-10-02 Thread Partap Davis 
Hi folks,

I just set up a slave server and it seemed to be working alright, 
catching up with the new data, and then the server suddenly went away.   
Looking at the slave's data directory, there are approximately 2 
relay-bin files.  I can't even restart the server now, because it gets a 
signal 11 immediately.  Most of the relay-bin files have nothing in 
them, but the ones that do have data give me something like this:

[EMAIL PROTECTED] data]# mysqlbinlog stout-relay-bin.1588
# at 4
#700101  0:00:00 server id 3  log_pos 0 Rotate to 
kazoo-binlog.020  pos: 560177478
ERROR: Error in Log_event::read_log_event(): 'read error', data_len: 
302717, event_type: 2
ERROR: Could not read entry at offset 47 : Error in log format or read error
[EMAIL PROTECTED] data]#

I'm not even sure where to start here...I figure I'm going to have to 
get a fresh copy of all the data from the master, but it's about 350 
gigs so that will take a while.  Meanwhile I'm trying to figure out how 
to make this not happen again...

Here's a bit from my .err file:
030929 15:28:29  mysqld started
030929 15:28:30  InnoDB: Started
030929 15:28:30  Found invalid password for user: '[EMAIL PROTECTED]'; Ignoring 
user
/usr/sbin/mysqld-max: ready for connections.
Version: '4.0.14-Max'  socket: '/var/lib/mysql/mysql.sock'  port: 3306
030929 19:39:59  mysqld started
030929 19:40:00  InnoDB: Started
030929 19:40:00  Found invalid password for user: '[EMAIL PROTECTED]'; Ignoring 
user
/usr/sbin/mysqld-max: ready for connections.
Version: '4.0.14-Max'  socket: '/var/lib/mysql/mysql.sock'  port: 3306
mysqld got signal 11;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help 
diagnose
the problem, but since we have already crashed, something is definitely 
wrong
and this may fail.

key_buffer_size=268435456
read_buffer_size=2093056
max_used_connections=2
max_connections=100
threads_connected=2
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections 
= 1187439 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd=0x885ff10
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
Cannot determine thread, fp=0x41f08b78, backtrace may not be correct.
Stack range sanity check OK, backtrace follows:
0x80dbe1f
0x40037f75
0x420828fc
0x8351165
0x8350f93
0x8350f54
0x8350f54
0x8350f54
0x8350f54
0x8351b6f
0x83711b9
0x837118d
0x83711c9
0x837118d
0x83711c9
0x837118d
0x837118d
0x83711c9
0x837118d
0x83709b9
0x83520fa
0x8351ed5
0x83508df
0x8136669
0x81108b8
0x814d74c
0x814cf7c
0x80e9363
0x80ea88b
0x80e5ed3
0x80ebe0e
0x80e50bf
0x40034fef
0x420e779a
New value of fp=(nil) failed sanity check, terminating stack trace!
Please read http://www.mysql.com/doc/en/Using_stack_trace.html and 
follow instructions on how to resolve the stack trace. Resolved
stack trace is much more helpful in diagnosing the problem, so please do
resolve it
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd-query at 0x8870968 = load data local infile 
'/db2/data/tmp/FcstHr_ece.txt' replace into table FcstHr_ece
thd-thread_id=49
The manual page at http://www.mysql.com/doc/en/Crashing.html contains
information that should help you find out what is causing the crash.

Number of processes running now: 0
031001 00:57:10  mysqld restarted
031001  0:57:11  InnoDB: Started
031001  0:57:11  Found invalid password for user: '[EMAIL PROTECTED]'; Ignoring 
user
/usr/sbin/mysqld-max: ready for connections.
Version: '4.0.14-Max'  socket: '/var/lib/mysql/mysql.sock'  port: 3306
mysqld got signal 11;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help 
diagnose
the problem, but since we have already crashed, something is definitely 
wrong
and this may fail.

key_buffer_size=268435456
read_buffer_size=2093056
max_used_connections=1
max_connections=100
threads_connected=1
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections 
= 1187439 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd=0x88323e8
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
Cannot determine thread, fp=0x41ed7b48, backtrace may not be correct.
Stack range sanity check OK, backtrace follows:
0x80dbe1f
0x40037f75

Query speed issue

2003-10-02 Thread Brad Teale 
Hello, 

The problem:
I have the following query with is taking upwards of 2 minutes to complete
and we need it faster, prefer less than 30 seconds (don't laugh):
select modelhr, avg(f.temp-b.temp), avg(abs(f.temp-b.temp)),
stddev(f.temp-b.temp), stddev(abs(f.temp-b.temp)), count(f.temp-b.temp) from
foo as f, bar as b where f.fyearmoda=b.yearmoda and f.fhr=b.hr and
f.stn=b.stn and b.yearmoda = '2003-01-01' and b.yearmoda = '2003-01-31'
and b.stn='' group by modelhr;

When we run explain we get:
+---+---+---+-+-+---
++
| table | type  | possible_keys | key | key_len | ref
| rows | Extra|
+---+---+---+-+-+---
++
| b | range | PRIMARY,interp_hr | PRIMARY |   7 | NULL
|  679 | Using where; Using temporary; Using filesort |
| f | ref   | stn,fcst  | stn |  11 |
const,m.yearmoda,m.Hr |   26 | Using where
|
+---+---+---+-+-+---
+--+--+

Is there a reasonable way to get this query from using temporary and
filesort?  I tried dumping the data into a temporary table, and the explain
ran the same.  Also, both MySQL setups perform the same.  Any
ideasPlease!

 System/Table Stuff Below
-
System: dual Xeon 2.4GHz machine with 2G RAM
Interconnect: QLogicFC 2200
Disk1: Sun T3 hardware raid5 with Reiserfs (64M cache controller)
Disk2: Sun T3 hardware raid0 with Reiserfs (64M cache controller)
OS: Red Hat Linux release 8.0 (with qlogicfc module)
MySQL1: 4.0.14 - prebuilt MySQL rpm uses Disk1
MySQL2: 4.0.15a - Hand built with MySQL Manual options uses Disk2

The table structures are as follows:
CREATE TABLE foo (
  yearmoda date NOT NULL default '-00-00',
  mruntime int(2) NOT NULL default '0',
  mhr int(3) NOT NULL default '0',
  fyearmoda date NOT NULL default '-00-00',
  fhr int(2) NOT NULL default '0',
  stn varchar(4) NOT NULL default '',
  temp decimal(6,2) default NULL,
... more but unused data here
  PRIMARY KEY  (yearmoda,mruntime,mhr,stn),
  KEY stn (stn,fyearmoda,fhr),
  KEY fcst (stn,yearmoda,mruntime)
) TYPE=MyISAM;

CREATE TABLE bar (
  stn char(4) NOT NULL default '',
  hr int(2) NOT NULL default '0',
  min int(2) NOT NULL default '0',
  day int(2) NOT NULL default '0',
  temp decimal(6,2) NOT NULL default '0.00',
... More unused data here
  yearmoda date NOT NULL default '-00-00',
  PRIMARY KEY  (stn,yearmoda,hr,min),
  KEY interp_hr (yearmoda,hr,stn)
) TYPE=MyISAM;

Table Stats:
foo - 38G - Data/18G - Index (326K rows)
bar - 24G - Data/14G - Index (35K rows)

  
Thanks,
Brad

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Seeking a good book on MySQL / ASP

2003-10-02 Thread MJ Dundee 
I have searched the web and a couple book stores for a book that can refer to covering 
MySQL with ASP.  I called MySQL to see if they had a reccomendation and they referred 
me to the maling lists. 

Thanks in advance...

Michael 
 





Sent via the WebMail system at mjdundeez.com


 
   

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Query speed issue

2003-10-02 Thread aguia 

What are the configuration you are using? What's the size of your buffers?
What's your system?

Maybe increasing sort buffer and key buffer will be good.

;)
Alexis

Quoting Brad Teale [EMAIL PROTECTED]:

 Hello, 
 
 The problem:
 I have the following query with is taking upwards of 2 minutes to complete
 and we need it faster, prefer less than 30 seconds (don't laugh):
 select modelhr, avg(f.temp-b.temp), avg(abs(f.temp-b.temp)),
 stddev(f.temp-b.temp), stddev(abs(f.temp-b.temp)), count(f.temp-b.temp) from
 foo as f, bar as b where f.fyearmoda=b.yearmoda and f.fhr=b.hr and
 f.stn=b.stn and b.yearmoda = '2003-01-01' and b.yearmoda = '2003-01-31'
 and b.stn='' group by modelhr;
 
 When we run explain we get:
 +---+---+---+-+-+---
 ++
 | table | type  | possible_keys | key | key_len | ref
 | rows | Extra|
 +---+---+---+-+-+---
 ++
 | b | range | PRIMARY,interp_hr | PRIMARY |   7 | NULL
 |  679 | Using where; Using temporary; Using filesort |
 | f | ref   | stn,fcst  | stn |  11 |
 const,m.yearmoda,m.Hr |   26 | Using where
 |
 +---+---+---+-+-+---
 +--+--+
 
 Is there a reasonable way to get this query from using temporary and
 filesort?  I tried dumping the data into a temporary table, and the explain
 ran the same.  Also, both MySQL setups perform the same.  Any
 ideasPlease!
 
  System/Table Stuff Below
 -
 System: dual Xeon 2.4GHz machine with 2G RAM
 Interconnect: QLogicFC 2200
 Disk1: Sun T3 hardware raid5 with Reiserfs (64M cache controller)
 Disk2: Sun T3 hardware raid0 with Reiserfs (64M cache controller)
 OS: Red Hat Linux release 8.0 (with qlogicfc module)
 MySQL1: 4.0.14 - prebuilt MySQL rpm uses Disk1
 MySQL2: 4.0.15a - Hand built with MySQL Manual options uses Disk2
 
 The table structures are as follows:
 CREATE TABLE foo (
   yearmoda date NOT NULL default '-00-00',
   mruntime int(2) NOT NULL default '0',
   mhr int(3) NOT NULL default '0',
   fyearmoda date NOT NULL default '-00-00',
   fhr int(2) NOT NULL default '0',
   stn varchar(4) NOT NULL default '',
   temp decimal(6,2) default NULL,
 ... more but unused data here
   PRIMARY KEY  (yearmoda,mruntime,mhr,stn),
   KEY stn (stn,fyearmoda,fhr),
   KEY fcst (stn,yearmoda,mruntime)
 ) TYPE=MyISAM;
 
 CREATE TABLE bar (
   stn char(4) NOT NULL default '',
   hr int(2) NOT NULL default '0',
   min int(2) NOT NULL default '0',
   day int(2) NOT NULL default '0',
   temp decimal(6,2) NOT NULL default '0.00',
 ... More unused data here
   yearmoda date NOT NULL default '-00-00',
   PRIMARY KEY  (stn,yearmoda,hr,min),
   KEY interp_hr (yearmoda,hr,stn)
 ) TYPE=MyISAM;
 
 Table Stats:
 foo - 38G - Data/18G - Index (326K rows)
 bar - 24G - Data/14G - Index (35K rows)
 
   
 Thanks,
 Brad
 
 -- 
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:   
 http://lists.mysql.com/[EMAIL PROTECTED]
 



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

trouble compiling

2003-10-02 Thread James Dennis 
Hello,

I am trying to compile MySQL 4.0.15a on a Solaris system. I am not  
having much luck and would appreciate some help.

Here is the error and I will tell all information I can think of that  
might help:

gcc -O3 -DDBUG_OFF -I/usr/local/openssl/include  
-I/usr/local/openssh/include -I/usr/local/mysql/include/mysql  
-I/usr/local/openldap/include -I/usr/local/share/libtool/libltdl  
-I/usr/local/krb5/include -I/usr/local/db/include  
-D_FILE_OFFSET_BITS=64 -DHAVE_CURSES_H  
-I/usr/local/src/mysql-4.0.15a/include -DHAVE_RWLOCK_T -o  
test_thr_alarm -DDEFAULT_BASEDIR=\/usr/local/mysql-4.0.15a\  
-DDATADIR=\/usr/local/mysql-4.0.15a/var\  
-DDEFAULT_CHARSET_HOME=\/usr/local/mysql-4.0.15a\  
-DSHAREDIR=\/usr/local/mysql-4.0.15a/share/mysql\ -DHAVE_CONFIG_H  
-I./../include -I../include -I.. -I. -I/usr/local/openssl/include  
-I/usr/local/openssh/include -I/usr/local/mysql/include/mysql  
-I/usr/local/openldap/include -I/usr/local/share/libtool/libltdl  
-I/usr/local/krb5/include -I/usr/local/db/include -O3 -DDBUG_OFF  
-I/usr/local/openssl/include -I/usr/local/openssh/include  
-I/usr/local/mysql/include/mysql -I/usr/local/openldap/include  
-I/usr/local/share/libtool/libltdl -I/usr/local/krb5/include  
-I/usr/local/db/include -D_FILE_OFFSET_BITS=64 -DHAVE_CURSES_H  
-I/usr/local/src/mysql-4.0.15a/include -DHAVE_RWLOCK_T -DMAIN  
./test_thr_alarm.c  -L/opt/sfw/lib -L/usr/local/openssl/lib  
-L/usr/local/openssh/lib -L/usr/local/mysql/lib/mysql  
-L/usr/local/openldap/lib -L/usr/local/lib -L/usr/local/krb5/lib  
-L/usr/local/db/lib -ldl libmysys.a ../dbug/libdbug.a  
../strings/libmystrings.a -lpthread -lthread -lz -lposix4 -lcrypt -lgen  
-lsocket -lnsl -lm -lpthread -lthread  
-R/opt/sfw/lib:/usr/local/openssl/lib:/usr/local/openssh/lib:/usr/ 
local/mysql/lib/mysql:/usr/local/openldap/lib:/usr/local/krb5/lib:/usr/ 
local/db/lib:/usr/local/lib
Undefined   first referenced
 symbol in file
set_timespec/var/tmp/ccHjlqre.o
ld: fatal: Symbol referencing errors. No output written to  
test_thr_alarm
collect2: ld returned 1 exit status
gmake[2]: *** [test_thr_alarm] Error 1
gmake[2]: Leaving directory `/usr/local/src/mysql-4.0.15a/mysys'
gmake[1]: *** [all-recursive] Error 1
gmake[1]: Leaving directory `/usr/local/src/mysql-4.0.15a'
gmake: *** [all] Error 2

So... set_timespec is related to pthreads from what I can tell so I'm  
assuming pthreads is having the issue. We usually do our MySQL  
install's from source and the 3.23 branch seemed to work fine. I  
checked in /path/to/source/include/my_global.h and there seems to be a  
set_timespec define. Maybe that's a backup if pthreads goes funny or  
doesn't exist? Either way, I attempted to add the code from my_global  
(copied below in case it is unclear what I actually copied), directly  
into the source to see if maybe it would compile (I know this isn't the  
best idea, but for information gathering purposes, hey, why not?)  
Anyway, when I ran make the original source somehow came back and over  
wrote my changes. Can't say I was aware make knew how to do that...

#define set_timespec(ABSTIME,SEC) \
{\
  struct timeval tv;\
  gettimeofday(tv,0);\
  (ABSTIME).tv_sec=tv.tv_sec+(time_t) (SEC);\
  (ABSTIME).tv_nsec=tv.tv_usec*1000;\
}
uname -a shows: SunOS hostname 5.8 Generic_108528-21 sun4u sparc  
SUNW,Ultra-60

Any help would be VERY appreciated!

- James Dennis

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

load data infile question

2003-10-02 Thread jawahar . muthukrishnan 
In MYSQL documentation the following line is

Some cases are not supported by LOAD DATA INFILE:
1. Fixed-size rows( FIELDS TERMINATED BY and FIELDS ENCLOSED BY both empty)
and BLOB or TEXT columns

I defined a column in a table as type TEXT. I then loaded values using LOAD
DATA INFILE into the table. Since I was able to load data for a TEXT
column, what does the above statement mean..




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Creating InnoDB tables with ansi option fails

2003-10-02 Thread Heikki Tuuri 
M. Dee,

yes, this is a known bug. I already fixed it in spring, but then
inadvertently wiped out the changes :(. Let us try to keep this bug in mind
so that it gets fixed soon.

Best regards,

Heikki Tuuri
Innobase Oy
http://www.innodb.com
Foreign keys, transactions, and row level locking for MySQL
InnoDB Hot Backup - a hot backup tool for MySQL



- Original Message - 
From: [EMAIL PROTECTED]
Newsgroups: mailing.database.myodbc
Sent: Thursday, October 02, 2003 5:39 PM
Subject: Creating InnoDB tables with ansi option fails


 Description:
 I have a mysql server running with ansi option.

 When I run my database create script I get:
   ERROR 1005: Can't create table './pinball/B.frm' (errno: 150)

 This seems to indicate that that:
   150 = Foreign key constraint is incorrectly formed

 I checked everything about the foreign key constraint but could
find
 no errors in the syntax. I seems that it has something to do with the
 double-quotes () when using InnoDB tables.
 How-To-Repeat:
 Running the following script reproduces the problem:
 CREATE TABLE A
 (
 A_IDinteger NOT NULL,
 PRIMARY KEY (A_ID)
 ) TYPE=INNODB;

 CREATE TABLE B
 (
 A_IDinteger,
 INDEX(A_ID),
 FOREIGN KEY (A_ID) REFERENCES A (A_ID)
 ) TYPE=INNODB;

 I get no errors when running it like:
 CREATE TABLE A
 (
 A_IDinteger NOT NULL,
 PRIMARY KEY (A_ID)
 ) TYPE=INNODB;

 CREATE TABLE B
 (
 A_IDinteger,
 INDEX(A_ID),
 FOREIGN KEY (A_ID) REFERENCES A (A_ID)
 ) TYPE=INNODB;

 or:

 CREATE TABLE A
 (
 A_IDinteger NOT NULL,
 PRIMARY KEY (A_ID)
 );

 CREATE TABLE B
 (
 A_IDinteger,
 INDEX(A_ID),
 FOREIGN KEY (A_ID) REFERENCES A (A_ID)
 );

 Fix:
 Unknown

 Submitter-Id: Marijn Dee
 Originator:
 Organization:
   Marijn DeeData Distilleries B.V.
   e-mail: [EMAIL PROTECTED]Kruislaan 402
 1098 SM Amsterdam
 The Netherlands
 
 MySQL support: none
 Synopsis: Creating foreign key on InnoDB table with ansi option fails
 Severity: serious
 Priority:
 Category: mysql
 Class: sw-bug
 Release: mysql-4.0.13 (Gentoo Linux mysql-4.0.13-r4)

 C compiler:gcc (GCC) 3.2.3 20030422 (Gentoo Linux 1.4 3.2.3-r1,
propolice)
 C++ compiler:  g++ (GCC) 3.2.3 20030422 (Gentoo Linux 1.4 3.2.3-r1,
propolice)
 Environment:

 System: Linux massicot 2.6.0-test5-mm4 #1 Tue Sep 23 14:49:07 CEST 2003
i686 Pentium III (Coppermine) GenuineIntel GNU/Linux
 Architecture: i686

 Some paths:  /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc
/usr/bin/cc
 GCC: Reading specs from /usr/lib/gcc-lib/i686-pc-linux-gnu/3.2.3/specs
 Configured with:
/tmp/portage/gcc-3.2.3-r1/work/gcc-3.2.3/configure --prefix=/usr --bindir=/u
sr/i686-pc-linux-gnu/gcc-bin/3.2 --includedir=/usr/lib/gcc-lib/i686-pc-linux
-gnu/3.2.3/include --datadir=/usr/share/gcc-data/i686-pc-linux-gnu/3.2 --man
dir=/usr/share/gcc-data/i686-pc-linux-gnu/3.2/man --infodir=/usr/share/gcc-d
ata/i686-pc-linux-gnu/3.2/info --enable-shared --host=i686-pc-linux-gnu --ta
rget=i686-pc-linux-gnu --with-system-zlib --enable-languages=c,c++,ada,f77,o
bjc,java --enable-threads=posix --enable-long-long --disable-checking --enab
le-cstdio=stdio --enable-clocale=generic --enable-__cxa_atexit --enable-vers
ion-specific-runtime-libs --with-gxx-include-dir=/usr/lib/gcc-lib/i686-pc-li
nux-gnu/3.2.3/include/g++-v3 --with-local-prefix=/usr/local --enable-shared 
--enable-nls --without-included-gettext
 Thread model: posix
 gcc version 3.2.3 20030422 (Gentoo Linux 1.4 3.2.3-r1, propolice)
 Compilation info: CC='gcc'

CFLAGS='-march=pentium3 -O3 -pipe -mfpmath=sse -msse -mmmx -fomit-frame-poin
ter -DHAVE_ERRNO_AS_DEFINE=1 -DUSE_OLD_FUNCTIONS'  CXX='g++'
CXXFLAGS='-march=pentium3 -O3 -pipe -mfpmath=sse -msse -mmmx -fomit-frame-po
inter -felide-constructors -fno-exceptions -fno-rtti'  LDFLAGS=''
ASFLAGS=''
 LIBC:
 lrwxrwxrwx1 root root   13 Jul 17 12:30 /lib/libc.so.6 -
libc-2.3.2.so
 -rwxr-xr-x1 root root  1466302 Jul 17 12:30 /lib/libc-2.3.2.so
 -rw-r--r--1 root root  2663206 Jul 17 12:30 /usr/lib/libc.a
 -rwxr-xr-x1 root root  204 Jul 17 12:30 /usr/lib/libc.so
 -rwxr-xr-x1 root root   580404 Sep  9 10:41 /usr/lib/libc.so.5
 Configure command: ./configure '--prefix=/usr' '--host=i686-pc-linux-gnu'
'--mandir=/usr/share/man' '--infodir=/usr/share/info' '--datadir=/usr/share'
'--sysconfdir=/etc' '--localstatedir=/var/lib' '--libexecdir=/usr/sbin'
'--sysconfdir=/etc/mysql' '--localstatedir=/var/lib/mysql' '--with-raid'
'--with-low-memory' '--enable-assembler' '--with-charset=latin1'
'--enable-local-infile' '--with-mysqld-user=mysql'
'--with-extra-charsets=all' '--enable-thread-safe-client'
'--with-client-ldflags=-lstdc++' '--with-comment=Gentoo Linux
mysql-4.0.13-r4' '--with-unix-socket-path=/var/run/mysqld/mysqld.sock'

Re: load data infile question

2003-10-02 Thread gerald_clark 


[EMAIL PROTECTED] wrote:

In MYSQL documentation the following line is

Some cases are not supported by LOAD DATA INFILE:
1. Fixed-size rows( FIELDS TERMINATED BY and FIELDS ENCLOSED BY both empty)
and BLOB or TEXT columns
I defined a column in a table as type TEXT. I then loaded values using LOAD
DATA INFILE into the table. Since I was able to load data for a TEXT
column, what does the above statement mean..
Well, when loading a single column table, there are no field separators.
The line terminator is sufficient.
Try defining a table with more than one field, and see what happens.

 



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Currency

2003-10-02 Thread Fabio Bernardo 
I´m having some problems with currency´s fields. Actually I dont know what
mysql´s field typeI have to choose..^
I wanna input this value: U$32.00 but, when I write the query: Select
'field' from table it returns..
'32' and not 32.00.

Which select statement do i have to write?

thanks

Re: Fwd: [ANN] INTRODUCING LDMLMySQL

2003-10-02 Thread Bill Doerrfeld 
At 3:12 PM -0700 10/1/03, Bill Doerrfeld wrote:
FYI. A 3rd party developer has just come out with an awesome Web 
browser based GUI for managing MySQL databases. Check it out!


To: [EMAIL PROTECTED]
Date: Wed, 01 Oct 2003 15:15:51 -0400
Subject: [ANN] INTRODUCING LDMLMySQL
From: Jim Van Heule [EMAIL PROTECTED]

INTRODUCING LDMLMYSQL THE OPEN SOURCE MYSQL DATABASE MANAGER AND QUERY
BUILDER FOR LASSO PROFESSIONAL
Note: Just thought I'd pass along that the developer of this free 
offering is changing its name. Presumably it will also be available 
at a new URL. If you're at all interested in being notified of this, 
you might contact Jim Van Heule at [EMAIL PROTECTED].

Enjoy!
--
-
Bill Doerrfeld[EMAIL PROTECTED]
Blue World Communications, Inc.   http://www.blueworld.com/
-
 Build and serve powerful data-driven Web sites
  with Lasso Studio and Lasso Professional.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Rephrase Select Statement

2003-10-02 Thread Carlos Vazquez 
Hi all!

I'd like to replace the values of nIndex field in NewOrder table with
the values of the same field in the table InvMast...

In MySQL 4 it is like this:

UPDATE neworder, invmast SET NewOrder.nIndex = InvMast.nIndex WHERE
neworder.sItemNo = invmast.sItemNo

The problem is this does not work in version 3.23 and need to re-write
the Select Statement...  

Any suggestions?


Thanks,

Carlos

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

bug in FOUND_ROWS

2003-10-02 Thread Shane Allen 
Running 4.0.15a. The second select's FOUND_ROWS() returns the wrong number of rows.

To test yourself:

create database test1234;
\r test1234
create table test (id int, primary key (id));
insert into test values (1), (2), (3), (4), (5);
select SQL_CALC_FOUND_ROWS * from test where id  3 limit 0, 1;
select FOUND_ROWS();
select SQL_CALC_FOUND_ROWS * from test where id  3 AND 1=2 limit 0, 1;
select FOUND_ROWS();
select SQL_CALC_FOUND_ROWS * from test where id  6 limit 0, 1;
select FOUND_ROWS();

My results:

mysql create database test1234;
Query OK, 1 row affected (0.01 sec)

mysql \r test1234
Connection id:122
Current database: test1234

mysql create table test (id int, primary key (id));
Query OK, 0 rows affected (0.01 sec)

mysql insert into test values (1), (2), (3), (4), (5);
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql select SQL_CALC_FOUND_ROWS * from test where id  3 limit 0, 1;
++
| id |
++
|  4 |
++
1 row in set (0.00 sec)

mysql select FOUND_ROWS();
+--+
| FOUND_ROWS() |
+--+
|2 |
+--+
1 row in set (0.00 sec)

mysql select SQL_CALC_FOUND_ROWS * from test where id  3 AND 1=2 limit 0, 1;
Empty set (0.00 sec)

mysql select FOUND_ROWS();
+--+
| FOUND_ROWS() |
+--+
|2 |
+--+
1 row in set (0.00 sec)

mysql select SQL_CALC_FOUND_ROWS * from test where id  6 limit 0, 1;
Empty set (0.00 sec)

mysql select FOUND_ROWS();
+--+
| FOUND_ROWS() |
+--+
|0 |
+--+
1 row in set (0.00 sec)

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Possible Commit Syntax Change for Improved TPS

2003-10-02 Thread Jeremy Zawodny 
On Tue, Sep 30, 2003 at 09:36:46PM +0100, Seun Osewa  wrote:
 Hi Jeremy,
 
 Hmmm ... I guess the meat of my suggestion is that it be made
 available on a per-transaction basis because in many applications
 some transactions are more critical than others.  So even on
 systems where there are *some* transactions that need to be
 flushed to log immediately we can still get some speedups by
 delaying log writes for transactions that do not need this...
 transactions that can afford to be lost if the server crashes.

I agree.  For ultimate flexability, you'd want this done on a
per-transaction basis.  I was simply pointing out that you can
get *some* of the benefit today at a server-wide level.

Jeremy
-- 
Jeremy D. Zawodny |  Perl, Web, MySQL, Linux Magazine, Yahoo!
[EMAIL PROTECTED]  |  http://jeremy.zawodny.com/

MySQL 4.0.15-Yahoo-SMP: up 18 days, processed 681,914,400 queries (417/sec. avg)

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Install question Linux RPMs

2003-10-02 Thread Harrell, Roger 
I am installing MySQL 4.0.15 on Red Hat Linux 8. I ran the rpms for server
and client. It installed and started mysqld but none of the command set
seems to be on the system anywhere. No mysql, mysqladmin, etc. The docs
don't give much help after running the rpms.  After secton 2.1.2 it refers
to section 2.4 Post-installation Setup and Testing. This section does not
talk about steps after running rpm -i on the server and client package. I
can't figure out what occurred. Has anyone experienced this before, or have
ideas on how to troubleshoot? 

Thanks,
Roger

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Install question Linux RPMs

2003-10-02 Thread Christensen, Dave 
You're going to have to see where the files were installed.  It was easier
with Red Hat 7.3 because the Kpackage program would tell you where the files
were installed.  I don't know how you find that info out with Red Hat 8.0 or
9.0.

-Original Message-
From: Harrell, Roger [mailto:[EMAIL PROTECTED] 
Sent: Thursday, October 02, 2003 4:56 PM
To: '[EMAIL PROTECTED]'
Subject: Install question Linux RPMs


I am installing MySQL 4.0.15 on Red Hat Linux 8. I ran the rpms for server
and client. It installed and started mysqld but none of the command set
seems to be on the system anywhere. No mysql, mysqladmin, etc. The docs
don't give much help after running the rpms.  After secton 2.1.2 it refers
to section 2.4 Post-installation Setup and Testing. This section does not
talk about steps after running rpm -i on the server and client package. I
can't figure out what occurred. Has anyone experienced this before, or have
ideas on how to troubleshoot? 

Thanks,
Roger

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

setting the wait_timeout option - doesn't work?

2003-10-02 Thread Matt Babineau 
Hey All--

I have MySQL 4.0.14 running on Redhat Linux and it won't accept my
change to the wait_timeout config option!

In the my.cnf file I have a line

wait_timeout = 60

but when I restart mysql and go into the server and type SHOW VARIABLES;

it says the wait_timeout is still 28800 which is too long!

Any ideas?

Thanks,
Matt


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: setting the wait_timeout option - doesn't work?

2003-10-02 Thread Jeremy Zawodny 
On Thu, Oct 02, 2003 at 02:02:34PM -0400, Matt Babineau wrote:
 Hey All--
 
 I have MySQL 4.0.14 running on Redhat Linux and it won't accept my
 change to the wait_timeout config option!
 
 In the my.cnf file I have a line
 
   wait_timeout = 60
 
 but when I restart mysql and go into the server and type SHOW VARIABLES;
 
 it says the wait_timeout is still 28800 which is too long!

Are you sure MySQL is reading the my.cnf file?

Is the setting in the correct ([mysqld]) section?

Jeremy
-- 
Jeremy D. Zawodny |  Perl, Web, MySQL, Linux Magazine, Yahoo!
[EMAIL PROTECTED]  |  http://jeremy.zawodny.com/

MySQL 4.0.15-Yahoo-SMP: up 18 days, processed 682,784,147 queries (417/sec. avg)

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: setting the wait_timeout option - doesn't work?

2003-10-02 Thread Matt Babineau 
Yeah mysql is reading the config file, because I have set other options
like query_cache_size...so I'm not sure :-(

On Thu, 2003-10-02 at 18:09, Jeremy Zawodny wrote:
 On Thu, Oct 02, 2003 at 02:02:34PM -0400, Matt Babineau wrote:
  Hey All--
  
  I have MySQL 4.0.14 running on Redhat Linux and it won't accept my
  change to the wait_timeout config option!
  
  In the my.cnf file I have a line
  
  wait_timeout = 60
  
  but when I restart mysql and go into the server and type SHOW VARIABLES;
  
  it says the wait_timeout is still 28800 which is too long!
 
 Are you sure MySQL is reading the my.cnf file?
 
 Is the setting in the correct ([mysqld]) section?
 
 Jeremy


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: setting the wait_timeout option - doesn't work?

2003-10-02 Thread miguel solorzano 
At 14:02 2/10/2003 -0400, Matt Babineau wrote:

Hi,

cut
it says the wait_timeout is still 28800 which is too long!

Any ideas?
mysql show variables like wait_timeout;
+---+---+
| Variable_name | Value |
+---+---+
| wait_timeout  | 28800 |
+---+---+
1 row in set (0.00 sec)
mysql SET @@WAIT_TIMEOUT=60;
Query OK, 0 rows affected (0.00 sec)
mysql show variables like wait_timeout;
+---+---+
| Variable_name | Value |
+---+---+
| wait_timeout  | 60|
+---+---+
1 row in set (0.00 sec)
--
Regards,
For technical support contracts, visit https://order.mysql.com/
Are you MySQL certified?, http://www.mysql.com/certification/
Miguel Angel Solórzano [EMAIL PROTECTED]
São Paulo - Brazil

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.516 / Virus Database: 313 - Release Date: 1/9/2003

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Install question Linux RPMs

2003-10-02 Thread Harrell, Roger 
Ugh, ok, somehow the client package did not install. When I searched for
files I found it odd that the client binaries were the ones missing. I must
have fat fingered the rpm -i command. I installed them and it's working now.
:-P

Roger 

-Original Message-
From: Christensen, Dave [mailto:[EMAIL PROTECTED]
Sent: Thursday, October 02, 2003 3:06 PM
To: Harrell, Roger; '[EMAIL PROTECTED]'
Subject: RE: Install question Linux RPMs


You're going to have to see where the files were installed.  It was easier
with Red Hat 7.3 because the Kpackage program would tell you where the files
were installed.  I don't know how you find that info out with Red Hat 8.0 or
9.0.

-Original Message-
From: Harrell, Roger [mailto:[EMAIL PROTECTED] 
Sent: Thursday, October 02, 2003 4:56 PM
To: '[EMAIL PROTECTED]'
Subject: Install question Linux RPMs


I am installing MySQL 4.0.15 on Red Hat Linux 8. I ran the rpms for server
and client. It installed and started mysqld but none of the command set
seems to be on the system anywhere. No mysql, mysqladmin, etc. The docs
don't give much help after running the rpms.  After secton 2.1.2 it refers
to section 2.4 Post-installation Setup and Testing. This section does not
talk about steps after running rpm -i on the server and client package. I
can't figure out what occurred. Has anyone experienced this before, or have
ideas on how to troubleshoot? 

Thanks,
Roger

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

thread problem

2003-10-02 Thread Mikel - 
Hi list,

I have the following problem and here is the message that displays the 
console when I try to connect

Warning: Can't create a new thread (errno 11). If you are not out of 
available memory, you can consult the manual for a possible OS-dependent

Greetings

Mikel

_
Charla con tus amigos en línea mediante MSN Messenger:  
http://messenger.microsoft.com/es

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: PHP/MySQL/MyODBC drivers

2003-10-02 Thread Paul DuBois 
At 16:43 -0400 10/1/03, [EMAIL PROTECTED] wrote:
I need to know the syntax to link two tables together that are in two
seperate DB's using the ODBC drivers in PHP.  Can anyone help with an
example?
Depends on what you mean by link two tables together.  if you're trying
to perform a join, you can simply refer to the tables using db_name.tbl_name
syntax.
You can read about name syntax in the MySQL Reference Manual:

http://www.mysql.com/doc/en/Legal_names.html

--
Paul DuBois, Senior Technical Writer
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
Are you MySQL certified?  http://www.mysql.com/certification/

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Currency

2003-10-02 Thread Paul DuBois 
At 17:32 -0300 10/2/03, Fabio Bernardo wrote:
I´m having some problems with currency´s fields. Actually I dont know what
mysql´s field typeI have to choose..^
I wanna input this value: U$32.00 but, when I write the query: Select
'field' from table it returns..
'32' and not 32.00.
Which select statement do i have to write?
Your requirements are unclear.

Are you saying you want to input the value U$32.00 and have it come
back out as 32.00?
If that's what you mean, it seems a little odd to me, frankly.

--
Paul DuBois, Senior Technical Writer
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
Are you MySQL certified?  http://www.mysql.com/certification/

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

ran out of space for bin logs

2003-10-02 Thread Christopher L. Everett 
Aparrently my binlogs grew and grew and ate up all the space on their 
partition.

At this point, I see this on the master

mysql show master status;
Empty set (0.00 sec)
and this on the slave:

mysql show slave status\G
*** 1. row ***
 Master_Host: master-db
 Master_User: repl
 Master_Port: 3306
   Connect_retry: 60
 Master_Log_File: carbon-bin.09
 Read_Master_Log_Pos: 201392116
  Relay_Log_File: silicon-relay-bin.07
   Relay_Log_Pos: 4
Relay_Master_Log_File: carbon-bin.09
Slave_IO_Running: No
   Slave_SQL_Running: Yes
 Replicate_do_db:
 Replicate_ignore_db:
  Last_errno: 0
  Last_error:
Skip_counter: 0
 Exec_master_log_pos: 201392116
 Relay_log_space: 4
which I think means my replications long past the point of retrieval.

I'm pretty well reconciled to taking my system out of production, 
copying all the
databases from the master to the slave, dropping the binlogs and restarting
the replication.

I have 2 questions:

1. Can I safely delete the binlogs and clear the binlog index by hand?
2. How can I regulate the size of the binlogs to something manageable?
TIA for your help.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Install question Linux RPMs

2003-10-02 Thread Paul DuBois 
At 17:06 -0500 10/2/03, Christensen, Dave wrote:
You're going to have to see where the files were installed.  It was easier
with Red Hat 7.3 because the Kpackage program would tell you where the files
were installed.  I don't know how you find that info out with Red Hat 8.0 or
9.0.
You can use rpm -qpl rpm-file to find out where the files in the RPM
will be installed.
-Original Message-
From: Harrell, Roger [mailto:[EMAIL PROTECTED]
Sent: Thursday, October 02, 2003 4:56 PM
To: '[EMAIL PROTECTED]'
Subject: Install question Linux RPMs
I am installing MySQL 4.0.15 on Red Hat Linux 8. I ran the rpms for server
and client. It installed and started mysqld but none of the command set
seems to be on the system anywhere. No mysql, mysqladmin, etc. The docs
don't give much help after running the rpms.  After secton 2.1.2 it refers
to section 2.4 Post-installation Setup and Testing. This section does not
talk about steps after running rpm -i on the server and client package. I
can't figure out what occurred. Has anyone experienced this before, or have
ideas on how to troubleshoot?
Thanks,
Roger


--
Paul DuBois, Senior Technical Writer
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
Are you MySQL certified?  http://www.mysql.com/certification/

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

grant by option on querys

2003-10-02 Thread Brad Teale 
Hi All,

I asked earlier about a query being slow, possibly due to MySQL 'Using
temporary; Using filesort' when processing the query.  I have done some
testing, and it appears that no matter what data set is used, MySQL always
performs a select with a 'grant by' clause using the temporary and filesort
methods.  The only time I could force MySQL into not using these methods
happened when a did a goup by on a column that absolutely contained the same
information.  Is this the standard behavior?  Is there anyway to get around
this?  Is there a MySQL variable I can tweak?


My example:
mysql desc foo;
+--+--+--+-++---+
| Field| Type | Null | Key | Default| Extra |
+--+--+--+-++---+
| stn  | char(4)  | YES  | MUL | NULL   |   |
| modelhr  | int(2)   | YES  | | NULL   |   |
| f_temp   | decimal(6,2) | YES  | | NULL   |   |
| m_temp   | decimal(6,2) | YES  | | NULL   |   |
| yearmoda | date |  | | -00-00 |   |
+--+--+--+-++---+
5 rows in set (0.00 sec)

mysql select * from foo;
+--+-++++
| stn  | modelhr | f_temp | m_temp | yearmoda   |
+--+-++++
| KHOU |   6 |  90.00 |  89.60 | 2003-06-01 |
| KHOU |   6 |  76.00 |  71.60 | 2003-06-01 |
| KHOU |   6 |  75.00 |  73.40 | 2003-06-01 |
| KHOU |   6 |  88.00 |  87.80 | 2003-06-01 |
+--+-++++
4 rows in set (0.01 sec)

mysql explain select stn, modelhr, m_temp from foo group by modelhr;
+---+--+---+--+-+--+--+-
+
| table | type | possible_keys | key  | key_len | ref  | rows | Extra
|
+---+--+---+--+-+--+--+-
+
| foo   | ALL  | NULL  | NULL |NULL | NULL |  120 | Using
temporary; Using filesort |
+---+--+---+--+-+--+--+-
+
1 row in set (0.01 sec)

mysql explain select stn, modelhr, m_temp from foo where stn='KHOU' and
yearmoda = '2003-06-02' group by modelhr;
+---+--+---+--+-+--+--+-
-+
| table | type | possible_keys | key  | key_len | ref  | rows | Extra
|
+---+--+---+--+-+--+--+-
-+
| foo   | ALL  | stn,stn_2 | NULL |NULL | NULL |   90 | Using where;
Using temporary; Using filesort |
+---+--+---+--+-+--+--+-
-+
1 row in set (0.05 sec)


Thanks,
Brad

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

ODBC Connect have problems .....

2003-10-02 Thread 
Hi mysql : 
   Client O.S. is Windows 2000 Professional 
   MyODBC Version is 2_50.39 for windows
   Mysql Database Engine version 3.23.52
   Mysql server O.S. Redhat 8
   Error Message :Host 'pc100' is not allowed to connect to mysql server (#1130)
Could you help me fix this problems .

thank you 
---
Leader University 
Wenjung Wang
Tel:06-2559000 ext. 51100
E-mail:[EMAIL PROTECTED]
---

Re: ODBC Connect have problems .....

2003-10-02 Thread Paul DuBois 
At 9:33 +0800 10/3/03, â§ïíâ wrote:
Hi mysql :
   Client O.S. is Windows 2000 Professional
   MyODBC Version is 2_50.39 for windows
   Mysql Database Engine version 3.23.52
   Mysql server O.S. Redhat 8
   Error Message :Host 'pc100' is not allowed to 
connect to mysql server (#1130)
Could you help me fix this problems .
This means there is no record at all listed in the mysql.user table
that allows anyone from 'pc100' to connect to the server.
You'll need to create an account that allows you to connect from 'pc100'.

http://www.mysql.com/doc/en/User_Account_Management.html

thank you
---
Leader University
Wenjung Wang
Tel:06-2559000 ext. 51100
E-mail:[EMAIL PROTECTED]
---


--
Paul DuBois, Senior Technical Writer
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
Are you MySQL certified?  http://www.mysql.com/certification/

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: grant by option on querys

2003-10-02 Thread Paul DuBois 
At 21:07 -0500 10/2/03, Brad Teale wrote:
Hi All,

I asked earlier about a query being slow, possibly due to MySQL 'Using
temporary; Using filesort' when processing the query.  I have done some
testing, and it appears that no matter what data set is used, MySQL always
performs a select with a 'grant by' clause using the temporary and filesort
methods.  The only time I could force MySQL into not using these methods
happened when a did a goup by on a column that absolutely contained the same
information.  Is this the standard behavior?  Is there anyway to get around
this?  Is there a MySQL variable I can tweak?
Try adding ORDER BY NULL to suppress the implicit sorting that GROUP BY does
in MySQL.
Of course, that means your results won't be sorted.  If you really want
them sorted, you might try indexing modelhr, the column you're grouping
by.  You might try indexing it anyway, in fact.  That may give you quicker
grouping.


My example:
mysql desc foo;
+--+--+--+-++---+
| Field| Type | Null | Key | Default| Extra |
+--+--+--+-++---+
| stn  | char(4)  | YES  | MUL | NULL   |   |
| modelhr  | int(2)   | YES  | | NULL   |   |
| f_temp   | decimal(6,2) | YES  | | NULL   |   |
| m_temp   | decimal(6,2) | YES  | | NULL   |   |
| yearmoda | date |  | | -00-00 |   |
+--+--+--+-++---+
5 rows in set (0.00 sec)
mysql select * from foo;
+--+-++++
| stn  | modelhr | f_temp | m_temp | yearmoda   |
+--+-++++
| KHOU |   6 |  90.00 |  89.60 | 2003-06-01 |
| KHOU |   6 |  76.00 |  71.60 | 2003-06-01 |
| KHOU |   6 |  75.00 |  73.40 | 2003-06-01 |
| KHOU |   6 |  88.00 |  87.80 | 2003-06-01 |
+--+-++++
4 rows in set (0.01 sec)
mysql explain select stn, modelhr, m_temp from foo group by modelhr;
+---+--+---+--+-+--+--+-
+
| table | type | possible_keys | key  | key_len | ref  | rows | Extra
|
+---+--+---+--+-+--+--+-
+
| foo   | ALL  | NULL  | NULL |NULL | NULL |  120 | Using
temporary; Using filesort |
+---+--+---+--+-+--+--+-
+
1 row in set (0.01 sec)
mysql explain select stn, modelhr, m_temp from foo where stn='KHOU' and
yearmoda = '2003-06-02' group by modelhr;
+---+--+---+--+-+--+--+-
-+
| table | type | possible_keys | key  | key_len | ref  | rows | Extra
|
+---+--+---+--+-+--+--+-
-+
| foo   | ALL  | stn,stn_2 | NULL |NULL | NULL |   90 | Using where;
Using temporary; Using filesort |
+---+--+---+--+-+--+--+-
-+
1 row in set (0.05 sec)


--
Paul DuBois, Senior Technical Writer
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
Are you MySQL certified?  http://www.mysql.com/certification/

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Currency

2003-10-02 Thread Scott Brown 
I am guessing that you are trying to find a MySQL data type of Currency, 
which does not exist.

I generally use a column that is DECIMAL(150,2) for US currency.

--Scott



At 01:32 PM 10/2/2003, Fabio Bernardo wrote:
I´m having some problems with currency´s fields. Actually I dont know what
mysql´s field typeI have to choose..^
I wanna input this value: U$32.00 but, when I write the query: Select
'field' from table it returns..
'32' and not 32.00.
Which select statement do i have to write?

thanks


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]