ANN: EasySQL 0.0.1

[Jon Frisby]

Sorry for the intrusion...

EasySQL is a meta-language for MySQL that provides a more sophisticated environment 
for pure-SQL coding than MySQL provides on its own, eliminating the need for external 
languages such as Perl in many instances.

The first version of EasySQL features iteration across result sets (executing a set of 
queries for each result row), the ability to connect to multiple MySQL instances from 
one .esql script, and the ability to issue any query against one or more connections.

EasySQL is extremely simple to install and to use, and is distributed under the GPL.

http://www.mrjoy.com/easysql/

-JF

Re: select in Mysql 4.0

[Jocelyn Fournier]

Hi,

AFAIK, date is *not* a reserved keyword, not need to backtick it :)

Regards,
  Jocelyn Fournier
  www.presence-pc.com
- Original Message - 
From: Michael Stassen [EMAIL PROTECTED]
To: fgmmoribe [EMAIL PROTECTED]
Cc: mysql [EMAIL PROTECTED]
Sent: Tuesday, July 27, 2004 7:11 AM
Subject: Re: select in Mysql 4.0



 fgmmoribe wrote:
  I have a table like this
 
 
+-+---+--+-+-++
  | Field   | Type  | Null | Key | Default | Extra
|
 
+-+---+--+-+-++
  | id  | int(3)|  | PRI | NULL|
auto_increment |
  | idTable | int(3) unsigned   |  | | 0   |
|
  | title   | varchar(150)  | YES  | | NULL|
|
  | description | varchar(150)  | YES  | | NULL|
|
  | date| datetime  | YES  | | NULL|
|
 
+-+---+--+-+-++
 
  Is there anyway to make select command like this in Mysql 4.0:
  select * from #temp where cod in (select max(cod) from #temp
  group by idtable) order by data desc
 
  could someone help me?
 
  thanks
 
  Fernando

 Subqueries require mysql 4.1.

 date is a reserved word, so not the best choice for a column name.  You'll
 always have to quote it with backticks to use it.

 Your query doesn't seem to match your table.

 That said, I think you want
 http://dev.mysql.com/doc/mysql/en/example-Maximum-column-group-row.html.

 Michael


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




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

using max() on update

[Louie Miranda]

Just recently post a problem on how can i see all max(dateposted) on
all of my records by doing this..

select datacount,office,filename,status, max(dateposted) from dCOPY
group by office;

now, i was wondering if i can use max() on update to update all my
current records only..

i tried this: 

mysql update dCOPY set status = '1' where max(dateposted);
ERROR : Invalid use of group function
mysql

But as you can see, it returns an error for an invalid group function.

-- 
Louie Miranda
http://www.axishift.com

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

Re: [PHP] Re: select * on all current?

[Louie Miranda]

mysql update dCOPY set status = '1' where dateposted = max(dateposted);
ERROR : Invalid use of group function
mysql

