Found link that points

[Paco Martinez]

Estimated gurus.

I've got a MyISAM table with 3000 rows.

Two days ago, it showed an error Got error 127 from table
handler.Therefore, I made OPTIMIZE TABLE tabla and it was solutioned.

But today, table has 3200 rows, and it shows another error Found link that
points at
2325343242342323 (outside data file) at 235896, which has benn solved with
OPTIMIZE TABLE.

Which can be the problem ???

In prevention, I have made an mysqldump and I have create another database
distinct

It will be useful to OPTIMIZE TABLE one time every day??

Thank you



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

mySQL and Firewall

[Brumpton Richard]

I am having a problem accessing my SQL from our webserver.   Between the
two is firewall.  All the ports between the SQL server and firewall are
currently blocked. 
except port 3306 which is open.
 
However, my I can't connect to the mySQL server.  Is there anything I
can do to test or what other ports do I need open?

Richard

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: MySQL Update based on SELECT CRITERIA

[Roger Baklund]

* USER
 Is there any way to do an Update...Select like an InsertSelect?

 For instance can I fill one table with data from another table.
 The columns
 do not match exactly so a table copy won't do much good.  But the data
 retrieved in the select command is compatible with the new table fields.

Use INSERT ... SELECT.

What you describe is not an update, it is an insert. The columns of the new
table does not have to match with the exising table, you just need to
formulate the select so that the correct fields are selected in the correct
order:

insert into whatever
  select f1, mid(f2,5,3) as f2, year(f3) as f3 from someother;

You can even use joins:

insert into whatever
  select f1, mid(f2,5,3) as f2, year(another.f3) as f3
  from someother, another
  where another.id = someother.something;

--
Roger
query


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Table appears in multiple joins returns null column

[DL Neil]

Keith,

 I got it!  What you said about replacing one JOIN with another is what did
 the trick.  After a little trial and error (maybe a little more than a
 little) I got the results I needed.  Thanks for the help.  In case you're
 still curious I am modeling short track racing here in New York state.  I
 used NASCAR in the example below of the tables because I thought it might be
 a bit more familiar.  Thanks again.


Well done!

Sorry about the delay, but when the list went mad it was just too confusing to work 
out which msgs were 'real'
and which were re-mails so wholesale deletion was the order of the day. Thanks for 
sending the explanations. I
enjoy watching car racing/rallying from time-to-time, but am not a great fan. The info 
might have helped me get
my head around the problem, but you beat me to it and so you get the chequered flag!

There are several sites around offering tutorials on joins. A simple/trivial join is 
featured in just about
every 'first steps' tutorial, so its not worth visiting too many of those. Go for an 
intermediate or even an
advanced 'course'. The other source of such data is a decent SQL/MySQL book. Many have 
been recommended - check
the list archives.

It seems very school-bookish, but it is worth getting your head around some of the set 
theory behind this stuff,
eg do I only want the join when there is data on 'both sides', or where data is 
'missing' should the 'left' row
be included with a NULL where the right row data would otherwise fit? (says he bravely 
attempting to  repress an
attack of the shudders)

