date:20040401

query-cavhe questions revisited

2004-04-01 Thread Mark

I recently just upgraded to MySQL 4.0.18. I still have a few questions about
the new query-cache. Since I am new to the query-cache, I really hope
someone has some answers.

The documentation says:

"The FLUSH TABLES statement also flushes the query cache."

What does that mean exactly? Does it mean FLUSH TABLES, in effect, is equal
to RESET QUERY CACHE, in that it purges the cache? Or does it mean FLUSH
TABLES just defrags the query-cache, and has the effect of FLUSH QUERY
CACHE? I would also be interesting to know whether defragging the
query-cache
takes any disk I/O.

Also, I initially set the query_cache_size to 128M. That is a bit excessive,
perhaps. Is there a known, say, 'key_buffer to query_cache_size' ratio?

A last question. :) I am correct in my assumption that, with the
query_cache and all, I can reduce key_buffer_size? It is now set to 64M
(against  a 128M query_cache_size). My guess is that I will need less of
key_buffer_size space. Right?

Thanks,

- Mark



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

Re: Re: Message

2004-04-01 Thread pbarakos

Here is the file.

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

Re: MySQL upgrade tips

2004-04-01 Thread Egor Egorov

Mike Blezien <[EMAIL PROTECTED]> wrote:
> Hello,
> 
> we are currently running MySQL version 3.23.58, and plan to upgrade to 4.0.18 
> via the RPM's on a RH/Linux system,... can we retain all the user privileges and 
> databases without loosing any data and our user privileges after the upgrade... 
> we do have backups of all the databases.
> 
> Is there any major changes we need to consider after upgrading ??

In 4.0.2 were added new privilges. So you should run mysql_fix_privilege_tables script 
to upgrade privilege tables.

Read also the following section of the manual. You can find there other changes in 4.0 
and some tips about upgrade from 3.23 to 4.0:
http://www.mysql.com/doc/en/Upgrading-from-3.23.html



-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Egor Egorov
 / /|_/ / // /\ \/ /_/ / /__   [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]

Re: What Is LIMIT? and How to Use LIMIT?

2004-04-01 Thread Johan Hook

Hi,
I guess this is for a java.sql.PreparedStatement as the '?'
seem to indicate that.
LIMIT limits ;-) the number of rows retrieved,
i.e. LIMIT 10, 20 gives you 20 rows starting at row 20 in the
resultset and LIMIT 10 gives you the first 10 rows.
See: http://www.mysql.com/doc/en/SELECT.html

/Johan

Caroline Jen wrote:

I saw somebody has MySQL statement this way:

StringBuffer sql = new StringBuffer(512);
sql.append("SELECT ThreadID, ForumID,
MemberName, LastPostMemberName, ThreadTopic,
ThreadBody");
sql.append(" FROM " + TABLE_NAME);
sql.append(" ORDER BY " + sort + " " + order);
sql.append(" LIMIT ?, ?");
What is LIMIT?  What those question marks stand for?

__
Do you Yahoo!?
Yahoo! Small Business $15K Web Design Giveaway 
http://promotions.yahoo.com/design_giveaway/



--
Johan Höök, Pythagoras Engineering Group
- MailTo:[EMAIL PROTECTED]
- http://www.pythagoras.se
Pythagoras AB, Stormbyv. 2-4, SE-163 55 SPÅNGA, Sweden
Phone: +46 8 760 00 10 Fax: +46 8 761 22 77


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

What Is LIMIT? and How to Use LIMIT?

2004-04-01 Thread Caroline Jen

I saw somebody has MySQL statement this way:

StringBuffer sql = new StringBuffer(512);
sql.append("SELECT ThreadID, ForumID,
MemberName, LastPostMemberName, ThreadTopic,
ThreadBody");
sql.append(" FROM " + TABLE_NAME);
sql.append(" ORDER BY " + sort + " " + order);
sql.append(" LIMIT ?, ?");

What is LIMIT?  What those question marks stand for?

__
Do you Yahoo!?
Yahoo! Small Business $15K Web Design Giveaway 
http://promotions.yahoo.com/design_giveaway/

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

Re: MySql SQL Help

2004-04-01 Thread Rhino

I can't suggest a complete solution but I think the answer is going to start
with the week() function. See
http://www.mysql.com/doc/en/Date_and_time_functions.html#IDX1403.

You're lucky; figuring out which week a date belongs to was something I had
to do in DB2 a few years back. The user was on an old version of DB2 that
didn't have the week() function yet and I had to do a *lot* of work to come
up with a way to calculate weeks without it. That solution involved writing
scripts to automatically build tables showing the week start and end dates
so that he could join to them just to get week numbers.

In your case, you can probably get what you need by simply selecting all the
rows you want to report on, then adding a GROUP BY for the week number. That
should work for the second of your reports, the one that shows actual totals
for the week in question.

I'm not so sure how to do cumulative totals in MySQL. It's easy enough in a
spreadsheet so you should be able to think of some way to do it in MySQL.

Like I said, I don't know the whole answer but I think you definitely want
to start with the week() function in a GROUP BY. Be sure you look at the
different modes supported by the week() function and make sure you pick the
one that matches the rules in your environment.

Rhino

- Original Message - 
From: "Ramesh" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Thursday, April 01, 2004 11:12 PM
Subject: MySql SQL Help


>
> Hi,
>
> Need a SQL/Design help.
>
> I have a table as follows:
>
> ORDER_HISTORY
> --
> ORDER_ID | ORDER_NAME | ORDER_AMOUNT | ORDER_DATE
> --
> 10   | Order 1| 20.00| 10-Jan-2003
> 11   | Order 2| 200.00   | 15-Jan-2003
> 12   | Order 3| 250.00   | 25-Jan-2003
> 13   | Order 4| 260.00   | 30-Mar-2003
> 14   | Order 5| 320.00   | 10-Jan-2004
>
> I want to write a SQL which generates a weekly report from the
ORDER_HISTORY table and shows it as:
>
> WEEKLY_DATE  |  CUMULATIVE AMOUNT
> --
> 10-JAN-2003  |  20
> 17-JAN-2003  |  220
> 24-JAN-2003  |  220
> 31-JAN-2003  |  490
> 07-FEB-2003  |  490
>
> Similarly another table without a Cumulative:
>
> WEEKLY_DATE  |  AMOUNT
> --
> 10-JAN-2003  |  20
> 17-JAN-2003  |  200
> 24-JAN-2003  |  0
> 31-JAN-2003  |  250
> 07-FEB-2003  |  0
>
> My thought process currently is to first get all the date intervals within
the min and max dates of ORDER_DATE with a 1 WEEK addition to each min date
and then for each of these intervals, I can then get the AMOUNT and add it
to up to the previous amount if cumulative or just leave it if not
cumulative.
>
> I am trying to find an elegant solution to implement this and not have too
many trips to the database, i.e once to get each interval of date between
the min and max and then for each interval go to the db again to get the
amount.
>
> Is there any function which will return all the starting dates of each
week between two dates?
>
> Appreciate any help or links to prior solutions or any book on this topic.
>
> thanks
>
>
>
> ___
> Join Excite! - http://www.excite.com
> The most personalized portal on the Web!
>
> -- 
> 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]

Re: MYSQLOUTPUT

2004-04-01 Thread Rhino

Can MySQL actually produce result sets like the ones Seena wants? In DB2,
which I know a lot better than MySQL, DB2 doesn't produce such a result set
by itself; you use a program like QMF to suppress the duplicate information
on the rows and replace them with blanks.

Is there an equivalent program that works with MySQL? Or can MySQL do this
by itself? I'm curious to hear the answer to this

Rhino

- Original Message - 
From: "Seena Blace" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Thursday, April 01, 2004 10:50 PM
Subject: Re: MYSQLOUTPUT


> Victor
> I want output like
> noname amt
> 1     10
>  20
>
> instead of below
>
> noname amt
> 1     10
> 1  20
> thx
>
>
>
> Victor Pendleton <[EMAIL PROTECTED]> wrote:
> Are you just wanting to remove the header information? If so, then try
> mysql --skip-column-names
>
> On Thu, 01 Apr 2004, Seena Blace wrote:
> >
> > Hi,
> >
> > I'm getting output like
> >
> > select a.hostid,a.hdesc,b.ipadd1,c.make,c.model,d.name,d.version from
> > host a,ipdet_temp1 b,makemodel c,software d,hsdet e
> > -> where a.hostid=b.hostname2 and a.makemodel=c.makemodelid and
> > a.hostid=e.host_id and e.software_id=d.softwareid and
> > a.hostid='dstm-db01' and b.vflag='N' order by a.hostid;
> >
> > hostid | hdesc | ipadd1 | make
> > | model | name | version |
> > +---++---+--
> > -+---+---+-+
> > | box1 | Server1(DB01) | xxx.xxx..xxx | Sun Micro System Inc. |
> > E420R | Sun OS | 8 |
> > | box1 | Server1(DB01) | xxx.xxx..xxx | Sun Micro System Inc. |
> > E420R | Oracle8i | 8171 |
> > | box1 | Server1(DB01) | xxx.xxx..xxx| Sun Micro System Inc. |
> > E420R | DataGuard | 817 |
> > +---++---+--
> > -+---+---+-
> >
> >
> > I want the output like
> > +---++---+--
> > -+---+---+-+
> > | box1 | Server1(DB01) | xxx.xxx..xxx | Sun Micro System Inc. |
> > E420R |Sun OS |8
> >
> > |Oracle8i |8171
> >
> > DataGuard | 817 |
> > +---++---+--
> > -+---+---+-
> >
> >
> > How to do this?
> > thx
> >
> >
> > -
> > Do you Yahoo!?
> > Yahoo! Small Business $15K Web Design Giveaway - Enter today
> >
>
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
>
>
> -
> Do you Yahoo!?
> Yahoo! Small Business $15K Web Design Giveaway - Enter today


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

MySql SQL Help

2004-04-01 Thread Ramesh


Hi,



Need a SQL/Design help.



I have a table as follows:



ORDER_HISTORY

--

ORDER_ID | ORDER_NAME | ORDER_AMOUNT | ORDER_DATE

--

10   | Order 1| 20.00| 10-Jan-2003

11   | Order 2| 200.00   | 15-Jan-2003

12   | Order 3| 250.00   | 25-Jan-2003

13   | Order 4| 260.00   | 30-Mar-2003

14   | Order 5| 320.00   | 10-Jan-2004



I want to write a SQL which generates a weekly report from the ORDER_HISTORY table and 
shows it as:



WEEKLY_DATE  |  CUMULATIVE AMOUNT

--

10-JAN-2003  |  20

17-JAN-2003  |  220

24-JAN-2003  |  220

31-JAN-2003  |  490

07-FEB-2003  |  490



Similarly another table without a Cumulative:



WEEKLY_DATE  |  AMOUNT

--

10-JAN-2003  |  20

17-JAN-2003  |  200

24-JAN-2003  |  0

31-JAN-2003  |  250

07-FEB-2003  |  0



My thought process currently is to first get all the date intervals within the min and 
max dates of ORDER_DATE with a 1 WEEK addition to each min date and then for each of 
these intervals, I can then get the AMOUNT and add it to up to the previous amount if 
cumulative or just leave it if not cumulative.



I am trying to find an elegant solution to implement this and not have too many trips 
to the database, i.e once to get each interval of date between the min and max and 
then for each interval go to the db again to get the amount.



Is there any function which will return all the starting dates of each week between 
two dates?



Appreciate any help or links to prior solutions or any book on this topic.



thanks





___
Join Excite! - http://www.excite.com
The most personalized portal on the Web!

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

Re: MYSQLOUTPUT

2004-04-01 Thread Seena Blace

Victor
I want output like 
noname amt
1     10
 20
 
instead of below

noname amt
1     10
1  20
thx
 


Victor Pendleton <[EMAIL PROTECTED]> wrote:
Are you just wanting to remove the header information? If so, then try
mysql --skip-column-names

On Thu, 01 Apr 2004, Seena Blace wrote:
> 
> Hi,
> 
> I'm getting output like 
> 
> select a.hostid,a.hdesc,b.ipadd1,c.make,c.model,d.name,d.version from
> host a,ipdet_temp1 b,makemodel c,software d,hsdet e
> -> where a.hostid=b.hostname2 and a.makemodel=c.makemodelid and
> a.hostid=e.host_id and e.software_id=d.softwareid and
> a.hostid='dstm-db01' and b.vflag='N' order by a.hostid;
> 
> hostid | hdesc | ipadd1 | make
> | model | name | version |
> +---++---+--
> -+---+---+-+
> | box1 | Server1(DB01) | xxx.xxx..xxx | Sun Micro System Inc. |
> E420R | Sun OS | 8 |
> | box1 | Server1(DB01) | xxx.xxx..xxx | Sun Micro System Inc. |
> E420R | Oracle8i | 8171 |
> | box1 | Server1(DB01) | xxx.xxx..xxx| Sun Micro System Inc. |
> E420R | DataGuard | 817 |
> +---++---+--
> -+---+---+-
> 
> 
> I want the output like 
> +---++---+--
> -+---+---+-+
> | box1 | Server1(DB01) | xxx.xxx..xxx | Sun Micro System Inc. |
> E420R |Sun OS |8
> 
> |Oracle8i |8171
> 
> DataGuard | 817 |
> +---++---+--
> -+---+---+-
> 
> 
> How to do this?
> thx
> 
> 
> -
> Do you Yahoo!?
> Yahoo! Small Business $15K Web Design Giveaway - Enter today
> 

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


-
Do you Yahoo!?
Yahoo! Small Business $15K Web Design Giveaway - Enter today

MySQL upgrade tips

2004-04-01 Thread Mike Blezien

Hello,

we are currently running MySQL version 3.23.58, and plan to upgrade to 4.0.18 
via the RPM's on a RH/Linux system,... can we retain all the user privileges and 
databases without loosing any data and our user privileges after the upgrade... 
we do have backups of all the databases.

Is there any major changes we need to consider after upgrading ??

TIA
--
MikeBlezien
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Thunder Rain Internet Publishing
Providing Internet Solutions that work!
http://www.thunder-rain.com
Quality Web Hosting
http://www.justlightening.net
MSN: [EMAIL PROTECTED]
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

5.0 InnoDB problem - ADD CONSTRAINT

2004-04-01 Thread Matt Chatterley

Hi folks. I have a problem creating a foreign key constraint which I just
don't seem to be able to figure out.

There are three tables, for which the 'show create' output is given below:

CREATE TABLE `users` (
  `UserID` int(11) unsigned NOT NULL auto_increment,
  `ContactID` int(10) unsigned NOT NULL default '0',
  `UserName` varchar(25) NOT NULL default '',
  `Password` varchar(32) NOT NULL default '',
  `LoggedOnAt` datetime default '-00-00 00:00:00',
  `Cookie` varchar(100) NOT NULL default '',
  PRIMARY KEY  (`UserID`),
  UNIQUE KEY `UserName` (`UserName`),
  KEY `ContactID` (`ContactID`),
  KEY `Cookie` (`Cookie`),
  CONSTRAINT `0_34` FOREIGN KEY (`ContactID`) REFERENCES `contact`
(`ContactID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

CREATE TABLE `usergroups` (
  `UserGroupID` int(10) unsigned NOT NULL auto_increment,
  `Code` varchar(20) NOT NULL default '',
  `Description` varchar(100) NOT NULL default '',
  PRIMARY KEY  (`UserGroupID`),
  KEY `CodeLookup` (`Code`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

CREATE TABLE `user_usergroups` (
  `UserGroupID` int(11) unsigned NOT NULL default '0',
  `UserID` int(11) unsigned NOT NULL default '0',
  PRIMARY KEY  (`UserID`,`UserGroupID`),
  CONSTRAINT `0_75` FOREIGN KEY (`UserID`) REFERENCES `users` (`UserID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1


As you can see, there is one FK on user_usergroups, which references the
users table (UserID). However, when I do this, to create a similar
constraint on UserGroupID to the usergroups table:

ALTER TABLE user_usergroups ADD CONSTRAINT FOREIGN KEY
`usergroupid_ref_usergroups`  (UserGroupID) REFERENCES usergroups
(UserGroupID);

I get this error:

[Dorfl] ERROR 1005: Can't create table '.\space\#sql-340_b.frm' (errno: 150)

The online documentation indicates that this is because my FK constraint is
'badly formed' (I looked up innodb error codes 1005 and 150 in the manual).

I have tried recreating the usergroups table with usergroupid as an int(11)
(I am unsure as to why it is length 10, rather than 11, to be honest - I
created the tables via MySQLCC, and other similar columns are length 11),
but this makes no difference. Both columns are unsigned and NOT NULL, and
although the documentation states that both parent and child columns must be
indexed - they are, because they are both a part (or the whole) of the
primary keys.

It cannot be because user_usergroups.UserGroupID is part of a combined
primary key - because UserID is too! This leaves the only reason I can
envisage as: It is because UserGroupID is not the FIRST column referenced in
a combined Primary Key - meaning I would have to create a secondary index on
it. Is this the case, or have I missed something obvious?


Before I forget, I am using (please forgive me), 5.0a-alpha on Windows 2K.


Thanks,


Matt



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

retrieve ignored records from LOAD DATA INFILE IGNORE

2004-04-01 Thread Will Lowe

Is there anyway to get mySQL to generate a warning or other info when
it ignores a row via LOAD DATA INFILE IGNORE?  I'm happy having the
duplicates ignored but ideally would like to log which records were
dupes in a place I can find them again.

-- 
thanks,

Will

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

FileMaker Pro compared to MySQL

2004-04-01 Thread Justin Tulloss

I am currently using FileMaker Pro for a relatively large database and I am
wondering how to speed it up. Would MySQL speed things up? Is FileMaker in
some way inferior to MySQL? If so, how? Would it be easy to use a FileMaker
plugin to access the MySQL database, or should I write a PHP front?

Thanks for you help,

Justin Tulloss
Network Administrator
InterChez Logistics Systems, Inc.

Re: [Fwd: Re: compiling with icc]

2004-04-01 Thread Yonah Russ

sorry- typo- the gcc binary was 4.0.18
yonah
Yonah Russ wrote:

Hi all- Here is more info on compiling mysql with icc 8 from the mysql 
packagers list-
As for my attempts, I compiled successfully a couple times and the 
first benchmark I did was faster than the gcc compiled server by about 
10%-

unfortunately, I can't say if this is a conculsive test since the gcc 
binary was 3.23 and the icc binary 4.1.1 - I only managed one 
benchmark b/c something has broken the benchmark suite and I haven't 
had time to check into it...

I'll try the following and get back to you- hopefully with good news.
yonah
 Original Message 
Subject: Re: compiling with icc
Date: Thu, 1 Apr 2004 22:23:02 +0200 (CEST)
From: Lenz Grimmer <[EMAIL PROTECTED]>
To: Yonah Russ <[EMAIL PROTECTED]>
CC: [EMAIL PROTECTED]
References: <[EMAIL PROTECTED]>


-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Hi Yonah,

sorry for the late reply. I have now been finally able to install icc 
8.0 on one our build hosts (Running SuSE Linux 8.2) and made some tests.

On Wed, 24 Mar 2004, Yonah Russ wrote:

I'm trying to compile mysql 4.0.18 with icc 8.0 on RH AS 3u1. I'm
including my last configuration for compiling and how it died- I've
tried about a million other ways- this seems to come the closest to
compiling normally but dies with an internal error(looks like intel's
fault not yours).
  Does anyone have a configuration where this compiles? What did I do 
wrong?

fyi- there is a problem with the configure/libtool script when you don't
compile -static and you use ipo... the compilation runs icc -shared to
link the libraries but it needs the -ipo option as well.
thanks in advance for any help!
yonah
here the environment variables:
AR=xiar
CC=icc
CFLAGS='-O3 -ipo -static -axWN -march=pentium4 -mcpu=pentium4'
CXX=icc
CXXFLAGS='-O3 -ipo -static -axWN -march=pentium4 -mcpu=pentium4'
LANG=C
LD=xild
PATH=/usr/local/intel/bin:/usr/kerberos/sbin:/usr/kerberos/bin:/bin:/sbin:/usr/bin:/usr/sbin:/usr/local/bin:/usr/local/sbin:/usr/bin/X11:/usr/X11R6/bin:/root/bin:/usr/local/mysql/bin 

this is the configure line:

./configure --prefix=/usr/local/mysql --enable-thread-safe-client 
--without-innodb --without-debug --enable-assembler 
--with-named-curses-libs=/usr/lib/libncurses.so.5.3

it dies with this error:

IPO Warning: unresolved : rl_attempted_completion_function
Referenced in ipo_icc5X7oE4.o
IPO Warning: unresolved : rl_completion_entry_function
Referenced in ipo_icc5X7oE4.o
IPO Warning: unresolved : rl_readline_name
Referenced in ipo_icc5X7oE4.o
IPO Warning: unresolved : popen
Referenced in ipo_icc5X7oE4.o
IPO Warning: unresolved : pclose
Referenced in ipo_icc5X7oE4.o
IPO Warning: unresolved : readline
Referenced in ipo_icc5X7oE4.o
IPO Warning: unresolved : add_history
Referenced in ipo_icc5X7oE4.o
IPO Warning: unresolved : completion_matches
Referenced in ipo_icc5X7oE4.o
IPO Warning: unresolved : write_history
Referenced in ipo_icc5X7oE4.o
IPO Warning: unresolved : read_history
Referenced in ipo_icc5X7oE4.o
IPO Warning: unresolved : __xstat64
Referenced in libmysqlclient.a(my_lib_ipo.o)
IPO Warning: unresolved : __fxstat64
Referenced in libmysqlclient.a(my_lib_ipo.o)
IPO Warning: unresolved : readdir64
Referenced in libmysqlclient.a(my_lib_ipo.o)
IPO Warning: unresolved : fdopen
Referenced in libmysqlclient.a(my_fopen_ipo.o)
IPO Warning: unresolved : fclose
Referenced in libmysqlclient.a(my_fopen_ipo.o)
IPO Warning: unresolved : gethostbyname_r
Referenced in libmysqlclient.a(my_gethostbyname_ipo.o)
IPO Warning: unresolved : __lxstat64
Referenced in libmysqlclient.a(my_symlink_ipo.o)
IPO Warning: unresolved : realpath
Referenced in libmysqlclient.a(my_symlink_ipo.o)
libmysql.c(1808): internal error: 0_1345
icc: error: problem during multi-file optimization compilation (code 4)
make[2]: *** [mysql] Error 4
make[2]: Leaving directory `/moat-files/mysql-4.0.18/client'
make[1]: *** [all-recursive] Error 1
make[1]: Leaving directory `/moat-files/mysql-4.0.18'
make: *** [all] Error 2


That really sounds like an icc bug. Here's the line we currently use 
for our binaries on icc:

CFLAGS="-O3 -unroll2 -ip -mp -no-gcc -restrict" CC=icc CXX=icc
CXXFLAGS="-O3 -unroll2 -ip -mp -no-gcc -restrict" ./configure
- --prefix=/usr/local/mysql --localstatedir=/usr/local/mysql/data
- --libexecdir=/usr/local/mysql/bin --with-extra-charsets=complex
- --enable-thread-safe-client --enable-local-infile --enable-assembler
- --disable-shared --with-client-ldflags=-all-static
- --with-mysqld-ldflags=-all-static --with-readline 
--with-embedded-server
- --with-innodb

You may be able to use higher optimization levels (e.g. by removing 
"-mp", which will however cause some loss in floatingpoint accuracy - 
some of the test suite tests will fail). I assume the "--no-gcc" is 
key here.

Bye,
LenZ
- -- Le

[Fwd: Re: compiling with icc]

2004-04-01 Thread Yonah Russ

Hi all- Here is more info on compiling mysql with icc 8 from the mysql 
packagers list-
As for my attempts, I compiled successfully a couple times and the first 
benchmark I did was faster than the gcc compiled server by about 10%-

unfortunately, I can't say if this is a conculsive test since the gcc 
binary was 3.23 and the icc binary 4.1.1 - I only managed one benchmark 
b/c something has broken the benchmark suite and I haven't had time to 
check into it...

I'll try the following and get back to you- hopefully with good news.
yonah
 Original Message 
Subject:Re: compiling with icc
Date:   Thu, 1 Apr 2004 22:23:02 +0200 (CEST)
From:   Lenz Grimmer <[EMAIL PROTECTED]>
To: Yonah Russ <[EMAIL PROTECTED]>
CC: [EMAIL PROTECTED]
References: <[EMAIL PROTECTED]>


-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Hi Yonah,

sorry for the late reply. I have now been finally able to install icc 8.0 
on one our build hosts (Running SuSE Linux 8.2) and made some tests.

On Wed, 24 Mar 2004, Yonah Russ wrote:

I'm trying to compile mysql 4.0.18 with icc 8.0 on RH AS 3u1. I'm
including my last configuration for compiling and how it died- I've
tried about a million other ways- this seems to come the closest to
compiling normally but dies with an internal error(looks like intel's
fault not yours).
  Does anyone have a configuration where this compiles? What did I do wrong?
fyi- there is a problem with the configure/libtool script when you don't
compile -static and you use ipo... the compilation runs icc -shared to
link the libraries but it needs the -ipo option as well.
thanks in advance for any help!
yonah
here the environment variables:
AR=xiar
CC=icc
CFLAGS='-O3 -ipo -static -axWN -march=pentium4 -mcpu=pentium4'
CXX=icc
CXXFLAGS='-O3 -ipo -static -axWN -march=pentium4 -mcpu=pentium4'
LANG=C
LD=xild
PATH=/usr/local/intel/bin:/usr/kerberos/sbin:/usr/kerberos/bin:/bin:/sbin:/usr/bin:/usr/sbin:/usr/local/bin:/usr/local/sbin:/usr/bin/X11:/usr/X11R6/bin:/root/bin:/usr/local/mysql/bin
this is the configure line:

./configure --prefix=/usr/local/mysql --enable-thread-safe-client 
--without-innodb --without-debug --enable-assembler 
--with-named-curses-libs=/usr/lib/libncurses.so.5.3

it dies with this error:

IPO Warning: unresolved : rl_attempted_completion_function
Referenced in ipo_icc5X7oE4.o
IPO Warning: unresolved : rl_completion_entry_function
Referenced in ipo_icc5X7oE4.o
IPO Warning: unresolved : rl_readline_name
Referenced in ipo_icc5X7oE4.o
IPO Warning: unresolved : popen
Referenced in ipo_icc5X7oE4.o
IPO Warning: unresolved : pclose
Referenced in ipo_icc5X7oE4.o
IPO Warning: unresolved : readline
Referenced in ipo_icc5X7oE4.o
IPO Warning: unresolved : add_history
Referenced in ipo_icc5X7oE4.o
IPO Warning: unresolved : completion_matches
Referenced in ipo_icc5X7oE4.o
IPO Warning: unresolved : write_history
Referenced in ipo_icc5X7oE4.o
IPO Warning: unresolved : read_history
Referenced in ipo_icc5X7oE4.o
IPO Warning: unresolved : __xstat64
Referenced in libmysqlclient.a(my_lib_ipo.o)
IPO Warning: unresolved : __fxstat64
Referenced in libmysqlclient.a(my_lib_ipo.o)
IPO Warning: unresolved : readdir64
Referenced in libmysqlclient.a(my_lib_ipo.o)
IPO Warning: unresolved : fdopen
Referenced in libmysqlclient.a(my_fopen_ipo.o)
IPO Warning: unresolved : fclose
Referenced in libmysqlclient.a(my_fopen_ipo.o)
IPO Warning: unresolved : gethostbyname_r
Referenced in libmysqlclient.a(my_gethostbyname_ipo.o)
IPO Warning: unresolved : __lxstat64
Referenced in libmysqlclient.a(my_symlink_ipo.o)
IPO Warning: unresolved : realpath
Referenced in libmysqlclient.a(my_symlink_ipo.o)
libmysql.c(1808): internal error: 0_1345
icc: error: problem during multi-file optimization compilation (code 4)
make[2]: *** [mysql] Error 4
make[2]: Leaving directory `/moat-files/mysql-4.0.18/client'
make[1]: *** [all-recursive] Error 1
make[1]: Leaving directory `/moat-files/mysql-4.0.18'
make: *** [all] Error 2


That really sounds like an icc bug. Here's the line we currently use for 
our binaries on icc:

CFLAGS="-O3 -unroll2 -ip -mp -no-gcc -restrict" CC=icc CXX=icc
CXXFLAGS="-O3 -unroll2 -ip -mp -no-gcc -restrict" ./configure
- --prefix=/usr/local/mysql --localstatedir=/usr/local/mysql/data
- --libexecdir=/usr/local/mysql/bin --with-extra-charsets=complex
- --enable-thread-safe-client --enable-local-infile --enable-assembler
- --disable-shared --with-client-ldflags=-all-static
- --with-mysqld-ldflags=-all-static --with-readline --with-embedded-server
- --with-innodb
You may be able to use higher optimization levels (e.g. by removing "-mp", 
which will however cause some loss in floatingpoint accuracy - some of the 
test suite tests will fail). I assume the "--no-gcc" is key here.

Bye,
	LenZ
- -- 
Lenz Grimmer <[EMAIL PROTECTED]>
Senior Production Engineer
MySQL GmbH, http:

FreeBSD stacksize problem, possibly?

2004-04-01 Thread Nick Gaugler

I am running mysql-standard-4.0.18-unknown-freebsd4.7-i386-linuxthreads and
I am unable to achieve more than 440 connections.  I receive the common
"Can't create a new thread (errno 35). If you are not out of available
memory, you can consult the manual for a possible OS-dependent bug".  Now, I
have 1.5GB of free memory, so that's not the problem. I've also thought
about the limits issue, I've checked, they are as follows:

 

Resource limits (current):

  cputime  infinity secs

  filesize infinity kb

  datasize  2096128 kb

  stacksize  524288 kb

  coredumpsize infinity kb

  memoryuseinfinity kb

  memorylocked infinity kb

  maxprocesses 5547

  openfiles   11095

  sbsize   infinity bytes

  vmemoryuse   infinity kb

 

 

 

My thread_stack is about 192kb in MySQL, so 192*440 is only 84MB, which
should be under 512MB.  I'm not running out of processes or file handles, or
sockets from sysctl -a.  Does anyone have any other suggestions or things to
double check?

 

Nick

Re: question on autoincrement

2004-04-01 Thread Victor Pendleton

Try this:
When you add an AUTO_INCREMENT column, column values are filled in with sequence 
numbers for you automatically. 
For MyISAM tables, you can set the first sequence number by executing SET
INSERT_ID=value before ALTER TABLE or by using the AUTO_INCREMENT=value table
option. See section 14.5.3.1 SET Syntax.  

>From here: http://www.mysql.com/doc/en/ALTER_TABLE.html

On Thu, 01 Apr 2004, Amit Wadhwa
wrote: >  > Hi All,
> I was wondering if there was a way in mySQL wherein I tell the
> autoincrement spec where to start from?
> Eg. Autoincrement startfrom 1
> And the values go in as: 
> 10001..10002 and so on,.. instead of 1..2..3..
> ..like sequencing in oracle...
> Ò0
> Any help is appreciated,
> Thanks
> Amit Wadhwa
> Data Analyst
> 

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

Re: MYSQLOUTPUT

2004-04-01 Thread Victor Pendleton

Are you just wanting to remove the header information?  If so, then try
mysql --skip-column-names

On Thu, 01 Apr 2004, Seena Blace wrote:
> 
> Hi,
>  
> I'm getting output like 
>  
>  select a.hostid,a.hdesc,b.ipadd1,c.make,c.model,d.name,d.version from
> host a,ipdet_temp1 b,makemodel c,software d,hsdet e
> -> where a.hostid=b.hostname2 and a.makemodel=c.makemodelid and
> a.hostid=e.host_id and e.software_id=d.softwareid and
> a.hostid='dstm-db01' and b.vflag='N' order by a.hostid;
>  
> hostid| hdesc  | ipadd1| make
> | model | name  | version |
> +---++---+--
> -+---+---+-+
> | box1 |  Server1(DB01) | xxx.xxx..xxx | Sun Micro System Inc. |
> E420R | Sun OS| 8   |
> | box1 |  Server1(DB01) | xxx.xxx..xxx | Sun Micro System Inc. |
> E420R | Oracle8i  | 8171|
> | box1 | Server1(DB01) |  xxx.xxx..xxx| Sun Micro System Inc. |
> E420R | DataGuard | 817 |
> +---++---+--
> -+---+---+-
>  
>  
> I want the output like 
> +---++---+--
> -+---+---+-+
> | box1 |  Server1(DB01) | xxx.xxx..xxx | Sun Micro System Inc. |
> E420R |Sun OS |8
>  
> |Oracle8i |8171
>  
> DataGuard | 817 |
> +---++---+--
> -+---+---+-
>  
>  
> How to do this?
> thx
> 
> 
> -
> Do you Yahoo!?
> Yahoo! Small Business $15K Web Design Giveaway - Enter today
> 

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

Re: InnoDB Deadlock cannot find free slot for undo

2004-04-01 Thread Bryan Heitman

No, we did not run out of disk space, we had 102GB free using reiserfs
partition.
We are running all the defaults for transactions, so we have REPEATABLE-READ
as our tx_isolation. We should have allowed enough time for this transaction
to finish, this is in a production environment that has run fine for over a
year now with InnoDB without this problem. We do not commit anything, its
all auto_commit. Something must have put an extreme burden on the box to
cause this undo log to fill up, but what must we increase so that we have a
bigger log?

I'm unsure if the problem really was related to our "undo log" needing to be
bigger, though it may have. What is really weird is that we had ~500 MySQL
processes that were sitting in the Sleep status on MySQL's SHOW PROCESSLIST
for over 2000 seconds, but in SHOW INNODB STATUS they were sitting in the
ACTIVE status. While these were like this, InnoDB would not move it's last
checkpoint time. We had to manually kill each one of the MySQL processess
sitting in the Sleep/ACTIVE status, once we accomplished this, InnoDB
continued to clean up it's group log files, and teh Last CheckPoint caught
up with the sequence number/flushed up to numbers.

Any help or a point in the right direction in this situation would be
helpful

Bryan Heitman
FuseMail
- Original Message - 
From: "Dathan Vance Pattishall" <[EMAIL PROTECTED]>
To: "'Bryan Heitman'" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Thursday, April 01, 2004 12:15 AM
Subject: RE: InnoDB Deadlock cannot find free slot for undo


> Did you run out of disk space? If not what transaction model are you
using?
> Is it repeatable read? If so, are you allowing the transaction to finish
> with a commit? If not your filling your transaction log and that's how
your
> getting this message. Call commit on the session OR SET AUTOCOMMIT=1 on
> connection. Additionally set your innodb_deadlock_timeout var.
>
>
> --
> DVP
>
>
> > -Original Message-
> > From: Bryan Heitman [mailto:[EMAIL PROTECTED]
> > Sent: Wednesday, March 31, 2004 2:53 PM
> > To: [EMAIL PROTECTED]
> > Subject: InnoDB Deadlock cannot find free slot for undo
> >
> > Looking for some help on tuning my InnoDB settings.  I received a InnoDB
> > deadlock.
> >
> > From analyzing show innodb status -->
> > * There were a ton of processes in the active status not doing anything.
> > * The log flushed up to was moving but the last checkpoint at was
> > extremely
> > old.
> >
> > Error in mysql error log scrolling repeatedly:
> > 16:21:37InnoDB: Warning: cannot find a free slot for an undo log. Do you
> > have too many active transactions running concurrently?
> >
> > version | 4.0.17-standard-log
> >
> > current innodb settings
> > set-variable = innodb_buffer_pool_size=768M
> > set-variable = innodb_additional_mem_pool_size=20M
> > set-variable=   innodb_log_file_size=256M
> > set-variable=   innodb_log_files_in_group=3
> >
> > Any help would be appreciated.
> >
> > Best,
> >
> >
> > Bryan Heitman
> > [EMAIL PROTECTED]
> >
> >
> > --
> > 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]
>
>


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

RE: question on autoincrement

2004-04-01 Thread joe collins

similar-ish to setting your sequence in oracle
try this:
The create table statement below will start the auto increment at 1

CREATE TABLE TableX (
  X_primary_key INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  X_col1 VARCHAR(20) NOT NULL,
  X_col2 VARCHAR(40) NOT NULL,
  X_col3 VARCHAR(10) NOT NULL,
  PRIMARY KEY(X_primary_key)
)
AUTO_INCREMENT = 1;

also see:
ALTER TABLE tbl_name AUTO_INCREMENT = 100
will start your records at 100

ALTER TABLE tbl_name AUTO_INCREMENT = 1000
will start your records at 100

I haven't actually used the last bit of code, it is courtsey of :
http://www.mysql.com/doc/en/example-AUTO_INCREMENT.html

this should sort you out..

Regards

Joe


-Original Message-
From: Amit Wadhwa [mailto:[EMAIL PROTECTED]
Sent: 01 April 2004 23:33
To: [EMAIL PROTECTED]
Subject: question on autoincrement


Hi All,
I was wondering if there was a way in mySQL wherein I tell the
autoincrement spec where to start from?
Eg. Autoincrement startfrom 1
And the values go in as:
10001..10002 and so on,.. instead of 1..2..3..
..like sequencing in oracle

Any help is appreciated,
Thanks
Amit Wadhwa
Data Analyst



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

MYSQLOUTPUT

2004-04-01 Thread Seena Blace

Hi,
 
I'm getting output like 
 
 select a.hostid,a.hdesc,b.ipadd1,c.make,c.model,d.name,d.version from host 
a,ipdet_temp1 b,makemodel c,software d,hsdet e
-> where a.hostid=b.hostname2 and a.makemodel=c.makemodelid and a.hostid=e.host_id 
and e.software_id=d.softwareid and a.hostid='dstm-db01' and b.vflag='N' order by 
a.hostid;
 
hostid| hdesc  | ipadd1| make  | model | 
name  | version |
+---++---+---+---+---+-+
| box1 |  Server1(DB01) | xxx.xxx..xxx | Sun Micro System Inc. | E420R | Sun OS
| 8   |
| box1 |  Server1(DB01) | xxx.xxx..xxx | Sun Micro System Inc. | E420R | Oracle8i  
| 8171|
| box1 | Server1(DB01) |  xxx.xxx..xxx| Sun Micro System Inc. | E420R | DataGuard 
| 817 |
+---++---+---+---+---+-
 
 
I want the output like 
+---++---+---+---+---+-+
| box1 |  Server1(DB01) | xxx.xxx..xxx | Sun Micro System Inc. | E420R |Sun OS |8
   
|Oracle8i |8171
   
  DataGuard | 817 |
+---++---+---+---+---+-
 
 
How to do this?
thx


-
Do you Yahoo!?
Yahoo! Small Business $15K Web Design Giveaway - Enter today

question on autoincrement

2004-04-01 Thread Amit Wadhwa

Hi All,
I was wondering if there was a way in mySQL wherein I tell the
autoincrement spec where to start from?
Eg. Autoincrement startfrom 1
And the values go in as: 
10001..10002 and so on,.. instead of 1..2..3..
..like sequencing in oracle
 
Any help is appreciated,
Thanks
Amit Wadhwa
Data Analyst

Re: Handling threads

2004-04-01 Thread Victor Pendleton

When this happens are you able to ping the MySQL server? The literal error
message is "Resource temporarily unavailable"
Have you looked in the hostname.err file to see if anything else is being
logged? 


  On Thu, 01 Apr 2004, Mikel - wrote: > Hi list, I'm having
the following problem when I tried to connect to MySQL  > server through
terminal mysql client it displayed "Can't create thread errno  > (11)" , I
worked around the variables thread_stack and thread_cache_size,  > its initial
value was 64KB for thread_stack and 20 for thread_cache_size; I  > read on
different mysql lists and the official documentation about this  > variables
and I guessed that incrementing the values of these variables  > could solve my
problem, so I incremented the thread_stak to 128KB and the  > thread_cache_size
to 50, after restarting MySQL worked pretty fine but  it  > lasted 20 hours to
displayed me the same error "Can't create thread errno  > (11)" the question
is, do I have to increment those values or I'll have to  > increment other
variables, or if you know any urls that I could use I really  > appreciate.
> 
> Thanks in advanced
> Greetings
> Mikel
> 
> 
> The system features are:
> - MySQL server 3.23.55
> - Linux version 2.4.22
> - Red Hat Linux 7.3 2.96-110 #3 SMP
> - 4GB RAM
> - 4 Xeon processors
> 
> _
> MSN. Más Útil Cada Día  http://www.msn.es/intmap/
> 
> 
> -- 
> 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]

Handling threads

2004-04-01 Thread Mikel -

Hi list, I'm having the following problem when I tried to connect to MySQL 
server through terminal mysql client it displayed "Can't create thread errno 
(11)" , I worked around the variables thread_stack and thread_cache_size, 
its initial value was 64KB for thread_stack and 20 for thread_cache_size; I 
read on different mysql lists and the official documentation about this 
variables and I guessed that incrementing the values of these variables 
could solve my problem, so I incremented the thread_stak to 128KB and the 
thread_cache_size to 50, after restarting MySQL worked pretty fine but  it 
lasted 20 hours to displayed me the same error "Can't create thread errno 
(11)" the question is, do I have to increment those values or I'll have to 
increment other variables, or if you know any urls that I could use I really 
appreciate.

Thanks in advanced
Greetings
Mikel
The system features are:
- MySQL server 3.23.55
- Linux version 2.4.22
- Red Hat Linux 7.3 2.96-110 #3 SMP
- 4GB RAM
- 4 Xeon processors
_
MSN. Más Útil Cada Día  http://www.msn.es/intmap/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Create Table Syntax

2004-04-01 Thread Khashan

 Thanks for the reply. 
It seems like the syntax for create_table/create_definition
 should have 2 part-2 modifiers
1- one part-2 'column' modifiers for the each column.
and 
2- another part-2 table modifiers for the table as a whole ?
 ( at the end of column definitions).
two questions:
 --  Are any of the these modifiers:
 - Primary Key
 - Index
 - KEY 
 - Unique 
 mutually exclusive at the column ot table level? 
   ( INDEX and KEY are the same, so only one is allowed) 
 -- Does it make sense for a user to specify 'Primary Key' as part of
 a column modifiers and then again specify 'Primary Key' as part of 
the table modifiers?
Ahmad Khashan

Rhino <[EMAIL PROTECTED]> wrote:

- Original Message - 
From: "Khashan" 
To: 
Sent: Thursday, April 01, 2004 1:04 PM
Subject: Create Table Syntax


>
> I am looking at the syntax for creating table( section 6.5.3 of the
manual):
>
> CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
[(create_definition,...)] [table_options] [select_statement]
>
>
>
> and:
>
> create_definition:
>
> part-1 (<- inserted by me for reference)
>
> col_name type [NOT NULL | NULL] etc...[reference_definition]
>
> part-2 (<- inserted by me for reference)
>
> | PRIMARY KEY (index_col_name,...)
>
> | KEY [index_name] (index_col_name,...)
>
> | INDEX [index_name] (index_col_name,...)
>
> | UNIQUE [INDEX] [index_name] (index_col_name,...)
>
> | FULLTEXT [INDEX] [index_name] (index_col_name,...)
>
> | [CONSTRAINT symbol] FOREIGN KEY .. [reference_definition]
>
> | CHECK (expr)
>
>
>
> The syntax suggests that for each column, in addition to specifying
part-1, I can specify any or all of the options in part-2 ???.
>
> 1- Is this true.
>
>
Yes.
>
> 2- Can I specify the options in part-2 after I defined all my columns. I
have used things like:
>
> CREATE TABLE animals (
>
> id MEDIUMINT NOT NULL AUTO_INCREMENT,
>
> name CHAR(30) NOT NULL,
>
> PRIMARY KEY (id)
>
> );
>
Yes, this is the way a CREATE TABLE normally looks: after each column
name/datatype/null specification ("part 1" in your terminology) come the
various attributes that further define the column ("part 2" in your
terminology). A given column can have one or more of these attributes.

Rhino

Do you Yahoo!?
Yahoo! Mail - More reliable, more storage, less spam

Renaming an index

2004-04-01 Thread Chad Attermann

Hello,

Through some strange quirk in my MySQL console application (an older version of 
PHPMyAdmin), 4 indexes that I created on different columns of a table all resulted in 
indexes with the same keyname, that of the first column that was indexed.  The field 
of each index is correct, just the keyname is wrong on 3 of them.

  Keyname Unique Field Action 

  BUYAGENTID No BUYAGENTID Drop 
  BUYAGENTID No SELLAGENTID Drop 
  BUYAGENTID No BUYACCOUNTID Drop 
  BUYAGENTID No SELLACCOUNTID Drop 


I noticed that the 3 indexes with the wrong keyname are never used, even with simple 
queries where they definitely should be used.  I need to change the keyname of these 
indexes without having to drop and re-create them as this table is now *very* large 
and it would take hours to reindex 3 columns.  Any assistance in changing the keyname, 
either through common commands or low-level file editing, would be greatly appreciated.

Thanks a lot!

Chad Attermann

Re: group by & order by rand() problem

2004-04-01 Thread Michael Stassen

Right.  You're grouping by user_id and throwing in title, and you're hoping 
to influence which of the titles is chosen to go with user_id, but as title 
is neither part of your group nor part of an aggregate function, its value 
is undefined.  See the manual for an explanation 
.

You could probably accomplish this with a variant of the MAX-CONCAT trick 
. 
Something like:

  SELECT user_id,
 SUBSTRING(MAX(CONCAT(TRUNCATE(RAND(),4),title)),7) AS Title
  FROM banners
  GROUP BY user_id;
Michael

m.pheasant wrote:

Order by is working after the group stage. 
You would need an aggregate function which chooses a random row. 
Some other SQL implementations would not let you select a column that is not
also grouped (eg title) or in an aggregate function as in your select ...
group by ... example.

m


-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
I have this table:

mysql> select * from banners;
++-+---+
| id | user_id | title |
++-+---+
|  1 |   1 | first banner  |
|  2 |   1 | second banner |
|  3 |   2 | third banner  |
|  4 |   2 | forth banner  |
|  5 |   2 | fifth banner  |
++-+---+
I would like to show a random banner for each user,
something like this:
first call
++-+---+
| id | user_id | title |
++-+---+
|  1 |   1 | first banner  |
|  3 |   2 | third banner  |
++-+---+
second call
++-+---+
| id | user_id | title |
++-+---+
|  2 |   1 | second banner |
|  4 |   2 | forth banner  |
++-+---+
etc...

I have tried with following query but the banner
doesn't change while multiple calls:
SELECT * FROM banners GROUP BY user_id ORDER BY RAND();

Can anyone help me?

Thanks in advance,

Alex


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

Fwd: failure notice

2004-04-01 Thread Tina Gross

Hi. This is the qmail-send program at lists.mysql.com.
I'm afraid I wasn't able to deliver your message to the following
addresses.
This is a permanent error; I've given up. Sorry it didn't work out.

<[EMAIL PROTECTED]>:
Sorry, no mailbox here by that name. (#5.1.1)

--- Below this line is a copy of the message.

Return-Path: <[EMAIL PROTECTED]>
Received: (qmail 24700 invoked from network); 31 Mar 2004 20:44:51
-
Received-SPF: pass (lists: local policy designates 131.122.220.10 as
permitted sender)
Received: from splat.usna.edu (HELO splat.usna.edu) (131.122.220.10)
  by lists.mysql.com (qpsmtpd/0.28-dev) with ESMTP; Wed, 31 Mar 2004
22:44:50 +0200
Received: from FSGWHUB.usna.edu (fsgwhub.usna.edu [131.122.220.41])
by splat.usna.edu (8.12.10/8.12.9) with ESMTP id i2VKiW5n021162
for <[EMAIL PROTECTED]>; Wed, 31 Mar
2004 15:44:37 -0500 (EST)
Received: from USNA-MTA by FSGWHUB.usna.edu
with Novell_GroupWise; Wed, 31 Mar 2004 15:44:32 -0500
Message-Id: <[EMAIL PROTECTED]>
X-Mailer: Novell GroupWise Internet Agent 6.5.2 Beta
Date: Wed, 31 Mar 2004 15:44:19 -0500
From: "Tina Gross" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Subject: Having problems installing DBD::mysql
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
X-Scanned-By: MIMEDefang 2.33 (www . roaringpenguin . com /
mimedefang)
X-Virus-Checked: Checked

First I was having problem with the :

Failed: Can't load
'/.cpan/build/DBD-mysql-2.9003/blib/arch/auto/DBD/mysql/mysql.so' for
module DBD::mysql: ld.so.1: /usr/local/bin/perl: fatal: relocation
error: file
/.cpan/build/DBD-mysql-2.9003/blib/arch/auto/DBD/mysql/mysql.so:
symbol
net_buffer_length: referenced symbol not found at
/usr/local/lib/perl5/5.8.0/sun4u-thread-multi-64int/DynaLoader.pm lin
e 229.

I work around that by getting the tarball of mysql and building it
manually.  Now I'm having trouble with make test failing and creating
a
core dump.

This what happens when I type make test

gcc -c 
-I/usr/local/lib/perl5/site_perl/5.8.0/sun4u-thread-multi-64int/auto/DB
I -I/usr/local/mysql/include -I/usr/local/mysql/include/mysql
-D_REENTRANT -fno
-strict-aliasing -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64 -O  
-DVERSION=\"2.
9003\" -DXS_VERSION=\"2.9003\" -fPIC
"-I/usr/local/lib/perl5/5.8.0/sun4u-thread
-multi-64int/CORE"   dbdimp.c
dbdimp.c: In function `mysql_db_FETCH_attrib':
dbdimp.c:1456: warning: cast from pointer to integer of different size
dbdimp.c: In function `mysql_st_FETCH_attrib':
dbdimp.c:2146: warning: cast from pointer to integer of different size
gcc -c 
-I/usr/local/lib/perl5/site_perl/5.8.0/sun4u-thread-multi-64int/auto/DB
I -I/usr/local/mysql/include -I/usr/local/mysql/include/mysql
-D_REENTRANT -fno
-strict-aliasing -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64 -O  
-DVERSION=\"2.
9003\" -DXS_VERSION=\"2.9003\" -fPIC
"-I/usr/local/lib/perl5/5.8.0/sun4u-thread
-multi-64int/CORE"   mysql.c
Running Mkbootstrap for DBD::mysql ()
chmod 644 mysql.bs
rm -f blib/arch/auto/DBD/mysql/mysql.so
LD_RUN_PATH="/usr/local/mysql/lib/mysql:/usr/local/lib:/usr/lib"
/usr/local/bin
/perl myld gcc  -shared -L/usr/local/mysql/lib
-L/usr/local/mysql/lib/mysql -L/
usr/local/lib dbdimp.o mysql.o  -o blib/arch/auto/DBD/mysql/mysql.so  
-L/usr/l
ocal/mysql/lib/mysql -lmysqlclient -lz -lposix4 -lcrypt -lgen -lsocket
-lnsl -l
m
chmod 755 blib/arch/auto/DBD/mysql/mysql.so
cp mysql.bs blib/arch/auto/DBD/mysql/mysql.bs
chmod 644 blib/arch/auto/DBD/mysql/mysql.bs
PERL_DL_NONLAZY=1 /usr/local/bin/perl "-MExtUtils::Command::MM" "-e"
"test_harn
ess(1, 'blib/lib', 'blib/arch')" t/*.t
t/00base...1..5
ok 1
ok 2
ok 3
ok 4
ok 5
ok
t/10dsnlistdubious
Test returned status 0 (wstat 139, 0x8b)
t/20createdrop.dubious
Test returned status 0 (wstat 139, 0x8b)
t/30insertfetchdubious
Test returned status 0 (wstat 139, 0x8b)
t/40bindparam..dubious
Test returned status 0 (wstat 139, 0x8b)
t/40blobs..dubious
Test returned status 0 (wstat 139, 0x8b)
t/40listfields.dubious
Test returned status 0 (wstat 139, 0x8b)
t/40nulls..dubious
Test returned status 0 (wstat 139, 0x8b)
t/40numrowsdubious
Test returned status 0 (wstat 139, 0x8b)
t/50chopblanks.dubious
Test returned status 0 (wstat 139, 0x8b)
t/50commit.dubious
Test returned status 0 (wstat 139, 0x8b)
t/60leaks..1..0 # Skip $ENV{SLOW_TESTS} is not set or
Proc::ProcessTabl
e not installed
skipped
all skipped: $ENV{SLOW_TESTS} is not set or Proc::ProcessTable
not inst
alled
t/ak-dbd...1..90
dubious
Test returned status 0 (wstat 139, 0x8b)
DIED. FAILED tests 1-90
Failed 90/90 tests, 0.00% okay
t/akmisc...Verifying whether fetchrow returns TRUE for
results.
1..351
dubious
Test returned status 0 (wstat 139, 0x8b)
DIED. FAILED tests 1-351
Failed 351/351 tests, 0.00% okay
t/dbdadmin..

AW: DB Connection

2004-04-01 Thread Freddie Sorensen

What is the connection type ? ODBC ? 

> -Ursprüngliche Nachricht-
> Von: Shrikrishna Sathe [mailto:[EMAIL PROTECTED] 
> Gesendet: Mittwoch, 31. März 2004 12:53
> An: [EMAIL PROTECTED]
> Betreff: DB Connection
> 
> The following .asp code sinppet gives error. 
>  
> Dim rs
> Set rs = Server.CreateObject("ADODB.Recordset")
> rs.Open "printjob",objConn, adOpenForwardOnly, 
> adLockOptimistic  , adCmdTable
> rs.AddNew 
> 
>rs("jobid") = Request.Form("jobno")
>rs("closesize") = Request.Form("closize")
>rs("opensize") = Request.Form("opsize")  
>  
>rs.Update
>rs.Close
>Set rs = Nothing
> 
> Is this AddNew call doesn't work in mysql ?
> The Insert into statement works well but it is very 
> cumbersome while adding >25 fields. 
> 
> please guide.
> 
> Thanks & Regards
> Shrikrishna
> 



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

MySQL in vxWorks ?

2004-04-01 Thread Juan Romano

We are considering porting mySQL to vxWorks.  Can anybody offer some
advice or references ? May be somebody has done this already.

The intention is to use a trimmed down version of mySQL with HEAP
tables. Only a very basic set of features would be used.  ODBC is a
requirement.

 

I'd appreciate any words of wisdom on the subject.

Thanks,

 

Juan//

Calculation in a mysql column

2004-04-01 Thread Mike R


I am using mysql 4.0.16.

Is it possible to put a calculation of two other columns as the default
value for a third column?

Like:

time = decimal 3,2
rate = decimal 3,2
totals = default is time * rate

Thanks,

-Mike




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

Re: starting mysql daemon

2004-04-01 Thread Hadi Ariwibowo

- Original Message -
From: "muhammad alqama" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Thursday, April 01, 2004 10:49 PM
Subject: Re: starting mysql daemon


> hi
> the error mentioned in the file is...
> "fatal error : can not open privilege tables can not
> find file : './mysql/host.frm' (errno 13)"
>
> but in the directory /var/lib/mysql/mysql the file
> host.frm exists...(/var/lib/mysql/  being the data
> dir)
>
> what to do now...

Not sure, but as I remember (Slackware), you should make the owner all of
/var/lib/mysql/ as "mysql", try this :
# chown -R mysql:mysql /var/lib/mysql


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

RE: ERROR 1045: Access denied for user: 'root@localhost' (Using password: NO)

2004-04-01 Thread Pahlevanzadeh Mohsen

I didn't found hostname.pid file.
SOS SOS

--- "J.R. Bullington" <[EMAIL PROTECTED]> wrote:
> The next step is to shutdown mysqld, delete the
> mysql and temp tables from
> your /data/ folder, and then re-run the
> scripts/mysql_install_db. That will
> reinstall the mysql/hosts.frm, which will basically
> reset your root user and
> password. However, it will also kill any users you
> have created. 
> 
> To reset the root password without "harming" other
> mysql users:
> 
> Shell> kill cat /path/to/mysql/data/hostname.pid
> Shell> ./bin/mysqld_safe --user=mysql
> --skip-grant-tables &
> Shell> mysqladmin -u root password 'newpassword'
> Shell> mysqladmin shutdown
> 
> Then restart the mysqld like normal. Hope this
> helps!
> 
> J.R.
> 


=
-DIGITAL  SIGNATURE---
///Mohsen Pahlevanzadeh
 Network administrator  & programmer 
  My work phone is : +98216054096-7   
  My home phone is: +98213810146  
My emails is  
  [EMAIL PROTECTED]   
My website is: http://webnegar.net


__
Do you Yahoo!?
Yahoo! Small Business $15K Web Design Giveaway 
http://promotions.yahoo.com/design_giveaway/

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

Re: Create Table Syntax

2004-04-01 Thread Rhino


- Original Message - 
From: "Khashan" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Thursday, April 01, 2004 1:04 PM
Subject: Create Table Syntax


>
> I am looking at the syntax for creating table( section 6.5.3 of the
manual):
>
> CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
[(create_definition,...)] [table_options] [select_statement]
>
>
>
> and:
>
> create_definition:
>
>   part-1 (<- inserted by me for reference)
>
>   col_name type [NOT NULL | NULL] etc...[reference_definition]
>
>  part-2 (<- inserted by me for reference)
>
>   | PRIMARY KEY (index_col_name,...)
>
>   | KEY [index_name] (index_col_name,...)
>
>   | INDEX [index_name] (index_col_name,...)
>
>   | UNIQUE [INDEX] [index_name] (index_col_name,...)
>
>   | FULLTEXT [INDEX] [index_name] (index_col_name,...)
>
>   | [CONSTRAINT symbol] FOREIGN KEY ..   [reference_definition]
>
>   | CHECK (expr)
>
>
>
> The syntax suggests that for each column, in addition to specifying
part-1, I can specify any or all of the options in part-2 ???.
>
> 1- Is this true.
>
>
Yes.
>
> 2- Can I specify the options in part-2 after I defined all my columns. I
have used things like:
>
> CREATE TABLE animals (
>
>  id MEDIUMINT NOT NULL AUTO_INCREMENT,
>
>  name CHAR(30) NOT NULL,
>
>  PRIMARY KEY (id)
>
>  );
>
Yes, this is the way a CREATE TABLE normally looks: after each column
name/datatype/null specification ("part 1" in your terminology) come the
various attributes that further define the column ("part 2" in your
terminology). A given column can have one or more of these attributes.

Rhino


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

Manual for MySQL control centre

2004-04-01 Thread Colin.Beeke

Dear Sir/Madam,

I am looking for a manual for MySQL Control Centre, so that I may supply
it to the students I teach. Do you have such an items or can offer any
assistance.

This I hope will assistance the students in their learning for a Module
called Internet Programming, for which we use MySQL as the database.

Thank you for your assistance.

Regards 

Colin Beeke
Senior Lecturer Thames Valley University.

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

Re: Weird Error message

2004-04-01 Thread Rhino

It would really help if you could tell us at least which version of MySQL
you are using. It might also help to know which OS you are using and what
command you issued to get this error message.

Rhino

- Original Message - 
From: "Jason Chuong" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Thursday, April 01, 2004 12:40 PM
Subject: Weird Error message


Hi All,

has anyone seen this error message before

040331  0:26:00  ft_read_next: Got error 127 when reading table ./
040331  0:26:38  ft_read_next: Got error 127 when reading table ./
040401  0:23:10  ft_read_first: Got error 127 when reading table ./
040401  0:23:19  ft_read_next: Got error 127 when reading table ./
040401  0:23:27  ft_read_next: Got error 127 when reading table ./
040401  0:25:21  ft_read_next: Got error 127 when reading table ./
040401  0:27:16  ft_read_next: Got error 127 when reading table ./


I did a " check table " command and the table came up fine.  No clue ???

Thank you for your assistant


-- 
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]

Create Table Syntax

2004-04-01 Thread Khashan


I am looking at the syntax for creating table( section 6.5.3 of the manual):

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [(create_definition,...)] 
[table_options] [select_statement]

 

and:

create_definition:

  part-1 (<- inserted by me for reference)

  col_name type [NOT NULL | NULL] etc..[reference_definition]

 part-2 (<- inserted by me for reference)

  | PRIMARY KEY (index_col_name,...)

  | KEY [index_name] (index_col_name,...)

  | INDEX [index_name] (index_col_name,...)

  | UNIQUE [INDEX] [index_name] (index_col_name,...)

  | FULLTEXT [INDEX] [index_name] (index_col_name,...)

  | [CONSTRAINT symbol] FOREIGN KEY .   [reference_definition]

  | CHECK (expr)

 

The syntax suggests that for each column, in addition to specifying part-1, I can 
specify any or all of the options in part-2 ???.  

1- Is this true. 

 

2- Can I specify the options in part-2 after I defined all my columns. I have used 
things like:

CREATE TABLE animals (

 id MEDIUMINT NOT NULL AUTO_INCREMENT,

 name CHAR(30) NOT NULL,

 PRIMARY KEY (id)

 );

P.S. I am trying to auto generate the create statement with any of the options  and 
want to make sure that the statement is solid before it is sent to the server. 

 

Thank you all in advance.

 


Do you Yahoo!?
Yahoo! Mail - More reliable, more storage, less spam

Re: starting mysql daemon

2004-04-01 Thread muhammad alqama

hello 
it is now running .. 
thank everyone who helped me ... many many thanks 
 
regards 
alqama 
 
 
 
 
 
- Original Message - 
From: Michael Stassen <[EMAIL PROTECTED]> 
Date: Thu, 01 Apr 2004 12:13:43 -0500 
To: muhammad alqama <[EMAIL PROTECTED]> 
Subject: Re: starting mysql daemon 
 
> All the files in the mysql directory should be 
owned by the mysql user, so  
> yes, you should change all of them. 
>  
> Michael 
>  
> muhammad alqama wrote: 
>  
> > hello  
> >   
> > the directory /var/lib/mysql/mysql (datadir) has  
> > read/write permissions for user "mysql" and for 
root  
> > group  
> >   
> > "drwx-- 2 mysql root..."  
> > but the file /var/lib/mysql/mysql/host.frm (as 
well  
> > as all the files in dir .../mysql/mysql) has  
> > permissions only for root  
> >  -wr-wr- 1 root root   
> > should i change the permissions for the file  
> > host.frm or the directory...for user mysql  
> >   
> > regards  
> > alqama  
> >   
> >   
> >   
> > - Original Message -  
> > From: [EMAIL PROTECTED]  
> > Date: Thu, 1 Apr 2004 10:02:22 -0600   
> > To: [EMAIL PROTECTED],  
> > [EMAIL PROTECTED],[EMAIL PROTECTED]  
> > Subject: RE: starting mysql daemon  
> >   
> >  
> >>Errno 13 indicates a permissions error.  Please  
> >  
> > check the ownership of the  
> >  
> >>data directories you're using to make sure that  
> >  
> > the mysql user & group.  
> >  
> >>  
> >>Dave  
> >>  
> >>-Original Message-  
> >>From: muhammad alqama  
> >  
> > [mailto:[EMAIL PROTECTED]   
> >  
> >>Sent: Thursday, April 01, 2004 9:46 AM  
> >>To: [EMAIL PROTECTED]; 
[EMAIL PROTECTED]  
> >>Subject: Re: starting mysql daemon  
> >>  
> >>  
> >>hi   
> >>the error mentioned in the file is...   
> >>"fatal error : can not open privilege tables can  
> >  
> > not   
> >  
> >>find file : './mysql/host.frm' (errno 13)"   
> >>   
> >>what to do now...   
> >>   
> >>regards
> >>alqama   
> >>   
> >>   
> >  
> >   
>  
-- 

Are you a Proud Pakistani ? 
Get your free Pakistani email from http://www.pakistans.com

Please visit our sponsors
www.shopngift.com  ( Gifts to Pakistan )
www.cdstore.com.pk ( Buy Online Pakistani and Indian CD)





Powered by Outblaze

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

Weird Error message

2004-04-01 Thread Jason Chuong

Hi All,

has anyone seen this error message before

040331  0:26:00  ft_read_next: Got error 127 when reading table ./
040331  0:26:38  ft_read_next: Got error 127 when reading table ./ 
040401  0:23:10  ft_read_first: Got error 127 when reading table ./
040401  0:23:19  ft_read_next: Got error 127 when reading table ./
040401  0:23:27  ft_read_next: Got error 127 when reading table ./
040401  0:25:21  ft_read_next: Got error 127 when reading table ./
040401  0:27:16  ft_read_next: Got error 127 when reading table ./


I did a " check table " command and the table came up fine.  No clue ???

Thank you for your assistant


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

Re: Record Change Date

2004-04-01 Thread Victor Pendleton

Can you add a timestamp field in your table? This field will be updated upon
insertion and updates.

 On Thu, 01 Apr 2004, Jeff Cook wrote: > Is there any way
to see what date a record was changed on?  I am building a > database of mostly
imported CSV files.  The files would be imported on the > day they were created
and would not have a date field in them.  May hope > would be to have a field
in the database that would show the date the record > last changed.
> 
> Any ideas would be very helpful.
> 
> Thank You
> 
> Jeff Cook
> Network Administrator
> Whatcom Educational Credit Union
> 
> -- 
> 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]

Record Change Date

2004-04-01 Thread Jeff Cook

Is there any way to see what date a record was changed on?  I am building a
database of mostly imported CSV files.  The files would be imported on the
day they were created and would not have a date field in them.  May hope
would be to have a field in the database that would show the date the record
last changed.

Any ideas would be very helpful.

Thank You

Jeff Cook
Network Administrator
Whatcom Educational Credit Union

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

Re: starting mysql daemon

2004-04-01 Thread Michael Stassen

All the files in the mysql directory should be owned by the mysql user, so 
yes, you should change all of them.

Michael

muhammad alqama wrote:

hello 
 
the directory /var/lib/mysql/mysql (datadir) has 
read/write permissions for user "mysql" and for root 
group 
 
"drwx-- 2 mysql root..." 
but the file /var/lib/mysql/mysql/host.frm (as well 
as all the files in dir .../mysql/mysql) has 
permissions only for root 
 -wr-wr- 1 root root  
should i change the permissions for the file 
host.frm or the directory...for user mysql 
 
regards 
alqama 
 
 
 
- Original Message - 
From: [EMAIL PROTECTED] 
Date: Thu, 1 Apr 2004 10:02:22 -0600  
To: [EMAIL PROTECTED], 
[EMAIL PROTECTED],[EMAIL PROTECTED] 
Subject: RE: starting mysql daemon 
 

Errno 13 indicates a permissions error.  Please 
check the ownership of the 

data directories you're using to make sure that 
the mysql user & group. 

Dave 

-Original Message- 
From: muhammad alqama 
[mailto:[EMAIL PROTECTED]  

Sent: Thursday, April 01, 2004 9:46 AM 
To: [EMAIL PROTECTED]; [EMAIL PROTECTED] 
Subject: Re: starting mysql daemon 

hi  
the error mentioned in the file is...  
"fatal error : can not open privilege tables can 
not  

find file : './mysql/host.frm' (errno 13)"  
 
what to do now...  
 
regards   
alqama  
 
 
 


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

DB Connection

2004-04-01 Thread Shrikrishna Sathe

The following .asp code sinppet gives error. 
 
Dim rs
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open "printjob",objConn, adOpenForwardOnly, adLockOptimistic  , adCmdTable
rs.AddNew 

   rs("jobid") = Request.Form("jobno")
   rs("closesize") = Request.Form("closize")
   rs("opensize") = Request.Form("opsize")  
 
   rs.Update
   rs.Close
   Set rs = Nothing

Is this AddNew call doesn't work in mysql ?
The Insert into statement works well but it is very cumbersome while adding >25 
fields. 

please guide.

Thanks & Regards
Shrikrishna

RE: starting mysql daemon

2004-04-01 Thread muhammad alqama

hello 
 
the directory /var/lib/mysql/mysql (datadir) has 
read/write permissions for user "mysql" and for root 
group 
 
"drwx-- 2 mysql root..." 
but the file /var/lib/mysql/mysql/host.frm (as well 
as all the files in dir .../mysql/mysql) has 
permissions only for root 
 -wr-wr- 1 root root  
should i change the permissions for the file 
host.frm or the directory...for user mysql 
 
regards 
alqama 
 
 
 
- Original Message - 
From: [EMAIL PROTECTED] 
Date: Thu, 1 Apr 2004 10:02:22 -0600  
To: [EMAIL PROTECTED], 
[EMAIL PROTECTED],[EMAIL PROTECTED] 
Subject: RE: starting mysql daemon 
 
> Errno 13 indicates a permissions error.  Please 
check the ownership of the 
> data directories you're using to make sure that 
the mysql user & group. 
>  
> Dave 
>  
> -Original Message- 
> From: muhammad alqama 
[mailto:[EMAIL PROTECTED]  
> Sent: Thursday, April 01, 2004 9:46 AM 
> To: [EMAIL PROTECTED]; [EMAIL PROTECTED] 
> Subject: Re: starting mysql daemon 
>  
>  
> hi  
> the error mentioned in the file is...  
> "fatal error : can not open privilege tables can 
not  
> find file : './mysql/host.frm' (errno 13)"  
>   
> what to do now...  
>   
> regards   
> alqama  
>   
>   
 
-- 

Are you a Proud Pakistani ? 
Get your free Pakistani email from http://www.pakistans.com

Please visit our sponsors
www.shopngift.com  ( Gifts to Pakistan )
www.cdstore.com.pk ( Buy Online Pakistani and Indian CD)





Powered by Outblaze

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

Re: load data help

2004-04-01 Thread beacker

David McBride <[EMAIL PROTECTED]> writes:
>Thanks so much, that did the trick.
>I really appreciate the mercy on a poor newbie.

David,
 You're quite welcome.  Now if I could find an employer willing
to pay me for utilizing the 23 years of knowledge I used to provide
such a solution :)
 Brad Eacker ([EMAIL PROTECTED])

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

4.1 timestamp errors

2004-04-01 Thread Arthur, John

Good morning,

I just upgraded to MySQL 4.1 from 4.0.  The upgrade went fine but now all of my 
timestamp fields are being cut off half way through the time.  Example field: 
"2004-04-01 10:4"
Can anyone provide some insight into why I have this issue and how I can resolve it?

Thanks!
- - - - - - - - - - - - - - - - - - - - - - - - - - - - 
John Arthur
Email: [EMAIL PROTECTED]
T-Mobile USA
Get more from life


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

Re: starting mysql daemon

2004-04-01 Thread muhammad alqama

hi  
the error mentioned in the file is...  
"fatal error : can not open privilege tables can not  
find file : './mysql/host.frm' (errno 13)" 
 
but in the directory /var/lib/mysql/mysql the file 
host.frm exists...(/var/lib/mysql/  being the data 
dir)  
  
what to do now...  
  
regards   
alqama  
  
  
- Original Message -  
From: Victor Pendleton <[EMAIL PROTECTED]>  
Date: Wed, 31 Mar 2004 16:03:43 -0600  
To: "muhammad alqama" <[EMAIL PROTECTED]>  
Subject: Re: starting mysql daemon  
  
> This file is normally located in the data directory  
>   
> On Wed, 31 Mar 2004, you wrote:  
> > hi  
> > can u tell me where this file "hostname.err" lies  
> > as i have searched and not found it .  
> >   
> >   
> > - Original Message -  
> > From: Victor Pendleton <[EMAIL PROTECTED]>  
> > Date: Wed, 31 Mar 2004 15:17:54 -0600  
> > To: "muhammad alqama" <[EMAIL PROTECTED]>,  
[EMAIL PROTECTED]  
> > Subject: Re: starting mysql daemon  
> >   
> > > Did you check in the hostname.err file to see  
why MySQL ended?  
> > >   
> > >   
> > > On Wed, 31 Mar 2004, muhammad alqama wrote:  
> > > > hello   
> > > > i am using slackware linux and i installed  
mysql   
> > > > server with the installation.   
> > > >
> > > > now i am trying to start the mysql daemon but  
it is   
> > > > not being start.   
> > > >
> > > > i have run "mysql_install_db --user=mysql" ..  
it   
> > > > runs successfully. but as i run the command   
> > > > "mysqld_safe" to run daemon it gives the  
following   
> > > > output..   
> > > > "   
> > > > i run..   
> > > > root../usr/bin > mysqld_safe --user=mysql &   
> > > > the output is   
> > > > [1] 1273   
> > > > root../usr/bin> starting mysqld daemon with  
database   
> > > > from /var/lib/mysql   
> > > > 040325 01:57:32 mysqld ended   
> > > > (the prompt "root../usr/bin> " is not  
returned.. i   
> > > > have to press ctrl+c)   
> > > > "   
> > > > also if i run
> > > > root../usr > libexec/mysqld   
> > > > the message is   
> > > > "fatal error : please read "security" section  
of the   
> > > > manual to find out how to run mysqld as root"   
> > > > 040325 2:10:39 aborting   
> > > > 040325 2:10:39 libexec/mysqld shutdown  
complete   
> > > > "   
> > > > please tell me what to do to start the daemon   
> > > >
> > > > --   
> > > >  
  
> > > > Are you a Proud Pakistani ?   
> > > > Get your free Pakistani email from http:// 
www.pakistans.com  
> > > >   
> > > > Please visit our sponsors  
> > > > www.shopngift.com  ( Gifts to Pakistan )  
> > > > www.cdstore.com.pk ( Buy Online Pakistani and  
Indian CD)  
> > > >   
> > > >   
> > > >   
> > > >   
> > > >   
> > > > Powered by Outblaze  
> > > >   
> > > > --   
> > > > 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]  
> > >   
> >   
> > --   
> >  
  
> > Are you a Proud Pakistani ?   
> > Get your free Pakistani email from http:// 
www.pakistans.com  
> >   
> > Please visit our sponsors  
> > www.shopngift.com  ( Gifts to Pakistan )  
> > www.cdstore.com.pk ( Buy Online Pakistani and  
Indian CD)  
> >   
> >   
> >   
> >   
> >   
> > Powered by Outblaze  
-- 

Are you a Proud Pakistani ? 
Get your free Pakistani email from http://www.pakistans.com

Please visit our sponsors
www.shopngift.com  ( Gifts to Pakistan )
www.cdstore.com.pk ( Buy Online Pakistani and Indian CD)





Powered by Outblaze

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

RE: starting mysql daemon

2004-04-01 Thread DChristensen

Errno 13 indicates a permissions error.  Please check the ownership of the
data directories you're using to make sure that the mysql user & group.

Dave

-Original Message-
From: muhammad alqama [mailto:[EMAIL PROTECTED] 
Sent: Thursday, April 01, 2004 9:46 AM
To: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: Re: starting mysql daemon


hi 
the error mentioned in the file is... 
"fatal error : can not open privilege tables can not 
find file : './mysql/host.frm' (errno 13)" 
 
what to do now... 
 
regards  
alqama 
 
 
- Original Message - 
From: Victor Pendleton <[EMAIL PROTECTED]> 
Date: Wed, 31 Mar 2004 16:03:43 -0600 
To: "muhammad alqama" <[EMAIL PROTECTED]> 
Subject: Re: starting mysql daemon 
 
> This file is normally located in the data directory
>  
> On Wed, 31 Mar 2004, you wrote:
> > hi
> > can u tell me where this file "hostname.err" lies 
> > as i have searched and not found it . 
> >  
> >  
> > - Original Message -
> > From: Victor Pendleton <[EMAIL PROTECTED]> 
> > Date: Wed, 31 Mar 2004 15:17:54 -0600 
> > To: "muhammad alqama" <[EMAIL PROTECTED]>, 
[EMAIL PROTECTED] 
> > Subject: Re: starting mysql daemon
> >  
> > > Did you check in the hostname.err file to see
why MySQL ended? 
> > >  
> > >  
> > > On Wed, 31 Mar 2004, muhammad alqama wrote:
> > > > hello
> > > > i am using slackware linux and i installed 
mysql  
> > > > server with the installation.
> > > >   
> > > > now i am trying to start the mysql daemon but
it is  
> > > > not being start.
> > > >   
> > > > i have run "mysql_install_db --user=mysql" ..
it  
> > > > runs successfully. but as i run the command
> > > > "mysqld_safe" to run daemon it gives the 
following  
> > > > output..
> > > > "  
> > > > i run..  
> > > > root../usr/bin > mysqld_safe --user=mysql &  
> > > > the output is  
> > > > [1] 1273  
> > > > root../usr/bin> starting mysqld daemon with 
database  
> > > > from /var/lib/mysql
> > > > 040325 01:57:32 mysqld ended  
> > > > (the prompt "root../usr/bin> " is not 
returned.. i  
> > > > have to press ctrl+c)
> > > > "  
> > > > also if i run   
> > > > root../usr > libexec/mysqld  
> > > > the message is  
> > > > "fatal error : please read "security" section 
of the  
> > > > manual to find out how to run mysqld as root"
> > > > 040325 2:10:39 aborting  
> > > > 040325 2:10:39 libexec/mysqld shutdown 
complete  
> > > > "
> > > > please tell me what to do to start the daemon  
> > > >   
> > > > --
> > > > 
 
> > > > Are you a Proud Pakistani ?
> > > > Get your free Pakistani email from http://
www.pakistans.com 
> > > >  
> > > > Please visit our sponsors
> > > > www.shopngift.com  ( Gifts to Pakistan ) 
> > > > www.cdstore.com.pk ( Buy Online Pakistani and 
Indian CD) 
> > > >  
> > > >  
> > > >  
> > > >  
> > > >  
> > > > Powered by Outblaze
> > > >  
> > > > --
> > > > 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] 
> > >  
> >  
> > --
> > 
 
> > Are you a Proud Pakistani ?
> > Get your free Pakistani email from http://
www.pakistans.com 
> >  
> > Please visit our sponsors
> > www.shopngift.com  ( Gifts to Pakistan ) 
> > www.cdstore.com.pk ( Buy Online Pakistani and 
Indian CD) 
> >  
> >  
> >  
> >  
> >  
> > Powered by Outblaze
-- 

Are you a Proud Pakistani ? 
Get your free Pakistani email from http://www.pakistans.com

Please visit our sponsors
www.shopngift.com  ( Gifts to Pakistan )
www.cdstore.com.pk ( Buy Online Pakistani and Indian CD)





Powered by Outblaze

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

Re: starting mysql daemon

2004-04-01 Thread muhammad alqama

hi 
the error mentioned in the file is... 
"fatal error : can not open privilege tables can not 
find file : './mysql/host.frm' (errno 13)" 
 
what to do now... 
 
regards  
alqama 
 
 
- Original Message - 
From: Victor Pendleton <[EMAIL PROTECTED]> 
Date: Wed, 31 Mar 2004 16:03:43 -0600 
To: "muhammad alqama" <[EMAIL PROTECTED]> 
Subject: Re: starting mysql daemon 
 
> This file is normally located in the data directory 
>  
> On Wed, 31 Mar 2004, you wrote: 
> > hi 
> > can u tell me where this file "hostname.err" lies 
> > as i have searched and not found it . 
> >  
> >  
> > - Original Message - 
> > From: Victor Pendleton <[EMAIL PROTECTED]> 
> > Date: Wed, 31 Mar 2004 15:17:54 -0600 
> > To: "muhammad alqama" <[EMAIL PROTECTED]>, 
[EMAIL PROTECTED] 
> > Subject: Re: starting mysql daemon 
> >  
> > > Did you check in the hostname.err file to see 
why MySQL ended? 
> > >  
> > >  
> > > On Wed, 31 Mar 2004, muhammad alqama wrote: 
> > > > hello  
> > > > i am using slackware linux and i installed 
mysql  
> > > > server with the installation.  
> > > >   
> > > > now i am trying to start the mysql daemon but 
it is  
> > > > not being start.  
> > > >   
> > > > i have run "mysql_install_db --user=mysql" .. 
it  
> > > > runs successfully. but as i run the command  
> > > > "mysqld_safe" to run daemon it gives the 
following  
> > > > output..  
> > > > "  
> > > > i run..  
> > > > root../usr/bin > mysqld_safe --user=mysql &  
> > > > the output is  
> > > > [1] 1273  
> > > > root../usr/bin> starting mysqld daemon with 
database  
> > > > from /var/lib/mysql  
> > > > 040325 01:57:32 mysqld ended  
> > > > (the prompt "root../usr/bin> " is not 
returned.. i  
> > > > have to press ctrl+c)  
> > > > "  
> > > > also if i run   
> > > > root../usr > libexec/mysqld  
> > > > the message is  
> > > > "fatal error : please read "security" section 
of the  
> > > > manual to find out how to run mysqld as root"  
> > > > 040325 2:10:39 aborting  
> > > > 040325 2:10:39 libexec/mysqld shutdown 
complete  
> > > > "  
> > > > please tell me what to do to start the daemon  
> > > >   
> > > > --  
> > > > 
 
> > > > Are you a Proud Pakistani ?  
> > > > Get your free Pakistani email from http://
www.pakistans.com 
> > > >  
> > > > Please visit our sponsors 
> > > > www.shopngift.com  ( Gifts to Pakistan ) 
> > > > www.cdstore.com.pk ( Buy Online Pakistani and 
Indian CD) 
> > > >  
> > > >  
> > > >  
> > > >  
> > > >  
> > > > Powered by Outblaze 
> > > >  
> > > > --  
> > > > 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] 
> > >  
> >  
> > --  
> > 
 
> > Are you a Proud Pakistani ?  
> > Get your free Pakistani email from http://
www.pakistans.com 
> >  
> > Please visit our sponsors 
> > www.shopngift.com  ( Gifts to Pakistan ) 
> > www.cdstore.com.pk ( Buy Online Pakistani and 
Indian CD) 
> >  
> >  
> >  
> >  
> >  
> > Powered by Outblaze 
-- 

Are you a Proud Pakistani ? 
Get your free Pakistani email from http://www.pakistans.com

Please visit our sponsors
www.shopngift.com  ( Gifts to Pakistan )
www.cdstore.com.pk ( Buy Online Pakistani and Indian CD)





Powered by Outblaze

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

mysql 2 nics forward to bound ipaddress

2004-04-01 Thread Frank Singleton

Hi,

If I start mysql  bound to a particular address on our server, eg:

mysqld --bind-address=MY_IP_ADDRESS

and this server has 2 nics connected via 2 switches. If the first NIC or 
switch fails,
can I use iptables to forward incoming mysql requests from the 2nd 
(alive) nic (IP address)
so it *looks* like it can still find the mysqld listening for packets on 
the first IP
address. Ditto on the way out ?

If not, what is a good alternative to reaching the same mysqld server 
via its 2nd nic
if the first fails.

Thanks / Frank





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

RE: Using Access as client all fields are marked "#Deleted"

2004-04-01 Thread Osvaldo Sommer

What type of connection are you using?
Linked table or you connect on demand?

Osvaldo

-Original Message-
From: Patrick Kirk [mailto:[EMAIL PROTECTED] 
Sent: Thursday, April 01, 2004 7:32 AM
To: Osvaldo Sommer
Subject: Re: Using Access as client all fields are marked "#Deleted"

Osvaldo Sommer wrote:
> Hi
> 
> 1.- Does all your tables have a primary key?
> 2.- add a field type timestamp to all your tables and don't touch it.
> 
> The problem is that access does not know if anyone has modify the
record
> 
> Osvaldo Sommer
> 
Thanks but it seems to make no difference.  Here's a small table example

that requires me to do a hard refresh whenever I add a new record.

CREATE TABLE `tblFolders` (
   `F_ID` tinyint(255) unsigned NOT NULL auto_increment,
   `NAME` varchar(30) NOT NULL default '',
   `TITLE` varchar(30) NOT NULL default '',
   `BODY` mediumtext NOT NULL,
   `CLASS` tinyint(10) NOT NULL default '1',
   `TIMESTAMP` timestamp(14) NOT NULL,
   PRIMARY KEY  (`F_ID`)
) TYPE=MyISAM COMMENT='THIS IS FOR PEOPLE WHO WANT TO BROWSE BY FOLDER' 
AUTO_INCREMENT=10 ;

Does that timstamp need some kind of tweaking?

---
Incoming mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.639 / Virus Database: 408 - Release Date: 3/22/2004
 

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.639 / Virus Database: 408 - Release Date: 3/22/2004
 


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

Re: autonumber rows in SQL result, how to

2004-04-01 Thread Egor Egorov

"Miroslav I." <[EMAIL PROTECTED]> wrote:
> - how to autonumber rows in SQL result
>
> I would like to put an auto numbering column in any SQL SELECT result, is it 
> possibile?

You can use variables, for example:

mysql> SELECT @id:=0;
++
| @id:=0 |
++
|  0 |
++
1 row in set (0.00 sec)

mysql> SELECT @id:[EMAIL PROTECTED], str FROM test;
++--+
| @id:[EMAIL PROTECTED] | str  |
++--+
|  1 | aaa  |
|  2 | bbb  |
|  3 | ccc  |
++--+
3 rows in set (0.01 sec)

http://www.mysql.com/doc/en/Variables.html
http://www.mysql.com/doc/en/example-user-variables.html




-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Egor Egorov
 / /|_/ / // /\ \/ /_/ / /__   [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]

RE: load data help

2004-04-01 Thread David McBride

Thanks so much, that did the trick.
I really appreciate the mercy on a poor newbie.

Thanks again,
David

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Wednesday, March 31, 2004 5:28 PM
To: David McBride
Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: Re: load data help 


>My question is, how can I take a log file that has 25 columns of data and
>tell mysql to only load column 1, column 3, and column 7 from the raw log
>file?

 I'm not sure mysql can do this.  I'd be more inclined to use cut
on a Linux system in the following fashion:

cut -d ' ' -f 1,3,7 out.dat

then use out.dat as the file spec for the 'Load Data INFILE'
   Brad Eacker ([EMAIL PROTECTED])



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

MySQL Administrator don't connect

2004-04-01 Thread Renato Cramer

Hello,

I use MySQL 4.1 in Windows 2000 and don't get connect with MySQL
Administrator.

The message what get is:
Could not connect to the specified host.
MySQL Error Nr.1115
Unknown character set: 'utf8'

Details of the my connection:
Username: root
Password: 
Hostname: localhost
Port: 3306

Someone can help me?

Thanks in advance,

Renato Cramer

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

autonumber rows in SQL result, how to

2004-04-01 Thread Miroslav I.

- how to autonumber rows in SQL result

I would like to put an auto numbering column in any SQL SELECT result, is it possibile?



miroslav i.

connection errors when making lot of connections continiously.

2004-04-01 Thread Grégory Starck

hi,

I'm using mysql mysql 4.0.18-log, and I'm having trouble (i tried also 
with an older 3.23.49-log and give the same).
It's running on debian 2.4.20.

I explain the case :

I've a multi-processes application, each of these process doing about that :

while (1)
{
 wait some client,
 while (1)
 {
   wait some command from client,
   do some work (1),
   connect to db,
   query,   # nb, all used query are very simple
   disconnect from db,
   some other work (2),
 }
 disconnect the client
}
the inner most { } could be executed many times during a second

the problem is that when the number of processes reach a certain limit 
(in my case, when i'm more than say : 500-600 process), there are some 
time to time some connect that returns error (always the same error) :

Can't connect to MySQL server on 'x.x.x.x' (99) (mysql_errno=2003). 
(when application on another host than db)
Can't connect to local MySQL server through socket 
'/var/run/mysqld/mysqld.sock' (11) (mysql_errno=2002). (when on the same 
host)

What is not normal I think, is that that doesn't come at any time, and 
when there are the errors, they don't stay always, but remains only, 
says 1 or 2 seconds (sometimes more, sometimes less), less when there 
are fewer processes doing the connections, and more when there are ... 
yes, more (processes doing the connections) ;-).

Also, that looks like a cyclic problem :

yesterday night, i made a test :
I launched a lot (2000 exactly) of clients calling my application :
start :
time = 0 (each "|" is says 1 minute duration)
| ok
| ok
| ok
| ok
| ok
| ho some connections in the 2000 returned errors (maybe a lot, nearly 
all of the 2000)
| ho some connections in the 2000 returned errors
| ok,
| ok
| ok
| ok
... ||...||| ok so says there are no more error during 30 minutes
| errors (a lot)
| errors (a lot)
| errors (a lot)
| ok says for 5 minutes
| errors (some, so maybe only a few)
| errors (some)
||| ok for says 20 minutes
| errors (a lot)
| errors (a lot)
...
and so on during 6 hours ...

the greater duration with no error at all was nearly 50 minutes !

I tried many things, but don't really know where does that fact come 
from ..

So, here is my question:
Where does the hell that comes from ?
Thanks a lot if you can help me !

NB: if needed, here is my my.cnf :

# This will be passed to all mysql clients
[client]
#password= my_password
port= 3306
socket= /var/run/mysqld/mysqld.sock
# Here is entries for some specific programs
# The following values assume you have at least 32M ram
[safe_mysqld]
err-log= /var/log/mysql/mysql.err
[mysqld]
user= mysql
pid-file= /var/run/mysqld/mysqld.pid
socket= /var/run/mysqld/mysqld.sock
port= 3306
#
# You can also put it into /var/log/mysql/mysql.log but I leave it in 
/var/log
# for backward compatibility. Both location gets rotated by the cronjob.
log= /var/log/mysql/mysql.log
basedir= /usr
datadir= /var/mysql
tmpdir= /tmp
language= /usr/share/mysql/english
skip-locking
#
# The skip-networkin option will no longer be set via debconf menu.
# You have to manually change it if you want networking i.e. the server
# listening on port 3306. The default is "disable" - for security reasons.
#skip-networking
set-variable= key_buffer=64M
set-variable= max_allowed_packet=4M
set-variable= table_cache=512
set-variable= sort_buffer=2M
set-variable= record_buffer=2M
set-variable= thread_cache=2
set-variable= thread_stack=4K
set-variable= max_connections=5050
set-variable= max_connect_errors=100
set-variable= wait_timeout=600
# Try number of CPU's*2 for thread_concurrency
set-variable= thread_concurrency=4
set-variable= myisam_sort_buffer_size=64M
#
set-variable= long_query_time=30

set-variable= open-files-limit=36864

#
# Here you can see queries with especially long duration
log-slow-queries= /var/log/mysql/mysql-slow.log
#
# The following can be used as easy to replay backup logs or for replication
#server-id= 1
#log-bin= /var/mysql/binlog/mysql-bin.log
#binlog-do-db= mail_profiling
#binlog-do-db= mail_profiling_devel
#binlog-ignore-db= mysql
#binlog-ignore-db= mail_accounting
#binlog-ignore-db= mail_accounting_devel
#binlog-ignore-db= mail_system
#binlog-ignore-db= mail_system_devel
#binlog-ignore-db= history
#binlog-ignore-db= historydb
#binlog-ignore-db= historydb_devel
#binlog-do-db= include_database_name
#
# Read the manual if you want to enable InnoDB!
# skip-innodb

[mysqldump]
quick
set-variable= max_allowed_packet=16M
[mysql]
#no-auto-rehash# faster start of mysql but no tab completition
[isamchk]
set-variable= key_buffer=128M
set-variable= sort_buffer=128M
set-variable= read_buffer=1M
set-variable= write_buffer=1M
[myisamchk]
set-variable= key_buffer=128M
set-variable= sort_buffer=128M
set-variab

Re: Is this possible?

2004-04-01 Thread Chris Boget

> GROUP_CONCAT() is in 4.1. :-)

Oh, so close yet so far.  We're running 4.0.14 and my company has
no plans on upgrading in the near future.  *sigh*

Thanks for the info, however!!

Chris


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

Re: INSERT INTO ... SELECT

2004-04-01 Thread Egor Egorov

"Ricardo Lopes" <[EMAIL PROTECTED]> wrote:
> I have to copy some records from one table into the same table is it
> posible?
> 
> my sql is like this:
> 
> INSERT INTO equipamento_componentes (cod_equipamento_componentes, cod_tipo,
> numero, data_colocacao, cod_componente)
> 
> SELECT '', 'C', 65, NOW(), EQ.cod_componente
> 
> FROM equipamento_componentes EQ
> 
> WHERE (EQ.cod_tipo='C' and EQ.numero=1)
> 
> 
> 
> The table is like this:
> 
> cod_equipamento_componentes intauto_increment
> 
> cod_tipochar
> 
> numeroint
> 
> data_colocacaodate
> 
> cod_componenteint (FK references another
> table)
> 
> 
> 
> I get an erro saying that i cannot use that table in the select, the table
> is a InnoDB.
> 
> Can i use the same table in the insert into select? do i have to use a
> temporary table?
> 

What version do you use?
You can insert data in to the same table as you use in the  SELECT part from version 
4.0.14:
http://www.mysql.com/doc/en/INSERT_SELECT.html



-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Egor Egorov
 / /|_/ / // /\ \/ /_/ / /__   [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]

Re: adding/changing passwords on mysql

2004-04-01 Thread Jigal van Hemert

From: "Talk21" <[EMAIL PROTECTED]>

> I have noticed that when I change a password using sqlyog,
> the password table stores as plain text which surely can'y be
>  right ?

> Can anyone tell me what I'm doing wrong here. All the
> passowrds originaly were added useing the command line
> client in Dos window. and when originally viewed they were
> just a sequence of letters and numbers so the password was
> not viewable.

Use the MySQL function PASSWORD() to encrypt the password before it's stored
in the db.
I don't know sqlyog, but there should be a way to do this...

Regards, Jigal.



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

adding/changing passwords on mysql

2004-04-01 Thread Talk21

Hi all,
I have noticed that when I change a password using sqlyog, the password table stores 
as plain text which surely can'y be right ?
Can anyone tell me what I'm doing wrong here. All the passowrds originaly were added 
useing the command line client in Dos window. and when originally viewed they were 
just a sequence of letters and numbers so the password was not viewable.

Thanks for any help on this.

Regards
Andy Fletcher

Re: Problems replicating

2004-04-01 Thread Jonas Lindén

Hola, I figured it out myself, The problem was that I didnt specify any
tables. I thought that if you replicated a database the tables would be
included. That is not the case.

I added this to make it work.
Replicate_Wild_Do_Table: CellDB%.%

Regards
/Jonas

- Original Message - 
From: "Jonas Lindén" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Thursday, April 01, 2004 10:33 AM
Subject: Problems replicating


Hello, I still trying to get my replication working witout success. I
included show processlist and show slave status. Can anyone make out from
them what I am lacking?

I am running mysql 5.0.0 on a linux master trying to replicate them on a
windows slave

Best regards
/Jonas


*** 1. row ***
 Slave_IO_State: Waiting for master to send event
Master_Host: myMaster.com
Master_User: repl
Master_Port: 3307
  Connect_Retry: 60
Master_Log_File: myMaster-bin.01
Read_Master_Log_Pos: 1469782
 Relay_Log_File: jonas-linden-relay-bin.05
  Relay_Log_Pos: 231
  Relay_Master_Log_File: myMaster-bin.01
   Slave_IO_Running: Yes
  Slave_SQL_Running: Yes
Replicate_Do_DB: celldb
Replicate_Ignore_DB:
 Replicate_Do_Table:
 Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
 Last_Errno: 0
 Last_Error:
   Skip_Counter: 0
Exec_Master_Log_Pos: 1469782
Relay_Log_Space: 136
Until_Condition: None
 Until_Log_File:
  Until_Log_Pos: 0
 Master_SSL_Allowed: No
 Master_SSL_CA_File:
 Master_SSL_CA_Path:
Master_SSL_Cert:
  Master_SSL_Cipher:
 Master_SSL_Key:
  Seconds_Behind_Master: 238384
1 row in set (0.00 sec)

mysql> show processlist\G;
*** 1. row ***
 Id: 255
   User: root
   Host: localhost:1886
 db: celldb
Command: Query
   Time: 0
  State: NULL
   Info: show processlist
*** 2. row ***
 Id: 448
   User: system user
   Host:
 db: NULL
Command: Connect
   Time: 339
  State: Waiting for master to send event
   Info: NULL
*** 3. row ***
 Id: 452
   User: system user
   Host:
 db: NULL
Command: Connect
   Time: 317
  State: Has read all relay log; waiting for the slave I/O thread to update
   Info: NULL
3 rows 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]

Using Access as client all fields are marked "#Deleted"

2004-04-01 Thread Patrick Kirk

Hi all,

My setup is windoes 2000, Access 2000 and Mysql built from source on Debian.

Some tables can be seen but all new entries are marked "#Deleted".  If I 
close the table in Access and re-open it, its looks fine.

Others have every field marked "#Deleted" and nothing I do makes those 
tables readbale.  HOWEVER, queries that rely on the data work accurately 
but show the results as "#Deleted".

I've installed myodbc, the latest MDAC and Jet Service packs.

Is there some test or diagnostic procedure to establish what isn't 
working?  I think I've followed all the documented steps but the lack of 
diagnostics makes this very difficult.

Thanks in advance,

Patrick

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

Problems replicating

2004-04-01 Thread Jonas Lindén

Hello, I still trying to get my replication working witout success. I included show 
processlist and show slave status. Can anyone make out from them what I am lacking?

I am running mysql 5.0.0 on a linux master trying to replicate them on a windows slave

Best regards
/Jonas


*** 1. row ***
 Slave_IO_State: Waiting for master to send event
Master_Host: myMaster.com
Master_User: repl
Master_Port: 3307
  Connect_Retry: 60
Master_Log_File: myMaster-bin.01
Read_Master_Log_Pos: 1469782
 Relay_Log_File: jonas-linden-relay-bin.05
  Relay_Log_Pos: 231
  Relay_Master_Log_File: myMaster-bin.01
   Slave_IO_Running: Yes
  Slave_SQL_Running: Yes
Replicate_Do_DB: celldb
Replicate_Ignore_DB:
 Replicate_Do_Table:
 Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
 Last_Errno: 0
 Last_Error:
   Skip_Counter: 0
Exec_Master_Log_Pos: 1469782
Relay_Log_Space: 136
Until_Condition: None
 Until_Log_File:
  Until_Log_Pos: 0
 Master_SSL_Allowed: No
 Master_SSL_CA_File:
 Master_SSL_CA_Path:
Master_SSL_Cert:
  Master_SSL_Cipher:
 Master_SSL_Key:
  Seconds_Behind_Master: 238384
1 row in set (0.00 sec)

mysql> show processlist\G;
*** 1. row ***
 Id: 255
   User: root
   Host: localhost:1886
 db: celldb
Command: Query
   Time: 0
  State: NULL
   Info: show processlist
*** 2. row ***
 Id: 448
   User: system user
   Host:
 db: NULL
Command: Connect
   Time: 339
  State: Waiting for master to send event
   Info: NULL
*** 3. row ***
 Id: 452
   User: system user
   Host:
 db: NULL
Command: Connect
   Time: 317
  State: Has read all relay log; waiting for the slave I/O thread to update
   Info: NULL
3 rows in set (0.00 sec)

InnoDB problems with 4.0.18-max

2004-04-01 Thread Rick Ellis

Hi Guys,

We are currently using MySQL as the backend to the RT Request Tracker
Ticketing system. The problem is that we are seeing total data loss from
the InnoDB after a proper shutdown of the database using mysqladmin
shutdown.

We have observed this once on a Sparc Enterprise 420R with 4 CPU's and 4
gigs of RAM, using the my-large.cnf as a template and Solaris 9, but we
see this most often on mysql 4.0-18-max running on a Enterprise 220R
with 2 CPU's and 2 gigs of ram, based on my-small.cnf and Solaris 8.

Both setups are using perl 5.8.3 and the latest Apache 3 release.

On restarting the Mysql server, (which serves a number of other
application, not using InnoDB perfectly) we cannot log in. Going into
Mysql itself, and use rt3; it complains that most of the tables are
blank. See extract below from the .err log file:


040310  7:03:37  /usr/local/mysql/bin/mysqld: Normal shutdown

040310  7:03:38  InnoDB: Starting shutdown...
040310  7:03:42  InnoDB: Shutdown completed
040310  7:03:42  /usr/local/mysql/bin/mysqld: Shutdown Complete

040310 07:04:06  mysqld started
040310  7:04:07  InnoDB: Database was not shut down normally.
InnoDB: Starting recovery from log files...
InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 0 2488475
InnoDB: Doing recovery: scanned up to log sequence number 0 2488475
040310  7:04:07  InnoDB: Flushing modified pages from the buffer pool...
040310  7:04:07  InnoDB: Started
/export/mysql4/bin/mysqld: ready for connections.
Version: '4.0.18-max'  socket: '/tmp/mysql.sock'  port: 3306
040310  7:05:41  InnoDB error:
Cannot find table rt3/Users from the internal data dictionary
of InnoDB though the .frm file for the table exists. Maybe you
have deleted and recreated InnoDB data files but have forgotten
to delete the corresponding .frm files of InnoDB tables, or you
have moved .frm files to another database?
Look from section 15.1 of http://www.innodb.com/ibman.html
how you can resolve the problem.
040310  7:05:41  InnoDB error:
Cannot find table rt3/Users from the internal data dictionary
of InnoDB though the .frm file for the table exists. Maybe you
have deleted and recreated InnoDB data files but have forgotten
to delete the corresponding .frm files of InnoDB tables, or you
have moved .frm files to another database?
Look from section 15.1 of http://www.innodb.com/ibman.html
how you can resolve the problem.
040310  7:05:41  InnoDB error:
Cannot find table rt3/Users from the internal data dictionary
of InnoDB though the .frm file for the table exists. Maybe you
have deleted and recreated InnoDB data files but have forgotten
to delete the corresponding .frm files of InnoDB tables, or you
have moved .frm files to another database?
Look from section 15.1 of http://www.innodb.com/ibman.html
how you can resolve the problem.
040310  7:05:41  InnoDB error:
Cannot find table rt3/Users from the internal data dictionary
of InnoDB though the .frm file for the table exists. Maybe you
have deleted and recreated InnoDB data files but have forgotten
to delete the corresponding .frm files of InnoDB tables, or you
have moved .frm files to another database?
Look from section 15.1 of http://www.innodb.com/ibman.html
how you can resolve the problem.
040310  7:05:41  InnoDB error:
Cannot find table rt3/Users from the internal data dictionary
of InnoDB though the .frm file for the table exists. Maybe you
have deleted and recreated InnoDB data files but have forgotten
to delete the corresponding .frm files of InnoDB tables, or you
have moved .frm files to another database?
Look from section 15.1 of http://www.innodb.com/ibman.html
how you can resolve the problem.

Settings from the worst affected system' my.cnf file are:

# Uncomment the following if you are using InnoDB tables
innodb_data_home_dir = /usr/local/mysql/data/
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /usr/local/mysql/data/
innodb_log_arch_dir = /usr/local/mysql/data/
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
set-variable = innodb_buffer_pool_size=32M
set-variable = innodb_additional_mem_pool_size=2M
# Set .._log_file_size to 25 % of buffer pool size
set-variable = innodb_log_file_size=5M
set-variable = innodb_log_buffer_size=8M
innodb_flush_log_at_trx_commit=1
set-variable = innodb_lock_wait_timeout=50
#set-variable = innodb_force_recovery=4

We are doing an sqldump every hour and before any shutdowns now, because
we can't guarantee that the database will come back up.

If anyone can spot the problem or suggest a course of action to debug
the issue I'll be very happy.
-- 
Rick Ellis <[EMAIL PROTECTED]>


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

64 matches

Mail list logo