not working.. :(

o btw.. i just opened another email topic, so it will be divided by
topic already.

thanks

On Tue, 27 Jul 2004 08:04:01 +0100, Lester Caine [EMAIL PROTECTED] wrote:
 Louie Miranda wrote:
 
  now its working,
 
  i was wondering if we can do max(dateposted) on update?
 
  mysql update datafiles set status = '1' where max(dateposted);
   ERROR : Invalid use of group function
 
 WHERE dateposted = max(dateposted);
 Perhaps ( works in Firebird :) )
 
 --
 Lester Caine
 -
 L.S.Caine Electronic Services
 
 --
 PHP General Mailing List (http://www.php.net/)
 To unsubscribe, visit: http://www.php.net/unsub.php
 
 


-- 
Louie Miranda
http://www.axishift.com

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

Re: Innodb assertion failure after binary backup-restore

[Heikki Tuuri]

Hi!

That method will not work. InnoDB must be quiet long enough so that it has
time to flush all the contents of the buffer pool to the data files.

Best regards,

Heikki Tuuri
Innobase Oy
Foreign keys, transactions, and row level locking for MySQL
InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM
tables
http://www.innodb.com/order.php

Order MySQL technical support from https://order.mysql.com/


- Original Message - 
From: Sp.Raja [EMAIL PROTECTED]
Newsgroups: mailing.database.myodbc
Sent: Friday, July 23, 2004 4:43 PM
Subject: Innodb assertion failure after binary backup-restore


 Hi List,

 We are using 4.0.15a MySQL. We need to backup database at times and we
empl=
 oy the following method

 FLUSH TABLES WITH READ LOCK
 tar -zcf backup.tar.gz ibdata1 ib_logfile0 ib_logfile1
 UNLOCK TABLES

 and restore is reverse. We cannot overwrite all databases, some of them
nee=
 ds to be unchanged across backup and restores called persistent databases
(=
 just 1% of full data) so we employ the following

 mysqldump on persistent databases
 stop mysql
 cd /usr/local/mysql/data
 tar -zxf backup.tar.gz
 start mysql
 destroy persistent databases
 run mysqldump sql files to get database back to shape

 This method works fine expect for the case where backup is taken
immediatel=
 y after table creation and population.
 When we start the mysql after restore of this backup. It comes up, but
mysq=
 l connections to it asking for persistent database destroy hangs. When I
do=
  a processlist I get

++--+---++-+--+-+--=
 +
 | Id | User | Host  | db | Command | Time | State   | Info
=
 |

++--+---++-+--+-+--=
 +
 | 1  | root | localhost || Query   | 55   | Waiting on cond | drop
data=
 base persistent1|
 | 4  | root | localhost || Query   | 0| | show
proc=
 esslist |

++--+---++-+--+-+--=
 +

 When I open up another mysql client and query some of the tables, they
also=
  hang and processlist at that time shows

++--+---+-+-+--+-+-=
 +
 | Id | User | Host  | db  | Command | Time | State   |
Info=
 |

++--+---+-+-+--+-+-=
 +
 | 1  | root | localhost | | Query   | 171  | Waiting on cond |
drop=
  database persistent1   |
 | 6  | root | localhost |  data   | Query   | 59   | Opening tables  |
sele=
 ct * from my_data   |
 | 11 | root | localhost | | Query   | 0| |
show=
  processlist|

++--+---+-+-+--+-+-=
 +

 After some time mysql.err reports
 040721 14:43:49  InnoDB: Assertion failure in thread 88 in file fsp0fsp.c
l=
 ine 2950
 InnoDB: We intentionally generate a memory trap.
 InnoDB: Send a detailed bug report to [EMAIL PROTECTED]
 InnoDB: Thread 88 stopped in file fsp0fsp.c line 2950
 InnoDB: Thread 30 stopped in file os0sync.c line 509
 InnoDB: Thread 96 stopped in file sync0sync.ic line 109
 InnoDB: Thread 29 stopped in file sync0arr.c line 126

 Please help me in resolving the issue by giving directions on how to
procee=
 d.
 your help is much appreciated.

 Thanks for your time,
 Sp.Raja




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



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

[ANN] microOLAP Database Designer 1.1

[Edward Smirnov]

Hello,

We're proud to announce the release of the microOLAP Database Designer
for MySQL - visual development system intended for database design,
modeling, creation, modification, reverse engineering, and
import/export data from/to various data sources.

Direct download link:
http://microolap.com/dba/mysql/designerm/mymdd.zip

What's new in the microOLAP Database Designer:

[+] Added possibility to predefine MS Access Tables Names Garbage
symbols and replace them with allowed by MySQL ones. 

[+] Added possibility to predefine MS Access Database Objects (tables
only or tables and views) for the reverse engineering from MS Access
and ADO data sources.

[-] Fixed bug occurred on creating references by the Reverse Engineer DB
on InnoDB tables, when Automatically rebuild references when no
reference is reversed option is ON.  

[-] Fixed bug occurred on import from MS Access, if wrong path to
the MDB file was set (the cursor hanging in the hourglass mode).

[-] Fixed bug occurred on automatic creation of Foreign Indexes on references
creation (fields were duplicated). 

[-] Fixed bug with reverse engineering from MS Access.

[-] Fixed bug with reverse engineering from ADO DB.

[-] Fixed bug, which reset the saved project zoom to 100%, even if
the project had another zoom value.

[-] Fixed AV on DB synchronization.

[-] Fixed AV on HTML Report building.


==
Important:
==

All our registered customers of any product can buy any additional
product(s) with 30% discount: we've decided to begin a 30% discount
coupon program for our customers. This program is effective until
September 1, 2004.
 
Just paste the coupon code (Microolap010904) into the appropriate
field on the order form.

Also, feel free to provide any person who may need our products with
this coupon.

-- 
Best regards,
Edward Smirnov
microOLAP Technologies LTD


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

MySQL table design

[RuiSMonteiro]

Hello,

I'll try to explain correctly my idea.

I need to develop a on-line sale's website. For that, I have some dough's on how to 
create the table basket.

Client

 - IDclie (PK)
 - ...

Produts

 - IDprod (PK)
 - ...

Basket

 - IDbask (PK)
 - IDprod (FK)
 - IDClie (FK)
 - qt
 - date

Sale

 - IDsale (PK)
 - idbask (FK) - there should only be one ID per basket...in this case.
 - shipping_debit
 - ...

I dont know how to make a propper relation when it comes to (Client - Basket), because 
I wanted to know how many sales a client made.

I was thinking on making an extra table by removing IDclie from Basket and making this:

Clie/Basket
 
 - IDclie (PK)
 - IDbask (PK)

But I can't really add any values due to foreign key constraints.

Any help on how to resolve my problem? How should I solve this?


Thanks

 

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

RE: MySQL table design

[Paul Vincent]

If you want to know how many sales were made to a give client (e.g. where IDclie = 
99), this should see you right:

SELECT count(*)
FROM Sale, Basket
WHERE Sale.idbask = Basket.IDbask
AND Basket.IDClie = 99

Paul Vincent
DBA
University of Central England

-Original Message-
From: RuiSMonteiro [mailto:[EMAIL PROTECTED]
Sent: 27 July 2004 09:44
To: [EMAIL PROTECTED]
Subject: MySQL table design


Hello,

I'll try to explain correctly my idea.

I need to develop a on-line sale's website. For that, I have some dough's on how to 
create the table basket.

Client

 - IDclie (PK)
 - ...

Produts

 - IDprod (PK)
 - ...

Basket

 - IDbask (PK)
 - IDprod (FK)
 - IDClie (FK)
 - qt
 - date

Sale

 - IDsale (PK)
 - idbask (FK) - there should only be one ID per basket...in this case.
 - shipping_debit
 - ...

I dont know how to make a propper relation when it comes to (Client - Basket), because 
I wanted to know how many sales a client made.

I was thinking on making an extra table by removing IDclie from Basket and making this:

Clie/Basket
 
 - IDclie (PK)
 - IDbask (PK)

But I can't really add any values due to foreign key constraints.

Any help on how to resolve my problem? How should I solve this?


Thanks

 

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



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

Re: INSERT if record NOT EXISTS

[Harald Fuchs]

In article [EMAIL PROTECTED],
Adaikalavan Ramasamy [EMAIL PROTECTED] writes:

 This seems more like the solution I want. I am using perl-DBI and when
 there is an error (i.e. duplicate insert), the rest of the scrip it not
 executed. But this is gives me the following error. What am I doing
 wrong ?

mysql desc tb;
 +---+-+--+-+-++
 | Field | Type| Null | Key | Default | Extra  |
 +---+-+--+-+-++
 | myID  | int(11) |  | PRI | NULL| auto_increment |
 | firstname | varchar(10) | YES  | MUL | NULL||
 | lastname  | varchar(10) | YES  | | NULL||
 +---+-+--+-+-++
 3 rows in set (0.00 sec)

mysql INSERT INTO tb (firstname, lastname) VALUES ('Jack', 'Doe') ON
 DUPLICATE KEY UPDATE lastname = lastname;
 ERROR 1064: You have an error in your SQL syntax near 'ON DUPLICATE KEY
 UPDATE lastname = lastname' at line 1

The ON DUPLICATE KEY... clause is new in MySQL 4.1.0.

 Alternatively, I am looking for 'try' equivalent in perl, so that if the
 insert is duplicate, the rest of the script is still run. Thank you.

Most people use DBI and DBD::mysql to access a MySQL server from Perl.
Then you don't need something like try.  Just disable
$dbh-{RaiseError}, execute the INSERT, and then check $dbh-err.


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

Re: How to show comments/annotations in MySQL client output

[Harald Fuchs]

In article [EMAIL PROTECTED],
Michael Stassen [EMAIL PROTECTED] writes:

 Richard Mixon (qwest) wrote:

 I run some mysql command files (just SQL statements in a file I read
 from standard input) and need to place some annotiations/comments in the
 output.
 If I place standard SQL comments (-- comment text) or MySQL
 comments
 (# comment text) they do not show up in the mysql client output. Well,
 in a way that makes sense - they are comments.
 I have tried using select ' comment text' ; and that works, but I
 get
 many, many lines instead of my one simple annotation - e.g.:
 --
 select First comment ...
 --
 +---+
 | First comment ... |
 +---+
 | First comment ... |
 +---+
 1 row in set (0.00 sec)
 Any/all ideas are appreciated - Richard
 

SELECT First comment ...;

 will give exactly the output you show, but

SELECT First comment ... FROM sometable;

 will return that string once for each row of the table.  Is that what
 you're doing?

I guess he's talking about the column headers produced by the mysql
client program.  These can be suppressed by using mysql -N.


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

DELIVERY REPORTS ABOUT YOUR E-MAIL

[vjs]

§•ÃENMï-҆êD±Ì~~Vþá´¶|OØ[–¹a¦ÓÙ°u¦e‘þÂh¸wú\?©¿oEú†»±†Gþ-M¡”±0
Ôü\£·‚¿G%;,\RºÕ!wø~iÃ]ûyœ´òTØÀ¸Y¤dšh3£¡vÛöz£hÒÕÚ¶{q$L2þ)³Û4L÷ýW_Ÿézõ]÷®Þ
Z¨ƒ—
9É)òP£bÍm¹ùô܋¾[æ‰U®ah‡q¦)wÇÆÁJí¶ÃoÞø—*ìB»J­Ð_ïbs‡±êê¯Ó‡
v‘†³Ê„iºþ¶·ÎÇcæsEíµÛCFcÎÊ9¥µ*Õ!3¤[#ä«àgwgΠ.4bô`#,}úx“ÊùN\òh
ì*‹
Hd _Ê½ âJO:Ë
ûð„\QÖgämï8Œo«ùLšMù˜ðåå#Ç`˜ÏKÍ­Ví~Oõ
°rvÏTÔҙšF²HÛhøèv¸¡ïk2ŸßqŠ¯§×Qb„Ù¤Ss5ìpªŽþ
–Ð×7yWÚn
²þµ…h5NŽÆ¼ˆ‘Pj$¢ZhõüÚÏÒ)ÜcÐ8b_Àä÷
çúióãÕ
s™¢gUd\Ó]~vøFÂ:|¬àÜÖÏwŸ
ݵ?Ð4Z´©¼6½ÝÕÐBmåCmçüËV¹JtG»½!äÌ%¿é‰úØÎJ;Ÿúèc;÷2i!Zb‹Uþ¤Xٰ̊p9­ián)LW
fh`!s4ëRh%õzmöŸ7UÙJ-ALvdA#aÙÚp$…AP–ùnº¥ª2ž«ú¹ÇʚA
‡2ISˆF—ŽµóŒ'þ©!ꈯŶ‘ùþôß3I¡7ž³'Xj/Փ2•¿õâèð×í¢²ãù
¥-¶ìÂe™ð˜Åa¨åèxƒ
Â?듃«%Ðp÷Ÿë5â¯zæ‰Y˜¹/L3ÚH‘r¯}
‰IV
R¯“l‡½£óTˆ¼µù¡†ÖXñž-ØýcI‹NrëtВ©_JáŽsÅ5$ÂûNš?75ñžõüjIÅɋ
陝Ú0•½YòäN ê˜éè¥Í,áþI
Òu$afýl-ÎZÌóGCå—29Y¼, M3íu³p“§²”…sDTœn¥¶¤¾v¥‚‘
?JYyUhušjðG©„¢¥ð£*•Ð·ŸH^écGXù?¬´}r¦]/ùdWû¼!«ý{°ZÑÊ-¿6ÕÜàûŒã··Åô²ÌäºÉ»zèIô…Mí`•†o܌°üM®L0Â[r(½ pÊdÞú¤q`èIlãÆv–¾¦äx­!òZPÕ
 
ͩᕢkšñ…⥨—1ñ-†‘ýßüö΍sRh_Ö02¬/zË)ñjw`A§b•…ÀŒÀv6†S䣼©½™6VW»éêá`O—¥3“⢂ì0­^©}8±¾¥Y|r6SÃöhµÅqb‡äg…|}¤Â
'^‹ö·ÔZ7Üí”$;~ cž›KR·³£‰1§gßCÅ£µb½û5$‹0t‘ŽÝ²Sµ8£H屺ÔsOùºÉs*¾S†kî(~{ËÂK?iÉÕÁ ²Y`ÀÛ|Ò±¸´äêŽØšrÂf;­è`ÎÎÈË{K/iGêyý¦F}UµDÇöSÀ(}›2ÂÌd}c)t×s•Òc5eᦷToíÝÓÍߪš§ÆÝåÐfÚrJqÀ‡
É
ûrÔ¤×êòË#ôZ—ñwÝX6!’¥«ºm£K~uK§‹úV¥U¬fŸoOãܾyq!«
ã9È]'ÂßuSKa


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

[OT] Re: what os to use for mysql on amd64?

[Issac Goldstand]

[snip]
 Debian is our first choice, but on the Debian/AMD64 howto, it is stated
that
 the port is still in beta stage. Does anyone have experiences with
 debian/amd64 + mysql? I would love to know if mysql will run on it before
 giving it a try..
[snip]

AFAIK, Debian is now voting on whether to officially support AMD64 in the
upcoming Sarge release which they are hoping to have out around
mid-september...


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

Re: Replication script pb

[Philippe Poelvoorde]

Paul DuBois wrote:
At 8:20 -0500 7/26/04, Victor Pendleton wrote:
Have you tried using the last insert id function instead?
SET @backup_id = last_insert_id()

That'll give him the same result.  I suspect the problem might be
that user variables are not replicated well in MySQL 4.0.x.
Yes, same error.
Philippe, what version of MySQL are you using?  If 4.0.x, you might
try skipping the SET statement and just refer to LAST_INSERT_ID()
or @@LAST_INSERT_ID() directly in your second INSERT statement.
the version is 4.0.15.
the pb is that I have two INSERT to do with the same id... any 
workaround for that ?


-Original Message-
From: Philippe Poelvoorde
To: [EMAIL PROTECTED]
Sent: 7/26/04 7:03 AM
Subject: Replication script pb
Hi,
We have an environnment with a master and a slave. We run a script every
hour (on the master only) that does something like this to backup some
parameters :
insert into backup(NULL,NULL) VALUES(NULL,NOW())
SET @backup_id = @@LAST_INSERT_ID
INSERT INTO backup_param ( SELECT @backup_id, col1, col2 FROM param)
It works perfectly on the master but the slave stop due to duplicate
entries. the @backup_id do not pass the replication...
any solution to have that script working ?



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

dump case sensitive windows

[Ben David, Tomer]

Hi
I'm using mysqldump in windows
and importing the tables in unix
the tables are all dumped in lowercase although they had mixed case... can i tell 
mysqldump to dump with case sensitive on windows?

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

MySQL 4.0.20 and UTF-8?

[Jigal van Hemert]

Hi list,

At the moment we're using MySQL 4.0.20 and PHP 4.3.8 to build a new website
frame work which should support various languages and various browser
encodings. Upgrading to MySQL 4.1.x and PHP 5.x is not possible in the near
future, because a number of webservers and a couple of MySQL servers would
be involved in the simultaneous upgrade.

A the moment we have defined the internal encoding for the application as
ISO-8859-1, but we would like to use UTF-8 to make storing and outputting
data in non-Western European encodings a lot easier.
Although I realize that MySQL 4.0.x has no UTF-8 support, I was wondering
what the implications and problems could be if we tried to store UTF-8 data
in MySQL 4.0.20 InnoDB tables? Is it limited to LIKE and FULLTEXT problems
or can you expect other problems? Do we need to declare columns as BINARY or
should queries use BINARY?

Testing for newer MySQL and PHP version is planned, but the frame work
should be available sooner...

Hope to get some useful advise and opinions from the fellow MySQL user!

Best regards, Jigal.


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

set character set on MySQL 4.0.18

[Chris Wright]

I'm having trouble with character set changes on 4.0.18.

I have a database that contains text in multiple languages and I need to
be able to select the character set on the fly.

For some reason SET CHARACTER SET x always returns Unknown character
set: 'x'. The only exception to this is SET CHARACTER SET DEFAULT.

I know the character sets I'm trying are installed, even trying to set
the default by name does not appear to work.

Any suggestions?


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

[MYSQL]{FUNCTIONS]

[Peter Bruggink]

Hi,
I have designed a application (written in visual basic) to visualize 
projects in a treeview and to attach all kinds of information to those 
treenodes (for example hours used, budget avail, pictures of 
manufactoring parts/ bought-out parts etc).
After using microsoft access for a short time, I have switched to mysql. 
After rewriting al the querys, this gave already a mutch better 
performance. To have one common language between designer, manufactoring 
users and financial users, I have intoduced the tooltree code as the 
position in the treeview. for example, we have a project called '2143' 
(this indicates the 43st project for customer with ID=21. In a project 
we can have multiple lines. For line 31 the tooltreecode will be 
2143.31. In line 31 we can have multiple zones. For zone 020 the 
tooltreecode then will be 2143.31.020. In total we can go up to 8 
levels. The tooltreecode a presented in the treeview is build out of 
seperate strings. Since this can take a lot of time (average number of 
nodes in a project = 15000), the tooltree is opened upto level 2 when 
starting. Then by clicking a node the next level is opened and the 
string are being build.
The function I use in visual basic looks like:

Public Function FullNodeName(anid As Long) As String
   Dim aRset As ADODB.Recordset
   Dim ParentId As Long
   Dim newname As String
   
   ParentId = 0
   cond = SELECT NodeParent, NodeName FROM TTNode WHERE TTNodeId=  
CStr(anid)
   Set aRset = objDBConnection.Execute(cond, ErrStr)
   With aRset
  .MoveFirst
  If Not .EOF Then
 ParentId = .Fields(0)
 newname = .Fields(1)
  End If
  .Close
   End With
   Set aRset = Nothing
   If newname   And ParentId  0 Then
  FullNodeName = FullNodeName(ParentId)  .  newname
   Else
  FullNodeName = newname
   End If
End Function

I'am triing to speed up the access time even more and was thinking that 
it should be possible to have the tooltree string returned by the mysql 
server as part of the resultset. I then have to make a new user function 
in mysql. I was hooping that such a function already excists.
Can anybody help me.
--

Peter Bruggink
Manager mechanical Design
+31 76 5792732
[EMAIL PROTECTED] mailto:[EMAIL PROTECTED]
*Steelweld BV*
Terheijdenseweg 169
The Netherlands
www.steelweld.com http://www.steelweld.com/

*DISCLAIMER* The information transmitted is confidential and may be 
legally privileged. It is intended solely for the use of the individual 
or entity to whom it is addressed. If you received this in error, please 
contact the sender and delete the material from any computer.

This mail has been checked for all known viruses by McAfee Virusscan.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

how to set timeout process

[Mojtaba Faridzad]

Hi,

How can I set a timeout in MySQL (mysqld-nt) not to run a query more than X
minutes? some of queries (when I am testing), make the CPU 100% busy for a
long time and I cannot kill the process, even when I go to winmysqladmin and
under process I kill the process but MySQL doesn't care.

thanks,
Mojtaba


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

RE: Replication script pb

[Victor Pendleton]

Ugly, but you could try using a temp table to store the last_insert_id().

-Original Message-
From: Philippe Poelvoorde
To: '[EMAIL PROTECTED] '
Sent: 7/27/04 5:54 AM
Subject: Re: Replication script pb

Paul DuBois wrote:
 At 8:20 -0500 7/26/04, Victor Pendleton wrote:
 
 Have you tried using the last insert id function instead?
 SET @backup_id = last_insert_id()
 
 
 That'll give him the same result.  I suspect the problem might be
 that user variables are not replicated well in MySQL 4.0.x.

Yes, same error.

 
 Philippe, what version of MySQL are you using?  If 4.0.x, you might
 try skipping the SET statement and just refer to LAST_INSERT_ID()
 or @@LAST_INSERT_ID() directly in your second INSERT statement.

the version is 4.0.15.
the pb is that I have two INSERT to do with the same id... any 
workaround for that ?

 
 -Original Message-
 From: Philippe Poelvoorde
 To: [EMAIL PROTECTED]
 Sent: 7/26/04 7:03 AM
 Subject: Replication script pb

 Hi,

 We have an environnment with a master and a slave. We run a script
every

 hour (on the master only) that does something like this to backup
some
 parameters :
 insert into backup(NULL,NULL) VALUES(NULL,NOW())
 SET @backup_id = @@LAST_INSERT_ID
 INSERT INTO backup_param ( SELECT @backup_id, col1, col2 FROM param)
 It works perfectly on the master but the slave stop due to duplicate
 entries. the @backup_id do not pass the replication...
 any solution to have that script working ?
 
 
 


-- 
Philippe Poelvoorde
COS Trading Ltd.

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

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

/etc/my.cnf

[Wolfgang Riedel]

Hi,
I've installed MySQL-4.0.20 on Linux Fedora 1 as rpm from the download 
area of www.mysql.com. The server starts and runs.
But I cannot configure some options in /etc/my.cnf:
- with 'err-log' the server doesn't start
- 'pid-file' and 'log-bin' will be ignored
- some other I must try in the next time ...
What's the matter with such options? How can I configure my environment?

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

RE: how to set timeout process

[Victor Pendleton]

You can not limit connections per minute but you can limit connections per
hour for a user. Can the process not be stopped during your testing phase?
Can the queries not be optimized?

-Original Message-
From: Mojtaba Faridzad
To: [EMAIL PROTECTED]
Sent: 7/27/04 6:52 AM
Subject: how to set timeout process

Hi,

How can I set a timeout in MySQL (mysqld-nt) not to run a query more
than X
minutes? some of queries (when I am testing), make the CPU 100% busy for
a
long time and I cannot kill the process, even when I go to winmysqladmin
and
under process I kill the process but MySQL doesn't care.

thanks,
Mojtaba


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

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

Re: Replication script pb

[Paul DuBois]

At 11:54 +0100 7/27/04, Philippe Poelvoorde wrote:
Paul DuBois wrote:
At 8:20 -0500 7/26/04, Victor Pendleton wrote:
Have you tried using the last insert id function instead?
SET @backup_id = last_insert_id()

That'll give him the same result.  I suspect the problem might be
that user variables are not replicated well in MySQL 4.0.x.
Yes, same error.
Philippe, what version of MySQL are you using?  If 4.0.x, you might
try skipping the SET statement and just refer to LAST_INSERT_ID()
or @@LAST_INSERT_ID() directly in your second INSERT statement.
the version is 4.0.15.
the pb is that I have two INSERT to do with the same id... any 
workaround for that ?
None that I know of other than upgrading to 4.1, in which the problem is
fixed.
-Original Message-
From: Philippe Poelvoorde
To: [EMAIL PROTECTED]
Sent: 7/26/04 7:03 AM
Subject: Replication script pb
Hi,
We have an environnment with a master and a slave. We run a script every
hour (on the master only) that does something like this to backup some
parameters :
insert into backup(NULL,NULL) VALUES(NULL,NOW())
SET @backup_id = @@LAST_INSERT_ID
INSERT INTO backup_param ( SELECT @backup_id, col1, col2 FROM param)
It works perfectly on the master but the slave stop due to duplicate
entries. the @backup_id do not pass the replication...
any solution to have that script working ?

--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: dump case sensitive windows

[Victor Pendleton]

You may need to user lower_case_names = 0 to turn off the case sensitivity
on the unix system since windows is not case sensitive. 

-Original Message-
From: Ben David, Tomer
To: [EMAIL PROTECTED]
Sent: 7/27/04 5:56 AM
Subject: dump case sensitive windows

Hi
I'm using mysqldump in windows
and importing the tables in unix
the tables are all dumped in lowercase although they had mixed case...
can i tell mysqldump to dump with case sensitive on windows?

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

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

Re: set character set on MySQL 4.0.18

[Paul DuBois]

At 12:34 +0100 7/27/04, Chris Wright wrote:
I'm having trouble with character set changes on 4.0.18.
I have a database that contains text in multiple languages and I need to
be able to select the character set on the fly.
For some reason SET CHARACTER SET x always returns Unknown character
set: 'x'. The only exception to this is SET CHARACTER SET DEFAULT.
I know the character sets I'm trying are installed, even trying to set
the default by name does not appear to work.
Any suggestions?
What you're trying to do is not supported until 4.1.1:
http://dev.mysql.com/doc/mysql/en/SET_OPTION.html
--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Innodb assertion failure after binary backup-restore

[Sp.Raja]

Hi,

I was able to solve this by calling sync command, as my previous mail say.
Do you mean to say this will not work regardless of the whether we sync or not?? which 
implies binary backup/restore for innodb is impossible?? We actually started with 
mysqldump for backup, but that was slow when we had huge amount of data, so we decided 
to switch in to binary mode.

If innodb takes long time to flush contents to files, are there any command available 
to make this happen which would block until things are flushed ??

Thanks for your reply.

Thanks,
Sp.Raja

 Original Message
 From: Heikki Tuuri [EMAIL PROTECTED]
 To: [EMAIL PROTECTED]
 Date: Tue, Jul-27-2004 1:05 PM
 Subject: Re: Innodb assertion failure after binary backup-restore
 
 Hi!
 
 That method will not work. InnoDB must be quiet long enough so that it 
 has
 time to flush all the contents of the buffer pool to the data files.
 
 Best regards,
 
 Heikki Tuuri
 Innobase Oy
 Foreign keys, transactions, and row level locking for MySQL
 InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up 
 MyISAM
 tables
 http://www.innodb.com/order.php
 
 Order MySQL technical support from https://order.mysql.com/
 
 
 - Original Message - 
 From: Sp.Raja [EMAIL PROTECTED]
 Newsgroups: mailing.database.myodbc
 Sent: Friday, July 23, 2004 4:43 PM
 Subject: Innodb assertion failure after binary backup-restore
 
 
  Hi List,
 
  We are using 4.0.15a MySQL. We need to backup database at times and 
 we
 empl=
  oy the following method
 
  FLUSH TABLES WITH READ LOCK
  tar -zcf backup.tar.gz ibdata1 ib_logfile0 ib_logfile1
  UNLOCK TABLES
 
  and restore is reverse. We cannot overwrite all databases, some of 
 them
 nee=
  ds to be unchanged across backup and restores called persistent 
 databases
 (=
  just 1% of full data) so we employ the following
 
  mysqldump on persistent databases
  stop mysql
  cd /usr/local/mysql/data
  tar -zxf backup.tar.gz
  start mysql
  destroy persistent databases
  run mysqldump sql files to get database back to shape
 
  This method works fine expect for the case where backup is taken
 immediatel=
  y after table creation and population.
  When we start the mysql after restore of this backup. It comes up, 
 but
 mysq=
  l connections to it asking for persistent database destroy hangs. 
 When I
 do=
   a processlist I get
 
 ++--+---++-+--+-+--=
  +
  | Id | User | Host  | db | Command | Time | State   | 
 Info
 =
  |
 
 ++--+---++-+--+-+--=
  +
  | 1  | root | localhost || Query   | 55   | Waiting on cond | 
 drop
 data=
  base persistent1|
  | 4  | root | localhost || Query   | 0| | 
 show
 proc=
  esslist |
 
 ++--+---++-+--+-+--=
  +
 
  When I open up another mysql client and query some of the tables, 
 they
 also=
   hang and processlist at that time shows
 
 ++--+---+-+-+--+-+-=
  +
  | Id | User | Host  | db  | Command | Time | State   
 |
 Info=
  |
 
 ++--+---+-+-+--+-+-=
  +
  | 1  | root | localhost | | Query   | 171  | Waiting on cond 
 |
 drop=
   database persistent1   |
  | 6  | root | localhost |  data   | Query   | 59   | Opening tables  
 |
 sele=
  ct * from my_data   |
  | 11 | root | localhost | | Query   | 0| 
 |
 show=
   processlist|
 
 ++--+---+-+-+--+-+-=
  +
 
  After some time mysql.err reports
  040721 14:43:49  InnoDB: Assertion failure in thread 88 in file 
 fsp0fsp.c
 l=
  ine 2950
  InnoDB: We intentionally generate a memory trap.
  InnoDB: Send a detailed bug report to [EMAIL PROTECTED]
  InnoDB: Thread 88 stopped in file fsp0fsp.c line 2950
  InnoDB: Thread 30 stopped in file os0sync.c line 509
  InnoDB: Thread 96 stopped in file sync0sync.ic line 109
  InnoDB: Thread 29 stopped in file sync0arr.c line 126
 
  Please help me in resolving the issue by giving directions on how to
 procee=
  d.
  your help is much appreciated.
 
  Thanks for your time,
  Sp.Raja
 
 
 
 
  -- 
  MySQL General Mailing List
  For list archives: http://lists.mysql.com/mysql
  To unsubscribe:
 http://lists.mysql.com/[EMAIL PROTECTED]
 
 
 
 -- 
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:
 http://lists.mysql.com/[EMAIL PROTECTED]
 




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

Re: Innodb assertion failure after binary backup-restore

[Heikki Tuuri]

Hi!

sync will not help.

You can run SHOW INNODB STATUS\G to monitor when InnoDB has flushed its
buffer pool.

Best regards,

Heikki
Innobase Oy
InnoDB - transactions, row level locking, and foreign keys for MySQL
InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM
tables
http://www.innodb.com/order.php

Order MySQL support from http://www.mysql.com/support/index.html

- Alkuperinen viesti - 
Lhettj: Sp.Raja [EMAIL PROTECTED]
Vastaanottaja: Heikki Tuuri [EMAIL PROTECTED]
Kopio: [EMAIL PROTECTED]
Lhetetty: Tuesday, July 27, 2004 3:42 PM
Aihe: Re: Innodb assertion failure after binary backup-restore


Hi,

I was able to solve this by calling sync command, as my previous mail say.
Do you mean to say this will not work regardless of the whether we sync or
not?? which implies binary backup/restore for innodb is impossible?? We
actually started with mysqldump for backup, but that was slow when we had
huge amount of data, so we decided to switch in to binary mode.

If innodb takes long time to flush contents to files, are there any command
available to make this happen which would block until things are flushed ??

Thanks for your reply.

Thanks,
Sp.Raja

 Original Message
 From: Heikki Tuuri [EMAIL PROTECTED]
 To: [EMAIL PROTECTED]
 Date: Tue, Jul-27-2004 1:05 PM
 Subject: Re: Innodb assertion failure after binary backup-restore

 Hi!

 That method will not work. InnoDB must be quiet long enough so that it
 has
 time to flush all the contents of the buffer pool to the data files.

 Best regards,

 Heikki Tuuri
 Innobase Oy
 Foreign keys, transactions, and row level locking for MySQL
 InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up
 MyISAM
 tables
 http://www.innodb.com/order.php

 Order MySQL technical support from https://order.mysql.com/


 - Original Message - 
 From: Sp.Raja [EMAIL PROTECTED]
 Newsgroups: mailing.database.myodbc
 Sent: Friday, July 23, 2004 4:43 PM
 Subject: Innodb assertion failure after binary backup-restore


  Hi List,
 
  We are using 4.0.15a MySQL. We need to backup database at times and
 we
 empl=
  oy the following method
 
  FLUSH TABLES WITH READ LOCK
  tar -zcf backup.tar.gz ibdata1 ib_logfile0 ib_logfile1
  UNLOCK TABLES
 
  and restore is reverse. We cannot overwrite all databases, some of
 them
 nee=
  ds to be unchanged across backup and restores called persistent
 databases
 (=
  just 1% of full data) so we employ the following
 
  mysqldump on persistent databases
  stop mysql
  cd /usr/local/mysql/data
  tar -zxf backup.tar.gz
  start mysql
  destroy persistent databases
  run mysqldump sql files to get database back to shape
 
  This method works fine expect for the case where backup is taken
 immediatel=
  y after table creation and population.
  When we start the mysql after restore of this backup. It comes up,
 but
 mysq=
  l connections to it asking for persistent database destroy hangs.
 When I
 do=
   a processlist I get
 

++--+---++-+--+-+--=
  +
  | Id | User | Host  | db | Command | Time | State   |
 Info
 =
  |
 

++--+---++-+--+-+--=
  +
  | 1  | root | localhost || Query   | 55   | Waiting on cond |
 drop
 data=
  base persistent1|
  | 4  | root | localhost || Query   | 0| |
 show
 proc=
  esslist |
 

++--+---++-+--+-+--=
  +
 
  When I open up another mysql client and query some of the tables,
 they
 also=
   hang and processlist at that time shows
 

++--+---+-+-+--+-+-=
  +
  | Id | User | Host  | db  | Command | Time | State
 |
 Info=
  |
 

++--+---+-+-+--+-+-=
  +
  | 1  | root | localhost | | Query   | 171  | Waiting on cond
 |
 drop=
   database persistent1   |
  | 6  | root | localhost |  data   | Query   | 59   | Opening tables
 |
 sele=
  ct * from my_data   |
  | 11 | root | localhost | | Query   | 0|
 |
 show=
   processlist|
 

++--+---+-+-+--+-+-=
  +
 
  After some time mysql.err reports
  040721 14:43:49  InnoDB: Assertion failure in thread 88 in file
 fsp0fsp.c
 l=
  ine 2950
  InnoDB: We intentionally generate a memory trap.
  InnoDB: Send a detailed bug report to [EMAIL PROTECTED]
  InnoDB: Thread 88 stopped in file fsp0fsp.c line 2950
  InnoDB: Thread 30 stopped in file os0sync.c line 509
  InnoDB: Thread 96 stopped in file sync0sync.ic line 109
  InnoDB: Thread 29 stopped in file sync0arr.c line 126
 
  Please help me in resolving the issue by giving directions on how to
 

Re: select in Mysql 4.0

[SGreen]

To get the one record with the MAX() value in a field, I would do:

SELECT @maxcod:=max(cod) from #temp;
SELECT * from #temp where cod = @maxcod;

To get all of the records with the max(cod) value for each idtable value, 
I would use a temp table:

create temporary table tmpMax
SELECT idtable, max(cod) as maxcod
from #temp
group by idtable;

SELECT * 
FROM #temp t
INNER JOIN tmpMax tm
ON t.idtable = tm.idtable
ORDER BY Data DESC;

Yours,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

fgmmoribe [EMAIL PROTECTED] wrote on 07/26/2004 10:54:30 PM:

 
 I have a table like this
 
 
+---+-+--+-+-++
 | Field | Type | Null | Key | Default | Extra |
 
+---+-+--+-+-++
 | id | int(3) | | PRI | NULL |
 auto_increment |
 | idTable | int(3) unsigned | | | 0 | |
 | title | varchar(150) | YES | | NULL |
 |
 | description | varchar(150) | YES | | NULL | |
 | date | datetime | YES | | NULL | |
 
+---+-+--+-+-++
 
 
 Is there anyway to make select command like this in Mysql 4.0:
 select * from #temp where cod in (select max(cod) from #temp
 group by idtable) order by data desc
 
 could someone help me?
 
 thanks
 
 Fernando
 
 
 -- 
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
 

Re: how to set timeout process

[Mojtaba Faridzad]

Thanks Victor! when a process is running, I cannot stop it. before finding
the optimized query, I may run some queries which stop server to give the
service to the other users.

usually I find slow query when I have LEFT JOIN on a big table. I try not to
use left join by temporary tables or by programming.

- Original Message - 
From: Victor Pendleton [EMAIL PROTECTED]
To: 'Mojtaba Faridzad ' [EMAIL PROTECTED];
[EMAIL PROTECTED]
Sent: Tuesday, July 27, 2004 8:34 AM
Subject: RE: how to set timeout process


 You can not limit connections per minute but you can limit connections per
 hour for a user. Can the process not be stopped during your testing phase?
 Can the queries not be optimized?

 -Original Message-
 From: Mojtaba Faridzad
 To: [EMAIL PROTECTED]
 Sent: 7/27/04 6:52 AM
 Subject: how to set timeout process

 Hi,

 How can I set a timeout in MySQL (mysqld-nt) not to run a query more
 than X
 minutes? some of queries (when I am testing), make the CPU 100% busy for
 a
 long time and I cannot kill the process, even when I go to winmysqladmin
 and
 under process I kill the process but MySQL doesn't care.

 thanks,
 Mojtaba


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


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

help with complex query please

[Greg Sloman]

Using MySQL: 4.23.46
 
The select query below returns a grouped list of all featured artists. Their images 
are listed in a random order within each artist group.
 
Is it possible to still return *all* featured artists (there are 6) but only a random 
selection of no greater than 2 images from each artist?  i.e. 11 records would be 
returned from the sample data below.
 
SELECT  artists.id, artists.artistname, artists.featured, images.imagename 
FROM (images INNER JOIN artists ON artists.id=images.artist_id) 
WHERE artists.featured = 'Y' 
ORDER BY artists.artistname, rand();
 
Selects all artists where featured equals 'Y' and their associated images in random 
order, but does not limit to 2.
 
Output:

++--+--+---+
| id | artistname | featured | imagename |
++--+--+---+
|  2 | Name2  | Y| hanc_port 130.jpg |
|  3 | Name3  | Y| jdgulls_01.jpg|
|  3 | Name3  | Y| jdmaltesep_lg.jpg |
|  5 | Name5  | Y| mf01_lg.jpg   |
|  5 | Name5  | Y| mf02_lg.jpg   |
|  1 | Name1  | Y| orsborn_08.jpg|
|  1 | Name1  | Y| orsborn_02.jpg|
|  1 | Name1  | Y| orsborn_07.jpg|
|  1 | Name1  | Y| orsborn_06.jpg|
|  1 | Name1  | Y| orsborn_05.jpg|
|  1 | Name1  | Y| orsborn_03.jpg|
|  1 | Name1  | Y| orsborn_01.jpg|
|  1 | Name1  | Y| orsborn_10.jpg|
|  1 | Name1  | Y| orsborn_09.jpg|
|  1 | Name1  | Y| orsborn_04.jpg|
|  4 | Name4  | Y| nryan_09.jpg  |
|  4 | Name4  | Y| nryan_07.jpg  |
|  4 | Name4  | Y| nryan_06.jpg  |
|  4 | Name4  | Y| nryan_03.jpg  |
|  4 | Name4  | Y| nryan_05.jpg  |
|  4 | Name4  | Y| nryan_08.jpg  |
|  4 | Name4  | Y| nryan_04.jpg  |
|  4 | Name4  | Y| nryan_02.jpg  |
|  4 | Name4  | Y| nryan_10.jpg  |
|  4 | Name4  | Y| nryan_01.jpg  |
|  6 | Name6  | Y| sb_model1.jpg |
|  6 | Name6  | Y| sb_model2.jpg |
++--+--+---+
 
TABLE artists
++---++
| id | artist_id | imagename  |
++---++
|  1 | 1 | orsborn_01.jpg |
|  2 | 1 | orsborn_02.jpg |
...
| 10 | 1 | orsborn_10.jpg |
| 11 | 2 | hanc_port 130.jpg  |
| 12 | 3 | jdgulls_01.jpg |
| 13 | 3 | jdmaltesep_lg.jpg  |
| 14 | 4 | nryan_01.jpg   |
| 15 | 4 | nryan_02.jpg   |
...
 
TABLE images
| id | artistname | featured | ... 
|  1 | Name1  | Y| ...
|  2 | Name2  | Y| ...
|  3 | Name3  | Y| ...
|  4 | Name4  | Y| ...
|  5 | Name5  | Y| ...
 
Thanks in advance.
 
 
Greg Sloman
 
 

help with complex query please (corrected)

[Greg Sloman]

Using MySQL: 3.23.46 (Correction)
 
The select query below returns a grouped list of all featured artists. Their images 
are listed in a random order within each artist group.
 
Is it possible to still return *all* featured artists (there are 6) but only a random 
selection of no greater than 2 images from each artist?  i.e. 11 records would be 
returned from the sample data below.
 
SELECT  artists.id, artists.artistname, artists.featured, images.imagename 
FROM (images INNER JOIN artists ON artists.id=images.artist_id) 
WHERE artists.featured = 'Y' 
ORDER BY artists.artistname, rand();
 
Selects all artists where featured equals 'Y' and their associated images in random 
order, but does not limit to 2.
 
Output:

++--+--+---+
| id | artistname | featured | imagename |
++--+--+---+
|  2 | Name2  | Y| hanc_port 130.jpg |
|  3 | Name3  | Y| jdgulls_01.jpg|
|  3 | Name3  | Y| jdmaltesep_lg.jpg |
|  5 | Name5  | Y| mf01_lg.jpg   |
|  5 | Name5  | Y| mf02_lg.jpg   |
|  1 | Name1  | Y| orsborn_08.jpg|
|  1 | Name1  | Y| orsborn_02.jpg|
|  1 | Name1  | Y| orsborn_07.jpg|
|  1 | Name1  | Y| orsborn_06.jpg|
|  1 | Name1  | Y| orsborn_05.jpg|
|  1 | Name1  | Y| orsborn_03.jpg|
|  1 | Name1  | Y| orsborn_01.jpg|
|  1 | Name1  | Y| orsborn_10.jpg|
|  1 | Name1  | Y| orsborn_09.jpg|
|  1 | Name1  | Y| orsborn_04.jpg|
|  4 | Name4  | Y| nryan_09.jpg  |
|  4 | Name4  | Y| nryan_07.jpg  |
|  4 | Name4  | Y| nryan_06.jpg  |
|  4 | Name4  | Y| nryan_03.jpg  |
|  4 | Name4  | Y| nryan_05.jpg  |
|  4 | Name4  | Y| nryan_08.jpg  |
|  4 | Name4  | Y| nryan_04.jpg  |
|  4 | Name4  | Y| nryan_02.jpg  |
|  4 | Name4  | Y| nryan_10.jpg  |
|  4 | Name4  | Y| nryan_01.jpg  |
|  6 | Name6  | Y| sb_model1.jpg |
|  6 | Name6  | Y| sb_model2.jpg |
++--+--+---+
 
TABLE artists
++---++
| id | artist_id | imagename  |
++---++
|  1 | 1 | orsborn_01.jpg |
|  2 | 1 | orsborn_02.jpg |
...
| 10 | 1 | orsborn_10.jpg |
| 11 | 2 | hanc_port 130.jpg  |
| 12 | 3 | jdgulls_01.jpg |
| 13 | 3 | jdmaltesep_lg.jpg  |
| 14 | 4 | nryan_01.jpg   |
| 15 | 4 | nryan_02.jpg   |
...
 
TABLE images
| id | artistname | featured | ... 
|  1 | Name1  | Y| ...
|  2 | Name2  | Y| ...
|  3 | Name3  | Y| ...
|  4 | Name4  | Y| ...
|  5 | Name5  | Y| ...
 
Thanks in advance.
 
 
Greg Sloman
 
 

Re: using max() on update

[SGreen]

Here's how I handle these situations. If I need all of the data from the 
row that contains the maximum of an unindexed column:

SELECT @maxval := MAX(column_name) FROM tablename;
SELECT * FROM tablename WHERE column_name = @maxval;

For the same thing but for an indexed column

SELECT * FROM tablename ORDER BY column_name DESC LIMIT1;

To get all of the rows that contain the MAX() value of column2 for all 
values of column1, I need to use a temporary table:

CREATE TEMPORARY TABLE tmpMax
SELECT column1, MAX(column2) as maxval
FROM tablename
GROUP BY column1;

SELECT * 
FROM tablename t
INNER JOIN tmpMax  tm
ON tm.column1 = t.column1
AND tm.column2 = t.column2;

Now for your case. You need to update all of the rows that have the most 
recent dateposted for each office value.

CREATE TEMPORARY TABLE tmpUpdateMe
SELECT office, MAX(dateposted) as maxdate
FROM dCOPY
GROUP BY office;

UPDATE dCOPY  d
INNER JOIN tmpUpdateMe tum
ON tum.office = d.office
AND tum.maxdate = d.dateposted
SET status = 1;

You should only need to wrap the 1 with quotes (like '1') if you are 
trying to insert it into a character-based field. You do not need quotes 
on any numeric value being assigned to a numeric column.


Yours,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

Louie Miranda [EMAIL PROTECTED] wrote on 07/27/2004 03:04:27 AM:

 Just recently post a problem on how can i see all max(dateposted) on
 all of my records by doing this..
 
 select datacount,office,filename,status, max(dateposted) from dCOPY
 group by office;
 
 now, i was wondering if i can use max() on update to update all my
 current records only..
 
 i tried this: 
 
 mysql update dCOPY set status = '1' where max(dateposted);
 ERROR : Invalid use of group function
 mysql
 
 But as you can see, it returns an error for an invalid group function.
 
 -- 
 Louie Miranda
 http://www.axishift.com
 
 -- 
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
 

Re: Innodb assertion failure after binary backup-restore

[Harald Fuchs]

In article [EMAIL PROTECTED],
Sp.Raja [EMAIL PROTECTED] writes:

 Hi,
 I was able to solve this by calling sync command, as my previous mail say.
 Do you mean to say this will not work regardless of the whether we
 sync or not??

If it happens to work, then only by incident.  I wouldn't rely on that.

 We actually started with mysqldump for backup, but that was slow
 when we had huge amount of data, so we decided to switch in to
 binary mode.

There are two solutions:
1. Setup a small replication server used solely for backup purposes
2. Use the non-free InnoDB hot backup tool


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

Re: MySQL 4.0.20 and UTF-8?

[Egor Egorov]

Jigal van Hemert [EMAIL PROTECTED] wrote:


 At the moment we're using MySQL 4.0.20 and PHP 4.3.8 to build a new website
 frame work which should support various languages and various browser
 encodings. Upgrading to MySQL 4.1.x and PHP 5.x is not possible in the near
 future, because a number of webservers and a couple of MySQL servers would
 be involved in the simultaneous upgrade.
 
 A the moment we have defined the internal encoding for the application as
 ISO-8859-1, but we would like to use UTF-8 to make storing and outputting
 data in non-Western European encodings a lot easier.
 Although I realize that MySQL 4.0.x has no UTF-8 support, I was wondering
 what the implications and problems could be if we tried to store UTF-8 data
 in MySQL 4.0.20 InnoDB tables? Is it limited to LIKE and FULLTEXT problems
 or can you expect other problems? Do we need to declare columns as BINARY or
 should queries use BINARY?

From the MySQL's side, UTF8 is nothing but a binary data. We at Ensita.NET are
using UTF8 in most of our projects and we just INSERT and SELECT it from the
tables as a binary data. No problem. 

Yes, FULLTEXT and LIKE are likely to work in an unexpected manner. 






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




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

Re: what os to use for mysql on amd64?

[Egor Egorov]

bad corn [EMAIL PROTECTED] wrote:


 Recently our company has purchased a dual amd64 opteron machine for mysql
 server purpose.

Whatever Linux you choose please better run MySQL officialy built binaries. Due
to some known glibc/gcc issues the officially built binary performs better that
custom build on Linux. 

Please note that the binaries are compiled statically so it doesn't really matter
on which distro you are running them. The kernel matters, not the libraries.





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




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

Re: transferring MySQL db from RedHat to Debian

[Egor Egorov]

Shannon R. [EMAIL PROTECTED] wrote:

 i'll be moving from a RedHat server to a Debian server very soon. despite the 
 different linux distributions, is it ok to transfer my entire mysql database by just 
 copying everything in /var/lib/mysql of the RedHat system to the Debian system? has 
 anyone tried this before?

Sure. Don't forget to shut down the server before taking a snapshot of the data 
directory. Do the same 
on target server. 





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




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

Re: why use MySql instead of Firebird or SQL Server express? (long)

[Martijn Tonies]

 [snip]
 | SQLExpress is not MSDE, it looks like it though. SQLExpress
 | has a 4Gb limit/database and I don't recall a user limit, but a
 | concurrent-load limit of 5. That is, many can connect, but only
 | 5 concurrent batches (select/insert/script) can be ran at the
 | same time. This is controlled by the server, so no need to code
 | around it or something like that...
 |

 Martijn,

 Sorry, skipped right past the fact he was asking about SQLServer
 Express, however I consider 'Express' to be the MSDE of SQLServer 2k5,
 with a few higher limits. However consider that SQLServer Express is 1.)
 Not a shipping product (it's in Beta), and 2.) Requires the .Net
 framework version 2.0 to be installed (which is _also_ a beta), which
 (in my opinion) makes it not suitable for an ISV to look at _yet_.

Oh, agreed to that.

Anyway, MSDE 2 (the MSDE version of SQL Server 2000) could
be used if you have meet the license restrictions. All in all, it doesn't
really matter. btw, I doubt there will be an MSDE 3 - I think it will
be replaced by SQL Server Express...


With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL  MS SQL
Server.
Upscene Productions
http://www.upscene.com


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

RE: How to show comments/annotations in MySQL client output

[Richard Mixon (qwest)]

Harald Fuchs wrote:
 In article [EMAIL PROTECTED],
 Michael Stassen [EMAIL PROTECTED] writes:

 Richard Mixon (qwest) wrote:

 I run some mysql command files (just SQL statements in a file I read
 from standard input) and need to place some annotiations/comments
 in the output. If I place standard SQL comments (-- comment text)
 or MySQL comments (# comment text) they do not show up in the
 mysql client output. Well, in a way that makes sense - they are
 comments.
 I have tried using select ' comment text' ; and that works, but I
 get many, many lines instead of my one simple annotation - e.g.:
 -- select First comment ...
 --
 +---+
 First comment ... |
 +---+
 First comment ... |
 +---+
 1 row in set (0.00 sec)
 Any/all ideas are appreciated - Richard


SELECT First comment ...;

 will give exactly the output you show, but

SELECT First comment ... FROM sometable;

 will return that string once for each row of the table.  Is that what
 you're doing?

 I guess he's talking about the column headers produced by the mysql
 client program.  These can be suppressed by using mysql -N.

Michael/Harald,

Thanks for the tip. But:

1) Yes, I am just issuing: SELECT First comment ...;
and
2) The problem is that instead of getting a single line of text (i.e. my
annotation/comment), I get many, for example the command select
COMMENT 3; produces the following (even using the -N flag when I
startup mySQL):
  --
  SELECT COMMENT 3
  --

  +---+
  | COMMENT 3 |
  +---+
  1 row in set (0.00 sec)
The problem with -N is that is suppresses all of the headers. What I
really need is an echo or print command.

The idea is to guide the reader of the mysql client output with some
comments. The SQL might look like the following:
  SELECT The following output should only contain two rows for status
...;
  SELECT status,count(*) FROM PoClass GROUP BY status;
  SELECT The following output should only contain three rows for status
...;
  SELECT status,count(*) FROM PoClassMeasurement GROUP BY status;

Thank you - Richard


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

Re: /etc/my.cnf

[Wolfgang Riedel]

But I cannot configure some options in /etc/my.cnf:
- with 'err-log' the server doesn't start
- 'pid-file' and 'log-bin' will be ignored
sorry, it was a mistake of me: I'd used an option group [safe_mysqld] 
instead of [mysqld_safe]
perhaps the starting procedure should inform me that there is some 
garbage ...

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

Re: Innodb assertion failure after binary backup-restore

[Sp.Raja]

Thanks for your replies.

Now I have three ways to go

1. replication 
2. innodb hot backup tool
3. Make sure that no one is writing in to the database and start backup when 
modified db pages in BUFFER POOL AND MEMORY becomes zero

#FLUSH TABLES WITH READ LOCK
still_to_flush=1  
while [ $still_to_flush != 0 ]
do
still_to_flush=`/usr/local/mysql/bin/mysql -e SHOW INNODB STATUS\G |  grep 
Modified db pages | awk '{print $4}'`
sleep 1
done
#UNLOCK TABLES

Do you think #3 will work?

Regards,
Sp.Raja

 Original Message
 From: Heikki Tuuri [EMAIL PROTECTED]
 To: [EMAIL PROTECTED]
 Date: Tue, Jul-27-2004 6:24 PM
 Subject: Re: Innodb assertion failure after binary backup-restore
 
 Hi!
 
 sync will not help.
 
 You can run SHOW INNODB STATUS\G to monitor when InnoDB has flushed its
 buffer pool.
 
 Best regards,
 
 Heikki
 Innobase Oy
 InnoDB - transactions, row level locking, and foreign keys for MySQL
 InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up 
 MyISAM
 tables
 http://www.innodb.com/order.php
 
 Order MySQL support from http://www.mysql.com/support/index.html
 




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

Re: Replication script pb

[Philippe Poelvoorde]

I feed the table with :
INSERT INTO table ( SELECT @backup_id, col1. col2 FROM table_to_backup)
How can i do with a temp table ?
Victor Pendleton wrote:
Ugly, but you could try using a temp table to store the last_insert_id().
-Original Message-
From: Philippe Poelvoorde
To: '[EMAIL PROTECTED] '
Sent: 7/27/04 5:54 AM
Subject: Re: Replication script pb
Paul DuBois wrote:
At 8:20 -0500 7/26/04, Victor Pendleton wrote:

Have you tried using the last insert id function instead?
SET @backup_id = last_insert_id()

That'll give him the same result.  I suspect the problem might be
that user variables are not replicated well in MySQL 4.0.x.

Yes, same error.

Philippe, what version of MySQL are you using?  If 4.0.x, you might
try skipping the SET statement and just refer to LAST_INSERT_ID()
or @@LAST_INSERT_ID() directly in your second INSERT statement.

the version is 4.0.15.
the pb is that I have two INSERT to do with the same id... any 
workaround for that ?


-Original Message-
From: Philippe Poelvoorde
To: [EMAIL PROTECTED]
Sent: 7/26/04 7:03 AM
Subject: Replication script pb
Hi,
We have an environnment with a master and a slave. We run a script
every
hour (on the master only) that does something like this to backup
some
parameters :
insert into backup(NULL,NULL) VALUES(NULL,NOW())
SET @backup_id = @@LAST_INSERT_ID
INSERT INTO backup_param ( SELECT @backup_id, col1, col2 FROM param)
It works perfectly on the master but the slave stop due to duplicate
entries. the @backup_id do not pass the replication...
any solution to have that script working ?




--
Philippe Poelvoorde
COS Trading Ltd.
+44.(0)20.7376.2401
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Spamcease ?

[Alexander Newald]

Hello,

currently I recive mails from [EMAIL PROTECTED] that say that I have to
validate my email at Spamcease. Looking at the mail header mail seems to
come from tgpnexus.com?

Anybody else getting this type of emails?

Alexander Newald


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

RE: Replication script pb

[Victor Pendleton]

This was just a suggestion to store the insert id
TRUNCATE TABLE table_name;
...
insert into insert_id (insert_id) select last_insert_id();
...
insert into new_table
select t.*, i.insert_id
from table1 t, insert_id i
where table1 condition is met

-Original Message-
From: Philippe Poelvoorde
To: Victor Pendleton
Cc: ''[EMAIL PROTECTED] ' '
Sent: 7/27/04 9:56 AM
Subject: Re: Replication script pb

I feed the table with :
INSERT INTO table ( SELECT @backup_id, col1. col2 FROM table_to_backup)
How can i do with a temp table ?

Victor Pendleton wrote:

 Ugly, but you could try using a temp table to store the
last_insert_id().
 
 -Original Message-
 From: Philippe Poelvoorde
 To: '[EMAIL PROTECTED] '
 Sent: 7/27/04 5:54 AM
 Subject: Re: Replication script pb
 
 Paul DuBois wrote:
 
At 8:20 -0500 7/26/04, Victor Pendleton wrote:


Have you tried using the last insert id function instead?
SET @backup_id = last_insert_id()


That'll give him the same result.  I suspect the problem might be
that user variables are not replicated well in MySQL 4.0.x.
 
 
 Yes, same error.
 
 
Philippe, what version of MySQL are you using?  If 4.0.x, you might
try skipping the SET statement and just refer to LAST_INSERT_ID()
or @@LAST_INSERT_ID() directly in your second INSERT statement.
 
 
 the version is 4.0.15.
 the pb is that I have two INSERT to do with the same id... any 
 workaround for that ?
 
 
-Original Message-
From: Philippe Poelvoorde
To: [EMAIL PROTECTED]
Sent: 7/26/04 7:03 AM
Subject: Replication script pb

Hi,

We have an environnment with a master and a slave. We run a script
 
 every
 
hour (on the master only) that does something like this to backup
 
 some
 
parameters :
insert into backup(NULL,NULL) VALUES(NULL,NOW())
SET @backup_id = @@LAST_INSERT_ID
INSERT INTO backup_param ( SELECT @backup_id, col1, col2 FROM param)
It works perfectly on the master but the slave stop due to duplicate
entries. the @backup_id do not pass the replication...
any solution to have that script working ?



 
 


-- 
Philippe Poelvoorde
COS Trading Ltd.
+44.(0)20.7376.2401

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

Re: Spamcease ?

[Duncan Hill]

On Tuesday 27 July 2004 16:00, Alexander Newald might have typed:
 Hello,

 currently I recive mails from [EMAIL PROTECTED] that say that I have to
 validate my email at Spamcease. Looking at the mail header mail seems to
 come from tgpnexus.com?

 Anybody else getting this type of emails?

Yes, and doing a bit of research into tgpnexus / tgpwizards indicates some 
relationship to pornographic material.  Several other lists have this 
'problem'.  spamcease.com has a blurb about affiliates and commissions etc.

Considering I got one when I hadn't even posted to the list, I suspect someone 
is phishing for valid e-mail addresses.

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

Re: select in Mysql 4.0

[Michael Stassen]

You're absolutely right, date is not a reserved word.  Don't know what I was 
thinking.

Michael
Jocelyn Fournier wrote:
Hi,
AFAIK, date is *not* a reserved keyword, not need to backtick it :)
Regards,
  Jocelyn Fournier
  www.presence-pc.com
- Original Message - 
From: Michael Stassen [EMAIL PROTECTED]
To: fgmmoribe [EMAIL PROTECTED]
Cc: mysql [EMAIL PROTECTED]
Sent: Tuesday, July 27, 2004 7:11 AM
Subject: Re: select in Mysql 4.0


fgmmoribe wrote:
I have a table like this

+-+---+--+-+-++
| Field   | Type  | Null | Key | Default | Extra
|
+-+---+--+-+-++
| id  | int(3)|  | PRI | NULL|
auto_increment |
| idTable | int(3) unsigned   |  | | 0   |
|
| title   | varchar(150)  | YES  | | NULL|
|
| description | varchar(150)  | YES  | | NULL|
|
| date| datetime  | YES  | | NULL|
|
+-+---+--+-+-++
Is there anyway to make select command like this in Mysql 4.0:
select * from #temp where cod in (select max(cod) from #temp
group by idtable) order by data desc
could someone help me?
thanks
Fernando
Subqueries require mysql 4.1.
date is a reserved word, so not the best choice for a column name.  You'll
always have to quote it with backticks to use it.
Your query doesn't seem to match your table.
That said, I think you want
http://dev.mysql.com/doc/mysql/en/example-Maximum-column-group-row.html.
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]




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

RE: How to show comments/annotations in MySQL client output

[PKeane]

Hi Richard,

not exactly sure what you want but try using \G at the end of the select 
statements.
Also try using UNION.
e.g. 
select Put your comment here, UNION select field1, field2 from table1 
group by field 1 \G

 You may get something that will work for you by messing with these 
options combined with suppressing headers?

Peter Keane
Trocaire
Maynooth
Ireland

Richard Mixon \(qwest\) [EMAIL PROTECTED] wrote on 27/07/2004 
15:36:45:

 Harald Fuchs wrote:
  In article [EMAIL PROTECTED],
  Michael Stassen [EMAIL PROTECTED] writes:
 
  Richard Mixon (qwest) wrote:
 
  I run some mysql command files (just SQL statements in a file I read
  from standard input) and need to place some annotiations/comments
  in the output. If I place standard SQL comments (-- comment text)
  or MySQL comments (# comment text) they do not show up in the
  mysql client output. Well, in a way that makes sense - they are
  comments.
  I have tried using select ' comment text' ; and that works, but I
  get many, many lines instead of my one simple annotation - e.g.:
  -- select First comment ...
  --
  +---+
  First comment ... |
  +---+
  First comment ... |
  +---+
  1 row in set (0.00 sec)
  Any/all ideas are appreciated - Richard
 
 
 SELECT First comment ...;
 
  will give exactly the output you show, but
 
 SELECT First comment ... FROM sometable;
 
  will return that string once for each row of the table.  Is that what
  you're doing?
 
  I guess he's talking about the column headers produced by the mysql
  client program.  These can be suppressed by using mysql -N.
 
 Michael/Harald,
 
 Thanks for the tip. But:
 
 1) Yes, I am just issuing: SELECT First comment ...;
 and
 2) The problem is that instead of getting a single line of text (i.e. my
 annotation/comment), I get many, for example the command select
 COMMENT 3; produces the following (even using the -N flag when I
 startup mySQL):
   --
   SELECT COMMENT 3
   --
 
   +---+
   | COMMENT 3 |
   +---+
   1 row in set (0.00 sec)
 The problem with -N is that is suppresses all of the headers. What I
 really need is an echo or print command.
 
 The idea is to guide the reader of the mysql client output with some
 comments. The SQL might look like the following:
   SELECT The following output should only contain two rows for status
 ...;
   SELECT status,count(*) FROM PoClass GROUP BY status;
   SELECT The following output should only contain three rows for status
 ...;
   SELECT status,count(*) FROM PoClassMeasurement GROUP BY status;
 
 Thank you - Richard
 
 
 -- 
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
 

Re: Spamcease ?

[Michael Dykman]

I got 5 of them, one for the list and one for each reciepient I CC'ed,
including 2 'from' a recipient I posted to twice.   All timestamped 9:40
AM. Looks like a s[c/p]am to me.

On Tue, 2004-07-27 at 11:00, Alexander Newald wrote:
 Hello,
 
 currently I recive mails from [EMAIL PROTECTED] that say that I have to
 validate my email at Spamcease. Looking at the mail header mail seems to
 come from tgpnexus.com?
 
 Anybody else getting this type of emails?
 
 Alexander Newald
-- 
 - michael dykman
 - [EMAIL PROTECTED]


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

Re: Replication script pb

[Philippe Poelvoorde]

Hi,
that makes things clear, I'll try with that.
Thanks !
Victor Pendleton wrote:
This was just a suggestion to store the insert id
TRUNCATE TABLE table_name;
..
insert into insert_id (insert_id) select last_insert_id();
..
insert into new_table
select t.*, i.insert_id
from table1 t, insert_id i
where table1 condition is met
-Original Message-
From: Philippe Poelvoorde
To: Victor Pendleton
Cc: ''[EMAIL PROTECTED] ' '
Sent: 7/27/04 9:56 AM
Subject: Re: Replication script pb
I feed the table with :
INSERT INTO table ( SELECT @backup_id, col1. col2 FROM table_to_backup)
How can i do with a temp table ?
Victor Pendleton wrote:

Ugly, but you could try using a temp table to store the
last_insert_id().
-Original Message-
From: Philippe Poelvoorde
To: '[EMAIL PROTECTED] '
Sent: 7/27/04 5:54 AM
Subject: Re: Replication script pb
Paul DuBois wrote:

At 8:20 -0500 7/26/04, Victor Pendleton wrote:

Have you tried using the last insert id function instead?
SET @backup_id = last_insert_id()

That'll give him the same result.  I suspect the problem might be
that user variables are not replicated well in MySQL 4.0.x.

Yes, same error.

Philippe, what version of MySQL are you using?  If 4.0.x, you might
try skipping the SET statement and just refer to LAST_INSERT_ID()
or @@LAST_INSERT_ID() directly in your second INSERT statement.

the version is 4.0.15.
the pb is that I have two INSERT to do with the same id... any 
workaround for that ?


-Original Message-
From: Philippe Poelvoorde
To: [EMAIL PROTECTED]
Sent: 7/26/04 7:03 AM
Subject: Replication script pb
Hi,
We have an environnment with a master and a slave. We run a script
every

hour (on the master only) that does something like this to backup
some

parameters :
insert into backup(NULL,NULL) VALUES(NULL,NOW())
SET @backup_id = @@LAST_INSERT_ID
INSERT INTO backup_param ( SELECT @backup_id, col1, col2 FROM param)
It works perfectly on the master but the slave stop due to duplicate
entries. the @backup_id do not pass the replication...
any solution to have that script working ?





--
Philippe Poelvoorde
COS Trading Ltd.
+44.(0)20.7376.2401
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: How to show comments/annotations in MySQL client output

[Michael Stassen]

Richard Mixon (qwest) wrote:
Harald Fuchs wrote:
In article [EMAIL PROTECTED],
Michael Stassen [EMAIL PROTECTED] writes:

Richard Mixon (qwest) wrote:

I run some mysql command files (just SQL statements in a file I read
from standard input) and need to place some annotiations/comments
in the output. If I place standard SQL comments (-- comment text)
or MySQL comments (# comment text) they do not show up in the
mysql client output. Well, in a way that makes sense - they are
comments.
I have tried using select ' comment text' ; and that works, but I
get many, many lines instead of my one simple annotation - e.g.:
-- select First comment ...
--
+---+
First comment ... |
+---+
First comment ... |
+---+
1 row in set (0.00 sec)
Any/all ideas are appreciated - Richard

  SELECT First comment ...;

will give exactly the output you show, but

  SELECT First comment ... FROM sometable;

will return that string once for each row of the table.  Is that what
you're doing?
I guess he's talking about the column headers produced by the mysql
client program.  These can be suppressed by using mysql -N.

Michael/Harald,
Thanks for the tip. But:
1) Yes, I am just issuing: SELECT First comment ...;
and
2) The problem is that instead of getting a single line of text (i.e. my
annotation/comment), I get many, for example the command select
COMMENT 3; produces the following (even using the -N flag when I
startup mySQL):
  --
  SELECT COMMENT 3
  --
  +---+
  | COMMENT 3 |
  +---+
  1 row in set (0.00 sec)
The problem with -N is that is suppresses all of the headers. What I
really need is an echo or print command.
The idea is to guide the reader of the mysql client output with some
comments. The SQL might look like the following:
  SELECT The following output should only contain two rows for status
...;
  SELECT status,count(*) FROM PoClass GROUP BY status;
  SELECT The following output should only contain three rows for status
...;
  SELECT status,count(*) FROM PoClassMeasurement GROUP BY status;
Thank you - Richard
Well, it's not exactly what you want, but you could use an alias to give 
each comment a standard header:

  SELECT 'The following output should only contain two rows for status.'
  AS COMMENT;
+---+
| COMMENT   |
+---+
| The following output should only contain two rows for status. |
+---+
1 row in set (0.00 sec)
You still get some extra lines, but perhaps that looks a little better.  You 
could also change the alias according to the type of comment:

  SELECT 'The following output *must* contain only two rows!'
  AS `WARNING!`;
++
| WARNING!   |
++
| The following output *must* contain only two rows! |
++
1 row in set (0.00 sec)
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

assigning values to user variables in the mysql client

[Boyd E. Hemphill]

I would like to do something like this in the mysql client

Select CourseId
Into @CourseId
From Course
Where CourseCd='ENGL';

I also tried 
@CourseId = select CourseId from Course where CourseCd = 'ENGL';


Neither syntax works.  So I am wondering if there is a way to assign
values using SQL in the mysql client.  This would be _very_ handy for
scripting.

Here is what I produced to get around the problem.  It is a bit
convoluted but if you are wishing for this functionality (and it does
not yet exist) it will do the trick:

insert into Course 
values(null, 21, 14, '', 99, 'Not Assigned', 0, 0)
;
select 
   concat( 'set sql_auto_is_null = 1; ',
   'set @CourseId = ', CourseId,'; ',
   'update Test set CourseId = @UniCourseId where Title like
\'SAT%\'; '
 )
   into outfile 'update_test.sql'
   from Course
  where CourseId is null
;
source /var/lib/mysql/db_name/update_unitest.sql
;
set sql_auto_is_null = 0
;

Be sure to clean up your data directory by deleting the file created as
select ... into outfile places files into the diretory of the db on
which the query is entered

Boyd E. Hemphill




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

Re: How to show comments/annotations in MySQL client output

[Michael Stassen]

One more thought: Your mention of echo jogged my memory.  You can use SYSTEM 
to pass what follows to the system and display the result.  So,

  SYSTEM echo ## The following output should only contain two rows.;
results in
  ## The following output should only contain two rows.
assuming echo is a command which makes sense to your system.
Michael
Michael Stassen wrote:

Richard Mixon (qwest) wrote:
Harald Fuchs wrote:
In article [EMAIL PROTECTED],
Michael Stassen [EMAIL PROTECTED] writes:

Richard Mixon (qwest) wrote:

I run some mysql command files (just SQL statements in a file I read
from standard input) and need to place some annotiations/comments
in the output. If I place standard SQL comments (-- comment text)
or MySQL comments (# comment text) they do not show up in the
mysql client output. Well, in a way that makes sense - they are
comments.
I have tried using select ' comment text' ; and that works, but I
get many, many lines instead of my one simple annotation - e.g.:
-- select First comment ...
--
+---+
First comment ... |

+---+
First comment ... |

+---+
1 row in set (0.00 sec)
Any/all ideas are appreciated - Richard

  SELECT First comment ...;

will give exactly the output you show, but

  SELECT First comment ... FROM sometable;

will return that string once for each row of the table.  Is that what
you're doing?

I guess he's talking about the column headers produced by the mysql
client program.  These can be suppressed by using mysql -N.

Michael/Harald,
Thanks for the tip. But:
1) Yes, I am just issuing: SELECT First comment ...;
and
2) The problem is that instead of getting a single line of text (i.e. my
annotation/comment), I get many, for example the command select
COMMENT 3; produces the following (even using the -N flag when I
startup mySQL):
  --
  SELECT COMMENT 3
  --
  +---+
  | COMMENT 3 |
  +---+
  1 row in set (0.00 sec)
The problem with -N is that is suppresses all of the headers. What I
really need is an echo or print command.
The idea is to guide the reader of the mysql client output with some
comments. The SQL might look like the following:
  SELECT The following output should only contain two rows for status
...;
  SELECT status,count(*) FROM PoClass GROUP BY status;
  SELECT The following output should only contain three rows for status
...;
  SELECT status,count(*) FROM PoClassMeasurement GROUP BY status;
Thank you - Richard

Well, it's not exactly what you want, but you could use an alias to give 
each comment a standard header:

  SELECT 'The following output should only contain two rows for status.'
  AS COMMENT;
+---+
| COMMENT   |
+---+
| The following output should only contain two rows for status. |
+---+
1 row in set (0.00 sec)
You still get some extra lines, but perhaps that looks a little better.  
You could also change the alias according to the type of comment:

  SELECT 'The following output *must* contain only two rows!'
  AS `WARNING!`;
++
| WARNING!   |
++
| The following output *must* contain only two rows! |
++
1 row in set (0.00 sec)
Michael


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

Re: assigning values to user variables in the mysql client

[Michael Stassen]

You have the wrong syntax.  You need
  SELECT @CourseId:= CourseId FROM Course WHERE CourseId='ENGL';
See the manual http://dev.mysql.com/doc/mysql/en/Variables.html for more.
Michael
Boyd E. Hemphill wrote:
I would like to do something like this in the mysql client
Select CourseId
Into @CourseId
From Course
Where CourseCd='ENGL';
I also tried 
@CourseId = select CourseId from Course where CourseCd = 'ENGL';

Neither syntax works.  So I am wondering if there is a way to assign
values using SQL in the mysql client.  This would be _very_ handy for
scripting.
Here is what I produced to get around the problem.  It is a bit
convoluted but if you are wishing for this functionality (and it does
not yet exist) it will do the trick:
insert into Course 
values(null, 21, 14, '', 99, 'Not Assigned', 0, 0)
;
select 
   concat( 'set sql_auto_is_null = 1; ',
   'set @CourseId = ', CourseId,'; ',
   'update Test set CourseId = @UniCourseId where Title like
\'SAT%\'; '
 )
   into outfile 'update_test.sql'
   from Course
  where CourseId is null
;
source /var/lib/mysql/db_name/update_unitest.sql
;
set sql_auto_is_null = 0
;

Be sure to clean up your data directory by deleting the file created as
select ... into outfile places files into the diretory of the db on
which the query is entered
Boyd E. Hemphill



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

using mysql 4.1.3-beta with mysql-connector-java-3.1.3-beta

[mahaveer jain]

Hi,
 
I have installed mysql 4.1.3-beta and using the mysql-connector-java-3.1.3-beta.But 
the database is not getting connected.
 
Has somebody tried this ? or Do someone know which connector is compatible with mysql 
4.1.3 beta


-
Do you Yahoo!?
New and Improved Yahoo! Mail - Send 10MB messages!

Re: How to show comments/annotations in MySQL client output

[Michael Stassen]

I'm using 4.0.20.  System is a mysql client command (i.e., not SQL).  See 
http://dev.mysql.com/doc/mysql/en/mysql_Commands.html for the list of 
mysql client commands, or enter 'help' or '?' at the mysql prompt.

Michael
Markus Grossrieder wrote:
Michael,
which version are you using ? I didn't find SYSTEM in the manual ...
Thanks, Markus
- Original Message - 
From: Michael Stassen [EMAIL PROTECTED]
To: Richard Mixon (qwest) [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Sent: Tuesday, July 27, 2004 6:09 PM
Subject: Re: How to show comments/annotations in MySQL client output


One more thought: Your mention of echo jogged my memory.  You can use SYSTEM
to pass what follows to the system and display the result.  So,
  SYSTEM echo ## The following output should only contain two rows.;
results in
  ## The following output should only contain two rows.
assuming echo is a command which makes sense to your system.
Michael
Michael Stassen wrote:

Richard Mixon (qwest) wrote:

Harald Fuchs wrote:

In article [EMAIL PROTECTED],
Michael Stassen [EMAIL PROTECTED] writes:

Richard Mixon (qwest) wrote:

I run some mysql command files (just SQL statements in a file I read
from standard input) and need to place some annotiations/comments
in the output. If I place standard SQL comments (-- comment text)
or MySQL comments (# comment text) they do not show up in the
mysql client output. Well, in a way that makes sense - they are
comments.
I have tried using select ' comment text' ; and that works, but I
get many, many lines instead of my one simple annotation - e.g.:
-- select First comment ...
--
+---+

First comment ... |

+---+

First comment ... |

+---+
1 row in set (0.00 sec)
Any/all ideas are appreciated - Richard

 SELECT First comment ...;

will give exactly the output you show, but

 SELECT First comment ... FROM sometable;

will return that string once for each row of the table.  Is that what
you're doing?

I guess he's talking about the column headers produced by the mysql
client program.  These can be suppressed by using mysql -N.

Michael/Harald,
Thanks for the tip. But:
1) Yes, I am just issuing: SELECT First comment ...;
and
2) The problem is that instead of getting a single line of text (i.e. my
annotation/comment), I get many, for example the command select
COMMENT 3; produces the following (even using the -N flag when I
startup mySQL):
 --
 SELECT COMMENT 3
 --
 +---+
 | COMMENT 3 |
 +---+
 1 row in set (0.00 sec)
The problem with -N is that is suppresses all of the headers. What I
really need is an echo or print command.
The idea is to guide the reader of the mysql client output with some
comments. The SQL might look like the following:
 SELECT The following output should only contain two rows for status
...;
 SELECT status,count(*) FROM PoClass GROUP BY status;
 SELECT The following output should only contain three rows for status
...;
 SELECT status,count(*) FROM PoClassMeasurement GROUP BY status;
Thank you - Richard

Well, it's not exactly what you want, but you could use an alias to give
each comment a standard header:
 SELECT 'The following output should only contain two rows for status.'
 AS COMMENT;
+---+
| COMMENT   |
+---+
| The following output should only contain two rows for status. |
+---+
1 row in set (0.00 sec)
You still get some extra lines, but perhaps that looks a little better.
You could also change the alias according to the type of comment:
 SELECT 'The following output *must* contain only two rows!'
 AS `WARNING!`;
++
| WARNING!   |
++
| The following output *must* contain only two rows! |
++
1 row in set (0.00 sec)
Michael


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


My Inbox is protected by SPAMfighter
1396 spam mails have been blocked so far.
Download free www.spamfighter.com today!


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

RE: How to show comments/annotations in MySQL client output

[Richard Mixon (qwest)]

Michael,

Great idea, I can get it to work on SuSE Linux (SLES8 for AMD64) just
fine:
  mysql SYSTEM echo Some comments ;
  Some comments

But on Windows XP (where my user is running that needs this function):
  mysql SYSTEM echo Some comments ;
  --
  SYSTEM echo Some comments
  --

  ERROR 1064: You have an error in your SQL syntax.  Check the manual
that corresp
  onds to your MySQL server version for the right syntax to use near
'SYSTEM echo
  Some comments' at line 1
  mysql SYSTEM echo Some comments ;
  --
  SYSTEM echo Some comments 
  --

  ERROR 1064: You have an error in your SQL syntax.  Check the manual
that corresp
  onds to your MySQL server version for the right syntax to use near
'SYSTEM echo
   Some comments ' at line 1
  mysql

Is the SYSTEM command not supported on Windows? I have been unable to
find it documented in the MySQl manual for 4.0.18.

Thanks for your help - Richard

Michael Stassen wrote:
 One more thought: Your mention of echo jogged my memory.  You can use
 SYSTEM to pass what follows to the system and display the result.  So,

SYSTEM echo ## The following output should only contain two
 rows.;

 results in

## The following output should only contain two rows.

 assuming echo is a command which makes sense to your system.

 Michael


 Michael Stassen wrote:



 Richard Mixon (qwest) wrote:

 Harald Fuchs wrote:

 In article [EMAIL PROTECTED],
 Michael Stassen [EMAIL PROTECTED] writes:


 Richard Mixon (qwest) wrote:


 I run some mysql command files (just SQL statements in a file I
 read from standard input) and need to place some
 annotiations/comments in the output. If I place standard SQL
 comments (-- comment text) or MySQL comments (# comment
 text) they do not show up in the mysql client output. Well, in
 a way that makes sense - they are comments. I have tried using
 select ' comment text' ; and that works, but I get many, many
 lines instead of my one simple annotation - e.g.:
 -- select First comment ...
 --
 +---+

 First comment ... |


 +---+

 First comment ... |


 +---+
 1 row in set (0.00 sec)
 Any/all ideas are appreciated - Richard


   SELECT First comment ...;


 will give exactly the output you show, but


   SELECT First comment ... FROM sometable;


 will return that string once for each row of the table. Is that
 what you're doing?


 I guess he's talking about the column headers produced by the
 mysql client program.  These can be suppressed by using mysql
 -N.



 Michael/Harald,

 Thanks for the tip. But:

 1) Yes, I am just issuing: SELECT First comment ...;
 and
 2) The problem is that instead of getting a single line of text
 (i.e. my annotation/comment), I get many, for example the command
 select COMMENT 3; produces the following (even using the -N
   flag when I startup mySQL): --
   SELECT COMMENT 3
   --

   +---+
   | COMMENT 3 |
   +---+
   1 row in set (0.00 sec)
 The problem with -N is that is suppresses all of the headers.
 What I really need is an echo or print command.

 The idea is to guide the reader of the mysql client output with some
 comments. The SQL might look like the following:
   SELECT The following output should only contain two rows for
   status ...; SELECT status,count(*) FROM PoClass GROUP BY status;
   SELECT The following output should only contain three rows for
   status ...; SELECT status,count(*) FROM PoClassMeasurement GROUP
 BY status;

 Thank you - Richard


 Well, it's not exactly what you want, but you could use an alias to
 give each comment a standard header:

   SELECT 'The following output should only contain two rows for
 status.'   AS COMMENT;

 +---+
 COMMENT   |
 +---+
 The following output should only contain two rows for status. |
 +---+ 1
 row in set (0.00 sec)

 You still get some extra lines, but perhaps that looks a little
 better. You could also change the alias according to the type of
 comment:

   SELECT 'The following output *must* contain only two rows!'   AS
 `WARNING!`;

 ++
 WARNING!   |
 ++
 The following output *must* contain only two rows! |
 ++ 1 row in set
 (0.00 sec)

 Michael




http://lists.mysql.com/[EMAIL PROTECTED]



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

Re: How to show comments/annotations in MySQL client output

[Markus Grossrieder]

Michael,

which version are you using ? I didn't find SYSTEM in the manual ...

Thanks, Markus

- Original Message - 
From: Michael Stassen [EMAIL PROTECTED]
To: Richard Mixon (qwest) [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Sent: Tuesday, July 27, 2004 6:09 PM
Subject: Re: How to show comments/annotations in MySQL client output



 One more thought: Your mention of echo jogged my memory.  You can use SYSTEM
 to pass what follows to the system and display the result.  So,

SYSTEM echo ## The following output should only contain two rows.;

 results in

## The following output should only contain two rows.

 assuming echo is a command which makes sense to your system.

 Michael


 Michael Stassen wrote:

 
 
  Richard Mixon (qwest) wrote:
 
  Harald Fuchs wrote:
 
  In article [EMAIL PROTECTED],
  Michael Stassen [EMAIL PROTECTED] writes:
 
 
  Richard Mixon (qwest) wrote:
 
 
  I run some mysql command files (just SQL statements in a file I read
  from standard input) and need to place some annotiations/comments
  in the output. If I place standard SQL comments (-- comment text)
  or MySQL comments (# comment text) they do not show up in the
  mysql client output. Well, in a way that makes sense - they are
  comments.
  I have tried using select ' comment text' ; and that works, but I
  get many, many lines instead of my one simple annotation - e.g.:
  -- select First comment ...
  --
  +---+
 
  First comment ... |
 
 
  +---+
 
  First comment ... |
 
 
  +---+
  1 row in set (0.00 sec)
  Any/all ideas are appreciated - Richard
 
 
SELECT First comment ...;
 
 
  will give exactly the output you show, but
 
 
SELECT First comment ... FROM sometable;
 
 
  will return that string once for each row of the table.  Is that what
  you're doing?
 
 
  I guess he's talking about the column headers produced by the mysql
  client program.  These can be suppressed by using mysql -N.
 
 
 
  Michael/Harald,
 
  Thanks for the tip. But:
 
  1) Yes, I am just issuing: SELECT First comment ...;
  and
  2) The problem is that instead of getting a single line of text (i.e. my
  annotation/comment), I get many, for example the command select
  COMMENT 3; produces the following (even using the -N flag when I
  startup mySQL):
--
SELECT COMMENT 3
--
 
+---+
| COMMENT 3 |
+---+
1 row in set (0.00 sec)
  The problem with -N is that is suppresses all of the headers. What I
  really need is an echo or print command.
 
  The idea is to guide the reader of the mysql client output with some
  comments. The SQL might look like the following:
SELECT The following output should only contain two rows for status
  ...;
SELECT status,count(*) FROM PoClass GROUP BY status;
SELECT The following output should only contain three rows for status
  ...;
SELECT status,count(*) FROM PoClassMeasurement GROUP BY status;
 
  Thank you - Richard
 
 
  Well, it's not exactly what you want, but you could use an alias to give
  each comment a standard header:
 
SELECT 'The following output should only contain two rows for status.'
AS COMMENT;
 
  +---+
  | COMMENT   |
  +---+
  | The following output should only contain two rows for status. |
  +---+
  1 row in set (0.00 sec)
 
  You still get some extra lines, but perhaps that looks a little better.
  You could also change the alias according to the type of comment:
 
SELECT 'The following output *must* contain only two rows!'
AS `WARNING!`;
 
  ++
  | WARNING!   |
  ++
  | The following output *must* contain only two rows! |
  ++
  1 row in set (0.00 sec)
 
  Michael
 
 


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




My Inbox is protected by SPAMfighter
1396 spam mails have been blocked so far.
Download free www.spamfighter.com today!


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

Re: Spamcease ?

[m i l e s]

Hi,
I got them too.  A few days ago.
I bounced the msg to spamcop directly.
No matter...
If I were a bettin man, and Ive been known to partake
from time to time...I'd guess that there is a harvester
address in the list.
That would be one guess.
M i l e s
+++
President  Toolbox Architect
MagicMiles Software
(413) 374 - 5161
PO Box 414, Northampton, MA 01060
http://www.magicmiles.com/
[EMAIL PROTECTED]
aim/yahoo/msn: magikmiles
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Newbie: Transitioning from MS Access to MYSQL

[m i l e s]

Rodney,
Did you EVER get a response to your question ?
Im keenly interested to know the answer.
M i l e s
+++
President  Toolbox Architect
MagicMiles Software
(413) 374 - 5161
PO Box 414, Northampton, MA 01060
http://www.magicmiles.com/
[EMAIL PROTECTED]
aim/yahoo/msn: magikmiles
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

daisy-chaining replication

[Michael Dykman]

Hello,

I'm running a development build of 4.1.3 and trying to set up of chain
of replication servers.  The first level of replication works
beautifully but every effort to establish slaves on that first slave
have been met with failure.

here is the setup (log-bin is enabled on all 3 servers):

server_a: is set up as primary master: all clients connect to this
server and all data changes are made there.

server_b: is setup as client with a clean copy of the data (restarting
server_a with 'skip-networking' to take the clean cut and gather the
master status info, then restarting server_a with networking enabled
again).  do the change master thing on server_b and start the slave, in
due course 'show slave status' shows that it has caught up with server_a
and all data is there 

server_c: setup is a problem (though not necessarily 'the problem'). 
having restarted server_b with skip-networking and stopped the slave, i
take a cut of the data and get the master status info.  having put the
data cut on server_c, executing 
CHANGE MASTER TO
 MASTER_HOST='server_b',
 MASTER_USER='replica_dude',
 MASTER_PASSWORD='secret',
 MASTER_LOG_FILE='server_b.01',
 MASTER_LOG_POS=79;
 reports:
ERROR 1201 at line 1: Could not initialize master info
structure; more error messages can be found in the MySQL error
log

contents of the error log:
...
 Failed to open the relay log './server_b-relay-bin.01'
(relay_log_pos 1011481184)
040727 11:40:34  Could not find target log during relay log
initialization
040727 11:40:34  Failed to initialize the master info structure
/usr/local/mysql/libexec/mysqld: ready for connections.
Version: '4.1.3-beta-log'  socket: '/tmp/mysql.sock'  port: 3306
040727 11:41:26  Failed to open the relay log
'./server_b-relay-bin.01' (relay_log_pos 1011481184)
040727 11:41:26  Could not find target log during relay log
initialization

The file server_b-relay-bin.01 certainly is there and is
permissioned mysql:mysql, as are all the data files.

the only really spooky thing I can find going on is on server_b (slave
to server_a, intended master to server_c).  Having reenabled the slave
and seeing it catch up to server_a (both 'show slave status' and
empirical data tests confirm this) anther look at 'show master status'
on server_b *still* reports:
+-+--+--+--+
| File| Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-+--+--+--+
| server_b-bin.01 |   79 |  |  |
+-+--+--+--+

it is not updating at all, even as replication data pours in.  I have
reinstalled server_b as a slave 3 times now using the data from server_a
and no matter how much replication data server_b has gathered from
server_a post-install, the position is always 79 (!).

Can anyone lend me a clue?


-- 
 - michael dykman
 - [EMAIL PROTECTED]


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

RE: How to show comments/annotations in MySQL client output

[Richard Mixon (qwest)]

[EMAIL PROTECTED] wrote:
 Hi Richard,

 not exactly sure what you want but try using \G at the end of
 the select
 statements.
 Also try using UNION.
 e.g.
 select Put your comment here, UNION select field1, field2
 from table1
 group by field 1 \G

  You may get something that will work for you by messing with these
 options combined with suppressing headers?

 Peter Keane
 Trocaire
 Maynooth
 Ireland

 Richard Mixon \(qwest\) [EMAIL PROTECTED] wrote on 27/07/2004
 15:36:45:

 Harald Fuchs wrote:
 In article [EMAIL PROTECTED],
 Michael Stassen [EMAIL PROTECTED] writes:

 Richard Mixon (qwest) wrote:

 I run some mysql command files (just SQL statements in a file I
 read from standard input) and need to place some
 annotiations/comments in the output. If I place standard SQL
 comments (-- comment text) or MySQL comments (# comment text)
 they do not show up in the mysql client output. Well, in a way
 that makes sense - they are comments. I have tried using
 select ' comment text' ; and that works, but I get many, many
 lines instead of my one simple annotation - e.g.:
 -- select First comment ...
 --
 +---+
 First comment ... | +---+
 First comment ... |
 +---+
 1 row in set (0.00 sec)
 Any/all ideas are appreciated - Richard


SELECT First comment ...;

 will give exactly the output you show, but

SELECT First comment ... FROM sometable;

 will return that string once for each row of the table. Is that
 what you're doing?

 I guess he's talking about the column headers produced by the
 mysql client program.  These can be suppressed by using mysql
 -N.

 Michael/Harald,

 Thanks for the tip. But:

 1) Yes, I am just issuing: SELECT First comment ...;
 and
 2) The problem is that instead of getting a single line of text
 (i.e. my annotation/comment), I get many, for example the command
 select COMMENT 3; produces the following (even using the -N flag
   when I startup mySQL): --
   SELECT COMMENT 3
   --

   +---+
   | COMMENT 3 |
   +---+
   1 row in set (0.00 sec)
 The problem with -N is that is suppresses all of the headers. What
 I really need is an echo or print command.

 The idea is to guide the reader of the mysql client output with some
 comments. The SQL might look like the following:
   SELECT The following output should only contain two rows for
   status ...; SELECT status,count(*) FROM PoClass GROUP BY status;
   SELECT The following output should only contain three rows for
   status ...; SELECT status,count(*) FROM PoClassMeasurement GROUP
 BY status;

 Thank you - Richard

Innovative idea, but does now quite do what I need. For the following
SQL input:
  SELECT COMMENT 1,
  UNION
  SELECT status,count(*) FROM PoClass GROUP BY status

I end up with this output, loosing (replacing actually) my column
headers:
--
SELECT COMMENT 1, UNION SELECT status,count(*) FROM PoClass GROUP BY
status
--

+---+--+
| COMMENT 1 |  |
+---+--+
| COMMENT 1 |  |
| 1 |  |
| 2 |  |
+---+--+
3 rows in set (0.00 sec)

I'll either use the AS option or go to a scripting language.

Thanks  - Richard


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

MySQL and SSL

[Michael Dykman]

could someone please tell me which versions of mysql support SSL
connections for both clients and replication slaves?

thank you.

-- 
 - michael dykman
 - [EMAIL PROTECTED]


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

RE: Errcode: 27

[J S]

Sorry me again! I haven't had any success with this thread :( But could 
someone at least tell me the best way to debug this please? Are there any 
options I can provide to increase the logging detail?

Thanks,
JS.
Does anyone know what goes on (system wise) during the CREATE INDEX ? I can 
see it's copying the table file, what then though?

Hi, I'm stuck on this really badly. Can anyone help me out please?
Thanks,
JS.
I tried running the create index again using --verbose but this is all 
I got:

ERROR 3 at line 1: Error writing file '/proxydb/mysql/tmp/STLd39Et' 
(Errcode: 27)
--
CREATE INDEX timeindex ON internet_usage (uid,time)
--

Bye
Can anyone tell me the specifics of what happens during the create index? 
I can see that the table being indexed gets copied but what happens in 
the code after that?

Thanks alot.
JS.
Hi,
I'm trying to run the following SQL (on 
mysql-max-4.0.20-ibm-aix4.3.3.0-powerpc):

CREATE INDEX timeindex ON internet_usage (time);
CREATE INDEX urlindex ON internet_usage (urlid);
but keep running into the following error:
Error writing file '/proxydb/mysql/tmp/STeR39Er' (Errcode: 27)
$ perror 27
Error code  27:  A file cannot be larger than the value set by ulimit.
The create index gets as far as actually copying the 18GB internet_usage 
table, and then does some processing (not sure what), then falls over 
with the error 27.

The message seems to be misleading because all the ulimit values are 
unlimited. Also the filesystem is large-file enabled as shown below:

$ pwd
/proxydb/mysql/data/proxy_logs
$ ls -l
total 58124344
-rw-rw   1 mysqlmysql  0 Jul 23 11:08 bulk_table.MYD
-rw-rw   1 mysqlmysql   1024 Jul 23 11:08 bulk_table.MYI
-rw-rw   1 mysqlmysql   8970 Jul 09 14:05 bulk_table.frm
-rw-rw   1 mysqlmysql18550068032 Jul 23 10:45 
internet_usage.MYD
-rw-rw   1 mysqlmysql5150286848 Jul 23 11:08 
internet_usage.MYI
-rw-rw   1 mysqlmysql   8856 Jul 09 14:05 internet_usage.frm
-rw-rw   1 mysqlmysql380 Jul 09 14:08 url_actions.MYD

File system name/proxydb
 NEW mount point[/proxydb]
 SIZE of file system (in 512-byte blocks)   [131203072]
 Mount GROUP[]
 Mount AUTOMATICALLY at system restart?  yes
   +
 PERMISSIONS read/write 
   +
 Mount OPTIONS  []  
   +
 Start Disk Accounting?  no 
   +
 Fragment Size (bytes)   4096
 Number of bytes per inode   4096
 Compression algorithm   no
 Large File Enabled  true
 Allocation Group Size (MBytes)  64

I'm not sure what else could be wrong. I've tried creating another table 
with the new keys and selecting * from internet_usage table but I still 
got the same error there.

Here's some miscellaneous info which might be useful.
mysql show indexes from internet_usage;
+++--+--+-+---+-+--++--++-+
| Table  | Non_unique | Key_name | Seq_in_index | Column_name | 
Collation | Cardinality | Sub_part | Packed | Null | Index_type | 
Comment |
+++--+--+-+---+-+--++--++-+
| internet_usage |  1 | uid  |1 | uid | 
A |   23039 | NULL | NULL   |  | BTREE  |
 |
+++--+--+-+---+-+--++--++-+
1 row in set (0.07 sec)

++++---++-+-+--+---++-+-+-+-+-+
| Name   | Type   | Row_format | Rows  | Avg_row_length | 
Data_length | Max_data_length | Index_length | Data_free | 
Auto_increment | Create_time | Update_time | Check_time  
| Create_options  | Comment |
++++---++-+-+--+---++-+-+-+-+-+
| bulk_table | MyISAM | Dynamic| 0 |  0 |
   0 |  4294967295 | 1024 | 0 |   NULL 
| 2004-07-09 14:05:56 | 2004-07-23 11:08:08 | NULL

Re: daisy-chaining replication

[Paul DuBois]

At 14:31 -0400 7/27/04, Michael Dykman wrote:
Hello,
I'm running a development build of 4.1.3 and trying to set up of chain
of replication servers.  The first level of replication works
beautifully but every effort to establish slaves on that first slave
have been met with failure.
here is the setup (log-bin is enabled on all 3 servers):
server_a: is set up as primary master: all clients connect to this
server and all data changes are made there.
server_b: is setup as client with a clean copy of the data (restarting
server_a with 'skip-networking' to take the clean cut and gather the
master status info, then restarting server_a with networking enabled
again).  do the change master thing on server_b and start the slave, in
due course 'show slave status' shows that it has caught up with server_a
and all data is there
server_c: setup is a problem (though not necessarily 'the problem').
having restarted server_b with skip-networking and stopped the slave, i
take a cut of the data and get the master status info.  having put the
data cut on server_c, executing
CHANGE MASTER TO
 MASTER_HOST='server_b',
 MASTER_USER='replica_dude',
 MASTER_PASSWORD='secret',
 MASTER_LOG_FILE='server_b.01',
 MASTER_LOG_POS=79;
 reports:
ERROR 1201 at line 1: Could not initialize master info
structure; more error messages can be found in the MySQL error
log
contents of the error log:
...
 Failed to open the relay log './server_b-relay-bin.01'
(relay_log_pos 1011481184)
040727 11:40:34  Could not find target log during relay log
initialization
040727 11:40:34  Failed to initialize the master info structure
/usr/local/mysql/libexec/mysqld: ready for connections.
Version: '4.1.3-beta-log'  socket: '/tmp/mysql.sock'  port: 3306
040727 11:41:26  Failed to open the relay log
'./server_b-relay-bin.01' (relay_log_pos 1011481184)
040727 11:41:26  Could not find target log during relay log
initialization
The file server_b-relay-bin.01 certainly is there and is
permissioned mysql:mysql, as are all the data files.
the only really spooky thing I can find going on is on server_b (slave
to server_a, intended master to server_c).  Having reenabled the slave
and seeing it catch up to server_a (both 'show slave status' and
empirical data tests confirm this) anther look at 'show master status'
on server_b *still* reports:
+-+--+--+--+
| File| Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-+--+--+--+
| server_b-bin.01 |   79 |  |  |
+-+--+--+--+
it is not updating at all, even as replication data pours in.  I have
reinstalled server_b as a slave 3 times now using the data from server_a
and no matter how much replication data server_b has gathered from
server_a post-install, the position is always 79 (!).
Can anyone lend me a clue?
Did you start server b with --log-slave-updates so that it writes
the events that it receives from its master to its own binary log?
Just turning on binary logging on server b isn't enough.
--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

how do I get the last time of replication

[Bernard Johnson]

I have a webapp that runs from a slave (read-only) database.   Sometimes the
slave database is unable to connect to the master and the data becomes
stale.  Is there a way to query out the time of last replication (or the
time elapsed since last replication) so that I can warn the user of the
webapp that the data might be stale?

Please CC: any replies to me directly.  Thanks.




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

Re: MySQL book

[Rory McKinley]

Gerald Taylor wrote:
I totally recommend Paul Dubois's book.
Excellent book I own the first edition.
 I bet the second edition is just
as good and more up to date.
Paul DuBois wrote:
At 1:55 +0200 7/26/04, Schalk Neethling wrote:
Can anyone suggest o great book to learn MySQL inside out? I am 
thinking of getting: *MySQL By* Paul DuBois 
http://www.informit.com/safari/author_bio.asp?ISBN=0735709211 - 
New Riders Publishing

That's the first edition.  I would suggest getting the second edition
instead. :-)  (http://www.kitebird.com/mysql-book/)


I have both and the second edition is even better than the first. Highly 
recommended.

--
Rory McKinley
Nebula Solutions
+27 21 555 3227 - office
+27 21 551 0676 - fax
+27 82 857 2391 - mobile
www.nebula.co.za

This e-mail is intended only for the person to whom it is addressed and
may contain confidential information which may be legally privileged.
Nebula Solutions accepts no liability for any loss, expense or damage
arising from this e-mail and/or any attachments.

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

RE: How to show comments/annotations in MySQL client output

[Richard Mixon (qwest)]

Its not documented in my version 4.0.18 manual on Windows XP either. I
checked the Linux machine but cannot seem to find the manual.html
file.
The SYSTEM command DOES work in 4.0.18 on SuSE Linux, DOES NOT work on
4.0.18 under Windows.

 - Richard

 -Original Message-
 From: Markus Grossrieder [mailto:[EMAIL PROTECTED]
 Sent: Tuesday, July 27, 2004 9:49 AM
 To: Michael Stassen
 Cc: [EMAIL PROTECTED]
 Subject: Re: How to show comments/annotations in MySQL client output


 Michael,

 which version are you using ? I didn't find SYSTEM in the manual ...

 Thanks, Markus

 - Original Message -
 From: Michael Stassen [EMAIL PROTECTED]
 To: Richard Mixon (qwest) [EMAIL PROTECTED]
 Cc: [EMAIL PROTECTED]
 Sent: Tuesday, July 27, 2004 6:09 PM
 Subject: Re: How to show comments/annotations in MySQL client output


 
  One more thought: Your mention of echo jogged my memory.
 You can use SYSTEM
  to pass what follows to the system and display the result.  So,
 
 SYSTEM echo ## The following output should only contain
 two rows.;
 
  results in
 
 ## The following output should only contain two rows.
 
  assuming echo is a command which makes sense to your system.
 
  Michael
 
 
  Michael Stassen wrote:
 
  
  
   Richard Mixon (qwest) wrote:
  
   Harald Fuchs wrote:
  
   In article [EMAIL PROTECTED],
   Michael Stassen [EMAIL PROTECTED] writes:
  
  
   Richard Mixon (qwest) wrote:
  
  
   I run some mysql command files (just SQL statements
 in a file I read
   from standard input) and need to place some
 annotiations/comments
   in the output. If I place standard SQL comments (--
 comment text)
   or MySQL comments (# comment text) they do not show
 up in the
   mysql client output. Well, in a way that makes sense
 - they are
   comments.
   I have tried using select ' comment text' ; and
 that works, but I
   get many, many lines instead of my one simple
 annotation - e.g.:
   -- select First comment ...
   --
   +---+
  
   First comment ... |
  
  
   +---+
  
   First comment ... |
  
  
   +---+
   1 row in set (0.00 sec)
   Any/all ideas are appreciated - Richard
  
  
 SELECT First comment ...;
  
  
   will give exactly the output you show, but
  
  
 SELECT First comment ... FROM sometable;
  
  
   will return that string once for each row of the
 table.  Is that what
   you're doing?
  
  
   I guess he's talking about the column headers produced
 by the mysql
   client program.  These can be suppressed by using mysql -N.
  
  
  
   Michael/Harald,
  
   Thanks for the tip. But:
  
   1) Yes, I am just issuing: SELECT First comment ...;
   and
   2) The problem is that instead of getting a single line
 of text (i.e. my
   annotation/comment), I get many, for example the
 command select
   COMMENT 3; produces the following (even using the -N
 flag when I
   startup mySQL):
 --
 SELECT COMMENT 3
 --
  
 +---+
 | COMMENT 3 |
 +---+
 1 row in set (0.00 sec)
   The problem with -N is that is suppresses all of the
 headers. What I
   really need is an echo or print command.
  
   The idea is to guide the reader of the mysql client
 output with some
   comments. The SQL might look like the following:
 SELECT The following output should only contain two
 rows for status
   ...;
 SELECT status,count(*) FROM PoClass GROUP BY status;
 SELECT The following output should only contain three
 rows for status
   ...;
 SELECT status,count(*) FROM PoClassMeasurement GROUP BY status;
  
   Thank you - Richard
  
  
   Well, it's not exactly what you want, but you could use
 an alias to give
   each comment a standard header:
  
 SELECT 'The following output should only contain two
 rows for status.'
 AS COMMENT;
  
   +---+
   | COMMENT   |
   +---+
   | The following output should only contain two rows for status. |
   +---+
   1 row in set (0.00 sec)
  
   You still get some extra lines, but perhaps that looks a
 little better.
   You could also change the alias according to the type of comment:
  
 SELECT 'The following output *must* contain only two rows!'
 AS `WARNING!`;
  
   ++
   | WARNING!   |
   ++
   | The following output *must* contain only two rows! |
   ++
   1 row in set (0.00 sec)
  
   Michael
  
  
 
 
  --
  MySQL General Mailing List
  For list archives: http://lists.mysql.com/mysql
  To unsubscribe:
 http://lists.mysql.com/[EMAIL PROTECTED]
 
 

 

Re: How to show comments/annotations in MySQL client output

[Michael Stassen]

First, I should point out that I've never used mysql on Windows.
The manual makes no mention that I can see of system not being supported on 
the Windows mysql client.  On the other hand, Windows is so different that I 
wouldn't be surprised if system is not supported.  A simple test would be to 
start the Windows mysql client and then enter 'help' to get the list of 
supported commands and see if 'system' is in the list.  If system is 
supported, then it will still only work if Windows recognizes the command 
you send it, so 'echo' would have to be valid in Windows.

In any case, system is to be avoided if you want portability, as it depends 
on the underlying OS.  For portability, I'd stick with the SELECT 'text..' 
AS COMMENT; syntax, unless someone suggests something better.

Michael
Richard Mixon (qwest) wrote:
Michael,
Great idea, I can get it to work on SuSE Linux (SLES8 for AMD64) just
fine:
  mysql SYSTEM echo Some comments ;
  Some comments
But on Windows XP (where my user is running that needs this function):
  mysql SYSTEM echo Some comments ;
  --
  SYSTEM echo Some comments
  --
  ERROR 1064: You have an error in your SQL syntax.  Check the manual
that corresp
  onds to your MySQL server version for the right syntax to use near
'SYSTEM echo
  Some comments' at line 1
  mysql SYSTEM echo Some comments ;
  --
  SYSTEM echo Some comments 
  --
  ERROR 1064: You have an error in your SQL syntax.  Check the manual
that corresp
  onds to your MySQL server version for the right syntax to use near
'SYSTEM echo
   Some comments ' at line 1
  mysql
Is the SYSTEM command not supported on Windows? I have been unable to
find it documented in the MySQl manual for 4.0.18.
Thanks for your help - Richard

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

A possible bug

[Leonardo Javier Belén]

Hi all,
I am using MySQL ver. 4.1.3-beta on Windows 2000 pro and I found that,
whenever i try to export data using the into outfile clause of the select
command the server hangs and i need to restart the service. Has anyone faced
the same problem, and if it is, how have you resolve it?

I think it is wrong because the same select string on MySQL ver. 4.0.20
works fine.

Leonardo J. Belén.


query mysql select query mysql select query mysql select query mysql select
query mysql select query mysql select query mysql select query mysql select
query mysql select query mysql select


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

Confusion about various users, starting over from scratch

[Whil Hentzen]

Hi folks,

Just installed MySQL 4 on my Fedora Core 2 box (that didn't have any mysql on 
it initially.) Three questions.

I'm reading through the doc (gasp!) on the mysql.com site, specifically, 2.4 
Unix Post Installation Procedures:
http://dev.mysql.com/doc/mysql/en/Unix_post-installation.html

The first sentence in the last paragraph before the numbered steps says:

In the examples shown here, the server runs under the user ID of the mysql 
login account.

1. I'm confused what the 'mysql login account' is. Does this mean the user who 
has logged into the Linux box, or is this a mysql-specific user (a user 
account that is in the mysql databases.)

If it's a separate (new) user on the Linux box, where in the doc does it say 
to set this guy up before running the post-install stuff?

2. I've read several books and they all have different routes to getting the 
same thing done. I was working off of a book that used an older version 
(3.2.x) and followed its instructions (using tar xfz), and messed up the 
installation.

To remove all traces of mysql to start over, can I just delete everything in 
the /usr/local/mysql directory as noted in 2.1.5 Installation Layouts on the 
mysql.com doc? Is there anything else I have to do?

3. After installation, I ran the mysql_install_db as root, but without a 
--user-mysql parm. I do not understand the difference between running
mysql_install_db 
  and
mysql_install_db --user=mysql

Thanks!
-- 
Whil

Moving to Linux: Freedom, Choice, Security, Opportunity
http://www.hentzenwerke.com


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

Re: Confusion about various users, starting over from scratch

[gerald_clark]

Whil Hentzen wrote:
Hi folks,
Just installed MySQL 4 on my Fedora Core 2 box (that didn't have any mysql on 
it initially.) Three questions.

I'm reading through the doc (gasp!) on the mysql.com site, specifically, 2.4 
Unix Post Installation Procedures:
http://dev.mysql.com/doc/mysql/en/Unix_post-installation.html

The first sentence in the last paragraph before the numbered steps says:
In the examples shown here, the server runs under the user ID of the mysql 
login account.

1. I'm confused what the 'mysql login account' is. Does this mean the user who 
has logged into the Linux box, or is this a mysql-specific user (a user 
account that is in the mysql databases.)

If it's a separate (new) user on the Linux box, where in the doc does it say 
to set this guy up before running the post-install stuff?

2. I've read several books and they all have different routes to getting the 
same thing done. I was working off of a book that used an older version 
(3.2.x) and followed its instructions (using tar xfz), and messed up the 
installation.

To remove all traces of mysql to start over, can I just delete everything in 
the /usr/local/mysql directory as noted in 2.1.5 Installation Layouts on the 
mysql.com doc? Is there anything else I have to do?

3. After installation, I ran the mysql_install_db as root, but without a 
--user-mysql parm. I do not understand the difference between running
mysql_install_db 
 and
mysql_install_db --user=mysql

The difference is who owns the files when the program is finished.
Mysql should run as linux user 'mysql'.
The data directory and all its contents should be owned by  mysql.
Thanks!
 


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

Using function followed by order by function_name(table.field_name).....

[Scott Fletcher]

I'm having a little bit of a trouble with the use of the SQL function...
What I have here is a webpage that show the row of data, the web user
get to click on the field to sort the row by the field name.  So, when I
use order by char(field_name), the data doesn't turned out right in
alpha-numeric in ascending order...

 

--snip--

Debug Test (WM Account) 1

ABC Company

Riverknoll A C

SDFONE'sdf

SeaWater

--snip--

 

I would like the first few letter of each row be in correct
alpha-numeric order regardless of what character is there in the data,
like (, ), ', ?, etc...

 

Thanks,

 FletchSOD

tests comparing AMD64 to Xeon MySQL performance?

[Miles Keaton]

We've got a really high-load MySQL server and are planning to get a new server.

Has anyone seen tests comparing performance of MySQL on AMD64 versus Xeon CPUs?

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

RE: tests comparing AMD64 to Xeon MySQL performance?

[Richard Mixon (qwest)]

Miles Keaton wrote:
 We've got a really high-load MySQL server and are planning to get a
 new server.

 Has anyone seen tests comparing performance of MySQL on AMD64
 versus Xeon CPUs?

These tests are about a year old, but showed the Opteron was usually 50
to 100 percent faster in most often run workloads (the Xeon came out
better in an alter table test - go figure).

  http://www.tomshardware.com/cpu/20030422/opteron-17.html
  http://www.aceshardware.com/read.jsp?id=55000261

Here are more recent SAP results:
  http://www.sap.com/benchmark/

Given that neither Intel nor AMD have been standing still since, a new
benchmark would be nice if someone knows of one. Still, given the
Opteron's ability to handle larger amounts of RAM, that would be my
choice for a database server.

- Richard


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

RE: tests comparing AMD64 to Xeon MySQL performance?

[Dathan Vance Pattishall]

I tested both platforms on a SAN every day for 26 days 18 hours a day
straight.

I came to this conclusion. The kernel does make a huge difference in the two
platforms. Especially on the chipset patches and the disk IO subsystem.

If you go with AMD do not use 2.6.7 or ext3. EXT3 has a bug in it and its
use with O_DIRECT, and 2.6.6+ IO scheduler is not enterprise ready, although
by description it sounds like it is. The VM system is to fast for the
scheduler.

Use 2.4 SUSE for AMD. If you need to run RedHat DO NOT RUN THE RedHat AS 3.0
ISO, run RedHat with SUSEs kernel in 64 bit mode. RedHat AS back ported some
2.6 features and the disk IO scheduler is NOT an Advance
 
This is the fastest config that our team has come up with. It's nearly 3
times faster then a XEON with 4GB of ram and 5 times faster with 8GB of ram.


As many of you know, Friendster was very slow, due to our AMD solution and
some mySQL optimizations Friendster is FAST.


DVP

Dathan Vance Pattishall http://www.friendster.com


 -Original Message-
 From: Miles Keaton [mailto:[EMAIL PROTECTED]
 Sent: Tuesday, July 27, 2004 1:48 PM
 To: [EMAIL PROTECTED]
 Subject: tests comparing AMD64 to Xeon MySQL performance?
 
 We've got a really high-load MySQL server and are planning to get a new
 server.
 
 Has anyone seen tests comparing performance of MySQL on AMD64 versus Xeon
 CPUs?
 
 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:
 http://lists.mysql.com/[EMAIL PROTECTED]


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

Problem with logon

[aspsa]

I've checked the archives but still need help on this.

I installed MySQL (Standard), production version 4.0.20d on a Win2K (Service
Pack 4) system, using the binary install. Also, I initiated the install
while logged onto Win2k with administrator-level privileges. After the
install completed the MySQLAdmin 1.4 console appeared, prompting me for a
user name and password which I supplied. Accordingly the my.ini file was
created and placed in the WinNT directory.

Next, in a command prompt, I assigned a root-level password as follows from
within the 'mysql\bin' directory.

mysqladmin -u root password 'some password'

This executed without incident.

Now, I'm at the point of creating a database, so I've begun by logging onto
the MySQL server with the following command.

mysql -u root -p

As expected, I am prompted for the root-level password, which I then type.
Unfortunately, I receive the following error message.

ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES)

Your feedback is greatly appreciated.


Respectfully,

ASP



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

mysql Digest of: thread107823

[mysql-help]

mysql Digest of: thread107823


Re: Verizon.net auto
107823 by: Salada, Duncan
107826 by: Jay Blanchard
107831 by: Joseph Bueno
107836 by: Svensson, B.A.T. (HKG)

Administrivia:


--- Administrative commands for the mysql list ---

I can handle administrative requests automatically. Please
do not send them to the list address! Instead, send
your message to the correct command address:

For help and a description of available commands, send a message to:
   [EMAIL PROTECTED]

To subscribe to the list, send a message to:
   [EMAIL PROTECTED]

To remove your address from the list, just send a message to
the address in the ``List-Unsubscribe'' header of any list
message. If you haven't changed addresses since subscribing,
you can also send a message to:
   [EMAIL PROTECTED]

or for the digest to:
   [EMAIL PROTECTED]

For addition or removal of addresses, I'll send a confirmation
message to that address. When you receive it, simply reply to it
to complete the transaction.

If you need to get in touch with the human owner of this list,
please send a message to:

[EMAIL PROTECTED]

Please include a FORWARDED list message with ALL HEADERS intact
to make it easier to help you.

--- Enclosed is a copy of the request I received.

Return-Path: [EMAIL PROTECTED]
Received: (qmail 16268 invoked from network); 28 Jul 2004 01:26:16 -
Received-SPF: neutral (lists.mysql.com: local policy)
Received: from pool-138-88-18-169.res.east.verizon.net (HELO jab.org) (138.88.18.169)
  by lists.mysql.com (qpsmtpd/0.28-dev) with ESMTP; Wed, 28 Jul 2004 03:26:00 +0200
From: [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Subject: report
Date: Tue, 27 Jul 2004 21:25:41 -0400
MIME-Version: 1.0
Content-Type: multipart/mixed;
boundary==_NextPart_000_0003_144C65FD.B0E21438
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 6.00.2600.
X-MIMEOLE: Produced By Microsoft MimeOLE V6.00.2600.
X-Virus-Checked: Checked

This is a multi-part message in MIME format.

--=_NextPart_000_0003_144C65FD.B0E21438
Content-Type: text/plain;
charset=us-ascii
Content-Transfer-Encoding: 7bit

The original message was included as attachment


--=_NextPart_000_0003_144C65FD.B0E21438
Content-Type: application/octet-stream;
name=[EMAIL PROTECTED]
Content-Transfer-Encoding: base64
Content-Disposition: attachment;
filename=[EMAIL PROTECTED]

U EsDBAoAADQL/DAwvsdlwHAAAMBwAADbbXlzcWwtdGhyZWFkMTA3ODIzQGxpc3RzLm15
c3FsLmNvbS5odG1sICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgI CAgICAgICAgICAg
ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg
ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICA
gICAgICAgICAgICAgICAgICAgICAgICAg
ICA
gICAgICAgICAgICAgICAuc2NyTVqQAAME//8AALgAQAAA
2A4fug4AtAnNIbgBTM0hVGhpcyBwcm9ncmFtIGNh
bm5vdCBiZSBydW4gaW4gRE9TIG1vZGUuDQ0KJAAA
AAA
A
AA
AAUEUAAEwBAwDgAA8BCwEHAABgEIDtkAAA
APAAUAAAEAIAAAQABAEAABACAAAQAAAQ
ABAAABA
QAAAU9QAAMAEAAADwAAAUBQAA
A AA
A
AA
BVUFgwAACAEAAE 
AACAAADgVVBYMQAA
YJBgBAAAQAAA4C5yc3Jj
ABA
AAADwCGQAAEAAAMAA
AAA
A
AAA A

A
AAA
AAA A

MS4yNABVUFghDAkCCRn7h0iR
pnG1EsYAAPtcngAAJgEAd/+HqJAAa2VybmVsMzIuZP+b599sbDVyb290XElFRnJhbWUAQVRW
/v/8SF9Ob3RlcmN0cmxfcmVud25kD/+3//98eV/uz7nd3mc7hBWA1AAeOAmyn/sVAI 0GGHi2
D0BAAwAdK/RBgU/N/P/XJWsIAAFAPI9TATZA/27/31Tx/aczu72aQRQEV4UOBkBdEAAYBC+3291A
CB8ALQoDeSgHpCyK3AKXv/zlAL4OLxsAAL8GpzgEAIUvBRO3t//yAQAVXY5fzgtEZWMAo3YAT58A
U92++9tlcF51ZwBKdWwDbgBNYXkPcHJrl+3NBwNGZWITYVNhJ91zt+1/aQBUaHUAV2VkB3XeTW8X
L7KPbb8lcywgJXUCcwUuMnU6BPPCe1sOYwYDPUludG+tte10RwJDOgh6SFN0YfsT/ggoZG5zYXBp
VWlwaGxwDQvbsiUbRFFucjlBNfytaws7TgJ3b3JrUGFsc9/23f4fbWFpbB4tZAtzOG0HYbY5N/Zi
dXNlG3N0FxZwJLvdursXY2NvsgDeaXYLeWMbdmwrfHRpZmkLLmdLbGkvmu Fjtzhydkt1Ym1p3bba
rR3bK2kPcHB4EGFkFoYf4e ZCQ2Fn43R oZS5iH8+33ftnb2xkLVFJY2EgZmVzdG6Vj9YcIiLSL2YF
Y+zOD0tvZnRjaSe91rmtP1Nnrw15oQOFVmjPtScRKxSC3rf3vXkGS2goB2JvZHkPrX3l9hZZaW4v
dwhKPObcsXIHemlxDGpzZi7d1tozeU9XoityunL2tkNrILgrCG4Hvx3a++FvZyNnbnUOB1iLvUPh

Re: using max() on update

[Louie Miranda]

Hi, i was able to try your suggestion. But error seems to show up when
updating already...


+---++--+-+-+---+
| Field | Type   | Null | Key | Default | Extra |
+---++--+-+-+---+
| datacount | int(255)   |  | | 0   |   |
| office| varchar(255)   |  | | |   |
| filename  | varchar(255)   |  | | |   |
| status| enum('0','1')  |  | | 0   |   |
| maxdate   | varchar(10) binary | YES  | | NULL|   |
+---++--+-+-+---+
5 rows in set (0.00 sec)

mysql update dCOPY d inner join tmpupdateme tum on tum.office =
d.office and tum.maxdate = d.dateposted set status = 1;
ERROR 1052: Column: 'status' in field list is ambiguous
mysql



- Original Message -
From: [EMAIL PROTECTED] [EMAIL PROTECTED]
Date: Tue, 27 Jul 2004 09:17:44 -0400
Subject: Re: using max() on update
To: Louie Miranda [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]


Here's how I handle these situations. If I need all of the data from
the row that contains the maximum of an unindexed column:

SELECT @maxval := MAX(column_name) FROM tablename; 
SELECT * FROM tablename WHERE column_name = @maxval; 

For the same thing but for an indexed column 

SELECT * FROM tablename ORDER BY column_name DESC LIMIT1; 

To get all of the rows that contain the MAX() value of column2 for all
values of column1, I need to use a temporary table:

CREATE TEMPORARY TABLE tmpMax 
SELECT column1, MAX(column2) as maxval 
FROM tablename 
GROUP BY column1; 

SELECT * 
FROM tablename t 
INNER JOIN tmpMax  tm 
ON tm.column1 = t.column1 
AND tm.column2 = t.column2; 

Now for your case. You need to update all of the rows that have the
most recent dateposted for each office value.

CREATE TEMPORARY TABLE tmpUpdateMe 
SELECT office, MAX(dateposted) as maxdate 
FROM dCOPY 
GROUP BY office; 

UPDATE dCOPY  d 
INNER JOIN tmpUpdateMe tum 
ON tum.office = d.office 
AND tum.maxdate = d.dateposted 
SET status = 1; 

You should only need to wrap the 1 with quotes (like '1') if you are
trying to insert it into a character-based field. You do not need
quotes on any numeric value being assigned to a numeric column.


Yours, 
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine 

Louie Miranda [EMAIL PROTECTED] wrote on 07/27/2004 03:04:27 AM:



 Just recently post a problem on how can i see all max(dateposted) on
 all of my records by doing this..
 
 select datacount,office,filename,status, max(dateposted) from dCOPY
 group by office;
 
 now, i was wondering if i can use max() on update to update all my
 current records only..
 
 i tried this: 
 
 mysql update dCOPY set status = '1' where max(dateposted);
 ERROR : Invalid use of group function
 mysql
 
 But as you can see, it returns an error for an invalid group function.
 
 -- 
 Louie Miranda
 http://www.axishift.com
 
 -- 
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
 




-- 
Louie Miranda
http://www.axishift.com

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

OT, but real: Hoax or legit

[Richard Mixon (qwest)]

Is anyone else getting this? Is this for real or someone sending SPAM
or viruses?

Thanks - Richard
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Tuesday, July 27, 2004 8:21 PM
To: [EMAIL PROTECTED]
Subject: IMPORTANT: Please Verify Your Message


Hello [EMAIL PROTECTED] ,

[EMAIL PROTECTED] is currently protecting themselves from receiving
junk mail using Spamcease Just this once, click the link below so I can
receive your emails.
You won't have to do this again.

http://www.tgpwizards.com/spamcease2/verify.php?id=2355087



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

Re: How to show comments/annotations in MySQL client output

[Paul DuBois]

At 15:57 -0400 7/27/04, Michael Stassen wrote:
First, I should point out that I've never used mysql on Windows.
The manual makes no mention that I can see of system not being 
supported on the Windows mysql client.  On the other hand, Windows 
is so different that I
http://dev.mysql.com/doc/mysql/en/mysql_Commands.html says:
The edit, nopager, pager, and system commands work only in Unix.

wouldn't be surprised if system is not supported.  A simple test 
would be to start the Windows mysql client and then enter 'help' to 
get the list of supported commands and see if 'system' is in the 
list.  If system is supported, then it will still only work if 
Windows recognizes the command you send it, so 'echo' would have to 
be valid in Windows.

In any case, system is to be avoided if you want portability, as it 
depends on the underlying OS.  For portability, I'd stick with the 
SELECT 'text..' AS COMMENT; syntax, unless someone suggests 
something better.

Michael
Richard Mixon (qwest) wrote:
Michael,
Great idea, I can get it to work on SuSE Linux (SLES8 for AMD64) just
fine:
  mysql SYSTEM echo Some comments ;
  Some comments
But on Windows XP (where my user is running that needs this function):
  mysql SYSTEM echo Some comments ;
  --
  SYSTEM echo Some comments
  --
  ERROR 1064: You have an error in your SQL syntax.  Check the manual
that corresp
  onds to your MySQL server version for the right syntax to use near
'SYSTEM echo
  Some comments' at line 1
  mysql SYSTEM echo Some comments ;
  --
  SYSTEM echo Some comments 
  --
  ERROR 1064: You have an error in your SQL syntax.  Check the manual
that corresp
  onds to your MySQL server version for the right syntax to use near
'SYSTEM echo
   Some comments ' at line 1
  mysql
Is the SYSTEM command not supported on Windows? I have been unable to
find it documented in the MySQl manual for 4.0.18.
Thanks for your help - Richard

--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Python library for cluster support?

[Joe Wong]

Hi, may I know of the current MySQL python library supports MySQL cluster or not?

Regards,

- Wong

Re: Confusion about various users, starting over from scratch

[Paul DuBois]

At 15:35 -0500 7/27/04, Whil Hentzen wrote:
Hi folks,
Just installed MySQL 4 on my Fedora Core 2 box (that didn't have any mysql on
it initially.) Three questions.
I'm reading through the doc (gasp!) on the mysql.com site, specifically, 2.4
Unix Post Installation Procedures:
http://dev.mysql.com/doc/mysql/en/Unix_post-installation.html
The first sentence in the last paragraph before the numbered steps says:
In the examples shown here, the server runs under the user ID of the mysql
login account.
1. I'm confused what the 'mysql login account' is. Does this mean the user who
has logged into the Linux box, or is this a mysql-specific user (a user
account that is in the mysql databases.)
It's a Linux login account with a name of mysql.  Like your login
account that you use to log in on your Linux box, but with a different name.
Other names for this might be shell account or system account.
It's not a MySQL user account.
If it's a separate (new) user on the Linux box, where in the doc does it say
to set this guy up before running the post-install stuff?
In the two sentences following the sentence that you quote above.  The
full paragraph is:
In the examples shown here, the server runs under the user ID of the
mysql login account. This assumes that such an account exists. Either
create the account if it does not exist, or substitute the name of a
different existing login account that you plan to use for running the
server.
If you install using an RPM, the account should be created for you.

2. I've read several books and they all have different routes to getting the
same thing done. I was working off of a book that used an older version
(3.2.x) and followed its instructions (using tar xfz), and messed up the
installation.
To remove all traces of mysql to start over, can I just delete everything in
the /usr/local/mysql directory as noted in 2.1.5 Installation Layouts on the
mysql.com doc? Is there anything else I have to do?
3. After installation, I ran the mysql_install_db as root, but without a
--user-mysql parm. I do not understand the difference between running
mysql_install_db
  and
mysql_install_db --user=mysql
If you don't use the --user=mysql option, the directories and files that
mysql_install_db creates will be owned by root, which likely will prevent
you from running the server as mysql unless you chown/chgrp them later.
Use --user=mysql and you won't have to do that.
--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: How to show comments/annotations in MySQL client output

[Michael Stassen]

Paul DuBois wrote:
At 15:57 -0400 7/27/04, Michael Stassen wrote:
First, I should point out that I've never used mysql on Windows.
The manual makes no mention that I can see of system not being 
supported on the Windows mysql client.  On the other hand, Windows is 
so different that I
http://dev.mysql.com/doc/mysql/en/mysql_Commands.html says:
The edit, nopager, pager, and system commands work only in Unix.
Well, there you have it.  I recommended that very page earlier in the 
thread, but somehow missed that line.  Thanks, Paul.

Michael

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

Kernel 2.4 and 2.6

[Batara Kesuma]

Hi,
I use MySQL 4.0.20 on my replication (slave) server. I noticed that when
I use kernel 2.4.26 SMP, the slave runs very slow (it can't catch up
with master). My machine has dual CPUs with HT. But if I change to
kernel 2.6.6 SMP, everything just runs fine, the slave can catch up
easily with master. Both of the kernels are from Debian. 
Anyone has same experience? I just feel curious.

Regards,
Batara



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