DATETIME question
Given a column DATETIMEcolumn (-MM-DD HH:MM:SS), is there a SELECT statement that will: select all entries whose (TIME) of DATETIMEcolumn is BETWEEN 'HH:MM:SS' AND 'HH:MM:SS', but whose (DATE) is anything? Thanks, John -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Server - automatic shutdown
Hello: We are using MySQL database on a FreeBSD server. The problem we are facing is sometimes the MySQL server is automatically shutting down. There is no error logged on the server. The entry in the log file mysite.com.err shows: 040525 21:52:37 /usr/local/mysql-3.23.49/libexec/mysqld: Normal shutdown This happened more than once in the last 2 months. Can anybody help me to fix this problem on the server. Sheni R Meledath [EMAIL PROTECTED]
Re: Large database with High Availability - how to do it?
Jochem van Dieten wrote: Daniel Ek wrote: Jonas Oreland wrote: Daniel Ek wrote: I today have a database using 20GB storage, which actually means that I should have at least 30GB (40GB recommended) ram using Mysql cluster. This could be 40 boxes with 1G ram each. But doesn't that increase the locking conflicts significantly? It is my understanding that 2PC locking conflicts scale with the third power of the number of nodes: http://citeseer.ist.psu.edu/gray96danger.html 1) We use primay copy, so locking is done in a predefined order per row. 2) The data is partitioned and not fully replicated 3) The partitioning is transparent to a MySQL user, but there are means of influencing the partitioning (still not exposed in sql :-() With this we achived good scalability, but depending on the usage profile, ethernet can put a limit to the scalability (with our current latest-and-greatest solutions). This is why we also work with different interconnects such as sci. Btw: We're also working on adding disk data, i.e. data that does not have to reside in ram all the time. This will however probably not be finished until the end of this year. How about a synchronous transaction log? 99.999% availability is nice for the PHB, but my concern is what happens when the 0.001% hits the fan, the whole cluster goes down and the transaction log has not yet been written to disk. The database does continuous checkpointing to disk, but does not flush the log on commit. So in the unlikely event of system failure, it will restart from the lastest checkpoint. The checkpointing interval is configurable (with a default of 1 sec) Disk durable commits is on the release plan (somewhere in time) Does MySQL Cluster meet the D of ACID? In the sence, that each commit is made on multiple boxes. Note also that the no of copies is configurable, between 1 4, so with 4 copies, the data is commited in 4 boxes before ack-ed to the users. Regards, Jonas Oreland -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Large database with High Availability - how to do it?
Hi Daniel, Daniel Ek wrote: Jonas Oreland wrote: Hi Daniel, Daniel Ek wrote: I really thought that MySQL tried to produce MySQL cluster for common hardware and with the design principle; more nodes with common hardware, insted of few nodes with extreme hardware. we do. I will probably never work for a company that can afford that kind of super computers. I today have a database using 20GB storage, which actually means that I should have at least 30GB (40GB recommended) ram using Mysql cluster. This could be 40 boxes with 1G ram each. Hopefully this would increase the chances of you ...for a company :-) Is there anyone that have a comment regarding how MySQL cluster acts with common hardware (if at all possible) The machines that you used sounded good. Btw: We're also working on adding disk data, i.e. data that does not have to reside in ram all the time. This will however probably not be finished until the end of this year. Regards, Jonas Oreland Thanks Jonas, That sounds a lot better in my ears and will probably be even better if the data does not have to reside in the ram all the time. Some followup questions to your answers though. 1 ) Just curious; e.g a power failure if the whole database resides in RAM wouldn't that mean data loss for the data that resides in the RAM or does the NBD engine simply add duplicate data on at least one node? It actully configurable how many copies of the data that will be created. (between 1 - 4, where 1 means only one version (not an extra copy)) If I have 40 nodes and a 20GB database would that mean that 1/20 would actually reside on 2 physical nodes and so on? yes. 2 ) Except obvious perfomance enhancements with the whole DB residing in RAM, are there any other reasons why you choose that approach? not really 3 ) I have tried to locate information on when 4.1 will be released as production release. Do you or anyone else in this list have any information regarding that? I don't know, probably someone else on this list knows... 4 ) I have also tried to locate price information on MySQL cluster but since it is included in version 4.1 would I be correct to assume that it is still the same license fee as MySQL Pro? I don't know, probably someone else on this list knows... --- General comment: Please download and read our whitepapers which will give a more complete picture than my short answers. I really wanted to say that you _dont_ need a machine with 40G ram to run a 40G database. And I will probably never use a machine with 40G ram either :-) Regards, Jonas Oreland -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Large database with High Availability - how to do it?
Hi Daniel, Daniel Ek wrote: I really thought that MySQL tried to produce MySQL cluster for common hardware and with the design principle; more nodes with common hardware, insted of few nodes with extreme hardware. we do. I will probably never work for a company that can afford that kind of super computers. I today have a database using 20GB storage, which actually means that I should have at least 30GB (40GB recommended) ram using Mysql cluster. This could be 40 boxes with 1G ram each. Hopefully this would increase the chances of you ...for a company :-) Is there anyone that have a comment regarding how MySQL cluster acts with common hardware (if at all possible) The machines that you used sounded good. Btw: We're also working on adding disk data, i.e. data that does not have to reside in ram all the time. This will however probably not be finished until the end of this year. Regards, Jonas Oreland -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
slow insert into select statement
I have this insert statement that took a long time to execute: INSERT INTO priceLast5 SELECT DISTINCT a.commodity, a.PricingDt FROM rptPricingTEST a WHERE a.PricingDt = ( SELECT MAX(PricingDt) FROM rptPricingTEST b WHERE b.PricingDt @date1 AND b.PricingDt = @date2 AND a.commodity = b.commodity ); Query OK, 3010 rows affected (4 min 17.37 sec) Is there any way I can modify the sql statement to speed this up? Here's the result of explain (splitted for easy reading): id select_typetable type possible_keys key -- -- - - - 1 PRIMARYa index NULL idxcommodity 2 DEPENDENT SUBQUERY b ref idxcommodity idxcommodity id key_len ref rows Extra -- --- - -- 1 25 NULL 384155 Using where; Using index 2 22 dbsdvitmp.a.commodity 52 Using where; Using index And here's the rest of the sql before the above insert: SELECT @date1:=DATE_SUB( CURDATE(), INTERVAL 1 MONTH); SELECT @date2:=CURDATE(); CREATE TEMPORARY TABLE priceLast5 ( commodity char(22) NOT NULL, PricingDt date NOT NULL, PRIMARY KEY (commodity), KEY idxPricingDt (PricingDt), ); LOCK TABLES rptPricingTEST a READ; The source table rptPricingTEST consists of commodity id (which is a concat of several ids), pricing date, and prices (low, hi, avg). I need to generate a report of price change for all commodity for the date interval selected by users. Since this will be a web-based report, to wait for 4 minutes for a response is unacceptable. The actual table has 2 million rows which I guess can take forever to execute. regards, nyem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: DATETIME question
John Mistler [EMAIL PROTECTED] wrote: Given a column DATETIMEcolumn (-MM-DD HH:MM:SS), is there a SELECT statement that will: select all entries whose (TIME) of DATETIMEcolumn is BETWEEN 'HH:MM:SS' AND 'HH:MM:SS', but whose (DATE) is anything? SELECT .. FROM t1 WHERE DATE_FORMAT(datetime_column, '%T') BETWEEN 'HH:MM:SS' AND 'HH:MM:SS'; or from version 4.1.1 you can use TIME() function for this purpose. Description of DATE_FROMAT() and TIME function you can see at: http://dev.mysql.com/doc/mysql/en/Date_and_time_functions.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: GROUP BY with MAX
Batara Kesuma [EMAIL PROTECTED] wrote: I have a table that looks like: mysql select * from test3; ++++ | sub_id | date | data | ++++ | 1 | 2004-05-01 | data 001 | | 1 | 2004-05-02 | data 002 | | 1 | 2004-05-03 | data 003 | | 2 | 2004-06-01 | data 2 001 | | 2 | 2004-06-02 | data 2 002 | | 2 | 2004-06-03 | data 2 003 | ++++ 6 rows in set (0.00 sec) If I group it by sub_id this is what I get: mysql select * from test3 group by sub_id; ++++ | sub_id | date | data | ++++ | 1 | 2004-05-01 | data 001 | | 2 | 2004-06-01 | data 2 001 | ++++ 2 rows in set (0.01 sec) I want to get the data from the MAX(date) grouped by sub_id, the result I want is: ++++ | sub_id | date | data | ++++ | 1 | 2004-05-03 | data 003 | | 2 | 2004-06-03 | data 2 003 | ++++ How can I do that? CREATE TEMPORARY TABLE tmp SELECT sub_id, MAX(date) AS date FROM test3 GROUP BY sub_id; SELECT test3.* FROM test3, tmp WHERE test3.sub_id=tmp.sub_id AND test3.date=tmp.date; If your version of MySQL server =4.1, subqueries may help you. http://dev.mysql.com/doc/mysql/en/example-Maximum-column-group-row.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL Server - automatic shutdown
Hi Sheni, I would start by running MySQL 3.23.58 which is the latest released version of the MySQL 3.23 branch. Also running MySQL on FreeBSD with linuxthreads enabled. If you cvsup your ports collection doing something like: cd /usr/ports/databases/mysql323-server make WITH_LINUXTHREADS=yes BUILD_OPTIMIZED=yes If you use portupgrade remember to use portupgrade -v mysql-server -M WITH_LINUXTHREADS=yes BUILD_OPTIMIZED=yes I tend to find running MySQL with linuxthreads and built with some optimisations improves MySQL's speed on Regards --jm -Original Message- From: Sheni R. Meledath [mailto:[EMAIL PROTECTED] Sent: 26 May 2004 08:29 AM To: MySQL Masters Subject: MySQL Server - automatic shutdown Hello: We are using MySQL database on a FreeBSD server. The problem we are facing is sometimes the MySQL server is automatically shutting down. There is no error logged on the server. The entry in the log file mysite.com.err shows: 040525 21:52:37 /usr/local/mysql-3.23.49/libexec/mysqld: Normal shutdown This happened more than once in the last 2 months. Can anybody help me to fix this problem on the server. Sheni R Meledath [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problems with MySQL 4.0.20
Hi! On May 25, Steven Roussey wrote: We had some servers that were upgraded from 4.0.17/18 to 4.0.20 and had several problems thereafter: 1. Tables with FTS indices became corrupted, with queries on them causing segfaults on the servers. Hmm, I don't see any changes in ft-related files since 4.0.18 that could cause it (there were bugfixes, but they affect only *searching* - that is MATCH - and not *updating*). Can you create a test case ? 2. BinLog files were getting created with ownership of root, not mysql. Then Mysql complains that it can not read the file and so goes and creates another (which is fine and owned by mysql). All slaves to the master then die with corruption warnings about the master. I don't really understand how it can happen - I'll let others comment on it. 3. All servers suddenly have a lot of connection errors: Aborted connection 109 to db: 'xyz' user: 'aaa' host: `something.i' (Got timeout reading communication packets) I think, this is because --log-warnings was changed to be ON by default. Disable with --skip-log-warnings 4. Thread stack warnings: Warning: Asked for 196608 thread stack, but got 126976 Same here. Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problems with MySQL 4.0.20
From: Sergei Golubchik [EMAIL PROTECTED] 4. Thread stack warnings: Warning: Asked for 196608 thread stack, but got 126976 Same here. OK, we can disable the warnings in the log file, but what's really behind this warning? A brand new, plain vanilla Fedora Core2 (aka RedHat FC2) installation with MySQL 4.0.20 produced this warning immediately. Does MySQL want more thread stack space? How badly does it need it? How can one make the OS to give it more? Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Running MySQL and PostgreSQL on the same hardware
Hi All, Does anyone have any experience of running MySQL and PostgreSQL on the same hardware? At the moment we have several reasonable fast servers (dual Xeon GHz, 1GB ram, 15,000rpm scsi disk) running MySQL in a replicated environment with high volumes of queries (high read:write ratio) and I now have a need to deploy PostgreSQL but I don't particularly want to buy a stack of hardware to do this. I would love to hear any experiences / problems that people have had or any ideas on the subject at all. Looking forward to the replies. Cheers, Andrew -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Running MySQL and PostgreSQL on the same hardware
Andrew I've done it but only in a test environment. I actually had 3 different versions of Mysql running plus Postgres. Each of the Mysql's and Postgress were installed to /opt/database-version and paths set accordingly. I've had no problems and they databases have been continuously up for over a month. Kevin Cowley RD Tel: 0118 902 9099 (direct line) Email: [EMAIL PROTECTED] Web: http://www.alchemetrics.co.uk -Original Message- From: Andrew Braithwaite [mailto:[EMAIL PROTECTED] Sent: 26 May 2004 10:47 To: [EMAIL PROTECTED] Subject: Running MySQL and PostgreSQL on the same hardware Hi All, Does anyone have any experience of running MySQL and PostgreSQL on the same hardware? At the moment we have several reasonable fast servers (dual Xeon GHz, 1GB ram, 15,000rpm scsi disk) running MySQL in a replicated environment with high volumes of queries (high read:write ratio) and I now have a need to deploy PostgreSQL but I don't particularly want to buy a stack of hardware to do this. I would love to hear any experiences / problems that people have had or any ideas on the subject at all. Looking forward to the replies. Cheers, Andrew -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] ** ALCHEMETRICS LIMITED (ALCHEMETRICS) Mulberry Park, Fishponds Road, Wokingham, Berkshire, RG41 2GX Tel: +44 (0) 118 902 9000Fax: +44 (0) 118 902 9001 This e-mail is confidential and is intended for the use of the addressee only. If you are not the intended recipient, you are hereby notified that you must not use, copy, disclose, otherwise disseminate or take any action based on this e-mail or any information herein. If you receive this transmission in error, please notify the sender immediately by reply e-mail or by using the contact details above and then delete this e-mail. Please note that e-mail may be susceptible to data corruption, interception and unauthorised amendment. Alchemetrics does not accept any liability for any such corruption, interception, amendment or the consequences thereof. ** -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL SMP Question
Hi. We need to bind mysql-server process to a specific processor on dual pentium iii machine. Is it possible? Ilia. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL SMP Question
Hi, On Linux this feature is available in 2.6 through system calls. See: http://www.linuxjournal.com/article.php?sid=6799 On Windows it is also available at least as function calls See: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ dllproc/base/multiple_processors.asp On Solaris you have a command psrset available from Solaris 2.6 that will do the trick. See: http://www.platform.com/services/support/services/Procset.asp Rgrds Mikael 2004-05-26 kl. 11.40 skrev : Hi. We need to bind mysql-server process to a specific processor on dual pentium iii machine. Is it possible? Ilia. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] Mikael Ronstrm, Senior Software Architect MySQL AB, www.mysql.com Clustering: http://www.infoworld.com/article/04/04/14/HNmysqlcluster_1.html http://www.eweek.com/article2/0,1759,1567546,00.asp
mysql_install_db problem
I'm on Linux and had to install the myself server since that didn't work from the Linux install cd. When I run mysql_install_db I get host name lookup failure . Please configure the hostname. However in my Linux hosts table I DO have the localhost address. and if I do resolveip localhost from the command line I get back that address. Please let me know what needs to be done to fix this. Thanks, sb
RE: need help with a complicated join
man i didnt even know you can do this AND s.date q.date i assumed that goes in a where clause ? -Original Message- From: news [mailto:[EMAIL PROTECTED] Behalf Of Harald Fuchs Sent: Wednesday, May 26, 2004 8:01 PM To: [EMAIL PROTECTED] Subject: Re: need help with a complicated join In article [EMAIL PROTECTED], Harald Fuchs [EMAIL PROTECTED] writes: SELECT q.symbol, q.date, q.quote * product (s.split_from / s.split_to) AS adjusted_quote FROM quotes q LEFT JOIN splits s ON s.symbol = q.symbol AND s.date q.date GROUP BY q.symbol, q.date, q.quote ORDER BY q.symbol, q.date The problem is how to define the 'product' aggregate (along the lines of 'sum'). [ I like talking to myself :-) ] A workaround for the missing product aggregate would be SELECT q.symbol, q.date, q.quote, q.quote * exp(sum(log(coalesce(s.split_from/s.split_to,1 FROM quotes q LEFT JOIN splits s ON s.symbol = q.symbol AND s.date q.date GROUP BY q.symbol, q.date, q.quote ORDER BY q.symbol, q.date but don't ask me how that performs... -- 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: Running MySQL and PostgreSQL on the same hardware
Thanks Kevin, I am comfortable with the software installs etc.. I was more concerned with hardware bottlenecks and OS (linux 2.4) problems etc.. Any pointers would be great.. Cheers, Andrew -Original Message- From: Kevin Cowley [mailto:[EMAIL PROTECTED] Sent: Wednesday 26 May 2004 10:53 To: Andrew Braithwaite; [EMAIL PROTECTED] Subject: RE: Running MySQL and PostgreSQL on the same hardware Andrew I've done it but only in a test environment. I actually had 3 different versions of Mysql running plus Postgres. Each of the Mysql's and Postgress were installed to /opt/database-version and paths set accordingly. I've had no problems and they databases have been continuously up for over a month. Kevin Cowley RD Tel: 0118 902 9099 (direct line) Email: [EMAIL PROTECTED] Web: http://www.alchemetrics.co.uk -Original Message- From: Andrew Braithwaite [mailto:[EMAIL PROTECTED] Sent: 26 May 2004 10:47 To: [EMAIL PROTECTED] Subject: Running MySQL and PostgreSQL on the same hardware Hi All, Does anyone have any experience of running MySQL and PostgreSQL on the same hardware? At the moment we have several reasonable fast servers (dual Xeon GHz, 1GB ram, 15,000rpm scsi disk) running MySQL in a replicated environment with high volumes of queries (high read:write ratio) and I now have a need to deploy PostgreSQL but I don't particularly want to buy a stack of hardware to do this. I would love to hear any experiences / problems that people have had or any ideas on the subject at all. Looking forward to the replies. Cheers, Andrew -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] ** ALCHEMETRICS LIMITED (ALCHEMETRICS) Mulberry Park, Fishponds Road, Wokingham, Berkshire, RG41 2GX Tel: +44 (0) 118 902 9000Fax: +44 (0) 118 902 9001 This e-mail is confidential and is intended for the use of the addressee only. If you are not the intended recipient, you are hereby notified that you must not use, copy, disclose, otherwise disseminate or take any action based on this e-mail or any information herein. If you receive this transmission in error, please notify the sender immediately by reply e-mail or by using the contact details above and then delete this e-mail. Please note that e-mail may be susceptible to data corruption, interception and unauthorised amendment. Alchemetrics does not accept any liability for any such corruption, interception, amendment or the consequences thereof. ** -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: slow insert into select statement
If you do the math a large result set will be created. You could rewriting your query or adding more indexes to see if this speeds up the process. -Original Message- From: nyem To: [EMAIL PROTECTED] Sent: 5/26/04 2:57 AM Subject: slow insert into select statement I have this insert statement that took a long time to execute: INSERT INTO priceLast5 SELECT DISTINCT a.commodity, a.PricingDt FROM rptPricingTEST a WHERE a.PricingDt = ( SELECT MAX(PricingDt) FROM rptPricingTEST b WHERE b.PricingDt @date1 AND b.PricingDt = @date2 AND a.commodity = b.commodity ); Query OK, 3010 rows affected (4 min 17.37 sec) Is there any way I can modify the sql statement to speed this up? Here's the result of explain (splitted for easy reading): id select_typetable type possible_keys key -- -- - - - 1 PRIMARYa index NULL idxcommodity 2 DEPENDENT SUBQUERY b ref idxcommodity idxcommodity id key_len ref rows Extra -- --- - -- 1 25 NULL 384155 Using where; Using index 2 22 dbsdvitmp.a.commodity 52 Using where; Using index And here's the rest of the sql before the above insert: SELECT @date1:=DATE_SUB( CURDATE(), INTERVAL 1 MONTH); SELECT @date2:=CURDATE(); CREATE TEMPORARY TABLE priceLast5 ( commodity char(22) NOT NULL, PricingDt date NOT NULL, PRIMARY KEY (commodity), KEY idxPricingDt (PricingDt), ); LOCK TABLES rptPricingTEST a READ; The source table rptPricingTEST consists of commodity id (which is a concat of several ids), pricing date, and prices (low, hi, avg). I need to generate a report of price change for all commodity for the date interval selected by users. Since this will be a web-based report, to wait for 4 minutes for a response is unacceptable. The actual table has 2 million rows which I guess can take forever to execute. regards, nyem -- 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: DATETIME question
Use the time_format function. -Original Message- From: John Mistler To: [EMAIL PROTECTED] Sent: 5/26/04 1:15 AM Subject: DATETIME question Given a column DATETIMEcolumn (-MM-DD HH:MM:SS), is there a SELECT statement that will: select all entries whose (TIME) of DATETIMEcolumn is BETWEEN 'HH:MM:SS' AND 'HH:MM:SS', but whose (DATE) is anything? Thanks, John -- 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: Running MySQL and PostgreSQL on the same hardware
Andrew I've not seen any problems, but them none of the databases are particularly loaded and most of the time are not being accessed concurrently. In all cases except for Mysql.3.23.52 - (which is the default patched) install on SLES8, I built the databases from source Postgres-7.4.2, Mysql-4.1.1, and Mysql-4.1.2-alpha-nightly. All our partitions are Reiser and the test box in question is a twin processor PIV with Raided 144GB disc on which all the databases sit. Kevin Cowley RD Tel: 0118 902 9099 (direct line) Email: [EMAIL PROTECTED] Web: http://www.alchemetrics.co.uk -Original Message- From: Andrew Braithwaite [mailto:[EMAIL PROTECTED] Sent: 26 May 2004 14:10 To: [EMAIL PROTECTED] Subject: RE: Running MySQL and PostgreSQL on the same hardware Thanks Kevin, I am comfortable with the software installs etc.. I was more concerned with hardware bottlenecks and OS (linux 2.4) problems etc.. Any pointers would be great.. Cheers, Andrew -Original Message- From: Kevin Cowley [mailto:[EMAIL PROTECTED] Sent: Wednesday 26 May 2004 10:53 To: Andrew Braithwaite; [EMAIL PROTECTED] Subject: RE: Running MySQL and PostgreSQL on the same hardware Andrew I've done it but only in a test environment. I actually had 3 different versions of Mysql running plus Postgres. Each of the Mysql's and Postgress were installed to /opt/database-version and paths set accordingly. I've had no problems and they databases have been continuously up for over a month. Kevin Cowley RD Tel: 0118 902 9099 (direct line) Email: [EMAIL PROTECTED] Web: http://www.alchemetrics.co.uk -Original Message- From: Andrew Braithwaite [mailto:[EMAIL PROTECTED] Sent: 26 May 2004 10:47 To: [EMAIL PROTECTED] Subject: Running MySQL and PostgreSQL on the same hardware Hi All, Does anyone have any experience of running MySQL and PostgreSQL on the same hardware? At the moment we have several reasonable fast servers (dual Xeon GHz, 1GB ram, 15,000rpm scsi disk) running MySQL in a replicated environment with high volumes of queries (high read:write ratio) and I now have a need to deploy PostgreSQL but I don't particularly want to buy a stack of hardware to do this. I would love to hear any experiences / problems that people have had or any ideas on the subject at all. Looking forward to the replies. Cheers, Andrew -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] ** ALCHEMETRICS LIMITED (ALCHEMETRICS) Mulberry Park, Fishponds Road, Wokingham, Berkshire, RG41 2GX Tel: +44 (0) 118 902 9000Fax: +44 (0) 118 902 9001 This e-mail is confidential and is intended for the use of the addressee only. If you are not the intended recipient, you are hereby notified that you must not use, copy, disclose, otherwise disseminate or take any action based on this e-mail or any information herein. If you receive this transmission in error, please notify the sender immediately by reply e-mail or by using the contact details above and then delete this e-mail. Please note that e-mail may be susceptible to data corruption, interception and unauthorised amendment. Alchemetrics does not accept any liability for any such corruption, interception, amendment or the consequences thereof. ** -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] ** ALCHEMETRICS LIMITED (ALCHEMETRICS) Mulberry Park, Fishponds Road, Wokingham, Berkshire, RG41 2GX Tel: +44 (0) 118 902 9000Fax: +44 (0) 118 902 9001 This e-mail is confidential and is intended for the use of the addressee only. If you are not the intended recipient, you are hereby notified that you must not use, copy, disclose, otherwise disseminate or take any action based on this e-mail or any information herein. If you receive this transmission in error, please notify the sender immediately by reply e-mail or by using the contact details above and then delete this e-mail. Please note that e-mail may be susceptible to data corruption, interception and unauthorised amendment. Alchemetrics does not accept any liability for any such corruption, interception, amendment or the consequences thereof. ** -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: need help with a complicated join
Harold, you win the EUREKA prize of the month! I had forgotten all about that silly algebraic trick. This answers another person's post from last week. (I will try to find it again) also looking for a PRODUCT() function. I agree about the unknown performance. If you only need to return adjusted quotes for particular symbols and a date range, you *may* be better off pre-calcuating the LOG()s in a temp table for what you need to quote. You can combine that with the stock symbols and the date ranges (from your main query) to minimize the number of items that the LEFT JOIN ends up processing. something like : CREATE TEMPORARY TABLE tmpSplits SELECT Symbol, date, log(split_from/split_to) as logadj FROM splits WHERE date *earliest date* AND Symbol in (*list of symbols*) SELECT q.symbol, q.date, q.quote, q.quote * COALESCE(exp(sum(ts.logadj)), 1) FROM quotes q LEFT JOIN tmpSplits ts ON ts.symbol = q.symbol AND ts.date q.date WHERE q.symbol in (*list of symbols*) and q.date *earliest date* GROUP BY q.symbol, q.date, q.quote ORDER BY q.symbol, q.date only benchmarking will truly show which is faster. Once again - A HUGE way-to-go for the math lesson! Shawn Green Database Administrator Unimin Corporation - Spruce Pine Harald Fuchs [EMAIL PROTECTED]To: [EMAIL PROTECTED] .netcc: Sent by: newsFax to: [EMAIL PROTECTED]Subject: Re: need help with a complicated join rg 05/26/2004 06:00 AM Please respond to hf517 In article [EMAIL PROTECTED], Harald Fuchs [EMAIL PROTECTED] writes: SELECT q.symbol, q.date, q.quote * product (s.split_from / s.split_to) AS adjusted_quote FROM quotes q LEFT JOIN splits s ON s.symbol = q.symbol AND s.date q.date GROUP BY q.symbol, q.date, q.quote ORDER BY q.symbol, q.date The problem is how to define the 'product' aggregate (along the lines of 'sum'). [ I like talking to myself :-) ] A workaround for the missing product aggregate would be SELECT q.symbol, q.date, q.quote, q.quote * exp(sum(log(coalesce(s.split_from/s.split_to,1 FROM quotes q LEFT JOIN splits s ON s.symbol = q.symbol AND s.date q.date GROUP BY q.symbol, q.date, q.quote ORDER BY q.symbol, q.date but don't ask me how that performs... -- 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]
UTF-8 settings and woes
hi,
i am trying to write utf-8 data via java into sql but
it wont work as expected.
first my setup
- suse 9.0
- kde 3.2
mysql SHOW VARIABLES LIKE 'char%';
+--++
| 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 | /usr/share/mysql/charsets/ |
| character_set_results| utf8 |
| version | 4.1.1-alpha-standard |
| version_comment | Official MySQL RPM |
| version_compile_machine | i686 |
| version_compile_os | pc-linux |
| wait_timeout | 28800 |
+--++
now i have the following script
[code]
[EMAIL PROTECTED]: more example_insert.sql
insert into CTPE_V01_00.CUSTOMER (ID, FAMILY_NAME, GIVEN_NAME)
values ('01', 'Käßsel', 'Böb');
insert into CTPE_V01_00.CUSTOMER (ID, FAMILY_NAME, GIVEN_NAME)
values ('02', 'Ægÿl', 'Àlbért');
insert into CTPE_V01_00.CUSTOMER (ID, FAMILY_NAME, GIVEN_NAME)
values ('03', '???', '???');
[/code]
which i execute and afterwards display is completely ok
[code]
mysql example_insert.sql
mysql use CTPE_V01_00;
mysql select FAMILY_NAME, GIVEN_NAME from CUSTOMER;
+++
| FAMILY_NAME| GIVEN_NAME |
+++
| Käßsel | Böb |
| Ægÿl | Àlbért |
| ??? | ??? |
+++
3 rows in set (0,00 sec)
mysql
[/code]
now i want to do that with java code instead. i tried
the following connectors
- mysql-connector-java-3.0.12-production-bin.jar
- mysql-connector-java-3.1.1-alpha-bin.jar
here's my code
[code]
public void testMySql()
{
String url = jdbc:mysql://localhost/ +
?autoReconnect=true +
useUnicode=true +
characterEncoding=utf8;
String cls = com.mysql.jdbc.Driver;
String user = ...;
String pwd = ...;
execute(url, cls, user, pwd);
}
public void execute(String _con, String _class,
String _user, String _pwd){
Connection con;
Statement stmt;
try{
Class.forName(_class);}
catch (java.lang.ClassNotFoundException e){
System.err.print(ClassNotFoundException: );
System.err.println(e.getMessage());}
try{
String sql1 = insert into CTPE_V01_00.CUSTOMER +
(ID, SHORT_ID, FAMILY_NAME, GIVEN_NAME) +
values ('01', '01','Käßsel', 'Böb');;
String sql2 = insert into CTPE_V01_00.CUSTOMER +
(ID, SHORT_ID, FAMILY_NAME, GIVEN_NAME) +
values ('02', '02','Ægÿl', 'Àlbért');;
String sql3 = insert into CTPE_V01_00.CUSTOMER +
(ID, SHORT_ID, FAMILY_NAME, GIVEN_NAME) +
values ('03', '03','???', +
'???');;
con = DriverManager.getConnection(_con, _user, _pwd);
stmt = con.createStatement();
stmt.executeUpdate(sql1);
stmt.executeUpdate(sql2);
stmt.executeUpdate(sql3);
stmt.close();
con.close();}
catch (SQLException ex){
ex.printStackTrace();}}
[/code]
now this results in the following output on the console
[code]
mysql select FAMILY_NAME, GIVEN_NAME from CUSTOMER;
+--+--+
| FAMILY_NAME | GIVEN_NAME
|
+--+--+
| KäÃsel | Böb
|
| Ãgÿl | Ãlbért
|
| инÑеÑнаÑион | инÑеÑнаÑион |
+--+--+
3 rows in set (0,00 sec)
[/code]
sigh not exactly what i expected.
what is also interesting is if i use DBFace (a eclipse plugin) in
combination with the drivers i mentioned the result is better - it is
not correct but it is better (not so much garbage).
any help is greatly appreciated.
tia
ciao robertj
smime.p7s
Description: S/MIME Cryptographic Signature
RE: slow insert into select statement
Hi, I would start with finding out if it's the select or the insert that's taking a long time... Does the priceLast5 table have heavy indexes to build? Try running the select seperately and see how long it takes... Andrew -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: Wednesday 26 May 2004 14:13 To: 'nyem '; '[EMAIL PROTECTED] ' Subject: RE: slow insert into select statement If you do the math a large result set will be created. You could rewriting your query or adding more indexes to see if this speeds up the process. -Original Message- From: nyem To: [EMAIL PROTECTED] Sent: 5/26/04 2:57 AM Subject: slow insert into select statement I have this insert statement that took a long time to execute: INSERT INTO priceLast5 SELECT DISTINCT a.commodity, a.PricingDt FROM rptPricingTEST a WHERE a.PricingDt = ( SELECT MAX(PricingDt) FROM rptPricingTEST b WHERE b.PricingDt @date1 AND b.PricingDt = @date2 AND a.commodity = b.commodity ); Query OK, 3010 rows affected (4 min 17.37 sec) Is there any way I can modify the sql statement to speed this up? Here's the result of explain (splitted for easy reading): id select_typetable type possible_keys key -- -- - - - 1 PRIMARYa index NULL idxcommodity 2 DEPENDENT SUBQUERY b ref idxcommodity idxcommodity id key_len ref rows Extra -- --- - -- 1 25 NULL 384155 Using where; Using index 2 22 dbsdvitmp.a.commodity 52 Using where; Using index And here's the rest of the sql before the above insert: SELECT @date1:=DATE_SUB( CURDATE(), INTERVAL 1 MONTH); SELECT @date2:=CURDATE(); CREATE TEMPORARY TABLE priceLast5 ( commodity char(22) NOT NULL, PricingDt date NOT NULL, PRIMARY KEY (commodity), KEY idxPricingDt (PricingDt), ); LOCK TABLES rptPricingTEST a READ; The source table rptPricingTEST consists of commodity id (which is a concat of several ids), pricing date, and prices (low, hi, avg). I need to generate a report of price change for all commodity for the date interval selected by users. Since this will be a web-based report, to wait for 4 minutes for a response is unacceptable. The actual table has 2 million rows which I guess can take forever to execute. regards, nyem -- 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: is there a PRODUCT() or MULTIPLY() aggregate function?
Everyone can thank Harold Fuchs for contributing this one: exp(sum(log(coalesce(*the field you want to multiply*,1))) The coalesce() function is there to guard against trying to calculate the logarithm of a null value and may be optional depending on your circumstances. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Victor Pendelton in response to Fyodor wrote No there is not. Two options could be to either create an UDF or use a programming language. -Original Message- From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: 5/25/04 1:25 PM Subject: is there a PRODUCT() or MULTIPLY() aggregate function? Is there an aggregate function that multiplies column values just like SUM() adds them? For instance, if SELECT value FROM data returns three values 2, 3, and 4, then SELECT PRODUCT(value) FROM data would return 24, which is 2 * 3 * 4. Thanks! --- Fyodor Golos Stockworm, Inc. /Victor to Fyodor -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: slow insert into select statement
I would also question the sub-select in the WHERE clause. I suspect that it is being evaluated once for each row of rptPricingTEST. You could get a significant performance boost if you move the results of that select into a temp table and INNER JOIN to it. That way you calculate the MAX() date only once for the entire table and not once per row (per row, per row,...) CREATE TEMPORARY TABLE tmpLatestDates SELECT commodity, MAX(PricingDt) as MaxDate FROM rptPricingTEST WHERE PricingDt @date1 AND PricingDt = @date2 GROUP BY commodity INSERT INTO priceLast5 SELECT DISTINCT a.commodity, a.PricingDt FROM rptPricingTEST a INNER JOIN tmpLatestDates b ON b.commodity = a.commodity AND b.PricingDt = a.PricingDt (By using the INNER JOIN, I question if the DISTINCT still needed, too.) After reviewing your original post, I am not confident that you are answering your question with this query. You said I need to generate a report of price change for all commodity for the date interval selected by users. which to me implies a report something like: +-+-+---+--++-+-+-+ |commodity|starting date|ending date|starting price|ending price|max price|min price|avg price| +-+-+---+--++-+-+-+ I am just not sure where you are going with the query you are building. Can you provide a template of the results you want? Thanks, Shawn Green Database Administrator Unimin Corporation - Spruce Pine Andrew Braithwaite To: Victor Pendleton [EMAIL PROTECTED], nyem [EMAIL PROTECTED] [EMAIL PROTECTED], [EMAIL PROTECTED] com cc: Fax to: 05/26/2004 10:28 Subject: RE: slow insert into select statement AM Hi, I would start with finding out if it's the select or the insert that's taking a long time... Does the priceLast5 table have heavy indexes to build? Try running the select seperately and see how long it takes... Andrew -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: Wednesday 26 May 2004 14:13 To: 'nyem '; '[EMAIL PROTECTED] ' Subject: RE: slow insert into select statement If you do the math a large result set will be created. You could rewriting your query or adding more indexes to see if this speeds up the process. -Original Message- From: nyem To: [EMAIL PROTECTED] Sent: 5/26/04 2:57 AM Subject: slow insert into select statement I have this insert statement that took a long time to execute: INSERT INTO priceLast5 SELECT DISTINCT a.commodity, a.PricingDt FROM rptPricingTEST a WHERE a.PricingDt = ( SELECT MAX(PricingDt) FROM rptPricingTEST b WHERE b.PricingDt @date1 AND b.PricingDt = @date2 AND a.commodity = b.commodity ); Query OK, 3010 rows affected (4 min 17.37 sec) Is there any way I can modify the sql statement to speed this up? Here's the result of explain (splitted for easy reading): id select_typetable type possible_keys key -- -- - - - 1 PRIMARYa index NULL idxcommodity 2 DEPENDENT SUBQUERY b ref idxcommodity idxcommodity id key_len ref rows Extra -- --- - -- 1 25 NULL 384155 Using where; Using index 2 22 dbsdvitmp.a.commodity 52 Using where; Using index And here's the rest of the sql before the above insert: SELECT @date1:=DATE_SUB( CURDATE(), INTERVAL 1 MONTH); SELECT @date2:=CURDATE(); CREATE TEMPORARY TABLE priceLast5 ( commodity char(22) NOT NULL,
RE: need help with a complicated join
Wow! What a trick! Harold, I am ashamed for not remembering that log/exp technique myself. Just out of curiosity, what is the difference between these two: COALESCE(*expression*, 1) IFNULL(*expression*, 1) Right off the bat, I know that COALESCE accepts multiple arguments, while IFNULL always takes two. If I only have two arguments, is there any advantage in using one or the other from any optimization points of view? --- Fyodor Golos Stockworm, Inc. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 26, 2004 8:42 AM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED]; news Subject: Re: need help with a complicated join Harold, you win the EUREKA prize of the month! I had forgotten all about that silly algebraic trick. This answers another person's post from last week. (I will try to find it again) also looking for a PRODUCT() function. I agree about the unknown performance. If you only need to return adjusted quotes for particular symbols and a date range, you *may* be better off pre-calcuating the LOG()s in a temp table for what you need to quote. You can combine that with the stock symbols and the date ranges (from your main query) to minimize the number of items that the LEFT JOIN ends up processing. something like : CREATE TEMPORARY TABLE tmpSplits SELECT Symbol, date, log(split_from/split_to) as logadj FROM splits WHERE date *earliest date* AND Symbol in (*list of symbols*) SELECT q.symbol, q.date, q.quote, q.quote * COALESCE(exp(sum(ts.logadj)), 1) FROM quotes q LEFT JOIN tmpSplits ts ON ts.symbol = q.symbol AND ts.date q.date WHERE q.symbol in (*list of symbols*) and q.date *earliest date* GROUP BY q.symbol, q.date, q.quote ORDER BY q.symbol, q.date only benchmarking will truly show which is faster. Once again - A HUGE way-to-go for the math lesson! Shawn Green Database Administrator Unimin Corporation - Spruce Pine Harald Fuchs [EMAIL PROTECTED]To: [EMAIL PROTECTED] .netcc: Sent by: newsFax to: [EMAIL PROTECTED]Subject: Re: need help with a complicated join rg 05/26/2004 06:00 AM Please respond to hf517 In article [EMAIL PROTECTED], Harald Fuchs [EMAIL PROTECTED] writes: SELECT q.symbol, q.date, q.quote * product (s.split_from / s.split_to) AS adjusted_quote FROM quotes q LEFT JOIN splits s ON s.symbol = q.symbol AND s.date q.date GROUP BY q.symbol, q.date, q.quote ORDER BY q.symbol, q.date The problem is how to define the 'product' aggregate (along the lines of 'sum'). [ I like talking to myself :-) ] A workaround for the missing product aggregate would be SELECT q.symbol, q.date, q.quote, q.quote * exp(sum(log(coalesce(s.split_from/s.split_to,1 FROM quotes q LEFT JOIN splits s ON s.symbol = q.symbol AND s.date q.date GROUP BY q.symbol, q.date, q.quote ORDER BY q.symbol, q.date but don't ask me how that performs... -- 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: DATETIME question
At 11:01 +0300 on 05/26/2004, Egor Egorov wrote about Re: DATETIME question: John Mistler [EMAIL PROTECTED] wrote: Given a column DATETIMEcolumn (-MM-DD HH:MM:SS), is there a SELECT statement that will: select all entries whose (TIME) of DATETIMEcolumn is BETWEEN 'HH:MM:SS' AND 'HH:MM:SS', but whose (DATE) is anything? SELECT .. FROM t1 WHERE DATE_FORMAT(datetime_column, '%T') BETWEEN 'HH:MM:SS' AND 'HH:MM:SS'; or from version 4.1.1 you can use TIME() function for this purpose. Description of DATE_FROMAT() and TIME function you can see at: http://dev.mysql.com/doc/mysql/en/Date_and_time_functions.html Note: This is the correct solution for your current setup. One minor caveat on going this route - Since you are looking at something past the start of the field, you must read every row for the check and can not use an index over that column. Depending on the number of rows and how often you do the select (and how hard it would be to alter the code that inserts new rows), you might want to look into adding a new column that has only the time in it and make it an Index. Then you can use the index column in your select. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: UTF-8 settings and woes
Try changing Eclipse's setting to saving the files as UTF-8 and also force the
JVM to the UTF-8 file encoding.
-Original Message-
From: robert kuzelj [mailto:[EMAIL PROTECTED]
Sent: 26 May 2004 15:01
To: [EMAIL PROTECTED]
Subject: UTF-8 settings and woes
hi,
i am trying to write utf-8 data via java into sql but
it wont work as expected.
first my setup
- suse 9.0
- kde 3.2
mysql SHOW VARIABLES LIKE 'char%';
+--++
| 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 | /usr/share/mysql/charsets/ |
| character_set_results| utf8 |
| version | 4.1.1-alpha-standard |
| version_comment | Official MySQL RPM |
| version_compile_machine | i686 |
| version_compile_os | pc-linux |
| wait_timeout | 28800 |
+--++
now i have the following script
[code]
[EMAIL PROTECTED]: more example_insert.sql
insert into CTPE_V01_00.CUSTOMER (ID, FAMILY_NAME, GIVEN_NAME)
values ('01', 'Käßsel', 'Böb');
insert into CTPE_V01_00.CUSTOMER (ID, FAMILY_NAME, GIVEN_NAME)
values ('02', 'Ægÿl', 'Àlbért');
insert into CTPE_V01_00.CUSTOMER (ID, FAMILY_NAME, GIVEN_NAME)
values ('03', '???', '???');
[/code]
which i execute and afterwards display is completely ok
[code]
mysql example_insert.sql
mysql use CTPE_V01_00;
mysql select FAMILY_NAME, GIVEN_NAME from CUSTOMER;
+++
| FAMILY_NAME| GIVEN_NAME |
+++
| Käßsel | Böb |
| Ægÿl | Àlbért |
| ??? | ??? |
+++
3 rows in set (0,00 sec)
mysql
[/code]
now i want to do that with java code instead. i tried
the following connectors
- mysql-connector-java-3.0.12-production-bin.jar
- mysql-connector-java-3.1.1-alpha-bin.jar
here's my code
[code]
public void testMySql()
{
String url = jdbc:mysql://localhost/ +
?autoReconnect=true +
useUnicode=true +
characterEncoding=utf8;
String cls = com.mysql.jdbc.Driver;
String user = ...;
String pwd = ...;
execute(url, cls, user, pwd);
}
public void execute(String _con, String _class,
String _user, String _pwd){
Connection con;
Statement stmt;
try{
Class.forName(_class);}
catch (java.lang.ClassNotFoundException e){
System.err.print(ClassNotFoundException: );
System.err.println(e.getMessage());}
try{
String sql1 = insert into CTPE_V01_00.CUSTOMER +
(ID, SHORT_ID, FAMILY_NAME, GIVEN_NAME) +
values ('01', '01','Käßsel', 'Böb');;
String sql2 = insert into CTPE_V01_00.CUSTOMER +
(ID, SHORT_ID, FAMILY_NAME, GIVEN_NAME) +
values ('02', '02','Ægÿl', 'Àlbért');;
String sql3 = insert into CTPE_V01_00.CUSTOMER +
(ID, SHORT_ID, FAMILY_NAME, GIVEN_NAME) +
values ('03', '03','???', +
'???');;
con = DriverManager.getConnection(_con, _user, _pwd);
stmt = con.createStatement();
stmt.executeUpdate(sql1);
stmt.executeUpdate(sql2);
stmt.executeUpdate(sql3);
stmt.close();
con.close();}
catch (SQLException ex){
ex.printStackTrace();}}
[/code]
now this results in the following output on the console
[code]
mysql select FAMILY_NAME, GIVEN_NAME from CUSTOMER;
+--+--+
| FAMILY_NAME | GIVEN_NAME
|
+--+--+
| KäÃsel | Böb
|
| Ãgÿl | Ãlbért
|
| инÑеÑнаÑион | инÑеÑнаÑион |
+--+--+
3 rows in set (0,00 sec)
[/code]
sigh not exactly what i expected.
what is also interesting is if i use DBFace (a eclipse plugin) in
combination with the drivers i mentioned the result is better - it is
not correct but it is better (not so much garbage).
any help is greatly appreciated.
tia
ciao robertj
Re: UTF-8 settings and woes
hi Yiannis, Try changing Eclipse's setting to saving the files as UTF-8 and also force the JVM to the UTF-8 file encoding. eclipse is already set to only write UTF-8. but how should i force the jvm to work with that encoding? ciao robertj smime.p7s Description: S/MIME Cryptographic Signature
Re: need help with a complicated join
At 14:07 -0500 on 05/25/2004, [EMAIL PROTECTED] wrote about need help with a complicated join: I am trying to come up with a query that takes two tables, one with non-split-adjusted historical stock prices, and one with information on splits, for instance: CREATE TABLE quotes ( symbol VARCHAR(127)NOT NULL, dateDATENOT NULL, quote FLOAT NOT NULL, PRIMARY KEY (symbol, date), INDEX (date), ); INSERT quotes VALUES (A, 2004-01-01, 3); INSERT quotes VALUES (A, 2004-01-02, 3); INSERT quotes VALUES (A, 2004-01-03, 3); INSERT quotes VALUES (A, 2004-01-04, 3); INSERT quotes VALUES (A, 2004-01-05, 2); INSERT quotes VALUES (A, 2004-01-06, 2); INSERT quotes VALUES (A, 2004-01-07, 2); INSERT quotes VALUES (A, 2004-01-08, 1); INSERT quotes VALUES (A, 2004-01-09, 1); CREATE TABLE splits ( symbol VARCHAR(127)NOT NULL, dateDATENOT NULL, split_from INT UNSIGNEDNOT NULL, split_toINT UNSIGNEDNOT NULL, PRIMARY KEY (symbol, date), ); INSERT splits VALUES (A, 2004-01-05, 2, 3); INSERT splits VALUES (A, 2004-01-08, 1, 2); I need to be able to pull out split-adjusted quotes, like this: SELECT symbol, date, ...some magic... FROM quotes WHERE symbol = A ORDER BY date; +++---++ | symbol | date | quote | adjusted_quote | +++---++ | A | 2004-01-01 | 3 | 1 | | A | 2004-01-02 | 3 | 1 | | A | 2004-01-03 | 3 | 1 | | A | 2004-01-04 | 3 | 1 | | A | 2004-01-05 | 2 | 1 | | A | 2004-01-06 | 2 | 1 | | A | 2004-01-07 | 2 | 1 | | A | 2004-01-08 | 1 | 1 | | A | 2004-01-09 | 1 | 1 | +++---++ Split-adjusting means that on a split date all previous prices are multiplied by split_from/split_to ratio. In my example two splits took place, one on 2004-01-05, which multiplied all previous prices by 2/3 and another one on 2004-01-08, which multiplied all previous prices (including those already affected by first split) by 1/2. Any help would be appreciated. Question: Are you doing this direct in MySQL or is it being done as a Web Inquiry that is doing the MySQL Select Under the Covers and then displaying the result? If the latter, then you can do it by first building a Temp Table of all records where symbol=A (fill in the requested symbol from the user query) AND date=as-of-date (again supplied by user) creating an adjusted field equal to the quote. The temp table now has only the requested table rows and ends at the as-of-date. You then read the splits table for all records dated before or on the as-of-date and do the updates to the adjusted field for each adjusted row (you can have the loop update the factor as needed so you only need to run the table once). Then just read and display the temp table. One additional question. From your definition when you say non-split-adjusted historical stock prices I assume that you mean that on the day a split occurs, that day's quote HAS BEEN adjusted for the split and all subsequent quotes are based on the status of the latest split. What you are trying to do with the adjusted column is adjust for the splits and have the quotes reflect the quote in terms of a block of stock quoted on day one in the table. IOW: If the first quote in the table was for 1 share and due to splits that block is now 5 shares, you want to multiple the todays (1-share) quote by 5 to get a constant based price and do the same on the other day by using the then current block size as the adjustment factor. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: UTF-8 settings and woes (update)
seems like my problem is not necesseraly tied to mysql.
at least i can reproduce all of this also with postgres.
instead of writing to the database i tried to read from
it (after inserting data via the following simple script on the
commandline)
[code]
[EMAIL PROTECTED]: more example_insert.sql
insert into CTPE_V01_00.CUSTOMER (ID, FAMILY_NAME, GIVEN_NAME)
values ('01', 'Käßsel', 'Böb');
insert into CTPE_V01_00.CUSTOMER (ID, FAMILY_NAME, GIVEN_NAME)
values ('02', 'Ægÿl', 'Àlbért');
insert into CTPE_V01_00.CUSTOMER (ID, FAMILY_NAME, GIVEN_NAME)
values ('03', '???', '???');
[EMAIL PROTECTED]: mysql example_insert.sql
[/code]
here it goes the code for reading:
[code]
01 public void list(String _con, String _class,
02 String _user, String _pwd)
03 {
04Connection con;
05Statement stmt;
06try{
07 Class.forName(_class);}
08catch (java.lang.ClassNotFoundException ex){}
09
10try{
11 String sql = select FAMILY_NAME, GIVEN_NAME from +
12 CTPE_V01_00.CUSTOMER;
13
14 con = DriverManager.getConnection(_con, _user, _pwd);
15 stmt = con.createStatement();
16 ResultSet rs = stmt.executeQuery(sql);
17 while (rs.next()){
18String family = rs.getString(FAMILY_NAME);
19String given = rs.getString(GIVEN_NAME);
20System.out.println(family + - + given);}
21 stmt.close();
22 con.close();}
23catch (SQLException ex){ex.printStackTrace();}}
[/code]
if executing this the result is again garbage.
[result]
[java] .K?sel - B?
[java] ?l - ?b?t
[java] ??? - ???
[/result]
now if i replace line 18 + 19 with the following everything is ok
[code]
18String family = new String(rs.getBytes(FAMILY_NAME));
19String given = new String(rs.getBytes(GIVEN_NAME));
[/code]
[result]
[java] .Käßsel - Böb
[java] Ægÿl - Àlbért
[java] ??? - ???
[/result]
seems like the driver(s) is not able to determin how to transform
strings into UTF-8.
any comments?
ciao robertj
smime.p7s
Description: S/MIME Cryptographic Signature
Re: UTF-8 settings and woes (update)
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 robert kuzelj wrote: [code] 18String family = new String(rs.getBytes(FAMILY_NAME)); 19String given = new String(rs.getBytes(GIVEN_NAME)); [/code] [result] [java] .Käßsel - Böb [java] Ægÿl - Àlbért [java] ??? - ??? [/result] seems like the driver(s) is not able to determin how to transform strings into UTF-8. Robert, Connector/J should be able to handle UTF-8 just fine as long as you've configured MySQL appropriately (and it appears from your previous posts that you have). (The character set itself is actually tested in the unit test suite). However, once you start displaying things on the console, all bets are off...because your console needs to understand UTF-8 as well, so using the 'eyeball' method of testing won't work too well here. Have you compared the strings you insert into the database with the strings you retrieve _inside_ the JVM (i.e. by using the .equals()) method...If the two strings are indeed equal inside Java, then you would at least know that the issue doesn't start there. As an aside, what does 'SHOW CREATE TABLE' for the tables in question say? -Mark - -- 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 iD8DBQFAtMv0tvXNTca6JD8RAmcLAKCxPpsz1e6ClolF5c+ML+SiIIp1oACcD+14 KopZcwVCfQwyZ0CldtBZ/oI= =NszQ -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Copying Tables with Foreign Keys
Can you run into problems when copying tables with foreign keys? I want to copy my Nations and States tables, making versions designed only to hold lengthy articles. I copied and coverted the States table successfully, but I'm running into problems with the Nations table. I get an error message referring to foreign key constraints. When I try it again, phpMyAdmin tells me the table has already been created. When I refresh the page, there it is! But there's no data in it - even though I directed it to copy the structure AND data. Is there a way to copy just a certain number of fields? All I really need (I think) are the columns with the nation's names (so I know which row is which), the column with abbreviations (for joining) and a third (and new) column for pasting articles. 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: a parameter in a sql script
Plinio Conti [EMAIL PROTECTED] wrote: I need to prepare some sql scripts which take parameters... how can I do? For example, suppose I want to have a sql script to create a new user granting to him some privileges and inserting its name in some application specific tables Seems stored procedure is that you are looking for: http://dev.mysql.com/doc/mysql/en/Stored_Procedures.html You can also put SQL commands to the text file. Then every time change username and other values and execute commands: shell mysql -uuser_name -p databasename file.sql -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ERROR 2013
Hi, I'm using MySql 4.0.18 on Linux (SuSE 8.2) and I'm getting this error ERROR 2013: Lost connection to MySQL server during query a few seconds after launching the following command LOAD DATA LOCAL INFILE '/root/hc.txt' into table af_afiliados ; soon after that I repeat the command mysql load data local infile /root/hc.txt into table af_afiliados; and the error seems to be slightly different ERROR 2006: MySQL server has gone away No connection. Trying to reconnect... Connection id:6 Current database: hrrg ERROR 2013: Lost connection to MySQL server during query Could ERROR 2013 be generated by some inconsistency in the data stored in /root/hc.txt file ? (this is a ASCII file,Which has been working perfectly till now, nonetheless there had been some changes into its data) Any hint would be greatly appreciated.. Thanks Mauricio -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Error 1054
Hi, I have a table Spot in the database as follows desc Spot - ; +---+-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | +---+-+--+-+-+---+ | Name | varchar(32) | YES | | NULL| | | ID| int(11) | | PRI | 0 | | | Row | varchar(32) | YES | | NULL| | | Column| varchar(32) | YES | | NULL| | | Probe_ID | int(11) | | | 0 | | | Array_Type_ID | int(11) | | | 0 | | +---+-+--+-+-+---+ When I try to access the field ID shown above though, it gives me error: mysql select ID from Spot; ERROR 1054: Unknown column 'ID' in 'field list' This is kind of weird. I will really appreciate any ideas Thank you, Kaustubh __ 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: ERROR 2013
This is just a shot in the dark, but I recall getting similar behavior (error...lost connection...reconnect) when I was trying to LOAD DATA that happened to violate foreign key constraints in my InnoDB tables. Hope that helps. --- Fyodor Golos Stockworm, Inc. -Original Message- From: Mauricio Pellegrini [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 26, 2004 5:03 PM To: [EMAIL PROTECTED] Subject: ERROR 2013 Hi, I'm using MySql 4.0.18 on Linux (SuSE 8.2) and I'm getting this error ERROR 2013: Lost connection to MySQL server during query a few seconds after launching the following command LOAD DATA LOCAL INFILE '/root/hc.txt' into table af_afiliados ; soon after that I repeat the command mysql load data local infile /root/hc.txt into table af_afiliados; and the error seems to be slightly different ERROR 2006: MySQL server has gone away No connection. Trying to reconnect... Connection id:6 Current database: hrrg ERROR 2013: Lost connection to MySQL server during query Could ERROR 2013 be generated by some inconsistency in the data stored in /root/hc.txt file ? (this is a ASCII file,Which has been working perfectly till now, nonetheless there had been some changes into its data) Any hint would be greatly appreciated.. Thanks 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: Error 1054
Try using backticks `ID` -Original Message- From: kaustubh shinde To: [EMAIL PROTECTED] Sent: 5/26/04 1:05 PM Subject: Error 1054 Hi, I have a table Spot in the database as follows desc Spot - ; +---+-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | +---+-+--+-+-+---+ | Name | varchar(32) | YES | | NULL| | | ID| int(11) | | PRI | 0 | | | Row | varchar(32) | YES | | NULL| | | Column| varchar(32) | YES | | NULL| | | Probe_ID | int(11) | | | 0 | | | Array_Type_ID | int(11) | | | 0 | | +---+-+--+-+-+---+ When I try to access the field ID shown above though, it gives me error: mysql select ID from Spot; ERROR 1054: Unknown column 'ID' in 'field list' This is kind of weird. I will really appreciate any ideas Thank you, Kaustubh __ 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: Error 1054
backticks didnt work :( --- Victor Pendleton [EMAIL PROTECTED] wrote: Try using backticks `ID` -Original Message- From: kaustubh shinde To: [EMAIL PROTECTED] Sent: 5/26/04 1:05 PM Subject: Error 1054 Hi, I have a table Spot in the database as follows desc Spot - ; +---+-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | +---+-+--+-+-+---+ | Name | varchar(32) | YES | | NULL | | | ID| int(11) | | PRI | 0 | | | Row | varchar(32) | YES | | NULL | | | Column| varchar(32) | YES | | NULL | | | Probe_ID | int(11) | | | 0 | | | Array_Type_ID | int(11) | | | 0 | | +---+-+--+-+-+---+ When I try to access the field ID shown above though, it gives me error: mysql select ID from Spot; ERROR 1054: Unknown column 'ID' in 'field list' This is kind of weird. I will really appreciate any ideas Thank you, Kaustubh __ 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] __ 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 and Turkish
How can I add Turkish support to the latest version of MySQL? I tried many times, but I can't do this. Mazhar Bilen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Error 1054
Maybe a long shot, but creating this table should have required using backticks around `Column` because it's a reserved word. Made me wonder if the ID field in the create statement was in backticks as well and maybe has an extra character. What does show create table Spot show? Hi, I have a table Spot in the database as follows desc Spot - ; +---+-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | +---+-+--+-+-+---+ | Name | varchar(32) | YES | | NULL| | | ID| int(11) | | PRI | 0 | | | Row | varchar(32) | YES | | NULL| | | Column| varchar(32) | YES | | NULL| | | Probe_ID | int(11) | | | 0 | | | Array_Type_ID | int(11) | | | 0 | | +---+-+--+-+-+---+ When I try to access the field ID shown above though, it gives me error: mysql select ID from Spot; ERROR 1054: Unknown column 'ID' in 'field list' This is kind of weird. I will really appreciate any ideas Thank you, Kaustubh __ Do you Yahoo!? Friends. Fun. Try the all-new Yahoo! Messenger. http://messenger.yahoo.com/ -- http://mike.kruckenberg.com | [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL and Turkish
Mazhar Bilen [EMAIL PROTECTED] wrote: How can I add Turkish support to the latest version of MySQL? I tried many times, but I can't do this. latin5 is used for turkish language. Start MySQL server with --default-character-set=latin5 option or put in the my.cnf: [mysqld] default-character-set=latin5 -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Backing up InnoDB MySQL DB
Hi all we're using MySQL 4.0.12 with InnoDB... please do you have any scripts, comments, suggestions, ideas, tutorials... about how to backup from that DB? It's possible to? Thanx for your time. Diga-me e eu esquecerei Mostre-me e lembrarei Ensina-me e aprenderei - Yahoo! Mail - Participe da pesquisa global sobre o Yahoo! Mail. Clique aqui!
Re: Backing up InnoDB MySQL DB
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Wednesday 26 May 2004 02:34 pm, ColdFusion Lists wrote: we're using MySQL 4.0.12 with InnoDB... please do you have any scripts, comments, suggestions, ideas, tutorials... about how to backup from that DB? It's possible to? If you can afford to shut it down, just stop the db, and copy (and zip) the db files.. If you can;t, you have to buy the mysql innodb backup tool from mysql.com.. (I think its from there.. ) The hotbackup tool it pretty automatted from what I hear. Jeff - -- Mercifully free of the ravages of intelligence. -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.4 (GNU/Linux) iD8DBQFAtPLuld4MRA3gEwYRAviTAKDbVUPLWpKPRVavRL62o1LBnFIGiQCfYxJm TFerdkznCkX3vo4Qqetk+Oc= =l0ew -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Backing up InnoDB MySQL DB
ColdFusion Lists [EMAIL PROTECTED] wrote: we're using MySQL 4.0.12 with InnoDB... please do you have any scripts, comments, suggestions, ideas, tutorials... about how to backup from that DB? It's possible to? You can use mysqldump utility that comes with MySQL distribution: http://dev.mysql.com/doc/mysql/en/mysqldump.html There is also non-free InnoDB Hot Backup program: http://www.innodb.com/order.php Some info about backing up InnoDB database find at: http://dev.mysql.com/doc/mysql/en/Backing_up.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Changing Password on Latest MySQL, etc
Dear All, Hello Installed the MySQL-server-4.0.20-0.i386.rpm on a RHL8 system Initialized the grant tables and then did: /usr/bin/mysqladmin -u rt password ACTUALPASSWORD And got: -bash: /usr/bin/mysqladmin: No such file or directory What gives mates? I had used the exact command before in another RHL8 system but it was a tar.gz package, I think. How can I changed or apply a password to mysql? Also, are there any other post-installation things to do after a Mysql installation? Newbie here. I'd like to know how to make Mysql secure more efficient. Thanks in advance for suggestions, time, information or links that can help us all. Carlos - Do you Yahoo!? Friends. Fun. Try the all-new Yahoo! Messenger
Re: Error 1054
Hi, Following is the output of show create table Spot command | Table | Create Table | +---+--- --+ | Spot | CREATE TABLE `Spot` ( `Name` varchar(32) default NULL, `ID ` int(11) NOT NULL default '0', `Row` varchar(32) default NULL, `Column` varchar(32) default NULL, `Probe_ID` int(11) NOT NULL default '0', `Array_Type_ID` int(11) NOT NULL default '0', PRIMARY KEY (`ID `) ) TYPE=MyISAM | As you can see ID was actually stored as `ID `. My mistake. Thanks a lot for all the replies. learnt something new today! :) Thanks again Kaustubh --- Michael Kruckenberg [EMAIL PROTECTED] wrote: Maybe a long shot, but creating this table should have required using backticks around `Column` because it's a reserved word. Made me wonder if the ID field in the create statement was in backticks as well and maybe has an extra character. What does show create table Spot show? Hi, I have a table Spot in the database as follows desc Spot - ; +---+-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | +---+-+--+-+-+---+ | Name | varchar(32) | YES | | NULL | | | ID| int(11) | | PRI | 0 | | | Row | varchar(32) | YES | | NULL | | | Column| varchar(32) | YES | | NULL | | | Probe_ID | int(11) | | | 0 | | | Array_Type_ID | int(11) | | | 0 | | +---+-+--+-+-+---+ When I try to access the field ID shown above though, it gives me error: mysql select ID from Spot; ERROR 1054: Unknown column 'ID' in 'field list' This is kind of weird. I will really appreciate any ideas Thank you, Kaustubh __ Do you Yahoo!? Friends. Fun. Try the all-new Yahoo! Messenger. http://messenger.yahoo.com/ -- http://mike.kruckenberg.com | [EMAIL PROTECTED] __ 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: Error 1054
Unless outlook is just formatting your message strangley it looks like the actual name of the ID field is `ID ` with two space char's included. As such you probably want to reccreate the table using `ID` in the create statement so that it will get created as you expect without these extra chars. John A. McCaskey -Original Message- From: kaustubh shinde [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 26, 2004 1:11 PM To: Michael Kruckenberg Cc: [EMAIL PROTECTED] Subject: Re: Error 1054 Hi, Following is the output of show create table Spot command | Table | Create Table | +---+--- +---+ --+ | Spot | CREATE TABLE `Spot` ( `Name` varchar(32) default NULL, `ID ` int(11) NOT NULL default '0', `Row` varchar(32) default NULL, `Column` varchar(32) default NULL, `Probe_ID` int(11) NOT NULL default '0', `Array_Type_ID` int(11) NOT NULL default '0', PRIMARY KEY (`ID `) ) TYPE=MyISAM | As you can see ID was actually stored as `ID `. My mistake. Thanks a lot for all the replies. learnt something new today! :) Thanks again Kaustubh --- Michael Kruckenberg [EMAIL PROTECTED] wrote: Maybe a long shot, but creating this table should have required using backticks around `Column` because it's a reserved word. Made me wonder if the ID field in the create statement was in backticks as well and maybe has an extra character. What does show create table Spot show? Hi, I have a table Spot in the database as follows desc Spot - ; +---+-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | +---+-+--+-+-+---+ | Name | varchar(32) | YES | | NULL | | | ID| int(11) | | PRI | 0 | | | Row | varchar(32) | YES | | NULL | | | Column| varchar(32) | YES | | NULL | | | Probe_ID | int(11) | | | 0 | | | Array_Type_ID | int(11) | | | 0 | | +---+-+--+-+-+---+ When I try to access the field ID shown above though, it gives me error: mysql select ID from Spot; ERROR 1054: Unknown column 'ID' in 'field list' This is kind of weird. I will really appreciate any ideas Thank you, Kaustubh __ Do you Yahoo!? Friends. Fun. Try the all-new Yahoo! Messenger. http://messenger.yahoo.com/ -- http://mike.kruckenberg.com | [EMAIL PROTECTED] __ 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: UTF-8 settings and woes (update)
hi mark, However, once you start displaying things on the console, all bets are off...because your console needs to understand UTF-8 as well, so using the 'eyeball' method of testing won't work too well here. in principle i agree with you. but only having java tests is simply not enough. i have to take into consideration that multiple clients may access the db. be it via the console or gui clients or what ever. so i have to make sure that every client is in sync. i have done now quite some extensive testing with various clients: - JFaceDBC (an eclipse plugin) i used it for writing the data into the db and reading from it. - the (mysql) console (read only) - mysql-command-center 0.94 (read only) - a java programm that reads from the database via a ResultSet. the reading is done once via getString and once via getBytes. i have defined two connection that i use for writing (exclusively thru JFaceDBC) and reading. NONE = jdbc:mysql://localhost/?autoReconnect=true UTF8 = as_above + useUnicode=truecharacterEncoding=UTF-8 A) write by NONE 1) read by NONE (JFACE) K??sel;B?; g?l;?lb?rt; ???;???; 2) read by UTF8 (JFACE) Käßsel;Böb; Ægÿl;Àlbért; ???;???; 3) read by UTF8 (mysql-CONSOLE) | Käßsel| Böb| | Ægÿl | Àlbért| | ??? | ??? | 4) read by UTF8 (mysql-cc 0.9.4) the same result as 3) 5) read by NONE (java getBytes) [java] K?sel - B? [java] ?l - ?b?t [java] ??? - ??? 6) read by NONE (java getString) [java] K??sel - B? [java] ?g?l - ?lb?rt [java] ??? - ??? 7) read by UTF (java getBytes) [java] Käßsel - Böb [java] Ægÿl - Àlbért [java] ??? - ??? 8) read by UTF (java getString) [java] K?sel - B? [java] ?l - ?b?t [java] ??? - ??? B) write by UTF8 1) read by NONE (JFACE) K??sel;B?; ? g?l;?lb?rt; ???;???; 2) read by UTF8 (JFACE) Käßsel;Böb; Ægÿl;Àlbért; ???;???; 3) read by UTF8 (mysql-CONSOLE) | Käßsel | Böb | | Ægÿl | Àlbért | | ??? | ??? | 4) read by UTF8 (mysql-cc 0.9.4) the same result as 3) 5) read by NONE (java getBytes) [java] K??sel - B? [java] ?g?l - ?lb?rt [java] ??? - ??? 6) read by NONE (java getString) [java] K??sel - B? [java] ?g?l - ?lb?rt [java] ??? - ??? 7) read by UTF (java getBytes) [java] Käßsel - Böb [java] Ægÿl - Àlbért [java] ??? - ??? 8) read by UTF (java getString) [java] K?sel - B? [java] ?l - ?b?t [java] ??? - ??? and here are the results so far 1) it is possible to get every into sync. JFace, mysql-console, mysqlcc 0.9.4 and java displayed the same stuff 2) it will only work if i write AND read thru a UTF-8 enabled connection. which is really no surprise (not even for me ;-) ). 3) i can get java only than into sync if i read from a ResultSet via s = new String(rs.getByte(fieldname)); i can not use the more natural s = rs.getString(fieldname); now thing begin to get realy weird if i start to write thru my java-app. - ResultSet.getBytes now returns garbage - ResultSet.getString shows the correct result - JFace shows almost the correct result (a lot of question marks thou) - mysql-console shows only garbage - mysqlcc 0.9.4 shows only garbage i am really puzzled. why cant i write via java to the db without f***ing up other clients and why cant i read via ResultSet.getString when other clients show the correct results but only thru getBytes? for any help or hints i'd be incredibly happy. ciao robertj ps: i hope the utf-8 chars dont get garbled. pps: sorry for the long mail. smime.p7s Description: S/MIME Cryptographic Signature
Re: UTF-8 settings and woes (update)
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 robert kuzelj wrote: hi mark, However, once you start displaying things on the console, all bets are off...because your console needs to understand UTF-8 as well, so using the 'eyeball' method of testing won't work too well here. in principle i agree with you. but only having java tests is simply not enough. i have to take into consideration that multiple clients may access the db. be it via the console or gui clients or what ever. so i have to make sure that every client is in sync. i have done now quite some extensive testing with various clients: - JFaceDBC (an eclipse plugin) i used it for writing the data into the db and reading from it. - the (mysql) console (read only) - mysql-command-center 0.94 (read only) - a java programm that reads from the database via a ResultSet. the reading is done once via getString and once via getBytes. i have defined two connection that i use for writing (exclusively thru JFaceDBC) and reading. NONE = jdbc:mysql://localhost/?autoReconnect=true UTF8 = as_above + useUnicode=truecharacterEncoding=UTF-8 A) write by NONE 1) read by NONE (JFACE) K??sel;B?; g?l;?lb?rt; ???;???; 2) read by UTF8 (JFACE) Ksel;Bb; gl;lbrt; ???;???; 3) read by UTF8 (mysql-CONSOLE) | Ksel| Bb| | gl | lbrt| | ??? | ??? | 4) read by UTF8 (mysql-cc 0.9.4) the same result as 3) 5) read by NONE (java getBytes) [java] K?sel - B? [java] ?l - ?b?t [java] ??? - ??? 6) read by NONE (java getString) [java] K??sel - B? [java] ?g?l - ?lb?rt [java] ??? - ??? 7) read by UTF (java getBytes) [java] Ksel - Bb [java] gl - lbrt [java] ??? - ??? 8) read by UTF (java getString) [java] K?sel - B? [java] ?l - ?b?t [java] ??? - ??? B) write by UTF8 1) read by NONE (JFACE) K??sel;B?; ? g?l;?lb?rt; ???;???; 2) read by UTF8 (JFACE) Ksel;Bb; gl;lbrt; ???;???; 3) read by UTF8 (mysql-CONSOLE) | Ksel | Bb | | gl | lbrt | | ??? | ??? | 4) read by UTF8 (mysql-cc 0.9.4) the same result as 3) 5) read by NONE (java getBytes) [java] K??sel - B? [java] ?g?l - ?lb?rt [java] ??? - ??? 6) read by NONE (java getString) [java] K??sel - B? [java] ?g?l - ?lb?rt [java] ??? - ??? 7) read by UTF (java getBytes) [java] Ksel - Bb [java] gl - lbrt [java] ??? - ??? 8) read by UTF (java getString) [java] K?sel - B? [java] ?l - ?b?t [java] ??? - ??? and here are the results so far 1) it is possible to get every into sync. JFace, mysql-console, mysqlcc 0.9.4 and java displayed the same stuff 2) it will only work if i write AND read thru a UTF-8 enabled connection. which is really no surprise (not even for me ;-) ). 3) i can get java only than into sync if i read from a ResultSet via s = new String(rs.getByte(fieldname)); i can not use the more natural s = rs.getString(fieldname); now thing begin to get realy weird if i start to write thru my java-app. - ResultSet.getBytes now returns garbage - ResultSet.getString shows the correct result - JFace shows almost the correct result (a lot of question marks thou) - mysql-console shows only garbage - mysqlcc 0.9.4 shows only garbage i am really puzzled. why cant i write via java to the db without f***ing up other clients and why cant i read via ResultSet.getString when other clients show the correct results but only thru getBytes? for any help or hints i'd be incredibly happy. robertj, Hopefully the following JUnit testcase helps show that your problem doesn't exist at the JDBC level. It creates a UTF-8 connection to MySQL-4.1.2, stores UTF-8 encoded strings directly and by prepared statements, retrieves them, compares to original as strings, and byte-for-byte using the UTF-8 encoding built in to the JVM. As a further demonstration, here's the results of a select from data stored by JDBC in a terminal that obviously doesn't understand UTF-8 (the DOS command window), which is my guess is your problem with mysql-console and mysqlcc, because mysql_console doesn't know anything about character sets, it relies on your terminal to display them correctly, and mysqlcc doesn't know about utf-8, it only knows about that character sets from MySQL-4.0.x and older: -
SHOW PROCESSLIST State Locked - what does this mean?
Hi, Currently, I have a situation where an app makes connections (via JDBC) to a mysql server, 50 connections at once, and everything just becomes super-slow. For instance, a SELECT that should take 0.01 sec takes several minutes. SHOW PROCESSLIST says that these threads that are connections from the app are in a state Locked. The mysql manual doesn't explain this option, or even list it as a possibility (maybe I'm looking at the wrong place: http://dev.mysql.com/doc/mysql/en/SHOW_PROCESSLIST.html) The other question is what one can do about this, to prevent this locking, or even diagnose it. Thanks for your help. Jim -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Used EXPLAIN, have indexes - query still surprisingly slow
I have the following query that takes anywhere from 1 to 3 seconds to run. I would expect it to run in less than 1/2 a second (and I really need it to do so). I've added the appropriate indices and tried EXPLAIN and ANALYZE; but, I'm not getting anywhere. SELECT message.id FROM message, message_thread WHERE message.id = message_thread.message_id ANDmessage_thread.thread_id = SOME_CONSTANT_NUMBER where SOME_CONSTANT_NUMBER is an integer literal, like 16400 (and no, I'm not putting quotes around the number). Here are the two tables involved: message table -- Field TypeNULLKey Default Extra -- id int(11)PRI NULL auto_increment msgtype_id int(11)MUL NULL content blobYES precedence varchar(255)YES message_thread table -- Field TypeNULLKey Default Extra -- id int(11)PRI NULL auto_increment message_id int(11)MUL -1 thread_id int(11)MUL -1 contact varchar(255)YES The message table has 1,117,213 records and the message_thread table has 2,563,893 records. At most, each query will return 200 records. Here is the output of the explain function (turned sideways): --- table : message_thread message --- type : ref eq_ref --- possible_keys : thread_id, PRIMARY message_id --- key : thread_id PRIMARY --- key_len : 4 4 --- ref : const message_thread.message_index --- rows : 200 1 --- Extra : Using where Using index --- This is running on a 2.2 GHz Pentium 4, NTFS File system, MS Windows 2000, MySQL 4.0.18. Each table is an InnoDB type. I've tried a combined thread_id/message_id index in the message_thread table; but, that did not seem to help at all. Any help would be appreciated. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Join locks the table?
If there is an FAQ where this is addressed, please point me to it so I can RTFM. I need to find out if table joins lock the table or, alternatively, how to find out what query is locking the table. We're doing a miles-from-zipcode query and I'm looking to speed it up. Since we only have a few of these queries and they are done repeatedly, I thought it'd be good to pre-compute distances from the specified zip. Something like this: mysql select * from zipDistances where origin=70118 limit 10; +-+--++ | zip_distant | distance | origin | +-+--++ | 70183 |5 | 70118 | | 70004 |5 | 70118 | | 70001 |5 | 70118 | | 70009 |5 | 70118 | | 70010 |5 | 70118 | | 70002 |5 | 70118 | | 70121 |5 | 70118 | | 70011 |5 | 70118 | | 70096 |5 | 70118 | | 70005 |5 | 70118 | +-+--++ We have a cron job that executes, takes an applicant and, for certain clients, checks to see if the applicant is within a specified distance. Previously, we would pre-compute the list of zips within the specified distance and create a query like this: SELECT id FROM applicant WHERE zip in (70183,70004,70001,...) All the possible zips would be listed out. I thought it would be better to change it: SELECT DISTINCT applicant.id FROM applicant,zipDistances WHERE zip=zip_distant AND origin = 70118 But, it looks like this join is locking the applicant table. Does this sound plausible? If it isn't plausible and I need to look elsewhere, how can I find out what query is locking the table? -- A choice between one man and a shovel, or a dozen men with teaspoons is clear to me, and I'm sure it is clear to you also. -- Zimran Ahmed http://www.winterspeak.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Problems with MySQL 4.0.20
We start mysql with 'service mysql start' (we install from the RPM for
linux).
I've never seen mysql create binlog files under the name root before, and
after reverting to an old version, it doesn't again. It created a big mess
with all the slaves stuck at the end of an older binlog and not advancing to
the next one and complaining about corruption. Unfortunately, I don't have
the contents of the log (I think the size of the file was 79 bytes) since a
script here checks that all the slaves are at a certain point and then
deletes the logs on the master.
Log:
040519 17:53:41 mysqld started
/usr/sbin/mysqld: ready for connections.
Version: '4.0.18-standard-log' socket: '/tmp/mysql.sock' port: 3306
040520 16:58:54 /usr/sbin/mysqld: Normal shutdown
040520 16:58:56 /usr/sbin/mysqld: Shutdown Complete
040520 16:58:56 mysqld ended
040520 16:59:10 mysqld started
040520 16:59:10 Warning: Asked for 196608 thread stack, but got 126976
/usr/sbin/mysqld: ready for connections.
Version: '4.0.20-standard-log' socket: '/tmp/mysql.sock' port: 3306
040520 16:59:14 Failed to open log (file '/binlogs/binlog.032', errno 13)
040520 16:59:34 Aborted connection 134 to db: 'db' user: 'aaa' host:
`something.i' (Got an error writing communication packets)
040520 16:59:36 Aborted connection 544 to db: 'db' user: 'aaa' host:
`something.i' (Got an error writing communication packets)
040520 16:59:36 Aborted connection 541 to db: 'db' user: 'aaa' host:
`something.i' (Got an error writing communication packets)
Binary logs are created by the mysqld daemon (after mysqld possibly
changes to uid of 'mysql' if --user=mysql was used). So in any case,
if mysqld is running as user mysql (no matter if it was 'mysql' which
started mysqld or if it was 'root' which did 'mysqld --user=mysql'),
the binary logs are created by 'mysql'.
If you have some binary logs created by 'root', it means 'mysqld' was
run as 'root'; this is what you should really check (if you can
provide us with the way you started mysqld ('service mysql start',
whatever) and a listing of 'ps -elf | grep mysqld', we may be able to
check if it is a MySQL bug but this is quite unlikely, from the above
reasoning).
Thank you!
--
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Mr. Guilhem Bichot [EMAIL PROTECTED]
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Software Developer
/_/ /_/\_, /___/\___\_\___/ Bordeaux, France
___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Problems with MySQL 4.0.20
Hmm, I don't see any changes in ft-related files since 4.0.18 that could cause it (there were bugfixes, but they affect only *searching* - that is MATCH - and not *updating*). Can you create a test case ? Well, I put up a file in the secret folder a few days ago as referenced in a bug report: http://bugs.mysql.com/?id=3870 There is a select statement that crashes the server found in the log file. I put the files up and posted the bug from a remote computer and couldn't write much about it at the time. The table is fine according to 'check table the_table_name'. The select crashes it. The select also crashes it in older versions of myslq!! Doing a repair in the old version and then doing the select in the old version is OK. That is why I came to the conclusion that the file is corrupt. CHECK TABLE does not find the corruption, however. Another note on this: The tables I had the most problems with had FTS indicies. I can't say that it is more than coincidental just yet. I am not conclusive that it is a cause and effect relationship at this time. Even returning to the older versions of mysql is not getting rid of all our problems (we are seeing extremely high loads on the same stream of queries as usual). Selectively repairing tables has helped. It may be that it is not FTS related and we should repair all tables. We are going to try that tonight. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysql_install_db problem
The easy way around this is to change your hostname to 'localhost,' install the scripts, and then change your hostname back. There are other ways to fixing this, but that's the fastest, I've found. J.R. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: UTF-8 settings and woes (update)
hallo mark, Hopefully the following JUnit testcase helps show that your problem doesn't exist at the JDBC level. It creates a UTF-8 connection to MySQL-4.1.2, stores UTF-8 encoded strings directly and by prepared statements, retrieves them, compares to original as strings, and byte-for-byte using the UTF-8 encoding built in to the JVM. i really appreciate your efforts to help me. many thanks. nevertheless, i had such a junit-test allready and the only thing that it proves (imo) is that you can write something into the db and retrieve it with the same encoding again. this tells nothing about the transformation of strings from a java app to the jdbc driver and back or which encoding is used during that transform. sorry for me being so resistant. but i tried now one additional client (SqlExplorer for intellij idea) and the result is again the same. if i execute my insert script via the shell [EMAIL PROTECTED]: mysql example_insert.sql or if i execute it with JFace (which then uses the connector/j jdbc driver) and if i check then in every single client...every single one displays the data as expected. if i write the data via my java-app all clients show garbage. maybe you understand that i am having a very hard time accepting that four client apps (SqlExplorer, JFace, mysqlcc, mysql-console) allways display the data correct if not written by my sample app and wrong if written by my app. of course it is possible that the other client-apps are misconfigured. but than again they must be wrongly configured in the exact same way as the results are very much the same. you must admit this is quite inprobable. As a further demonstration, here's the results of a select from data stored by JDBC in a terminal that obviously doesn't understand UTF-8 (the DOS command window), which is my guess is your problem with mysql-console and mysqlcc, because mysql_console doesn't know anything about character sets, it relies on your terminal to display them correctly, and mysqlcc doesn't know about utf-8, it only knows about that character sets from MySQL-4.0.x and older: i am very sure that my bash is correctly configured. most of my samples i copied some text from the unicode site into some file that i saved as utf-8. i can easily 'more' that file and everything is displayed correctly (including cyrillic and thai characters). the only (partial) sollution seems to be to use ResultSet.getBytes when reading from the db. maybe that is also what JFaceDBC and SqlExplorer do. nevertheless it does not solve the problem on how to write to the db without shutting of all other clients. ciao robertj smime.p7s Description: S/MIME Cryptographic Signature
Re: UTF-8 settings and woes (update)
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 robert kuzelj wrote: hallo mark, Hopefully the following JUnit testcase helps show that your problem doesn't exist at the JDBC level. It creates a UTF-8 connection to MySQL-4.1.2, stores UTF-8 encoded strings directly and by prepared statements, retrieves them, compares to original as strings, and byte-for-byte using the UTF-8 encoding built in to the JVM. i really appreciate your efforts to help me. many thanks. nevertheless, i had such a junit-test allready and the only thing that it proves (imo) is that you can write something into the db and retrieve it with the same encoding again. this tells nothing about the transformation of strings from a java app to the jdbc driver and back or which encoding is used during that transform. RobertJ, If you specify UTF-8 as the characterEncoding connection property, then that is the transform that is used from client - server. The transform that is used from server - client is whatever character set the column in the table is set to when you created the table (or conversely if you use cast/convert in SQL to change it to some other character set). sorry for me being so resistant. but i tried now one additional client (SqlExplorer for intellij idea) and the result is again the same. if i execute my insert script via the shell [EMAIL PROTECTED]: mysql example_insert.sql or if i execute it with JFace (which then uses the connector/j jdbc driver) and if i check then in every single client...every single one displays the data as expected. if i write the data via my java-app all clients show garbage. maybe you understand that i am having a very hard time accepting that four client apps (SqlExplorer, JFace, mysqlcc, mysql-console) allways display the data correct if not written by my sample app and wrong if written by my app. of course it is possible that the other client-apps are misconfigured. but than again they must be wrongly configured in the exact same way as the results are very much the same. you must admit this is quite inprobable. If you look at my previous e-mail, I _do_ show the command line client retrieving your data just fine with UTF-8 data that was written into the database by the _JDBC_ driver: And here's one from a unix x-term, which _does_ know about UTF-8: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 153 to server version: 4.1.2-alpha-max-log - -- Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql select * from testUtf8; +++ | field1 | field2 | +++ | Käßsel | Böb| | Käßsel | Böb| +++ 2 rows in set (0.02 sec) The only thing I can think of is there is something that is pertinent to your problem that you are not showing with your code snippets that is causing this issue. Would it be possible for you to post a _full_ testcase with your create table statements and such? You might also try upgrading to a bk build of MySQL-4.1, only because there have been charset changes in MySQL-4.1, and you're not using the latest-and-greatest. -Mark - -- 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 iD8DBQFAtSGytvXNTca6JD8RAjxoAKCPGQJ0hJa1VmZJZEwsbXOjDxq58wCfWHKk 1Bag2o23pJabTxKML92U4Mw= =tk6+ -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Mysqld stalls
I've been trying to fine tune my mysqld settings in my.cnf on a very busy server. It seems to be doing fine, as in the server loading. BUT every so often I see the number of processes spike and then it sesms mysqld is unresponsive through httpd and I end up having to restart mysqld to get it going again. What settings should I be tweaking ? Mark Susol -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Copying Tables with Foreign Keys
are you using: INSERT INTO new tablecolumns SELECT oldcolumns FROM oldtable; I am still a relative beginner and this was the way I was taught. I have personally never had a problem (but then I was using Oracle in school). Respectfully, Ligaya Turmelle David Blomstrom [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Can you run into problems when copying tables with foreign keys? I want to copy my Nations and States tables, making versions designed only to hold lengthy articles. I copied and coverted the States table successfully, but I'm running into problems with the Nations table. I get an error message referring to foreign key constraints. When I try it again, phpMyAdmin tells me the table has already been created. When I refresh the page, there it is! But there's no data in it - even though I directed it to copy the structure AND data. Is there a way to copy just a certain number of fields? All I really need (I think) are the columns with the nation's names (so I know which row is which), the column with abbreviations (for joining) and a third (and new) column for pasting articles. 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: Mysqld stalls
Mark Susol|Ultimate Creative Media wrote: I've been trying to fine tune my mysqld settings in my.cnf on a very busy server. It seems to be doing fine, as in the server loading. BUT every so often I see the number of processes spike and then it sesms mysqld is unresponsive through httpd and I end up having to restart mysqld to get it going again. Regarding the I see the number of processes spike phrase what processes spike and how do you observe this happening? (Oh, and Linux, UNIX, Windows..?). If you mean the load average seen in w, uptime or top spikes, that is one thing. If you mean the number of MySQL threads grows suddenly, that could be another. Tells us about your machine (cpu, hd, memory, OS). Then, did you enable the slow query log? How about binary logs? Can you isolate the SQL statements in effect at the time of the process spike? Depending on what you mean I've seen servers broght to their knees by single inefficient queries that don't use indexes for joins or select criteria (where clauses) or use open ended like queries on varchar fields for joins, etc. Indexing join fields (keys) has dramatic impact on server performance, positively (indexing too much causes other problems, but I see that much less than not enough or improper indexing). What settings should I be tweaking ? None, yet. Let's nail down the description of the problem and then see if we need to investigate some query optimization. Mark Susol HTH, Robert J Taylor [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Stats ?
Hello, Is there any way to have per user stats? In the bin log we have nothing but exec_time=0 (seems this is seconds) - is there a way for more precise statistics what different users do regarding CPU usage by MySQL per user? Best regards, Doytchin Spiridonovmailto:[EMAIL PROTECTED] http://www.WebYou.com Web Design and Development -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Defaul of NOW()
I have a field in mysql 4, using InnoDB Field is timestamp 14 and defualt is set to 00, which I want to be the result of NOW() so that every record made will get NOW() as the value, I can not get it to work... ALTER TABLE `addresses` CHANGE `added` `added` TIMESTAMP( 14 ) DEFAULT 'NOW()'; Query OK, 2 rows affected (0.01 sec) Records: 2 Duplicates: 0 Warnings: 0 It tells me it worked, but then it reverts back to the zero's. -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Fax: 313.557.5052 [EMAIL PROTECTED]Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Determine table type and comments
I can not find this in the docs. I can see in phpmyadmin what a table comment is and what type of table it is, such as MyISAM etc. From the mysql shell, how do I get to this info? -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Fax: 313.557.5052 [EMAIL PROTECTED]Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Determine table type and comments
Scott Haneda wrote: I can not find this in the docs. I can see in phpmyadmin what a table comment is and what type of table it is, such as MyISAM etc. From the mysql shell, how do I get to this info? Try mysql SHOW CREATE TABLE tablename; (Not sure if that covers comments since I've, yes I admit it, never used comments on tables directly.) HTH, Robert J Taylor [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Determine table type and comments
On Wed, May 26, 2004 at 06:22:45PM -0700, Scott Haneda wrote: I can not find this in the docs. I can see in phpmyadmin what a table comment is and what type of table it is, such as MyISAM etc. From the mysql shell, how do I get to this info? You can use SHOW TABLE STATUS: http://dev.mysql.com/doc/mysql/en/SHOW_TABLE_STATUS.html Jim Winstead MySQL AB -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Defaul of NOW()
On Wed, May 26, 2004 at 06:20:22PM -0700, Scott Haneda wrote: I have a field in mysql 4, using InnoDB Field is timestamp 14 and defualt is set to 00, which I want to be the result of NOW() so that every record made will get NOW() as the value, I can not get it to work... ALTER TABLE `addresses` CHANGE `added` `added` TIMESTAMP( 14 ) DEFAULT 'NOW()'; Query OK, 2 rows affected (0.01 sec) Records: 2 Duplicates: 0 Warnings: 0 It tells me it worked, but then it reverts back to the zero's. If you do a SHOW CREATE TABLE on the table, you'll see that it has actually ignored your DEFAULT. (What is has done is actually transformed it to '00', since that is what the string 'NOW()' becomes when you convert it to a TIMESTAMP.) Read this section in the manual for information on how the default value for TIMESTAMP columns is handled: http://dev.mysql.com/doc/mysql/en/TIMESTAMP_pre-4.1.html Support for specifying how TIMESTAMP columns get updated is coming in 4.1.2. Right now, it is only documented in the change notes: http://dev.mysql.com/doc/mysql/en/News-4.1.2.html Jim Winstead MySQL AB -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Used EXPLAIN, have indexes - query still surprisingly slow
Andy Henshaw wrote: I have the following query that takes anywhere from 1 to 3 seconds to run. I would expect it to run in less than 1/2 a second (and I really need it to do so). I've added the appropriate indices and tried EXPLAIN and ANALYZE; but, I'm not getting anywhere. SELECT message.id FROM message, message_thread WHERE message.id = message_thread.message_id ANDmessage_thread.thread_id = SOME_CONSTANT_NUMBER where SOME_CONSTANT_NUMBER is an integer literal, like 16400 (and no, I'm not putting quotes around the number). Here are the two tables involved: message table -- Field TypeNULLKey Default Extra -- id int(11)PRI NULL auto_increment msgtype_id int(11)MUL NULL content blobYES precedence varchar(255)YES Not sure if this is the cause, but from personal experience (lots of rows and big blobs) consider the following: Every time you search the message table the BLOB is being loaded into memory even if it is not being returned in the query. Instead, break apart this table into 2: message(id, msgtype_id,precedence) message_blob(id, content) Then select the row you want from message_blob from a list of rows from message. message_thread table -- Field TypeNULLKey Default Extra -- id int(11)PRI NULL auto_increment message_id int(11)MUL -1 thread_id int(11)MUL -1 contact varchar(255)YES The message table has 1,117,213 records and the message_thread table has 2,563,893 records. At most, each query will return 200 records. Here is the output of the explain function (turned sideways): --- table : message_thread message --- type : ref eq_ref --- possible_keys : thread_id, PRIMARY message_id --- key : thread_id PRIMARY --- key_len : 4 4 --- ref : const message_thread.message_index --- rows : 200 1 --- Extra : Using where Using index --- This is running on a 2.2 GHz Pentium 4, NTFS File system, MS Windows 2000, MySQL 4.0.18. Each table is an InnoDB type. I've tried a combined thread_id/message_id index in the message_thread table; but, that did not seem to help at all. Any help would be appreciated. HTH, Robert J Taylor [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Foreign Key Constraints
Hi, I am trying to use the foreign key constraints from InnoDB and creating indexes is a requirement for foreign key. The problem is that by creating index for my foreign key, it does not allow my foreign key to have null or blank values which my records will have. For eg. a BorrowerID is a foreign key on a Book table, but when the book is not borrowed, the BorrowerID will be null and I can't seem to import the data containing null values for the foreign key. Is there a way to solve this? Thanks. Msg sent via Spymac Mail - http://www.spymac.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Foreign Key Constraints
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Wednesday 26 May 2004 11:22 pm, [EMAIL PROTECTED] wrote: Hi, I am trying to use the foreign key constraints from InnoDB and creating indexes is a requirement for foreign key. The problem is that by creating index for my foreign key, it does not allow my foreign key to have null or blank values which my records will have. For eg. a BorrowerID is a foreign key on a Book table, but when the book is not borrowed, the BorrowerID will be null and I can't seem to import the data containing null values for the foreign key. Is there a way to solve this? Thanks. I am not to sure how its possible to fix it.. Thinking about this.. I would have a bookid which is never null. Considering no matter if the book is out or not, you have that book. I then would have a borrow table, lets say, as the foreign key would be bookid.. I date borrowed, and date returned would be how I would know if its out or not.. Just thinking off the top of my head as there probably is a better way to do it. I am sure there are other ways to do this.. Foreign keys can't be null. Jeff - -- Calm down--It's only ones and zeroes. -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.4 (GNU/Linux) iD8DBQFAtW9lld4MRA3gEwYRAkaAAKCtA0+GlFLRifkWDdH661LIF7gaVwCdHM8V zxbQk96yn0v3dTTBD9F4gWM= =JLfz -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Feature Request: UPDATE 'error codes' or mysql_affected_rows() to be more accurate
I'm developing a program where I try an UPDATE ... LIMIT 1 and if mysql_affected_rows == 0, then I know nothing was updated and so I do an INSERT. I find this is much cleaner and the majority of the time, I'm going to do UPDATES, so I didn't want to waste a SELECT (even though I hear they're cheap). I'm doing these queries several times per second. however... Of course UPDATE doesn't 'ERROR if the record doesn't exist, it just didn't do anything (therefore that's why I use the mysql_num_rows() to check). The problem is that if I am actually doing an UPDATE to a record where nothing actually changed in the existing record, I still get mysql_affected_rows() equal to 0. *grrr*. It would be extremely useful to somehow get a result of maybe -1 if I tried to update a record that didn't exist, versus a result of -2 if I tried to update a record that did exist, but mySQL didn't change anything. I don't know exactly what I'm asking for other than a way to know the difference... At the very least, it seems to me that if I update a record that exists already (even if no data changed), I should still get mysql_affected_rows() 0 (since in theory I matched something, even if mySQL behind the scenes didn't change the data). Out of curiosity, if I have a TIMESTAMP column, would that solve my problem, since mySQL should be forced to update that TIMESTAMP right?? [btw, I know I could try this idea, but I'm home and my code is at work right now and I just had the idea! ;-] http://daevid.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