Originally joins in SQL were all done by adding multiple tables to the FROM clause and 
at least one (in)equality
to the WHERE clause (note the 'two' entries'). The more recent SQL 'standard' 
introduced specific JOIN clauses,
eg INNER JOIN, OUTER JOIN, and a few variants on each theme. Using this syntax the 
joins become much more
obvious/better documented. I (and others from 'the old school') learned the original 
methods, and still don't
seem to have firmly lodged the newer syntax in my tired brain. If you come across a 
good tutorial online, please
share it with me too...

Regards,
=dn



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: auto_increment value recycled/reset in BDB table? [3.23.46]

[Sinisa Milivojevic]

Thank you, we shall investigate this problem.

-- 
Regards,
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Mr. Sinisa Milivojevic [EMAIL PROTECTED]
 / /|_/ / // /\ \/ /_/ / /__   MySQL AB, Fulltime Developer
/_/  /_/\_, /___/\___\_\___/   Larnaca, Cyprus
   ___/   www.mysql.com


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Help Regarding datadir configuration

[Asit Satpathy]

Mysql pick up the database list from its default datadir C:/mysql/data
as it is mentioned in
my.ini file in window.
but i want to use my own directory which contains some database , if i
am setting  the datadir into my
directory i am able to use my own directory to which i have to copy the
default mysql database;

i want to use both data directory of mysql as well as my default
directory. if possible then please send a reply how
can i set both the directory to datadir. i am sending my.ini file which
is being used.


#This File was made using the WinMySQLAdmin 1.3 Tool
#2/4/02 12:14:58 PM

#Uncomment or Add only the keys that you know how works.
#Read the MySQL Manual for instructions

[mysqld]
basedir=C:/mysql
#bind-address=164.100.20.26
datadir=C:/mysql/data
datadir=C:/asitdatabase
#language=C:/mysql/share/your language directory
#slow query log#=
#tmpdir#=
#port=3306
#set-variable=key_buffer=16M

[WinMySQLadmin]
Server=C:/mysql/bin/mysqld-max-nt.exe
user=asit
password=asit
QueryInterval=10

  Regards
Asit


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: Empty mysql.user entries???

[Victoria Reznichenko]

Takacs,

Thursday, February 14, 2002, 9:19:37 AM, you wrote:

TI Wed, 13 Feb 2002 15:02:01 PM, you wrote:

 TI After the default mysql-3.23-47 installation,
 TI there are two entries in the mysql.user table
 TI where the User and Password fields are empty.
 TI I'd like to know what is the purpose of these
 TI records?
 TI The first has a 'localhost' entry in the Host field
 TI and the second one has the 'hostname'.
 TI Can I delete them, or will this crash the database?

 These are default records in mysql.user and all priv_types are
 set to 'N'. These rows are corresponding to default user in mysql.

TI OK.
TI But who is that 'default user'?
TI As I know just the root and an anonymous user's created as
TI the initial MySQL accounts.
TI At least I've found it in the doc;

TI An anonymous user is created that can do anything with databases that have
TI a name of 'test' or starting with 'test_'. Connections must be made from the
TI local host. This means any local user can connect without a password and be
TI treated as the anonymous user.

TI So it would be that default user what you wrote about?
Yes.
TI Is it like scott/tiger in ORACLE?
TI We don't have any real database, named 'test' or 'test_*', just
TI what the MySQL installation set.
TI Can I delete that default user and 'test' database from our public DB,
TI just after the installation?

Yes, you can delete them.


TI Whatsoever, what is the purpose of that default database named 'test'?
TI I think anyone can create a database if she/he wants to try mysql.

If a user has grants to create databases, he can create one. But i
would advise you to be careful with global privileges.


TI This means any local user can connect without a password and be treated as
TI the anonymous user.

TI I'm afraid we don't need this feature.

Anonymous user has privileges by default only in 'test' database. If
you drop 'test' db, he looses all the rights.


TI Thanks in advance!
TI Regards;
TI Istvan




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




-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

MySQL installed but no MySQLAdmin???

[Egor Egorov]

Vernon,

Thursday, February 14, 2002, 5:01:06 AM, you wrote:

VAW Why, if I have MySQL is the MySQLAdmin not on the box? Is 
VAW there something else I must install? I've downloaded the 
VAW rpm from the web site and installed. When it boots, MySQL 
VAW does come up and it says that it has started without 
VAW errors. 

VAW What am I missing?

The mysqladmin is a client-side script. Are you sure that MySQL-client is installed?





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



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Mysql Row Lock Problem

[TAKAHASHI, Tomohiro]

  Hi, Amit
  
  What kind problem do you have?
  I can't understand your problem.

  In JDBC API, Connection.commit() method does not return
affected row count.
  Generally, Statement.executeUpdate(String) method returns
that count.

Thanks.

Amit Dilip Lonkar wrote:
 
 HI
 
 I am using java. i am fireing the following query ,after and insert or
 update in Mysql through Java
 int returnValue = backend.setQueryResult("commit");
 
 where setQueryResult(String sql) is method that updates data in database.
 i have set the connection.setAutoCommit(false). The "returValue" is
 returned as "0" when the query is fired.

-- 
TAKAHASHI, Tomohiro

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: W2K Network

[Victoria Reznichenko]

Martin,

Thursday, February 14, 2002, 7:42:43 AM, you wrote:

MJ You need to set access privileges for SQL server. try with this : 

MJ GRANT ALL PRIVILEGES ON database.tables TO [EMAIL PROTECTED] \g
MJ flush privileges \g

Martin, you are right, but i want to add that there is no need to run
mysqladmin reload or FLUSH PRIVILEGES if you use GRANT.

MJ mySQL prompt.
MJ Try also to delete password from DNS on client machine.
MJ (I'm maybe make a syntax error, look in mySQL manual for details).

You can see info about GRANT in the manual:
http://www.mysql.com/doc/G/R/GRANT.html

MJ -Original Message-
MJ From: Otto Saayman [mailto:[EMAIL PROTECTED]]
MJ Sent: Wednesday, February 13, 2002 9:32 PM
MJ To: [EMAIL PROTECTED]
MJ Subject: W2K Network


MJ Hi there

MJ I have an Internal Network running MySQL with all machines running W2K. One
MJ of them has a VB Application which uses MyODBC to connect to another server
MJ running MySQL. The VB produces this error:

MJ Access Denied for username@vbappmachinename ... Using Password YES ...

MJ ODBC is set up with IP Address, port, username, password and as a System
MJ Data Source.

MJ No Machine Name.

MJ The VB App uses the System DS Name.

MJ This setup works fine if the VB App and MySQL server reside on the same
MJ machine. How do I get the other machine to work in the same way?

MJ Thank you


MJ Otto
MJ [EMAIL PROTECTED]




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




-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Help Regarding datadir configuration

[Egor Egorov]

Asit,

Thursday, February 14, 2002, 1:21:04 PM, you wrote:

AS Mysql pick up the database list from its default datadir C:/mysql/data
AS as it is mentioned in
AS my.ini file in window.
AS but i want to use my own directory which contains some database , if i
AS am setting  the datadir into my
AS directory i am able to use my own directory to which i have to copy the
AS default mysql database;

AS i want to use both data directory of mysql as well as my default
AS directory. if possible then please send a reply how
AS can i set both the directory to datadir. i am sending my.ini file which
AS is being used.

It's impossible for one MySQL server.
[skip]

AS   Regards
AS Asit





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



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

SQL Help, Please...

[Carl Shelbourne]

Hi

I am trying to write an auction script that uses mysql as its backend. Each
auction can have upto 25 sub auctions(cells) taking place.

I'm trying to query the DB to give me a list of all the successfull bids
for each cell, for each auction...

SELECT b.auctionId, b.cellId, MAX(b.bid) as bid, b.bidderId FROM sa_bid as b, 
sa_auction AS a WHERE (a.state='active' AND b.auctionId=a.Id) GROUP BY
auctionId,cellId ORDER BY bidTime DESC

This is further complicated in so much that multiple MAX bids may exist at the same 
value, but, only the earliest should be returned for each cell.

Which is returning some of the columns correctly, namely auctionid, cellid and bid, 
but it does not return the bidderId correctly.

Can anybody help?

Cheers

Carl


#
# Table structure for table `sa_auction`
#

CREATE TABLE sa_auction (
  id int(11) NOT NULL auto_increment,
  start datetime NOT NULL default '-00-00 00:00:00',
  end datetime NOT NULL default '-00-00 00:00:00',
  state enum('waiting','active','expired') NOT NULL default 'waiting',
  PRIMARY KEY  (id)
) TYPE=MyISAM;

#
# Dumping data for table `sa_auction`
#

INSERT INTO sa_auction (id, start, end, state) VALUES (1, '2002-01-23 21:42:50', 
'2002-04-30 11:30:00', 'active');
INSERT INTO sa_auction (id, start, end, state) VALUES (2, '2002-01-23 21:42:50', 
'2002-02-09 06:30:00', 'expired');
INSERT INTO sa_auction (id, start, end, state) VALUES (3, '2002-03-23 21:42:50', 
'2002-07-05 09:00:00', 'waiting');
INSERT INTO sa_auction (id, start, end, state) VALUES (4, '2002-03-23 21:42:50', 
'2002-08-01 11:30:00', 'waiting');
# 

#
# Table structure for table `sa_bid`
#

CREATE TABLE sa_bid (
  id int(11) NOT NULL auto_increment,
  auctionId int(11) NOT NULL default '0',
  cellId tinyint(4) NOT NULL default '0',
  bid int(11) NOT NULL default '0',
  bidderId mediumint(9) NOT NULL default '0',
  bidtime timestamp(14) NOT NULL,
  PRIMARY KEY  (id),
  UNIQUE KEY id (id),
  KEY id_2 (id)
) TYPE=MyISAM;

#
# Dumping data for table `sa_bid`
#

INSERT INTO sa_bid (id, auctionId, cellId, bid, bidderId, bidtime) VALUES (1, 1, 5, 1, 
1, 20020128225421);
INSERT INTO sa_bid (id, auctionId, cellId, bid, bidderId, bidtime) VALUES (2, 1, 5, 2, 
2, 20020128225424);
INSERT INTO sa_bid (id, auctionId, cellId, bid, bidderId, bidtime) VALUES (3, 1, 17, 
15, 2, 20020213214856);
INSERT INTO sa_bid (id, auctionId, cellId, bid, bidderId, bidtime) VALUES (4, 1, 5, 3, 
4, 20020213215649);




This e-mail and any attachments are confidential.  If you are not the intended 
recipient, please notify us immediately by reply e-mail and then delete this message 
from your system. Do not copy this e-mail or any attachments, use the contents for any 
purpose, or disclose the contents to any other person: to do so could be a breach of 
confidence.

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: Help doesn't work in mysqlgui

[Sinisa Milivojevic]

Peter Pierre writes:
 mysql
 
 Is there a manual for it.
 

Yes, there is.

It is a file MySQL.help, that came with distro.

To set it up to work with F1 key, see the config example of my.cnf,
that also came with distro.

-- 
Regards,
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Mr. Sinisa Milivojevic [EMAIL PROTECTED]
 / /|_/ / // /\ \/ /_/ / /__   MySQL AB, Fulltime Developer
/_/  /_/\_, /___/\___\_\___/   Larnaca, Cyprus
   ___/   www.mysql.com


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: request for help with multiple JOINs

[DL Neil]

Andreas,

If anyone else is interested, I have solved the problem in stepwise/tutorial fashion 
below (best viewed using a
fixed font).
If anyone is skilled in the user of FROM...JOINs, (I'm sure Andreas, and) I'd welcome 
a critique/any
improvements!

I have taken a look at this problem, and being more used to the 'old style' of joins 
(partly in the WHERE and
partly in the FROM clause), took the opportunity to try to use your style and (better) 
memorise how the 'newer'
FROM...JOIN clauses work.

 Is there anyone who could help me with this simple query.

 Did i ask anything in a wrong way because I still got no answers to my
 request?


 SELECT at.name, av.value, at.unit, at.id, a.product_id
 FROM  attribute_type at
 LEFT OUTER JOIN  attribute a on (at.id = a.type_id)
 LEFT OUTER JOIN  attribute_value av on (av.id = a.value_id)
 WHERE (a.product_id = 21 OR a.product_id IS Not NULL OR a.value_ID IS
 Not NULL OR a.type_ID IS Not NULL)

 and the result:

 | name | value | unit | id | product_id |
 | height   | 10| cm   |  1 | 21 |
 | width| 20| cm   |  2 | 32 |
 | width| 30| cm   |  2 | 40 |
 | diameter | 222   | cm   |  3 | 21 |
 Number of Results: 4

 I only want to have attributes for one product_id (e.g. 21). However,
 every attributetype available has to be in the result and any
 corresponding value or NULL.

 So my preferred result should look like that:

 | name | value | unit | id | product_id |
 | height   | 10| cm   |  1 | 21 |
 | width|  NULL| cm   |  2 |  NULL |   because
 width is for this product still empty but should be available to fill
 with a value
 | diameter | 222   | cm   |  3 | 21 |
 Number of Results: 3

 Any chance to achieve this?


 I have 3 tables to describe attributes for a product database.

 e.g.
 attribute_type:
 ++--+--+
 | id | name | unit |
 ++--+--+
 |  1 | height   | cm   |
 |  2 | width| cm   |
 |  3 | diameter | cm   |
 ++--+--+

 attribute_value:
 ++--+
 | id | value|
 ++--+
 |  1 | 10   |
 |  2 | 20   |
 |  3 | 30   |
 |  4 | 222  |
 ++--+

 attribute:
 ++--+--++
 | id | type_id  | value_id | product_id |
 ++--+--++
 |  1 | 1| 1|   21   |
 |  2 | 2| 2|   32   |
 |  3 | 2| 3|   40   |
 |  4 | 3| 4|   21   |
 ++--+--++

 In a web form where I edit the product, there is also an attribute
 section where I would like to give certain attributes values specific
 for the product. The problem is when I associate values to attributes in
 one product, the same attribute in other products is hidden (not in the
 result of the query). Therefore I would like to list all attribute_types
 for each product regardless if there are values assigned or not.

 I used the following query and I think there must be something wrong:

 SELECT at.name, av.value, at.unit,  at.id, a.product_id
 FROMattribute_type at
 LEFT OUTER JOIN attribute a on (at.id = a.type_id)
 LEFT OUTER JOIN attribute_value av on (av.id = a.value_id)
 WHERE (a.product_id = 21 OR a.product_id IS NULL OR a.value_ID
 IS NULL OR a.type_ID IS NULL)



Let's get started:

- I built the three tables on my system and populated them with the same sample data 
(sometimes it's faster for
me to ask you, but this was quick and easy to do late last night!)

- you want every attributetype available has to be in the result, so let's start 
with the attribute_type
table:

SELECT T.id, T.name, T.unit FROM attribute_type AS T

This produces exactly what you have above.

NB whereas you used AS at, I have used the alias T because AT is a SQL keyword. 
Also note that I didn't use
T.* (which would have been easier), because later on we will move individual columns 
around the resultset to
'pair' columns used in joins, and eventually need to discard one of each pair - 'which 
one' is another question
that has bugged you!

- the many-to-many relationships are handled by the attribute table, so let's bring 
that in next. Use a LEFT
JOIN to ensure that every row from the 'left' be listed:

mysql SELECT T.id, T.name, A.value_id, T.unit, A.type_id, A.Product_id
- FROM attribute_type AS T
-   LEFT JOIN attribute AS A ON ( T.id = A.type_id );
++--+--+--+-++
| id | name | value_id | unit | type_id | Product_id |
++--+--+--+-++
|  1 | height   |1 | cm   |   1 | 21 |
|  2 | width|2 | cm   |   2 | 32 |
|  2 | width|3 | cm   |   2 | 40 |
|  3 | diameter |4 | cm   |   

GRANT TEMPORARY CREATE ...

[Charlie Thunderberg]

Hi,

My database users have problems with the missing sub-query feature of MySql 
that I would like to solve by letting them create temporary tables since 
their scope is a connection thread. However I cannot let them to start 
creating tables that are not temporary.

Unfortunately I cannot find such privilege system currently supported by 
MySQL. Could anyone confirm that or suggest a solution?
Thanks for any response!

Charlie

_
Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp.


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

FW: On-Line Backups

[Gareth Davies]

Hi all,

Will MySQLDump and the update-log work 100% if I'm not using transactions
with the database? I'm concerned that if there are a few updates buzzing
along together and then the tables are locked, I could end up with a copy of
the DB that has only a part install in it.

Or is there a better way of doing on-line backups?

Thanks folks,
Gareth


Gareth Davies
Director
FrogTrade Ltd

T: +44 (0)1422-250800
M: +44 (0)7970-733851
F: +44 (0)1422-354232
E: [EMAIL PROTECTED]
W: www.frogtrade.com

This communication contains information which is confidential and may also
be privileged. It is for the exclusive use of the addressee. If you are not
the addressee please note that any distribution, reproduction, copying,
publication or use of this communication or the information in it is
prohibited. If you have received this communication in error, please contact
us immediately and also delete the communication from your computer.


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Upgrading from 3.23.36 to 3.23.39 or Greater

[Egor Egorov]

Kevin,

Thursday, February 14, 2002, 8:30:18 AM, you wrote:

KC Is their any special instructionsfor upgrading within versions.  A software
KC vendor says I need to be at 3.23.39 or greater for their product to
KC function.  I am at 3.23.36.  When I try to upgrade with rpm -Uvh her is my
KC results:

KC [root@chilly admin]# rpm -Uvh MySQL-3.23.48-1.i386.rpm
KC error: failed dependencies:
KC MySQL-server conflicts with mysql-server-3.23.36-1
KC [root@chilly admin]#

It looks like you are trying to install MySQL binary distribution over
RedHat binary distribution...

Please dump your databases using mysqldump, then completely erase the MySQL
installation:

 rpm -qa | grep -i mysql

See the list of MySQL packages and remove them. Then install the
binary distribution from MySQL, and put your dumped tables back.





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



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

mySQL and Firewall

[Victoria Reznichenko]

Brumpton,

Thursday, February 14, 2002, 10:47:14 AM, you wrote:

BR I am having a problem accessing my SQL from our webserver.   Between the
BR two is firewall.  All the ports between the SQL server and firewall are
BR currently blocked. 
BR except port 3306 which is open.
 
BR However, my I can't connect to the mySQL server.  Is there anything I
BR can do to test or what other ports do I need open?

Are you sure that port 3306 is not blocked? Is it really the 3306 port that MySQL is 
listening to? 
Please, check your permissions to connect to the MySQL server.

BR Richard




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




-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Updating SuSE 7.3

[Victoria Reznichenko]

Jochen,

Thursday, February 14, 2002, 8:52:01 AM, you wrote:

JK Can I use the RedHat-rpms on mysql.com to update a SuSE 7.3 prof or 
JK do I need to compile it from source with some parameters?
JK I run 3.23.44 max and I want to update to 3.23.48 max

Yes, you can use MySQL (rpm) for upgrade.




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




-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

re-ordering rows

[Bryan McCloskey]

Greetings,

How do I get the rows in a table to be in a different
order? I know I can sort a SELECT statement with an
ORDER BY clause, but how do I make this a permanent
adjustment to the table, so that all future SELECTs
will produce ordered data? How does MySQL know what
orders the rows are in (how are they actually stored),
and how can I change that?

Here's what's happening: say I have a table with an ID
field like this:

ID
--
1
2
3
4
5

Then I delete a few rows:

ID
--
1
2
5

Now, if I insert new rows, they come out like this:

ID
--
1
2
7
6
5

How do I get the table so that the inherent order of
the rows (not just the output of a SELECT query with
an ORDER BY) is numerical?

-b

=
---
Schrodinger may have slept here.
---

__
Do You Yahoo!?
Send FREE Valentine eCards with Yahoo! Greetings!
http://greetings.yahoo.com

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

insert picture file in MySQL (+php)

[Reuben D Budiardja]

Hi, 
I remember reading article about how to use MySql table as a place to put 
picture file. I can't find that article now. Can anyone help me with this? or 
point me to URL or something. I am using php as the front end.

Thanks.
Reuben D. Budiardja

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: insert picture file in MySQL (+php)

[Todd Williamsen]

Reuben,

You can store the images into a database, but no one recommends it.
What I do, is actually store the location of the file in the database
then have PHP open it.  

-Original Message-
From: Reuben D Budiardja [mailto:[EMAIL PROTECTED]] 
Sent: Thursday, February 14, 2002 9:08 AM
To: [EMAIL PROTECTED]
Subject: insert picture file in MySQL (+php)



Hi, 
I remember reading article about how to use MySql table as a place to
put 
picture file. I can't find that article now. Can anyone help me with
this? or 
point me to URL or something. I am using php as the front end.

Thanks.
Reuben D. Budiardja

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail
[EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: insert picture file in MySQL (+php)

[Reuben D Budiardja]

On Thursday 14 February 2002 10:16 am, Todd Williamsen wrote:
 Reuben,

 You can store the images into a database, but no one recommends it.
 What I do, is actually store the location of the file in the database
 then have PHP open it.

Any reason why it is not recommended? 
I was going to do it that way (store the location of the file only), and have 
php open it and then use something like IMG SRC=$location.

But the problem is, that way everyone who knows a bit about html can use view 
source and know the locations of the image file, and can open it directly. 
It's a problem for my particular case because we want to restrict access to 
the images, and only certain people can see certain image, and so on. 

The only I can think about right now to achieve that is to store the image 
directly in the table. That's why I asked.

Thanks.
Reuben D. Budiardja




 -Original Message-
 From: Reuben D Budiardja [mailto:[EMAIL PROTECTED]]
 Sent: Thursday, February 14, 2002 9:08 AM
 To: [EMAIL PROTECTED]
 Subject: insert picture file in MySQL (+php)




-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

re-ordering rows

[Victoria Reznichenko]

Bryan,

Thursday, February 14, 2002, 4:54:11 PM, you wrote:

BM Greetings,

BM How do I get the rows in a table to be in a different
BM order? I know I can sort a SELECT statement with an
BM ORDER BY clause, but how do I make this a permanent
BM adjustment to the table, so that all future SELECTs
BM will produce ordered data? How does MySQL know what
BM orders the rows are in (how are they actually stored),
BM and how can I change that?

You should use indexes. Look at: http://www.mysql.com/doc/M/y/MySQL_indexes.html

BM Here's what's happening: say I have a table with an ID
BM field like this:

BM ID
BM --
BM 1
BM 2
BM 3
BM 4
BM 5

BM Then I delete a few rows:

BM ID
BM --
BM 1
BM 2
BM 5

BM Now, if I insert new rows, they come out like this:

BM ID
BM --
BM 1
BM 2
BM 7
BM 6
BM 5

BM How do I get the table so that the inherent order of
BM the rows (not just the output of a SELECT query with
BM an ORDER BY) is numerical?

BM -b


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




-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: insert picture file in MySQL (+php)

[Todd Williamsen]

The two biggest reasons why 

1.  Bloats the database
2.  Slow at retrieving the images

-Original Message-
From: Reuben D Budiardja [mailto:[EMAIL PROTECTED]] 
Sent: Thursday, February 14, 2002 9:30 AM
To: Todd Williamsen; [EMAIL PROTECTED]
Subject: Re: insert picture file in MySQL (+php)


On Thursday 14 February 2002 10:16 am, Todd Williamsen wrote:
 Reuben,

 You can store the images into a database, but no one recommends it. 
 What I do, is actually store the location of the file in the database 
 then have PHP open it.

Any reason why it is not recommended? 
I was going to do it that way (store the location of the file only), and
have 
php open it and then use something like IMG SRC=$location.

But the problem is, that way everyone who knows a bit about html can use
view 
source and know the locations of the image file, and can open it
directly. 
It's a problem for my particular case because we want to restrict access
to 
the images, and only certain people can see certain image, and so on. 

The only I can think about right now to achieve that is to store the
image 
directly in the table. That's why I asked.

Thanks.
Reuben D. Budiardja




 -Original Message-
 From: Reuben D Budiardja [mailto:[EMAIL PROTECTED]]
 Sent: Thursday, February 14, 2002 9:08 AM
 To: [EMAIL PROTECTED]
 Subject: insert picture file in MySQL (+php)




-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail
[EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Using password: _No_

[Cliff]

Hi,
After a couple helpful answers to my post, I realized I wasn't going
crazy, but that the root probably had a password and I just didn't know
it.  Found this documentation which wasn't very clear except for the
comments at the bottom of the page.  
  
http://www.mysql.com/doc/R/e/Resetting_permissions.html

1. /usr/local/mysql/bin/safe_mysqld --skip-grant-tables 

2. /usr/local/mysql

3. use mysql;

4. update user set password = password('...') where user = 'root'
and host='localhost'; 

5. Stop and Start the MySQL server.

Thanks very much for the help.


Abdulhakeem wrote:
 
 No you don't have to re-install.You should use this  command to start the
 mysql server;
 etc/init.d/mysqld start
 Then use this command to connect to the server
 mysql
 However if you are logged in as a different user other than root you must
 specfy your user name,host name and password by using the following command
 mysql -h localhost -u user -p mypass
 I hope this helps,please let me know as i am new to mysql too(1 week)
 

 --
 Atoyebi  Abdulhakeem (MCSE)
 [EMAIL PROTECTED]  www.artsinscience.com
 
 -Original Message-
 From: Cliff [mailto:[EMAIL PROTECTED]]
 Sent: Wednesday, February 13, 2002 4:18 AM
 To: [EMAIL PROTECTED]
 Subject: Using password: _No_
 
 Hello,
 Can anyone help?
 When trying to run mysql I get an ERROR 1045: Access denied for user:
 'root@localhost' (Using password: No).
 In the documentation it mentions 'Using password: YES'.  Have tried
 starting mysql with --skip-grant-tables and then mysqladmin
 flush-privileges which doesn't seem to do anything.
 What does the Password No mean?  How do I correct it short of
 reinstallation?   Well, I don't know maybe I need to start over yet
 again.
 
 Thanks very much for any help,
 Cliff
 
 -
 Before posting, please check:
http://www.mysql.com/manual.php   (the manual)
http://lists.mysql.com/   (the list archive)
 
 To request this thread, e-mail [EMAIL PROTECTED]
 To unsubscribe, e-mail
 [EMAIL PROTECTED]
 Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: insert picture file in MySQL (+php)

[Eric Torr Klopper]

Howdy

 You can store the images into a database, but no one recommends it.
 What I do, is actually store the location of the file in the database
 then have PHP open it.

How would you go about doing this. Could somebody send me some sample code and also 
explain how to get the image link into the database so I can access the image. I'm 
fairly new to MySql and 
PHP so any help on this will be appreciated.

Cheers
Eric



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: [Repost: Mysql Replication Problems]

[Tobias Eggendorfer]

Hi,

On 13 Feb 2002, at 12:44, Henning Sprang wrote:
 Was something wrong with this post or is it really such a weird
 problem that there is neither a solution nor anything i could do to
 solve it, nor anything i could do to find out more about it?
Well. Replication is somewhat weird. ;-)

 Slave:  error running query 'drop table tropon_base.TB_Tage,tropon_001.TB_Tage' 
020209  3:49:32  Error
 running query, slave aborted. Fix the problem, and re-start the slave
 thread with mysqladmin start-slave. 
 ERROR: 1051  Unknown table 'TB_Tage,TB_Tage'

Sounds, as if you were doing some cross database updates / 
inserts. This might lead to problems if you only replicate some of 
the databases on the master.

So, if there is a Database A, B, C, D on master each with tables 
A1,A2,A3 or B1,B2,B3 and so on, and you only replicate A and B 
and you do:
use A;
insert into C.C1 (a,b,c) VALUES (1,2,3);
you'll get this error. 

You might avoid it with replicate-wild-ignore-table=C.*

More details in:
http://www.mysql.com/documentation/mysql/bychapter/manual_My
SQL_Database_Administration.html#Replication_Options

But note that:
use C;
insert into A.A1 (a,b,c) VALUES (1,2,3);
would not be replicated to the slave, if you choosed to replicate 
only A and B. Even though it affects A. (Strange, but that's the way 
live goes). At least with MySQL 3.23.36

So best is to avoid cross-databases actions except for SELECT 
with replication enabled.

Bye

  Tobias

---
Tobias Eggendorfer
E-Mail: [EMAIL PROTECTED]

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

DB for Palm

[jhsena]

As some people knows, Oracle 9.0 has a version to Palm OS and privete
driver for the exchange. I'd like to know if MuSQL or even other DB like
PostrgreSQL has a kind of project in mind? Or is there any academic or an
Open Source project to DB for PALM OS?

Please PVT-ME,

Thanks



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Help Regarding datadir configuration

[Miguel Angel Solorzano]

At 16:51 14/02/2002 +0530, Asit Satpathy wrote:
Hi!

Mysql pick up the database list from its default datadir C:/mysql/data
as it is mentioned in
my.ini file in window.
but i want to use my own directory which contains some database , if i
am setting  the datadir into my
directory i am able to use my own directory to which i have to copy the
default mysql database;

i want to use both data directory of mysql as well as my default
directory. if possible then please send a reply how
can i set both the directory to datadir. i am sending my.ini file which
is being used.

You only can use one basedir and one datadir in the my.ini file. However
if you want to have others databases in another directory, you can use
the symbolic-link feature (read the Manual, how to handle this on Windows).

Regards,
Miguel


#This File was made using the WinMySQLAdmin 1.3 Tool
#2/4/02 12:14:58 PM

#Uncomment or Add only the keys that you know how works.
#Read the MySQL Manual for instructions

[mysqld]
basedir=C:/mysql
#bind-address=164.100.20.26
datadir=C:/mysql/data
datadir=C:/asitdatabase
#language=C:/mysql/share/your language directory
#slow query log#=
#tmpdir#=
#port=3306
#set-variable=key_buffer=16M

[WinMySQLadmin]
Server=C:/mysql/bin/mysqld-max-nt.exe
user=asit
password=asit
QueryInterval=10

   Regards
Asit


-
Before posting, please check:
http://www.mysql.com/manual.php   (the manual)
http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

-- 
For technical support contracts, goto https://order.mysql.com/
__  ___ ___   __
   /  |/  /_ __/ __/ __ \/ /Miguel A. Solórzano [EMAIL PROTECTED]
  / /|_/ / // /\ \/ /_/ / /__   MySQL AB, FullTime Developer
/_/  /_/\_, /___/\___\_\___/   Mogi das Cruzes - São Paulo, Brazil
___/   www.mysql.com


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: re-ordering rows

[Tod Harter]

On Thursday 14 February 2002 09:54, Bryan McCloskey wrote:
 Greetings,

 How do I get the rows in a table to be in a different
 order? I know I can sort a SELECT statement with an
 ORDER BY clause, but how do I make this a permanent
 adjustment to the table, so that all future SELECTs
 will produce ordered data? How does MySQL know what
 orders the rows are in (how are they actually stored),
 and how can I change that?

 Here's what's happening: say I have a table with an ID
 field like this:

 ID
...

Well, in most RDBMS you would use a VIEW to accomplish that, but as MySQL has 
no views, you simply MUST use an ORDER BY when you want a sorted result set! 
No database guarantees any natural order to the way it stores data in 
tables. Some products do let you specify an index as defaulting to ascending 
or descending, but you STILL have to say ORDER BY to get sorting in the first 
place.

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Does delete from .. where (condition) use an index?

[Heikki Tuuri]

Hi!

I tested with .48 and it seems that if the estimator thinks you are going to
delete  10 % of the rows in your table, it scans the whole table.

In .47 this % limit is smaller, because I changed .48 to favor more index
searches over table scans.

But I tested with a very uniform table. Your table is less uniform and the
estimator may be less accurate.

Make sure you commit your transactions often, because only then purge can
remove delete marked records from indexes. If purge cannot run, you may have
 10 % of records delete marked but not yet removed at the old end of the
index. That will mislead the estimator.

Best regards,

Heikki Tuuri
Innobase Oy
---
Order technical MySQL/InnoDB support at https://order.mysql.com/
See http://www.innodb.com for the online manual and latest news on InnoDB


-Original Message-
From: Eric Mayers [EMAIL PROTECTED]
To: Heikki Tuuri [EMAIL PROTECTED]; [EMAIL PROTECTED]
[EMAIL PROTECTED]
Date: Thursday, February 14, 2002 3:44 AM
Subject: RE: Does delete from .. where (condition) use an index?


Heikki,

mysql EXPLAIN SELECT * FROM Syslog WHERE datestamp = 2002021310712;
++--+---+--+-+--+-+-
---+
| table  | type | possible_keys | key  | key_len | ref  | rows|
Extra  |
++--+---+--+-+--+-+-
---+
| Syslog | ALL  | ds_index  | NULL |NULL | NULL | 2204932 |
where used |
++--+---+--+-+--+-+-
---+
1 row in set (0.00 sec)

How can I force it to use the index?

(the indexes look like:

mysql show index from Syslog;
+++--+--+-+-
--+-+--++-+
| Table  | Non_unique | Key_name | Seq_in_index | Column_name |
Collation | Cardinality | Sub_part | Packed | Comment |
+++--+--+-+-
--+-+--++-+
| Syslog |  1 | ds_index |1 | datestamp   | A
|5893 | NULL | NULL   | |
| Syslog |  1 | ca_index |1 | caid| A
|9730 | NULL | NULL   | |
+++--+--+-+-
--+-+--++-+
2 rows in set (6.90 sec)

)

Thanks,
Eric


 -Original Message-
 From: Heikki Tuuri [mailto:[EMAIL PROTECTED]]
 Sent: Wednesday, February 13, 2002 5:34 PM
 To: [EMAIL PROTECTED]
 Subject: Re: Does delete from .. where (condition) use an index?


 Eric,

 MySQL is very pessimistic about key accesses: it assumes many
 of them cause
 a random disk read.

 Please print

 EXPLAIN SELECT * FROM Syslog WHERE datestamp  ...

 to see how it accesses the table.

 I have tuned the optimization in 3.23.48 so that it would favor index
 searches more often. Please try also with 3.23.48.

 Regards,

 Heikki
 Innobase Oy

 Eric Mayers wrote in message ...
 I haven't been able to find details about how/if MySQL (InnoDB)
 optimizes deletes.  Does it use indexes?  Can I force it to use an
 index?
 
 My table is defined as:
 
 CREATE TABLE Syslog(
   id int(11) not null,
   datestamp timestamp(14),
   message char(255),
   KEY ds_index(datestamp),
   KEY id_index(id)
 ) type=InnoDB;
 
 
 There are about 15 mil rows, and I'm just trying to delete a small
 portion of them (0.05% perhaps).  My delete looks like:
 
 DELETE FROM Syslog WHERE datestamp = 2002021310712;
 
 (the magic number there comes from SELECT (min(datestamp) +
 30) AS min
 FROM Syslog in a previous query).
 
 .. and its taking a very long time.. watching the innodb monitor I'm
 seeing a large number of reads/s (29000) and a small number
 of deletes/s
 (10) which makes me believe its not using the index..
 
 Any way to improve this?
 
 Eric Mayers
 Software Engineer I




 -
 Before posting, please check:
http://www.mysql.com/manual.php   (the manual)
http://lists.mysql.com/   (the list archive)

 To request this thread, e-mail [EMAIL PROTECTED]
 To unsubscribe, e-mail
 [EMAIL PROTECTED]
 Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php





-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: insert picture file in MySQL (+php)

[Guy Davis]

On Thu, 2002-02-14 at 08:42, Eric Torr Klopper wrote:
 How would you go about doing this. Could somebody send me some sample
code and also explain how to get the image link into the database so I
can access the image. I'm fairly new to MySql and 
 PHP so any help on this will be appreciated.

I use this for the photo album on my web site (http://www.guydavis.ca).
It's not a high traffic site by any means, so performance isn't an
issue.  Can't show you PHP, but here's what I did in an Java servlet. 
It handles uploaded (POST) images from friends.  byte_out is a
ByteArrayOutputStream.  imageIcon is an Icon that I create using JDK
1.4's headless AWT support to allow me to calculate the image's
thumbnail dimensions.

  Connection con = WebUtils.getConnection();
  String st = INSERT INTO photos (location, description, date_shot,
filename, mimetype, img_data, ;
  st += height, width, thumb_height, thumb_width, category, access,
title) ;
  st += VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);
  PreparedStatement stmnt = con.prepareStatement(st);
  stmnt.setString(1, ((String) params.get(location)));
  stmnt.setString(2, ((String) params.get(description)));
  stmnt.setString(3, date);
  stmnt.setString(4, (image.getFileName()));
  stmnt.setString(5, image.getContentType());
  stmnt.setBytes(6, byte_out.toByteArray());
  stmnt.setInt(7, imageIcon.getIconHeight());
  stmnt.setInt(8, imageIcon.getIconWidth());
  stmnt.setInt(9, thumb_height);
  stmnt.setInt(10, thumb_width);
  stmnt.setInt(11, Integer.parseInt((String) params.get(category)));
  stmnt.setString(12, (String) params.get(access));
  stmnt.setString(13, ((String) params.get(title))); 
  stmnt.executeUpdate();

Given that most of the photos posted by myself and friends are personal,
I needed an access control system and I wanted something more robust
than a publicly accessible image with a random name.  The only way to do
that is to have a servlet or CGI read in a image from disk or database
(where's it's not web accessible) and write the data out to the client.

Here's the relevant code from my PhotoServlet:

  String query = SELECT * FROM photos WHERE id=+id;
  ResultSet rs = WebUtils.doSQL(query);
  if (!rs.first()) {
displayError(request, response);
return;
  }  

  // if it's marked personal, only logged in people should see it
  if ((rs.getString(access).equalsIgnoreCase(Personal))  
(session.getValue(login) == null)) {
displayError(request, response);
return;
}

  if (rs.getString(filename).toUpperCase().indexOf(.GIF) =
0)  
response.setContentType(image/gif);   
  else
response.setContentType(image/jpeg);  

  Blob img_blob = rs.getBlob(img_data);
  InputStream in = img_blob.getBinaryStream();
  while (in.available()  0)
  buf_out.write(in.read());
  buf_out.flush();
  buf_out.close();  


Here's the CREATE TABLE for my photos table:

photos | CREATE TABLE `photos` (
  `id` smallint(5) unsigned NOT NULL auto_increment,
  `location` varchar(255) NOT NULL default '',
  `description` mediumtext NOT NULL,
  `date_shot` date NOT NULL default '-00-00',
  `filename` varchar(255) NOT NULL default '',
  `mimetype` varchar(50) NOT NULL default '',
  `height` smallint(5) unsigned default NULL,
  `width` smallint(5) unsigned default NULL,
  `thumb_height` smallint(5) unsigned default NULL,
  `thumb_width` smallint(5) unsigned default NULL,
  `img_data` longblob,
  `category` smallint(5) unsigned default NULL,
  `access` enum('Public','Personal') default 'Personal',
  `title` varchar(255) default NULL,
  PRIMARY KEY  (`id`)
) TYPE=MyISAM


I don't see why you shouldn't be able to do something similar in PHP.
Hope this helps.

-- 
Guy Davis   Phone:   (403) 301-3426   
Pason Systems   Fax: (403) 301-3499
PGP: 65BA 484B 0B96 5F3B 4D40  DCA2 B2AE 6B5A F52B 1445

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Undefined reference to 'btr_search_check_guess' ??

[Lou Picciano]

Dear Listers:

In configuring for a 4.0.1 compile, all goes well.

The compiler, however, reports (apparently in the InnoBase section of the
compile):

../innobase/btr/libbtr.a(btr0sea.o): In nunction 'btr_search_guess_on_hash':
btr0sea.o(.text+0x1c58): undefined reference to 'btr_search_check_guess'
collect2: ld returned 1 exit status
make[3]: *** [mysqld] Error 1
make[3]: Leaving directory '/usr/local/src/mysql-4.0.1-alpha/sql'
make[2]: *** [all-recursive] Error 1
make[2]: Leaving directory '/usr/local/src/mysql-4.0.1-alpha/sql'
make[1]: *** [all-recursive] Error 1
make[1]: Leaving directory '/usr/local/src/mysql-4.0.1-alpha
make: *** [all-recursive-am] Error 2

I am no expert in using gcc, so have no idea how to recover from these
messages.

Does anyone have a hint?

Thanks - Lou


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: SQL Help, Please...

[Tod Harter]

On Thursday 14 February 2002 07:58, Carl Shelbourne wrote:
 Hi

 I am trying to write an auction script that uses mysql as its backend. Each
 auction can have upto 25 sub auctions(cells) taking place.

 I'm trying to query the DB to give me a list of all the successfull bids
 for each cell, for each auction...

 SELECT b.auctionId, b.cellId, MAX(b.bid) as bid, b.bidderId FROM sa_bid as
 b, sa_auction AS a WHERE (a.state='active' AND b.auctionId=a.Id) GROUP BY
 auctionId,cellId ORDER BY bidTime DESC

 This is further complicated in so much that multiple MAX bids may exist at
 the same value, but, only the earliest should be returned for each cell.

 Which is returning some of the columns correctly, namely auctionid, cellid
 and bid, but it does not return the bidderId correctly.

 Can anybody help?

Your query is simply NOT relationally correct... The database has NO way to 
know WHICH bidder id to return in a given group. Suppose that for a given 
auctionid and cellid there might be 12 different bidders. You are telling the 
database engine to return ONE record for that group of 12 rows, so which 
bidderid will it use? The correct behaviour would be for MySQL to reject the 
query, it simply cannot be properly processed. Unfortunately I've found that 
MySQL doesn't behave correctly in these cases, instead it just returns one of 
the possible bidderid values at random. 

Your query would be technically correct if you used a summary function on 
bidderid, like MAX(b.bidderId) or somesuch. The rule is that the returned 
columns in a GROUP BY must either by mentioned in the GROUP BY section of the 
query itself, OR they must be the results of a summary function. Any other 
use is not correct for the reason stated above.

In other words, you need to rewrite your application logic. Most likely you 
will need to add the b.bidderId to the GROUP BY and have the program walk 
through the result set and do further sumarization on its own. Alternately 
you might be able to craft an SQL statement that gets you what you want, but 
without correlated subqueries it is going to be difficult or impossible. I've 
had this same sort of problem myself... 

 Cheers

 Carl


 #
 # Table structure for table `sa_auction`
 #

 CREATE TABLE sa_auction (
   id int(11) NOT NULL auto_increment,
   start datetime NOT NULL default '-00-00 00:00:00',
   end datetime NOT NULL default '-00-00 00:00:00',
   state enum('waiting','active','expired') NOT NULL default 'waiting',
   PRIMARY KEY  (id)
 ) TYPE=MyISAM;

 #
 # Dumping data for table `sa_auction`
 #

 INSERT INTO sa_auction (id, start, end, state) VALUES (1, '2002-01-23
 21:42:50', '2002-04-30 11:30:00', 'active'); INSERT INTO sa_auction (id,
 start, end, state) VALUES (2, '2002-01-23 21:42:50', '2002-02-09 06:30:00',
 'expired'); INSERT INTO sa_auction (id, start, end, state) VALUES (3,
 '2002-03-23 21:42:50', '2002-07-05 09:00:00', 'waiting'); INSERT INTO
 sa_auction (id, start, end, state) VALUES (4, '2002-03-23 21:42:50',
 '2002-08-01 11:30:00', 'waiting'); #
 

 #
 # Table structure for table `sa_bid`
 #

 CREATE TABLE sa_bid (
   id int(11) NOT NULL auto_increment,
   auctionId int(11) NOT NULL default '0',
   cellId tinyint(4) NOT NULL default '0',
   bid int(11) NOT NULL default '0',
   bidderId mediumint(9) NOT NULL default '0',
   bidtime timestamp(14) NOT NULL,
   PRIMARY KEY  (id),
   UNIQUE KEY id (id),
   KEY id_2 (id)
 ) TYPE=MyISAM;

 #
 # Dumping data for table `sa_bid`
 #

 INSERT INTO sa_bid (id, auctionId, cellId, bid, bidderId, bidtime) VALUES
 (1, 1, 5, 1, 1, 20020128225421); INSERT INTO sa_bid (id, auctionId, cellId,
 bid, bidderId, bidtime) VALUES (2, 1, 5, 2, 2, 20020128225424); INSERT INTO
 sa_bid (id, auctionId, cellId, bid, bidderId, bidtime) VALUES (3, 1, 17,
 15, 2, 20020213214856); INSERT INTO sa_bid (id, auctionId, cellId, bid,
 bidderId, bidtime) VALUES (4, 1, 5, 3, 4, 20020213215649);



 
 This e-mail and any attachments are confidential.  If you are not the
 intended recipient, please notify us immediately by reply e-mail and then
 delete this message from your system. Do not copy this e-mail or any
 attachments, use the contents for any purpose, or disclose the contents to
 any other person: to do so could be a breach of confidence.

 -
 Before posting, please check:
http://www.mysql.com/manual.php   (the manual)
http://lists.mysql.com/   (the list archive)

 To request this thread, e-mail [EMAIL PROTECTED]
 To unsubscribe, e-mail
 [EMAIL PROTECTED] Trouble
 unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, 

RE: insert picture file in MySQL (+php)

[John Lodge]

Reuben,

Try http://www.phpbuilder.com/columns/florian19991014.php3
 
John Lodge

-Original Message-
From: Reuben D Budiardja [mailto:[EMAIL PROTECTED]]
Sent: Thursday, February 14, 2002 3:08 PM
To: [EMAIL PROTECTED]
Subject: insert picture file in MySQL (+php)



Hi, 
I remember reading article about how to use MySql table as a place to put 
picture file. I can't find that article now. Can anyone help me with this?
or 
point me to URL or something. I am using php as the front end.

Thanks.
Reuben D. Budiardja

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail
[EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

FW: Janus GridEX Error with mySQL

[Jennifer Keenan]

Has anyone ever tried to use the Janus GridEX control with mySQL?  I get an
ODBC Driver does not support the requested properties error.  

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: Does delete from .. where (condition) use an index?

[Eric Mayers]

Heikki, 

Here is what you asked for:

mysql EXPLAIN SELECT * FROM Syslog USE INDEX(ds_index) WHERE datestamp
= 20020213185230;
++--+---+--+-+--+--+
+
| table  | type | possible_keys | key  | key_len | ref  | rows | Extra
|
++--+---+--+-+--+--+
+
| Syslog | ALL  | ds_index  | NULL |NULL | NULL | 4719 | where
used |
++--+---+--+-+--+--+
+
1 row in set (0.00 sec)

And here is a single InnoDB Monitor output:

=
020214  9:07:25 INNODB MONITOR OUTPUT
=
--
SEMAPHORES
--
OS WAIT ARRAY INFO: reservation count 85376, signal count 83573
Mutex spin waits 107640, rounds 1033422, OS waits 1
RW-shared spins 34894, OS waits 17557; RW-excl spins 34957, OS waits
34400

TRANSACTIONS

Trx id counter 0 10816708
Purge done for trx's n:o  0 475530 undo n:o  0 0
Total number of lock structs in row lock hash table 13009
---TRANSACTION 0 10816707, OS thread id 876555, not started, runs or
sleeps
MySQL thread id 205, query id 12109132 localhost root
INSERT INTO logs.Syslog set caid='630', datestamp=NULL,
message='4187481481 1481418748 418748 418748
---TRANSACTION 0 5633944, OS thread id 1445901 updating or deleting,
active, runs or sleeps, has 13010 lock struct(s), undo log entries
650408
MySQL thread id 344, query id 6056015 localhost root updating
DELETE FROM logs.Syslog where (datestamp = (20020213190141 + (3600)))

FILE I/O

I/O thread 0 state: waiting for i/o request
I/O thread 1 state: waiting for i/o request
I/O thread 2 state: waiting for i/o request
I/O thread 3 state: waiting for i/o request
Pending normal aio reads: 0, aio writes: 0,
 ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 1
1164459 OS file reads, 1266570 OS file writes, 100823 OS fsyncs
23.25 reads/s, 27.88 writes/s, 2.06 fsyncs/s
-
INSERT BUFFER
-
Ibuf for space 0: size 518, free list len 272, seg size 791,
9152117 inserts, 8984010 merged recs, 675487 merges
---
LOG
---
Log sequence number 0 4190770295
Log flushed up to   0 4190657162
Last checkpoint at  0 4178363488
1 pending log writes, 0 pending chkp writes
52481 log i/o's done, 0.88 log i/o's/second
--
BUFFER POOL AND MEMORY
--
Total memory allocated 33029216; in additional pool allocated 269312
Free list length  121
LRU list length   856
Flush list length 805
Buffer pool size  1024
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 1256311, created 255856, written 1602244
24.31 reads/s, 3.88 creates/s, 36.00 writes/s
Buffer pool hit rate 994 / 1000
--
ROW OPERATIONS
--
1 queries inside InnoDB; main thread: flushing log
Number of rows inserted 10806106, updated 0, deleted 806508, read
6850315
181.44 inserts/s, 0.00 updates/s, 11.69 deletes/s, 11.69 reads/s

END OF INNODB MONITOR OUTPUT



Eric


 -Original Message-
 From: Heikki Tuuri [mailto:[EMAIL PROTECTED]]
 Sent: Wednesday, February 13, 2002 5:59 PM
 To: Eric Mayers; [EMAIL PROTECTED]
 Subject: Re: Does delete from .. where (condition) use an index?
 
 
 Eric,
 
 print what
 
 EXPLAIN SELECT * FROM Syslog USE INDEX(ds_index) WHERE ...
 
 says.
 
 Please also show what the InnoDB monitor prints.
 
 Regards,
 
 Heikki
 
 
 -Original Message-
 From: Eric Mayers [EMAIL PROTECTED]
 To: Heikki Tuuri [EMAIL PROTECTED]; [EMAIL PROTECTED]
 [EMAIL PROTECTED]
 Date: Thursday, February 14, 2002 3:44 AM
 Subject: RE: Does delete from .. where (condition) use an index?
 
 
 Heikki,
 
 mysql EXPLAIN SELECT * FROM Syslog WHERE datestamp = 2002021310712;
 ++--+---+--+-+--+
 -+-
 ---+
 | table  | type | possible_keys | key  | key_len | ref  | rows|
 Extra  |
 ++--+---+--+-+--+
 -+-
 ---+
 | Syslog | ALL  | ds_index  | NULL |NULL | NULL | 2204932 |
 where used |
 ++--+---+--+-+--+
 -+-
 ---+
 1 row in set (0.00 sec)
 
 How can I force it to use the index?
 
 (the indexes look like:
 
 mysql show index from Syslog;
 +++--+--+
 -+-
 --+-+--++-+
 | Table  | Non_unique | Key_name | Seq_in_index | Column_name |
 Collation | Cardinality | Sub_part | Packed | Comment |
 +++--+--+
 -+-
 --+-+--++-+
 | Syslog |  1 | ds_index |1 | datestamp   | A
 |5893 | NULL | NULL   | |
 | Syslog |  1 | ca_index |1 | caid| A
 | 

date comes out as '0000-00-00'

[Stancescu, Catalina]

 Hi ,

 I have a servlet in which I connect through a mysql driver to the
database. The servlet has a string that represents the date in the
format 'MM/DD/YY'.

 When I am trying to insert this date, I get sometimes the correct date,
sometimes I get the '-00-00'.

Here is the code:
 String sql_1 =  INSERT INTO bookmgr.copy (bookid, emp_purchaser,
date_expensed, emp_holder) VALUES (?,?,?,?);
 ps = myConnection.prepareStatement(sql_1); 
// inserting records
ps.setInt(1, bookid);
ps.setInt(2, userId);
ps.setString(3, expensed_date);
ps.setInt(4, userId);
ps.executeUpdate();

 I understand that this particular date '-00-00' comes up when you
have an invalid date. But I am checking that I get the correct date in
the servlet from the jsp (which comes from  a calendar)  and again,
sometimes it works, sometimes it doesn't.

 Thanks for any help,
 Catalina

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Manual references to safe_mysqld

[Michael Widenius]

Hi!

(Please continue this thread only on the internals list)

 Ken == Ken Menzel [EMAIL PROTECTED] writes:

Ken Hi Monty and Jeremy,

 We have been a bit reluctant to update the manual, as most of our
 users are still using 3.23 and could easily be confused by this.
 
 I think we should wait until 4.0 goes into beta before doing the
Ken update.

Ken If I may suggest,  Could we have two manuals online then?  One for
Ken 4.x,  and one for version 3.  I think that this would avoid confusion.
Ken Furthurmore,  I will volunter some time from my web staff to help
Ken maintain this (I do understand it is almost double work to have two
Ken manuals online) if that would help any.

I don't know if this is really necessary.  As long as we are clearly
marking the 4.0 features as such in the manual, we should be ok.

Ken With all the great stuff (or differences) in 4.0 that is not (or
Ken different) in 3.23.xx I feel it can already be confusing!  IE
Ken (Referential integrity,  Replication, Cascadining Deletes, UNIONS,
Ken FULLTEXT Features, Table to table UPDATE syntax, table details,
Ken security(SSH?), etc (long list!) ).

Ken MySQL 4.0.1 is running VERY well here, but I think it may be confusing
Ken even during the beta stage to have only the beta manual
Ken online/available for download.  I think both should be there.

Ken The other suggestion would be to make the online manual only
Ken the -stable manual,  I know how to build the manual for 4.0 from the
Ken source,  but I personally like the idea of both the alpha/beta and the
Ken stable manual being available!  You guy's are doing alot of great work
Ken it would be good to have it online ASAP!

As the 4.0 manual includes a lot of new stuff, that is highly relevant
also for 3.23, I don't think this is a good way to go either.

Regards,
Monty

-- 
For technical support contracts, goto https://order.mysql.com/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Mr. Michael Widenius [EMAIL PROTECTED]
 / /|_/ / // /\ \/ /_/ / /__   MySQL AB, CTO
/_/  /_/\_, /___/\___\_\___/   Helsinki, Finland
   ___/   www.mysql.com

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Need help please!

[Richard C Rossy]

 
Ok I can't seem to get this. Now is it possible to have MySQL on a other
computer and run the script.
In other words  the perl script is on foo.com and MySQL database is on
foo2.com. What is need to make the script work. What I have in the
script is:
 
Configure.pl
 
sub configure {

# CGI CONFIGURATION VARIABLES

$mailprogram = /usr/sbin/sendmail -t;
$mysqlhost = mysql.foo2.com 3306;
$mysqldatabase = matchpro;
$mysqlusername = username;
$mysqlpassword = password;
add.pl 
 
#!/usr/bin/perl -s
use Socket;
use DBI;
 
require configure.pl;
configure; 
open (HEADERHTML, $headerhtml);
@header = HEADERHTML;
close HEADERHTML;
open (FOOTERHTML, $footerhtml);
@footer = FOOTERHTML;
close FOOTERHTML;
 
$orderid = generateorderid;
$profilenumber = $orderid;
 
form_parse;
   $AdHeadline = $FORM{'AdHeadline'};
   $EmailAddress = $FORM{'EmailAddress'};
   $TelephoneAreaCode = $FORM{'TelephoneAreaCode'};
   $City = $FORM{'City'};
   $StateProvince = $FORM{'StateProvince'};
   $Zip = $FORM{'Zip'};
   $Country = $FORM{'Country'};
   $RelationshipPreference = $FORM{'RelationshipPreference'};
   $SexualPreference = $FORM{'SexualPreference'};
   $Username = $FORM{'Username'};
   $Password = $FORM{'Password'};
   $VerifyPassword = $FORM{'VerifyPassword'};
   $SmokingPreference = $FORM{'SmokingPreference'};
   $DrinkingPreference = $FORM{'DrinkingPreference'};
   $MaritialStatus = $FORM{'MaritialStatus'};
   $HaveChildren = $FORM{'HaveChildren'};
   $BodyBuild = $FORM{'BodyBuild'};
   $Height = $FORM{'Height'};
   $Religion = $FORM{'Religion'};
   $Race = $FORM{'Race'};
   $AstrologicalSign = $FORM{'AstrologicalSign'};
   $Age = $FORM{'Age'};
   $Occupation = $FORM{'Occupation'};
   $MiscComments = $FORM{'MiscComments'};
   $MiscComments =~ s/ /\|/g;
   $MiscComments =~ s/\s/\|/g;
   $MiscComments =~ s/\|+/ /g;
 
 
$dbh = DBI-connect(DBI:mysql:$mysqldatabase,
$mysqlhost,$mysqlusername, $mysqlpassword) || die(Couldn't
connect to database!\n);
print Content-type: text/html\n\n;
printheader;
errcheck;
checkuniqueusernames;
checkuniqueemails;
savedata;
printconfirmation;
printfooter;
emailmember;
 
$dbh-disconnect;
 
There is more but the script never gets past $dbh = DBI
 


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail mysql-unsubscribe-##L=##[EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: date comes out as '0000-00-00'

[John McConnell]

This doesn't really answer your question, but whenever I deal with 
dates I convert them to Unix and store them in the database, and then 
convert them back when I use them.  Sorting dates doesn't seem to 
work as well in a non-unix format, and with UNIX dates you have much 
more control over the way the date is printed out. So you could just 
print the month if you wanted, or print the date in a different 
format.  It gives a lot of flexibility.  That might be something for 
you to think about.  I'm not sure how you do that in jsp, but it's 
probably pretty easy.

As for your problem, I use php and not jsp, so I can't help...sorry.

John

  Hi ,

  I have a servlet in which I connect through a mysql driver to the
database. The servlet has a string that represents the date in the
format 'MM/DD/YY'.

  When I am trying to insert this date, I get sometimes the correct date,
sometimes I get the '-00-00'.

Here is the code:
  String sql_1 =  INSERT INTO bookmgr.copy (bookid, emp_purchaser,
date_expensed, emp_holder) VALUES (?,?,?,?);
  ps = myConnection.prepareStatement(sql_1);
   // inserting records
   ps.setInt(1, bookid);
   ps.setInt(2, userId);
   ps.setString(3, expensed_date);
   ps.setInt(4, userId);
   ps.executeUpdate();

  I understand that this particular date '-00-00' comes up when you
have an invalid date. But I am checking that I get the correct date in
the servlet from the jsp (which comes from  a calendar)  and again,
sometimes it works, sometimes it doesn't.

  Thanks for any help,
  Catalina

-
Before posting, please check:
http://www.mysql.com/manual.php   (the manual)
http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail 
[EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php


-- 

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: date comes out as '0000-00-00'

[DL Neil]

Hi Catalina,

 I have a servlet in which I connect through a mysql driver to the
database. The servlet has a string that represents the date in the
format 'MM/DD/YY'.

 When I am trying to insert this date, I get sometimes the correct date,
sometimes I get the '-00-00'.

Here is the code:
 String sql_1 =  INSERT INTO bookmgr.copy (bookid, emp_purchaser,
date_expensed, emp_holder) VALUES (?,?,?,?);
 ps = myConnection.prepareStatement(sql_1); 
// inserting records
ps.setInt(1, bookid);
ps.setInt(2, userId);
ps.setString(3, expensed_date);
ps.setInt(4, userId);
ps.executeUpdate();

 I understand that this particular date '-00-00' comes up when you
have an invalid date. But I am checking that I get the correct date in
the servlet from the jsp (which comes from  a calendar)  and again,
sometimes it works, sometimes it doesn't.


=Have I understood you correctly - that you are loading 'MM/DD/YY' dates into MySQL?

=MySQL expects to see dates in [CC]YY-MM-DD format
=See 6.2.2.2  The DATETIME, DATE, and TIMESTAMP Types

=Do you need to re-format the date-data before INSERTing?
=dn



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: date comes out as '0000-00-00'

[Cindy]

   I have a servlet in which I connect through a mysql driver to the
 database. The servlet has a string that represents the date in the
 format 'MM/DD/YY'.
 
   When I am trying to insert this date, I get sometimes the correct date,
 sometimes I get the '-00-00'.
 
 Here is the code:
   String sql_1 =  INSERT INTO bookmgr.copy (bookid, emp_purchaser,
 date_expensed, emp_holder) VALUES (?,?,?,?);
   ps = myConnection.prepareStatement(sql_1);
  // inserting records
  ps.setInt(1, bookid);
  ps.setInt(2, userId);
  ps.setString(3, expensed_date);
  ps.setInt(4, userId);
  ps.executeUpdate();
 
   I understand that this particular date '-00-00' comes up when you
 have an invalid date. But I am checking that I get the correct date in
 the servlet from the jsp (which comes from  a calendar)  and again,
 sometimes it works, sometimes it doesn't.

If you're trying to insert a date of the format mm/dd/yy into
something that's expecting [yy]yy/mm/dd, you're going to run into that
kind of random problem, most likely on days  12 (which would not be
a reasonable month value and hence invalid).  Seems to me you ahve to
reformat your date from jsp before you put it into the mysql database.

--Cindy

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

ANN: EMS MySQL Manager 1.6 released

[Igor Brynskich]

Dear Sirs and Madams,

EMS HiTech company is announcing the next version (1.6) of MySQL
Manager -- A Powerful MySQL Administration and Development Tool for
Windows95/98/ME/NT/2000/XP.

You can download the latest version from
http://www.mysqlmanager.com/download.phtml


What's new in version 1.6?

1. EMS HiTech is now an official MySQL AB Software Partner!

2. Interface localization has been implemented. Five languages -
English, French, Italian, Dutch and Russian - are included in the
installation package. Spanish localization is coming soon. To change the
program language choose the Select Program Language item of the
Options menu or select it using combo box at the Localization tab of
Environment Options form. Also you can edit the interface localization
file by yourself. Just press Shift+Ctrl+L on any form to call the
Localization Editor or create your own *.lng file based on any of the
existing ones (see $(MySQL Manager)\Languages folder to find them).
We'll be very grateful to anyone who will send us a translation of
'english.lng' to his native language different from the already
existing. Please be free to send your sugesstions at
[EMAIL PROTECTED]

3. DB Explorer: three modes of viewing table's details in SQL Assistant
were added. Now you can view table's fields, table's indices or table
status. To switch between the modes use drop down menu of View Mode
button or local menu of SQL Assistant.

4. DB Explorer: the ability of editing/dropping fields or indices was
added to SQL Assistant. Use local popup menu to edit or drop the
objects.

5. Blob Editor: the ability of viewing images in GIF format was added.
(*)

6. Query Builder: the last used query is being saved now on closing the
Builder and is being restored on the next session of the Query Builder
work. (*)

7. You can restore the default size of any child forms by pressing
Ctrl+D now.

8. Some minor bugfixes and small improvements.

(*) -- Professional Edition only


What is the EMS MySQL Manager?

EMS MySQL Manager provides you powerful and effective tools for MySQL
Server administration and objects management. Its Graphical User
Interface (GUI) allows you to create/edit of all MySQL database objects
most easy and simple way, run SQL scripts, manage users and administrate
users' privileges, visually build SQL queries, extract or print
metadata, export/import data, view/edit BLOBs and many more services
that will make you work with MySQL server as easy as you want...

Best regards,
EMS HiTech development team.
http://www.ems-hitech.com



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: re-ordering rows

[Rick Emery]

Why is the internal order important?  When SELECTing, the internal order is
of no importance to MYSQL.  It does not speed-up the query or access.  When
discussing relational database systems, all that matters is the order of
output.

-Original Message-
From: Victoria Reznichenko [mailto:[EMAIL PROTECTED]]
Sent: Thursday, February 14, 2002 9:26 AM
To: [EMAIL PROTECTED]
Subject: re-ordering rows


Bryan,

Thursday, February 14, 2002, 4:54:11 PM, you wrote:

BM Greetings,

BM How do I get the rows in a table to be in a different
BM order? I know I can sort a SELECT statement with an
BM ORDER BY clause, but how do I make this a permanent
BM adjustment to the table, so that all future SELECTs
BM will produce ordered data? How does MySQL know what
BM orders the rows are in (how are they actually stored),
BM and how can I change that?

You should use indexes. Look at:
http://www.mysql.com/doc/M/y/MySQL_indexes.html

BM Here's what's happening: say I have a table with an ID
BM field like this:

BM ID
BM --
BM 1
BM 2
BM 3
BM 4
BM 5

BM Then I delete a few rows:

BM ID
BM --
BM 1
BM 2
BM 5

BM Now, if I insert new rows, they come out like this:

BM ID
BM --
BM 1
BM 2
BM 7
BM 6
BM 5

BM How do I get the table so that the inherent order of
BM the rows (not just the output of a SELECT query with
BM an ORDER BY) is numerical?

BM -b


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




-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

small little 'bug' in newest source version

[Kedryna, Bartosz]

In mysqldump.c there is // in 2 places, older compilers don't like it,
changing to /* */ fixes the problem.

Bart Kedryna
Software Engineer
Online Support
[EMAIL PROTECTED]
(215) 386 0100 x1470
ISI
3501 Market Street
Philadelphia, PA 19104


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: re-ordering rows

[Bryan McCloskey]

You're right, it's not important how the data is
stored inside the database. I was just hoping that
there would be a way to set a default order so that I
wouldn't have to write a cumbersome ORDER BY phrase
every time I wanted to see the data. I thought that
perhaps indexes could accomplish this, somehow by
telling them to re-index the column, but perhaps not.

-b

--- Rick Emery [EMAIL PROTECTED] wrote:
 Why is the internal order important?  When
 SELECTing, the internal order is
 of no importance to MYSQL.  It does not speed-up the
 query or access.  When
 discussing relational database systems, all that
 matters is the order of
 output.

__
Do You Yahoo!?
Send FREE Valentine eCards with Yahoo! Greetings!
http://greetings.yahoo.com

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Does delete from .. where (condition) use an index?

[Heikki Tuuri]

Eric,

thank you for the printouts. Looks like MySQL refused to obey the USE INDEX
clause! Did you run the EXPLAIN on a very small table? Otherwise I do not
understand why it reports only 4700 rows.

You have a very long-running mass delete below. It has row locks on 13000
pages = 200 MB. It has delete marked (= deleted)  650 000 rows (= number of
undo log entries).

There are quite a lot of disk reads and writes per second: 23 + 28. The load
is probably disk-bound.

You have a very small buffer pool, only 1024 pages = 16 MB. I assume this is
a stress test for a very small buffer pool.

There are no dangling open transactions: only the mass delete and a single
insert exist.

In short, everything looks ok in the monitor output.

I think it is worth to try 3.23.48 because the optimizer is tuned there. I
tested deleting 15 000 rows from a 150 000 row table, and .48 chose to use
the right index.

Make sure the delete batches are small enough so that the optimizer picks
the right index. EXPLAIN SELECT is the way to study optimizer choices.

Note that because of the insert buffer, InnoDB can make inserts with less
disk i/o than deletes. In the monitor output you see 9 000 000 insert buffer
records were merged in 700 000 merges: on the average 13 records were
inserted at a time. Deleting these records will use more disk i/o because
there is no similar optimization in deletes.

Best regards,

Heikki Tuuri
Innobase Oy
---
Order technical MySQL/InnoDB support at https://order.mysql.com/
See http://www.innodb.com for the online manual and latest news on InnoDB


-Original Message-
From: Eric Mayers [EMAIL PROTECTED]
To: Heikki Tuuri [EMAIL PROTECTED]; [EMAIL PROTECTED]
[EMAIL PROTECTED]
Date: Thursday, February 14, 2002 7:28 PM
Subject: RE: Does delete from .. where (condition) use an index?


Heikki,

Here is what you asked for:

mysql EXPLAIN SELECT * FROM Syslog USE INDEX(ds_index) WHERE datestamp
= 20020213185230;
++--+---+--+-+--+--+
+
| table  | type | possible_keys | key  | key_len | ref  | rows | Extra
|
++--+---+--+-+--+--+
+
| Syslog | ALL  | ds_index  | NULL |NULL | NULL | 4719 | where
used |
++--+---+--+-+--+--+
+
1 row in set (0.00 sec)

And here is a single InnoDB Monitor output:

=
020214  9:07:25 INNODB MONITOR OUTPUT
=
--
SEMAPHORES
--
OS WAIT ARRAY INFO: reservation count 85376, signal count 83573
Mutex spin waits 107640, rounds 1033422, OS waits 1
RW-shared spins 34894, OS waits 17557; RW-excl spins 34957, OS waits
34400

TRANSACTIONS

Trx id counter 0 10816708
Purge done for trx's n:o  0 475530 undo n:o  0 0
Total number of lock structs in row lock hash table 13009
---TRANSACTION 0 10816707, OS thread id 876555, not started, runs or
sleeps
MySQL thread id 205, query id 12109132 localhost root
INSERT INTO logs.Syslog set caid='630', datestamp=NULL,
message='4187481481 1481418748 418748 418748
---TRANSACTION 0 5633944, OS thread id 1445901 updating or deleting,
active, runs or sleeps, has 13010 lock struct(s), undo log entries
650408
MySQL thread id 344, query id 6056015 localhost root updating
DELETE FROM logs.Syslog where (datestamp = (20020213190141 + (3600)))

FILE I/O

I/O thread 0 state: waiting for i/o request
I/O thread 1 state: waiting for i/o request
I/O thread 2 state: waiting for i/o request
I/O thread 3 state: waiting for i/o request
Pending normal aio reads: 0, aio writes: 0,
 ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 1
1164459 OS file reads, 1266570 OS file writes, 100823 OS fsyncs
23.25 reads/s, 27.88 writes/s, 2.06 fsyncs/s
-
INSERT BUFFER
-
Ibuf for space 0: size 518, free list len 272, seg size 791,
9152117 inserts, 8984010 merged recs, 675487 merges
---
LOG
---
Log sequence number 0 4190770295
Log flushed up to   0 4190657162
Last checkpoint at  0 4178363488
1 pending log writes, 0 pending chkp writes
52481 log i/o's done, 0.88 log i/o's/second
--
BUFFER POOL AND MEMORY
--
Total memory allocated 33029216; in additional pool allocated 269312
Free list length  121
LRU list length   856
Flush list length 805
Buffer pool size  1024
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 1256311, created 255856, written 1602244
24.31 reads/s, 3.88 creates/s, 36.00 writes/s
Buffer pool hit rate 994 / 1000
--
ROW OPERATIONS
--
1 queries inside InnoDB; main thread: flushing log
Number of rows inserted 10806106, updated 0, deleted 806508, read
6850315
181.44 inserts/s, 0.00 updates/s, 11.69 deletes/s, 11.69 reads/s

END OF INNODB MONITOR OUTPUT



Eric


 

Required files on client machine?

[Scalper]

The VB front end I built using MySQL with MyODBC works great on my local
machine after performing the binary installations. If I move the front end
to remote machine, what files are required for successful connecting? Just
those bundled with MyODBC? I am attempting to keep the install as lean as
possible.

--Craig


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: re-ordering rows

[Keith A. Calaman]

What Rick said is absolutely correct and you probably are obsessing about
something that doesn't matter.  But I would venture you are using an
auto-number field as the primary key when you could easily change it to a
function something similar to:  set ID = MAX(ID) + 1.

-Original Message-
From: Bryan McCloskey [mailto:[EMAIL PROTECTED]]
Sent: Thursday, February 14, 2002 4:03 PM
To: [EMAIL PROTECTED]
Subject: RE: re-ordering rows


You're right, it's not important how the data is
stored inside the database. I was just hoping that
there would be a way to set a default order so that I
wouldn't have to write a cumbersome ORDER BY phrase
every time I wanted to see the data. I thought that
perhaps indexes could accomplish this, somehow by
telling them to re-index the column, but perhaps not.

-b

--- Rick Emery [EMAIL PROTECTED] wrote:
 Why is the internal order important?  When
 SELECTing, the internal order is
 of no importance to MYSQL.  It does not speed-up the
 query or access.  When
 discussing relational database systems, all that
 matters is the order of
 output.

__
Do You Yahoo!?
Send FREE Valentine eCards with Yahoo! Greetings!
http://greetings.yahoo.com

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail
[EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Undefined reference to 'btr_search_check_guess' ??

[Heikki Tuuri]

Hi!


January 12, 2002:
There is a bug in some GCC compiler versions and consequently compilation of
the function btr_search_check_guess fails. Fix: replace the declaration
'UNIV_INLINE' in that function in mysql/innobase/btr/btr0sea.c by the
declaration 'static'. Fixed in 3.23.48.


Best regards,

Heikki Tuuri
Innobase Oy
---
Order technical MySQL/InnoDB support at https://order.mysql.com/
See http://www.innodb.com for the online manual and latest news on InnoDB


Lou Picciano wrote in message ...
Dear Listers:

In configuring for a 4.0.1 compile, all goes well.

The compiler, however, reports (apparently in the InnoBase section of the
compile):

../innobase/btr/libbtr.a(btr0sea.o): In nunction
'btr_search_guess_on_hash':
btr0sea.o(.text+0x1c58): undefined reference to 'btr_search_check_guess'
collect2: ld returned 1 exit status
make[3]: *** [mysqld] Error 1
make[3]: Leaving directory '/usr/local/src/mysql-4.0.1-alpha/sql'
make[2]: *** [all-recursive] Error 1
make[2]: Leaving directory '/usr/local/src/mysql-4.0.1-alpha/sql'
make[1]: *** [all-recursive] Error 1
make[1]: Leaving directory '/usr/local/src/mysql-4.0.1-alpha
make: *** [all-recursive-am] Error 2

I am no expert in using gcc, so have no idea how to recover from these
messages.

Does anyone have a hint?

Thanks - Lou




-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

FW: [PHP] MySQL question...not sure if this is the correct forum to ask.

[Rick Emery]

-Original Message-
From: Peter Ruan [mailto:[EMAIL PROTECTED]]
Sent: Thursday, February 14, 2002 4:04 PM
To: [EMAIL PROTECTED]
Subject: [PHP] MySQL question...not sure if this is the correct forum to
ask.


Hi,

Can the UPDATE statement have conditional check embedded in it?  I
have a page that displays a record (in a FORM format) that the user can
change the information on each column.  I want to check each column and
see which has been changed and update the table for entries that were
changed only.

for each column data {
  if column is changed
  then update;
  else
  do nothing;
}

Maybe I am making this too complicated than it needs and just go ahead
and update all of the columns regardless with the new values, regardless
they are actually different or not.

Thanks in advance,
-Peter




-- 
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Need help please!

[Brian Reichert]

On Thu, Feb 14, 2002 at 01:05:24PM -0500, Richard C Rossy wrote:
  
 Ok I can't seem to get this. Now is it possible to have MySQL on a other
 computer and run the script.
 In other words  the perl script is on foo.com and MySQL database is on
 foo2.com. What is need to make the script work. What I have in the
 script is:

Have you actually read the perl docs for DBI?

   DBI Class Methods

   ...

   connect

 $dbh = DBI-connect($data_source, $username, $password)
   or die $DBI::errstr;

   ...

   Examples of $data_source values are:

 dbi:DriverName:database_name
 dbi:DriverName:database_name@hostname:port
 dbi:DriverName:database=database_name;host=hostname;port=port

   There is no standard for the text following the driver
   name. Each driver is free to use whatever syntax it
   wants.

So - compare the documentation with what your effort was, and note
the difference:

 $dbh = DBI-connect(DBI:mysql:$mysqldatabase,
 $mysqlhost,$mysqlusername, $mysqlpassword) || die(Couldn't
 connect to database!\n);

-- 
Brian 'you Bastard' Reichert[EMAIL PROTECTED]
37 Crystal Ave. #303Daytime number: (603) 434-6842
Derry NH 03038-1713 USA Intel architecture: the left-hand path

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: [PHP] MySQL question...not sure if this is the correct forum to ask.

[Daniel Rosher]

What about REPLACE?

http://www.mysql.com/doc/R/E/REPLACE.html

'REPLACE works exactly like INSERT, except that if an old record in the
table has the same value as a new record on a unique index, the old record
is deleted before the new record is inserted'

Regards,
Dan

 -Original Message-
 From: Rick Emery [mailto:[EMAIL PROTECTED]]
 Sent: Friday, 15 February 2002 11:10 a.m.
 To: [EMAIL PROTECTED]
 Cc: '[EMAIL PROTECTED]'
 Subject: FW: [PHP] MySQL question...not sure if this is the correct
 forum to ask.


 -Original Message-
 From: Peter Ruan [mailto:[EMAIL PROTECTED]]
 Sent: Thursday, February 14, 2002 4:04 PM
 To: [EMAIL PROTECTED]
 Subject: [PHP] MySQL question...not sure if this is the correct forum to
 ask.


 Hi,

 Can the UPDATE statement have conditional check embedded in it?  I
 have a page that displays a record (in a FORM format) that the user can
 change the information on each column.  I want to check each column and
 see which has been changed and update the table for entries that were
 changed only.

 for each column data {
   if column is changed
   then update;
   else
   do nothing;
 }

 Maybe I am making this too complicated than it needs and just go ahead
 and update all of the columns regardless with the new values, regardless
 they are actually different or not.

 Thanks in advance,
 -Peter




 --
 PHP General Mailing List (http://www.php.net/)
 To unsubscribe, visit: http://www.php.net/unsub.php

 -
 Before posting, please check:
http://www.mysql.com/manual.php   (the manual)
http://lists.mysql.com/   (the list archive)

 To request this thread, e-mail [EMAIL PROTECTED]
 To unsubscribe, e-mail
 [EMAIL PROTECTED]
 Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php




-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

OT: Auth_MySQL

[Mike(mickalo)Blezien]

Hello All,

sorry about the off-topic... somewhat :)

We are going to be implementing the mod_auth_mysql apache module on a client's
site. And I was wondering, when using this module for password protection, and
the appropriate .htaccess file is put into the protected directory, do I need to
specify a specific host, username and password in the httpd.conf in order to
connect to the MySQL database that stores the httpd_auth table with the
usernames and passwords or does the user, where the .htaccess file is located
require access privileges to the database??

IE. user account foobar has an account setup:
/home/foobar/public_html on the server

now foobar puts a .htaccess file in their /home/foobar/public_html/members
folder... does foobar need to have to access privileges to MySQL database that
stores the httpd_auth table that stores their valid username and passwords?? Or
do I need to specify these paramaters in the httpd.conf with the Auth_MySQL_Info
directive??

Again, my apologise to the list if this is off-topic. any response, please send
directly to me then.

TIA,



Mike(mickalo)Blezien
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Thunder Rain Internet Publishing
Providing Internet Solutions that work!
http://www.thunder-rain.com
Tel: 1(225)686-2002
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: Updating SuSE 7.3

[Matthew Walker]

You /can/, but it may not work. SuSE uses a slightly different directory
structure, and RedHat RPMs may get confused. If at all possible, use
SuSE provided packages.


Matthew Walker
Ecommerce Project Manager
Mountain Top Herbs


-Original Message-
From: Victoria Reznichenko [mailto:[EMAIL PROTECTED]] 
Sent: Thursday, February 14, 2002 7:36 AM
To: [EMAIL PROTECTED]
Subject: Updating SuSE 7.3

Jochen,

Thursday, February 14, 2002, 8:52:01 AM, you wrote:

JK Can I use the RedHat-rpms on mysql.com to update a SuSE 7.3 prof or 
JK do I need to compile it from source with some parameters?
JK I run 3.23.44 max and I want to update to 3.23.48 max

Yes, you can use MySQL (rpm) for upgrade.




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




-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail
[EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php



---
Incoming mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.323 / Virus Database: 180 - Release Date: 2/8/2002
 

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.323 / Virus Database: 180 - Release Date: 2/8/2002
 

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

MySQL + Access + MyODBC + LARGE Tables

[Bill Adams]

Monty, Venu, I hope you read this... :)


I really, really want to use MySQL as the database backend for my
datawarehouse.  Mind you I have played around with merge tables quite a
bit and know that MySQL is more than up to the task.  There are numerous
(not necessarily cost related) reasons as to why MySQL is better for my
application. If it were just me, it would be a slam-dunk as I only use
perl, etc. to extract data from the database.  However most of my users
use MS Access as a front end and extraction tool.

When pulling datasets from a database, Access tries to be smart and if
there is what it thinks is a primary key on a table, it will extract the
values of the primary key for the matching records and then re-query the
table with a parameterized query to get the rest of the values.  This is
true in both the case where a user tries to view a table or runs a
simple query.

Taking a simple case of the user opening the table in data sheet view
(if this is solved, the other cases will be solved too), the following
happens -- okay, this is a bit simplified, see my message Large
Datasets w/Access for better background:
http://lists.mysql.com/cgi-ez/ezmlm-cgi?5:mss:4918:200202:bjcebaokcknfmaldpokp

-- Access opens a statement handle (#1) and queries the table for the
primary key values.  E.g. It would pass SELECT idx FROM TABLE.  Note
that it only cares about getting a partial list here.  I.e. if the
screen only shows 10 records, Access only cares about 10 primary key
values.

-- Access opens a second statement handle (#2) without closing the first
handle and then gets the values in a parameterized query. E.g.: SELECT
a, b, idx FROM table WHERE idx=? OR idx=?  It then pulls the
records it cares about with this statement and closes the statement.

-- If, say, the user presses page down, [I think] access then gets the
next set of primary key values from statement handle #1, sets up another
prepared query and gets the values as above.


MyODBC, as compiled today, uses mysql_store_result to get records.  This
is fine for reasonably sized tables.  However, if the table has millions
of records, writing the results to a temporary table has many
detrimental effects, e.g.: Access seems to hang from the user's
perspectiv, Access crashes because there are too many records for it to
handle at once (data requirements to great); MySQL creates HUGE
temporary tables or bombs if SQL_BIG_RESULT was not set.  

So in the case of a very long table, it is important to use
mysql_use_result instead.  This makes it so that results are returned
right away and eases the load on all programs involved.  The astute
reader will realize that if one uses mysql_use_result and does not fetch
all of the records, the next query will return the remaining records
from the previous query first.  It follows that Access bombs because in
statement #2 it is getting results from statement #1. (This is seen from
the myodbc.log line:  | error: message: Commands out of sync;  You
can't run this command now in the myodbc3.dll changed to use the said
function.)

The bottom line is that in order for MySQL + Access + MyODBC to be
usable as a datawarehouse MySQL/MyODBC (a) must be able to return
uncached results; and (b) be able to have multiple statements open,
active, and with pending data to be fetched at the same time.

SO

Does anyone have any suggestions on how to accomplish this?  

How difficult would it be (for a relatively good C/C++ programmer) to
alter mysqld so that mysql_use_result could handle multiple statements
open at the same time?

Other suggestions...?


Thanks for reading this and your time.


--Bill
(all opinions are mine, bla bla bla)
(I am on the MyODB list but not the MySQL list at the moment)




-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: how do you increment a field on the fly?

[David S. Jackson]

How do you add a column that increments on the fly those fields
you've selected to print in a mysql query?
 
Example:
 
  select count(g.Group_ID) as Number, g.Description,
  sum(i.Retail_Value) from Groups g, Item i where i.Group_ID =
  g.Group_ID and i.Group_ID  0 group by i.Group_ID order by
  i.Category_ID;
 
My intention was to have the Number field simple be a number that
increments by one for each line that prints out.  But, of course,
the documentation says that's not what the count function is
for.  
 
How can I add a simple little old line counter?
 
-- 
David S. Jackson[EMAIL PROTECTED]
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
I put instant coffee in a microwave and almost went
back in time.  -- Steven Wright


-- 
David S. Jackson[EMAIL PROTECTED]
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
I'm not afraid of death -- I just don't want to be
there when it happens.
-- Woody Allen

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

select then delete everything selected

[Eric Mayers]

Hi List, 

I need to select a set of stuff from the database and then delete
exactly the same stuff.  I've formed two query statements and I'm
tacking modifications to their ends to be consistent, but I'm not sure
if with a limit clause the database will delete the same set of contents
selected?  Here's what I'm doing (in pesudocode):

query = SELECT * FROM log where id in (idlist) ;
dquery = DELETE FROM log where id in (idlist) ;

(if test1) querymod = AND message like '%include%' ;
(if test2) querymod .= AND message not (like '%exclude%') ;
(if test3) querymod .= LIMIT 1000;

query = query + querymod;
dquery = dquery + querymod;

mysql_query(query);
fetch_and_prepare_results();

(if delete_shown) mysql_query(dquery);

display_results();

.. So, in this case is there any way I can guarantee that the items
deleted are the same ones that get displayed?  Is there any way I can do
this without including a unique identifier column and linking the delete
statement into that?

Thanks, 
Eric Mayers
Software Engineer I
Captus Networks










-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: data type bigint(20)

[John D. Kirkpatrick]

MySQL Gurus,

I'm trying to figure out what the number after the type means for numbers.
I  noticed in someone's code that for the ID field they used bigint(20).
bigint I thought was fixed at 8 bytes. Does this override the size? 20
bytes??? 20 bits?

Thanks
John



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: data type bigint(20)

[Eric Mayers]

John, 

The number in the parenthsis tells mysql how to format the column when
it goes to display it..  Here is the relevant section of
http://www.mysql.com/doc/N/u/Numeric_types.html:

Another extension is supported by MySQL for optionally specifying the
display width of an integral value in parentheses following the base
keyword for the type (for example, INT(4)). This optional width
specification is used to left-pad the display of values whose width is
less than the width specified for the column, but does not constrain the
range of values that can be stored in the column, nor the number of
digits that will be displayed for values whose width exceeds that
specified for the column. When used in conjunction with the optional
extension attribute ZEROFILL, the default padding of spaces is replaced
with zeroes. For example, for a column declared as INT(5) ZEROFILL, a
value of 4 is retrieved as 4. Note that if you store larger values
than the display width in an integer column, you may experience problems
when MySQL generates temporary tables for some complicated joins, as in
these cases MySQL trusts that the data did fit into the original column
width.

Eric

 -Original Message-
 From: John D. Kirkpatrick [mailto:[EMAIL PROTECTED]]
 Sent: Thursday, February 14, 2002 3:27 PM
 To: [EMAIL PROTECTED]
 Subject: Re: data type bigint(20) 
 
 
 MySQL Gurus,
 
 I'm trying to figure out what the number after the type means 
 for numbers.
 I  noticed in someone's code that for the ID field they used 
 bigint(20).
 bigint I thought was fixed at 8 bytes. Does this override the size? 20
 bytes??? 20 bits?
 
 Thanks
 John
 
 
 
 -
 Before posting, please check:
http://www.mysql.com/manual.php   (the manual)
http://lists.mysql.com/   (the list archive)
 
 To request this thread, e-mail [EMAIL PROTECTED]
 To unsubscribe, e-mail 
 [EMAIL PROTECTED]
 Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
 
 

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

HELP: cannot connect to MySQL server

[wally liau]

Dear MySql experts: 

we have mysql and mm driver installed on a unix
server, but when I try to run Blob (in testsuite/), it
gives error message: 

cannot connect to MySQL server on //localhost:3306

here is the code for connection: 

try {
Class.forName(org.gjt.mm.mysql.Driver).newInstance();
Connection conn =
DriverManager.getConnection(jdbc:mysql://localhost:3306/test?user=testpassword=test);
...

}
our system admininstor doesn't seem to know what's
going wrong (that's why I'm looking for help from
mysql community). 

Any info would be appreciated. 






__
Do You Yahoo!?
Send FREE Valentine eCards with Yahoo! Greetings!
http://greetings.yahoo.com

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

explain table within perl DBI

[Brian Warn]

When I run the code  below, information about the first column in each
table in my list is excluded.  Any ideas why?

@table_listing=`cat /usr/local/file.txt`;  # contains table list used
below

$dbh =
DBI-connect(dbi:mysql:database=dbname;host=hostIP;port=port_number,
user, password) or dienice(Can't connect: $DBI:errstr);
foreach $table (@table_listing){
chomp $table;
print ~~ $table Table ~\n;

$statement=explain $table;
$sth = $dbh-prepare($statement) or dienice(Can't prepare
statement: ,$dbh-errstr);
$dbh-errstr;   
$sth-execute;
@row_ary = $sth-fetchrow_array;
while (($expl0,$expl1,$expl2) = $sth-fetchrow_array){
print $expl0 ... $expl1 ... $expl2\n;
}
$sth-finish;
print \n;
}
$dbh-disconnect;

-Brian
sql, query, mysql, database


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: explain table within perl DBI

[Keith C. Ivey]

On 14 Feb 2002, at 15:51, Brian Warn wrote:

 When I run the code  below, information about the first column in each
 table in my list is excluded.  Any ideas why?
[snip]
   @row_ary = $sth-fetchrow_array;

You've read that information into @row_ary, but then you never do 
anything with it.

   while (($expl0,$expl1,$expl2) = $sth-fetchrow_array){
   print $expl0 ... $expl1 ... $expl2\n;
   }

Now you're printing the rest of the information.

 sql, query, mysql, database

-- 
Keith C. Ivey [EMAIL PROTECTED]
Washington, DC

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: explain table within perl DBI

[Paul DuBois]

At 15:51 -0800 2/14/02, Brian Warn wrote:
When I run the code  below, information about the first column in each
table in my list is excluded.  Any ideas why?

Why are you reading a row into @row_ary?

I bet you're thinking that row will correspond to the row of column
headers that you'd see were you to run the query in the mysql client.
But it doesn't.  So you're reading the first row of information (i.e.,
the information for the first table column), but discarding it.


@table_listing=`cat /usr/local/file.txt`;  # contains table list used
below

$dbh =
DBI-connect(dbi:mysql:database=dbname;host=hostIP;port=port_number,
user, password) or dienice(Can't connect: $DBI:errstr);
foreach $table (@table_listing){
   chomp $table;
   print ~~ $table Table ~\n;

   $statement=explain $table;
   $sth = $dbh-prepare($statement) or dienice(Can't prepare
statement: ,$dbh-errstr);
   $dbh-errstr;
   $sth-execute;
   @row_ary = $sth-fetchrow_array;
   while (($expl0,$expl1,$expl2) = $sth-fetchrow_array){
   print $expl0 ... $expl1 ... $expl2\n;
   }
   $sth-finish;
   print \n;
}
$dbh-disconnect;

-Brian
sql, query, mysql, database


-
Before posting, please check:
http://www.mysql.com/manual.php   (the manual)
http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Need help about mysql order and index....

[Jean-Francois Dionne]

Hi there, I hope someone will can help me... First, sorry for bad english :)
i'm french
First all I have those index in my table
+---+---+-++
--+-++--++--
+
| Table | Non_unique | Key_name  | Seq_in_index | Column_name | Collation |
Cardinality | Sub_part | Packed | Comment |
+---+---+-++
--+-++--++--
+
| Stats |  0   | PRIMARY   |1| ID
| A  | 4568748 | NULL  | NULL|   |
| Stats |  1   | Port_ID   |1| Port_ID
| A  |   1  | NULL  | NULL|   |
| Stats |  1   | Timestamp  |1| Timestamp
| A  |   49126   | NULL  | NULL|   |
+---+---+-++
--+-++--++--
+

You have to know I have 4 500 000 in my db

Okay I have this query:
select Timestamp from Stats use index (Timestamp) where Timestamp =
'2002-01-01' and Timestamp  '2002-02-01' order by Timestamp limit 1;
this one is jsut doing fine.. it take 0.00 sec to do it...

but
select Timestamp from Stats use index (Timestamp) where Timestamp =
'2002-01-01' and Timestamp  '2002-02-01' order by Timestamp desc limit 1;
this one take sometime 10 sec to do I understand is the desc the prob..
because my index is in asc (collation=A)
my question is... is there a way for maiking a index in desc? or maybe
something else?

Jean-Francois Dionne
Yard


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: MySQL + Access + MyODBC + LARGE Tables

[Keith A. Calaman]

I'm not an expert on MySQL or can address any of the tuning issues you bring
up.  I will say this, you are not totally correct in how ACCESS is
retrieving records.  VB and Microsoft Jet retrieve dynasets which is
basically the primary key in its entirety.  When you move to the next screen
ACCESS retrieves the attribute values related to the primary key.  The
dynaset is stored in RAM and if there is none available it will go to
virtual memory.  Thus, if you have millions of records ACCESS is going to
retrive millions of KEY_ID and try and store them within the local machine's
Volitile memory space.  I'm sure you can see the problem here because you
are also trying to run an operating system and at least one application at
the same time.

The trick is to only bring the dynaset accross the network you need to
retrieve and use MySQL's indexing processing power to get the records.  I
have had success with tables with millions of records in ACCESS on a PC.  Of
course, if I tried to open and browse through the table in datasheet view it
would drag down the system and take 20 mins just to open the table with the
first set of records.  However, if I sent a record limiting query to the
backend the only records sent over the network would be the ones requested.
I don't think I ever ran into a situation where an end user needed to browse
through a table with a million records.

Another word to the wise about ACCESS.  Make sure you split your database
into a back-end and front end so the user is actually working off the front
end located within their local drivespace.  You would put linked and local
tables in the back-end and forms and reports in the front.  This way if
there is a local system lock it will only trash the local application and
not the network application.  You can see the issue here as well.  The
simple act of someone killing the cpu power during a write operation and the
phone will be ringing because no one can access the database
application...if you don't have a back-up you might just be writing the
thing all over again.  I know you probably are aware of this issue but it
didn't hurt to say it (*_*).

I hope this helped at least a little.

-Original Message-
From: Bill Adams [mailto:[EMAIL PROTECTED]]
Sent: Thursday, February 14, 2002 6:05 PM
To: MySQL List; MyODBC Mailing List
Subject: MySQL + Access + MyODBC + LARGE Tables


Monty, Venu, I hope you read this... :)


I really, really want to use MySQL as the database backend for my
datawarehouse.  Mind you I have played around with merge tables quite a
bit and know that MySQL is more than up to the task.  There are numerous
(not necessarily cost related) reasons as to why MySQL is better for my
application. If it were just me, it would be a slam-dunk as I only use
perl, etc. to extract data from the database.  However most of my users
use MS Access as a front end and extraction tool.

When pulling datasets from a database, Access tries to be smart and if
there is what it thinks is a primary key on a table, it will extract the
values of the primary key for the matching records and then re-query the
table with a parameterized query to get the rest of the values.  This is
true in both the case where a user tries to view a table or runs a
simple query.

Taking a simple case of the user opening the table in data sheet view
(if this is solved, the other cases will be solved too), the following
happens -- okay, this is a bit simplified, see my message Large
Datasets w/Access for better background:
http://lists.mysql.com/cgi-ez/ezmlm-cgi?5:mss:4918:200202:bjcebaokcknfmaldpo
kp

-- Access opens a statement handle (#1) and queries the table for the
primary key values.  E.g. It would pass SELECT idx FROM TABLE.  Note
that it only cares about getting a partial list here.  I.e. if the
screen only shows 10 records, Access only cares about 10 primary key
values.

-- Access opens a second statement handle (#2) without closing the first
handle and then gets the values in a parameterized query. E.g.: SELECT
a, b, idx FROM table WHERE idx=? OR idx=?  It then pulls the
records it cares about with this statement and closes the statement.

-- If, say, the user presses page down, [I think] access then gets the
next set of primary key values from statement handle #1, sets up another
prepared query and gets the values as above.


MyODBC, as compiled today, uses mysql_store_result to get records.  This
is fine for reasonably sized tables.  However, if the table has millions
of records, writing the results to a temporary table has many
detrimental effects, e.g.: Access seems to hang from the user's
perspectiv, Access crashes because there are too many records for it to
handle at once (data requirements to great); MySQL creates HUGE
temporary tables or bombs if SQL_BIG_RESULT was not set.

So in the case of a very long table, it is important to use
mysql_use_result instead.  This makes it so that results are returned
right away and eases the load on all programs 

Re: Distributed Fulltext?

[Alex Aulbach]

Wednesday, from Mike Wexler:
 I don't think that would be appropriate. My example, is our site (tias.com) has
 lots of antiques and collectibles. One popular categories is jewelry. If
 somebody does a search for gold jewelry and the search engine interprets this
 as anything that mentions gold or jewelry. It is going to match a lot of items.
 It would be nice if we could use explain or something like it to get a rough
 estimate of how many results a query would generate, and if it was really bad,
 we could tell the user to be more specific.

This is not a solution, but we make it by using the sql query

SELECT COUNT(*) FROM table WHERE MATCH(index) AGAINST ('gold')
(results e.g. in 100)

and

SELECT COUNT(*) FROM table WHERE MATCH(index) AGAINST ('jewelry')
(results e.g. in 200)

OR-Search: The result is between 300 and 500 matches.
AND-Search: The result is between 0 and 200 matches.

The problem is: The queries lasts nearly as fast, as the complete search.
:)

-- 

SSilk - Alexander Aulbach - Herbipolis/Frankonia Minoris


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

perfect attendance

[Jason Yates]

Alright, I have student table and an attendance table.

  student table

student_id int(10)
name   varchar(60)
school int(4)
district   int(4)

 attendance table

student_id  int(10)
status  varchar(4)
datedate

Here is the query,

SELECT a.name,a.student_id,count(b.status) as count 
FROM student as a left join attendance as b
ON a.student_id = b.student_id
WHERE a.school='29'
GROUP BY (a.student_id) 
HAVING count='0'

The query works fine, but if I want to give a date range.  For instance
perfect attendance between 2001-09-10 and 2001-10-10, the query won't
work because the table is left joined.  The date value in the attendance
table will just return as NULL. Is there a way around this?

-Jason Yates


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: how do you increment a field on the fly?

[Daniel Rosher]

David,

try

1) create temporary table x (a INT PRIMARY KEY AUTO_INCREMENT)
[select_statement]

where [select_statement] is some legal select statement, presumebly

select g.Description,sum(i.Retail_Value)
from Groups g, Item i
where i.Group_ID =g.Group_ID
and i.Group_ID  0
group by i.Group_ID
order by i.Category_ID;

then

2) select * from x

When you close the connection the trmporary table x will be removed. The
same temporary table name can be used for multiple connections.

Otherwise get the resultset into your application, and then increment a
counter in a for/while loop etc

Regards,
Dan

 -Original Message-
 From: David S. Jackson [mailto:[EMAIL PROTECTED]]
 Sent: Friday, 15 February 2002 12:08 p.m.
 To: [EMAIL PROTECTED]
 Subject: Re: how do you increment a field on the fly?


 How do you add a column that increments on the fly those fields
 you've selected to print in a mysql query?

 Example:

   select count(g.Group_ID) as Number, g.Description,
   sum(i.Retail_Value) from Groups g, Item i where i.Group_ID =
   g.Group_ID and i.Group_ID  0 group by i.Group_ID order by
   i.Category_ID;

 My intention was to have the Number field simple be a number that
 increments by one for each line that prints out.  But, of course,
 the documentation says that's not what the count function is
 for.

 How can I add a simple little old line counter?

 --
 David S. Jackson[EMAIL PROTECTED]
 =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
 I put instant coffee in a microwave and almost went
 back in time.  -- Steven Wright


 --
 David S. Jackson[EMAIL PROTECTED]
 =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
 I'm not afraid of death -- I just don't want to be
 there when it happens.
   -- Woody Allen

 -
 Before posting, please check:
http://www.mysql.com/manual.php   (the manual)
http://lists.mysql.com/   (the list archive)

 To request this thread, e-mail [EMAIL PROTECTED]
 To unsubscribe, e-mail
 [EMAIL PROTECTED]
 Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php




-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Distributed Fulltext?

[Alex Aulbach]

Wednesday, from David Axmark:
  Your other point about exact vs. approximate answers is unclear, I expect
  that Google's answers are exact for their currently available indexes at any
  given time.  But even if they are approximate, I'd be happy with that too.
  The scoring on a FULLTEXT search in Mysql is exact but based on a
  formula that is approximate anyway.

 No, MySQL returns all data according to a search. Web engines return
 what they they find on one search machine. So you can get different
 results with Google every time you hit refresh if you are routed to
 different machines. This had happened to me when I was looking for the
 number of matches and not the result itself.

 So we should try to make fulltext searches with a limit between 10 and
 100 be fast to be closer to google.

 I have also head about some other things web search engines do since I
 know some people at FAST but I have forgot that already.

My opinion is, that mySQL itself never should try to find approximate
matches. This is against the definition of SQL itself. SQL is a fourth
generation language. That means, if you say SELECT, the engine selects.
And it has to be as exactly that, what I have searched, every time, on
every machine in any combination with the same data.

So SQL needs a new language construct to make an approximate search. But
what is an approximate search? How is approximate defined?

I don't think it is a good idea to implement it in this way.
Approximazation must be always done on the application level, cause it is
highly dependend on application, what an approximate result could be.

 We will try to make every feature as good as possible. But we do have
 limited resources.

Exactly. FTS is not so important as other features and people which want
you to include a new feature should think about supporting mysql with
money. :-)

But (yes, we support mysql! :-) I think the need is growing rapidly, cause
the amount of data, that has to be indexed is growing over the years. And
other DB's have much more experices with it. Currently we can live with
the speed. Those who cannot live with it should buy better machines,
think about their SE-concept or support mysql.

Search engines techniques are *not* trivial, so the last way is in my eyes
one of the cheapest.


 Well there is always the option of sponsoring further fulltext
 development. We have a guy who has been working on the GNU fulltext
 engines who is interesting in working with MySQL fulltext. But for the
 moment we can not afford it.

This was my first thought: People write about speed problems and how to
cluster and so on. Things, that I would calculate with weeks and high TCO.

But it maybe much cheaper to pay mySQL for this. How much do you estimate
would it cost to implement inverted files? I think this is difficult,
cause Sergei told me, that he couldn't use mySQL-index files any more.

I just ask, nothing special in sight, but many questions from everyone who
needs it. Cause FTS is a feature which highly improves the value of a web
site. And coustomers have no problem to pay for things they think they get
money for. FTS is such a thing.

But perhaps if we know, under which circumstances FTS is improved, it is
easier for us to find a possible way to share the costs for it or find a
compromise. I also understand, if mySQL don't want to speak about it
here.

I think it is also important for us, how much it can be theoretically
improved. My calculations showed me a theoretical speed up of factor 100
or so. This is ... wow. But in live everything is most times slower...


 So if some of you are interested in sponsoring this (or know about
 others who might be) write to [EMAIL PROTECTED]

Or like this... maybe we find coustomers who needs it. Think it's
possible.

My personal feeling is and my stomach says, that fulltext indexing is a
feature, which needs to be expanded.

-- 

SSilk - Alexander Aulbach - Herbipolis/Frankonia Minoris



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

SQL question on BETWEEN in MySQL

[Kyle Hayes]

I am reposting this because it got sent right at the start of all the fun
with the mailing list.

Best,
Kyle

-

I have a series of data in one table that I need to put into a set of ranges.

Here is a simplified version of the tables:

data table fields (data):
 val int(10) not null,
 row_id int(10) not null auto_increment,
 primary key


Range table fields (range):

 lo int(10) not null,
 hi int(10) not null,
 range_id int(10) not null,
 primary key (lo,hi),
 index rng_indx(range_id)


What I need to do is to count the number of entries in the data table that
fall in the ranges in the range table.  Ideally, I would have something
like this:

SELECT r.range_id as 'Range', count(*) as 'Hits'
FROM data d, range r
WHERE d.val BETWEEN r.lo AND r.hi
GROUP BY r.range_id


When I set up a test for this, explain shows that I will be doing a table
scan.  This is not what I want.  I have 5M rows in data and 10k+ in range.

The ranges do not overlap, so I will not get multiple rows per value in
the data table.  Some values may lie outside all ranges.

Am I misusing between here?  If so, is there a construct in MySQL that
I can use to speed this up?  I know that comparison queries other than
equal and not equal tend to cause table scans, but between seems to 
work with very different queries.  Should I restructure the query somehow?

Any help from the guru's is appreciated.

Best,
Kyle
-- 
Quicknet's MicroTelco fax and voice service has just added
another carrier giving MicroTelco users more low rates to
choose from. This new carrier is Altair Telecom - a low cost,
worldwide voice telephony carrier that is available by up-grading
at no additional cost to Internet SwitchBoard v6.0 or MicroTelco
Gateway v2.5. http://www.quicknet.net/download/

Cut costs, Fax smart. Use iPrint2Fax worldwide and save!
==
FREE software download available at www.iPrint2Fax.com
==

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: data type bigint(20)

[Daniel Rosher]

http://www.mysql.com/doc/N/u/Numeric_types.html

20 is the display width:

'for a column declared as INT(5) ZEROFILL, a value of 4 is retrieved as
4'

Regards
Dan

 -Original Message-
 From: John D. Kirkpatrick [mailto:[EMAIL PROTECTED]]
 Sent: Friday, 15 February 2002 12:27 p.m.
 To: [EMAIL PROTECTED]
 Subject: Re: data type bigint(20)


 MySQL Gurus,

 I'm trying to figure out what the number after the type means for numbers.
 I  noticed in someone's code that for the ID field they used bigint(20).
 bigint I thought was fixed at 8 bytes. Does this override the size? 20
 bytes??? 20 bits?

 Thanks
 John



 -
 Before posting, please check:
http://www.mysql.com/manual.php   (the manual)
http://lists.mysql.com/   (the list archive)

 To request this thread, e-mail [EMAIL PROTECTED]
 To unsubscribe, e-mail
 [EMAIL PROTECTED]
 Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php




-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Slow Query...

[RBRoa]

Hi,

I still got stuck on a query...so slow...it took a minute or more

How can I improve my server...

Specs...

Computer:
Pentium II 333MHZ
128MB
20GB HardDrive

OS:
Windows 98,

Database:

Table definition

| edate | date  | YES  | | NULL|
| etime | int(5) unsigned zerofill  | YES  | | NULL|
| lec   | varchar(5)| YES  | | NULL|
| trunk | varchar(5)| YES  | | NULL|
| bound | varchar(5)| YES  | | NULL|
| att   | int(10) unsigned zerofill | YES  | | NULL|
| suc   | int(10) unsigned zerofill | YES  | | NULL|
| ovf   | int(10) unsigned zerofill | YES  | | NULL|
| fir   | int(10) unsigned zerofill | YES  | | NULL|
| alt   | int(10) unsigned zerofill | YES  | | NULL|
| ter   | int(10) unsigned zerofill | YES  | | NULL|
| rng   | int(10) unsigned zerofill | YES  | | NULL|
| tot   | float unsigned zerofill   | YES  | | NULL|
| cal   | float unsigned zerofill   | YES  | | NULL|
| mtu   | float unsigned zerofill   | YES  | | NULL|
| eqp   | int(10) unsigned zerofill | YES  | | NULL|
| srv   | int(10) unsigned zerofill | YES  | | NULL|
| loc   | int(10) unsigned zerofill | YES  | | NULL|
| flt   | int(10) unsigned zerofill | YES  | | NULL|


Indexing

Edate
Etime
Lec
Trunk
Bound
Att
Suc
Cal
Eqp

Currently my table is filled with 1M+ rows...

My problem is...when I used MAX() and GROUP BY enclosed in SELECT statement,
the performance is not convincing...
I used the MySQL console for testing...and I based the time result also...




R.B.Roa 
Traffic Management Engineer
PhilCom Corporation
Tel.No. (088) 858-1028
Mobile No. (0919) 30856267


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Mildly Off-topic: BLOB type to store documents...

[Daniel Kasak]

  Hi all.
And Hi to the spam filter: MySQL, database, sql, etc...

We are considering using the BLOB data type to store documents - .doc, 
.xls, .pdf mainly.
I have also considered just storing a hyperlink to a file on the 
network, but with the amount of files, it seems a little messy.
So I'm pretty much decided as long as I can get it to work...

I'm using Access 2002. What I'd like is for people to be able to use a 
FileOpen - type dialog box to select the document, which is then copied 
into the BLOB field. Then they can later double-click on the BLOB field 
to open the document in the default application for the particular file 
type. The problem is that I don't quite understand how to do this. I can 
get the dialog box fine (from some Access examples databases). But as 
for copying from the network into a database field - I dunno. Oh, and 
the same for getting it back out :)

Any clues / examples / links greatly appreciated.

Dan

-- 
Daniel Kasak
IT Developer
* NUS Consulting Group*
Level 18, 168 Walker Street
North Sydney, NSW, Australia 2060
T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989
email: [EMAIL PROTECTED]
website: www.nusconsulting.com



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: HELP: cannot connect to MySQL server

[Brian Reichert]

On Thu, Feb 14, 2002 at 03:41:32PM -0800, wally liau wrote:
 Dear MySql experts: 
 
 we have mysql and mm driver installed on a unix
 server, but when I try to run Blob (in testsuite/), it
 gives error message: 
 
 cannot connect to MySQL server on //localhost:3306

Can you, at this point, connect to the server via the 'mysql' binary?

- Is there a server running on this machine?

- Is there a server running on this machine at port 3306?

- Is there a server running on this machine at port 3306 that lets
  user 'test' coonect with the password 'test'?

 our system admininstor doesn't seem to know what's
 going wrong (that's why I'm looking for help from
 mysql community). 

I'm sorry. :/

-- 
Brian 'you Bastard' Reichert[EMAIL PROTECTED]
37 Crystal Ave. #303Daytime number: (603) 434-6842
Derry NH 03038-1713 USA Intel architecture: the left-hand path

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

mysql won't start via init script

[Faine, Mark]

If I start mysql by changing to the msyql directory and run safe_mysqld
--user=mysql  everything works fine, if I try to start mysql at boot time
with an init script it starts and immediately dies the log files say it
can't find a certain library file (a file that is in my LD_LIBRARY_PATH). It
has no problem starting if I wait till the server boots up completly and
start it manually with the init script or with safe_mysqld

??

Thanks,
Mark

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

mysql database question

[Jason G. Embuscado]

what affects the rows in the host table of database mysql? is it when a
particular host logs in the server's mysql console? or is it when a user
browses a web file that launches a script to access a database? or both?

thanks
jembo


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: admin privilege problems

[System Administrator a.k.a. The Root of the Problem]

On Tue, Feb 05, 2002 at 07:10:01PM +0200, Victoria Reznichenko wrote:
 jeremy,
 
 Tuesday, February 05, 2002, 6:40:29 PM, you wrote:
 
 jr hello, 
 
 jr i made the mistake of changing my root password manually in the mysql
 jr database. after doing so, i 'flushed privileges'.
 
 jr now i'm unable to log on using the root. i've tried to log on using the new
 jr password a number of times, but i'm always denied access. strangely enough,
 jr other users (that have passwords) can log on successfully without providing
 jr a password. does anybody have any recommendations for logging on as the root
 jr user ... and fixing this privileges problem?
 
 Look at: http://www.mysql.com/doc/R/e/Resetting_permissions.html
 It will help you.
 
 jr thx
 jr jeremy
 
 
 
 
 -- 
 For technical support contracts, goto https://order.mysql.com/
 This email is sponsored by Ensita.net http://www.ensita.net/
__  ___ ___   __
   /  |/  /_ __/ __/ __ \/ /Victoria Reznichenko
  / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
 /_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.net
___/   www.mysql.com
 
 
 
 
 -
 Before posting, please check:
http://www.mysql.com/manual.php   (the manual)
http://lists.mysql.com/   (the list archive)
 
 To request this thread, e-mail [EMAIL PROTECTED]
 To unsubscribe, e-mail [EMAIL PROTECTED]
 Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
 
 
 -
 Before posting, please check:
http://www.mysql.com/manual.php   (the manual)
http://lists.mysql.com/   (the list archive)
 
 To request this thread, e-mail [EMAIL PROTECTED]
 To unsubscribe, e-mail [EMAIL PROTECTED]
 Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
 


What if you run into:


Script started on Thu Feb 14 20:35:44 2002
doctor.nl2k.ab.ca//var$ mysqladmin -h localhost -u root password 'Nathan84'
mysqladmin: unable to change password; error: 'You must have privileges to update 
tables in the mysql database to be able to change passwords for others'
doctor.nl2k.ab.ca//var$ exuit   it
exit

Script done on Thu Feb 14 20:35:56 2002
-- 
contact:
Dave Yadallee  NetKnow  The Internet Knowledge Company
[EMAIL PROTECTED]  http://www.nl2k.ab.ca
990-3244

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

InnoDB create table error 150

[Richard R. Harms]

The create table command included at the end of this message gives 
the following error:

ERROR 1005: Can't create table './catalogmgr/macitm_vendordfrom.frm' 
(errno: 150)

Creating it with type=myisam, and then doing an alter table and 
converting it to innodb works okay. It just cannot be initially 
created as an innodb table.

The error occurs on both Red Hat Linux 6.2 and Mac OS X 10.1.2. MySQL 
was compiled on both systems with the --enable-raid flag. my.cnf 
settings are included below as well.

=rh

CREATE TABLE macitm_vendordfrom (
   e_vendorno varchar(10) default NULL,
   e_apvendor varchar(20) default NULL,
   e_company char(2) default NULL,
   e_division char(2) default NULL,
   e_name varchar(30) default NULL,
   e_lastname varchar(16) default NULL,
   e_initial char(2) default NULL,
   e_firstname varchar(16) default NULL,
   e_title char(2) default NULL,
   e_ref1 varchar(30) default NULL,
   e_ref2 varchar(30) default NULL,
   e_street varchar(30) default NULL,
   e_city varchar(30) default NULL,
   e_state char(2) default NULL,
   e_zip varchar(10) default NULL,
   e_countrycode varchar(4) default NULL,
   e_dayphone varchar(16) default NULL,
   e_freightppcode char(2) default NULL,
   e_freightppbasis bigint(10) default NULL,
   e_freightpct int(5) default NULL,
   e_fobcity varchar(30) default NULL,
   e_foreigncurrency char(2) default NULL,
   e_termscode varchar(4) default NULL,
   e_termspct int(4) default NULL,
   e_termsdays int(5) default NULL,
   e_standarddays int(5) default NULL,
   e_faxno varchar(16) default NULL,
   e_miscdata40 varchar(40) default NULL,
   e_vendorcomments001 varchar(60) default NULL,
   e_vendorcomments002 varchar(60) default NULL,
   e_vendorcomments003 varchar(60) default NULL,
   e_vendorcomments004 varchar(60) default NULL,
   e_vendorcomments005 varchar(60) default NULL,
   e_vendorcomments006 varchar(60) default NULL,
   e_vendorcomments007 varchar(60) default NULL,
   e_vendorcomments008 varchar(60) default NULL,
   e_addldata varchar(20) default NULL,
   e_email varchar(48) default NULL
) TYPE=InnoDB;

[mysqld]
set-variable = delayed_queue_size=10
set-variable = sort_buffer=8M
set-variable = join_buffer=4M
set-variable = tmp_table_size=4M
set-variable = max_sort_length=16384
set-variable = max_allowed_packet=1M
set-variable = query_cache_size=2M
set-variable = query_cache_startup_type=1
#
innodb_data_home_dir = /usr/local/var/.ibdata
#Data files must be able to
#hold your data and indexes
innodb_data_file_path = ibdata1:500M;ibdata2:500M;ibdata3:500M
#Set buffer pool size to 50 - 80 %
#of your computer's memory
set-variable = innodb_buffer_pool_size=32M
set-variable = innodb_additional_mem_pool_size=8M
#
innodb_log_group_home_dir = /usr/local/var/.iblogs
#.._log_arch_dir must be the same
#as .._log_group_home_dir
innodb_log_arch_dir = /usr/local/var/.iblogs
innodb_log_archive=0
set-variable = innodb_log_files_in_group=3
#Set the log file size to about
#15 % of the buffer pool size
set-variable = innodb_log_file_size=50M
set-variable = innodb_log_buffer_size=8M
#Set ..flush_log_at_trx_commit to
#0 if you can afford losing
#a few last transactions
innodb_flush_log_at_trx_commit=1
set-variable = innodb_file_io_threads=4
set-variable = innodb_lock_wait_timeout=50

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: ....from ms access to mysql

[Pedja Delic]

Hello,
Look on the following page: www.dbtools.com.br.
There is a good software which can handle that task.



- Original Message -
From: Fabio Amura [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Thursday, February 14, 2002 8:33 AM
Subject: from ms access to mysql


 Hi,
 i have to migrate from ms access to my sql, and i dont know how can i
do...
 do your know at easy metod for this migration, and can you give me
 indication or link

 i'm not an espert...
 thanks
 fabio

 -Messaggio originale-
 Da: Jochen Kaechelin [mailto:[EMAIL PROTECTED]]
 Inviato: giovedì 14 febbraio 2002 7.52
 A: [EMAIL PROTECTED]
 Oggetto: Updating SuSE 7.3


 Can I use the RedHat-rpms on mysql.com to update a SuSE 7.3 prof or
 do I need to compile it from source with some parameters?


 I run 3.23.44 max and I want to update to 3.23.48 max
 --
 Jochen

 -
 Before posting, please check:
http://www.mysql.com/manual.php   (the manual)
http://lists.mysql.com/   (the list archive)

 To request this thread, e-mail [EMAIL PROTECTED]
 To unsubscribe, e-mail
 [EMAIL PROTECTED]
 Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php


 -
 Before posting, please check:
http://www.mysql.com/manual.php   (the manual)
http://lists.mysql.com/   (the list archive)

 To request this thread, e-mail [EMAIL PROTECTED]
 To unsubscribe, e-mail
[EMAIL PROTECTED]
 Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: MySQL + Access + MyODBC + LARGE Tables

[Venu]

Hi, 

 
 
 Monty, Venu, I hope you read this... :)
 
 
 I really, really want to use MySQL as the database backend for my
 datawarehouse.  Mind you I have played around with merge tables quite a
 bit and know that MySQL is more than up to the task.  There are numerous
 (not necessarily cost related) reasons as to why MySQL is better for my
 application. If it were just me, it would be a slam-dunk as I only use
 perl, etc. to extract data from the database.  However most of my users
 use MS Access as a front end and extraction tool.
 
 When pulling datasets from a database, Access tries to be smart and if
 there is what it thinks is a primary key on a table, it will extract the
 values of the primary key for the matching records and then re-query the
 table with a parameterized query to get the rest of the values.  This is
 true in both the case where a user tries to view a table or runs a
 simple query.
 
 Taking a simple case of the user opening the table in data sheet view
 (if this is solved, the other cases will be solved too), the following
 happens -- okay, this is a bit simplified, see my message Large
 Datasets w/Access for better background:
 http://lists.mysql.com/cgi-ez/ezmlm-cgi?5:mss:4918:200202:bjcebaok
 cknfmaldpokp
 
 -- Access opens a statement handle (#1) and queries the table for the
 primary key values.  E.g. It would pass SELECT idx FROM TABLE.  Note
 that it only cares about getting a partial list here.  I.e. if the
 screen only shows 10 records, Access only cares about 10 primary key
 values.
 
 -- Access opens a second statement handle (#2) without closing the first
 handle and then gets the values in a parameterized query. E.g.: SELECT
 a, b, idx FROM table WHERE idx=? OR idx=?  It then pulls the
 records it cares about with this statement and closes the statement.
 
 -- If, say, the user presses page down, [I think] access then gets the
 next set of primary key values from statement handle #1, sets up another
 prepared query and gets the values as above.
 
 
 MyODBC, as compiled today, uses mysql_store_result to get records.  This
 is fine for reasonably sized tables.  However, if the table has millions
 of records, writing the results to a temporary table has many
 detrimental effects, e.g.: Access seems to hang from the user's
 perspectiv, Access crashes because there are too many records for it to
 handle at once (data requirements to great); MySQL creates HUGE
 temporary tables or bombs if SQL_BIG_RESULT was not set.  

Probably we can add extra DSN options, to make use of either 
mysql_store_result() or mysql_use_result(). In the second 
case, lot of code change is needed in all result set dependency 
APIs too.  

 
 So in the case of a very long table, it is important to use
 mysql_use_result instead.  This makes it so that results are returned
 right away and eases the load on all programs involved.  The astute
 reader will realize that if one uses mysql_use_result and does not fetch
 all of the records, the next query will return the remaining records
 from the previous query first.  It follows that Access bombs because in
 statement #2 it is getting results from statement #1. (This is seen from
 the myodbc.log line:  | error: message: Commands out of sync;  You
 can't run this command now in the myodbc3.dll changed to use the said
 function.)

Can you be more specific on this ? And a MS ODBC DM trace will be better 
to analyze.

 
 The bottom line is that in order for MySQL + Access + MyODBC to be
 usable as a datawarehouse MySQL/MyODBC (a) must be able to return
 uncached results; and (b) be able to have multiple statements open,
 active, and with pending data to be fetched at the same time.

Try to use Dynamic Cursor Type (OPTION=32) in MyODBC 3.51.

Regards, Venu
--
For technical support contracts, go to https://order.mysql.com
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /   Mr. Venu [EMAIL PROTECTED]
 / /|_/ / // /\ \/ /_/ / /__  MySQL AB, Developer
/_/  /_/\_, /___/\___\_\___/  California, USA
   ___/  www.mysql.com

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

for fetching particular colum

[Chetan Lavti]

hi,
I am querying from the C api's,i am getting some result-set from which I
want to display particular colum??
Is there any C api by which I can directly point to the particular colum
and print it..

Thanks and regards,
Chetan Lavti

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: re-ordering rows

[BD]

At 03:26 PM 2/14/2002 , you wrote:
What Rick said is absolutely correct and you probably are obsessing about
something that doesn't matter.  But I would venture you are using an
auto-number field as the primary key when you could easily change it to a
function something similar to:  set ID = MAX(ID) + 1.

Keith,
  I agree with what everyone is saying. But for display purposes, 
it would be nice if MySQL could display a row# for the result set.  This 
could be used to display a line# at the start of each row, like Select 
count, col1, col2 from table1 where ... and the running count would show

Count   Col1   Col2
  1 ABC   DEF
  2GHI  KLM

It would act as sort of a line item# for display purposes only. As everyone 
mentioned, it doesn't make any sense to store this number in the table. But 
it might come in handy for display purposes.

Brent


-Original Message-
From: Bryan McCloskey [mailto:[EMAIL PROTECTED]]
Sent: Thursday, February 14, 2002 4:03 PM
To: [EMAIL PROTECTED]
Subject: RE: re-ordering rows


You're right, it's not important how the data is
stored inside the database. I was just hoping that
there would be a way to set a default order so that I
wouldn't have to write a cumbersome ORDER BY phrase
every time I wanted to see the data. I thought that
perhaps indexes could accomplish this, somehow by
telling them to re-index the column, but perhaps not.

-b

--- Rick Emery [EMAIL PROTECTED] wrote:
  Why is the internal order important?  When
  SELECTing, the internal order is
  of no importance to MYSQL.  It does not speed-up the
  query or access.  When
  discussing relational database systems, all that
  matters is the order of
  output.

__
Do You Yahoo!?
Send FREE Valentine eCards with Yahoo! Greetings!
http://greetings.yahoo.com

-
Before posting, please check:
http://www.mysql.com/manual.php   (the manual)
http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail
[EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php


-
Before posting, please check:
http://www.mysql.com/manual.php   (the manual)
http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: MySQL + Access + MyODBC + LARGE Tables

[BD]

Bill,
Some databases can use a live result set when retrieving a lot of 
records and I really really wish MySQL could do the same. A live result set 
does not create a temporary table or use memory to retrieve all the 
records. It will grab 50 or so records at a time, and when scrolling 
further down through the query, it will grab another 50 and so on. And yes, 
these queries are bi-directional.

   I've successfully created queries with other databases that 
retrieved a million records in less than 0.1 seconds on a P133 machine. 
For browsing data in a grid, it is instantaneous even when sorting on an 
index column. Traversing the entire 1 million rows uses absolutely no 
additional memory. I had a memory monitor running in the background and 
from start to finish it used maybe 5k of ram and no additional disk space 
was used for temporary tables which means disk activity was extremely 
low.  Slapping on any kind of Where clause doesn't slow it down because if 
you're displaying the results to a grid, it fills the grid with the first 
10 rows that it finds, then when you page down it pulls in the next 10 
rows. These are the benefits of using a live result set.

   Now the drawback of using a live result set is it doesn't create a 
static snapshot of the table. A static result set creates a copy of the 
rows at the instant the query was executed. It does this to protect the 
rows from being changed by another user. You'd want a static result set 
when printing reports that are doing subtotals because you don't want other 
people throwing your totals off. With a live result set, if I'm going 
through the query and I'm on row 100, another user can change row 150 which 
may now exclude row 150 from my query because it now falls outside the 
scope of the Where clause. For me 98% of the time, I don't really care if 
this happens. The additional speed, lower memory use, and very low disk 
activity more than makes up for it.

  So yes, you can access some databases extremely fast. I wish Monty 
would implement this for MySQL. It would have most people drooling on their 
keyboards. :-0...

Brent



At 05:04 PM 2/14/2002 , you wrote:
Monty, Venu, I hope you read this... :)


I really, really want to use MySQL as the database backend for my
datawarehouse.  Mind you I have played around with merge tables quite a
bit and know that MySQL is more than up to the task.  There are numerous
(not necessarily cost related) reasons as to why MySQL is better for my
application. If it were just me, it would be a slam-dunk as I only use
perl, etc. to extract data from the database.  However most of my users
use MS Access as a front end and extraction tool.

When pulling datasets from a database, Access tries to be smart and if
there is what it thinks is a primary key on a table, it will extract the
values of the primary key for the matching records and then re-query the
table with a parameterized query to get the rest of the values.  This is
true in both the case where a user tries to view a table or runs a
simple query.

Taking a simple case of the user opening the table in data sheet view
(if this is solved, the other cases will be solved too), the following
happens -- okay, this is a bit simplified, see my message Large
Datasets w/Access for better background:
http://lists.mysql.com/cgi-ez/ezmlm-cgi?5:mss:4918:200202:bjcebaokcknfmaldpokp

-- Access opens a statement handle (#1) and queries the table for the
primary key values.  E.g. It would pass SELECT idx FROM TABLE.  Note
that it only cares about getting a partial list here.  I.e. if the
screen only shows 10 records, Access only cares about 10 primary key
values.

-- Access opens a second statement handle (#2) without closing the first
handle and then gets the values in a parameterized query. E.g.: SELECT
a, b, idx FROM table WHERE idx=? OR idx=?  It then pulls the
records it cares about with this statement and closes the statement.

-- If, say, the user presses page down, [I think] access then gets the
next set of primary key values from statement handle #1, sets up another
prepared query and gets the values as above.


MyODBC, as compiled today, uses mysql_store_result to get records.  This
is fine for reasonably sized tables.  However, if the table has millions
of records, writing the results to a temporary table has many
detrimental effects, e.g.: Access seems to hang from the user's
perspectiv, Access crashes because there are too many records for it to
handle at once (data requirements to great); MySQL creates HUGE
temporary tables or bombs if SQL_BIG_RESULT was not set.

So in the case of a very long table, it is important to use
mysql_use_result instead.  This makes it so that results are returned
right away and eases the load on all programs involved.  The astute
reader will realize that if one uses mysql_use_result and does not fetch
all of the records, the next query will return the remaining records
from the previous query first.  It 

License Question

[Andrew Crum]

Can I statically link to libmysqlclient? If so, what are the implications?
Will I have to distribute my application's source? IANAL, so what do I need
to do? What if I link dynamically? Can I redistribute the compiled
libmysqlclient library? What if I just link dynamically?

Cheers,
Andrew


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: SQL Help, Please...

[Anvar Hussain K.M.]

Hi,

At 11:21 AM 14/02/2002 -0500, you wrote:
On Thursday 14 February 2002 07:58, Carl Shelbourne wrote:
  Hi
 
  I am trying to write an auction script that uses mysql as its backend. Each
  auction can have upto 25 sub auctions(cells) taking place.
 
  I'm trying to query the DB to give me a list of all the successfull bids
  for each cell, for each auction...
 
  SELECT b.auctionId, b.cellId, MAX(b.bid) as bid, b.bidderId FROM sa_bid as
  b, sa_auction AS a WHERE (a.state='active' AND b.auctionId=a.Id) GROUP BY
  auctionId,cellId ORDER BY bidTime DESC
 
  This is further complicated in so much that multiple MAX bids may exist at
  the same value, but, only the earliest should be returned for each cell.
 
  Which is returning some of the columns correctly, namely auctionid, cellid
  and bid, but it does not return the bidderId correctly.
 
  Can anybody help?

Your query is simply NOT relationally correct... The database has NO way to
know WHICH bidder id to return in a given group. Suppose that for a given
auctionid and cellid there might be 12 different bidders. You are telling the
database engine to return ONE record for that group of 12 rows, so which
bidderid will it use? The correct behaviour would be for MySQL to reject the
query, it simply cannot be properly processed. Unfortunately I've found that
MySQL doesn't behave correctly in these cases, instead it just returns one of
the possible bidderid values at random.

This behaviour is well documented in the manual.  There is a very practical
reason to allow this behaviour.  Suppose for efficiency reasons data is 
denormalized
and for example, id,name and some other particulars are all kept in the same
table .  If the database was very strict that all the columns selected 
should be
in the group by expression, one will have to put all these columns (id,name,..)
in the group by clause. Then the db engine will have to take all these 
fields in
the intermediate ordering phase of the query execution.  Surely this will be
inefficient in both time and space.  But with the 'incorrect' behaviour of 
Mysql
one can put all the data columns required to be returned in the select and do
group by only the id. This would make the query to complete very fast compared
to the former and the effect will be even more pronounced with index on id 
field.


Your query would be technically correct if you used a summary function on
bidderid, like MAX(b.bidderId) or somesuch. The rule is that the returned
columns in a GROUP BY must either by mentioned in the GROUP BY section of the
query itself, OR they must be the results of a summary function. Any other
use is not correct for the reason stated above.

In other words, you need to rewrite your application logic. Most likely you
will need to add the b.bidderId to the GROUP BY and have the program walk
through the result set and do further sumarization on its own. Alternately
you might be able to craft an SQL statement that gets you what you want, but
without correlated subqueries it is going to be difficult or impossible. I've
had this same sort of problem myself...
 
  Cheers
 
  Carl

The problem can be solved by using temporary tables.

Create temporary table tmp1
SELECT b.auctionId, b.cellId, MAX(b.bid) as bid FROM sa_bid as b,
  sa_auction AS a WHERE (a.state='active' AND b.auctionId=a.Id) GROUP BY
auctionId,cellId ;

Create temporary table tmp2
Select t1.auctionid, t1.cellid, t1.bid, min(b.bidtime) as bidtime from tmp1 
as t1, sa_bid as b
where (t1.auctionid = b.auctionid and t1.cellid = b.cellid and t1.bid = 
b.bid)
group by t1.auctionid,t1.cellid,t1.bid

Select t2.*, b.bidderid from tmp2 as t2, sa_bid as b
where t2.auctionid = b.auctionid and t2.cellid = b.cellid and t2.bid = 
b.bid and t2.bidtime = b.bidtime

I hope there may be better and simpler ways to achieve the objective.

Surely correlated subquery and derived table features might have been good 
features for such
situations.
Anvar.
 
  #
  # Table structure for table `sa_auction`
  #
 
  CREATE TABLE sa_auction (
id int(11) NOT NULL auto_increment,
start datetime NOT NULL default '-00-00 00:00:00',
end datetime NOT NULL default '-00-00 00:00:00',
state enum('waiting','active','expired') NOT NULL default 'waiting',
PRIMARY KEY  (id)
  ) TYPE=MyISAM;
 
  #
  # Dumping data for table `sa_auction`
  #
 
  INSERT INTO sa_auction (id, start, end, state) VALUES (1, '2002-01-23
  21:42:50', '2002-04-30 11:30:00', 'active'); INSERT INTO sa_auction (id,
  start, end, state) VALUES (2, '2002-01-23 21:42:50', '2002-02-09 06:30:00',
  'expired'); INSERT INTO sa_auction (id, start, end, state) VALUES (3,
  '2002-03-23 21:42:50', '2002-07-05 09:00:00', 'waiting'); INSERT INTO
  sa_auction (id, start, end, state) VALUES (4, '2002-03-23 21:42:50',
  '2002-08-01 11:30:00', 'waiting'); #
  
 
  #
  # Table structure for table `sa_bid`
  #
 
  CREATE TABLE sa_bid (
id int(11) NOT NULL 

synopsis of the problem (one line)

[root]

Description:

How-To-Repeat:

Fix:


Submitter-Id:  submitter ID
Originator:root
Organization:
 
MySQL support: [none | licence | email support | extended email support ]
Synopsis:  
Severity:  
Priority:  
Category:  mysql
Class: 
Release:   mysql-3.23.41 (Source distribution)

Environment:

System: Linux C4561Fabrizio-MIHSR.med 2.4.7-10 #1 Thu Sep 6 17:27:27 EDT 2001 i686 
unknown
Architecture: i686

Some paths:  /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc
GCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/2.96/specs
gcc version 2.96 2731 (Red Hat Linux 7.1 2.96-98)
Compilation info: CC='gcc'  CFLAGS='-O2 -march=i386 -mcpu=i686 -D_GNU_SOURCE 
-D_FILE_OFFSET_BITS=64 -D_LARGEFILE_SOURCE'  CXX='c++'  CXXFLAGS='-O2 -march=i386 
-mcpu=i686 -D_GNU_SOURCE -D_FILE_OFFSET_BITS=64 -D_LARGEFILE_SOURCE'  LDFLAGS=''
LIBC: 
lrwxrwxrwx1 root root   13 Feb  5 22:04 /lib/libc.so.6 - libc-2.2.4.so
-rwxr-xr-x1 root root  1282588 Sep  5 05:49 /lib/libc-2.2.4.so
-rw-r--r--1 root root 27304836 Sep  5 05:34 /usr/lib/libc.a
-rw-r--r--1 root root  178 Sep  5 05:34 /usr/lib/libc.so
Configure command: ./configure  i386-redhat-linux --prefix=/usr --exec-prefix=/usr 
--bindir=/usr/bin --sbindir=/usr/sbin --sysconfdir=/etc --datadir=/usr/share 
--includedir=/usr/include --libdir=/usr/lib --libexecdir=/usr/libexec 
--localstatedir=/var --sharedstatedir=/usr/com --mandir=/usr/share/man 
--infodir=/usr/share/info --without-debug --without-readline --enable-shared 
--with-extra-charsets=complex --with-bench --localstatedir=/var/lib/mysql 
--with-unix-socket-path=/var/lib/mysql/mysql.sock --with-mysqld-user=mysql 
--with-extra-charsets=all --disable-assember --with-berkeley-db 
--enable-large-files=yes --enable-largefile=yes --with-thread-safe-client 
--enable-assembler

















































































-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Database synchronization

[MySQL]

I'd like to connect a local sql server to a remote server and get 1
database synchronized between them.  For various reasons, I need to
try to run this from within the sql protocol (i.e. no ftp, etc.).

Replication is not the answer, and I can only work with one database
at a time, so a SELECT is out too.  It seems that only a perl/C
program can handle this task.  Ok, I can write the fetch  insert
process, but is there is simple way to 'know' that all records have
been accurately transferred?

TIA

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Callbacks or External Procedures?

[Andrew Crum]

Is there _any_ way to do something like Oracle's OCI callback functions?

What I need to do:
Whenever my MySQL database changes data (inserts, updates, whatever) I need
to call some of my own code.

Any ideas?

-Andrew


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Suspected Bug

[Fred Lovine]

MySQL is awesome, but I think I found a bug.

The following script:

CREATE  TABLE X1 (x smallint);
insert into X1 values(5);
RENAME TABLE X1 TO X2;

is producing the following error:

7 - Error on rename '.\db\X1.MY1' to '.\db\X2.MYI' (Errcode: 13)

The problem seems to be caused by using uppercase table names. The problem
does not happen when the table names are all lowercase.

MySQL version: 3.23.47-nt
Running on Windows 2000 Pro

Feel free to contact me if there are any questions.

Server Variables

VariableCLW Laptop
back_log50
basedir C:\CLW\MySQL\
binlog_cache_size   32768
character_set   latin1
character_sets  latin1 big5 czech euc_kr gb2312 gbk sjis tis620 ujis dec8 dos
german1 hp8 koi8_ru latin2 swe7 usa7 cp1251 danish hebrew win1251 estonia
hungarian koi8_ukr win1251ukr greek win1250 croat cp1257 latin5
concurrent_insert   ON
connect_timeout 5
datadir C:\CLW\MySQL\data\
delay_key_write ON
delayed_insert_limit100
delayed_insert_timeout  300
delayed_queue_size  1000
flush   OFF
flush_time  1800
have_bdbNO
have_gemini NO
have_innodb NO
have_isam   YES
have_raid   NO
have_opensslNO
init_file
interactive_timeout 28800
join_buffer_size131072
key_buffer_size 8384512
languageC:\CLW\MySQL\share\english\
large_files_support ON
log OFF
log_update  OFF
log_bin OFF
log_slave_updates   OFF
log_long_queriesOFF
long_query_time 10
low_priority_updatesOFF
lower_case_table_names  1
max_allowed_packet  130048
max_binlog_cache_size   4294967295
max_binlog_size 1073741824
max_connections 100
max_connect_errors  10
max_delayed_threads 20
max_heap_table_size 16777216
max_join_size   4294967295
max_sort_length 1024
max_user_connections0
max_tmp_tables  32
max_write_lock_count4294967295
myisam_max_extra_sort_file_size 256
myisam_max_sort_file_size   2047
myisam_recover_options  0
myisam_sort_buffer_size 8388608
net_buffer_length   16384
net_read_timeout30
net_retry_count 10
net_write_timeout   60
open_files_limit0
pid_fileC:\CLW\MySQL\data\laptop.pid
port3306
protocol_version10
record_buffer   131072
record_rnd_buffer   131072
query_buffer_size   0
safe_show_database  OFF
server_id   0
slave_net_timeout   3600
skip_lockingON
skip_networking OFF
skip_show_database  OFF
slow_launch_time2
socket  MySQL
sort_buffer 2097144
sql_mode0
table_cache 64
table_type  MYISAM
thread_cache_size   0
thread_stack65536
transaction_isolation   READ-COMMITTED
timezoneEastern Standard Time
tmp_table_size  33554432
tmpdir  C:\WINNT\TEMP\
version 3.23.47-nt
wait_timeout28800


Fred Lovine
[EMAIL PROTECTED]


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

HA mysql DB

[Wolfgang Pichler]

Hi List
  I have to set up a HA-Linux Server with Apache and mysql running on
two machines over load balancing. Apache isn't a problem. But mysql.
AFAIK it is impossible to cluster mysql - it only supports
replication. As I've said already, we have only two machines, and two
firewalls of course, for web and mysql server - but this two machines
should for the best work load balanced and failsafe. So, if one machine
fails because of a hardware fault - the other machine must be able to
overtake the whole work. So it is necersary to install Apache and mysql
on both server's. And, to get the best performance out of it, I've got
the following idea: The whole WebApplication has an own db interface so
that it should be possible to...

First Server runs: apache and mysql master - all write Operations are
done one the master - read operations from the first server are also
done one the master.

Second Server runs: apache and mysql slave - gets synchronized by the
master - read operations from the second server are done on the slave
- write operations are getting directed to the master - which then
synchronize the slave.

Because of dynamic load balancing this should get a nice average load. 

If then one machine fails - then you have on both machines everthing you
need to work in standalone mode. The only thing is, that you need some
scripts which switches the databases from slave to master mode, doing
synchronization after the are working again, redirecting all the
read/write operations to one machine, and some additional work.

So, after a long paragraph, what to you think - should this be possible
or have you any better ideas ? With what I'am wrong ?
 






-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Does delete from .. where (condition) use an index?

[Heikki Tuuri]

Eric,

-Original Message-
From: Eric Mayers [EMAIL PROTECTED]
To: Heikki Tuuri [EMAIL PROTECTED]
Date: Friday, February 15, 2002 1:10 AM
Subject: RE: Does delete from .. where (condition) use an index?


Heikki,

I thought I'd bring this off the list to reduce list traffic (if you
think its useful please feel free to respond on the list).  I only used


I respond to the mailing list because I think this is useful info for other
users.

the USE INDEX syntax in the EXPLAIN SELECT ... statement, not in the
delete.  It gives me a syntax error if I include USE INDEX in a DELETE
statement...

DELETE FROM SYSLOG USE INDEX(ds_index) WHERE datestamp  37827382;  Is
this the wrong syntax?  Can I tell it to use the index?


Sorry, no. There is no such syntax for DELETE FROM.

...

I'm working on getting more memory in the box so I can increase the size
of the buffer pool considerably.  I've also put a limit on the delete
statement and put it in a loop checking affected rows and that seems to
have helped signifigantly

A great idea, which did not come to my mind: using the LIMIT clause in
DELETE FROM you can easily control the size of deletions!

Again, thanks for your help!

Best Regards,
Eric Mayers
Software Engineer I
Captus Networks

Best regards,

Heikki Tuuri
Innobase Oy
---
InnoDB - transactions, row level locking, and foreign key support for MySQL
See http://www.innodb.com, download MySQL-Max from http://www.mysql.com



 -Original Message-
 From: Heikki Tuuri [mailto:[EMAIL PROTECTED]]
 Sent: Thursday, February 14, 2002 1:23 PM
 To: Eric Mayers; [EMAIL PROTECTED]
 Subject: Re: Does delete from .. where (condition) use an index?


 Eric,

 thank you for the printouts. Looks like MySQL refused to obey
 the USE INDEX
 clause! Did you run the EXPLAIN on a very small table?
 Otherwise I do not
 understand why it reports only 4700 rows.

 You have a very long-running mass delete below. It has row
 locks on 13000
 pages = 200 MB. It has delete marked (= deleted)  650 000
 rows (= number of
 undo log entries).

 There are quite a lot of disk reads and writes per second: 23
 + 28. The load
 is probably disk-bound.

 You have a very small buffer pool, only 1024 pages = 16 MB. I
 assume this is
 a stress test for a very small buffer pool.

 There are no dangling open transactions: only the mass delete
 and a single
 insert exist.

 In short, everything looks ok in the monitor output.

 I think it is worth to try 3.23.48 because the optimizer is
 tuned there. I
 tested deleting 15 000 rows from a 150 000 row table, and .48
 chose to use
 the right index.

 Make sure the delete batches are small enough so that the
 optimizer picks
 the right index. EXPLAIN SELECT is the way to study optimizer choices.

 Note that because of the insert buffer, InnoDB can make
 inserts with less
 disk i/o than deletes. In the monitor output you see 9 000
 000 insert buffer
 records were merged in 700 000 merges: on the average 13 records were
 inserted at a time. Deleting these records will use more disk
 i/o because
 there is no similar optimization in deletes.

 Best regards,

 Heikki Tuuri
 Innobase Oy
 ---
 Order technical MySQL/InnoDB support at https://order.mysql.com/
 See http://www.innodb.com for the online manual and latest
 news on InnoDB


 -Original Message-
 From: Eric Mayers [EMAIL PROTECTED]
 To: Heikki Tuuri [EMAIL PROTECTED]; [EMAIL PROTECTED]
 [EMAIL PROTECTED]
 Date: Thursday, February 14, 2002 7:28 PM
 Subject: RE: Does delete from .. where (condition) use an index?


 Heikki,
 
 Here is what you asked for:
 
 mysql EXPLAIN SELECT * FROM Syslog USE INDEX(ds_index)
 WHERE datestamp
 = 20020213185230;
 ++--+---+--+-+--+
 --+
 +
 | table  | type | possible_keys | key  | key_len | ref  |
 rows | Extra
 |
 ++--+---+--+-+--+
 --+
 +
 | Syslog | ALL  | ds_index  | NULL |NULL | NULL |
 4719 | where
 used |
 ++--+---+--+-+--+
 --+
 +
 1 row in set (0.00 sec)
 
 And here is a single InnoDB Monitor output:
 
 =
 020214  9:07:25 INNODB MONITOR OUTPUT
 =
 --
 SEMAPHORES
 --
 OS WAIT ARRAY INFO: reservation count 85376, signal count 83573
 Mutex spin waits 107640, rounds 1033422, OS waits 1
 RW-shared spins 34894, OS waits 17557; RW-excl spins 34957, OS waits
 34400
 
 TRANSACTIONS
 
 Trx id counter 0 10816708
 Purge done for trx's n:o  0 475530 undo n:o  0 0
 Total number of lock structs in row lock hash table 13009
 ---TRANSACTION 0 10816707, OS thread id 876555, not started, runs or
 sleeps
 MySQL thread id 205, query id 12109132 localhost root
 INSERT INTO logs.Syslog set caid='630', datestamp=NULL,
 message='4187481481 1481418748 418748 418748
 ---TRANSACTION 0 5633944, OS thread id 1445901 updating or deleting,