RE: Codes for U.S. Counties
You should try the USGS or the USPS sites. I would expect that either of them might offer a set of standard codes for identifying counties from the US. Dave -Original Message- From: David Blomstrom [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 02, 2004 9:22 PM To: [EMAIL PROTECTED] Subject: Codes for U.S. Counties I have a big database focusing on U.S. counties. I'd like to assign each county a code for use as a key. I'm thinking of simply using the state postal code, followed by numerals. For example, Arizona's counties would be arranged alphabetically, beginning with az1, az2, az3, etc. I just wondered if anyone on this list has worked with counties in databases and is aware of a pre-existing code system that's in fairly wide use. It would be nice to make a database that's compatible with other databases, if they share a common code for counties. Thanks. __ Do you Yahoo!? Friends. Fun. Try the all-new Yahoo! Messenger. http://messenger.yahoo.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Re: Codes for U.S. Counties
I don't know if this will help, but I had these links left over from some work I did a few years ago... http://www.itl.nist.gov/fipspubs/fip6-4.htm http://www.itl.nist.gov/fipspubs/co-codes/states.htm On Wed, 2 Jun 2004, David Blomstrom wrote: I have a big database focusing on U.S. counties. I'd like to assign each county a code for use as a key. I'm thinking of simply using the state postal code, followed by numerals. For example, Arizona's counties would be arranged alphabetically, beginning with az1, az2, az3, etc. I just wondered if anyone on this list has worked with counties in databases and is aware of a pre-existing code system that's in fairly wide use. It would be nice to make a database that's compatible with other databases, if they share a common code for counties. Thanks. __ Do you Yahoo!? Friends. Fun. Try the all-new Yahoo! Messenger. http://messenger.yahoo.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Exporting/Importing Databases
David Blomstrom [EMAIL PROTECTED] wrote: --- Robert A. Rosenberg [EMAIL PROTECTED] wrote: At 20:36 -0700 on 06/02/2004, David Blomstrom wrote about Exporting/Importing Databases: Third, my practice database includes half a dozen tables, but only one made it online, followed by this error message: Database USERNAME running on localhost Error SQL-query : CREATE TABLE `counties` ( `SCode` varchar( 6 ) NOT NULL default '', `NameC` varchar( 255 ) default NULL , `TypeC` varchar( 255 ) default NULL , `Seat` varchar( 255 ) default NULL , `Area_MI` decimal( 10, 1 ) default NULL , `Area_KM` decimal( 10, 1 ) default NULL , `Pop_2000` int( 10 ) default NULL , `Pop_1990` int( 10 ) default NULL , `Pop_MI` decimal( 10, 1 ) default NULL , `Pop_KM` decimal( 10, 1 ) default NULL , `Race1` int( 10 ) default NULL , `Race2` int( 10 ) default NULL , `Amerindian` int( 10 ) default NULL , `White` int( 10 ) default NULL , `Black` int( 10 ) default NULL , `Asian` int( 10 ) default NULL , `Pacific_Island` int( 6 ) default NULL , `Some_Other_Race` int( 10 ) default NULL , `Hispanic` int( 10 ) default NULL , `id` int( 6 ) NOT NULL AUTO_INCREMENT , PRIMARY KEY ( `id` ) , KEY `SCode` ( `SCode` ) , CONSTRAINT `0_132` FOREIGN KEY ( `SCode` ) REFERENCES `statesarticles` ( `SCode` ) ON UPDATE CASCADE ) TYPE = InnoDB AUTO_INCREMENT =3143 MySQL said: #1005 - Can't create table './[USERNAME]/counties.frm' (errno: 150) Do you know what this means? You just got caught with the old Referential Integrity Gotcha. It will not allow you to create countries since statesarticles does not (yet) exist. The fix is to temporally turn off RI during the load. Put this command at the start of your file: SET FOREIGN_KEY_CHECKS = 0; and this one at the end: SET FOREIGN_KEY_CHECKS = 1; and it will work. OK, is this something I can do in phpMyAdmin or another software program, or do I have to open the MySQL file itself? I've scarcely touched MySQL files, but I found the file counties.frm, which I assume I would open with Notepad, right? And then I can make SET FOREIGN_KEY_CHECKS = 0; the very first line and SET FOREIGN_KEY_CHECKS = 1; the very last line, after which I would EXPORT my database, then import it, right? And since I like to plan ahead, is it OK to insert these two lines in ALL my MySQL documents, just to be prepared for this error? If I inadvertently stick these lines in a file that doesn't have a foreign key, will it cause a problem? Finally, after I've imported my database online, do I have to go back and remove these two lines, or can I just leave them there indefinitely as a safeguard? You should put these lines to the dump file that contains CREATE TABLE statements, not to the *.frm files. Then load tables. If you use command-line client you can do: mysql SET FOREIGN_KEY_CHECKS = 0; mysql SOURCE file_name; mysql SET FOREIGN_KEY_CHECKS = 1; -- 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]
My question is too idiot for nobody answer?
-Mensagem original- De: Renato Cramer Enviada em: quarta-feira, 2 de junho de 2004 11:07 Para: Lista MySQL (E-mail) Assunto: Restore from Backup done with Replication features Dear Friends, Considering what a backup is done with the method following: 1. One master server replicate to one slave server. 2. The master server handle all manipulations and transactions of data (all tables are InnoDB type). 3. The slave server is exclusively used for to make backups. 4. The replication of the slave server is interrupted (stop slave statement). 5. The slave server is correctly shutdown. 6. One copy of the MySQL's data directory is done via operating system commands. 7. The slave server is started. 8. The replication of the slave server is started (start slave statement). In case problems with the master server, where is needed restore data from backup, is done the following: A. One copy is restored to MySQL's data directory in master server and also in slave server. B. The master server is started. C. The slave server is started. D. The replication of the slave server is started. My questions are: - The copy restored (A) will function in both servers? - The restart of the slave's replication (D) must be done with reset slave or start slave statement? - Exist a better method (1-8) to make consistents backups of the transactional databases without interrupt or block the master server and without use the InnoDB Hot Backup tool? All sugestions will be welcome. Thanks in advance, Renato Cramer. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Unicode characters become question marks
Are you characters being escaped before being stored?
\ucharacter-code?
-Original Message-
From: Silvio Lopes de Oliveira
To: Victor Pendleton; James Huang ; [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Sent: 6/2/04 6:24 PM
Subject: RE: Unicode characters become question marks
You know, now I'm sure that the chars are getting stored as '?' as well.
I tried the test you suggested again, but with a small modification. I
typed:
SELECT IF(networkname='?', 1, 0) from networktable;
and it returned 1. Because I used '?' instead of the chinese char and it
matched, then obviously the stored character is a '?'. So my conclusion
is the same as James Huang's; the problem happens when the string is
stored. But no solution yet, though.
S Lopes
-Original Message-
From: Victor Pendleton [mailto:[EMAIL PROTECTED]
Sent: Wednesday, June 02, 2004 10:09 AM
To: Silvio Lopes de Oliveira; Victor Pendleton; 'James Huang ';
'[EMAIL PROTECTED] '
Subject: RE: Unicode characters become question marks
If you can type the character into the keyboard try this.
SELECT IF(col1=chinese-character, 1, 0)
Else try this from a java program
if (rset.getString(col1).equals(chinese-character))
{
System.out.println(match);
}
else
{System.out.println(invalid);
}
-Original Message-
From: Silvio Lopes de Oliveira
To: Victor Pendleton; James Huang ; [EMAIL PROTECTED]
Sent: 6/2/04 11:59 AM
Subject: RE: Unicode characters become question marks
Yes, my display can handle the Chinese characters. I have also changed
the application font of MySQL Control Center to SimSun, which supports
all the Chinese characters I am using. When I type the characters in
MySQL Control Center, I see the Chinese characters. I edit a varchar
field in an existing record, I type the Chinese content, hit enter, save
the table, and I still see the Chinese characters. When I requery the
table, the Chinese characters have become question marks.
As for verifying whether the correct Unicode is being stored, how do I
do that? All I can see once I requery are the question marks. I don't
know whether the Unicode is being stored as question marks, or whether
it is stored correctly and is getting converted to question marks when
the stored data is retrieved.
S Lopes
-Original Message-
From: Victor Pendleton [mailto:[EMAIL PROTECTED]
Sent: Wednesday, June 02, 2004 9:51 AM
To: 'James Huang '; Silvio Lopes de Oliveira; '[EMAIL PROTECTED] '
Subject: RE: Unicode characters become question marks
Can you display properly handle the Chinese characters? I would try to
verify that the correct unicode code is being stored.
-Original Message-
From: James Huang
To: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Sent: 6/2/04 11:45 AM
Subject: RE: Unicode characters become question marks
I saw the same problem with 5.0 alpha and Java/JDBC. The text was
Chinese
characters in Java; the tables were created with default character set
UTF8.
Seems only questions marks are stored.
Wondering if far-east characters in UTF8 are support by MySQL's UTF8
support?
-James
From: Silvio Lopes de Oliveira [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Subject: Unicode characters become question marks
Date: Wed, 2 Jun 2004 09:39:14 -0700
MySQL Server: 4.1.1 alpha
MySQL Control Center: 0.9.4 beta
I am not sure whether this is a Control Center or MySQL Server problem,
but
here it goes:
I have a database with MyISAM tables created using character set UTF-8.
I
have installed support for Chinese on my machine, and using MySQL
Control
Center I entered some values in Chinese for some varchar fields. I had
also
changed the app font for Control Center to SimSun, which supports
Chinese
characters. When I requery the table, the Chinese characters have been
changed to question marks. I expected, of course, that the Chinese
characters would be displayed.
I tried this to access the data programatically (using an MFC app and
ODBC
Connector) and it also shows question marks. I'm not sure whether the
conversion to question marks occurs when the data is stored into the
table,
or when the data is retrieved.
I found the following discussion thread debating what seems to be a
similar
issue, but it was not clear whether to me they ever determined a
solution
or if it is a bug:
http://lists.mysql.com/mysql/164067
Here are the values for my character set variables:
+--+--+
| Variable_name| Value|
+--+--+
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_set_database | utf8 |
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character-sets-dir | C:\mysql\share\charsets/ |
| character_set_results| utf8 |
+--+--+
I
RE: My question is too idiot for nobody answer?
If you choose to copy the files I would ensure that you are getting the innodb binary logs as well if they do not exists in the mysql data directory. The innodb backup tool or even mysqldump may provide a more consistent snapshot. ... If you restore to the master and you want to ensure a replication is starts again one solution is: Master Server: *Apply backup *Clear binary logs *Start master Slave Server: *Apply backup *Clear binary and relay logs *Delete master.info file *Start slave *Check to see if slave is running and connected to the master -Original Message- From: Renato Cramer To: Lista MySQL (E-mail) Sent: 6/3/04 6:52 AM Subject: My question is too idiot for nobody answer? -Mensagem original- De: Renato Cramer Enviada em: quarta-feira, 2 de junho de 2004 11:07 Para: Lista MySQL (E-mail) Assunto: Restore from Backup done with Replication features Dear Friends, Considering what a backup is done with the method following: 1. One master server replicate to one slave server. 2. The master server handle all manipulations and transactions of data (all tables are InnoDB type). 3. The slave server is exclusively used for to make backups. 4. The replication of the slave server is interrupted (stop slave statement). 5. The slave server is correctly shutdown. 6. One copy of the MySQL's data directory is done via operating system commands. 7. The slave server is started. 8. The replication of the slave server is started (start slave statement). In case problems with the master server, where is needed restore data from backup, is done the following: A. One copy is restored to MySQL's data directory in master server and also in slave server. B. The master server is started. C. The slave server is started. D. The replication of the slave server is started. My questions are: - The copy restored (A) will function in both servers? - The restart of the slave's replication (D) must be done with reset slave or start slave statement? - Exist a better method (1-8) to make consistents backups of the transactional databases without interrupt or block the master server and without use the InnoDB Hot Backup tool? All sugestions will be welcome. Thanks in advance, Renato Cramer. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to retrieve MySQL Warning Messages
Hi, Mysql 4.018 , SuSE 8.2. I'm using LOAD DATA LOCAL INFILE //.txt INTO TABLE zz_zzz ; and I get the following message Query OK, 158063 rows affected (1 min 36.49 sec) Records: 158063 Deleted: 0 Skipped: 0 Warnings: 158115 Nothing seems to be wrong with the table after executing the command. I would like to know the cause of the WARNINGS or what is the text corresponding to those warnings Is that possible? Thank you Mauricio -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to retrieve MySQL Warning Messages
You need version 4.1.x and greater. Then issue SHOW WARNINGS after the statement that generated the warnings. -Original Message- From: Mauricio Pellegrini To: MySql List Sent: 6/3/04 11:20 AM Subject: How to retrieve MySQL Warning Messages Hi, Mysql 4.018 , SuSE 8.2. I'm using LOAD DATA LOCAL INFILE //.txt INTO TABLE zz_zzz ; and I get the following message Query OK, 158063 rows affected (1 min 36.49 sec) Records: 158063 Deleted: 0 Skipped: 0 Warnings: 158115 Nothing seems to be wrong with the table after executing the command. I would like to know the cause of the WARNINGS or what is the text corresponding to those warnings Is that possible? Thank you Mauricio -- 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]
RES: How to retrieve MySQL Warning Messages
Hello Mauricio, I tested in version 4.1.0 and not function. Only in version 4.1.1, according to text below extracted from MySQL manual: Note that the framework for warnings was added in MySQL 4.1.0, at which point many statements did not generate warnings. In 4.1.1, the situation is much improved, with warnings generated for statements such as LOAD DATA INFILE and DML statements such as INSERT, UPDATE, CREATE TABLE, and ALTER TABLE. Regards, Renato Cramer. -Mensagem original- De: Mauricio Pellegrini [mailto:[EMAIL PROTECTED] Enviada em: quinta-feira, 3 de junho de 2004 13:20 Para: MySql List Assunto: How to retrieve MySQL Warning Messages Hi, Mysql 4.018 , SuSE 8.2. I'm using LOAD DATA LOCAL INFILE //.txt INTO TABLE zz_zzz ; and I get the following message Query OK, 158063 rows affected (1 min 36.49 sec) Records: 158063 Deleted: 0 Skipped: 0 Warnings: 158115 Nothing seems to be wrong with the table after executing the command. I would like to know the cause of the WARNINGS or what is the text corresponding to those warnings Is that possible? Thank you Mauricio -- 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: Self-Join Query
You may want to forget all those joins and filters to create two columns, which is probably making MySQL do multiple full table scans. It sounds like what you are really looking for is to separate your hits into 2 columns based on the ip address, and perhaps put a filter on the hit time or page name. Create the counts for your separate columns by putting a condition in your column selection, setting the value for the column to a 1 or 0 depending on internal or external address and then sum those 1s and 0s. SELECT page_name, SUM(if(ip LIKE '10.%' OR ip LIKE '139.230.%',1,0)) as Internal SUM(if(ip NOT LIKE '10.%' AND ip NOT LIKE '139.230.%',1,0)) as External FROM ip_logs GROUP BY page_name That should be pretty quick and putting a WHERE condition on it should just making things faster, since the selection is being narrowed and indexes can be used. On Jun 2, 2004, at 11:35 PM, James KATARSKI wrote: Hi All, I'm attempting to generate a report of page hits from both internal and external IP addresses, from one table, using self join. Some sample data: ++-+++ | hit_no | page_name | ip | hit_time | ++-+++ | 6649 | printing| 10.77.1.128| 1061362239 | | 6650 | wireless| 10.77.28.100 | 1061365331 | | 6651 | after_hours | 10.77.31.101 | 1061365461 | | 6632 | labtimes| 10.77.25.102 | 1061350012 | | 6633 | after_hours | 10.77.25.102 | 1061350017 | | 6634 | labtimes| 10.77.25.102 | 1061350325 | | 6635 | practise| 10.77.30.114 | 1061350609 | | 6636 | support | 10.19.7.155| 1061352345 | | 6637 | help| 203.35.134.16 | 1061352351 | | 6638 | support | 10.19.7.156| 1061352352 | | 6639 | support | 10.19.7.151| 1061352387 | | 6640 | support | 10.19.7.159| 1061352411 | | 6621 | support | 10.19.7.158| 1061348961 | | 6620 | support | 10.19.6.112| 1061348628 | | 7318 | labtimes| 202.137.192.7 | 1063262879 | | 6284 | conditions | 10.77.31.109 | 1060605402 | | 7317 | practise| 202.137.192.7 | 1063262789 | | 7316 | wireless| 203.59.185.185 | 1063262707 | | 7315 | wireless| 10.77.28.121 | 1063256685 | ++-+++ I'm trying to generate a report like this: (Which I've done in TWO querys, the copied and pasted together) +-+--+--+ | Page Name | Internal | External | +-+--+--+ | after_hours | 615 | 105 | | conditions | 332 | 50 | | faq | 89 |2 | | help| 458 | 174 | | labtimes| 682 | 143 | | support | 2151 | 383 | | passwords | 154 | 22 | | practise| 497 | 99 | | printing| 801 | 85 | | wireless| 926 | 180 | +-+--+--+ Using a query like this: SELECT DISTINCT i.page_name AS 'Page Name', COUNT(i.page_name) as 'Internal', COUNT(e.page_name) as 'External' FROM ip_logs i, ip_logs e WHERE (i.hit_no != e.hit_no) AND (i.ip LIKE '10.%' OR i.ip LIKE '139.230.%') #Internal Addresses AND (e.ip NOT LIKE '10.%' AND e.ip NOT LIKE '139.230.%') #External Addresses GROUP BY i.page_name; The query takes between 30 seconds and 4.5 minutes to process 6000 rows, depending upon what extra WHERE conditions I put in (like i.hit_no = e.hit_no, or i.page_name = e.page_name etc), the result of which looks like: +-+--+--+ | Page Name | Internal | External | +-+--+--+ | after_hours | 638520 | 638520 | | conditions | 353064 | 353064 | | faq | 108924 | 108924 | | help| 355568 | 355568 | | labtimes| 674828 | 674828 | | oracle | 2213536 | 2213536 | | passwords | 165264 | 165264 | | practise| 498296 | 498296 | | printing| 896432 | 896432 | | wireless| 933992 | 933992 | +-+--+--+ Can anyone offer any suggestions as to the problem with my logic? Regards, James Katarski Systems Administrator School of Computer Information Science Edith Cowan University ML -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- 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: Self-Join Query
In article [EMAIL PROTECTED], James KATARSKI [EMAIL PROTECTED] writes: I'm trying to generate a report like this: (Which I've done in TWO querys, the copied and pasted together) +-+--+--+ | Page Name | Internal | External | +-+--+--+ | after_hours | 615 | 105 | | conditions | 332 | 50 | | faq | 89 |2 | | help| 458 | 174 | | labtimes| 682 | 143 | | support | 2151 | 383 | | passwords | 154 | 22 | | practise| 497 | 99 | | printing| 801 | 85 | | wireless| 926 | 180 | +-+--+--+ Using a query like this: SELECT DISTINCT i.page_name AS 'Page Name', COUNT(i.page_name) as 'Internal', COUNT(e.page_name) as 'External' FROM ip_logs i, ip_logs e WHERE (i.hit_no != e.hit_no) AND (i.ip LIKE '10.%' OR i.ip LIKE '139.230.%') #Internal Addresses AND (e.ip NOT LIKE '10.%' AND e.ip NOT LIKE '139.230.%') #External Addresses GROUP BY i.page_name; You could try something like SELECT page_name AS Page Name, sum(CASE WHEN ip LIKE '10.%' OR ip LIKE '139.230.%' THEN 1 ELSE 0 END) AS Internal, sum(CASE WHEN ip LIKE '10.%' OR ip LIKE '139.230.%' THEN 0 ELSE 1 END) AS External FROM tst1 GROUP BY page_name If you have an index on ip, this should be pretty fast. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: RES: How to retrieve MySQL Warning Messages
Mauricio, When I had a similar problem, I located it by using MS Excel to compare the original csv file to the results of a SELECT * INTO OUTFILE csv file of the MySQL data. The Excel formula I used was =a1=ab1 , where a1 and ab1 are the two cells you want to compare. I found it to be a field formatting problem in my case. I was using 'float' when I needed 'double' in the field definitions. MySQL noted an error for each instance where the results were not *exactly* the same as the input data. You have a larger dataset than I was using, but you could use the same method with just a few lines of the input data to see if there is an obvious error. Dan -Mensagem original- De: Mauricio Pellegrini [mailto:[EMAIL PROTECTED] Enviada em: quinta-feira, 3 de junho de 2004 13:20 Para: MySql List Assunto: How to retrieve MySQL Warning Messages Hi, Mysql 4.018 , SuSE 8.2. I'm using LOAD DATA LOCAL INFILE //.txt INTO TABLE zz_zzz ; and I get the following message Query OK, 158063 rows affected (1 min 36.49 sec) Records: 158063 Deleted: 0 Skipped: 0 Warnings: 158115 Nothing seems to be wrong with the table after executing the command. I would like to know the cause of the WARNINGS or what is the text corresponding to those warnings Is that possible? Thank you Mauricio -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: JOINing complication, help please
(To the esteemed members of the MySQL mailing list, my sincere apologies
for the length of this post-- SG)
Luc,
You are killing yourself with all of those decisions.
Take this for example:
, IF(
CONTACT_X_CUSTOMER.ID_ADDRESS 0
, IF(
CONTACT_X_CUSTOMER_ADDRESS.AddrLine2 IS NULL OR
TRIM(CONTACT_X_CUSTOMER_ADDRESS.AddrLine2) = ''
, ''
, CONCAT(
' '
, CONTACT_X_CUSTOMER_ADDRESS.AddrLine2
)
)
, IF(
CUSTOMER.ID_ADDRESS_SHIPTO 0
, IF(
SHIPTO_ADDRESS.AddrLine2 IS NULL OR
TRIM(SHIPTO_ADDRESS.AddrLine2) = ''
, ''
, CONCAT(
' '
, SHIPTO_ADDRESS.AddrLine2
)
)
, IF(
MAIN_ADDRESS.AddrLine2 IS NULL OR
TRIM(MAIN_ADDRESS.AddrLine2) = ''
, ''
, CONCAT(
' '
, MAIN_ADDRESS.AddrLine2
)
)
)
)
can all be condensed to
RTRIM(ISNULL(CONCAT(' ',if(CONTACT_X_CUSTOMER.ID_ADDRESS
0,CONTACT_X_CUSTOMER_ADDRESS.AddrLine2, IF(CUSTOMER.ID_ADDRESS_SHIPTO 0,
SHIPTO_ADDRESS.AddrLine2, MAIN_ADDRESS.AddrLine2))),''))
Breaking that down from the inside out:
Choose which field to return based on the available IDs: IF(IF())
Add leading spaces: CONCAT()
Check for NULL. If it is, make it an empty string: ISNULL()
Collapse any remaining strings that are all blanks to an empty string but
leave your leading spaces: RTRIM()
Your JOINs seem OK, you have 3 sets of addresses to check and 4 tables per
address so 12 joins is not unusual for your data set. I am not fond of the
big IF() clause in your 3rd join but I think you said in a follow-up letter
that you didn't mean for that to be in there so I am not sure if I need to
comment on it or not.
You may end up with a faster set of results and code that's easier to
maintain if you UNION the three sets of addresses together rather than try
to return everything all at once. Something like:
(SELECT CONTACT_X_CUSTOMER.ID
, CONTACT.LastName AS 'Last Name'
, CONTACT.FirstName AS 'First Name'
, CONTACT_X_CUSTOMER.Email AS 'Email'
, CONTACT_X_CUSTOMER.Active AS 'Active'
, ADDRESS.AddrLine1
+ RTRIM(CONCAT(' ',ISNULL(ADDRESS.AddrLine2,'')))
+ RTRIM(CONCAT(' ',ISNULL(ADDRESS.AddrLine3,''))) as 'Address'
, RTRIM(CONCAT(' ',ISNULL(CITY.Name,''))) as 'City'
, RTRIM(CONCAT(' ',ISNULL(PROVINCE.Name,''))) as 'Province'
, RTRIM(CONCAT(' ',ISNULL(COUNTRY.Name,''))) as 'Country'
, RTRIM(CONCAT(' ',ISNULL(ADDRESS.PostalCode,''))) as 'Postal Code'
. 'Contact Address' as 'Address Type'
FROM CONTACT_X_CUSTOMER
LEFT JOIN CONTACT
ON CONTACT_X_CUSTOMER.ID_CONTACT = CONTACT.ID
LEFT JOIN CUSTOMER
ON CONTACT_X_CUSTOMER.ID_CUSTOMER = CUSTOMER.ID
LEFT JOIN ADDRESS
ON CONTACT_X_CUSTOMER.ID_ADDRESS = ADDRESS.ID
LEFT JOIN CITY
ON ADDRESS.ID_CITY = CITY.ID
LEFT JOIN PROVINCE
ON ADDRESS.ID_PROVINCE = PROVINCE.ID
LEFT JOIN COUNTRY
ON ADDRESS.ID_COUNTRY = COUNTRY.ID
WHERE CONTACT_X_CUSTOMER.ID_CUSTOMER = 17
AND CONTACT_X_CUSTOMER.ID_ADDRESS 0
)
UNION
(SELECT CONTACT_X_CUSTOMER.ID
, CONTACT.LastName AS 'Last Name'
, CONTACT.FirstName AS 'First Name'
, CONTACT_X_CUSTOMER.Email AS 'Email'
, CONTACT_X_CUSTOMER.Active AS 'Active'
, ADDRESS.AddrLine1
+ RTRIM(CONCAT(' ',ISNULL(ADDRESS.AddrLine2,'')))
+ RTRIM(CONCAT(' ',ISNULL(ADDRESS.AddrLine3,''))) as 'Address'
, RTRIM(CONCAT(' ',ISNULL(CITY.Name,''))) as 'City'
, RTRIM(CONCAT(' ',ISNULL(PROVINCE.Name,''))) as 'Province'
, RTRIM(CONCAT(' ',ISNULL(COUNTRY.Name,''))) as 'Country'
, RTRIM(CONCAT(' ',ISNULL(ADDRESS.PostalCode,''))) as 'Postal Code'
. 'Customer Ship To' as 'Address Type'
FROM CONTACT_X_CUSTOMER
LEFT JOIN CONTACT
ON CONTACT_X_CUSTOMER.ID_CONTACT = CONTACT.ID
LEFT JOIN CUSTOMER
ON CONTACT_X_CUSTOMER.ID_CUSTOMER = CUSTOMER.ID
LEFT JOIN ADDRESS
ON CUSTOMER.ID_ADDRESS_SHIPTO = ADDRESS.ID
LEFT JOIN CITY
ON ADDRESS.ID_CITY = CITY.ID
LEFT JOIN PROVINCE
ON ADDRESS.ID_PROVINCE = PROVINCE.ID
LEFT JOIN COUNTRY
ON ADDRESS.ID_COUNTRY = COUNTRY.ID
WHERE CONTACT_X_CUSTOMER.ID_CUSTOMER = 17
AND NOT CONTACT_X_CUSTOMER.ID_ADDRESS0
AND CUSTOMER.ID_ADDRESS_SHIPTO 0
)
UNION
(SELECT CONTACT_X_CUSTOMER.ID
, CONTACT.LastName AS 'Last Name'
, CONTACT.FirstName AS 'First Name'
, CONTACT_X_CUSTOMER.Email AS 'Email'
, CONTACT_X_CUSTOMER.Active AS 'Active'
, ADDRESS.AddrLine1
+ RTRIM(CONCAT(' ',ISNULL(ADDRESS.AddrLine2,'')))
+ RTRIM(CONCAT(' ',ISNULL(ADDRESS.AddrLine3,''))) as 'Address'
, RTRIM(CONCAT(' ',ISNULL(CITY.Name,''))) as 'City'
, RTRIM(CONCAT('
Re: Codes for U.S. Counties
David, I would recommend you check out the US Census, the USGS, and the USPS. All of those organizations have to deal with county-level information on a daily basis. If anyone has publicly available data (which would include the county codes you seek), they would. You should be able to get to each of their respective sites if you first visit the US Government's portal site at http://www.firstgov.gov. Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine David Blomstrom david_blomstrom@To: [EMAIL PROTECTED] yahoo.com cc: Fax to: 06/02/2004 10:21 Subject: Codes for U.S. Counties PM I have a big database focusing on U.S. counties. I'd like to assign each county a code for use as a key. I'm thinking of simply using the state postal code, followed by numerals. For example, Arizona's counties would be arranged alphabetically, beginning with az1, az2, az3, etc. I just wondered if anyone on this list has worked with counties in databases and is aware of a pre-existing code system that's in fairly wide use. It would be nice to make a database that's compatible with other databases, if they share a common code for counties. Thanks. __ Do you Yahoo!? Friends. Fun. Try the all-new Yahoo! Messenger. http://messenger.yahoo.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]
Re[2]: My question is too idiot for nobody answer?
On Thursday, June 3, 2004, 8:14:00 AM, Victor wrote: VP If you choose to copy the files I would ensure that you are getting the VP innodb binary logs as well if they do not exists in the mysql data VP directory. The innodb backup tool or even mysqldump may provide a more VP consistent snapshot. If I might add $0.02 here... It has been my experience that mysqldump is the best solution. * The data is portable to alternate installs if needed (including changes in memory configurations which can improve performance...) * The output of mysqldump is typically much smaller when compressed than the raw data files. This invariably makes the operation faster and more efficient. Best, _M -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Does MySQL Daemonize?
Our software environment requires mysqld to be daemonized. (running in nohup cannot help) Does mysqld support it? Or do we need to change the source code to do it? Thanks, Sp.Raja -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Does MySQL Daemonize?
In the last episode (Jun 03), Sp.Raja said: Our software environment requires mysqld to be daemonized. (running in nohup cannot help) Does mysqld support it? Or do we need to change the source code to do it? Just run safe_mysqld , or place the mysql.server script in your system's rc.d directory to make it start on bootup. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Tuning MySQL 4.0.20 for large full table scans
The schema in question needs a redesign (dynamic row format, contains two BLOB columns) but I was wondering if anyone has written up some guidelines for general data warehouse configuration of MySQL 4.0 -- Google has not turned up anything useful. An example table has 2.1M rows and is 365MB in size. Queries against the table are generally full table scans as efforts to index the table yield little gain (the indexes don't seem to be selective enough). Also, joins on this table are miserable since the BLOB columns make MySQL use tmp disk for sorting instead of keeping everything in memory. There's 10 GB RAM on the box, we're using 64-bit build of MySQL on Solaris 8, and tmp_table_size = 2G, sort_buffer_size = 2G, max_heap_table_size = 2G. I've got read_buffer_size and read_rnd_buffer_size cranked up to 64M, which I'm hoping will make these full table scans do better -- I could be wrong, though. Storage is not local disk but on HBA-attached SAN. As I said, obvious steps to take are to rework the schema and introduce composite/conjoint tables where possible and to move those BLOB columns out of the main fact table ... but even then, there should be optimal settings for a DB that generally does full table scans on 2M rows ... a lot of the data can be kept in that 10 GB of memory, if I could only force MySQL to use it: those BLOB columns are probably killing me. Any tuning advice would be much appreciated. Thanks. -- Dossy -- Dossy Shiobara mail: [EMAIL PROTECTED] Panoptic Computer Network web: http://www.panoptic.com/ He realized the fastest way to change is to laugh at your own folly -- then you can let go and quickly move on. (p. 70) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Tuning MySQL 4.0.20 for large full table scans
In the last episode (Jun 03), Dossy said: The schema in question needs a redesign (dynamic row format, contains two BLOB columns) but I was wondering if anyone has written up some guidelines for general data warehouse configuration of MySQL 4.0 -- Google has not turned up anything useful. An example table has 2.1M rows and is 365MB in size. Queries against the table are generally full table scans as efforts to index the table yield little gain (the indexes don't seem to be selective enough). Consider multicolumn indexes; if you always SELECT field1 from table where field2=blah, creating an index on (field2,field1) will let mysql bypass table lookups completely. Also, joins on this table are miserable since the BLOB columns make MySQL use tmp disk for sorting instead of keeping everything in memory. Unless you're selecting those blob fields, I don't think mysql will keep them during the join operation. I could be wrong though. I've got read_buffer_size and read_rnd_buffer_size cranked up to 64M, which I'm hoping will make these full table scans do better -- I could be wrong, though. Storage is not local disk but on HBA-attached SAN. What's your I/O throughput (run iostat -xcnz 2) during one of these full table scans? If you can get 100MB/sec, a scan of a 365MB file should take under 5 seconds. As I said, obvious steps to take are to rework the schema and introduce composite/conjoint tables where possible and to move those BLOB columns out of the main fact table ... but even then, there should be optimal settings for a DB that generally does full table scans on 2M rows ... a lot of the data can be kept in that 10 GB of memory, if I could only force MySQL to use it: those BLOB columns are probably killing me. With 10GB of RAM, Solaris should be caching your entire table in memory. You will still have the overhead of mysql reading the data from the OS cache but you should still get good performance. Make soure you're not mounting your filesystem with the forcedirectio option, which will disable the OS cache. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Warning Messages - Continued..
Hi again, Thanks to all your kind answers, I've found the Warning Text is as Follows Warning|1260|Record count is fewer than the column count at row 1 Warning|1260|Record count is fewer than the column count at row 2 Warning|1260|Record count is fewer than the column count at row 3 . Etcetera But, after searching the online Documentation I didn't find further explanation about those messages. What could possibly be the problem if your record count is fewer than the column count? When I start with the table there are no records in it. So, its true that record count is = Cero at the time The number of columns for the table ( column count ) is always 54 of course. Any Ideas...? Thanks you Mauricio PS: Thanks Renato, Victor, Dan for your answers. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqldump WHERE clause
MYSQL 4.0.13 I must be doing something wrong - I am trying to do a mysql dump and it keeps yelling that tables dont exist whenever I put a where clause in.. it runs fine when I leave the where clause off - any help here? mysqldump -u root -p hotswap EmailDatabase_k EmailMessage_k --where=EmailDatabase_k.Account='kvanoni' and EmailDatabase_k.id=EmailMessage_k.id kvanoni.db if I make it just a single clause, it then says that table doesnt exist etc... as I said, I take out the where and it works...thanks --- Chris McKeever If you want to reply directly to me, please use cgmckeever--at--prupref---dot---com A href=http://www.prupref.com;www.prupref.com/A Prudential Preferred Properties A href=http://www.prupref.com;Chicago and Illinois NorthShore Real Estate Experts/A Prudential Preferred Properties www.prupref.com Success Driven By Results Results Driven By Commitment Commitment Driven By Integrity We Are Prudential Preferred Properties -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Tuning MySQL 4.0.20 for large full table scans
On 2004.06.03, Dan Nelson [EMAIL PROTECTED] wrote: Consider multicolumn indexes; if you always SELECT field1 from table where field2=blah, creating an index on (field2,field1) will let mysql bypass table lookups completely. Our typical queries look something like: SELECT level1, level2, level3, SUM(count1), SUM(count2) FROM table WHERE level1 = 'value1' AND level2 = 'value2' AND level3 IS NULL GROUP BY level1, level2, level3 In the real data, we have many more than 3 levels. Perhaps I should have been including the count columns in the indexes as well -- hmm. Also, joins on this table are miserable since the BLOB columns make MySQL use tmp disk for sorting instead of keeping everything in memory. Unless you're selecting those blob fields, I don't think mysql will keep them during the join operation. I could be wrong though. The problem is that our longest running queries are this shape: INSERT INTO destination_table SELECT level1, level2, ... leveN, SUM(count1), SUM(count2) ... FROM source_merge_table -- no WHERE clause GROUP BY level1 ... etc source_merge_table is a MERGE table type that can union 12 or more tables. (Once we go to 4.1, we can eliminate the MERGE tables and use a derived table in the FROM clause with UNION ALL, but for now in 4.0, we have to use MERGE tables.) Yes, the BLOB columns are included in the SELECT clause. This forces the query to go straight to tmpfile on disk. I've got read_buffer_size and read_rnd_buffer_size cranked up to 64M, which I'm hoping will make these full table scans do better -- I could be wrong, though. Storage is not local disk but on HBA-attached SAN. What's your I/O throughput (run iostat -xcnz 2) during one of these full table scans? If you can get 100MB/sec, a scan of a 365MB file should take under 5 seconds. I'll have to look at iostat next time the query runs. Thanks for the reminder to look at iostat, duh. As I said, obvious steps to take are to rework the schema and introduce composite/conjoint tables where possible and to move those BLOB columns out of the main fact table ... but even then, there should be optimal settings for a DB that generally does full table scans on 2M rows ... a lot of the data can be kept in that 10 GB of memory, if I could only force MySQL to use it: those BLOB columns are probably killing me. With 10GB of RAM, Solaris should be caching your entire table in memory. You will still have the overhead of mysql reading the data from the OS cache but you should still get good performance. Make soure you're not mounting your filesystem with the forcedirectio option, which will disable the OS cache. Well, what seems to happen is the data gets read from the MyISAM table, gets written back out to disk in the tmpdir, then when it's all done, gets actually placed in the .MYD file -- it's the back-and-forthing to disk that I presume is eating a lot of the time. Not sure what can be done about making it not go straight to tmpdir with a BLOB column in the SELECT clause, though. Probably nothing, in 4.0. -- Dossy -- Dossy Shiobara mail: [EMAIL PROTECTED] Panoptic Computer Network web: http://www.panoptic.com/ He realized the fastest way to change is to laugh at your own folly -- then you can let go and quickly move on. (p. 70) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Warning Messages - Continued..
I believe this means mysql found fewer columns in each row of your infile than are defined in your table. Michael Mauricio Pellegrini wrote: Hi again, Thanks to all your kind answers, I've found the Warning Text is as Follows Warning|1260|Record count is fewer than the column count at row 1 Warning|1260|Record count is fewer than the column count at row 2 Warning|1260|Record count is fewer than the column count at row 3 . Etcetera But, after searching the online Documentation I didn't find further explanation about those messages. What could possibly be the problem if your record count is fewer than the column count? When I start with the table there are no records in it. So, its true that record count is = Cero at the time The number of columns for the table ( column count ) is always 54 of course. Any Ideas...? Thanks you Mauricio PS: Thanks Renato, Victor, Dan for your answers. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump WHERE clause
On Thu, 3 Jun 2004 11:54 , McKeever Chris [EMAIL PROTECTED] sent: MYSQL 4.0.13 I must be doing something wrong - I am trying to do a mysql dump and it keeps yelling that tables dont exist whenever I put a where clause in.. it runs fine when I leave the where clause off - any help here? mysqldump -u root -p hotswap EmailDatabase_k EmailMessage_k --where=EmailDatabase_k.Account='kvanoni' and EmailDatabase_k.id=EmailMessage_k.id kvanoni.db if I make it just a single clause, it then says that table doesnt exist etc... as I said, I take out the where and it works...thanks I think it has something to do with the dual tables, and the query - so now the question becomes how can I do something like what I am doing? --- Chris McKeever If you want to reply directly to me, please use cgmckeever--at--prupref---dot---com http://www.prupref.com;www.prupref.com Prudential Preferred Properties http://www.prupref.com;Chicago and Illinois NorthShore Real Estate Experts Prudential Preferred Properties www.prupref.com Success Driven By Results Results Driven By Commitment Commitment Driven By Integrity We Are Prudential Preferred Properties -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] Prudential Preferred Properties www.prupref.com Success Driven By Results Results Driven By Commitment Commitment Driven By Integrity We Are Prudential Preferred Properties -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump WHERE clause
McKeever Chris wrote: MYSQL 4.0.13 I must be doing something wrong - I am trying to do a mysql dump and it keeps yelling that tables dont exist whenever I put a where clause in.. it runs fine when I leave the where clause off - any help here? mysqldump -u root -p hotswap EmailDatabase_k EmailMessage_k --where=EmailDatabase_k.Account='kvanoni' and EmailDatabase_k.id=EmailMessage_k.id kvanoni.db mysqldump -u root -p hotswap EmailDatabase_k EmailMessage_k --where=EmailDatabase_k.Account='kvanoni' and EmailDatabase_k.id=EmailMessage_k.id kvanoni.db if I make it just a single clause, it then says that table doesnt exist etc... as I said, I take out the where and it works...thanks --- Chris McKeever If you want to reply directly to me, please use cgmckeever--at--prupref---dot---com A href=http://www.prupref.com;www.prupref.com/A Prudential Preferred Properties A href=http://www.prupref.com;Chicago and Illinois NorthShore Real Estate Experts/A Prudential Preferred Properties www.prupref.com Success Driven By Results Results Driven By Commitment Commitment Driven By Integrity We Are Prudential Preferred Properties -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump WHERE clause
On Thu, 03 Jun 2004 12:07 , gerald_clark [EMAIL PROTECTED] sent: McKeever Chris wrote: MYSQL 4.0.13 I must be doing something wrong - I am trying to do a mysql dump and it keeps yelling that tables dont exist whenever I put a where clause in.. it runs fine when I leave the where clause off - any help here? mysqldump -u root -p hotswap EmailDatabase_k EmailMessage_k --where=EmailDatabase_k.Account='kvanoni' and EmailDatabase_k.id=EmailMessage_k.id kvanoni.db mysqldump -u root -p hotswap EmailDatabase_k EmailMessage_k --where=EmailDatabase_k.Account='kvanoni' and EmailDatabase_k.id=EmailMessage_k.id kvanoni.db gerald thanks for the response - but that is what I had - different quote placement - but same results nonetheless .. does this dual table/where clause work for you if I make it just a single clause, it then says that table doesnt exist etc... as I said, I take out the where and it works...thanks --- Chris McKeever If you want to reply directly to me, please use cgmckeever--at--prupref---dot---com http://www.prupref.com;www.prupref.com Prudential Preferred Properties http://www.prupref.com;Chicago and Illinois NorthShore Real Estate Experts Prudential Preferred Properties www.prupref.com Success Driven By Results Results Driven By Commitment Commitment Driven By Integrity We Are Prudential Preferred Properties Prudential Preferred Properties www.prupref.com Success Driven By Results Results Driven By Commitment Commitment Driven By Integrity We Are Prudential Preferred Properties -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
can you drop multiple indexes at one time?
I definitely don't see this in the documentation anywhere, but can you drop multiple indexes at one time with an alter table? Donny
SQL help
What I really want was mysql update records set records.prio=2 where records.in=(select records.id from records, audit_log, audit_log_records where audit_log.tracker_id=audit_log_records.tracker_id and records.id=audit_log_records.id and audit_log.operation='D' and audit_log.completed is null); but that gives a syntax error and I don't think I can do a select inside an update. :-( mysql update records set records.prio=2 where audit_log.tracker_id=audit_log_records.tracker_id and records.id=audit_log_records.id and audit_log.operation='D' and audit_log.completed is null; ERROR 1109: Unknown table 'audit_log' in where clause -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump WHERE clause
McKeever Chris wrote: MYSQL 4.0.13 I must be doing something wrong - I am trying to do a mysql dump and it keeps yelling that tables dont exist whenever I put a where clause in.. it runs fine when I leave the where clause off - any help here? mysqldump -u root -p hotswap EmailDatabase_k EmailMessage_k --where=EmailDatabase_k.Account='kvanoni' and EmailDatabase_k.id=EmailMessage_k.id kvanoni.db if I make it just a single clause, it then says that table doesnt exist etc... as I said, I take out the where and it works...thanks --- Chris McKeever I expect mysqldump processes your command line one table at a time and does something like SELECT * FROM db.table; to get the row data. Adding --where adds a WHERE clause to that. The problem is that you cannot refer to a table in the WHERE clause that wasn't mentioned in the FROM clause, and there's only the one table in there. This is because mysqldump is designed to make table backups. It is not designed to run arbitrary queries. In other words, I think mysqldump is not designed to do what you are trying to do. Instead, you probably need to look into SELECT ... INTO OUTFILE http://dev.mysql.com/doc/mysql/en/SELECT.html or, if you're trying to make a new table with a subset of the rows from the old table, CREATE ... SELECT http://dev.mysql.com/doc/mysql/en/CREATE_TABLE.html. If you really want a file with SQL statements, I suppose you could use CREATE...SELECT to make the subset table, then use mysqldump without a where clause on that, and finally DROP the subset table. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Exporting/Importing Databases
David Blomstrom [EMAIL PROTECTED] wrote: --- Egor Egorov [EMAIL PROTECTED] wrote: David Blomstrom [EMAIL PROTECTED] wrote: --- Robert A. Rosenberg [EMAIL PROTECTED] wrote: You just got caught with the old Referential Integrity Gotcha. It will not allow you to create countries since statesarticles does not (yet) exist. The fix is to temporally turn off RI during the load. Put this command at the start of your file: SET FOREIGN_KEY_CHECKS = 0; and this one at the end: SET FOREIGN_KEY_CHECKS = 1; and it will work. You should put these lines to the dump file that contains CREATE TABLE statements, not to the *.frm files. Then load tables. If you use command-line client you can do: mysql SET FOREIGN_KEY_CHECKS = 0; mysql SOURCE file_name; mysql SET FOREIGN_KEY_CHECKS = 1; Does anyone on this list know... 1. Can I also open the file with Notepad and insert the statements? Yes. 2. Where's the location of this dump file? Where did you save it? 3. After I import my SQL file online, do I have to then change the file back, deleting the two statements I added? Up to you. If you plan to reuse this dump file, you can keep SET FOREIGN_KEY_CHECKS statements too. I'm leery of using the command-client, because it's unfamiliar to me, and I'm worried about making a major mistake. -- 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]
v4.0.13 - v4.0.18
Hi, I want to move a DB to a new server. The old one has MySQl version 4.0.13. The new one has 4.0.18. Is there any kinda prep that needs to be done or can I move it right on in? thank you for your time, Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL help
Bob Lockie wrote: What I really want was mysql update records set records.prio=2 where records.in=(select records.id from records, audit_log, audit_log_records where audit_log.tracker_id=audit_log_records.tracker_id and records.id=audit_log_records.id and audit_log.operation='D' and audit_log.completed is null); but that gives a syntax error and I don't think I can do a select inside an update. :-( Subqueries require mysql 4.1 or higher. mysql update records set records.prio=2 where audit_log.tracker_id=audit_log_records.tracker_id and records.id=audit_log_records.id and audit_log.operation='D' and audit_log.completed is null; ERROR 1109: Unknown table 'audit_log' in where clause You have to name all the tables you need in the UPDATE clause before you can use them in the WHERE clause. So, you need UPDATE records, auditlog, audit_log_records SET records.prio=2 WHERE audit_log.tracker_id=audit_log_records.tracker_id AND records.id=audit_log_records.id AND audit_log.operation='D' AND audit_log.completed is null; This is a multiple-table update, which is supported starting with mysql 4.0.4. Prior to that, you can't do this with one statement. See the manual http://dev.mysql.com/doc/mysql/en/UPDATE.html. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL help
Bob Lockie [EMAIL PROTECTED] wrote: What I really want was mysql update records set records.prio=2 where records.in=(select records.id from records, audit_log, audit_log_records where audit_log.tracker_id=audit_log_records.tracker_id and records.id=audit_log_records.id and audit_log.operation='D' and audit_log.completed is null); but that gives a syntax error and I don't think I can do a select inside an update. :-( mysql update records set records.prio=2 where audit_log.tracker_id=audit_log_records.tracker_id and records.id=audit_log_records.id and audit_log.operation='D' and audit_log.completed is null; ERROR 1109: Unknown table 'audit_log' in where clause You must specify 'audit_log' and 'audit_log_records' tables too. UPDATE records, audit_log, audit_log_records SET records.prio=2 WHERE .. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Unicode characters become question marks
I'm confirmed that all the data were inserted into database correctly. I
need to look elsewhere. The following is the test script, written in
JudoScript.
Silvio, this may be another easy way for you to confirm what's in the
database. Suppose you have Java 1.3 or up installed; all you need is to put
these 2 jar files in the CLASSPATH: (a) judo.jar from
http://www.judoscript.com/download.html and (b) the mysql-connector-J jar
files from mysql.com. Then cut-n-paste the following and you are ready to
run this:
java judo mysqltest.judo
Super easy, if you know how to run Java software.
Cheers!
-James Huang
//
// File: connect.judi
//
connect to
'jdbc:mysql://localhost/mydb?useUnicode=truecharacterEncoding=utf-8',
'myuser', 'myuser';
//
// File: mysqltest.judo
//
!include 'connect.judi'
asUnicode = '\u7247\u4EEE\u540D';
println 'As Java Unicode (int)chars:';
printString asUnicode;
// create table
executeSQL {
DROP TABLE IF EXISTS testFoo;
CREATE TABLE testFoo (field1 VARCHAR(32)) CHARACTER SET UTF8;
}
// insert unicode into table
preparedExecuteUpdate upd:
INSERT INTO testFoo VALUES(?)
; with @1 = asUnicode;
// get back unicode from table
executeQuery qry:
SELECT * FROM testFoo
;
println 'Retrieved from database as (int)chars:';
while qry.next() {
printString qry[1];
}
catch: $_.pist();
finally: disconnect();
function printString s {
lastidx = s.length()-1;
for i from 0 to lastidx {
ch = s.charAt(i);
println ch, ' - ', ch.unicode().fmtHex();
}
println;
}
From: James Huang [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
CC: [EMAIL PROTECTED], [EMAIL PROTECTED]
Subject: Re: Unicode characters become question marks
Date: Wed, 02 Jun 2004 22:11:31 -0700
This test code works, with both mysql-connector-j 3.0.14 and 3.0.11,
against mysql 5.0-alpha. I'll look more and report anything if interesting.
Thanks,
-James
From: Mark Matthews [EMAIL PROTECTED]
To: James Huang [EMAIL PROTECTED]
CC: [EMAIL PROTECTED], [EMAIL PROTECTED], [EMAIL PROTECTED]
Subject: Re: Unicode characters become question marks
Date: Wed, 02 Jun 2004 13:04:38 -0500
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
James Huang wrote:
Victor,
I'm positive the database is storing ?'s. You may test with these
steps:
1) insert \u7247\u4EEE\u540D into a UTF8 table;
James,
Have you set your JDBC driver's character set to be UTF-8 using the
characterEncoding property?
2) Query and get it back into string s;
3) for each char c in s: System.out.println((int)c);
Here's what I get (converting the chars to int to avoid any display
problems)...at least on my end, w/ Connector/J 3.0.14 and MySQL-4.1.x,
what I put in is what I get back out, so my guess is something between
the database and your display is munging the characters...Is whatever
you're using for output set to the correct encoding?:
As Java Unicode (int)chars:
7247
4eee
540d
Retrieved from database as (int)chars:
7247
4eee
540d
(full disclosure, here's my testcase):
public void testFoo() throws Exception {
Properties props = new Properties();
props.setProperty(characterEncoding, utf-8);
Connection utf8Conn = getConnectionWithProps(props);
Statement utf8Stmt = utf8Conn.createStatement();
utf8Stmt.executeUpdate(DROP TABLE IF EXISTS testFoo);
utf8Stmt.executeUpdate(CREATE TABLE testFoo (field1 VARCHAR(32)
CHARACTER SET UTF8) CHARACTER SET UTF8);
utf8Stmt.executeUpdate(INSERT INTO testFoo VALUES
('\u7247\u4EEE\u540D'));
System.out.println(As Java Unicode (int)chars: );
String asUnicode = \u7247\u4EEE\u540D;
for (int i = 0; i asUnicode.length(); i++) {
System.out.println(Integer.toHexString((int)asUnicode.charAt(i)));
}
System.out.println();
ResultSet rs = utf8Stmt.executeQuery(SELECT * FROM testFoo);
rs.next();
String utf8String = rs.getString(1);
System.out.println(Retrieved from database as (int)chars: );
for (int i = 0; i utf8String.length(); i++) {
System.out.println(Integer.toHexString((int)utf8String.charAt(i)));
}
}
- --
Mr. Mark Matthews
MySQL AB, Software Development Manager, J2EE and Windows Platforms
Office: +1 708 332 0507
www.mysql.com
MySQL Guide to Lower TCO
http://www.mysql.com/it-resources/white-papers/tco.php
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.2.3 (MingW32)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org
iD8DBQFAvha1tvXNTca6JD8RAiB6AJ9FGD0XHFwph8pBJSM5iBQeypbYfwCguIEV
kgjo+ZcICok1bdypNl82cVc=
=uRlQ
-END PGP SIGNATURE-
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Exporting/Importing Databases
--- Egor Egorov [EMAIL PROTECTED] wrote: David Blomstrom [EMAIL PROTECTED] wrote: If you use command-line client you can do: mysql SET FOREIGN_KEY_CHECKS = 0; mysql SOURCE file_name; mysql SET FOREIGN_KEY_CHECKS = 1; Does anyone on this list know... 1. Can I also open the file with Notepad and insert the statements? Yes. 2. Where's the location of this dump file? Where did you save it? I don't know; I didn't even know it existed! When I EXPORT a database, am I creating just ONE file or more than one? At first, I thought there was just one - the file that landed on my desktop. I inserted these two statements in that file (using Notepad), but I couldn't publish it online. Then I thought I understood that there's a SECOND file - a dump file that I'm supposed to modify. But I didn't knowingly save it to any particular location, because I wasn't even aware of its existence. During the export operation, I wasn't prompted to save a dump file to a certain location. So let me go back to sqaure one... 1. When I export a database, how many files are created? 2. If more than one file are created, including a dump file, I'm supposed to insert the referential integrity statements in the dump file, not the SQL file, right? 3. How can I determine the location of the dump file I saved? Thanks. __ Do you Yahoo!? Friends. Fun. Try the all-new Yahoo! Messenger. http://messenger.yahoo.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SOLVED Re: mysqldump WHERE clause
On Thu, 03 Jun 2004 13:57 , Michael Stassen [EMAIL PROTECTED] sent: McKeever Chris wrote: MYSQL 4.0.13 I must be doing something wrong - I am trying to do a mysql dump and it keeps yelling that tables dont exist whenever I put a where clause in.. it runs fine when I leave the where clause off - any help here? mysqldump -u root -p hotswap EmailDatabase_k EmailMessage_k --where=EmailDatabase_k.Account='kvanoni' and EmailDatabase_k.id=EmailMessage_k.id kvanoni.db if I make it just a single clause, it then says that table doesnt exist etc... as I said, I take out the where and it works...thanks --- Chris McKeever I expect mysqldump processes your command line one table at a time and does something like SELECT * FROM db.table; to get the row data. Adding --where adds a WHERE clause to that. The problem is that you cannot refer to a table in the WHERE clause that wasn't mentioned in the FROM clause, and there's only the one table in there. This is because mysqldump is designed to make table backups. It is not designed to run arbitrary queries. In other words, I think mysqldump is not designed to do what you are trying to do. Instead, you probably need to look into SELECT ... INTO OUTFILE http://dev.mysql.com/doc/mysql/en/SELECT.html or, if you're trying to make a new table with a subset of the rows from the old table, CREATE ... SELECT http://dev.mysql.com/doc/mysql/en/CREATE_TABLE.html. Michael - I owe you a big wet sloppy kiss - that worked perfectly - created the new table, dropped the old, swapped the new one in and ran the dump and then imported what I needed back to the production DB... If you really want a file with SQL statements, I suppose you could use CREATE...SELECT to make the subset table, then use mysqldump without a where clause on that, and finally DROP the subset table. Michael Prudential Preferred Properties www.prupref.com Success Driven By Results Results Driven By Commitment Commitment Driven By Integrity We Are Prudential Preferred Properties -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Exporting/Importing Databases
Wow, I tried importing my SQL file again, with the referential integrity statements. This time I got a NEW error: Database geoblue_world - Table continents running on localhost Error There seems to be an error in your SQL query. The MySQL server error output below, if there is any, may also help you in diagnosing the problem ERROR: Unknown Punctuation String @ 5 STR: / SQL: br / bWarning/b: mysql_free_result(): supplied argument is not a valid MySQL result resource in bC:\xampp\phpmyadmin\libraries\export\sql.php/b on line b273/bbr / SQL-query : Warning: mysql_free_result(): supplied argument is not a valid MySQL result resource in C:\xampp\phpmyadmin\libraries\export\sql.php on line 273 MySQL said: #1064 - 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 'br / bWarning/b: mysql_free_result(): supplied argument * * * * * * * * * * I decided to try a new strategy - exporting and importing one table at a time. I exported one table, inserted the two referential integrity statements, and it worked. So I guess I'll use this strategy for the time being, but it would be nice to be able to publish an entire database, all in one fell swoop! __ Do you Yahoo!? Friends. Fun. Try the all-new Yahoo! Messenger. http://messenger.yahoo.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Where do exported SQL files go?
At 22:17 -0700 on 06/02/2004, David Blomstrom wrote about Re: Where do exported SQL files go?: --- Robert A. Rosenberg [EMAIL PROTECTED] wrote: At 20:13 -0700 on 06/02/2004, David Blomstrom wrote about Re: Where do exported SQL files go?: (I'm using Mozilla Firefox, which downloads everything to the desktop, though I don't understand why it considers this a download when it's a database on my computer.) Might it be doing this due to PHPAdmin being a WebPage application and thus being served by your Web Server making the file need to be a FTP Download g? Well, that's as good an explanation as any. :) It isn't that big a deal; at least I know where I can find everything I download, and I can always copy and rename them and move them somewhere else if necessary. It just confused me this time around. I apologize if I seemed to have been flippant in my wording instead of just saying Here's Why. I am 100% sure of the correctness of my solution (ie: PHPMyAdmin IS a Web Application running on the Web Server not an application running on the user's machine [even if the Server is running on the user's machine it us still the Server not the user who is running the program]). I phrased it that way since it is one of those Can't see the Forest for the Trees situations where the answer is obvious once you look at the problem the correct way and want'ed to inject some humor to downplay an offence at pointing out the obvious. If it had been mysqladmin, the file would just be saved since you would be on your machine doing the query not using a Web Browser to issue it to a Web Application. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Exporting/Importing Databases
At 21:54 -0700 on 06/02/2004, David Blomstrom wrote about Re: Exporting/Importing Databases: --- Robert A. Rosenberg [EMAIL PROTECTED] wrote: At 20:36 -0700 on 06/02/2004, David Blomstrom wrote about Exporting/Importing Databases: Third, my practice database includes half a dozen tables, but only one made it online, followed by this error message: Database USERNAME running on localhost Error SQL-query : CREATE TABLE `counties` ( `SCode` varchar( 6 ) NOT NULL default '', `NameC` varchar( 255 ) default NULL , `TypeC` varchar( 255 ) default NULL , `Seat` varchar( 255 ) default NULL , `Area_MI` decimal( 10, 1 ) default NULL , `Area_KM` decimal( 10, 1 ) default NULL , `Pop_2000` int( 10 ) default NULL , `Pop_1990` int( 10 ) default NULL , `Pop_MI` decimal( 10, 1 ) default NULL , `Pop_KM` decimal( 10, 1 ) default NULL , `Race1` int( 10 ) default NULL , `Race2` int( 10 ) default NULL , `Amerindian` int( 10 ) default NULL , `White` int( 10 ) default NULL , `Black` int( 10 ) default NULL , `Asian` int( 10 ) default NULL , `Pacific_Island` int( 6 ) default NULL , `Some_Other_Race` int( 10 ) default NULL , `Hispanic` int( 10 ) default NULL , `id` int( 6 ) NOT NULL AUTO_INCREMENT , PRIMARY KEY ( `id` ) , KEY `SCode` ( `SCode` ) , CONSTRAINT `0_132` FOREIGN KEY ( `SCode` ) REFERENCES `statesarticles` ( `SCode` ) ON UPDATE CASCADE ) TYPE = InnoDB AUTO_INCREMENT =3143 MySQL said: #1005 - Can't create table './[USERNAME]/counties.frm' (errno: 150) Do you know what this means? You just got caught with the old Referential Integrity Gotcha. It will not allow you to create countries since statesarticles does not (yet) exist. The fix is to temporally turn off RI during the load. Put this command at the start of your file: SET FOREIGN_KEY_CHECKS = 0; and this one at the end: SET FOREIGN_KEY_CHECKS = 1; and it will work. OK, is this something I can do in phpMyAdmin or another software program, or do I have to open the MySQL file itself? Yes you need to edit the dumped file. BTW: As of 4.1, mysqldump will automatically add these statements. I am thinking of filing a bug report against PHPMyadmin to add the statements. I've scarcely touched MySQL files, but I found the file counties.frm, which I assume I would open with Notepad, right? And then I can make SET FOREIGN_KEY_CHECKS = 0; the very first line and SET FOREIGN_KEY_CHECKS = 1; the very last line, after which I would EXPORT my database, then import it, right? They do into the EXPORTED file after you create it. And since I like to plan ahead, is it OK to insert these two lines in ALL my MySQL documents, just to be prepared for this error? If I inadvertently stick these lines in a file that doesn't have a foreign key, will it cause a problem? No it will not cause a problem. You are just turning off the check for RI (just like the If Exits clause on the DROP TABLE command turns off the check to see if there is a table to delete before doing the CREATE TABLE). Finally, after I've imported my database online, do I have to go back and remove these two lines, or can I just leave them there indefinitely as a safeguard? Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: v4.0.13 - v4.0.18
michael young [EMAIL PROTECTED] wrote: I want to move a DB to a new server. The old one has MySQl version 4.0.13. The new one has 4.0.18. Is there any kinda prep that needs to be done or can I move it right on in? Making backup of your databases is always recommended. -- 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]
VB .NET MYSQL - LOCK TABLES
Hello MySql List, I have create connection with VB .NET MySql, and now i must use the LOCK TABLES statment. I want to know how i can use this sintax .. i must open connection - begin the LOCK TABLES - begin the select statment, and then UNLOCK TABLES .. I think so that is not correctly .. where i can see the example for this problem ? Excusme for my english .. i don's speak and write very well!! Mirco
Can you rename or copy a database?
My database world won't work online, because the online version is username_world, with a different username and password. So I want to create a copy of world named username_world. I created a new database named username_world and started importing tables into it, until I hit a snag... MySQL said: #1005 - Can't create table '.\cdcol\counties2.frm' (errno: 150) * * * * * * * * * * The quickest, easiest strategy would be to simply create a copy of my database. But I don't see any copy commands, unless you do it with the export function. If so, would simply save it as a SQL file alongside your other databases? SQL LaTeX CSV for Ms Excel data CSV data XML __ Do you Yahoo!? Friends. Fun. Try the all-new Yahoo! Messenger. http://messenger.yahoo.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Table types
Hi, Is it wise to have a database with hybrid table types? In other words: if I have a table that wouldn´t have many INSERT/DELETE/UPDATE queries, may I create it as MyISAM type and even interact (make JOINs) with other InnoBD and MyISAM tables? Or is it better (faster) to create all columns with the same type (InnoDB)? Thanks, Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Join question
Running 4.0.18 I am trying to run a query where the query gets the offer_ID of a certain customer from the offer table and displays in the results the offer_Name associated with the offer_ID. Right now the way the query is working it displays all the offers in the offer table regardless of the offer_ID assigned to the customer. Any thoughts? SELECT c.cst_SiteID, c.cst_IDC, a.asset_ID, o.offer_ID, o.offer_Name FROM customers c, assets a , offers o WHERE c.cst_SiteID = a.asset_SiteID AND cst_Name LIKE 'z%' Results: | cst_SiteID | cst_IDC | asset_ID | offer_ID | offer_Name | ++-+--+--++ | 6916 | 2 |18165 |3 | Basic Monitoring | | 6916 | 2 |18165 |8 | Unknown| | 6916 | 2 |18165 |1 | Advanced Managed | | 6916 | 2 |18165 |5 | Mixed Managed | | 6916 | 2 |18165 |6 | No Monitoring | | 6916 | 2 |18165 |2 | Advanced Monitoring| | 6916 | 2 |18165 |4 | Internally Managed | | 6916 | 2 |18165 |7 | Performance Monitoring | ++-+--+--++ customers Table +++--+-+ -+-+---+ | cst_ID | cst_SiteID | cst_Name | cst_IDC | cst_MgtType | cst_POC | cst_Offer | +++--+-+ -+-+---+ | 2418 | 897 | JTE (H.K.) Limited | 9 | 5 | 0 | 6 | | 2417 | 799 | Zape Corporation | 7 | 5 | 0 | 6 | | 2416 | 728 | Zone , Inc. | 9 | 5 | 0 | 6 | | 2415 | 702 | ZL Batavia, LLC | 16 | 1 | 0 | 1 | +++--+-+ -+-+---+ offers Table +--++--- --+ | offer_ID | offer_Name | offer_Search | +--++--- --+ |1 | Advanced Managed | Advanced Managed | |2 | Advanced Monitoring| Advanced Monitoring | |4 | Internally Managed | Internally Managed | |3 | Basic Monitoring | Basic Monitoring | |5 | Mixed Managed | Mixed Managed | |6 | No Monitoring | No Monitoring | |7 | Performance Monitoring | Performance Monitoring, Performance Managed | |8 | Unknown| Unknown | +--++--- --+ Sincerely, Chris Dietzler ATT Enhanced Network Services 858 812 4062 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Warning Messages - Continued..
Yes, you were right! After reading your mail, counted the columns in my .txt file and discovered that there was a missing one. I've just added the column, issued a LOAD DATA... and the result was Zero warnings this time. Thanks a lot. Mauricio On Thu, 2004-06-03 at 09:59, Michael Stassen wrote: I believe this means mysql found fewer columns in each row of your infile than are defined in your table. Michael Mauricio Pellegrini wrote: Hi again, Thanks to all your kind answers, I've found the Warning Text is as Follows Warning|1260|Record count is fewer than the column count at row 1 Warning|1260|Record count is fewer than the column count at row 2 Warning|1260|Record count is fewer than the column count at row 3 . Etcetera But, after searching the online Documentation I didn't find further explanation about those messages. What could possibly be the problem if your record count is fewer than the column count? When I start with the table there are no records in it. So, its true that record count is = Cero at the time The number of columns for the table ( column count ) is always 54 of course. Any Ideas...? Thanks you Mauricio PS: Thanks Renato, Victor, Dan for your answers. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Join question
On Thu, 3 Jun 2004 13:06:54 -0700 Chris Dietzler [EMAIL PROTECTED] wrote: Running 4.0.18 I am trying to run a query where the query gets the offer_ID of a certain customer from the offer table and displays in the results the offer_Name associated with the offer_ID. Right now the way the query is working it displays all the offers in the offer table regardless of the offer_ID assigned to the customer. Any thoughts? SELECT c.cst_SiteID, c.cst_IDC, a.asset_ID, o.offer_ID, o.offer_Name FROM customers c, assets a , offers o WHERE c.cst_SiteID = a.asset_SiteID AND cst_Name LIKE 'z%' snip Global rule for joins - if you have N tables (or table aliases) involed a JOIN, you need N-1 JOIN conditions in your WHERE clause, or using INNER JOIN clauses. In your query cst_Name LIKE 'z%' is NOT a join condition, it's just a filter. Nothing actually joins your offers table in the above query. Try: SELECT c.cst_SiteID, c.cst_IDC, a.asset_ID, o.offer_ID, o.offer_Name FROM customers c, assets a , offers o WHERE c.cst_SiteID = a.asset_SiteID AND c.cst_Offer = o.offer_ID AND cst_Name LIKE 'z%'; Or in preferable INNER JOIN syntax which makes it easier to forget a JOIN condition: SELECT c.cst_SiteID, c.cst_IDC, a.asset_ID, o.offer_ID, o.offer_Name FROM customers c INNER JOIN assets a ON c.cst_SiteID = a.asset_SiteID INNER JOIN offers o ON c.cst_Offer = o.offer_ID WHERE cst_Name LIKE 'z%'; Josh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: VB .NET MYSQL - LOCK TABLES
You will need to reuse your database connection, do not open a new connection with each call or the lock will not be there. LOCK TABLE table1 READ; SELECT * FROM table1; UNLOCK TABLES; -Original Message- From: Mirco Santori To: [EMAIL PROTECTED] Sent: 6/3/04 2:17 PM Subject: VB .NET MYSQL - LOCK TABLES Hello MySql List, I have create connection with VB .NET MySql, and now i must use the LOCK TABLES statment. I want to know how i can use this sintax .. i must open connection - begin the LOCK TABLES - begin the select statment, and then UNLOCK TABLES .. I think so that is not correctly .. where i can see the example for this problem ? Excusme for my english .. i don's speak and write very well!! Mirco -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Can you rename or copy a database?
You have hit a foreign key constraint error. You can try appending SET FOREIGN_KEYS_CHECK = 0 at the beginning of your script and SET FOREIGN_KEYS_CHECK = 1 and the end of your script to disable the foreign key constraints. -Original Message- From: David Blomstrom To: [EMAIL PROTECTED] Sent: 6/3/04 2:44 PM Subject: Can you rename or copy a database? My database world won't work online, because the online version is username_world, with a different username and password. So I want to create a copy of world named username_world. I created a new database named username_world and started importing tables into it, until I hit a snag... MySQL said: #1005 - Can't create table '.\cdcol\counties2.frm' (errno: 150) * * * * * * * * * * The quickest, easiest strategy would be to simply create a copy of my database. But I don't see any copy commands, unless you do it with the export function. If so, would simply save it as a SQL file alongside your other databases? SQL LaTeX CSV for Ms Excel data CSV data XML __ Do you Yahoo!? Friends. Fun. Try the all-new Yahoo! Messenger. http://messenger.yahoo.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]
Re: Join question
On Thu, 3 Jun 2004 15:22:36 -0500 Josh Trutwin [EMAIL PROTECTED] wrote: Or in preferable INNER JOIN syntax which makes it easier to forget a JOIN condition: Oops - I meant harder to forget not easier to forget. Doh. SELECT c.cst_SiteID, c.cst_IDC, a.asset_ID, o.offer_ID, o.offer_Name FROM customers c INNER JOIN assets a ON c.cst_SiteID = a.asset_SiteID INNER JOIN offers o ON c.cst_Offer = o.offer_ID WHERE cst_Name LIKE 'z%'; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Slow querys When ADSL is down on W2K
Hi, This seems to be a strange situation. I have a server ( Sun fire v65x ) running SuSE 8.2 and My-Sql 4.1.1alpha-max. I've detected that when our adsl internet connection is down the querys on our local network turns to be unusually slow. But this situation occurs only when querys are executed from PC's running W2k. Other machines running Linux, on the same local net, doesn't seem to be affected by this. Those machines performs the exact same querys in a fraction of time compared to the ones running W2k. All the PC's and the server are phisically connected to the same Switch. There's a gateway which provides the internet connection. Querys are performed from within EMS MySQl manager on the W2k boxes and from MySql Control Center on Linux. There's also one more thing, the same query executed from the MySQl CLIENT ( the text based application ) returns normal execution times on the W2k boxes. Soon as the ADSL service is restored execution times return to normal on w2k and remains unchanged on Linux. I can't figure why this happens.. any ideas are especially welcome Thanks Mauricio -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table types
Hi Ronan, Yes, it's fine to mix table types in databases and queries. Matt - Original Message - From: Ronan Lucio Sent: Thursday, June 03, 2004 2:44 PM Subject: Table types Hi, Is it wise to have a database with hybrid table types? In other words: if I have a table that wouldn´t have many INSERT/DELETE/UPDATE queries, may I create it as MyISAM type and even interact (make JOINs) with other InnoBD and MyISAM tables? Or is it better (faster) to create all columns with the same type (InnoDB)? Thanks, Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: VB .NET MYSQL - LOCK TABLES
Put of all three lines of your sql in one string variable with the semi-colons and execute it as one command. Works for me using the ByteFX libraries. Larry - Original Message - From: Victor Pendleton [EMAIL PROTECTED] To: 'Mirco Santori ' [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Thursday, June 03, 2004 1:25 PM Subject: RE: VB .NET MYSQL - LOCK TABLES You will need to reuse your database connection, do not open a new connection with each call or the lock will not be there. LOCK TABLE table1 READ; SELECT * FROM table1; UNLOCK TABLES; -Original Message- From: Mirco Santori To: [EMAIL PROTECTED] Sent: 6/3/04 2:17 PM Subject: VB .NET MYSQL - LOCK TABLES Hello MySql List, I have create connection with VB .NET MySql, and now i must use the LOCK TABLES statment. I want to know how i can use this sintax .. i must open connection - begin the LOCK TABLES - begin the select statment, and then UNLOCK TABLES .. I think so that is not correctly .. where i can see the example for this problem ? Excusme for my english .. i don's speak and write very well!! Mirco -- 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: Slow querys When ADSL is down on W2K
I'd suggest you add a static route to the W2k clients just to see if that corrects the problem... try this command: route ADD ddd.ddd.ddd.ddd MASK mmm.mmm.mmm.mmm ggg.ggg.ggg.ggg d = destination (the mysql DB server) m = subnet mask (255.255.255.0 or whatever your route happens to be) g = gateway (I guess this would be the switch, not quite sure, suggestions anyone???) If this solves the problem, you should make the route persistent by adding '-p' to the above command. Hope this helps. Mauricio Pellegrini wrote: Hi, This seems to be a strange situation. I have a server ( Sun fire v65x ) running SuSE 8.2 and My-Sql 4.1.1alpha-max. I've detected that when our adsl internet connection is down the querys on our local network turns to be unusually slow. But this situation occurs only when querys are executed from PC's running W2k. Other machines running Linux, on the same local net, doesn't seem to be affected by this. Those machines performs the exact same querys in a fraction of time compared to the ones running W2k. All the PC's and the server are phisically connected to the same Switch. There's a gateway which provides the internet connection. Querys are performed from within EMS MySQl manager on the W2k boxes and from MySql Control Center on Linux. There's also one more thing, the same query executed from the MySQl CLIENT ( the text based application ) returns normal execution times on the W2k boxes. Soon as the ADSL service is restored execution times return to normal on w2k and remains unchanged on Linux. I can't figure why this happens.. any ideas are especially welcome Thanks Mauricio -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Tuning MySQL 4.0.20 for large full table scans
Hi Dan, - Original Message - From: Dan Nelson Sent: Thursday, June 03, 2004 12:34 PM Subject: Re: Tuning MySQL 4.0.20 for large full table scans [snip] Not sure what can be done about making it not go straight to tmpdir with a BLOB column in the SELECT clause, though. Probably nothing, in 4.0. Do you actually see a temp file being created? With tmp_table_size set to 2gb, it shouldn't have to go to disk. Some more interesting data would be the State column from show processlist during the query, every 10 seconds or so. Currently, temporary tables with TEXT/BLOB columns are always created on disk because HEAP tables don't support variable-length rows. I think this limitation will be lifted in 5.0 or 5.1. For the original poster, maybe this is one of the times that a RAM disk could be useful for MySQL's tmpdir. Matt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
error 2013 : Lost connection to MySQL server during query
Hello all, I'm getting the error message 2013 : Lost connection to MySQL server during query during inserts on a lightly-loaded mysql server. I also noticed the following error in the error log... Version: '4.0.20-standard-log' socket: '/tmp/mysql.sock' port: 3306 040603 16:25:48 Aborted connection 1 to db: 'milterdb' user: 'milteruser' host: `localhost' (Got an error reading communication packets) I am going nuts trying to fix this error! I've done some googling and the only fixes I've heard of are fixing name resolution errors. I'm thinking that has nothing to do with this setup since 1000s of other inserts/queries are fine. One second it is working, the next second it stops with this error. The application interfaces with a Sendmail mail server and archives all incoming/outgoing email traffic to the MySQL database. The source is available if anybody thinks my poor coding is the problem. Any help or ideas will be greatly appreciated. Thanks in advance! Daniel Whitener basic config info is below. I can provide more details if needed... I'm using suse 9.1 uranus:~ # uname -a Linux uranus 2.6.4-52-default #1 Wed Apr 7 02:08:30 UTC 2004 i686 i686 i386 GNU/Linux uranus:~ # mysql status; -- mysql Ver 12.22 Distrib 4.0.20, for pc-linux (i686) Connection id: 15 Current database: Current user: [EMAIL PROTECTED] SSL:Not in use Current pager: less Using outfile: '' Server version: 4.0.20-standard-log Protocol version: 10 Connection: Localhost via UNIX socket Client characterset:latin1 Server characterset:latin1 UNIX socket:/tmp/mysql.sock Uptime: 55 min 51 sec Threads: 2 Questions: 313 Slow queries: 1 Opens: 11 Flush tables: 1 Open tables: 5 Queries per second avg: 0.093 mysqld section of the my.cnf file... # The MySQL server [mysqld] port= 3306 socket = /tmp/mysql.sock wait_timeout = 7200 interactive_timeout = 28800 skip-locking key_buffer = 64M max_allowed_packet = 10M table_cache = 128 sort_buffer_size = 1M read_buffer_size = 1M net_buffer_length = 8K myisam_sort_buffer_size = 16M user = mysql thread_stack = 1M thread_cache = 8 query_cache_size = 16M thread_concurrency = 4 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help with a SELECT Statement
Hi Everyone. I have the following SELECT statement the get information from two tables. The statement only works when it finds a value for the image_archive.circuit value in the circuits table. However, not all entries in the database has a value in the this field, some are set to 0 as the circuit for that image was not known or the image was not a circuit. SELECT image_archive.filename, image_archive.year, image_archive.month, image_archive.driver_forename, image_archive.driver_surname, image_archive.team, image_archive.event, circuits.name as circuit_name, image_archive.description, image_archive.title, image_archive.membership_no FROM image_archive, circuits WHERE image_archive.id = 109 AND circuits.id = image_archive.circuit How can I modify the statement to allow it to return a record when the image_archive.circuit value is 0, but to return the circuit name when the value is greater than 0. At the moment when the circuit value is 0 no records are returned even though I know the rest of the information is in the image_archive table. Hope that makes sense. Thanks in advances for any help. BTW, I am using 4.0.18. Best Regards Andrew SpamNet - Stop spam immediately and help me too! http://www.cloudmark.com/spamnet www.cloudmark.com/spamnet Referral Code: 2tc4hl (use the code a get spamnet for $1.99 per month instead of $4.99!!!)
Re: Granting privileges to other users
At 13:40 -0700 6/3/04, Robert Frame wrote: Thank you for the reply. I apologize for not clearly demonstrating what I am trying to accomplish. No apology necessary. I know what you're trying to accomplish. My questions (which you have not answered) are designed to cause you to think about what your GRANT statement *actually* does, because that will help lead you to understand why that statement doesn't accomplish what you want. I am trying to create an easy method of adding users to my database schema by creating a few template users. Their names are SysAdmin, Manager, and Employee. The SysAdmin will need to be able to Select, Insert, Update and Delete records for all the tables. The Manager will have a narrower range of access, with Select and Update abilities on most tables (but not all), and limited Insert and Delete records. The Employee will have the least access to the tables, with no Insert or Delete capabilities and limited Select and Update privileges. My goal is that once this is setup I can give a new user access to the tables by using the level of privileges of one of the template users, rather then specifying the exact level of access on every table for each user that I add. I am looking for the SQL command to do this. Thanks, Rob Paul DuBois [EMAIL PROTECTED] wrote: At 9:15 -0700 6/2/04, Robert Frame wrote: This is probably something simple that I am just not seeing, but I would appreciate your help. As root, I have created a schema named test, along with several tables. I then created a template user named SysAdmin for test using the following syntax. GRANT SELECT, INSERT, UPDATE, DELETE ON TEST What does ON TEST mean? TO SysAdmin IDENTIFIED BY 'password' What does TO SysAdmin mean? That is, what do *you* think the effect of those clauses should be? WITH GRANT OPTION; I plan on using SysAdmin as a template for other users. How do I do this? I would expect to be able to login as SysAdmin and then create a new user using something like: GRANT ALL ON TEST TO USER1 IDENTIFIED BY Îuser1'; However I continue to get errors saying something like SysAdmin does not have access to MySQL. Do I have to do this as root? If so, what is the syntax to do so? -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can you rename or copy a database?
At 12:44 -0700 6/3/04, David Blomstrom wrote: My database world won't work online, because the online version is username_world, with a different username and password. So I want to create a copy of world named username_world. Databases don't have usernames or passwords. I created a new database named username_world and started importing tables into it, until I hit a snag... MySQL said: #1005 - Can't create table '.\cdcol\counties2.frm' (errno: 150) * * * * * * * * * * The quickest, easiest strategy would be to simply create a copy of my database. But I don't see any copy commands, unless you do it with the export function. What connection does creating a copy of your database have to the error shown above? That is, why would creating a copy of your database make the error go away? If so, would simply save it as a SQL file alongside your other databases? Not sure what that means. What does it mean? SQL LaTeX CSV for Ms Excel data CSV data XML What does this list signify? You've presented it without explanation. Please explain. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Slow querys When ADSL is down on W2K
sound like windows is looking for something.. is there any kind of DNS lookup or a machine in the Network Neighbourhood/Domain that might be unavailable when the adsl goes down? Not a network expert, so just a guess 8-) Peter -Original Message- From: Mauricio Pellegrini [mailto:[EMAIL PROTECTED] Sent: 04 June 2004 00:44 To: MySql List Subject: Slow querys When ADSL is down on W2K Hi, This seems to be a strange situation. I have a server ( Sun fire v65x ) running SuSE 8.2 and My-Sql 4.1.1alpha-max. I've detected that when our adsl internet connection is down the querys on our local network turns to be unusually slow. But this situation occurs only when querys are executed from PC's running W2k. Other machines running Linux, on the same local net, doesn't seem to be affected by this. Those machines performs the exact same querys in a fraction of time compared to the ones running W2k. All the PC's and the server are phisically connected to the same Switch. There's a gateway which provides the internet connection. Querys are performed from within EMS MySQl manager on the W2k boxes and from MySql Control Center on Linux. There's also one more thing, the same query executed from the MySQl CLIENT ( the text based application ) returns normal execution times on the W2k boxes. Soon as the ADSL service is restored execution times return to normal on w2k and remains unchanged on Linux. I can't figure why this happens.. any ideas are especially welcome Thanks Mauricio -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] Email has been scanned for viruses and SPAM by Trader Mailmanager www.trader.uk.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Can you rename or copy a database?
--- Schalk [EMAIL PROTECTED] wrote: I would suggest exporting your current database as a .sql file taking across all structure and data. Create your new database and run the .sql file on it. Thanks for the tip. Let me just make sure I understand the mechanics. Let's say I have a database named works that features four tables. I want to make an exact copy named works2. Would I export works as works2, then create a new database named works2, then place the SQL file works2 inside the database works2? Thanks. __ Do you Yahoo!? Friends. Fun. Try the all-new Yahoo! Messenger. http://messenger.yahoo.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MAX question
Hi, Table mpprim has UNIQUE constraint on 3 fields: Primka, Tip, Godina (Document, Type, Year in English). I am trying to insert a new record into it without having first to count how manny documents of certain type exist in table for choosen year. Sample data: ID(Autoinc) Primka(INT not null) Tip(ENUM(D,M,..) Godina(YEAR) 1 1 D 2004 2 2 D 2004 3 1 M 2004 ... When I do SELECT IFNULL(MAX(Primka),0)+1 AS Primka FROM mpprim WHERE Godina = 2004 AND Tip = M group by Godina I don't get ANY result if there isn't at least one row with Type set to M in choosen year. Is there a way to make this work? Tonci Grgin ARTRONIC d.o.o. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can you rename or copy a database?
I want to make an exact copy named works2. what about INSERT INTO table1 (table1Columns) SELECT table2Columns FROM table2; David Blomstrom [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] --- Schalk [EMAIL PROTECTED] wrote: I would suggest exporting your current database as a .sql file taking across all structure and data. Create your new database and run the .sql file on it. Thanks for the tip. Let me just make sure I understand the mechanics. Let's say I have a database named works that features four tables. I want to make an exact copy named works2. Would I export works as works2, then create a new database named works2, then place the SQL file works2 inside the database works2? Thanks. __ Do you Yahoo!? Friends. Fun. Try the all-new Yahoo! Messenger. http://messenger.yahoo.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help with a SELECT Statement
On Thu, 3 Jun 2004 22:36:55 +0100 Andrew Dixon [EMAIL PROTECTED] wrote: Hi Everyone. I have the following SELECT statement the get information from two tables. The statement only works when it finds a value for the image_archive.circuit value in the circuits table. However, not all entries in the database has a value in the this field, some are set to 0 as the circuit for that image was not known or the image was not a circuit. When you have an unknown value, the best value to use in a relational database instead of 0 or an empty string or anything else is NULL, that's what NULL was actually created for (well, one of the reasons anyway). Then your query would turn into a fairly simple outer join. FROM image_archive ia LEFT OUTER JOIN circuits c ON ia.id = c.circuit WHERE ia.id = 109 SELECT image_archive.filename, image_archive.year, image_archive.month, image_archive.driver_forename, image_archive.driver_surname, image_archive.team, image_archive.event, circuits.name as circuit_name, image_archive.description, image_archive.title, image_archive.membership_no FROM image_archive, circuits WHERE image_archive.id = 109 AND circuits.id = image_archive.circuit Maybe tack on: OR image_archive.circuit = 0? How can I modify the statement to allow it to return a record when the image_archive.circuit value is 0, but to return the circuit name when the value is greater than 0. At the moment when the circuit value is 0 no records are returned even though I know the rest of the information is in the image_archive table. Hope that makes sense. Thanks in advances for any help. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
php to mysql
Hi, I don't know whether I can ask this question on this mailing list or not, but I want to know how the php communicates with mysql, when it is on the localhost. Does it use unix domain sockets or it uses TCP sockets only? Any help or pointers in this regard will be of great help for me. Thanks, ramana. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Self-Join Query
Harold and Brent, Thanks for your help. I tried Brent's solution first, but the MySQL client didn't seem to like the IF statements. The case statements worked sweet though. Cheers guys, James Katarski Systems Administrator School of Computer Information Science Edith Cowan University ML -Original Message- From: news [mailto:[EMAIL PROTECTED] On Behalf Of Harald Fuchs Sent: Thursday, June 03, 2004 10:23 PM To: [EMAIL PROTECTED] Subject: Re: Self-Join Query In article [EMAIL PROTECTED], James KATARSKI [EMAIL PROTECTED] writes: I'm trying to generate a report like this: (Which I've done in TWO querys, the copied and pasted together) +-+--+--+ | Page Name | Internal | External | +-+--+--+ | after_hours | 615 | 105 | | conditions | 332 | 50 | | faq | 89 |2 | | help| 458 | 174 | | labtimes| 682 | 143 | | support | 2151 | 383 | | passwords | 154 | 22 | | practise| 497 | 99 | | printing| 801 | 85 | | wireless| 926 | 180 | +-+--+--+ Using a query like this: SELECT DISTINCT i.page_name AS 'Page Name', COUNT(i.page_name) as 'Internal', COUNT(e.page_name) as 'External' FROM ip_logs i, ip_logs e WHERE (i.hit_no != e.hit_no) AND (i.ip LIKE '10.%' OR i.ip LIKE '139.230.%') #Internal Addresses AND (e.ip NOT LIKE '10.%' AND e.ip NOT LIKE '139.230.%') #External Addresses GROUP BY i.page_name; You could try something like SELECT page_name AS Page Name, sum(CASE WHEN ip LIKE '10.%' OR ip LIKE '139.230.%' THEN 1 ELSE 0 END) AS Internal, sum(CASE WHEN ip LIKE '10.%' OR ip LIKE '139.230.%' THEN 0 ELSE 1 END) AS External FROM tst1 GROUP BY page_name If you have an index on ip, this should be pretty fast. -- 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: php to mysql
venkata ramana wrote: Hi, I don't know whether I can ask this question on this mailing list or not, but I want to know how the php communicates with mysql, when it is on the localhost. Does it use unix domain sockets or it uses TCP sockets only? Any help or pointers in this regard will be of great help for me. Thanks, ramana. if you don't specify a hostname it uses unix sockets. $dbh=mysql_connect() or die(mysql_error()); # uses unix sockets you can prove it by changing mysql.default_socket=/tmp/mysql.sock to something wrong in your php.ini and then watching it fail when you connect. -jsd- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Subscription
Good evening, I would like to subscribe to MySQL mailing list. Thank you. Cedomilj _ FREE pop-up blocking with the new MSN Toolbar get it now! http://toolbar.msn.click-url.com/go/onm00200415ave/direct/01/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
IS NULL works, but IN (NULL) doesn't
How come this one works: SELECT wifi_list, IFNULL(wifi_list, 0) as wifi_list_new, FROM wifi_table LEFT JOIN Swordfish.scanner ON scanner.scanner_id = wifi_table.scanner_id LEFT JOIN wifi_wlist_table ON wifi_table_mac = mac WHERE last_seen = CURRENT_DATE AND wifi_list IS NULL; This one works too SELECT wifi_list, IFNULL(wifi_list, 0) as wifi_list_new, FROM wifi_table LEFT JOIN Swordfish.scanner ON scanner.scanner_id = wifi_table.scanner_id LEFT JOIN wifi_wlist_table ON wifi_table_mac = mac WHERE last_seen = CURRENT_DATE HAVING wifi_list_new IN (0,1,2); But this one doesn't... I *need* this to work: SELECT wifi_list, IFNULL(wifi_list, 0) as wifi_list_new, FROM wifi_table LEFT JOIN Swordfish.scanner ON scanner.scanner_id = wifi_table.scanner_id LEFT JOIN wifi_wlist_table ON wifi_table_mac = mac WHERE last_seen = CURRENT_DATE AND wifi_list IN (NULL); mysql Ver 12.22 Distrib 4.0.16, for pc-linux (i686) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql error
Hi all, I have a problem when I'm trying to connect to mysql server at my web hosting... appears error message like this : Could not connect to MySQL server! 2002: Can't connect to local MySQL server through socket '/usr/home/greatdynamic/mysql/mysql-greatdynamic.sock' (2) never appeared such problem before... I guest that I need to restart mysql (maybe mysql hang after left running all the time).. so how to restart mysql ... I've tried this command : mysql/bin/safe_mysqld (not work) By regard; Sukanto
Re: IS NULL works, but IN (NULL) doesn't
At 18:32 -0700 6/3/04, Daevid Vincent wrote: How come this one works: SELECT wifi_list, IFNULL(wifi_list, 0) as wifi_list_new, FROM wifi_table LEFT JOIN Swordfish.scanner ON scanner.scanner_id = wifi_table.scanner_id LEFT JOIN wifi_wlist_table ON wifi_table_mac = mac WHERE last_seen = CURRENT_DATE AND wifi_list IS NULL; This one works too SELECT wifi_list, IFNULL(wifi_list, 0) as wifi_list_new, FROM wifi_table LEFT JOIN Swordfish.scanner ON scanner.scanner_id = wifi_table.scanner_id LEFT JOIN wifi_wlist_table ON wifi_table_mac = mac WHERE last_seen = CURRENT_DATE HAVING wifi_list_new IN (0,1,2); But this one doesn't... I *need* this to work: If you need it to work, I'm afraid you're out of luck. Think about what a IN (x,y,z) means. It's basically the same as a = x or a = y or a = z. So wifi_list IN (NULL) is like wifi_list = NULL, which is never true. SELECT wifi_list, IFNULL(wifi_list, 0) as wifi_list_new, FROM wifi_table LEFT JOIN Swordfish.scanner ON scanner.scanner_id = wifi_table.scanner_id LEFT JOIN wifi_wlist_table ON wifi_table_mac = mac WHERE last_seen = CURRENT_DATE AND wifi_list IN (NULL); -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Subscription
On Thu, 03 Jun 2004 14:56:23 -0500 Tom Murdock [EMAIL PROTECTED] wrote: Good evening, I would like to subscribe to MySQL mailing list. Thank you. Your wish appears to have been granted. :) Josh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Subscription
Dear Tom [aka Cedomilj], Thank you for interest in MySQL AB - the world leader ... (blah-blah). Subscribing to MySQL mailing list is a free and unattended service. You don't have to remit any money to Nigeria (ZERO MILLION DOLLARS). Just sit back on your chair and read messages, posted to mailing list. No special arrangements needed. Best regards, MySQL DB Supporter - Original Message - From: Tom Murdock [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, June 03, 2004 12:56 PM Subject: Subscription Good evening, I would like to subscribe to MySQL mailing list. Thank you. Cedomilj _ FREE pop-up blocking with the new MSN Toolbar get it now! http://toolbar.msn.click-url.com/go/onm00200415ave/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]
