version
I have an install script that does some stuff with mysql (i.e. install, start, etc). It installs mysql Ver 14.12 Distrib 5.0.19, for pc-linux-gnu (i686) using readline 5.0 This was good when we just used CentOS 4.5. Now we are doing some later CentOS versions and the mysql version may be higher. I want to do something like mysql --version and process the result and if the version is = 5.0.19 skip the mysql installation and just do the other stuff. I can't compare as it is right now because the . and stuff may screw up the comparison (e.g. ver 5.2 will show as greater than 5.19 eg). I want to know, if I break the individual pieces like 14 12 5 0 19 I can do some sort of calculation to determine a number that I can actually compare. Or can I just remove all the decimal points, like 14.12.5.0.19 becomes 14125019? I might have to make it like 14120050019 or something. What is an algorithm I can use? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: version
I am using RPM. And for Centos 5 it was installing a lower version of mysql than what was installed with the system. That is why I want to check before doing the RPM. I guess the alternative is to use the latest version all the time, but not sure whether that will work on the 4.5 version. The other thing is, mysql appears to keep changing between /etc/init.d/mysqld and /etc/init/mysql so I also have to do an ls /etc/init.s/mysql* to figure out what to use to start which is also kind of a pain. -Original Message- From: Johan De Meersman [mailto:vegiv...@tuxera.be] Sent: Thursday, January 14, 2010 09:44 AM To: tony.chamberl...@lemko.com Cc: mysql@lists.mysql.com Subject: Re: version You *should* be using a package manager (perfectly fine RPMs available for all your needs), but if you must do this, it's a reasonably safe bet to right-align and zero-pad all your number to 4 digits, at which point you're free to concatenate them and treat them as a single number. 14.12.5.0.19 then becomes 0014 0012 0005 0019 which becomes 14001200050019. You'd probably be safe with 3 or maaaybe even two positions, depending on how many releases get done :-) The better-but-more-work way is to compare every number separately, starting with the major release. On Thu, Jan 14, 2010 at 4:21 PM, tony.chamberl...@lemko.com wrote: I have an install script that does some stuff with mysql (i.e. install, start, etc). It installs mysql Ver 14.12 Distrib 5.0.19, for pc-linux-gnu (i686) using readline 5.0 This was good when we just used CentOS 4.5. Now we are doing some later CentOS versions and the mysql version may be higher. I want to do something like mysql --version and process the result and if the version is = 5.0.19 skip the mysql installation and just do the other stuff. I can't compare as it is right now because the . and stuff may screw up the comparison (e.g. ver 5.2 will show as greater than 5.19 eg). I want to know, if I break the individual pieces like 14 12 5 0 19 I can do some sort of calculation to determine a number that I can actually compare. Or can I just remove all the decimal points, like 14.12.5.0.19 becomes 14125019? I might have to make it like 14120050019 or something. What is an algorithm I can use? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=vegiv...@tuxera.be -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: novice on SQL
Hi John, right the problem boils down to this: sitetable tasktable ID taskidtaskid Changes ---- 11010100 21110120 SELECT sitetable.ID, tasktable.Changes FROM sitetable,tasktable WHERE sitetable.taskid = tasktable.taskid; and get the following: IDChanges 1 100 1 120 but what I need is the following format ID Changes1 Changes2 (limits of 5) --- 1 100120etc ps: a collegue said to me that DBs are not design to do what I wanted to do (in 1 sql query anyway). I'm beginning to accept that comment :( Thanks John Tony John Hicks [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] tony yau wrote: Hi John, tried your suggestion but I can't get it to work. This is because I don't know how to set conditions in the following clauses (because there isn't any) and Table1.[condition for Changes1] and Table2.[condition for Changes2] and Table3.[condition for Changes3] What values do you want for Changes1, Changes2, etc.? (How are you selecting for them.) Post your SQL here if you need further help. --J the result I've got was similar to the following (note the ID is pkey of another table) IDChanges1Changes2Changes3 - 1 10.010.0same as 1 10.310.3 1 12.212.2 2 31.031.0 3 1.021.02 3 4.94.9 thanks for your help anyway Tony tony yau [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Hi John, I didn't know you can do that! (such a novice indeed!) Thank you for your reply, I will put it to the test first thing when i get back to the office tomo. Cheers John Hicks [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] tony yau wrote: Hello, I can get a select result like the following: (SELECT ID,Changes FROM mytable WHERE somecondition;) IDChanges - 1 10.0 1 10.3 1 12.2 2 31.0 3 1.02 3 4.9 how can I get the above result sets into the following format (columns 'Changes1','Changes2',... are all from 'Changes') IDChanges1Changes2Changes3 (limits of 5) 1 10.010.312.2 2 31.0 3 1.024.9 I have got a method that works (I think) by first do a SELECT getting DISTINCT id values and then foreach of these ID I do another SELECT to get the Changes values and then just massage the display. Is there another way of doing this by using a single SQL query? There may be a simpler way, but this should work: select Table.ID, Table1.Changes as Changes1, Table2.Changes as Changes2, Table3.Changes as Changes3 from Table, Table as Table1, Table as Table2, Table as Table3 where Table.ID = Table1.ID and Table.ID = Table2.ID and Table.ID = Table3.ID and Table1.[condition for Changes1] and Table2.[condition for Changes2] and Table3.[condition for Changes3] order by table.ID --J -- 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: novice on SQL
Hi John, right the problem boils down to this: sitetable tasktable ID taskidtaskid Changes ---- 11010100 21110120 SELECT sitetable.siteid, tasktable.prices FROM sitetable,tasktable WHERE sitetable.taskid = tasktable.taskid; and get the following: IDChanges 1 100 1 120 but what I need is the following format siteidprices1 prices2 (limits of 5) --- 1 100120etc ps: a collegue said to me that DBs are not design to do what I wanted to do (in 1 sql query anyway). I'm beginning to accept that comment :( Thanks John Tony John Hicks [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] tony yau wrote: Hi John, tried your suggestion but I can't get it to work. This is because I don't know how to set conditions in the following clauses (because there isn't any) and Table1.[condition for Changes1] and Table2.[condition for Changes2] and Table3.[condition for Changes3] What values do you want for Changes1, Changes2, etc.? (How are you selecting for them.) Post your SQL here if you need further help. --J the result I've got was similar to the following (note the ID is pkey of another table) IDChanges1Changes2Changes3 - 1 10.010.0same as 1 10.310.3 1 12.212.2 2 31.031.0 3 1.021.02 3 4.94.9 thanks for your help anyway Tony tony yau [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Hi John, I didn't know you can do that! (such a novice indeed!) Thank you for your reply, I will put it to the test first thing when i get back to the office tomo. Cheers John Hicks [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] tony yau wrote: Hello, I can get a select result like the following: (SELECT ID,Changes FROM mytable WHERE somecondition;) IDChanges - 1 10.0 1 10.3 1 12.2 2 31.0 3 1.02 3 4.9 how can I get the above result sets into the following format (columns 'Changes1','Changes2',... are all from 'Changes') IDChanges1Changes2Changes3 (limits of 5) 1 10.010.312.2 2 31.0 3 1.024.9 I have got a method that works (I think) by first do a SELECT getting DISTINCT id values and then foreach of these ID I do another SELECT to get the Changes values and then just massage the display. Is there another way of doing this by using a single SQL query? There may be a simpler way, but this should work: select Table.ID, Table1.Changes as Changes1, Table2.Changes as Changes2, Table3.Changes as Changes3 from Table, Table as Table1, Table as Table2, Table as Table3 where Table.ID = Table1.ID and Table.ID = Table2.ID and Table.ID = Table3.ID and Table1.[condition for Changes1] and Table2.[condition for Changes2] and Table3.[condition for Changes3] order by table.ID --J -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: novice on SQL
Hi John, tried your suggestion but I can't get it to work. This is because I don't know how to set conditions in the following clauses (because there isn't any) and Table1.[condition for Changes1] and Table2.[condition for Changes2] and Table3.[condition for Changes3] the result I've got was similar to the following (note the ID is pkey of another table) IDChanges1Changes2Changes3 - 1 10.010.0same as 1 10.310.3 1 12.212.2 2 31.031.0 3 1.021.02 3 4.94.9 thanks for your help anyway Tony tony yau [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Hi John, I didn't know you can do that! (such a novice indeed!) Thank you for your reply, I will put it to the test first thing when i get back to the office tomo. Cheers John Hicks [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] tony yau wrote: Hello, I can get a select result like the following: (SELECT ID,Changes FROM mytable WHERE somecondition;) IDChanges - 1 10.0 1 10.3 1 12.2 2 31.0 3 1.02 3 4.9 how can I get the above result sets into the following format (columns 'Changes1','Changes2',... are all from 'Changes') IDChanges1Changes2Changes3 (limits of 5) 1 10.010.312.2 2 31.0 3 1.024.9 I have got a method that works (I think) by first do a SELECT getting DISTINCT id values and then foreach of these ID I do another SELECT to get the Changes values and then just massage the display. Is there another way of doing this by using a single SQL query? There may be a simpler way, but this should work: select Table.ID, Table1.Changes as Changes1, Table2.Changes as Changes2, Table3.Changes as Changes3 from Table, Table as Table1, Table as Table2, Table as Table3 where Table.ID = Table1.ID and Table.ID = Table2.ID and Table.ID = Table3.ID and Table1.[condition for Changes1] and Table2.[condition for Changes2] and Table3.[condition for Changes3] order by table.ID --J -- 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]
novice on SQL
Hello, I can get a select result like the following: (SELECT ID,Changes FROM mytable WHERE somecondition;) IDChanges - 1 10.0 1 10.3 1 12.2 2 31.0 3 1.02 3 4.9 how can I get the above result sets into the following format (columns 'Changes1','Changes2',... are all from 'Changes') IDChanges1Changes2Changes3 (limits of 5) 1 10.010.312.2 2 31.0 3 1.024.9 I have got a method that works (I think) by first do a SELECT getting DISTINCT id values and then foreach of these ID I do another SELECT to get the Changes values and then just massage the display. Is there another way of doing this by using a single SQL query? any help or hints will be very much appreciated. Thanks -- Tony -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: novice on SQL
Hi John, I didn't know you can do that! (such a novice indeed!) Thank you for your reply, I will put it to the test first thing when i get back to the office tomo. Cheers John Hicks [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] tony yau wrote: Hello, I can get a select result like the following: (SELECT ID,Changes FROM mytable WHERE somecondition;) IDChanges - 1 10.0 1 10.3 1 12.2 2 31.0 3 1.02 3 4.9 how can I get the above result sets into the following format (columns 'Changes1','Changes2',... are all from 'Changes') IDChanges1Changes2Changes3 (limits of 5) 1 10.010.312.2 2 31.0 3 1.024.9 I have got a method that works (I think) by first do a SELECT getting DISTINCT id values and then foreach of these ID I do another SELECT to get the Changes values and then just massage the display. Is there another way of doing this by using a single SQL query? There may be a simpler way, but this should work: select Table.ID, Table1.Changes as Changes1, Table2.Changes as Changes2, Table3.Changes as Changes3 from Table, Table as Table1, Table as Table2, Table as Table3 where Table.ID = Table1.ID and Table.ID = Table2.ID and Table.ID = Table3.ID and Table1.[condition for Changes1] and Table2.[condition for Changes2] and Table3.[condition for Changes3] order by table.ID --J -- 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]
what is the sql command to export the whole database ?
What is the sql command to export the whole database ? __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
sql command for exporting database
Hi Is there any sql commands for dumping/exporing the content of a database to a file ?? Thanks. Tony. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
innodb locking
Hi, I have a query: UPDATE dbseXyzOrders.tblOrder SET intPoUid = 98 WHERE intOrderUid = 10798 intOrderUid is the primary key There are 25 columns in the table and a further 8 of these have indexes on them. The table is innodb I have just tried to run the above query 3 times and i got the follwing error Invalid Query Lock wait timeout exceeded; try restarting transaction AFAIK innodb locks on row level, so does that mean that something else is locking that row and won't let me update. If so how can i find out what? Here is a copy of what mytop says at the time the query is being run Thanks for any help MySQL on localhost (4.1.8a-Debian_1-log) up 4+08:28:06 [15:07:19] Queries: 6.0M qps: 17 Slow: 120.0 Se/In/Up/De(%): 68/19/01/00 qps now:8 Slow qps: 0.0 Threads:7 ( 3/ 0) 77/00/00/00 Cache Hits: 3.0M Hits/s: 8.5 Hits now: 4.2 Ratio: 74.2% Ratio now: 70.0% Key Efficiency: 97.7% Bps in/out: 6.8k/ 9.8k Now in/out: 3.5k/ 5.0k Id User Host/IP DB TimeCmd Query or State -- --- -- --- -- 554082 root localhost test 0 Query show full processlist 603034 root localhost0 Sleep 603086 root localhost0 Sleep 602989 root localhost1 Sleep 603301 root localhost1 Sleep 604008 root localhost 11 Query UPDATE dbseXyzOrders.tblOrder SET intPoUid = 97 WHERE intOrde 389249 repl clank 133504 Binlog Has sent all binlog to slave; waiting for binlog to be update -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: innodb locking
On Wed, 2005-10-05 at 07:12 -0700, Sujay Koduri wrote: Is this happening every time you try this, or it happened first time? It's not every time but this is not the first. Mostly the query is ok, but I would like to find out why it's happening. Also can you please tell what isolation level are you using now. I am using the default which i beleive is REPEATABLE READ. The particular query that is failing is done using autocommit as it a standalone query. Thanks tony sujay -Original Message- From: Tony Leake [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 05, 2005 7:27 PM To: mysql@lists.mysql.com Subject: innodb locking Hi, I have a query: UPDATE dbseXyzOrders.tblOrder SET intPoUid = 98 WHERE intOrderUid = 10798 intOrderUid is the primary key There are 25 columns in the table and a further 8 of these have indexes on them. The table is innodb I have just tried to run the above query 3 times and i got the follwing error Invalid Query Lock wait timeout exceeded; try restarting transaction AFAIK innodb locks on row level, so does that mean that something else is locking that row and won't let me update. If so how can i find out what? Here is a copy of what mytop says at the time the query is being run Thanks for any help MySQL on localhost (4.1.8a-Debian_1-log) up 4+08:28:06 [15:07:19] Queries: 6.0M qps: 17 Slow: 120.0 Se/In/Up/De(%): 68/19/01/00 qps now:8 Slow qps: 0.0 Threads:7 ( 3/ 0) 77/00/00/00 Cache Hits: 3.0M Hits/s: 8.5 Hits now: 4.2 Ratio: 74.2% Ratio now: 70.0% Key Efficiency: 97.7% Bps in/out: 6.8k/ 9.8k Now in/out: 3.5k/ 5.0k Id User Host/IP DB TimeCmd Query or State -- --- -- --- -- 554082 root localhost test 0 Query show full processlist 603034 root localhost0 Sleep 603086 root localhost0 Sleep 602989 root localhost1 Sleep 603301 root localhost1 Sleep 604008 root localhost 11 Query UPDATE dbseXyzOrders.tblOrder SET intPoUid = 97 WHERE intOrde 389249 repl clank 133504 Binlog Has sent all binlog to slave; waiting for binlog to be update -- 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]
timing problem
Hi I have 2 applications communicating via a mysql database the db is 4.1.8 running on a debian linux system. All tables are innodb app 1 1 runs on a windows machine, is written in c# and talks to the db with odbc app 2 runs on the same machine as the db and is writtin in php. Here's the problem app 1 writes sales data to a table, when it is finished it unsets a flag in another table to indicate that it is finished app 2 polls for the flag to be unset, then reads the sales data. Sometimes whem app 2 reads the data there is nothing to be read, i am logging the queries that app 2 is using to do the inserts, then by the time i can open up a terminal and query the table manually the data is there. The only thing I can assume it that there is some kind of timing issue and the data is not fully written when I try to read it, this doesn't happen every time and may only be when the server is loaded. Does this happen? If so what can I do about it, would putting the inserts into one big transaction help? At the moment All of the inserts are done by implicit commits. Sorry for the essay, i an just trying to fully document what I know. Thanks tony -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: timing problem
On Thu, 2005-09-29 at 09:30 -0400, [EMAIL PROTECTED] wrote: The key here is that you are making multiple changes from app1 that really should be within a transaction. The entire process of writing sales data and unsetting a flag from app1 needs to be transacted. That way the other server (app2) will either have consistent data or will never find out that anything was going on in the first place. Many thanks Shawn, I few changes needed to the app but i'll give it a go. Regards tony -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: timing problem
On Thu, 2005-09-29 at 16:15 +0100, Nuno Pereira wrote:
[EMAIL PROTECTED] wrote:
This one is interesting in terms of concurrency...
Is the app2 the one responsable for setting the flag? I supose that it is.
If that happens it's important that app2 doesn't mess with the flag, i
mean, it may lead to problems if app2 sees that app1 writes the sales
data, app2 sees it, starts reading it, app1 writes more data and app2
sets the flag without seeing that app1 have written more data, and sets
the flag without reading the new one.
Make this work without problems can be tricky, and I don't see a good
solution to this in five minutes. When app2 reads data what app does
with it? How app2 sees what was the last data she read?
--
Nuno Pereira
I don't think there is any concurency problem:
App 2 sets the flag and then polls for it to be unset, it is unset by
app 1 when it has written all of the sales data. meanwhile app 2 is
polling for the flag to be unset again and does nothing until it is, if
app 1 dies before unsetting the flag app then app 2 will sit in a loop
forever.
when the flag is unset, app 2 reads it, process it and posts it to a
website via xmlrpc
app 2 site in a shell script while loop: (pseudocide)
while (1){
start app 2
sleep (60)
}
so there can never be more than one instance of app 2 running.
tony
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
colum totals
Hi, I'm sure I read somewhere about a new function that would give the totals of all columns, ie if i have 2 cols, numberOfOrders valueOfOrders for a range of dates i can get something like the following Date numberOfOrdersvalueOfOrders 01-09 4 20.00 02-09 6 100.00 total 10120.00 could someone point me to this in the manual please as I can't seem to find it. Or did i just imagine it anyway? thanks tony -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: colum totals
On Fri, 2005-09-16 at 14:02 +0100, Mark Leith wrote: http://dev.mysql.com/doc/mysql/en/group-by-modifiers.html perfect, thank you. tony -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: setting expiry date
not sure i completely understand your question but i think the user
enters a date, and you want to delete all records that match that date?
also i'm assuming that the entered date hits your script as a post
variable.
if so, you need to convert the posted date into the same format as the
records in the database and delete all matching records.
//convert the date.
$mysqlDate = preg_replace(|(\d{2})/(\d{2})/(\d{4)|, \\3-\\2-\\1,
$_POST['strUserDate']);
//format the sql query
$strQuery = sprintf(delete from news where date = '%s',$mysqlDate);
then run the query.
tony
On Mon, 2005-09-12 at 12:56 +0100, [EMAIL PROTECTED] wrote:
I have a php/mysql database with articles. What I need is a php sctipt
or some mysql that will compare the current day with the 'expiry
date' entered by the user and if the there is a difference then drop
it from the database..
I am fairly new to mysql so do not know if there is a way to
automatically drop records when they expire.
I retrieve the date (called time) and format it like this...
$query= SELECT DATE_FORMAT(time, '%d.%m.%Y') AS time, article, id
FROM news ORDER BY id DESC;
The expiry date is entered through a javascript widget and is in the
format dd/mm/.
thanks,
Ross
www.blue-fly.co.uk | [EMAIL PROTECTED] | 07816 996 930 | 0131 553
3935
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
replicartion error
Hi I set up replication for the first time last week. 1 master 1 slave. Everything worked fine for 1 week, i checked out a couple of records this morning to check everything was still working and the slave had stopped. show slave status showed that there had been a duplicate key error, the record that was the duplicate did indeed exist and so did many after it. I did slave stop and slave start, then show slave status now indicateds that it is moving further through the master log but records i update are not getting into the slave, and it is now about a full day behind. so I have 3 questions how did this duplicate key happen, there was no error in my application logs on the master as I would expect? did i do wrong by restarting the slave? is is possible to remidy the situation without re-coyping the data files? thanks in advance, current status below (the slave is incrementing but always a little behind the master as there is a lot of queries going on) show master status; +--++--+--+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +--++--+--+ | mysql-bin.04 | 1033832609 | | | +--++--+-- slow slave status \G *** 1. row *** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.10.5 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.04 Read_Master_Log_Pos: 1035462332 Relay_Log_File: clank-relay-bin.03 Relay_Log_Pos: 1024944283 Relay_Master_Log_File: mysql-bin.03 Slave_IO_Running: Yes Slave_SQL_Running: No Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 1062 Last_Error: Error 'Duplicate entry '1787' for key 1' on query. Default database: ''. Query: 'INSERT INTO dbseContentManagement.tblDailySpecial ( intProductUid ,fltPrice1Thx ,fltPrice2Thx ,fltPrice3Thx ,fltPrice4Thx ,fltPrice1Xyz ,fltPrice2Xyz ,fltPrice3Xyz ,fltPrice4Xyz ,boolIsEmailProduct ,boolIsFixedSpecials ,boolIsFixedHomepage ,strDate ) VALUES (1081,0.50,0.45,0.40,0.35,0.60,0.60,0.60,0.60,0,0,0,'20050823')' Skip_Counter: 0 Exec_Master_Log_Pos: 1024944197 Relay_Log_Space: 2109204794 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: NULL -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need to install MySQL extensions for php...
On Tue, 2005-08-02 at 19:54 -0300, Javier Carlos Viegas wrote: PHP needs a set of MySQL functions called MySQL extension How can i install those? Do i missed some configuration options?? When you compile php you need to add --with-mysql to your configure line ie ./configure --with-mysql if you have installed mysql in a non standard location you may have to do --with-mysql=/path/to/mysql also if you're using php 4.1 or above, considder using the mysqli extension instead of mysql as it is faster and has more features you will find all the information you need in the php manual http://www.php.net/manual/en/index.php tony Thanks for any help provided. Javier __ Correo Yahoo! Espacio para todos tus mensajes, antivirus y antispam ¡gratis! ¡Abrí tu cuenta ya! - http://correo.yahoo.com.ar -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: advanced group by
On Wed, 2005-08-03 at 11:59 +0100, James M. Gonzalez wrote: I would like to obtain the following results: CompanyName - WhatToShip - Ready - Almost - Done Foo- car - 26 - 2- 23 Foo-elephant - 43 - 0- 15 Foo-acuarium - 12 - 6- 47 Bar- mobile- 9- 0- 52 Bar- fan - 15 - 4- 43 select SELECT sum(if(tracking_number = '' AND serialNumber = ''),1,0) as ready ,sum(if(tracking_number != '' AND serialNumber = ''),1,0) as almost from etc I havent specifically tested this but thats the general idea, it's called cross tabulation. if you google cross tabulation tutorial i'm sure something will come up tony -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Field size error
On Wed, 2005-08-03 at 20:11 +0700, Dwi Putra L wrote: Some days ago, I create a software, using delphi 7 and mysql 4.0.0 Alpha as the database. are you sure about the 4.0.0 Alpha ? if so your're about 2 years behind... tony -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: innodb performance issues
On Fri, 2005-07-15 at 13:28 -0700, David Griffiths wrote: David, Thanks for your suggestions, i'll give them a try. There are other tuning choices (including the thread-pool-cache). The best resource is the page on innodb performance tuning, and it can be found here: http://dev.mysql.com/doc/mysql/en/innodb-configuration.html I didi read through this before I posted, however I am a programmer with no real admin experience in at the deep end, and it was all a little over my head :( I guess i'll get to understand it in time. Regards tony -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
innodb performance issues
Hi, A few days ago i posted a quaestion about performace, I now have a little more info, hopefully someone can help. I have a table, tblShoppingCart with 3 fields, cartUid (int 11 auto increment) userUid (int 11, indexed) strCartHash (varchar 32) The table is innodb Nomally my server load is below 0.1 and everythings fine, I have a process that runs occasionally that pushes the load up to 1.5, when this happens inserts into the table seem to get blocked, ie taking up to 20 seconds, as soon as the load drops the inserts are fine again. Interestingly, if I convert the table to myisam I don't get this problem. However I really want to keep the table innodb as I use it in transactions latter. My my.cnf file is coppied from the default huge.cnf file, i have duel xeons with 4gb of ram and i'm running mysql 4.1.1 on red hat linux. Any pointers on where i can look further appreciated. Tony -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: innodb performance issues
Hi David, On Fri, 2005-07-15 at 10:25 -0700, David Griffiths wrote: Tony, - not sure what version you are using 4.1.11. Server is a duel xeon machine with 4gb or ram running mysql and apache webserver and not much else. You should have used my-innodb-heavy-4G.cnf as the starting point for an InnoDB system. I can use this instead if it's going to help. If you want help, you'll need to post your my.cnf file, [client] port= 3306 socket = /var/lib/mysql/mysql.sock # The MySQL server [mysqld] port= 3306 socket = /var/lib/mysql/mysql.sock skip-locking key_buffer = 384M max_allowed_packet = 1M table_cache = 512 sort_buffer_size = 2M read_buffer_size = 2M read_rnd_buffer_size = 8M myisam_sort_buffer_size = 64M thread_cache = 8 query_cache_size = 32M log = /var/log/mysql/mysql.log log-slow-queries= /var/log/mysql/mysql-slow.log set-variable= max_connections=250 server-id = 1 innodb_data_home_dir = /var/lib/mysql/ innodb_log_group_home_dir = /var/lib/mysql/ innodb_buffer_pool_size = 384M innodb_additional_mem_pool_size = 20 the full table definition (try SHOW CREATE TABLE tblSoppingCart; and pasting the results in here). tblCart | CREATE TABLE `tblCart` ( `intCartUid` int(11) NOT NULL auto_increment, `intUserUid` int(11) NOT NULL default '0', `tsCartCreated` datetime NOT NULL default '-00-00 00:00:00', `tsLastUpdated` datetime NOT NULL default '-00-00 00:00:00', `strCartHash` varchar(32) NOT NULL default '', PRIMARY KEY (`intCartUid`), KEY `intUserUid` (`intUserUid`), KEY `tsLastUpdated` (`tsLastUpdated`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 You'll need to also post the queries that are hitting the database while you're having these issues. # Query_time: 20 Lock_time: 0 Rows_sent: 0 Rows_examined: 0 SET insert_id=34475,timestamp=1121407309; INSERT INTO dbseThxWebOrders.tblCart (intUserUid,tsCartCreated,strCartHash) VALUES (0,now(),'4e5d105f7cd34268e1a5e160d479ed91'); is an example from my slow query log. All of the offending queries today were this same query. Thanks for you help Tony -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
slow queries
Hi,
I have a query that keeps coming up in my slow queries log. The whole
database is innodb and i'm using mysql 4.1.11 on 64bit intel running red
hat linux. There are less than 100 rows in the offending table at anyone
time, and the server load rarely creeps up above 0.5
If i try to manually insert this row, i cant make it take longer than a
fraction of a second. I do get a few other random queries in the log
that take an age but this one comes up a lot.
Does anyone know if there are any issues with ON DUPLICATE KEY UPDATE,
or using varchar fields as a primary key?
if not how can i investigate this further? Table and query below
Thanks in advance
Tony
CREATE TABLE `tblSessionData` (
`sessionKey` varchar(32) NOT NULL default '',
`data` text NOT NULL,
`expiry` int(11) NOT NULL default '0',
PRIMARY KEY (`sessionKey`),
KEY `expiry` (`expiry`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
# Query_time: 26 Lock_time: 0 Rows_sent: 0 Rows_examined: 0
INSERT INTO dbseInfrastructure.tblSessionData
(sessionKey,expiry,data)
VALUES
('1dbeb00777bf1cd20f8e12d044f4fa4c',
1121252767, 'intRightBanner|i:6;formRequiredFields|a:2:{s:8:\username
\;i:0;s:8:\password\;i:0;}formValidation|a:2:{s:8:\username
\;i:0;s:8:\password\;i:0;}')
ON DUPLICATE KEY UPDATE
expiry=1121252767
,data='intRightBanner|
i:6;formRequiredFields|a:2:{s:8:\username\;i:0;s:8:\password
\;i:0;}formValidation|a:2:{s:8:\username\;i:0;s:8:\password\;
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: myisam or innodb (chestnut)
On Wed, 2005-07-13 at 15:02 +0100, zzapper wrote: Is myisam still dominant for web applications? depends on your application, if you're running a bulliten board or a simple catalogue site, then myisam is a good choice as it is faster. However for an ecommerce site i would definately go for innodb as it allows foreign keys and more importantly (imho) transactions. If what you're doing involves dealing with peoples money, its a good idea to be able to roll back if something goes wrong. tomy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL give up on a query after a certain length of time
Hi Guys, you can always set a max time out for your php script (php.ini) Tony - Original Message - From: Gleb Paharenko [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Friday, May 20, 2005 12:45 PM Subject: Re: MySQL give up on a query after a certain length of time Hello. I don't remember such way (except your transaction could abort due to lock timeout). Check with SHOW PROCESSLIST in what state your query hangs. Jacob Friis Larsen [EMAIL PROTECTED] wrote: Hello. Is there a way to have MySQL give up on a query after a certain length of t= ime? I use Debian Linux Sarge, MySQL 4.1 and Php 5. Sometimes I have queries that take forever although the same query returns fast at other times. Thanks, Jacob -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Create DB help
Hello,
I am new with using mySQL.
I have a little problem
I am starting a new web site on which I wanna use the same DB I am using on another
site (that was created by somebody else). Instead of starting from scratch, I would
like to use a copy of the DB we have, since we know it is already good and tested.
I have an .sql file for the database structure.
I found this:
http://www.jsw4.net/info/list-archives/mysql/02-wk49/msg00049.html
I believe it is exactly what I need.
What I need to knwo now... could you please tell me WHERE I need to try:
mysql -u{username} -p {databasename}filename.sql
Thank you very much in advance
Tony
RE: upgraded hardware: new server is faster, but GROUP BY opera tions are slower???
Hmmm... So are you saying that GROUP BY operations use the CPU cache to a larger extent than other operations? (Is this documented somewhere?) Don't forget that the other 80-odd operations listed in the benchmark are actually faster on the new machine. It's odd that only these GROUP BY type operations are slower on the new box! They must use the hardware in a different way? Here is a summary of what I see regarding the CPU cache (from dmesg). Do you think these figures support your theory? (I'm not sure how Athlons compare to Xeons?) Note that the new CPU's have twice the amount of level 2 cache. The old box (2 CPU's): CPU: L1 I Cache: 64K (64 bytes/line), D cache 64K (64 bytes/line) CPU: L2 Cache: 256K (64 bytes/line) CPU0: AMD Athlon(tm) MP 2000+ stepping 02 per-CPU timeslice cutoff: 731.44 usecs. CPU1: AMD Athlon(tm) MP stepping 02 . CPU clock speed is 1666.7119 MHz. And the new box (4 CPU's): CPU: L1 I cache: 12K, L1 D cache: 8K CPU: L2 cache: 512K CPU0: Intel(R) Xeon(TM) CPU 2.80GHz stepping 09 per-CPU timeslice cutoff: 1463.10 usecs. CPU3: Intel(R) Xeon(TM) CPU 2.80GHz stepping 09 . CPU clock speed is 2793.7204 MHz. Thanks for your advice everyone! Tony Ps. Good suggestion Donny, but both /tmp's are ext3. (And the new /tmp is faster, based on this little test: rm -f ttt; sync; sync; time dd if=/dev/zero of=./ttt bs=16M count=32) Kevin Cowley wrote: Don't tell me - you upgraded a PIII server to a PIV server? The cache on the PIV is les than half the size of a PIII. We've hit this problem with our own apps that by the way they operate cache a lot of data. A PIII 1.4GHz will match a 2.4 GHz PIV. Yes, MySQL code is written with a lot of care to use the CPU cache efficiently. Having a larger cache will compensate for the lack of CPU speed to a great extent. So the results do look about right. *** Bear Stearns is not responsible for any recommendation, solicitation, offer or agreement or any information about any transaction, customer account or account activity contained in this communication. *** -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
upgraded hardware: new server is faster, but GROUP BY operations are slower???
Hi all, Just bought a new server, which was supposed to improve the performance of our app. The new machine has the same OS (Redhat 8), same MySQL (4.0.18), and same my.cnf. The problem is that this (frequently run) query, actually runs 41% slower! select * from LEG L, LEG_DETAIL D, DEAL_LEGS G where L.latest_id = D.latest_id and D.latest_id = G.latest_id group by L.dealid limit 75; If I remove the GROUP BY, then the new box returns the results 14% faster than the old server. (So what's so special about GROUP BY?) I ran the benchmark scripts on both boxes, and it seems the following operations are slower on the new server, for some reason? count_distinct (1000) count_distinct_2 (1000) count_distinct_group (1000) count_distinct_group_on_key (1000) count_distinct_group_on_key_parts (1 count_distinct_key_prefix (1000) count_group_on_key_parts (1000) count_on_key (50100) select_distinct (800) select_group (2911) Can anyone suggest why the new machine might be slower, ONLY IN THE ABOVE areas? Just in case these numbers help, here are lines from the RUN file, for two of the slow operations above Operationsecondsusr sys cputests count_distinct_group 19.001.170.081.251000 count_distinct_group (new box)26.000.390.260.651000 count_distinct12.000.510.020.531000 count_distinct (new box) 15.000.100.010.111000 Any advice at all, would be very much appreciated! Thanks, Tony *** Bear Stearns is not responsible for any recommendation, solicitation, offer or agreement or any information about any transaction, customer account or account activity contained in this communication. *** -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Selecting non opposites
I'm a newbie but thought I'd try my hand and offer an extremely inefficient query that seemed to work ... select * from word join word as tw join word as tm where concat(word.wd, tw.wd, tm.wd) not like '%male%female%' and concat(word.wd, tw.wd, tm.wd) not like '%female%male%' ORDER BY rand() LIMIT 1; Dave Dash wrote: Yeah, I want to avoid code if possible, some ideas was marking adjectives with a class tag, and doing a group by class so only one item in any given class would appera e.g. good and evil would have the same class or male and female would have the same class. this, however, requires me to enter a class id for each thing which is time(or code) consuming, but doable. Diana Cristina Neves Soares wrote: To select random values you could use: SELECT word FROM table ORDER BY rand() LIMIT 3 , But the problem of not to select male and female together... Well, you could LIMIT your query to 4 and in your code, if one entry is one of male or female, you test the others not to be female or male (respectively). For shure, you will always have 3 different values and you won't have male and female together (pityfully ;-) Diana Soares -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SQL Injection
Hi All, I've been hearing a bit about SQL injection lately, but the only documentation I can find refers to Microsoft or Oracle. Anyone know of good articles about injection in MySQL? Prevention? Detection? Is MySQL less vulnerable? Thanks, Tony -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Text formatting
That's because the browser doesn't read the line breaks without a br / tag at the end. All you need to do is this: ?php echo nl2br($textarea); ? nl4br(); will insert a br / tag after every new line so your browser can display it correctly. On Sunday, September 28, 2003, at 11:07 PM, delz wrote: Hi All, I'm using mysql and php but I'm having problems printing my output using a browser. It's like this, i have a form and it has a text area that has the following contents: Hello World. Hello World. Hello World. What happens is when I output the content using the php command echo, i.e. ?php echo $textarea;? the output is like this on the browser : Hello World.Hello World. Hello World. Its printing the output in one straight line. Are there any command other than echo that can output the same format as that given on the text area. Please help. Regards, Delz
Re: Text formatting
On Sunday, September 28, 2003, at 11:40 PM, Tony Thomas wrote: nl4br(); will insert a br / tag after every new line so your browser can display it correctly. Ahem. I mean nl2br();
select data from two tables without join
I have two tables that are similar, but not related. One is for meetings and the other for training. I'd like to run a query to select data from both based on the date so I can display the information on a web page. Is that possible? It seems unnecessary to run a separate query for each. I'd like to do something like this: SELECT * FROM meetings, trainings WHERE (meet_date = CURDATE()) AND (train_date = CURDATE()) ORDER BY meet_date DESC, train_date DESC; But this doesn't work. Is something like this possible? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: select data from two tables without join
I'm using 3.23.56, so UNION is out. MERGE looks like it might do the trick. The tables are duplicate other than the field names. The structure is identical. On Tuesday, September 16, 2003, at 08:32 AM, Haydies wrote: If the tables are the same, then maybe you could use one table with a field that tells the application if its a meeting or training. Other then that, UNION is very cool. As long as you have the same field definitions in 2 or more queries they can be added togeather. Unfortunatly you can do sub selects in 4.0.1 :-( so the list will always come out with the first table at the top, the second following. On a non-MySQL point, if you do select using a union but want every thing to be sorted as if it was one table then you can always use an array to store the records and sort that. It works really well with PHP. This is also a solution if you are using MySQL 3.x as that dosn't support UNION. Haydies. - Original Message - From: Tony Thomas [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, September 16, 2003 2:01 PM Subject: select data from two tables without join : I have two tables that are similar, but not related. One is for : meetings and the other for training. I'd like to run a query to select : data from both based on the date so I can display the information on a : web page. Is that possible? It seems unnecessary to run a separate : query for each. I'd like to do something like this: : : SELECT * FROM meetings, trainings : WHERE (meet_date = CURDATE()) AND (train_date = CURDATE()) : ORDER BY meet_date DESC, train_date DESC; : : But this doesn't work. Is something like this possible? : : : -- : 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: select data from two tables without join
My fields have unique names. For instance, the fields for the date in
each table are meet_date and train_date for the meetings and trainings
tables respectively.
If I did a merge, would I still be able to separate them out with PHP?
For instance:
while ($row=mysql_fetch_array($result)) {
echo $row[meet_date] . br /\n .
$row[training_date] . br /\n;
}
Or do field names need to be identical too (thus eliminating any chance
of keeping the data separate)?
On Tuesday, September 16, 2003, at 09:42 AM, Haydies wrote:
If you use merge you might not be able to tell which records came from
which
table. If that dosn't matter obviously its not a problem.
- Original Message -
From: Tony Thomas [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Tuesday, September 16, 2003 2:48 PM
Subject: Re: select data from two tables without join
: I'm using 3.23.56, so UNION is out. MERGE looks like it might do the
: trick. The tables are duplicate other than the field names. The
: structure is identical.
:
: On Tuesday, September 16, 2003, at 08:32 AM, Haydies wrote:
:
: If the tables are the same, then maybe you could use one table
with a
: field
: that tells the application if its a meeting or training.
:
: Other then that, UNION is very cool. As long as you have the same
field
: definitions in 2 or more queries they can be added togeather.
: Unfortunatly
: you can do sub selects in 4.0.1 :-( so the list will always come
out
: with
: the first table at the top, the second following.
:
: On a non-MySQL point, if you do select using a union but want every
: thing to
: be sorted as if it was one table then you can always use an array
to
: store
: the records and sort that. It works really well with PHP. This is
also
: a
: solution if you are using MySQL 3.x as that dosn't support UNION.
:
: Haydies.
--
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: Lotus Notes/Script...
In a message dated 9/15/03 4:53:18 PM Eastern Daylight Time, [EMAIL PROTECTED] writes: Would I have to have built MySQL with ODBC support? I installed the binary so wouldn;t this already be available... Download and install the MyODBC driver for your client application machine (surprisingly, it may be the Lotus Notes server in this case...but it's been a while since I've used Notes). If you are running MySQL on a *nix/Linux box, you'll need ODBCunix installed there too. Otherwise, if its on a Microsoft platform, it's likely already there. Links: http://www.odbcunix.org (probably on RedHat's site too) http://www.mysql.com/downloads/api-myodbc.html Hope this helps. /Tony ps. database, SQL, queries, sort, select, alter, table and other words for the list man to read. __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
CocoaMySQL
Good news for OS X users. CocoaMySQL has just been updated and it now supports CSV imports among other improvements. It's freeware too. http://www.MacUpdate.com/info.php/id/10573 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [OT] Autoresponders (is Re: Spam from this list)
I've been getting autoresponders from [EMAIL PROTECTED] [EMAIL PROTECTED] (The Oracle) [EMAIL PROTECTED] On Saturday, August 23, 2003, at 12:41 PM, Joe Baptista wrote: BTW, did anyone around here get mail from CServe containing important email addresses such as support? I got from them these mails twice to three times or so. Just wondering... i've been getting email from something called the oracle.
Re: Setting up MySQL on Mac OS X
Once you get everything up and running, and get the basics of MySQL down, here are some tools that can make database administration on the Mac a little simpler in terms of doing every day things, like creating databases, tables, searching, sorting and exporting: Cocoa MySQL- http://versiontracker.com/dyn/moreinfo/macosx/17838 I use Cocoa MySQL every day. It crashes occasionally (it's still in the beta version), but I find it easy-to-use. YourSQL - http://versiontracker.com/dyn/moreinfo/macosx/18018 Very similar interface to Cocoa MySQL Navicat - http://versiontracker.com/dyn/moreinfo/macosx/13371 I haven't used this one as much, but it looks like it might be good. Also, in the way of learning MySQL, I'd recommend Larry Ullman's _MySQL_ on Peach Pit press (ISBN 0-321-12731-5). It's an easy read and it will get you up and running quickly. I refer to this daily as well. Good luck! On Friday, August 22, 2003, at 09:12 AM, Rich Allen wrote: iH you may find this site helpful in getting MySQL running for the first time http://www.entropy.ch/software/macosx/mysql/ also recommend that you spend some time learning how to be root, unix permissions, etc - hcir On Thursday, Aug 21, 2003, at 22:53 America/Anchorage, Bob Goldberg wrote: I'm a novice Unix user, and I can't get MySQL set up properly on my Mac. It seems to have installed OK, but when I try to run the mysql_install_ db script, I get errors. I can't set the root password either that the documentation tells me to. As background, I just want to use MySQL to work with databases and PHP and HTML to set up web pages for database input and output. I'm running MySQL on my own machine at home, and have no use for passwords or any other administrative functions. I'm just looking to use the program to set up and query databases. Can anyone point me to a way to avoid all the UNIX admin stuff and run the database? I can get in, but the mysql database doesn't appear when I hit SHOW DATABASES as the tutuorial says, and I can't access or create new databases. I think all this may have something to do with my user account on my machine, but I'm not savvy enough to know how to override permissions, be root, or any of that stuff.
Re: MySQLD Binary Log
The following is my guess, give it a try if you want: Make sure that in your config file, log-bin isn't present, delete all the log files, as well as the host-bin.index file and restart MySQL. I utilize my log files, so I can't say how to stop them totally. -- Tony Evans Webmaster/DNS Administrator Netsync Internet Services, Corp. http://www.netsync.net/ Email: [EMAIL PROTECTED] Phone: 800-642-4294 Turtle said: We've been using MySQLD for a few years now quite happily. It's amazing how well it performs. However, I have a minor issue, and I'm not sure how to deal with it. I've scoured the manual, searched list archives, the web, etc., and am coming up empty. Somehow, the server is recording the Binary log in the data directory. Each of these log files takes up a gigabyte. This would be all well and good if we were using the server in as a master in a replication setup, but we are not. It WAS formerly acting as a master in a replication environment, but we've long since made it the only SQL server on our network. We've removed (commented) all the lines in my.cnf that deal with replication, and the log-bin is one of these lines. To clarify: the log-bin line in the my.cnf file IS commented with a hash-mark (#). The server has been restarted since this change, and SHOW MASTER STATUS returns an empty set of data (only showing the field names.) the SQL command RESET MASTER does nothing. It does not delete the hostname-bin.### files. the mysqld commandline parameters follow: /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/inferno.pid --skip-locking We're running version 3.23.41 for pc-linux-gnu on i686, running on SuSE Linux 7.1, kernel 2.4.18 #2 SMP on a Dual-Processor Pentium II 450MHz server So, if any of you can shed some light as to why it would be saving the binary log against our will, or as to how to stop it, it would be greatly appreciated. Brian -- 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: mac osx
There are some good Mac clients to access MySQL. I personally use CocoaMySQL for much of my day to day activity. You can find them all on versiontracker.org. YourSQL looks okay too. I agree though, the fancy interface, should you choose to use one of the above clients, is much more useful if you know some basics about how MySQL and Databases in general work. (Not assuming you don't, you may.) You'll need to use Terminal anyway to start the MySQL daemon. Marc Liyanage has some good documentation on MySQL on OS X. http://www.entropy.ch/software/macosx/mysql/ I followed his instructions and it works beautifully on my machine. On Thursday, June 19, 2003, at 03:31 PM, Brent Baisley wrote: There is no built-in graphically interface for running MySQL. It's all command line, meaning you need to use your terminal. You absolutely must read something on how to use MySQL. There are a lot of good books out there, I used MySQL by Paul DuBois (who is also on this list). MySQL is a database engine, which kind of works like FileMaker Server if you are familiar with that. On Thursday, June 19, 2003, at 02:14 PM, 4mula design wrote: Hi, I have installed mysql-standard 4.0.13 for Mac OSX10.2. I cannot get the mysql application to work-the files are white and cannot be launched. Any ideas what to do? Regards JB -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- 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 Needed! install client only
I need to install a mysql client only on a solaris2.8 machine, the mysql server is on another machine. I could not make the compile work using the source download. I got an error in make. Can someone point me to a binary client download somewhere? Or give me a hint of the following compile error. # make install ... cc -DDEFAULT_CHARSET_HOME=\/usr/local/mysqlc\ -DDATADIR=\/usr/local/mysqlc/var\ -DSHAREDIR=\/usr/local/mysqlc/share/mysql\ -DDONT_USE_RAID -I. -I. -I.. -I./../include -I../include -I./.. -I.. -I.. -O -DDBUG_OFF -DHAVE_CURSES_H -I/usr/mysql-3.23.55/include -DHAVE_RWLOCK_T -c net.c -KPIC -DPIC -o net.o net.c, line 184: syntax error before or at: / net.c, line 184: warning: character constant too long net.c, line 186: undefined symbol: is_blocking cc: acomp failed for net.c *** Error code 1 make: Fatal error: Command failed for target `net.lo' Current working directory /usr/mysql-3.23.55/libmysql_r *** Error code 1 make: Fatal error: Command failed for target `install-recursive' - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Build error (v3.23.55) on Solaris 2.8
When I tried to build a client only version (v3.23.55) on a Solaris2.8 machine, I got the following error: ( while doing make install) I did ./configure -without-server. It works fine. This is the error make gives. ... cc -DDEFAULT_CHARSET_HOME=\/usr/local/mysqlc\ -DDATADIR=\/usr/local/mysqlc/var\ -DSHAREDIR=\/usr/local/mysqlc/share/mysql\ -DDONT_USE_RAID -I. -I. -I.. -I./../include -I../include -I./.. -I.. -I.. -O -DDBUG_OFF -DHAVE_CURSES_H -I/usr/mysql-3.23.55/include -DHAVE_RWLOCK_T -c net.c -KPIC -DPIC -o net.o net.c, line 184: syntax error before or at: / net.c, line 184: warning: character constant too long net.c, line 186: undefined symbol: is_blocking cc: acomp failed for net.c *** Error code 1 make: Fatal error: Command failed for target `net.lo' Current working directory /usr/mysql-3.23.55/libmysql_r *** Error code 1 make: Fatal error: Command failed for target `install-recursive' Can someone help? Actually, if someone knows where I can find a client binary for Solaris 2.8, I can use it. Then I don't have to build myself. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
can't use MySQL control center 0.8.7-alpha update Chinese
Hi, I can't use MySQL control center 0.8.7-alpha to insert records with fields of Chinese GB2312 characters. The updated execution result is something like ?20, I use varchar. I run it on IIS, Chinese windows2000 professtional with service pack3. But when I use console insert record command, with windows console (code page 936 GBK), without control center, it works fine. Also, same configuration works with PremiumSoft MySQL studio 4.7.2 trial version to insert or update the record. So I guess there must be something wrong with the MySQL control center 0.8.7-alpha. Did I do something wrong or miss something? Can some one out there kind enough to help me get over it, I would appreciate it very much. Looking forward to hearing from you soon. Thank you! - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
True/False
I am new to MySQL and I am attempting a new project to help me learn. I the past I have used a Boolean data type for True/False, Yes/No type data. What data type should be used to achieve the storage of this type of data in MySQL? I have bought the reference manual (a good buy IMHO) but so far can't find the answer. Regards Tony Roberts Mailbox Solutions Ltd [EMAIL PROTECTED] 01777 700 600 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.389 / Virus Database: 220 - Release Date: 16/09/02 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
ODBC connectivity?
Recently, something happened on my system that I'm not sure I could ever explain. I'm curious if the expertise of the list might be able to help me out. Please speak layman terms, as I'm forced to jump in this without testing the waters... I was working fine with MSAccess 97 to MySQL (ver 3.23.38) via MySQL ODBC drivers 2.50 on RH7.1 alpha build. Several days ago, my Access began failing to connect to the MySQL database with the ODBC drivers. I checked the /path_to_mysql/server.log file and discovered that whenever the connection attempt was made by MSAccess, MySQL killed and restarted. I've done some browsing of the 'archives' and came to the conclusion that the ODBC drivers probably needed updating - since I was still able to connect by telnet to the server:port from localhost that MySQL was assigned to listen to for my connections. Upgrading to ODBC 3.51.04 has not really fixed my problem, but has given me an error number (#2203) in the windows error boxes. My MySQL continues to kill/restart when attempting connection via ODBC. I have PERL scripts that write/modify via SQL commands to the MySQL databases that still work ok, which tells me that the global and user privileges are still intact with the data files. I've ran 'myisamchk -r *.MYI' and 'myisamchk -e *.MYI' in the directory with the database files and no errors were reported. Also - I've noticed that as root, I cannot shut down the MySQL using the standard /etc/rc.d/init.d/mysqld stop. It fails, and I cannot figure out why. Failing to properly shut down on a reboot or other system malfunction will one day severely corrupt my data - and I'm not looking forward to that. But, I can shut the databases down with 'mysqladmin -p shutdown' and supplying the root password. I'm baffled. I'm not officially part of the list, so if anyone would care to lend some aid, please e-mail me directly (or to the list and I'll eventually get it). At this point, I'm desperate. The hot breath of my users are on my neck... Thanks in advance. -Tony -- __ Tony Alston - Westinghouse TRU Solutions, LLC - Email mailto:tony.alston;wipp.ws Web Site http://www.t2ed.com __ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: OT but related: DNS using SQL databases
FYI..I use MyDNS here, and i really like it. has a nice little contribed PHP interface included in it too. be aware, it's only an authoritative name server, if you want to do recursive/cache, you need something like djbdns dnscache along side it. -Tony - System Administrator Net Infrastructure - -Original Message- From: Joseph Bueno [mailto:[EMAIL PROTECTED]] Sent: Thursday, October 03, 2002 5:37 AM To: David Durham Cc: [EMAIL PROTECTED] Subject: Re: OT but related: DNS using SQL databases David Durham wrote: Hi, I have heard of some Linux based DNS services that use MySQL as the bases for zone files. Is there anyone who could point me in the right direction or has experience in using these tools. The is my understanding that queries can be created that allow much easier maintanence of the DNS system. Thanks. -David Hi, A quick search on Google gave me a pointer to MyDNS: http://mydns.bboy.net/ Hope this helps Joseph Bueno - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Select Query
I seem to be having problems trying to get to http://www.corereader.com ... Anyone else? +--+ | ___ ___ | | //_/_/_/_/_/ //_/_/_/ | |//_/ //_/ _/ | | //_/ //_/ _/| | //_/ //_/_/_/ | |--| | Tony Devlin - [EMAIL PROTECTED] | | Airewaves Broadband, Systems Admin | | Website - www.airewaves.com | | Atlanta, Ga - 678.522.3923 | | Beaufort, SC - 843.379.AIRE(2473)| +--+ -Original Message- From: John Ragan [mailto:[EMAIL PROTECTED]] Sent: Thursday, October 03, 2002 10:47 AM To: [EMAIL PROTECTED]; Chris Kay Subject: Re: Select Query if you have an ms. windows machine for a front end, i recommend that you download corereader from http://CoreReader.com/ . ( it's free. ) it does pointclick queries, so you can quickly experiment with them until you get what you want. it installs at the novice level, so set it to the proficient skill level. in the where clause frame, you'll find drop-down lists for the selects. i believe that the one that you want is the is in which will produce the ansi92 in select for you. when it's returning what you want, open the sql frame, which will show you the sql statement that it built for you. (fair warning: not even corereader can make the data connection simple. ) Query ( that gets past the anti spam ) Question is.. I have a select where I want to get ID 15 id 25 Can I do something like WHERE ID = 15,25 Or do I have to do WHERE ID = 15 ID = 25 - Chris Kay Techex Communications Website: www.techex.com.au Email: [EMAIL PROTECTED] Telephone: 1300 88 111 2 - Fax: (02) 9970 5788 - -- John Ragan [EMAIL PROTECTED] 501-228-0317 http://www.CoreReader.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Help request for connection problem with Java app
Hi I have MySQL (version 3.23.38) installed on Win 98 running locally as mysqld-opt which I am calling from a Java app using the mm.mysql-2.0.4 JDBC driver. I've also tried the new Connector/J but the same problem exists. When I try to have more than 30 or so open connections at a time from the Java app I am getting the following error back 'Cannot connect to MySQL server on localhost:3306. Is there a MySQL server running on the machine/port you are trying to connect to? (java.net.SocketException)' The problem does not always occur when I first use the Java app and access the database but after restarting the app several times the error occurs and will subsequently re-occur every time. Prior to the problem I'd not changed any of the configuration settings from the MySQL install. I've tried increasing max_connections (which was set at 100) but this has no effect. Sounds to me like some resource is being used up and possibly not being released but apart from that I haven't much clue. Regards Tony Prichard - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Can't start mysqld from Source Distro - can't readmessagefile errmsg.sys
I think I have solved this - though what I had to do was weird. In mysql-VERSION/support-files there's a program called comp_err. You need to cd to the share/language directory and run # comp_err errmsg.txt errmsg.sys Then run scripts/mysql_install_db. Why should I have to do this? Sounds like a bug to me, and I've now done this on both SuSE 7.0 and SuSE8.0. Tony Butcher From: Gerald Clark [EMAIL PROTECTED] Organization: Supplier Systems Corporation Date: Fri, 06 Sep 2002 13:30:29 -0500 To: Tony Butcher [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: Can't start mysqld from Source Distro - can't read messagefile errmsg.sys You have to create a mysql user and chown -R mysql /usr/local/mysql then run ./scripts/mysql_install_db --user=mysql Tony Butcher wrote: Hi, I have compiled 4.0.3-beta onto SuSE Linux 7.0. The configure, make and make install go fine, but then when I try to run the mysql_install_db script I get a failure. Here's the error: backup:/usr/local/mysql-4.0.3-beta # ./scripts/mysql_install_db Preparing db table Preparing host table Preparing user table Preparing func table Preparing tables_priv table Preparing columns_priv table Installing all prepared tables 020906 13:20:20 Can't read from messagefile '/usr/local/mysql/share/mysql/english/errmsg.sys' 020906 13:20:20 Aborting Installation of grant tables failed! Examine the logs in /usr/local/mysql/var for more information. You can also try to start the mysqld daemon with: /usr/local/mysql/libexec/mysqld --skip-grant You can use the command line tool /usr/local/mysql/bin/mysql to connect to the mysql database and look at the grant tables: shell /usr/local/mysql/bin/mysql -u root mysql mysql show tables Try 'mysqld --help' if you have problems with paths. Using --log gives you a log in /usr/local/mysql/var that may be helpful. The latest information about MySQL is available on the web at http://www.mysql.com Please consult the MySQL manual section: 'Problems running mysql_install_db', and the manual section that describes problems on your OS. Another information source is the MySQL email archive. Please check all of the above before mailing us! And if you do mail us, you MUST use the /usr/local/mysql/bin/mysqlbug script! It complains that it can't read the messagfile errmsg.sys, but I know it's there! Here it is: backup:/usr/local/mysql-4.0.3-beta # l /usr/local/mysql/share/mysql/english/errmsg.sys -rw-r--r-- 1 root mysql 12183 Sep 2 17:34 /usr/local/mysql/share/mysql/english/errmsg.sys Is anyone else getting this? Any ideas how to get round it? Thanks! Tony Butcher - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Can't start mysqld from Source Distro - can't read messagefileerrmsg.sys
Hi, I have compiled 4.0.3-beta onto SuSE Linux 7.0. The configure, make and make install go fine, but then when I try to run the mysql_install_db script I get a failure. Here's the error: backup:/usr/local/mysql-4.0.3-beta # ./scripts/mysql_install_db Preparing db table Preparing host table Preparing user table Preparing func table Preparing tables_priv table Preparing columns_priv table Installing all prepared tables 020906 13:20:20 Can't read from messagefile '/usr/local/mysql/share/mysql/english/errmsg.sys' 020906 13:20:20 Aborting Installation of grant tables failed! Examine the logs in /usr/local/mysql/var for more information. You can also try to start the mysqld daemon with: /usr/local/mysql/libexec/mysqld --skip-grant You can use the command line tool /usr/local/mysql/bin/mysql to connect to the mysql database and look at the grant tables: shell /usr/local/mysql/bin/mysql -u root mysql mysql show tables Try 'mysqld --help' if you have problems with paths. Using --log gives you a log in /usr/local/mysql/var that may be helpful. The latest information about MySQL is available on the web at http://www.mysql.com Please consult the MySQL manual section: 'Problems running mysql_install_db', and the manual section that describes problems on your OS. Another information source is the MySQL email archive. Please check all of the above before mailing us! And if you do mail us, you MUST use the /usr/local/mysql/bin/mysqlbug script! It complains that it can't read the messagfile errmsg.sys, but I know it's there! Here it is: backup:/usr/local/mysql-4.0.3-beta # l /usr/local/mysql/share/mysql/english/errmsg.sys -rw-r--r-- 1 root mysql 12183 Sep 2 17:34 /usr/local/mysql/share/mysql/english/errmsg.sys Is anyone else getting this? Any ideas how to get round it? Thanks! Tony Butcher
Re: Can't start mysqld from Source Distro - can't readmessagefile errmsg.sys
Sorry, user mysql and group mysql already exist, and permissions were already set correctly. It didn't work. Any more ideas? Thanks again. Tony From: Gerald Clark [EMAIL PROTECTED] Organization: Supplier Systems Corporation Date: Fri, 06 Sep 2002 13:30:29 -0500 To: Tony Butcher [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: Can't start mysqld from Source Distro - can't read messagefile errmsg.sys You have to create a mysql user and chown -R mysql /usr/local/mysql then run ./scripts/mysql_install_db --user=mysql Tony Butcher wrote: Hi, I have compiled 4.0.3-beta onto SuSE Linux 7.0. The configure, make and make install go fine, but then when I try to run the mysql_install_db script I get a failure. Here's the error: backup:/usr/local/mysql-4.0.3-beta # ./scripts/mysql_install_db Preparing db table Preparing host table Preparing user table Preparing func table Preparing tables_priv table Preparing columns_priv table Installing all prepared tables 020906 13:20:20 Can't read from messagefile '/usr/local/mysql/share/mysql/english/errmsg.sys' 020906 13:20:20 Aborting Installation of grant tables failed! Examine the logs in /usr/local/mysql/var for more information. You can also try to start the mysqld daemon with: /usr/local/mysql/libexec/mysqld --skip-grant You can use the command line tool /usr/local/mysql/bin/mysql to connect to the mysql database and look at the grant tables: shell /usr/local/mysql/bin/mysql -u root mysql mysql show tables Try 'mysqld --help' if you have problems with paths. Using --log gives you a log in /usr/local/mysql/var that may be helpful. The latest information about MySQL is available on the web at http://www.mysql.com Please consult the MySQL manual section: 'Problems running mysql_install_db', and the manual section that describes problems on your OS. Another information source is the MySQL email archive. Please check all of the above before mailing us! And if you do mail us, you MUST use the /usr/local/mysql/bin/mysqlbug script! It complains that it can't read the messagfile errmsg.sys, but I know it's there! Here it is: backup:/usr/local/mysql-4.0.3-beta # l /usr/local/mysql/share/mysql/english/errmsg.sys -rw-r--r-- 1 root mysql 12183 Sep 2 17:34 /usr/local/mysql/share/mysql/english/errmsg.sys Is anyone else getting this? Any ideas how to get round it? Thanks! Tony Butcher - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Problem with Tables...
I have a problem with MySQL. I am trying to insert data using INFILE, and it works, except that it ALWAYS stop at 255 (autoindex) and will not go any further, either by manual insert or textfile insert. I am running MySQL 3.23.38-max-nt on a Windows 2000 Server. Is this a known issue? something I can fix? configure problem? Any help is greatly appreciated. +--+ | ___ ___ | | //_/_/_/_/_/ //_/_/_/ | |//_/ //_/ _/ | | //_/ //_/ _/| | //_/ //_/_/_/ | +--+ | Tony Devlin - [EMAIL PROTECTED] | | Airewaves Broadband, Systems Admin | | Website - www.airewaves.com | | Atlanta, Ga - 678.522.3923 | | Beaufort, SC - 843.379.AIRE(2473)| +--+ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Some prob...
Got this when i use make libmysql.c mkdir .libs gcc -DDEFAULT_CHARSET_HOME=\/usr/local/mysql\ -DDATADIR=\/usr/local/mysql/var\ -DSHAREDIR=\/usr/local/mysql/share/mysql\ -DUNDEF_THREADS_HACK -DDONT_USE_RAID -I./../include -I../include -I./.. -I.. -I.. -O3 -DDBUG_OFF -c libmysql.c -fPIC -DPIC -o .libs/libmysql.lo libmysql.c: In function `mysql_real_connect': libmysql.c:1324: warning: passing arg 5 of `gethostbyname_r' from incompatible pointer type libmysql.c:1324: too few arguments to function `gethostbyname_r' libmysql.c:1324: warning: assignment makes pointer from integer without a cast make[2]: *** [libmysql.lo] Error 1 make[2]: Leaving directory `/usr/src/mysql-3.23.51/libmysql' make[1]: *** [all-recursive] Error 1 make[1]: Leaving directory `/usr/src/mysql-3.23.51' make: *** [all-recursive-am] Error 2 [root@nonifo mysql-3.23.51]# First of all i have tar -zxvf mysql-3.23.51.tar.gz Then i have enter the map, runing ./configure -prefix=/usr/local/mysql Then make Lol // Tony B [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MySQL 3.23.33 mysqldump of mysql database has syntax errors in table creation
Description:
When dumping the 'mysql' database, the dump file contains syntax errors,
and I can't find where this has been rectified in later releases.
Example dump using 'mysqldump -cl mysql':
[snip]#
# Table structure for table 'db'
#
CREATE TABLE db (
Host char(60) NOT NULL default '',
Db char(32) NOT NULL default '',
User char(16) NOT NULL default '',
Select_priv enum('N','Y') NOT NULL default 'N',
Insert_priv enum('N','Y') NOT NULL default 'N',
Update_priv enum('N','Y') NOT NULL default 'N',
Delete_priv enum('N','Y') NOT NULL default 'N',
Create_priv enum('N','Y') NOT NULL default 'N',
Drop_priv enum('N','Y') NOT NULL default 'N',
Grant_priv enum('N','Y') NOT NULL default 'N',
References_priv enum('N','Y') NOT NULL default 'N',
Index_priv enum('N','Y') NOT NULL default 'N',
Alter_priv enum('N','Y') NOT NULL default 'N',
PRIMARY KEY (Host,Db,User),
KEY User(User)
) TYPE=MyISAM;
[/snip]
Trying to re-load this table fails with the following error message:
ERROR 1064: You have an error in your SQL syntax near 'User(User)
) TYPE=MyISAM' at line 16
Hopefully I'm not just re-iterating a previously fixed problem. Feel
free to email me if more information is needed.
How-To-Repeat:
Dumping the mysql database is enough to re-create this problem. (Checked in both
3.23.33 and 3.23.32.)
Fix:
Changing
KEY User(User)
To
KEY User (User)
In the dump fixes this.
Submitter-Id: None
Originator:Tony Wells
Organization:
None
MySQL support: none
Synopsis: mysqldump of mysql database has syntax error in SQL
Severity: non-critical
Priority:
Category: mysql
Class:
Release: mysql-3.23.33 (Source distribution)
Server: /usr/local/bin/mysqladmin Ver 8.15 Distrib 3.23.33, for -freebsd4.2 on i386
Copyright (C) 2000 MySQL AB MySQL Finland AB TCX DataKonsult AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license
Server version 3.23.33
Protocol version10
Connection Localhost via UNIX socket
UNIX socket /tmp/mysql.sock
Uptime: 3 min 37 sec
Threads: 1 Questions: 195 Slow queries: 0 Opens: 64 Flush tables: 1 Open tables:
58 Queries per second avg: 0.899
Environment:
System: FreeBSD camel.kdsi.net 4.5-STABLE FreeBSD 4.5-STABLE #0: Wed Apr 24 10:19:40
CDT 2002 [EMAIL PROTECTED]:/usr/obj/usr/src/sys/IPFW i386
Some paths: /usr/bin/perl /usr/bin/make /usr/local/bin/gmake /usr/bin/gcc /usr/bin/cc
GCC: Using builtin specs.
gcc version 2.95.3 20010315 (release) [FreeBSD]
Compilation info: CC='cc' CFLAGS='-O -pipe' CXX='c++' CXXFLAGS='-O -pipe
-felide-constructors -fno-rtti -fno-exceptions' LDFLAGS=''
LIBC:
-r--r--r-- 1 root wheel 1221930 Apr 24 11:25 /usr/lib/libc.a
lrwxr-xr-x 1 root wheel 9 Apr 24 11:25 /usr/lib/libc.so - libc.so.4
-r--r--r-- 1 root wheel 579224 Apr 24 11:25 /usr/lib/libc.so.4
Configure command: ./configure --localstatedir=/var/db/mysql --without-perl
--without-debug --without-readline --without-bench --with-mit-threads=no
--with-libwrap --with-low-memory --enable-assembler --prefix=/usr/local
i386--freebsd4.2
Perl: This is perl, version 5.005_03 built for i386-freebsd
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Capacité Mysql
Bonjour, J'utilise Mysql 3.23... sous un environnement Solaris 7. La taille de la base de donnée est de 1Go 5. Quelles sont les limites pour le bon fonctionnment de mysql ? Existe il un moyen plus rapide de faire des copies de bases que le dump ? Merci Cordialement, Tony Jarriault --- Service webmaster : mailto:[EMAIL PROTECTED] Tel : 01-34-49-06-69 MGN : http://www.mgn.fr --- Tony Jarriault mailto:[EMAIL PROTECTED] Tel : 01-34-49-06-43 MATRA GLOBAL NETSERVICES Societé du groupe PROSODIE 8, rue Grange Dame Rose 78140 Vélizy - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Speed question.
By any chance are you using DBI, and prepare in the query? If so, have you use prepare_cached and roled the select statement into a function that accepts the 'CONSTANT' as an input param to the function? That gave me tremendous perf improvements. -Original Message- From: BD [SMTP:[EMAIL PROTECTED]] Sent: Wednesday, March 20, 2002 10:42 AM To: [EMAIL PROTECTED] Subject:Re: Speed question. At 01:17 AM 3/20/2002, you wrote: An Oracle DB programmer reviewed a query that I wrote and told me that putting constants at the beginning of the query would make it slower. I thought I'd go to the experts on MySQL and be told the truth one way of the other. Example query: SELECT TABLE1.COL, TABLE2.COL FROM TABLE1, TABLE2 WHERE TABLE1.COL='CONSTANT' AND TABLE1.COL=TABLE2.COL [ OR ] SELECT TABLE1.COL, TABLE2.COL FROM TABLE1, TABLE2 WHERE TABLE1.COL=TABLE2.COL AND TABLE1.COL='CONSTANT' I know that indexes are important and such, the above is just an example to give some visual. Thanks for any help you can offer. Rob. Rob, The only thing to watch out for is that the constant should be the same data type as the column. Fox example, don't compare a Char to an Integer (or vice-versa) otherwise MySQL will have to convert the column on a row by row basis thus eliminating the use of the index.. Also if you perform a function on a column name, then it also can't use the index. So if you need to use functions it is best that you move the function to the constant side of the comparison if possible. Brent _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Adding Columns to table
You may also find it more convenient to edit a text file with all of your data and table creation commands in it, then use the 'source' command inside an mysql session to execute them. That way when you run into an error, you just edit the file and repeat. Another nicety about MySQL is a command that you could put in your edit file just prior to the create table command to delete the table, if it already exists. Be careful though, not to delete a table that you intend on altering. The syntax is like this: delete table IF EXISTS tablexyz; create table tablexyz ( columnA tinyint auto_increment, ... -Original Message- From: Paul DuBois [SMTP:[EMAIL PROTECTED]] Sent: Wednesday, March 20, 2002 5:42 PM To: alan4100; [EMAIL PROTECTED] Subject:Re: Adding Columns to table At 15:30 -0500 3/20/02, alan4100 wrote: I am creating a database called Meet_a_Geek at MS DOS prompt. The customer table has about 30 fields..no time counting..but I noticed that I cannot go beyond 9 fields without ending with a semicolon. The mysql program does not require that you enter an entire query on a single line. Just enter it over the source of several lines, ending the last one with a semicolon. Also another problem crops up when I tried to recreate the customer table with the remaining fields yet to be created. The error message says Customer Table existed. Is there a windows program that I can input all at once with ease? I know more questions are keeping coming in...Please just refer me to good tutorial sites so I can continue and finish this assignment. You may take a glance at my assignment below from cut and paste with dos editor. == == mysql use Meet_a_Geek; Database changed mysql create table customers (Customer_ID INT NOT NULL PRIMARY KEY AUTO_INCREME NT, First_Name VARCHAR(20) NOT NULL, LAst_Name VARCHAR(30) NOT NULL); Query OK, 0 rows affected (0.00 sec) mysql show tables from Meet_a_Geek; +---+ | Tables_in_Meet_a_Geek | +---+ | customers | +---+ 1 row in set (0.01 sec) mysql create table customers (Customer_ID INT NOT NULL PRIMARY KEY AUTO_INCREME NT, First_Name VARCHAR(20) NOT NULL, LAst_Name VARCHAR(30) NOT NULL, address VAR CHAR(50), City VARCHAR(20), State VARCHAR(2), Zip VARCHAR(20), E_Mail VARCHAR(20 ), Age INT); ERROR 1050: Table 'customers' already exists mysql create table customers (Customer_ID INT NOT NULL PRIMARY KEY AUTO_INCREME NT, First_Name VARCHAR(20) NOT NULL, LAst_Name VARCHAR(30) NOT NULL, address VAR CHAR(50), City VARCHAR(20), State VARCHAR(2), Zip VARCHAR(20), E_Mail VARCHAR(20 ), Age INT); - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Adding Columns to table
Just put a carriage return (e.g. the enter key) in your command every, say 72 characters or wherever the next logical word occurs. MySQL really only cares about ending all the entry with a semi-colon (or some maximum line length which I think you've run up against). In short, hitting the enter key after on column definition does not send it to MySQL for processing, the ending semi-colon does that for you. :-) -Original Message- From: alan4100 [SMTP:[EMAIL PROTECTED]] Sent: Wednesday, March 20, 2002 3:31 PM To: [EMAIL PROTECTED] Subject:Adding Columns to table I am creating a database called Meet_a_Geek at MS DOS prompt. The customer table has about 30 fields..no time counting..but I noticed that I cannot go beyond 9 fields without ending with a semicolon. Also another problem crops up when I tried to recreate the customer table with the remaining fields yet to be created. The error message says Customer Table existed. Is there a windows program that I can input all at once with ease? I know more questions are keeping coming in...Please just refer me to good tutorial sites so I can continue and finish this assignment. You may take a glance at my assignment below from cut and paste with dos editor. == == mysql use Meet_a_Geek; Database changed mysql create table customers (Customer_ID INT NOT NULL PRIMARY KEY AUTO_INCREME NT, First_Name VARCHAR(20) NOT NULL, LAst_Name VARCHAR(30) NOT NULL); Query OK, 0 rows affected (0.00 sec) mysql show tables from Meet_a_Geek; +---+ | Tables_in_Meet_a_Geek | +---+ | customers | +---+ 1 row in set (0.01 sec) mysql create table customers (Customer_ID INT NOT NULL PRIMARY KEY AUTO_INCREME NT, First_Name VARCHAR(20) NOT NULL, LAst_Name VARCHAR(30) NOT NULL, address VAR CHAR(50), City VARCHAR(20), State VARCHAR(2), Zip VARCHAR(20), E_Mail VARCHAR(20 ), Age INT); ERROR 1050: Table 'customers' already exists mysql create table customers (Customer_ID INT NOT NULL PRIMARY KEY AUTO_INCREME NT, First_Name VARCHAR(20) NOT NULL, LAst_Name VARCHAR(30) NOT NULL, address VAR CHAR(50), City VARCHAR(20), State VARCHAR(2), Zip VARCHAR(20), E_Mail VARCHAR(20 ), Age INT); - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Re: Searchable archives of this list
- Original Message - From: Kim Hendrikse [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Sunday, March 03, 2002 7:04 PM Subject: Searchable archives of this list Hi, For those of you who are not aware of it, we run a searchable archive of this list at the following location: Personally I find your automenus (under IE) most annoying and I for one won't be using the service. T. mysql table row - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Hi
- Original Message - From: cherian [EMAIL PROTECTED] To: MySql [EMAIL PROTECTED] Sent: Saturday, March 02, 2002 1:12 PM Subject: Hi Hi, I have an application that has got Oracle as the central databse, if all the client machines are online then it will update the central oracle databse otherwise in the offline mode the client machine will update the local MS Access databse, and later on when ever the network comes back the central databse is updated with the changes. I want now to change the MS Access with MySql in the client machine, is it possible to do so ? Of course it's possible but you don't give many clues about what info you're after? What are your timescales, what's the budget, what are the clients written in, what is the central db update process written in, any ODBC... and a hundred other questions. Just what are you asking here? Tony - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Showing results of a search
- Original Message - From: Daniel Ferreira Castro [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, March 01, 2002 1:22 PM Subject: Showing results of a search I am programming the scripts of the Intranet of my Office, and I am using PHP and MySql. I would like that when I execute a search the results would be show on pages with 20 results per page. Is that possible??? Thank you Daniel F. Castro This is a good site explaining just what you are after... http://www.webmasterbase.com/article.php?pid=0aid=662 Tony - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
crash bug with ORDER BY text
Using versions 3.23.47 or 3.23.49a on linux i386 precompiled binaries. Using ORDER BY on a column that is type text causes a crash. The following MySQL Query crashes it's MySQL thread: create table test ( id int, note text); insert into test values (1, 'blah'); select * from test order by note; I haven't tried any other versions yet, but the crash doesn't happen with 3.23.21-beta. -Tony Tony J. White [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Fw: would you send me the mysql distribution which can support big5
- Original Message - From: Sinisa Milivojevic [EMAIL PROTECTED] To: Tony Wu [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Tuesday, February 19, 2002 8:38 PM Subject: Re: would you send me the mysql distribution which can support big5 On Tue, 19 Feb 2002 12:43:04 +0800 Tony Wu [EMAIL PROTECTED] wrote: Dear Sir, Would you send me the mysql distribution which can support big5, because our databases has too many big5 character! Please help! Your Sincerely, Tony Wu EDP Department Tel : 852-29435587 Sanyo Energy (HK) Co., Ltd. Hi! This list is dedicated to the discussions on the internal functioning of mysql server. For the questions, like the above, please write to [EMAIL PROTECTED] -- Regards, -- For technical support contracts, go to https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Sinisa Milivojevic [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, FullTime Developer /_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: seeking partner
And YOU are part of the problem - every spam is backed up by one of your complaints, doubling the traffic. If it really bothers you that much then set up a filter rule (killfile) for the persistent spammers, or risk being killfiled yourself - you have become a spammer. Can't you take your petitions to the list admins privately? T. . - Original Message - From: Marjolein Katsma [EMAIL PROTECTED] To: MySQL [EMAIL PROTECTED]; MySQL [EMAIL PROTECTED] Sent: Monday, January 28, 2002 11:47 AM Subject: Re: seeking partner Of course, the SAME spam we saw before appears again. No surprise. Does anyone still believe this will go away by itself? It won't - it will only get worse - unless the list administrators actually *do* something about it! The filters DO NOT WORK. At 17:05 2002-01-28, you wrote: TF Information Exchange (TF) Physical Address : Room 210, Building 2, Chegongzhuang Street No. 6, Xicheng District, Beijing, China Post Code: 100044 Tel: +86-10-6800-3112 Fax: +86-10-6800-1452 Web site: http://www.tangfeng.org E-mail: [EMAIL PROTECTED] [EMAIL PROTECTED] [EMAIL PROTECTED] Grrr: database,sql,query,table -- Marjolein Katsma HomeSite Help - http://hshelp.com/ - Extensions, Tips and Tools The Bookstore - http://books.hshelp.com/ - Books for webmasters and webrookies - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: [OT] Re: Understanding throughput with JDBC
Rownum exists of a sort with the limit clause.
Rowid does not exist all at.
Tony
- Original Message -
From: Emmanuel van der Meulen [EMAIL PROTECTED]
To: MySQL Java List [EMAIL PROTECTED]; MySQL General List
[EMAIL PROTECTED]; Nick [EMAIL PROTECTED]
Sent: Sunday, January 20, 2002 12:46 PM
Subject: RE: [OT] Re: Understanding throughput with JDBC
Hello Nick,
Does ROWNUM exist in MySQL?
Kind reagrds
Emmanuel
-Original Message-
From: Nick [mailto:[EMAIL PROTECTED]]
Sent: 19 January 2002 20:41
To: Shankar Unni
Cc: [EMAIL PROTECTED]
Subject: [OT] Re: Understanding throughput with JDBC
With Oracle, the *only* solution is to issue a query for the whole
resultset and scroll through it - it doesn't support anything
*like* the
LIMIT concept, though I suppose you *could* imitate it with
WHERE ROWNUM
blah.
With Oracle, you can retrieve a part of a result set by using inline
views:
SQL select d.c1,d.c2,d.c3
2 from (select c1,c2,c3,rownum c4 from foo order by c3) d
3 where d.c4 =15 and d.c4 = 19
4 /
C1 C2 C3
115 30 15
116 32 16
117 34 17
118 36 18
119 38 19
Not as elegant as I would like, but it works.
--
Nick
Email: [EMAIL PROTECTED]
-
Please check http://www.mysql.com/Manual_chapter/manual_toc.html;
before
posting. To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, send a message to the address shown in the
List-Unsubscribe header of this message. If you cannot see it,
e-mail [EMAIL PROTECTED] instead.
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Porting from MS SQL to MySQL
- Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Friday, January 18, 2002 2:14 PM Subject: Re: Porting from MS SQL to MySQL Jeremy Zawodny wrote: On Fri, Jan 18, 2002 at 03:16:15PM +0200, Markus Lervik wrote: Hello all! We've requested a database from different companies, and specifically said we wanted MySQL or PostgreSQL because of the open source angle and we're a library. One company offered MS SQL as the platform and said that they can later on port it to MySQL. For this they wanted 18 000 euro. Now, what I want to know is, how easy is it to port a (fairly complicated) database from MS SQL to MySQL? It can't be work worth 18 000 euro, now can it? That's a bit strange. If the app is built with MySQL in mind, porting it should be very, very easy. But if they're going to build the app with MySQL in mind anyway, it doesn't make much sense to do so on a platform other than MySQL, does it? Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.41-max: up 15 days, processed 362,696,624 queries (268/sec. avg) I agree. If they are a seriuos company they should build it after the customers wishes i.e if you want mysql the company should build it with mysql. For 18,000 euro i could build the system myself:) My two cents /PM\ What about the customer who asks a car company to make the vehicle's tryes out of velvet? Would you go off in a huff if they refused and demand they do it? There are obviously issues here that we are not privy to; there *must* be logic behind the choice of SQLServer. Are they saying that mySQL isn't upto it? Porting a DB takes more 'than a couple of hours'. What about the written procedures, the security mappings, the back up and recovery procs, the fallback arrangements, the testing etc. If you think E18k is a lot then ask for a detailed task plan with effort; find out what they are asking you to pay for. The DB was described as 'fairly complicated' whatever that may mean. Perhaps - and we are all guesing - there are remote data issues, views, stored procs, java and god knows what else that all needs to be integrated. Bottom line when you get a quote is find out what they want to do task by task and then cut it down from there. Tony - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Porting from MS SQL to MySQL
- Original Message - From: [EMAIL PROTECTED] To: Tony Buckley [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Friday, January 18, 2002 3:34 PM Subject: Re: Porting from MS SQL to MySQL SNIPSNIP:) I agree. If they are a seriuos company they should build it after the customers wishes i.e if you want mysql the company should build it with mysql. For 18,000 euro i could build the system myself:) My two cents /PM\ What about the customer who asks a car company to make the vehicle's tryes out of velvet? Would you go off in a huff if they refused and demand they do it? There are obviously issues here that we are not privy to; there *must* be logic behind the choice of SQLServer. Are they saying that mySQL isn't upto it? Ah but if i say i want a mysql server and costs for it they shouldnt say we can do it in mssql I think they should give me an estimate on the costs for what i want then i can discuss how to get the cost down Unless they are saying they doubt that mySQL is upto, it so it's no good quoting. They may have a room full of SQLServer people twiddling their thumbs in which case I agree with you, they are not bucking for the customer here. On the other hand they have very real doubts that mySQL is upto the job; they may be wrong on this last point, but at least they are being honest in their beliefs. Who knows! Porting a DB takes more 'than a couple of hours'. What about the written procedures, the security mappings, the back up and recovery procs, the fallback arrangements, the testing etc. If you think E18k is a lot then ask for a detailed task plan with effort; find out what they are asking you to pay for. The DB was described as 'fairly complicated' whatever that may mean. Perhaps - and we are all guesing - there are remote data issues, views, stored procs, java and god knows what else that all needs to be integrated. Mysql has always filled my need (wich is with perl,java,php) Granted it would be better to know exactly what fairly complicated is Bottom line when you get a quote is find out what they want to do task by task and then cut it down from there. I have to agree with this but i dont think you should pay for getting a workdescription from them (ofcourse i dont know much about administrating papperwork i am a technichian) 100% agree. Planning, including bidding for a job, is an overhead. There should be no charge for this. IBM would disagree with us however :-) Tony - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Porting from MS SQL to MySQL
- Original Message - From: j.urban [EMAIL PROTECTED] To: Tony Buckley [EMAIL PROTECTED] Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Friday, January 18, 2002 4:07 PM Subject: Re: Porting from MS SQL to MySQL Porting a DB takes more 'than a couple of hours'. What about the written procedures, the security mappings, the back up and recovery procs, the fallback arrangements, the testing etc. Yes, porting a database that was written for MSSQL with no intention of porting can be a painful proposition. However, if you have control over how the system is developed, you can easily design the system to be compatible with EITHER MSSQL or MySQL (the differeces are well-documented). If you develop your system with porting in mind (ie the original post of they'll develop in SQLServer and port it to MySQL later) porting should not take more than a couple of hours. You simply choose appropriate datatypes and don't use MSSQL-specific extensions... I still don't agree with this. Yes you can ease the passage by considering all the issues up front but it is still not a trivial job for a database of any consequence. There is more to a database than a physical schema - what about all the administration procedures that sit around it, what about tuning the new physical implementation, what about reviewing the access paths and optimisation, what about the redevelopment of data loading scripts. As I have said in another post, it's futile arguing about it because we don't know enough about the technical situation let the business/political one. Are you seriously saying you could sit down in front a reasonably sized DB you had never seen before and understand all the business issues and pick it up and ship to a new RDBMS and platform, rewrite the document, replan what I have stated above, and get it back up and running in two hours? Perhaps I am getting too old and slow but it would take me longer :-) I am not saying it's a huge task to do any of this but whoever said, I could do it in a couple of hours, doesn't understand the background that led to a company quoting E18k; nor do any of us, and for anything other than a very very trivial system, two hours seems inadequate. This is an area that interests me, because I directly bid for work such as this, and when tendering you usually find the bloke down the road working out of his spare bedroom that thinks he can do it for a tenner over one day. The company requesting the work then thinks that everyone else is overinflating their prices so goes cheap and pays for it big time downstream. Cheapest and quickest is rarely best. On the flip side, nor is most expensive. Tricky world init. Tony - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Porting from MS SQL to MySQL
- Original Message - From: j.urban [EMAIL PROTECTED] To: Tony Buckley [EMAIL PROTECTED] Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Friday, January 18, 2002 4:10 PM Subject: Re: Porting from MS SQL to MySQL Unless they are saying they doubt that mySQL is upto, it so it's no good quoting. They may have a room full of SQLServer people twiddling their thumbs in which case I agree with you, they are not bucking for the customer here. On the other hand they have very real doubts that mySQL is upto the job; they may be wrong on this last point, but at least they are being honest in their beliefs. Who knows! This doesn't make much sense. If they're being honest and they believe MySQL can't handle it, why would they offer to port it to MySQL for 18,000 euro? I don't know. Perhaps they are offering not just to port to mySQL but redesign the bits that they believed made an impractical initial mySQL installation. Perhaps 'impractical' because of time and MSSQL would be faster. Perhaps impractical because the CEO is shagging the marketing manager of MSSQL and didn't want to upset her. We don't know!!! My point is that the originator of the question didn't seem to have a clear idea of what was being offered for E18k but that could be an entirely reasonable bid for the work and application required. Just because it looks like a 'big number' doesn't mean that isn't what it will cost. All of a bit of a futile argument really without knowing a lot more. Tony - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Spam - a possible cause ?
- Original Message - From: Marjolein Katsma [EMAIL PROTECTED] To: [EMAIL PROTECTED] [RegSoft/mysql] [EMAIL PROTECTED]; MySQL [EMAIL PROTECTED] Sent: Saturday, January 12, 2002 12:44 PM Subject: RE: Spam - a possible cause ? Matthew, snip Spam is people not interested in MySQL at all posting a Business opportunity (Dear mysql, Join today for FREE and a chance to win a $100 Shopping Spree at The DHS Club Outlet Center!) or Golden Investment Opportunity or Great Growth Potential stock scams. Marjolein, They sound like great opportunities. Do you have any more info? Tony - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: user / pwd access to db driven website
Why don't you forget about the automated subscription service and use the phone or email to get the money in. Once you have your 5-10+ subscriptions up and running then you can upgrade properly and then get your shopping cart or whatever. Presumably you'll also want to use a different userID/password per user and track what they are doing so you'll need a completely new set of infrastructure tables and the backend code to maintain them. Another idea if your technical skills aren't yet upto the job would be to put the data onto CD and just send that out to the early customers; the data doesn't sound that volatile and web delivery may not be the best way. All the above would also give you a much earlier idea of whether this will be a success or not. Very few sites that ask for money for information are a success and if just five subscribers will bank roll you a new platform then your prices are clearly not a couple of pence per access. Good luck, Tony - Original Message - From: Investor [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Sunday, January 06, 2002 5:49 PM Subject: user / pwd access to db driven website HI all, I would be extremely appreciative with solution suggestions to the problem I am having. I really could use some help right now !! I am trying to get a database driven website up and running that has some amount of security on it (which i intend to make progresively better). It needs user/pwd access to a database of info, after a subscription fee is paid. Currently I am having trouble setting up the basic structure. By the way this is a starup company, and only 5- 10 subscriptions would fund much better software / hardware / server for the site. So my goal is to set up something inexpensive to get those 5 or so subscriptions. Attempt No. 1: I setup a test site ( virtual hosting I think it is called) that utilizes perl ms access. It works oK except I didn't get the user/pwd thing working. It has pwd protected directories - however they told me queries will not work with the pwd protected directory insalled. Technical support is very slow in responding. And I didn't figure out how to provide the needed security mentioned above. Currently I have been reading about mysql and just installed it on windows 98. I am open to changing over to linux based systems. However I would like to create the tempory site to get a few subsciptions prior to forking out the funds. So finally now that you guys know my situation can you please make some suggestions as to how to get the temporary site up ( to get 5 subscriptions or so) and I think I coudl painfully, over time, work on a site that could handle much more. Information: The database would be text only, less than 1 GB ! Right now I think it is at 5 MB but will increase. I am familar with perl 5.6 and ms access. Budget: Shoe string budget for now. :) I would greatly appreciate assistance I am sure many of you dealt with this type of situation before. = Regards, Investorclb __ Do You Yahoo!? Send FREE video emails in Yahoo! Mail! http://promo.yahoo.com/videomail/ - Please check http://www.mysql.com/Manual_chapter/manual_toc.html; before posting. To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, send a message to the address shown in the List-Unsubscribe header of this message. If you cannot see it, e-mail [EMAIL PROTECTED] instead. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Multiples instances of MySQL
See manual section 4.1.4. - Original Message - From: Emmanuel van der Meulen [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Sunday, December 30, 2001 9:47 AM Subject: Multiples instances of MySQL Hello all, Could someone please advise, what is the procedure to start and run more than one instance of MySQL? Kind regards Emmanuel - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Smalltalk and mySQL
Anyone have any experience of using mySQL within Smalltalk servers on a unix/linux platform? Any decent links? I am just exploring this now as I have heard there is now a Smalltalk mod for Apache. This sounds like a fun combination to play with! thanks, Tony - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL Searchable Mailing List Archive now up.
- Original Message - From: Robert Alexander [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, December 14, 2001 9:19 PM Subject: MySQL Searchable Mailing List Archive now up. So, being ahem 'between contracts' right now, I decided finally to write one. I've actually been keeping an archive of the MySQL list for quite a while in anticipation of doing this some day. Just doin' my bit... The archive is at http://archive.workmate.ca/myarchive Simply fabulous! Thanks! Tony - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: [OT] A News Group Perhaps.
Hi Matt, I am sure this has been said before so my apologies if I bore! Most mail clients let you setup rules. For the mySQL list I automatically redirect all messages into a separate folder based on the [EMAIL PROTECTED] email address. This creates a fabulous resource that can be searched (title and text) and can be read or ignored by choice. I am not a great fan of Newsgroups - they get spammed too much and messages get lost or archived. Some ISPs carry them, some don't. All too hit and miss for a resource on which I rely and am very grateful for. Tony - Original Message - From: Matthew Darcy [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, December 12, 2001 10:58 AM Subject: RE: A News Group Perhaps. Hi, I was wondering if the mysql list had any plans to be put onto a news group. I have only been a memeber a short time but I have found %50 of the information to be usefull. This does however mean that %50 is does not apply to me at this time. Due to this I get a lot of emails that are of no use to me at the moment. I would be keen to talk about hosting this list as a news group to make it browsable so myself and others could pick out infromation that is usefull to me at this time. The email list could stay for mass contributions but I would rather just browse for information I need. Any thoughts on this ? Thanks, Matt. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: From Oracle to the MySQL Database Server
Hi Arjen, - Original Message - From: Arjen G. Lentz [EMAIL PROTECTED] To: Tony Buckley [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Sunday, December 09, 2001 1:40 AM Subject: Re: From Oracle to the MySQL Database Server But eh, what are those gripes about transactions and locking? Are you aware that the MySQL server with the InnoDB table handler (since over a year now!) features full transactional capabilities including row level locking? You just need to grab a 3.23-Max version, or compile 3.23 from source, or any 4.0 version. For more info on the InnoDB table handler, see http://www.mysql.com/doc/I/n/InnoDB.html in the online manual. Yep, I do know about that and I was wrong not to mention it. It's just something that I haven't set up to use yet because I don't 'need' it as another level of complexity (at the moment). I love the idea of being able to choose whether I want it or not. For what I am doing at the moment I really don't need it - a few hanging FK's or missing children won't bother me as I am dealing with statistical analysis and as long as the failures are within tolerence than that's fine! It's just so fast compared to what I have been using! When I said 'gripes', I didn't mean mine - there are the usual knockers who probably haven't looked at page 1 of the manual let alone tried the system. I have come across experienced database people who get very snotty when they find out about transactional processing not being inherent in mySQL even though there are some very commercial systems out there that have the same approach to get the speed they need. People's attitudes (especially those with an Oracle background IMHO) are very hard to change. Thanks for adding to my misleading note. Are there any highend figures to estimate the performance hit by using InnoDB across the platforms? Tony - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: IN keyword supported?
Hi Bennett, - Original Message - From: Bennett Haselton [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Sunday, December 09, 2001 12:31 PM Subject: IN keyword supported? I'm trying to use the IN operator as described in my databases textbook from college. This query: select count(*) from user where user.ID in (select ID from user); is supposed to evaluate to count the number of rows in the 'user' table where the ID field is in the set of all ID field values in the 'user' table (in other words, just a roundabout way of counting the rows in the 'user' table, written solely to demonstrate the IN keyword). There doesn't seem to be a syntax error in that example, but MySQL gives the error: ERROR 1064: You have an error in your SQL syntax near 'select ID from user)' at line 1 Is the IN keyword not supported in MySQL, or am I using it wrong? Subqueries are not supported in mySQL. Have to rewrite it with a combination of outer joins to do the same thing. Have a look at section 1.7.4.1 in the manual. Tony - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Annoying article about MySql
snipped I used Oracle8i before (under very heavy load) and did not have any problems there. I am now using MySql under not so heavy load and have not yet had any problems with it, but just want to be prepared when and if they come along. Oracle 8i rel 3 enterprise edition - 28,000 pounds sterling. mySQL - freeish. Coming from an Oracle background, I started off hating mySQL. So far, I have only 'played' with it, but now have enough confidence to start using it for real. It is super fast and my test loads (as much as I can throw at it from several machines/sources/threads) have yet to fail. Does the job very nicely. Yes there are the usual gripes about views, transactional processing, and locking, but if you know about these at the design stage they can be managed. I'm a fan! Tony - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL 3.23 Manual - PDF
Image you had a product and wanted to make a PDF manual available for your users. Where would you put it? Website, under documentation. BINGO! http://www.mysql.com/downloads/download.php?file=Downloads/Manual/manual.pdf Not sure why this MySQL list has so many questions and folks have to be trained to check the main website first. Heck it even says to RTFM in the trailer message on each of the posts...but people post Q's here first. I know. I was one. For 3 months. The truth is, the main website, bitbybit FAQ and Paul DuBois' MySQL book have an enormous wealth or well organized information. Not trying to bust your chops, I did it (and still do from time to time), just letting you know that there is a faster (and more complete) way to find answers. HTH, Kind regards, Tony On Wednesday 28 November 2001 01:27 pm, A. Clausen wrote: Is there still a PDF-formatted version of the MySQL 3.23 manual available? A. Clausen [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
inserting from one table into another
I thought the following SQL statement would work (both tables are the same schema): insert into table1 values ( select * from table2 ) ; ...or was I seeing magic posts in the wee hours of the morning? I didn't think that was a subselect, but maybe I am wrong there too. Any help would be greatly appreciated...as I comb thru bitbybit's FAQ regards, T - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: inserting from one table into another
...I hate when this happens, I found it. For those who didn't know: insert into table1 select table2.* from table2; I know, I should be column specifc to accomodate table schema changes later in life, but this was a test. T On Tuesday 27 November 2001 12:46 pm, Tony wrote: I thought the following SQL statement would work (both tables are the same schema): insert into table1 values ( select * from table2 ) ; ...or was I seeing magic posts in the wee hours of the morning? I didn't think that was a subselect, but maybe I am wrong there too. Any help would be greatly appreciated...as I comb thru bitbybit's FAQ regards, T - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: varchar in the foodchain
On Friday 09 November 2001 12:23 am, Steve Meyers wrote: Tables with variable length rows can get ugly if not optimized frequently. We had one which was taking 2.5 sec to do an indexed query, and when we optimized the table it dropped to .2 sec. But as long as you keep your tables optimized, variable length rows can be faster. The main efficiency concern, though, is the size of your indexes. MySQL keeps your indexes in memory, so smaller indexes are better. That is why it is best to use hash values when indexing long character strings (or even fairly short ones). A 32-bit hash value of a 20-character field takes 1/5 the space. That means five times as much key can be in memory at once. Hope that makes sense... Steve Meyers Makes great sense to me, many thanks. I have often seen varchar used (on many DBs), and often wondered if it was just an easy way out. Most of what I am doing are of transactional nature vs. large char storage. For example, email addy, I've made as varchar(40). However, in light of this thread, I'll just make them char(40) and keep my indices int and bigint. The space savings and additional watchdogging for optimization, in my case do not warrant varchar (IMO). Many thanks. :-) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
??Sourcing in sections of a database create file
Years ago I worked on an RDBMS that had a feature to allow execution of 'sections' of a command file, avoiding others. I am still ign scanning the docs, but have not found anything similar in MySQL. For example, in a database creation table one could enter (in MySQL format) the following: mysql source master_DB_creation(table_a, table_c, consistency_check); Where the file master_DB_creation file would contain: --- ?section table_a drop table if exists table_a; create table table_a ( fooint not null auto_increment, barchar(12), primary key (foo) ); ?section table_b drop table if exists table_b; create table table_b ( fooint not null auto_increment, namechar(12), ... ... yada ... yada ..yoda... ... phone char(12), primary key (foo) ); ?section table_c drop table if exists table_c; create table table_c ( fooint not null auto_increment, namechar(12), streetchar(24), city char(18), state char(2), zipchar(9), PIN int, primary key (foo, int) ); ?section consistency_check select some context from database to ensure consistency, etc.. --- Does anyone know whether this functionality exists in MySQL? Regards, Tony - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: varchar in the foodchain
On Thursday 08 November 2001 10:55 am, Paul DuBois wrote: At 10:00 AM -0500 11/8/01, Tony wrote: Does anyone know if putting (or grouping) varchar columns at the end of a table provides any performance improvements? My indices are all integers, but not have varchar columns in between several integer columns. You'll get a speed improvement only if all your columns are fixed length. Otherwise the table has variable length rows, no matter the placement of the variable length coluumns. So then is the real purpose of using varchars, to save disk space? ( I realize this is probably a general database question, just trying to learn). - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Web Hosting
You might want to try Hurricane Electric, I've been using them for over a year, MySQL, Telent, cgi, email account, DNS support starting at $10/month. http://www.he.net They've been great. Cheers, T On Tuesday 30 October 2001 11:44 pm, Joe Fan wrote: My current web hosting company (www.ehost.com) does not support MySql. Is there a way that I can install within my own CGI-bin library? If not, are there any other open-source RDBMS available that would work independently of hosting servic? Thanks, Joe Come visit http://www.joe-fan.com For the Fans, by the Fans ! - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Problem adding table
On Wednesday 31 October 2001 10:44 am, Richard W. Wood wrote: I've been trying to add the following table to my database, and I keep getting the error below. CREATE TABLE mod_userpage_data ( id INT(5) NOT NULL default '0', title VARCHAR(200) NULL, data LONGTEXT NULL, PRIMARY KEY (id) ); ERROR 1064 at line 1: You have an error in your SQL syntax near 'data LONGTEXT NULL, PRIMARY KEY (id) )' at line 4 Richard, Could you possibly have some strange characters in your source file (assuming that you are using one)? Maybe something that an editor left behind. I tried your example, verbatim and it worked on both an Alpha system and an Intel system running RedHat 7.1 and MySQL 3.23.38 (see below): Logging to file 'test.results' mysql use test; Database changed mysql create table mod_userpage_data ( - id INT(5) NOT NULL default '0', - title VARCHAR(200) NULL, - data LONGTEXT NULL, - PRIMARY KEY (id) - ); Query OK, 0 rows affected (0.00 sec) mysql show tables; +---+ | Tables_in_test| +---+ | mod_userpage_data | +---+ 1 row in set (0.00 sec) mysql describe mod_userpage_data; +---+--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-+---+ | id| int(5) | | PRI | 0 | | | title | varchar(200) | YES | | NULL| | | data | longtext | YES | | NULL| | +---+--+--+-+-+---+ 3 rows in set (0.00 sec) mysql quit mysqladmin Ver 8.20 Distrib 3.23.38, for redhat-linux-gnu on alpha Copyright (C) 2000 MySQL AB MySQL Finland AB TCX DataKonsult AB This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license Server version 3.23.38 Protocol version10 Connection Localhost via UNIX socket UNIX socket /var/lib/mysql/mysql.sock Uptime: 1 day 59 min 37 sec Threads: 2 Questions: 42 Slow queries: 0 Opens: 14 Flush tables: 1 Open tables: 3 Queries per second avg: 0.000 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MySQLfont Tools
On Wednesday 31 October 2001 12:00 pm, Schmidt, Allen J. wrote: I've been lurking here for a while and have been itching to ask this... Does anyone use mysqlfront to manage and work with MySQL on Windows? I love it and can not live without it now. My other favorite is Advanced Query Tool and the combination is fantastic! Any thoughts?? (and it does not beep at me...) Allen I do. Love it. I actual only use Windows as a requirement for a consulting job. I have MySQL installed on several machines (Alpha and Intel-based) and Linux as wellas NT. MySQLfront is a great tool for daily tasks, etc. I must confess to preferring the text-based approach, probably the habit of years of big DB work on Tandem systems, where GUI tools were (and are) few and far between. I have not heard of the Advanced Query Tool, which company makes it? Regards, Tony - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Autoincrement
If you have not obtained Paul DuBois's MySQL (New Riders pub, ISBN 0-7357-0921-1), you should consider it a great investment. For what it's worth, page 169 has a section on resequencing auto_increment columns that you might be able to apply to your database. Undoubtedly, there are pages on the online docs too, I just found his book, dog eared and easy to find things. HTH, Tony On Friday 26 October 2001 08:07 pm, Joe wrote: Is there a query I can run to change the next autoindex to the next highest integer in the column. I mean I have my userIDs, and they are autoincrementing. But if I have say 9 users delete 5 of users, and then want to compact the table so that the ids are all sequential. How can I make it so the next autoincrement starts from say 5 instead of ten? thanks I hope this makes sense - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Column data type conversion Q
Not sure if this post worked the first time (I never saw it come in), so a thousand pardons if this is a re-post. I come from the school of hard knocks, and so it seems my MySQL indoctrination is no exception. 2 million rows of 'varchar' data must be the least efficient type to sort by. I need to change a column data type from 'varchar(10)' to 'int' type. I created another table, with the changed data type on the one column. I thought I read that nested selects were not supported by MySQL, but is it possible to do something like this: insert into new_table values (select '__enter-some-sort-of-on-the-fly- data-type-conversion-on-a-column-or-two__' from old_table); Or, is there an ALTER TABLE command on the existing table. Surely, this would take a long time on a couple million rows. I am running MySQL 3.23.36 and RedHat 7.1 (2.4.3-12) Kind regards, Tony --botspeak: sql, query, database - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Uneven replication nodes??
No updates to the master is the rule, our primary key is actually based on a timestamp unless we can figure an auto_increment bigint (yes, expecting an obscene amount of transactions) as the key. I think the bigint auto_increment might be a better option, certainly easier to use in a select as the key on one of the slaves than a pesky timestamp--er, I think. Right now it is timestamp. Thanks for the thoughts guys, I'll be sure to provide feedback on the results if you are interested. Regards, Tony On Thursday 25 October 2001 12:45 pm, Kyle Hayes wrote: On Wednesday 24 October 2001 23:09, Jeremy Zawodny wrote: On Wed, Oct 24, 2001 at 06:25:58AM -0400, Tony wrote: I am looking for a creative way to optimize my web application (largely reads) with a transaction firehose on the data input side. Batch vs. OLTP, if you will. Is there any technical reason why I could not configure the master database without indices and the slave(s) _with_ indices? The intent being to isolate the inbound transactions from the reads _and_ make the database inserts as simple as possible (e.g. few or no indices). That ought to work. Unless you have _any_ UPDATEs. Then, it'll slow to a crawl. MySQL is really fast if you've got the right indexes. It isn't much faster than grep on a big text file if you don't :-) If all you have are INSERTs and you can prove that they will never try to overwrite old data on the slaves, you should be fine. This is a pretty serious constraint though. Several slaves could be configured to throttle performance on the web side (more reads, move slaves, etc.), which could gain a performance edge by indexing. For disaster recovery of the master, one slave could be untouched and not indexed. I'd be interested to hear comments. That's an interesting idea that I hadn't considered before. I'd be worried about maintaining the strict completely new INSERTs only policy over time. If you are running stock or measurement data into it where it is guaranteed that it is always new INSERTs, you should be OK. Best, Kyle bot fodder: database, query, mysql, lions and tigers and bears, oh my! - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Uneven replication nodes??
I am looking for a creative way to optimize my web application (largely reads) with a transaction firehose on the data input side. Batch vs. OLTP, if you will. Is there any technical reason why I could not configure the master database without indices and the slave(s) _with_ indices? The intent being to isolate the inbound transactions from the reads _and_ make the database inserts as simple as possible (e.g. few or no indices). Several slaves could be configured to throttle performance on the web side (more reads, move slaves, etc.), which could gain a performance edge by indexing. For disaster recovery of the master, one slave could be untouched and not indexed. I'd be interested to hear comments. Thanks in advance, /Tony - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: How to copy table structure easily.
On Monday 22 October 2001 09:55 am, you wrote: Just want to copy the table structure of a table i Have but dont have the the create statement and dont want to have to type it again , it has a lot of fields. Can I easily replicate a table. Dexter, You can use Mysqldump to produce just the create table text (using the '-d' option). If you omit the '-d' option, it will also produce an 'insert' statement for each row in the DB. Obvisously, this can get quite huge depending on the amount of data in your DB. [tony@hoosier tony]$ mysqldump -p test -d Enter password: # MySQL dump 8.13 # # Host: localhostDatabase: test # # Server version3.23.36 # # Table structure for table 'member' # CREATE TABLE member ( ID int(11) NOT NULL auto_increment, last_name varchar(20) NOT NULL default '', first_name varchar(20) NOT NULL default '', suffix varchar(5) default NULL, expiration date default '-00-00', email varchar(100) default NULL, street varchar(50) default NULL, city varchar(50) default NULL, state char(2) default NULL, zip varchar(10) default NULL, phone varchar(20) default NULL, interests varchar(255) default NULL, PRIMARY KEY (ID,last_name), KEY index_1 (last_name), KEY index_2 (zip) ) TYPE=MyISAM; [tony@hoosier tony]$ If you want to duplicate the DB, there are a variety of methods, I'd refer to Paul DuBois's MySQL book. Enormously valuable book. HTH, Tony _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Oracle conversion...
Is there a quick and easy way to determine the maximum used length of a varchar column? I've built and imported an Oracle DB into a MySQL one and now I'd like to greatly reduce the number of varchars that were being used in the original (e.g. Oracle) DB on my MySQL version. I am digging into the manual, but time savings would be greatly appreciated. Regards, Tony - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Oracle conversion...
Many thanks. Worked fine. Sadly, almost every one of the varchar columns I checked return near the max length. Is there still a huge performance penalty, if most of the rows come close to hitting the max of the defined varchar length? I suppose it depends on your data, and how you use it. Just curious to hear some opinions. On Monday 22 October 2001 05:42 pm, Moshe Gurvich wrote: select max(length(column)) from table -Original Message- From: Tony [mailto:[EMAIL PROTECTED]] Sent: Monday, October 22, 2001 2:36 PM To: [EMAIL PROTECTED] Subject: Oracle conversion... Is there a quick and easy way to determine the maximum used length of a varchar column? I've built and imported an Oracle DB into a MySQL one and now I'd like to greatly reduce the number of varchars that were being used in the original (e.g. Oracle) DB on my MySQL version. I am digging into the manual, but time savings would be greatly appreciated. Regards, Tony - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php --- Incoming mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.281 / Virus Database: 149 - Release Date: 9/18/2001 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.281 / Virus Database: 149 - Release Date: 9/18/2001 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Index spindle??
Thanks, that worked. However, it appears that each time I execute an alter table command (such as addng a new index), I'll have to repeat the process of creating the index, moving the file, then again defining the symlink. MySQL does not seem to respect the link with new DDL commands like that. Not the end of the world, just a bit of a surprise behavior to me. Again, thanks. On Saturday 20 October 2001 06:37 pm, you wrote: On Sat, Oct 20, 2001 at 05:54:28PM -0400, tony.icuc wrote: Would anyone know of a method to create/fake/move/configure and index on an alternate disk than the base table? I hail from a platform that allows specific disk specification for partition, index and alternate key files. Naturally, I referenced Paul DuBois' amazing book, to no avail. Just curious if this is possible with the standard MySQL 3.23.26. Create the index, move the .MYI file to where you'd like, and leave a symlink which points to it. MySQL will respect that. Jeremy - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Index spindle??
Would anyone know of a method to create/fake/move/configure and index on an alternate disk than the base table? I hail from a platform that allows specific disk specification for partition, index and alternate key files. Naturally, I referenced Paul DuBois' amazing book, to no avail. Just curious if this is possible with the standard MySQL 3.23.26. Thanks in advance. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Lost connection CRASH!!!!
Hi, MySQL 3.22.32 has worked well on my bsd system until recently when there are frequent crashes. I think this is related to an increase in table size. It happens episodically but can be consistently reproduced with a command such as this - mysql alter Table KeywordIndex add Index(MessageID); ERROR 2013: Lost connection to MySQL server during query mysql (KeywordIndex has 5 entries). I have increased the following System variables - --set-variable max_allowed_packet=67108864 --set-variable connect_timeout=60 --set-variable tmp_table_size=8388608 --set-variable table_cache=128 I have struggled with this for some time and any suggestions would be very much appreciated. Thanks, Tony Lembke - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
