Re: A question about mysql database backup.
Li, Lenny Yong Bo (Lenny) wrote: Now I am assigned to do the backup of a whole mysql database (version: 3.23.36). I want to make sure whether the following procedures are correct for database backup. 1. mysqldump -F --opt database_name database_name.backup In my opinion, you should generally add the -Q option which quotes table and row names. Otherwise, restoring data can be really hard in case of perhaps only one complicated row name... Regards, Fred -- Condense soup, not books! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Using IA64 build on Itanium running HPUX B.11.22
Hi I am linking my program (which works fine on a PA-RISC 32-bit) with libmysqlclient_r.a. The following error is reported. ld: Mismatched Data ABI. Expected None but found EF_IA_64_ABI64 in file libmysqlclient_r.a[libmysql.o] How do I resolve the error. Hope somebody can throw light on this. Thanks and regards, Goutham S Mohan - Do you Yahoo!? Yahoo! Mail SpamGuard - Read only the mail you want.
Transfering from access to MySQL?
Hello, Could someone help me with a tip on how I can convert my old Access DBs to MySQL? Regards /Jonas
RE: A question about mysql database backup.
Fred, Thanks for your quick response! Do you mean that only one step is enough for a backup mysqldump -F --opt --quote-names database_name database_name.backup? The second step is not needed at all. Am I right? I added the option -Q, it failed, but the --quote-names option works well. Maybe the version number is not high enough. Regards, Lenny Li == LSS-MI Development, RD, Lucent Qingdao [EMAIL PROTECTED] 86-532-8702000-5033 http://gdcsr60.gdc.lucent.com/~lennyli -Original Message- From: Frederic Wenzel [mailto:[EMAIL PROTECTED] Sent: Wednesday, February 25, 2004 4:13 PM To: Li, Lenny Yong Bo (Lenny) Cc: [EMAIL PROTECTED] Subject: Re: A question about mysql database backup. Li, Lenny Yong Bo (Lenny) wrote: Now I am assigned to do the backup of a whole mysql database (version: 3.23.36). I want to make sure whether the following procedures are correct for database backup. 1. mysqldump -F --opt database_name database_name.backup In my opinion, you should generally add the -Q option which quotes table and row names. Otherwise, restoring data can be really hard in case of perhaps only one complicated row name... Regards, Fred -- Condense soup, not books! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Transfering from access to MySQL?
Jonas Lindén wrote: Hello, Could someone help me with a tip on how I can convert my old Access DBs to MySQL? Hello Jonas, I managed to do this out of Access by exporting the tables to a MyODBC DSN. Please check the column types later - don't know if they are all converted correctly. Regards, Fred -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 4.0.X with 4.1.1
Andrey Kotrekhov [EMAIL PROTECTED] wrote: SQL Hi, All! Is 4.1.1 replication compatible with 4.0.x? I have problem to replicate data from 4.0.17 to 4.1.1 show slave status show all are OK perlicate position and the exec position are the same. But if I update data on master, slave is not update own data :( What replication options do you use? Check if queries are present in the master binary logs and in the relay logs on the slave. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
libmysqld and PHP
I was wondering if I can user libmysqld with PHP. If it's possible would someone post a simple exampe. TIA, David -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 4.0.X with 4.1.1
SQL Hi! Andrey Kotrekhov [EMAIL PROTECTED] wrote: SQL Hi, All! Is 4.1.1 replication compatible with 4.0.x? I have problem to replicate data from 4.0.17 to 4.1.1 show slave status show all are OK perlicate position and the exec position are the same. But if I update data on master, slave is not update own data :( What replication options do you use? Check if queries are present in the master binary logs and in the relay logs on the slave. I research the problem and have got some details. Now we have this theme of mysqld replication (with server ids) +-+ +--+-1| 4 | | ^-| | | 5-+ +--6 And when I update something server 1 query relicates to the servers 5,6,4 But when I update the same table in server 4 query replicates to the server 1 but doesn't replicate to the servers 5 and 6. Servers 1,4 has version 4.0.17. Servers 5,6 has version 4.1.1 Is it a bug? Can anybody repeat it? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] Best regards. ___ Andrey Kotrekhov [EMAIL PROTECTED] ISP Alkar Teleport . +380 562 34-00-44 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 4.0.X with 4.1.1
Andrey Kotrekhov [EMAIL PROTECTED] wrote: Andrey Kotrekhov [EMAIL PROTECTED] wrote: SQL Hi, All! Is 4.1.1 replication compatible with 4.0.x? I have problem to replicate data from 4.0.17 to 4.1.1 show slave status show all are OK perlicate position and the exec position are the same. But if I update data on master, slave is not update own data :( What replication options do you use? Check if queries are present in the master binary logs and in the relay l= ogs on the slave. I research the problem and have got some details. Now we have this theme of mysqld replication (with server ids) +-+ +--+-1| 4 | | ^-| | | 5-+ +--6 And when I update something server 1 query relicates to the servers 5,6,4 But when I update the same table in server 4 query replicates to the server 1 but doesn't replicate to the servers 5 and 6. Servers 1,4 has version 4.0.17. Servers 5,6 has version 4.1.1 Is it a bug? Can anybody repeat it? Did you run server 1 with --log-slave-updates option? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Center a table
hi ,
Can somebody help me tocenter this table
?
?php/* Connecting, selecting database
*/$link = mysql_connect("localhost", "root", "z")
or die("Could not connect : " . mysql_error());#echo "Connected
successfully";mysql_select_db("abcd") or die("Could not select
database");
/* Performing SQL query */$query = "SELECT *
FROM ServiceStatus";$result = mysql_query($query) or die("Query failed : " .
mysql_error());
/* Printing results in HTML */print "table
border=\"1\ cellpadding=\"5\" cellspacing=\"0\"\n";while ($line =
mysql_fetch_array($result, MYSQL_ASSOC)) { echo
"\ttr\n"; foreach ($line as $col_value)
{ echo
"\t\ttd$col_value/td\n";
} echo "\t/tr\n";}echo
"/table\n";
/* Free resultset
*/mysql_free_result($result);
/* Closing connection
*/mysql_close($link);?
Thanx in advance
curlys
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Center a table
I think that
print table border=\1\ cellpadding=\5\ cellspacing=\0\
align=\center\\n;
or
print centertable border=\1\ cellpadding=\5\ cellspacing=\0\
align=\center\/center\n;
should be enough...
Jiri Matejka
==Wednesday, February 25, 2004 11:26 AM [EMAIL PROTECTED] wrote:
hi ,
Can somebody help me to center this table ?
?php
/* Connecting, selecting database */
$link = mysql_connect(localhost, root, z)
or die(Could not connect : . mysql_error());
#echo Connected successfully;
mysql_select_db(abcd) or die(Could not select database);
/* Performing SQL query */
$query = SELECT * FROM ServiceStatus;
$result = mysql_query($query) or die(Query failed : .
mysql_error());
/* Printing results in HTML */
print table border=\1\ cellpadding=\5\ cellspacing=\0\\n;
while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
echo \ttr\n;
foreach ($line as $col_value) {
echo \t\ttd$col_value/td\n;
}
echo \t/tr\n;
}
echo /table\n;
/* Free resultset */
mysql_free_result($result);
/* Closing connection */
mysql_close($link);
Thanx in advance
curlys
--
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]
error 07002:SQLBindParameter not used for all parameter
Hi, I have an application working in Windows, developed in VB5 using RDO method to access data. I use a parametrised query that is correctly working on e PC with Office97 installed. When is installed Office2000 I receive the error: 07002:[MYSQL][ODBC 3.51 Driver] [mysqld-3.23.53 max] SQLBindParameter not used for all parameter The incriminated procedure is: Dim Qy as New RdoQuery Dim Rs as RdoResultSet With Qy Set .ActiveConnection = Cns .SQL = select FLD1,FLD2,FLD3 from TABLE1 WHERE FLD1 =? .RowsetSize = 1 .rdoParameters(0).Name = FLD1 .rdoParameters(0) = Null .rdoParameters(0).Type = rdTypeCHAR SET Rs= .OpenResultset(RdOpenKeySet,RdConcurRowVer) ' 1 End With The error rise executing line 1 Thank Enzo Baldo Enzo Baldo __ Tiscali ADSL SENZA CANONE: Attivazione GRATIS, contributo adesione GRATIS, modem GRATIS, 50 ore di navigazione GRATIS. ABBONARTI TI COSTA SOLO UN CLICK! http://point.tiscali.it/adsl/index.shtml -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 4.0.X with 4.1.1
. What replication options do you use? Check if queries are present in the master binary logs and in the relay l= ogs on the slave. I research the problem and have got some details. Now we have this theme of mysqld replication (with server ids) +-+ +--+-1| 4 | | ^-| | | 5-+ +--6 And when I update something server 1 query relicates to the servers 5,6,4 But when I update the same table in server 4 query replicates to the server 1 but doesn't replicate to the servers 5 and 6. Servers 1,4 has version 4.0.17. Servers 5,6 has version 4.1.1 Is it a bug? Can anybody repeat it? Did you run server 1 with --log-slave-updates option? No. But I can restart mysqld with this option. Should I do this? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] Best regards. ___ Andrey Kotrekhov [EMAIL PROTECTED] ISP Alkar Teleport . +380 562 34-00-44 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Center a table
? ... ... /* Performing SQL query */ $query = SELECT * FROM ServiceStatus; $result = mysql_query($query) or die(Query failed : . mysql_error()); ? !-- Printing results in HTML catering for all browsers -- div style=text-align:center; table border=1 cellpadding=5 cellspacing=0 style=margin:auto; ? . . Louise - Original Message - From: CurlyBraces Technologies ( Pvt ) Ltd To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Wednesday, February 25, 2004 8:26 PM Subject: Center a table hi , Can somebody help me to center this table ?
uppercase field constraints
can i create a constraint to a field so that every data entered to that field (string) be stored in uppercase, if is posible can anybody give me an example or point me to a link with documentation. Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: uppercase field constraints
Hi Ricardo, can i create a constraint to a field so that every data entered to that field (string) be stored in uppercase, if is posible can anybody give me an example or point me to a link with documentation. MySQL doesn't support check constraints. With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help is appreciated
I am running Delphi 6 Ent. along with Corelab
technology to access MySQL 4.1.14.
We run into problem of table coruption (error: 127,
145) from time to time using the following SQLs. This
is done in the transaction context. Most of the time
it works fine but it does get into problem. We have
tested it with simple insert and it seems to be
working fine.
We really appreciate your input.
{SQL}
Delete From Table1
INSERT INTO Table1 (Field1, Field2) SELECT a.Field1,
'2004-02-25' From Table2 a LEFT Join Table3 b ON
(a.Field1 = b.Field2)
where a.Field4 = '0' and a.Field5 = '0' and a.Field6
= '2004-02-25'
and a.Field7 Like 'ABC%' and b.Field1 = 'FF'
regards
___
Yahoo! Messenger - Communicate instantly...Ping
your friends today! Download Messenger Now
http://uk.messenger.yahoo.com/download/index.html
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 4.0.X with 4.1.1
What replication options do you use? Check if queries are present in the master binary logs and in the relay l= ogs on the slave. I research the problem and have got some details. Now we have this theme of mysqld replication (with server ids) +-+ +--+-1| 4 | | ^-| | | 5-+ +--6 And when I update something server 1 query relicates to the servers 5,6,4 But when I update the same table in server 4 query replicates to the server 1 but doesn't replicate to the servers 5 and 6. Servers 1,4 has version 4.0.17. Servers 5,6 has version 4.1.1 Is it a bug? Can anybody repeat it? Did you run server 1 with --log-slave-updates option? No. But I can restart mysqld with this option. Should I do this? Thank you very much. Problem has been resolved -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] Best regards. ___ Andrey Kotrekhov [EMAIL PROTECTED] ISP Alkar Teleport . +380 562 34-00-44 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] Best regards. ___ Andrey Kotrekhov [EMAIL PROTECTED] ISP Alkar Teleport . +380 562 34-00-44 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 4.0.X with 4.1.1
Andrey Kotrekhov [EMAIL PROTECTED] wrote: What replication options do you use? Check if queries are present in the master binary logs and in the rela= y l=3D ogs on the slave. I research the problem and have got some details. Now we have this theme of mysqld replication (with server ids) +-+ +--+-1| 4 | | ^-| | | 5-+ +--6 And when I update something server 1 query relicates to the servers 5,6,4 But when I update the same table in server 4 query replicates to the server 1 but doesn't replicate to the servers 5 and 6. Servers 1,4 has version 4.0.17. Servers 5,6 has version 4.1.1 Is it a bug? Can anybody repeat it? Did you run server 1 with --log-slave-updates option? No. But I can restart mysqld with this option. Should I do this? Yes. You should start MySQL server with above option if you set up chain replication servers: http://www.mysql.com/doc/en/Replication_Options.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Disabling logging for connection
Hi! I have a problem with logging. I am logging all queries to MySQL server. And I have a program which inserts large chunks of data. The problem is that I would like to log all queries except for those insert queries as those flood my logs (they get really very big). The program is written in Perl. But I would like to know if MySQL API supports something like that? Is there some workaround? So is there a way to say that you do not want this connection (I would set this in that program with inserts queries) to be logged - or this query? Is there some other solution? I just do not want those insert gueries to be logged. Best regards and please help Mike __ Do you Yahoo!? Yahoo! Mail SpamGuard - Read only the mail you want. http://antispam.yahoo.com/tools -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
max_user_connections -- help
Hi, One of our enterprise database servers (MySQL 4.0.16-max on Solaris 9) recently refused connections with a 1203 error, User [username] has already more than 'max_user_connections' active connections. Show variables like '%conn%' displayed: +--+---+ | Variable_name| Value | +--+---+ | connect_timeout | 20| | max_connections | 3000 | | max_connect_errors | 10| | max_user_connections | 1800 | +--+---+ ... and show status like '%conn%' displayed: +--+-+ | Variable_name| Value | +--+-+ | Aborted_connects | 3174| | Connections | 6058425 | | Max_used_connections | 1771| | Threads_connected| 4 | +--+-+ Setting max_user_connections to 0 (zero), which according to the manual sets the variable to unlimited, allowed *no* connections. I upped max_user_connections to a higher value, but I don't understand why MySQL refused connections when there were no more than 4 threads connected to the database at the time the connection was refused. Is this a known problem? Is there a solution? - ian -- | Ian SalesUnmetered ADSL solutions | database administrator for Home Business | PlusNet Technologies Ltd. @ http://www.plus.net + - My Referrals - It pays to recommend PlusNet - --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.516 / Virus Database: 313 - Release Date: 01/09/2003 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Strange behaviour restoring from 3.x to 4.0
I have a 3.x database that I backed up and restored to a 4.0 server. All worked fine, except for one issue: One table holds addresses, and when restoring it, would fail with an unmatched quotes message. On further examination, I found that it would occur on entries where the text was like 123 Main St, #301, but would work fine on entries like 123 Main St, #22. The obvious difference being that the apartment number is either less than or greater than 256. Any ideas on why this quirk occurs, and how to get around it? As a temporary measure I escape quoted all the hash signs in an address, and it worked fine. But why did it happen in the first place? Is there something I have to do on the dump?
RE: MAX_ROWS
Ok I think I am a bit confused on how the MAX_ROWS works... This is the result of SHOW TABLE STATUS \G: Name: gabe_test Type: MyISAM Row_format: Fixed Rows: 33 Avg_row_length: 5 Data_length: 165 Max_data_length: 327679 Index_length: 1024 Data_free: 0 Auto_increment: NULL Create_time: 2004-02-24 14:34:30 Update_time: 2004-02-24 14:37:43 Check_time: NULL Create_options: max_rows=3 Comment: What values of MAX_ROWS and AVG_ROW_LENGTH would I need so that I could limit this table to 3 [or n] number of records? How do I calculate this? Additionally, is there a better way, not using the OS, to limit the size of MyISAM tables? Thanks Gabe -Original Message- From: Keith C. Ivey [mailto:[EMAIL PROTECTED] Sent: Tuesday, February 24, 2004 5:11 PM To: [EMAIL PROTECTED] Subject: Re: MAX_ROWS On 24 Feb 2004 at 22:01, Alison W wrote: Yes: MAX_ROWS is a *guidance* to the system in setting up the table and not a *limit* in any way. Well, it is a limit in one way. MySQL uses it (in MyISAM tables) to calculate the size of the pointer used for positions within the data file. If the data file becomes larger than can be handled by that size of pointer, then you can't add any more records to the table (unless you increase MAX_ROWS or AVG_ROW_LENGTH so that the pointer size is increased). -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org -- 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: Transfering from access to MySQL?
I use SQLyog - http://www.webyog.com/sqlyog It has a pretty good ODBC Import Wizard. Karam --- Jonas_Lindén [EMAIL PROTECTED] wrote: Hello, Could someone help me with a tip on how I can convert my old Access DBs to MySQL? Regards /Jonas __ Do you Yahoo!? Yahoo! Mail SpamGuard - Read only the mail you want. http://antispam.yahoo.com/tools -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Pre-loading index file
Eric B. [EMAIL PROTECTED] wrote: I know that in 4.11+ you can preload your MYI index file into the key cache using LOAD INDEX (http://www.mysql.com/doc/en/Index_preloading.html), but is there a way to configure MySQL to do this whenever the MySQL service starts? I'm willing to write .sql batch statements, but is there someway to configure the ini or the cnf file to execute a sequence of .sql statements upon startup? What about --init-file option of mysqld? http://www.mysql.com/doc/en/Server_options.html Secondly, is there any way to determine the amount of space an entire index takes? Or how much space the non-leaf nodes in the index takes? No, you can't. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Disabling logging for connection
Mike Mimic [EMAIL PROTECTED] wrote: Hi! I have a problem with logging. I am logging all queries to MySQL server. And I have a program which inserts large chunks of data. The problem is that I would like to log all queries except for those insert queries as those flood my logs (they get really very big). The program is written in Perl. But I would like to know if MySQL API supports something like that? Is there some workaround? So is there a way to say that you do not want this connection (I would set this in that program with inserts queries) to be logged - or this query? Is there some other solution? I just do not want those insert gueries to be logged. If you execute SET SQL_LOG_OFF=1, no logging will be done for this client: http://www.mysql.com/doc/en/SET_OPTION.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Error 2013 when using mysql client, Version 12.21
Hi! When i'm using mysql client, version 12.21 - Distrib: 4.0.15 / windows, for queries or loading data, i always get an error 2013 (lost connection...) after 30 seconds and the connection breaks down. This doesn't happen, when i use the older version 11.16 , Distrib: 4.0.0 / windows. There is no parameter set for mysql client in our my.ini. Does anybody know the reason? Best regards, Kai Bungarz Wissenschaftliches Institut der AOK (WIdO) Kortrijker Straße 1 53177 Bonn Tel.: 0228/843-142 (Sekr.: -393) Fax: 0228/843-144 Email: [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INSERT returning ID
Look into the mysql_insert_id function, that looks like what you want. On Feb 24, 2004, at 12:02 PM, David Scott wrote: Hi list peeps In many of my projects I have the need to insert a new record into a table and then later on in the script update this record, Right now I am doing this by doing the insert, then immediately doing a Select to return the latest id (unique id) which I store later for my update. Is there any way I can do an insert and have it return the unique auto-assigned id? Cheers Dave -- 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]
Re: Error 2013 when using mysql client, Version 12.21
Bungarz, Kai [EMAIL PROTECTED] wrote: When i'm using mysql client, version 12.21 - Distrib: 4.0.15 / windows, for queries or loading data, i always get an error 2013 (lost connection...)=20 after 30 seconds and the connection breaks down. This doesn't happen, when i use the older version 11.16 , Distrib: = 4.0.0 / windows. There is no parameter set for mysql client in our my.ini.=20 Does anybody know the reason? There was a bug in Windows client 4.0.15 that caused Lost connection error. You should upgrade client program. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Nested queries
This is the query i want to run but i doesn't work. select id from c_table where users_id in (select id from users wherelocations_id=3) order by data_ora But it gives me this error: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'select id from users where locations_id=3) order by data_ora It seems correct to me. What's the problem? Gregorio ___ IncrediMail - il mondo della posta elettronica si è finalmente evoluto - Clicca Qui
trouble with C API Prepared statements.
Hai MySQLians, I have listed down the log details which shows the mysql servers normal shutdown and improper shutdown while it got trouble from C coding. Log details from c:\mysql\data\mysql.err MySql: ready for connections. Version: '5.0.0-alpha-max-debug' socket: '' port: 3306 Error: Freeing unallocated data at line 181, 'C:\build500\build\mysql-5.0.0-alpha\mysys\my_alloc.c' 040224 20:40:01 MySql: Normal shutdown MySql: ready for connections. Version: '5.0.0-alpha-max-debug' socket: '' port: 3306 040225 10:35:20 MySql: Normal shutdown 040225 10:35:21 InnoDB: Starting shutdown... 040225 10:35:23 InnoDB: Shutdown completed; log sequence number 0 43634 040225 10:35:24 MySql: Shutdown Complete MySql: ready for connections. Version: '5.0.0-alpha-max-debug' socket: '' port: 3306 Error: Freeing unallocated data at line 181, 'C:\build500\build\mysql-5.0.0-alpha\mysys\my_alloc.c' Note that my machine dont have this directory structure and file as 'C:\build500\build\mysql-5.0.0-alpha\mysys\my_alloc.c' I have quote these things into MySQL mailing list and waiting for the reply After that I have created sample COBOL coding to work with MySQL prepared statements. Error obtained from mysql_error() inside COBOL coding as; *Can't open shared memory. Request event don't create (2)* This error obtained once the execution crossed the mysql_bind_param() function and lead to mysql_error(). Anyhow after restart the server i can able to see the data i intented to insert. I want to clarify is this related to memory allocation (or) OS un supporting? thanks Arun. Yahoo! India Insurance Special: Be informed on the best policies, services, tools and more. Go to: http://in.insurance.yahoo.com/licspecial/index.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fetch data and search on different tables.
Hi,
I have a question regarding to search with fulltext on table and fetch
the data from another.
Table one:
id, textid, name, number, url
Table two:
id, text
On table two there is a fulltext index.
These two tables recently was one table, but i had to split them due to
the amount of data.
textid in table one is refering to id in table two, in order to keep
person and text together.
Before the split my search question looked like this:
select id,name,number,url match(text)
against('$searchString') as relevance from table where
match(text) against('$searchString' IN BOOLEAN MODE)
having relevance 0.9 order by relevance DESC
But due to the split of the table i cant really figure out how to make
the question.
I want to search with fulltext on table2(text) and fetch all the
corresponding data from table1 where textid=(table2.id).
Any tips?
Best regards
// Fredrik Carlsson
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Nested queries
Gregorio [EMAIL PROTECTED] wrote: This is the query i want to run but i doesn't work. select id from c_table where users_id in (select id from users where locations_id=3) order by data_ora But it gives me this error: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'select id from users where locations_id=3) order by data_ora It seems correct to me. What's the problem? Subqueries are supported in MySQL since version 4.1. In earlier versions you can rewrite query without a subquery: SELECT c_table.id FROM c_table, users WHERE c_table.user_id=users.id AND user.locations_id=3 ORDER BY c_table.data_ora; http://www.mysql.com/doc/en/Rewriting_subqueries.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: inserting huge string
Have you changed the interactive_timeout and wait_timeout values? Original Message On 2/24/04, 4:54:57 PM, [EMAIL PROTECTED] wrote regarding inserting huge string: hi, I am using mysql 4.0.14. I have a table where one of the fields has a datatype longblob. What I am trying to do is to enter a genome sequence which has around 175 characters. the file size is about 1.5MB which is well within the longblob size. I am using python to enter the sequence from a file to the database and the while i am running the python program ,the connection to the db is lost. So it takes in only around 103500 characters. I tried changing the variables in mysql,the max_allowed_packet using the following command: set max_allowed_packet=4; I tried changing it to max 4G which is the maximum size for longblob. But it changed to a maximum of 1G. Even is it is 1G, the size of the file i am trying to enter is well within the size of the datatype. I am not sure how to enter this huge string. What should I do?? thanks, liz -- 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: Nested queries
What MySQL version are you running? Original Message dated 2/25/04, 9:19:43 AM Author: Gregorio [EMAIL PROTECTED] Re: Nested queries: This is the query i want to run but i doesn't work. select id from c_table where users_id in (select id from users where locations_id=3) order by data_ora But it gives me this error: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'select id from users where locations_id=3) order by data_ora It seems correct to me. What's the problem? Gregorio ___ IncrediMail - il mondo della posta elettronica si è finalmente evoluto - Clicca Qui -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Nested queries
Gregorio [EMAIL PROTECTED] wrote on 25/02/2004 15:19:43: This is the query i want to run but i doesn't work. select id from c_table where users_id in (select id from users where locations_id=3) order by data_ora But it gives me this error: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'select id from users where locations_id=3) order by data_ora It seems correct to me. What's the problem? Which MySQL version are you using.? Subselects (which is what this is called) are only implemented from MySQL 4.1, which is still in beta. Before that, you have to work around using joins - see http://www.mysql.com/doc/en/Rewriting_subqueries.html. I think your query might be phrased as select id from c_table join users on c_table.users_id = users.id where users.location_id = 3 ; Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
disable an option but not take server down?
Greetings, Our MySQL server (4.0.13) is currently running with the option --skip-networking. We want the server to accept TCP/IP connections. If this option were specified in the config file, perhaps I could just modify the config file and HUP the server process. So there would be no server down time. But now it's a command line option, how can I disable it without stopping/starting the server? Any ideas? Thanks, Bing -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: uppercase field constraints
Think that is for your script to work on, i.e.
INSERT INTO table
(field1, field2)
VALUES (UCASE('form.field1'), form.field2)
depending on the syntax of your programming language (and which MySQL
version you are using). The above works on 4.1.1.
Terry
--Original Message-
can i create a constraint to a field so that every data entered to that
field (string) be stored in uppercase, if is posible can anybody give
me an
example or point me to a link with documentation.
Thanks.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Pre-loading index file
Thanks Victoria! I must have missed that option. Exactly what I was looking for! Eric Victoria Reznichenko [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Eric B. [EMAIL PROTECTED] wrote: I know that in 4.11+ you can preload your MYI index file into the key cache using LOAD INDEX (http://www.mysql.com/doc/en/Index_preloading.html), but is there a way to configure MySQL to do this whenever the MySQL service starts? I'm willing to write .sql batch statements, but is there someway to configure the ini or the cnf file to execute a sequence of .sql statements upon startup? What about --init-file option of mysqld? http://www.mysql.com/doc/en/Server_options.html Secondly, is there any way to determine the amount of space an entire index takes? Or how much space the non-leaf nodes in the index takes? No, you can't. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Count()
My program, NCR (Non-Conformity Report), keeps track of problems with items that are received from vendors. I am creating a report where the user can retrieve a list of vendors that have had a NCR written against them a certain number of times. For example, if the user wants to see what vendors have had more than 2 NCR's written against them. I need something like this, but it doesn't work. It gives me an error saying invalid use of group function. So I put Count(Subs.Vendor) 2 into a HAVING clause and it return only one vendor. I know there are more. SELECT Subs.Vendor FROM tblNCRLog INNER JOIN Subs ON tblNCRLog.SubID = Subs.SubNo WHERE ((tblNCRLog.CurrentDate '2002/10/1') AND Count(Subs.Vendor) 2); Does someone have any suggestions? Jacque
Re: Count()
Jacque Scott wrote: My program, NCR (Non-Conformity Report), keeps track of problems with items that are received from vendors. I am creating a report where the user can retrieve a list of vendors that have had a NCR written against them a certain number of times. For example, if the user wants to see what vendors have had more than 2 NCRs written against them. I need something like this, but it doesn't work. It gives me an error saying invalid use of group function. So I put Count(Subs.Vendor) 2 into a HAVING clause and it return only one vendor. I know there are more. SELECT Subs.Vendor FROM tblNCRLog INNER JOIN Subs ON tblNCRLog.SubID = Subs.SubNo WHERE ((tblNCRLog.CurrentDate '2002/10/1') AND Count(Subs.Vendor) 2); Does someone have any suggestions? Jacque You say you use HAVING, but it's not in your example query. Perhaps that's an oversight. But you're also missing a GROUP BY. Your join gets you one row per NCR per vendor. You need to group by vendor, then check the count with HAVING. I think you need something like: SELECT Subs.Vendor, COUNT(*) AS NCRs FROM Subs, tblNCRLog WHERE Subs.SubNo = tblNCRLog.SubID AND tblNCRLog.CurrentDate '2002/10/1' GROUP BY Subs.Vendor HAVING NCRs 2 That would give the vendors and the counts. If you really don't want to see the counts, so long as they're greater than 2, something like this should do: SELECT Subs.Vendor FROM Subs, tblNCRLog WHERE Subs.SubNo = tblNCRLog.SubID AND tblNCRLog.CurrentDate '2002/10/1' GROUP BY Subs.Vendor HAVING COUNT(*) 2 Hope that helps. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Count()
That works. I knew I was missing something simple. It was the GROUP BY and the HAVING together. Thank you very much. Michael Stassen [EMAIL PROTECTED] 2/25/2004 9:05:34 AM Jacque Scott wrote: My program, NCR (Non-Conformity Report), keeps track of problems with items that are received from vendors. I am creating a report where the user can retrieve a list of vendors that have had a NCR written against them a certain number of times. For example, if the user wants to see what vendors have had more than 2 NCRs written against them. I need something like this, but it doesn't work. It gives me an error saying invalid use of group function. So I put Count(Subs.Vendor) 2 into a HAVING clause and it return only one vendor. I know there are more. SELECT Subs.Vendor FROM tblNCRLog INNER JOIN Subs ON tblNCRLog.SubID = Subs.SubNo WHERE ((tblNCRLog.CurrentDate '2002/10/1') AND Count(Subs.Vendor) 2); Does someone have any suggestions? Jacque You say you use HAVING, but it's not in your example query. Perhaps that's an oversight. But you're also missing a GROUP BY. Your join gets you one row per NCR per vendor. You need to group by vendor, then check the count with HAVING. I think you need something like: SELECT Subs.Vendor, COUNT(*) AS NCRs FROM Subs, tblNCRLog WHERE Subs.SubNo = tblNCRLog.SubID AND tblNCRLog.CurrentDate '2002/10/1' GROUP BY Subs.Vendor HAVING NCRs 2 That would give the vendors and the counts. If you really don't want to see the counts, so long as they're greater than 2, something like this should do: SELECT Subs.Vendor FROM Subs, tblNCRLog WHERE Subs.SubNo = tblNCRLog.SubID AND tblNCRLog.CurrentDate '2002/10/1' GROUP BY Subs.Vendor HAVING COUNT(*) 2 Hope that helps. Michael
Setting SQL_BIG_SELECTS=1 for MySQL server
We've recently started using MySQL for customers with a large number of records on their systems. For the first time, one of our customers has gone over the 4 million record mark, and we're running into some problems with the MAX_JOIN_SIZE and the SQL_BIG_SELECTS. Using the control center, or command line option, we can successfully 'Set SQL_BIG_SELECTS=1' and solve the problem. So the question is, is there a way to set the server so that the default is SQL_BIG_SELECTS=1? Setting it as a variable in the my.cnf file doesn't seem to work, it causes the server to be unable to start. Is it something that needs to be set through the application accessing the database each time it opens a new connection? Or can we set it through the application globally? Forgive me if this is an obvious answer, I have spent a long time searching the documentation and other places online, but can't seem to find the solution. Thanks in advance for any assistance, Jen Jennifer Horne, Software Developer [EMAIL PROTECTED] Panda Voice Systems Inc. 1.888.767.2632 ext 23
count(*) in V4.0.18/V4.1
Hi, I have a strange problem in the return types of the query: select count(*) from table gives in MySQL Version 4.0.18 an return type of long. In Version 4.1.1 its an char (0). The query goes vi MyODBC to MySQL. I checked the ODBC Trace to see the different return types. Does anyone know about this problem? Are there any server variables to get the right result? I already checked the manual/bug/changes section of MySQL. But I did get any ideas. I hope somebody can help me. Thanks in advance. Mario -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
updating tables in MySQL
Hello, Sorry if this is a obvious question, but I am new to mySQL, and PHP. I am used to working with MS SQL Server 2000 and coldfusion. In SQL server I can build DTS packages and schedule them to update the data in my tables with different queries, and to import current data from other databases. Is there a way I can do this in mySQL? I found how I can schedule scripts, but am not sure how to go about making a script do queries, or how to import data from a other data source. I could write a script to insert each record one at a time, but that seems like a lot of work. I work with fairly large tables. I am new to both Linux, and mySQL, but do have a decent amount of experience in other SQL system. I am using RedHat 9, mySQL Ver 12.22 Distrib 4.0.18, for pc-linux (i686). Thanks in advance, Todd
RE: MAX_ROWS
On 25 Feb 2004 at 8:35, Tucker, Gabriel wrote: What values of MAX_ROWS and AVG_ROW_LENGTH would I need so that I could limit this table to 3 [or n] number of records? You can't. That's not what MAX_ROWS and AVG_ROW_LENGTH are for. They're only there to allow MySQL to decide how many bytes it needs to use for the pointers into the data file. Unless you're going to be using tables bigger than 4 GB, there's probably no reason for you to worry about those values at all. You can indirectly limit the number of records in a table to 127 or 255 or 32,767 or 65,535 or ... by using an AUTO_INCREMENT primary key that's a TINYINT or UNSIGNED TINYINT or SMALLINT or UNSIGNED SMALLINT ..., but I don't think that's going to help you either. -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problem with indexes
So I'm having issues with indexes in mysql 4.0 I issue the following: Create Index Index1 on Table1 (ParentID,ClassID,Amount,Memo(20)); That works fine. However I'm running into problems when I'm trying to insert into this table using INSERT I'm receiving errors about duplicate keys against this Index. I thought this Index should be non-unique since I didn't specify the UNIQUE keyword. This table does not have a Primary Key either. Kinda need help ASAP, so any ideas would be appreciated. Thanks. -- James Lamanna -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query Problems
I am doing a very simple query joining two copies of tables with identical structures but different data. We are running MySQL 4.1.1. The tables each have about 24,000 lines of data in them. For some reason this query, which is a simple join between the two tables is taking 8 minutes to run. The query looks something like this: Select * FROM Table1 as a INNER JOIN Table2 as b ON (a.ID = b.ID or (a.Field1 = b.Field1 and a.Field2 = b.Field2)) WHERE bla bla bla We have tried to set up indexes and the query still takes 8 minutes to run. It only returns 6,000 records. Does anyone have any idea what might be the problem and what we can do to fix it? Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query Problems
I am doing a very simple query joining two copies of tables with identical structures but different data. We are running MySQL 4.1.1. The tables each have about 24,000 lines of data in them. For some reason this query, which is a simple join between the two tables is taking 8 minutes to run. The query looks something like this: Select * FROM Table1 as a INNER JOIN Table2 as b ON (a.ID = b.ID or (a.Field1 = b.Field1 and a.Field2 = b.Field2)) WHERE bla bla bla We have tried to set up indexes and the query still takes 8 minutes to run. It only returns 6,000 records. Does anyone have any idea what might be the problem and what we can do to fix it? Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Query Problems
What does the explain look like? -Original Message- From: Eric Scuccimarra [mailto:[EMAIL PROTECTED] Sent: Wednesday, February 25, 2004 1:03 PM To: [EMAIL PROTECTED] Subject: Query Problems I am doing a very simple query joining two copies of tables with identical structures but different data. We are running MySQL 4.1.1. The tables each have about 24,000 lines of data in them. For some reason this query, which is a simple join between the two tables is taking 8 minutes to run. The query looks something like this: Select * FROMTable1 as a INNER JOIN Table2 as b ON (a.ID = b.ID or (a.Field1 = b.Field1 and a.Field2 = b.Field2)) WHERE bla bla bla We have tried to set up indexes and the query still takes 8 minutes to run. It only returns 6,000 records. Does anyone have any idea what might be the problem and what we can do to fix it? Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Problems
Do you have separate indexes on: Table1.ID Table2.ID Table1.Field1 Table2.Field1 Table1.Field1 Table1.Field2 Select* FROM Table1 as a INNER JOIN Table2 as b ON (a.ID = b.ID or (a.Field1 = b.Field1 and a.Field2 = b.Field2)) WHERE bla bla bla We have tried to set up indexes and the query still takes 8 minutes to run. It only returns 6,000 records. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
AW: Transfering from access to MySQL?
Jonas Check out the free DBManager at http://www.dbtools.com.br/EN/dbmanager.php It can not only import directly from Access but might be very handy later in your every day work with MySQL as a front-end Couldn't live without it ! Freddie -Ursprüngliche Nachricht- Von: Jonas Lindén [mailto:[EMAIL PROTECTED] Gesendet: Mittwoch, 25. Februar 2004 09:33 An: [EMAIL PROTECTED] Betreff: Transfering from access to MySQL? Hello, Could someone help me with a tip on how I can convert my old Access DBs to MySQL? Regards /Jonas -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
command line escape for apostrophe
I usually use php for most of my work, but I have to run a fair percentage of maintenance etc from the mysql shell program. I have a record that has an apostrophe and so was inserted using php by escaping the ' with \ so that it was put in as \' and it shows up in the record as O'Brien as it should. I am trying to run a select statement from the shell program and it will not accept \ or double '' for escaping it. Pretty simple problem, but I can't find anything via google for it. (Or the manual for that matter. ) TIA Larry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Transfering from access to MySQL?
At 02:32 2/25/2004, Jonas Lindén wrote: Hello, Could someone help me with a tip on how I can convert my old Access DBs to MySQL? Regards /Jonas I've had some success with Cynergi.net's ExportSQL script http://www.Cynergi.net/exportsql/ Start Here to Find It Fast! - http://www.US-Webmasters.com/best-start-page/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: updating tables in MySQL
Are you using the DTS to schedule replication or periodic data imports? Original Message On 2/25/04, 11:48:02 AM, HACKATHORN, TODD (SWBT) [EMAIL PROTECTED] wrote regarding updating tables in MySQL: Hello, Sorry if this is a obvious question, but I am new to mySQL, and PHP. I am used to working with MS SQL Server 2000 and coldfusion. In SQL server I can build DTS packages and schedule them to update the data in my tables with different queries, and to import current data from other databases. Is there a way I can do this in mySQL? I found how I can schedule scripts, but am not sure how to go about making a script do queries, or how to import data from a other data source. I could write a script to insert each record one at a time, but that seems like a lot of work. I work with fairly large tables. I am new to both Linux, and mySQL, but do have a decent amount of experience in other SQL system. I am using RedHat 9, mySQL Ver 12.22 Distrib 4.0.18, for pc-linux (i686). Thanks in advance, Todd -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with indexes
Can you do a show create table Table1 and see how the index is defined? Original Message On 2/25/04, 12:02:43 PM, James Lamanna [EMAIL PROTECTED] wrote regarding Problem with indexes: So I'm having issues with indexes in mysql 4.0 I issue the following: Create Index Index1 on Table1 (ParentID,ClassID,Amount,Memo(20)); That works fine. However I'm running into problems when I'm trying to insert into this table using INSERT I'm receiving errors about duplicate keys against this Index. I thought this Index should be non-unique since I didn't specify the UNIQUE keyword. This table does not have a Primary Key either. Kinda need help ASAP, so any ideas would be appreciated. Thanks. -- James Lamanna -- 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: command line escape for apostrophe
At 14:25 -0500 2/25/04, Larry Brown wrote: I usually use php for most of my work, but I have to run a fair percentage of maintenance etc from the mysql shell program. I have a record that has an apostrophe and so was inserted using php by escaping the ' with \ so that it was put in as \' and it shows up in the record as O'Brien as it should. I am trying to run a select statement from the shell program and it will not accept \ or double '' for escaping it. Pretty simple problem, but I can't find anything via google for it. (Or the manual for that matter. ) \ and double '' both should work inside '-quoted strings. Please show how you're entering your query and the resulting error message. TIA Larry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com MySQL Users Conference: April 14-16, 2004 http://www.mysql.com/uc2004/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: command line escape for apostrophe
Are you not able to do SELECT * FROM table WHERE col = O\'Brien ? Original Message On 2/25/04, 1:25:06 PM, Larry Brown [EMAIL PROTECTED] wrote regarding command line escape for apostrophe: I usually use php for most of my work, but I have to run a fair percentage of maintenance etc from the mysql shell program. I have a record that has an apostrophe and so was inserted using php by escaping the ' with \ so that it was put in as \' and it shows up in the record as O'Brien as it should. I am trying to run a select statement from the shell program and it will not accept \ or double '' for escaping it. Pretty simple problem, but I can't find anything via google for it. (Or the manual for that matter. ) TIA Larry -- 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: Query Problems
On 25 Feb 2004 at 13:09, Eric Scuccimarra wrote: Select* FROM Table1 as a INNER JOIN Table2 as b ON (a.ID = b.ID or (a.Field1 = b.Field1 and a.Field2 = b.Field2)) WHERE bla bla bla It's hard to know without seeing the indexes and the full WHERE clause, but part of the problem could be that MySQL can't use an index for the join because of the OR. One possibility would be to break in into two queries and use a UNION: ( SELECT * FROM Table1 a INNER JOIN Table2 b ON a.ID = b.ID WHERE condition ) UNION ( SELECT * FROM Table1 a INNER JOIN Table2 b ON a.Field1 = b.Field1 AND a.Field2 = b.Field2 WHERE condition ) ORDER BY whatever; -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
[bug] Temp table cannot be used twice in a query
Description: When a temp table is included twice in a query mysql fails with the error: ERROR 1137 at line 9: Can't reopen table: 'foo' This happens on both 4.0.17 and 4.0.18. It did not happen on 4.0.14. How-To-Repeat: create temporary table test (pk int primary key); select * from test as foo, test as bar where foo.pk=bar.pk; Fix: Dropping the temporary from the above example fixes the problem, but is not a great solution. Submitter-Id: John Heitmann Originator: Organization: organization of PR author (multiple lines) MySQL support: none Synopsis: Temp table can't be used twice in a query [4.0.x] Severity: serious Priority: medium Category: mysql Class: sw-bug Release: mysql-4.0.18 (Source distribution) Server: /opt/cn/bin/mysqladmin Ver 8.40 Distrib 4.0.18, for pc-linux on i686 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 4.0.18-log Protocol version10 Connection Localhost via UNIX socket UNIX socket /opt/cn/var/mysql4/mysql.sock Uptime: 43 min 7 sec Threads: 9 Questions: 839777 Slow queries: 0 Opens: 2313 Flush tables: 1 Open tables: 64 Queries per second avg: 324.614 C compiler:cc (GCC) 3.2.1 C++ compiler: c++ (GCC) 3.2.1 Environment: machine, os, target, libraries (multiple lines) System: Linux ***blanked* 2.4.18-24.7.xsmp #3 SMP Thu Feb 13 10:34:29 EST 2003 i686 unknown Architecture: i686 Some paths: /opt/cn/bin/perl /opt/cn/bin/make /opt/cn/bin/gmake /opt/cn/bin/gcc /opt/cn/bin/cc GCC: Reading specs from /opt/cn/lib/gcc-lib/i686-pc-linux-gnu/3.2.1/specs Configured with: ../configure --prefix=/opt/cn --exec-prefix=/opt/cn --includedir=/opt/cn/include/gcc --with-gxx-include-dir=/opt/cn/include/g++ --with-local-prefix=/opt/cn/lib/gcc-lib --enable-languages=c,c++ --enable-threads=posix --disable-maintainer-mode --disable-shared --disable-nls --with-gnu-ld --with-ld=/opt/cn/bin/ld --with-gnu-as --with-as=/opt/cn/bin/as --with-gnu-as --with-gnu-ld Thread model: posix gcc version 3.2.1 Compilation info: CC='/opt/cn/bin/cc' CFLAGS='-O2 -pipe' CXX='/opt/cn/bin/c++' CXXFLAGS='-O2 -pipe' LDFLAGS='' ASFLAGS='' LIBC: lrwxrwxrwx1 root root 13 Mar 2 2003 /lib/libc.so.6 - libc-2.2.5.so -rwxr-xr-x1 root root 1260480 Apr 15 2002 /lib/libc-2.2.5.so -rw-r--r--1 root root 2310808 Apr 15 2002 /usr/lib/libc.a -rw-r--r--1 root root 178 Apr 15 2002 /usr/lib/libc.so Configure command: ./configure '--prefix=/opt/cn' '--sysconfdir=/opt/cn/etc/mysql4' '--localstatedir=/opt/cn/var/mysql4' '--libexecdir=/opt/cn/libexec/mysql4' '--with-unix-socket-path=/opt/cn/var/mysql4/mysql.sock' '--with-mysqld-user=bschmaus' '--enable-thread-safe-client' '--with-berkeley-db' '--with-low-memory' '--disable-shared' '--with-client-ldflags=-static' '--with-mysqld-ldflags=-static' 'CC=/opt/cn/bin/cc' 'CFLAGS=-O2 -pipe' 'CXXFLAGS=-O2 -pipe' 'CXX=/opt/cn/bin/c++' -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL load balancing
Hi, Currently our web infrastructure has one main MySQL server, to which connections are made by (mostly) mod_perl running under Apache (on 3 different machines), and several other custom-built application servers on other servers (which have persistant connections, and do both reads and writes). This machine answers between 300 and 1200 queries per second, and the load average is generally around 3-5 (falling below 1 off-peak). We have max_connections set to 100, and are starting to hit this limit quite frequently. The machine is a dual PIII 1.2Ghz with 1.5GB RAM and a 73GB SCSI RAID-5 array, and it only runs MySQL. I am intending on implementing a load-balancing solution, whereby we have a pool of around 3 slaves replicating off the main database server. mod_perl scripts can then make a connection to a random slave, and send their read queries there. Hopefully we can then scale this by adding more slaves to the pool. Problem: all the mod_perl pages run a few write queries, so they will require a connection to the main database server. Since around 80% of our queries are reads, would you recommend that each script has two connections: one for read queries, and one for write queries? We can determine which queries should be run on which connection using Perl. I have looked at the master-master replication solution so that any machine in the replication pool can have write queries run on it; unfortunately several of our tables use auto_increment fields, so this isn't an option. Any advice on this would be appreciated. Alex Greg -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: command line escape for apostrophe
My bad people. The error was elsewhere in the query, but the error happenned to describe the query as failing near where the apostrophe was. Sorry to waste anyone's time. Larry -Original Message- From: Larry Brown [mailto:[EMAIL PROTECTED] Sent: Wednesday, February 25, 2004 2:25 PM To: MySQL List Subject: command line escape for apostrophe I usually use php for most of my work, but I have to run a fair percentage of maintenance etc from the mysql shell program. I have a record that has an apostrophe and so was inserted using php by escaping the ' with \ so that it was put in as \' and it shows up in the record as O'Brien as it should. I am trying to run a select statement from the shell program and it will not accept \ or double '' for escaping it. Pretty simple problem, but I can't find anything via google for it. (Or the manual for that matter. ) TIA Larry -- 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]
OSX 10.3 Binaries and 64 Bit
Hi! One of my associates here read a report somewhere that mysqld when compiled under OS X 10.3 was 40%+ more efficient due to improvements in the compilers and the way 10.3 work. Also 10.3 is a 64 Bit Operating System and it would be a major benefit to us to set some of the memory values in excess of 2MBytes on our 64 Bit G5 servers. Sadly my associate can't find the original report any longer, so I don't have the reference material or exact information. MySQL themselves repeatedly says we are better off using MySQL compiled binaries than compiling our own. So then how can we take advantage of these OSX 10.3 compiler gains when MySQL is only offering 10.2 compiled versions on their web site. Which brings me to my questions... If the OSX 10.3 Compilers are so much more efficient and result in major performance gains how long will it be before MySQL starts providing a MySQL binary compiled for OSX 10.3? Along with regular 10.3 is it possible MySQL will offer Binaries for 10.3 64 bit for those people with G5 servers (and with Apple being a major sponsor of the forthcoming Users conference and making a big deal about the new G5 servers I would think they have a major interest in this issue too). In the absence of these apparently faster improved versions of MySQL if we did want to make our own binaries using 10.3 can some talk us Mac guys (who haven't had the long history of building our own binaries most of the Unix guys have) through the exact process of getting the source code and compiling it on our systems? Best Regards, Bruce -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: updating tables in MySQL
HACKATHORN, TODD (SWBT) wrote: Hello, Sorry if this is a obvious question, but I am new to mySQL, and PHP. I am used to working with MS SQL Server 2000 and coldfusion. In SQL server I can build DTS packages and schedule them to update the data in my tables with different queries, and to import current data from other databases. Is there a way I can do this in mySQL? I found how I can schedule scripts, but am not sure how to go about making a script do queries, or how to import data from a other data source. I could write a script to insert each record one at a time, but that seems like a lot of work. I work with fairly large tables. I am new to both Linux, and mySQL, but do have a decent amount of experience in other SQL system. I am using RedHat 9, mySQL Ver 12.22 Distrib 4.0.18, for pc-linux (i686). Todd: MySQL does not have this ability. You can, however, accomplish this with command-line shell/Perl/PHP scripts run from cron (type man cron and man crontab to learn more, if you are not familiar with it). -- Sasha Pachev Create online surveys at http://www.surveyz.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help is appreciated
A Z wrote:
I am running Delphi 6 Ent. along with Corelab
technology to access MySQL 4.1.14.
We run into problem of table coruption (error: 127,
145) from time to time using the following SQLs. This
is done in the transaction context. Most of the time
it works fine but it does get into problem. We have
tested it with simple insert and it seems to be
working fine.
We really appreciate your input.
{SQL}
Delete From Table1
INSERT INTO Table1 (Field1, Field2) SELECT a.Field1,
'2004-02-25' From Table2 a LEFT Join Table3 b ON
(a.Field1 = b.Field2)
where a.Field4 = '0' and a.Field5 = '0' and a.Field6
= '2004-02-25'
and a.Field7 Like 'ABC%' and b.Field1 = 'FF'
Looks like a bug report. Create a test case to duplicate this corruption and
submit it to the MySQL developers.
However, if you did mean to use transactions, there is another problem. You are
using MyISAM tables (since you got error 127), which are not transactional. Try
ALTER TABLE tbl_name TYPE=INNODB for all tables you are using.
P.S. If you are able to duplicate the bug, try the test case on 4.0.18 before
submitting it.
--
Sasha Pachev
Create online surveys at http://www.surveyz.com/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: C api: core dump on mysql_real_connect
Cliff Addy wrote:
I've got on that really has me stumped ...
I've modified tha analog web stats program before to use a mysql database
before and I'm trying to do it again on a new system. I have this
function:
void db_connect(){
printf(start connect\n);
mysql_init(dbh);
if (!mysql_real_connect(dbh,localhost,usr,xxx,rdns,0,NULL,0)){
fprintf(stderr, Connection to rdns database failed\n);
exit(1);
}
printf(finish connect\n);
}
where dbh is a global MYSQL structure. This code works fine on the old
system. If I pull it out into it's own little test program on the new
server, it also works fine. But when I put it in with the analog source
code, it compiles fine but the mysql_real_connect causes a core dump when
run.
Running 4.0.17 on FreeBSD 4.9R
Any ideas?
The most common reason for the above error is mysql.h header/libmysqlsclient.so
library incompatibility. Make sure they are in sync.
--
Sasha Pachev
Create online surveys at http://www.surveyz.com/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Subquery Help
I'm about to pull my hair out on this one so I thought I would see if somebody could point me in the right direction. I have a subquery that like like so. SELECT * FROM Word INNER JOIN DomainWord USING ( word ) INNER JOIN Domain USING ( domain ) WHERE Domain.domain = ANY( SELECT Domain.domain FROM Word INNER JOIN DomainWord USING ( word ) INNER JOIN Domain USING ( domain ) WHERE Word.word = 'php' ) AND parked = 'N' And the results are below. Only problem is that I only want the domain back if pvr for all entries is Y. If there are any N's I don't want the domain back. +-++-+-++--- --++---++-+ | word| word_count | updated | pvr | domain | word | domain | words | parked | updated | +-++-+-++--- --++---++-+ | extreme |108 | 2004-02-25 13:07:21 | Y | extremephp.org | extreme | extremephp.org | 5 | N | 2004-02-25 11:48:41 | | mep |187 | 2004-02-25 11:48:41 | Y | extremephp.org | mep | extremephp.org | 5 | N | 2004-02-25 11:48:41 | | php | 26 | 2004-02-25 13:10:53 | Y | extremephp.org | php | extremephp.org | 5 | N | 2004-02-25 11:48:41 | | php | 26 | 2004-02-25 13:10:53 | Y | phpiba.com | php | phpiba.com | 3 | N | 2004-02-25 13:10:53 | | hpib| 3 | 2004-02-25 13:10:53 | N | phpiba.com | hpib | phpiba.com | 3 | N | 2004-02-25 13:10:53 | | iba | 66 | 2004-02-25 13:10:58 | Y | phpiba.com | iba | phpiba.com | 3 | N | 2004-02-25 13:10:53 | +-++-+-++--- --++---++-+ Any ideas? Donny -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with indexes
Found out that the problem was actually a bug in 4.0.17. Upgrading to 4.0.18 seems to have fixed the problem. Noted here: http://bugs.mysql.com/bug.php?id=2446 [EMAIL PROTECTED] wrote: Can you do a show create table Table1 and see how the index is defined? Original Message On 2/25/04, 12:02:43 PM, James Lamanna [EMAIL PROTECTED] wrote regarding Problem with indexes: So I'm having issues with indexes in mysql 4.0 I issue the following: Create Index Index1 on Table1 (ParentID,ClassID,Amount,Memo(20)); That works fine. However I'm running into problems when I'm trying to insert into this table using INSERT I'm receiving errors about duplicate keys against this Index. I thought this Index should be non-unique since I didn't specify the UNIQUE keyword. This table does not have a Primary Key either. Kinda need help ASAP, so any ideas would be appreciated. -- James Lamanna Applied Minds, Inc. 1209 Grand Central Ave. Glendale, CA 91201 (818) 332-5214 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with indexes
I am assuming that the Memo field is a text datatype and you were encountering the duplicate key bug. Original Message On 2/25/04, 3:28:58 PM, James Lamanna [EMAIL PROTECTED] wrote regarding Re: Problem with indexes: Found out that the problem was actually a bug in 4.0.17. Upgrading to 4.0.18 seems to have fixed the problem. Noted here: http://bugs.mysql.com/bug.php?id=2446 [EMAIL PROTECTED] wrote: Can you do a show create table Table1 and see how the index is defined? Original Message On 2/25/04, 12:02:43 PM, James Lamanna [EMAIL PROTECTED] wrote regarding Problem with indexes: So I'm having issues with indexes in mysql 4.0 I issue the following: Create Index Index1 on Table1 (ParentID,ClassID,Amount,Memo(20)); That works fine. However I'm running into problems when I'm trying to insert into this table using INSERT I'm receiving errors about duplicate keys against this Index. I thought this Index should be non-unique since I didn't specify the UNIQUE keyword. This table does not have a Primary Key either. Kinda need help ASAP, so any ideas would be appreciated. -- James Lamanna Applied Minds, Inc. 1209 Grand Central Ave. Glendale, CA 91201 (818) 332-5214 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL load balancing
At 02:06 PM 2/25/2004, you wrote: Hi, Currently our web infrastructure has one main MySQL server, to which connections are made by (mostly) mod_perl running under Apache (on 3 different machines), and several other custom-built application servers on other servers (which have persistant connections, and do both reads and writes). This machine answers between 300 and 1200 queries per second, and the load average is generally around 3-5 (falling below 1 off-peak). We have max_connections set to 100, and are starting to hit this limit quite frequently. The machine is a dual PIII 1.2Ghz with 1.5GB RAM and a 73GB SCSI RAID-5 array, and it only runs MySQL. I am intending on implementing a load-balancing solution, whereby we have a pool of around 3 slaves replicating off the main database server. mod_perl scripts can then make a connection to a random slave, and send their read queries there. Hopefully we can then scale this by adding more slaves to the pool. Problem: all the mod_perl pages run a few write queries, so they will require a connection to the main database server. Since around 80% of our queries are reads, would you recommend that each script has two connections: one for read queries, and one for write queries? We can determine which queries should be run on which connection using Perl. I have looked at the master-master replication solution so that any machine in the replication pool can have write queries run on it; unfortunately several of our tables use auto_increment fields, so this isn't an option. Here are a couple of suggestions. You can have auto-inc fields unique between databases by creating a primary index from the AutoInc, and Db_Id columns. Each database would have its own Db_Id so you know which database created the row once it is merged back into the main database. This also works with InnoDb tables as long as the auto-inc field appears first in the key. So the key would be '1,A' for rcd_id=1 for database A, and '1,B' for rcc_id=1 on database B etc. Another alternative would be to split the application into several databases, one that is replicated for reading (DBR1, DBR2, DBR3 etc), a single database for updates (DBU1) and a master database (DBM). The webservers access the replicated databases (DBR1...DBR3), and updates are made to DBU1. Your web application just uses a different connection to access DBU1 instead of DBRn. The updated rows of DBU1 are then fetched and imported into DBM, the master database. This can be a manual process that is done every 5 minutes or so. When the rows are imported into DBM, the rows are removed from DBU1 so DBU1 has only new rows that haven't been imported yet. The changes made to DBM are then replicated back to DBR1 through DBR3. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with indexes
Yes. [EMAIL PROTECTED] wrote: I am assuming that the Memo field is a text datatype and you were encountering the duplicate key bug. Original Message On 2/25/04, 3:28:58 PM, James Lamanna [EMAIL PROTECTED] wrote regarding Re: Problem with indexes: Found out that the problem was actually a bug in 4.0.17. Upgrading to 4.0.18 seems to have fixed the problem. Noted here: http://bugs.mysql.com/bug.php?id=2446 [EMAIL PROTECTED] wrote: Can you do a show create table Table1 and see how the index is defined? Original Message On 2/25/04, 12:02:43 PM, James Lamanna [EMAIL PROTECTED] wrote regarding Problem with indexes: So I'm having issues with indexes in mysql 4.0 I issue the following: Create Index Index1 on Table1 (ParentID,ClassID,Amount,Memo(20)); That works fine. However I'm running into problems when I'm trying to insert into this table using INSERT I'm receiving errors about duplicate keys against this Index. I thought this Index should be non-unique since I didn't specify the UNIQUE keyword. This table does not have a Primary Key either. Kinda need help ASAP, so any ideas would be appreciated. -- James Lamanna Applied Minds, Inc. 1209 Grand Central Ave. Glendale, CA 91201 (818) 332-5214 -- James Lamanna Applied Minds, Inc. 1209 Grand Central Ave. Glendale, CA 91201 (818) 332-5214 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SQL_BIG_TABLES and replication
I'm currently running mysql 4.0.13 on red hat 7.2. The following create table query currently requires the user to use SET SQL_BIG_TABLES=1 for the query to go through on the master successfully. However, that doesn't seem to get set when the slave tries to replicate the create table statement. I've tried restarting the slave with the --big-tables option, but that doesn't seem to help either. The resulting table files on the master are relatively small... 8.4k Feb 16 22:37 60dayREGusers_sep_oct2003.frm 5.0M Feb 16 22:37 60dayREGusers_sep_oct2003.MYD 9.5M Feb 16 22:37 60dayREGusers_sep_oct2003.MYI however the ProfileIDValue_REGID table is rather large. 8.4k Sep 15 09:44 ProfileIDValue_REGID.frm 499M Feb 16 22:17 ProfileIDValue_REGID.MYD 443M Feb 16 22:18 ProfileIDValue_REGID.MYI 8.4k Nov 13 11:47 UniqueID_oct2003.frm 32M Nov 13 11:53 UniqueID_oct2003.MYD 51M Nov 13 11:53 UniqueID_oct2003.MYI 8.4k Jan 9 10:50 UniqueID_sep2003.frm 34M Jan 9 10:58 UniqueID_sep2003.MYD 55M Jan 9 10:58 UniqueID_sep2003.MYI Anyone have any thoughts? ERROR: 1114 The table '#sql_931_0' is full 040225 15:34:25 Slave: error 'The table '#sql_931_0' is full' on query 'create table 60dayREGusers_sep_oct2003 (primary key(UniqueID)) select distinct UniqueID_sep2003.UniqueID from UniqueID_sep2003,ProfileIDValue_REGID where UniqueID_sep2003.UniqueID=ProfileIDValue_REGID.UniqueID union select distinct UniqueID_oct2003.UniqueID from UniqueID_oct2003,ProfileIDValue_REGID where UniqueID_oct2003.UniqueID=ProfileIDValue_REGID.UniqueID', error_code=1114 Thanks, Brian I don't need any of that SQL stuff -- I just want a database!
Query help - add results then divide by
Good afternoon, How can I take the 3 results below add them together then divide by 131.77? Can it all be done in one SQL statement? Thanks in advance. mysql describe ads; +---+---+--+-+++ | Field | Type | Null | Key | Default| Extra | +---+---+--+-+++ | adID | int(11) | | PRI | NULL | auto_increment | | page | int(11) | | | 0 | | | adnum | varchar(20) | | || | | date | date | | | -00-00 | | | depth | decimal(3,2) | YES | | 0.00 | | | timestamp | timestamp(14) | YES | | NULL | | | col | int(11) | YES | | 0 | | | acc | varchar(50) | | || | | editionID | int(11) | | | 0 | | +---+---+--+-+++ 9 rows in set (0.00 sec) mysql SELECT ((ads.col * 1.91) * ads.depth) FROM ads Where date = '2004-02-26' AND editionID = '13' AND ads.page = '16'; +-+ | ((ads.col * 1.91) * ads.depth) | +-+ |7.64 | | 34.38 | |7.64 | +-+ 3 rows in set (0.01 sec)
Re: Query Problems
No, we tried individual indexes and then one big grouped index but not individual indexes on each of the fields. Adding the index actually added a few seconds to the query so we weren't sure if that was the way to go. I'll try this, though. Eric At 10:36 AM 2/25/2004 -0800, Daniel Clark wrote: Do you have separate indexes on: Table1.ID Table2.ID Table1.Field1 Table2.Field1 Table1.Field1 Table1.Field2 Select* FROM Table1 as a INNER JOIN Table2 as b ON (a.ID = b.ID or (a.Field1 = b.Field1 and a.Field2 = b.Field2)) WHERE bla bla bla We have tried to set up indexes and the query still takes 8 minutes to run. It only returns 6,000 records. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Problems
I know Oracle likes the indexes separatly, but mySQL might like combinations. No, we tried individual indexes and then one big grouped index but not individual indexes on each of the fields. Adding the index actually added a few seconds to the query so we weren't sure if that was the way to go. I'll try this, though. Eric At 10:36 AM 2/25/2004 -0800, Daniel Clark wrote: Do you have separate indexes on: Table1.ID Table2.ID Table1.Field1 Table2.Field1 Table1.Field1 Table1.Field2 Select* FROM Table1 as a INNER JOIN Table2 as b ON (a.ID = b.ID or (a.Field1 = b.Field1 and a.Field2 = b.Field2)) WHERE bla bla bla We have tried to set up indexes and the query still takes 8 minutes to run. It only returns 6,000 records. -- 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]
Installation problem!!!
Hello List: I have installed MySQL Master Server from Binaries 4-5 times without problem. Now I am installing MySQL CLIENT from source and have run into problem, as explained below: Download mysql-5.0.0-alpha.tar.gz in /usr/local % cd /usr/local % gunzip mysql-5.0.0-alpha.tar.gz | tar -xvf - % cd mysql-5.0.0-alpha % ./configure --without-server --prefix=/usr/local/mysql % make % make install % scripts/mysql_install_db When I run the last line scripts/mysql_install_db, I get an error message No such file or directory. I have looked into scripts folder and ther is mysql_install_db.sh. Has anybody else experienced the same problem? What is causing this error? Thanks in advance. Kirti -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
dollar amounts
What is the preferred way of storing a dollar amount in the range 0.00 - 9.99? double decimal(11,2) bigint (storing value*100) ...? I'm more interested in speed of use as an indexed column (especially for range searches) than in disk space usage. -keith -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query help - add results then divide by
What about SELECT (SUM( ads.col * 1.91) * ads.depth ) ) / 131.77 FROM ads WHERE date = '2004-02-26' AND editionID = '13' AND ads.page = '16' Original Message On 2/25/04, 4:19:12 PM, Rogers, Dennis [EMAIL PROTECTED] wrote regarding Query help - add results then divide by : Good afternoon, How can I take the 3 results below add them together then divide by 131.77? Can it all be done in one SQL statement? Thanks in advance. mysql describe ads; +---+---+--+-+++ | Field | Type | Null | Key | Default| Extra | +---+---+--+-+++ | adID | int(11) | | PRI | NULL | auto_increment | | page | int(11) | | | 0 | | | adnum | varchar(20) | | || | | date | date | | | -00-00 | | | depth | decimal(3,2) | YES | | 0.00 | | | timestamp | timestamp(14) | YES | | NULL | | | col | int(11) | YES | | 0 | | | acc | varchar(50) | | || | | editionID | int(11) | | | 0 | | +---+---+--+-+++ 9 rows in set (0.00 sec) mysql SELECT ((ads.col * 1.91) * ads.depth) FROM ads Where date = '2004-02-26' AND editionID = '13' AND ads.page = '16'; +-+ | ((ads.col * 1.91) * ads.depth) | +-+ |7.64 | | 34.38 | |7.64 | +-+ 3 rows in set (0.01 sec) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Query help - add results then divide by
Thanks so much!! SELECT (SUM(( ads.col * 1.91) * ads.depth ) / 131.77) * 100 FROM ads WHERE date = '2004-02-26' AND editionID = '13' AND ads.page = '16' -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, February 25, 2004 5:55 PM To: Rogers, Dennis Cc: '[EMAIL PROTECTED]'; '[EMAIL PROTECTED]'; Hines, David Subject: Re: Query help - add results then divide by What about SELECT (SUM( ads.col * 1.91) * ads.depth ) ) / 131.77 FROM ads WHERE date = '2004-02-26' AND editionID = '13' AND ads.page = '16' Original Message On 2/25/04, 4:19:12 PM, Rogers, Dennis [EMAIL PROTECTED] wrote regarding Query help - add results then divide by : Good afternoon, How can I take the 3 results below add them together then divide by 131.77? Can it all be done in one SQL statement? Thanks in advance. mysql describe ads; +---+---+--+-+++ | Field | Type | Null | Key | Default| Extra | +---+---+--+-+++ | adID | int(11) | | PRI | NULL | auto_increment | | page | int(11) | | | 0 | | | adnum | varchar(20) | | || | | date | date | | | -00-00 | | | depth | decimal(3,2) | YES | | 0.00 | | | timestamp | timestamp(14) | YES | | NULL | | | col | int(11) | YES | | 0 | | | acc | varchar(50) | | || | | editionID | int(11) | | | 0 | | +---+---+--+-+++ 9 rows in set (0.00 sec) mysql SELECT ((ads.col * 1.91) * ads.depth) FROM ads Where date = '2004-02-26' AND editionID = '13' AND ads.page = '16'; +-+ | ((ads.col * 1.91) * ads.depth) | +-+ |7.64 | | 34.38 | |7.64 | +-+ 3 rows in set (0.01 sec)
default encrypt for PASSWORD
I'm sure this is in the manual somewhere or in the archives, but I'm not finding it and I bet someone easily knows the short answer: Using 3.23 setting a varchar field to PASSWORD(secret) Having altered nothing regarding encryption in the server what is the default encryption type for PASSWORD? cuz I told PEAR::Auth that the encryption type is md5 and if that is wrong that could be the reason why it is not working. Thanks, Tom Dick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
fulltext search always returns no results
Summary: When I run a fulltext search, it always returns no results. I have
added a fulltext index to the column being searched. Also, I am searching
for a term that is in the table, but not in more than 50% of the rows.
I notice that when I add EXPLAIN to my search, the key_len of my fulltext
index is 0. Does that mean my fulltext index is empty, explaining why my
searches never return any results?
Either way, I'd be grateful for any suggestions on how to get fulltext
search to work!
Config:
MySQL 3.23.55 running on my webhost's Linux box
phpMyAdmin 2.1.0
Long description:
My test database has two tables and 16 rows. The table named entry starts
out looking like this:
Field Type AttributesNullDefaultExtra
---
itemid mediumint(8) UNSIGNED No 0
permid mediumint(8) UNSIGNED No 0
eventtime datetimeNo -00-00 00:00:00
subjectvarchar(255)Yes
event textYes
current_music varchar(255)Yes
current_mood varchar(255)Yes
Indexes
KeynameUniqueField
--
PRIMARYYes itemid
To allow fulltext search, I add a fulltext index via phpMyAdmin's Run SQL
query textarea:
ALTER TABLE entry ADD FULLTEXT subject_index (subject);
I log out of and back into phpMyAdmin. Now there is a new row in the list of
indexes:
KeynameUniqueField
--
PRIMARYYes itemid
subject_index Nosubject
I do a fulltext search for a term I know is in the subject data:
SELECT subject FROM entry WHERE MATCH (subject) AGAINST ('contact');
But I get back an error/empty results:
Error
SQL-query:
SELECT subject FROM entry WHERE MATCH (subject) AGAINST ('contact');
MySQL said:
I run the query again, using EXPLAIN:
EXPLAIN SELECT subject FROM entry WHERE MATCH (subject) AGAINST ('contact');
The query is echoed (with an additional LIMIT apparently added by
phpMyAdmin):
EXPLAIN SELECT subject FROM entry WHERE MATCH (subject) AGAINST ('contact');
LIMIT 0, 30
And the EXPLAIN output is:
table type possible_keys keykey_len ref rowsExtra
--
entry fulltext subject_index subject_index 0 1 where used
_
Watch high-quality video with fast playback at MSN Video. Free!
http://click.atdmt.com/AVE/go/onm00200365ave/direct/01/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: fulltext search always returns no results
Excuse if i'm not correct but this may be your problemo ?
MySQL 3.23.55 running on my webhost's Linux box
phpMyAdmin 2.1.0
I didnt think fulltext was in 3.23 wasnt this a Mysql 4 feature ??
Summary: When I run a fulltext search, it always returns no results. I
have added a fulltext index to the column being searched. Also, I am
searching for a term that is in the table, but not in more than 50% of
the rows.
I notice that when I add EXPLAIN to my search, the key_len of my
fulltext index is 0. Does that mean my fulltext index is empty,
explaining why my searches never return any results?
Either way, I'd be grateful for any suggestions on how to get fulltext
search to work!
Config:
MySQL 3.23.55 running on my webhost's Linux box
phpMyAdmin 2.1.0
Long description:
My test database has two tables and 16 rows. The table named entry
starts out looking like this:
Field Type AttributesNullDefaultExtra
---
itemid mediumint(8) UNSIGNED No 0
permid mediumint(8) UNSIGNED No 0
eventtime datetimeNo -00-00 00:00:00
subjectvarchar(255)Yes
event textYes
current_music varchar(255)Yes
current_mood varchar(255)Yes
Indexes
KeynameUniqueField
--
PRIMARYYes itemid
To allow fulltext search, I add a fulltext index via phpMyAdmin's Run
SQL query textarea:
ALTER TABLE entry ADD FULLTEXT subject_index (subject);
I log out of and back into phpMyAdmin. Now there is a new row in the
list of indexes:
KeynameUniqueField
--
PRIMARYYes itemid
subject_index Nosubject
I do a fulltext search for a term I know is in the subject data:
SELECT subject FROM entry WHERE MATCH (subject) AGAINST ('contact');
But I get back an error/empty results:
Error
SQL-query:
SELECT subject FROM entry WHERE MATCH (subject) AGAINST ('contact');
MySQL said:
I run the query again, using EXPLAIN:
EXPLAIN SELECT subject FROM entry WHERE MATCH (subject) AGAINST
('contact');
The query is echoed (with an additional LIMIT apparently added by
phpMyAdmin):
EXPLAIN SELECT subject FROM entry WHERE MATCH (subject) AGAINST
('contact'); LIMIT 0, 30
And the EXPLAIN output is:
table type possible_keys keykey_len ref rows
Extra
--
entry fulltext subject_index subject_index 0 1 where
used
_
Watch high-quality video with fast playback at MSN Video. Free!
http://click.atdmt.com/AVE/go/onm00200365ave/direct/01/
--
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: libmysqld and PHP
You might want to post this on the PHP user lists at news.php.net sub-group php.general Respectfully, Ligaya Turmelle David Jackson [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] I was wondering if I can user libmysqld with PHP. If it's possible would someone post a simple exampe. TIA, David -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query help - add results then divide by
I think that you can just do this: select sum(ads.col)*1.191*sum(ads.depth)/131.77 where date ='2004-02-26' AND editionID = '13' AND ads.page = '16'; because of the disttributive property of multiplication. (2 * 1.191) +(6*1.91) +(4*1.91)/131.77 = 12 *1.91/131.77 = (12*1.91)/131.77 = 12*(1.91/131.77) Test it to make sure I understand what you're asking, but it worked for my in my tests. bob Rogers, Dennis wrote: Good afternoon, How can I take the 3 results below add them together then divide by 131.77? Can it all be done in one SQL statement? Thanks in advance. mysql describe ads; +---+---+--+-+++ | Field | Type | Null | Key | Default| Extra | +---+---+--+-+++ | adID | int(11) | | PRI | NULL | auto_increment | | page | int(11) | | | 0 | | | adnum | varchar(20) | | || | | date | date | | | -00-00 | | | depth | decimal(3,2) | YES | | 0.00 | | | timestamp | timestamp(14) | YES | | NULL | | | col | int(11) | YES | | 0 | | | acc | varchar(50) | | || | | editionID | int(11) | | | 0 | | +---+---+--+-+++ 9 rows in set (0.00 sec) mysql SELECT ((ads.col * 1.91) * ads.depth) FROM ads Where date = '2004-02-26' AND editionID = '13' AND ads.page = '16'; +-+ | ((ads.col * 1.91) * ads.depth) | +-+ |7.64 | | 34.38 | |7.64 | +-+ 3 rows in set (0.01 sec) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
urgent help required for mysql
040225 13:57:20 mysqld started InnoDB: The first specified data file ./ibdata1 did not exist: InnoDB: a new database to be created! 040225 13:57:21 InnoDB: Setting file ./ibdata1 size to 10 MB InnoDB: Database physically writes the file full: wait... 040225 13:57:22 InnoDB: Log file ./ib_logfile0 did not exist: new to be created InnoDB: Setting log file ./ib_logfile0 size to 5 MB InnoDB: Database physically writes the file full: wait... 040225 13:57:22 InnoDB: Log file ./ib_logfile1 did not exist: new to be created InnoDB: Setting log file ./ib_logfile1 size to 5 MB InnoDB: Database physically writes the file full: wait... InnoDB: Doublewrite buffer not found: creating new InnoDB: Doublewrite buffer created InnoDB: Creating foreign key constraint system tables InnoDB: Foreign key constraint system tables created 040225 13:57:25 InnoDB: Started 040225 13:57:25 Fatal error: Can't open privilege tables: Table 'mysql.host' doesn't exist 040225 13:57:25 Aborting 040225 13:57:25 InnoDB: Starting shutdown... 040225 13:57:27 InnoDB: Shutdown completed -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL_BIG_TABLES and replication
Stanton, Brian wrote: I'm currently running mysql 4.0.13 on red hat 7.2. The following create table query currently requires the user to use SET SQL_BIG_TABLES=1 for the query to go through on the master successfully. However, that doesn't seem to get set when the slave tries to replicate the create table statement. I've tried restarting the slave with the --big-tables option, but that doesn't seem to help either. The resulting table files on the master are relatively small... 8.4k Feb 16 22:37 60dayREGusers_sep_oct2003.frm 5.0M Feb 16 22:37 60dayREGusers_sep_oct2003.MYD 9.5M Feb 16 22:37 60dayREGusers_sep_oct2003.MYI however the ProfileIDValue_REGID table is rather large. 8.4k Sep 15 09:44 ProfileIDValue_REGID.frm 499M Feb 16 22:17 ProfileIDValue_REGID.MYD 443M Feb 16 22:18 ProfileIDValue_REGID.MYI 8.4k Nov 13 11:47 UniqueID_oct2003.frm 32M Nov 13 11:53 UniqueID_oct2003.MYD 51M Nov 13 11:53 UniqueID_oct2003.MYI 8.4k Jan 9 10:50 UniqueID_sep2003.frm 34M Jan 9 10:58 UniqueID_sep2003.MYD 55M Jan 9 10:58 UniqueID_sep2003.MYI Anyone have any thoughts? ERROR: 1114 The table '#sql_931_0' is full 040225 15:34:25 Slave: error 'The table '#sql_931_0' is full' on query 'create table 60dayREGusers_sep_oct2003 (primary key(UniqueID)) select distinct UniqueID_sep2003.UniqueID from UniqueID_sep2003,ProfileIDValue_REGID where UniqueID_sep2003.UniqueID=ProfileIDValue_REGID.UniqueID union select distinct UniqueID_oct2003.UniqueID from UniqueID_oct2003,ProfileIDValue_REGID where UniqueID_oct2003.UniqueID=ProfileIDValue_REGID.UniqueID', error_code=1114 Looks like a bug to me. MySQL should be able to figure out it needs to use the disk when an in-memory temp table exceeds tmp_table_size without SQL_BIG_TABLES. It would be nice if you could create a test case for it and submit it to MySQL developers. For now, try increasing tmp_table_size ( make sure you have enough RAM + swap space to deal with it, though). If re-writing the query is an option, I would also try it without UNION, which is a fairly new feature and could still have a few quirks. -- Sasha Pachev Create online surveys at http://www.surveyz.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
problem with 4.0.18
Hi folks, my system: openbsd 3.3 mysql ver: 4.0.18 Well, I was working on phpBB conf, when I've seen errors from mysql: mysql use mysql Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Didn't find any fields in table 'columns_priv' Didn't find any fields in table 'db' Didn't find any fields in table 'func' Didn't find any fields in table 'host' Didn't find any fields in table 'tables_priv' Didn't find any fields in table 'user' Database changed mysql select * from user; ERROR 1105: File './mysql/user.MYD' not found (Errcode: 9) But I've all as before: /usr/local/mysql/var: observe# ls cacti ib_logfile1 newsys observe.nesys.it.pid ib_arch_log_00 ibdata1 observe.log training ib_logfile0 mysql observe.nesys.it.err what about this? where's my mistake? I hope you could help me, now all is down. thanks for support Regards Andrea -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query help - add results then divide by
Rogers, Dennis wrote: Good afternoon, How can I take the 3 results below add them together then divide by 131.77? Can it all be done in one SQL statement? Thanks in advance. mysql describe ads; +---+---+--+-+++ | Field | Type | Null | Key | Default| Extra | +---+---+--+-+++ | adID | int(11) | | PRI | NULL | auto_increment | | page | int(11) | | | 0 | | | adnum | varchar(20) | | || | | date | date | | | -00-00 | | | depth | decimal(3,2) | YES | | 0.00 | | | timestamp | timestamp(14) | YES | | NULL | | | col | int(11) | YES | | 0 | | | acc | varchar(50) | | || | | editionID | int(11) | | | 0 | | +---+---+--+-+++ 9 rows in set (0.00 sec) mysql SELECT ((ads.col * 1.91) * ads.depth) FROM ads Where date = '2004-02-26' AND editionID = '13' AND ads.page = '16'; +-+ | ((ads.col * 1.91) * ads.depth) | +-+ |7.64 | | 34.38 | |7.64 | +-+ 3 rows in set (0.01 sec) SELECT SUM((ads.col * 1.91) * ads.depth)/131.77 FROM ads Where date = '2004-02-26' AND editionID = '13' AND ads.page = '16'; -- Sasha Pachev Create online surveys at http://www.surveyz.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: OSX 10.3 Binaries and 64 Bit
Bruce Dembecki wrote: Hi! One of my associates here read a report somewhere that mysqld when compiled under OS X 10.3 was 40%+ more efficient due to improvements in the compilers and the way 10.3 work. Also 10.3 is a 64 Bit Operating System and it would be a major benefit to us to set some of the memory values in excess of 2MBytes on our 64 Bit G5 servers. Sadly my associate can't find the original report any longer, so I don't have the reference material or exact information. MySQL themselves repeatedly says we are better off using MySQL compiled binaries than compiling our own. So then how can we take advantage of these OSX 10.3 compiler gains when MySQL is only offering 10.2 compiled versions on their web site. Which brings me to my questions... If the OSX 10.3 Compilers are so much more efficient and result in major performance gains how long will it be before MySQL starts providing a MySQL binary compiled for OSX 10.3? Back when I worked for MySQL we were experts at building x86 Linux binaries, were good with FreeBSD and Sparc Solaris, plowed our way through on Windows, and tried our luck with lots of struggle and varying degrees of success everywhere else. I would suspect things have not changed very much in the last 10 months. So, if you have a weird system like OSX, do not be afraid to build your own binary on it. -- Sasha Pachev Create online surveys at http://www.surveyz.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: OSX 10.3 Binaries and 64 Bit
Hi Bruce, On Thu, 2004-02-26 at 06:26, Bruce Dembecki wrote: Hi! One of my associates here read a report somewhere that mysqld when compiled under OS X 10.3 was 40%+ more efficient due to improvements in the compilers and the way 10.3 work. Also 10.3 is a 64 Bit Operating System and it would be a major benefit to us to set some of the memory values in excess of 2MBytes on our 64 Bit G5 servers. Sadly my associate can't find the original report any longer, so I don't have the reference material or exact information. MySQL themselves repeatedly says we are better off using MySQL compiled binaries than compiling our own. So then how can we take advantage of these OSX 10.3 compiler gains when MySQL is only offering 10.2 compiled versions on their web site. Which brings me to my questions... If the OSX 10.3 Compilers are so much more efficient and result in major performance gains how long will it be before MySQL starts providing a MySQL binary compiled for OSX 10.3? Along with regular 10.3 is it possible MySQL will offer Binaries for 10.3 64 bit for those people with G5 servers (and with Apple being a major sponsor of the forthcoming Users conference and making a big deal about the new G5 servers I would think they have a major interest in this issue too). This should certainly be possible. I have forwarded your message to our build team. In the absence of these apparently faster improved versions of MySQL if we did want to make our own binaries using 10.3 can some talk us Mac guys (who haven't had the long history of building our own binaries most of the Unix guys have) through the exact process of getting the source code and compiling it on our systems? The general compile process is documented in the manual at www.mysql.com/doc/, with specifics for various platforms. I won't speculate what the specific issues for this new environment might be. When the build engineers have the details, we'll update the manual accordingly. Regards, Arjen. -- Arjen Lentz, Technical Writer, Trainer Brisbane, QLD Australia MySQL AB, www.mysql.com Sydney 7 Jun 2004 (5 days): Using Managing MySQL Training Training,Support,Licenses,T-shirts @ https://order.mysql.com/?marl -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqldump
Hello All, How can I dump selected rows into a file (using a query or mysqldump)? i.e, I want to dump only the rows of this query: SELECT * FROM tbl WHERE id100 AND id200; thanks in advance, -Lorderon. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump
At 2:50 +0200 2/26/04, Lorderon wrote: Hello All, How can I dump selected rows into a file (using a query or mysqldump)? i.e, I want to dump only the rows of this query: SELECT * FROM tbl WHERE id100 AND id200; mysqldump --help shows that it takes a --where / -w option. So: mysqldump -w id100 and id 200 db_name tbl_name might do the job. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com MySQL Users Conference: April 14-16, 2004 http://www.mysql.com/uc2004/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query error in Access
Hello Everyone, If I run the following query in MySQL Control Center or MySQL-Front it works correctly, SELECT -1 AS ProductID, Add New Part AS PartNumber, AS VendorPartNo, AS Description, AS VendorStatus FROM Products UNION SELECT ProductID, PartNumber, If(SubNo=1135, VendorPart,AltVendorPart) AS VendorPartNo, Description, If(SubNo=1135, Primary,Alternate) AS VendorStatus FROM Products WHERE ((Obsolete=0) AND (SubNo=1135)) OR ((AltSubNo=1135)) ORDER BY ProductID, VendorPartNo, VendorStatus DESC; If I run the same query in MSAccess, where my user interface is, I get the following error, [MySQL][ODBC 3.51 Driver][mysqld-4.1.1-alpha-log]You have an error in your SQL syntax. Check the manual that corresponds to you MySQL server version for the syntax to use near 'Description FROM products WHERE (((Obsolete = 0 ) AND (SubNo = (#1064) My log file shows the following, 1163 Query (SELECT ProductID ,NSIPartNumber ,,Description FROM products WHERE (((Obsolete = 0 ) AND (SubNo = 1135 ) ) OR (AltSubNo = 1135 ) ) ) UNION (SELECT -1 ,'Add New Part' ,'' ,'' FROM products ) I'm aware of the difference between Access and MySQL regarding the IIF versus IF and I've tried the query both ways with no success. SubNo is a valid ID. In both MySQL Control Center or MySQL-Front this query returns 58 records in about on third of a second. Any thoughts?
Re: Query Problems
Tried to make the indexes separate and did an EXPLAIN and no performance increase and this is what the explain says: id select_type table typepossible_keys key key_len ref rowsExtra 1 SIMPLE tb ALL PRIMARY,tb_ndx3,tb_ndx4,tb_ndx5 NULLNULLNULL24238 Using where 1 SIMPLE tb2 ALL PRIMARY,tb_ndx1,tb_ndx2,tb_ndx3 NULLNULLNULL24063 Using where 1 SIMPLE c eq_ref PRIMARY,c_ndx1 PRIMARY 4 Table1.key 1NULL Not sure exactly what this means but the fact that it isn't using keys seems to me that it would be a problem. Could it be the JOIN condition, which in this case is (tb.a = tb2.a OR (tb.b = tb2.b AND tb.c = tb2.c)) Seems like this should run rather smoothly but I've never done JOIN conditions like this before. -- At 05:33 PM 2/25/2004, Daniel Clark wrote: I know Oracle likes the indexes separatly, but mySQL might like combinations. No, we tried individual indexes and then one big grouped index but not individual indexes on each of the fields. Adding the index actually added a few seconds to the query so we weren't sure if that was the way to go. I'll try this, though. Eric At 10:36 AM 2/25/2004 -0800, Daniel Clark wrote: Do you have separate indexes on: Table1.ID Table2.ID Table1.Field1 Table2.Field1 Table1.Field1 Table1.Field2 Select* FROM Table1 as a INNER JOIN Table2 as b ON (a.ID = b.ID or (a.Field1 = b.Field1 and a.Field2 = b.Field2)) WHERE bla bla bla We have tried to set up indexes and the query still takes 8 minutes to run. It only returns 6,000 records. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump
Something like that: SELECT - INTO OUTFILE 'filename' FROM Table etc etc ; Marcelo Araujo On Wednesday 25 February 2004 21:50, Lorderon wrote: Hello All, How can I dump selected rows into a file (using a query or mysqldump)? i.e, I want to dump only the rows of this query: SELECT * FROM tbl WHERE id100 AND id200; thanks in advance, -Lorderon. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: default encrypt for PASSWORD
At 15:02 -0700 2/25/04, Colleen Dick wrote: I'm sure this is in the manual somewhere or in the archives, but I'm not finding it and I bet someone easily knows the short answer: Using 3.23 setting a varchar field to PASSWORD(secret) Having altered nothing regarding encryption in the server what is the default encryption type for PASSWORD? cuz I told PEAR::Auth that the encryption type is md5 and if that is wrong that could be the reason why it is not working. If you want MD5 encryption, you could use the MD5() function. PASSWORD() uses encryption that is intended for use with MySQL account management, not for general application encryption. http://www.mysql.com/doc/en/Encryption_functions.html -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com MySQL Users Conference: April 14-16, 2004 http://www.mysql.com/uc2004/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Counting rows when order is ambiguous
Say I have this query: SELECT * FROM topics ORDER BY lastPostTime DESC; How would I modify it to answer the question How many rows would be returned before the row that has topics.id = $x? I was thinking of something like this: $xPostTime = SELECT lastPostTime FROM topics WHERE id = $x; SELECT COUNT(*) FROM topics WHERE lastPostTime $xPostTime; but this statement breaks down in the case where there are rows having lastPostTime = $xPostTime. Would I have to do something un-portable like this: SELECT COUNT(*) FROM topics WHERE lastPostTime $xPostTime OR (lastPostTime = $xPostTime AND id $x); or could I do something more elegant that looks like: SELECT COUNT(*) FROM topics ORDER BY lastPostTime DESC HAVING something that's true iff it comes before row with id = $x; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query optimization help
I've got a query that I can't seem to get optimized, so I'm hoping someone here can spot something I've missing! Table has three columns: CoordID int unsigned, Zip_Lo char(9), Zip_Hi char(9) Table has 3 million records indexes: acg_lo (Zip_Lo) acg_hi (Zip_Hi) acg_combined (Zip_Lo, Zip_Hi) Here's the query: select * from acg where zip4_lo_pot = '80128' and zip4_hi_pot = '80128' Explain shows: type: ALL possible keys: acg_lo,acg_hi,acg_combined rows: 3022309 extra: Using where So, how can I optimize this? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query optimization help
Maybe i'm wrong here, someone correct me, if its just int's you are gonna use set the field types to bigint it may search faster you are doing a character search, to get there quicker in a text search scenerio i'd suggest mysql4 and full text searching MATCH AGAINST I've got a query that I can't seem to get optimized, so I'm hoping someone here can spot something I've missing! Table has three columns: CoordID int unsigned, Zip_Lo char(9), Zip_Hi char(9) Table has 3 million records indexes: acg_lo (Zip_Lo) acg_hi (Zip_Hi) acg_combined (Zip_Lo, Zip_Hi) Here's the query: select * from acg where zip4_lo_pot = '80128' and zip4_hi_pot = '80128' Explain shows: type: ALL possible keys: acg_lo,acg_hi,acg_combined rows: 3022309 extra: Using where So, how can I optimize this? -- 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: default encrypt for PASSWORD
Paul DuBois wrote: At 15:02 -0700 2/25/04, Colleen Dick wrote: I'm sure this is in the manual somewhere or in the archives, but I'm not finding it and I bet someone easily knows the short answer: Using 3.23 setting a varchar field to PASSWORD(secret) Having altered nothing regarding encryption in the server what is the default encryption type for PASSWORD? cuz I told PEAR::Auth that the encryption type is md5 and if that is wrong that could be the reason why it is not working. If you want MD5 encryption, you could use the MD5() function. PASSWORD() uses encryption that is intended for use with MySQL account management, not for general application encryption. http://www.mysql.com/doc/en/Encryption_functions.html Ah... so I shouldn't use it except for the grant tables..I guess I just carried my admin habits over into the app arena. OK then, MD5 is fine with me. Poof they're all changed. And the users authenticate too. THANKS FOR YOUR ANSWER! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
