Please help me optimize a MySQL server.
Hi, I'm running a MySQL server on a duel P III 1G, with 2 GB RAM. MySQL 4.0.18 compiled from source. We have 2 webservers running apache, And this is the backend database server. The server is really slow. a select on a table with 138,247 rows takes about 1.6 - 2+ seconds, I have about 50% CPU idle. using MyISAM table types. sure I can provide any statistics about the running server. Do you think that switching to innodb'd help ? Is it a problem with Debian woody ?? Any suggestions are welcomed. Many thanks! -- -- Katoob Main Developer Linux registered user #224950, ICQ #58475622 -- Don't send me any attachment in Micro$oft (.DOC, .PPT) format please Read http://www.fsf.org/philosophy/no-word-attachments.html Preferable attachments: .PDF, .HTML, .TXT Thanx for adding this text to Your signature -- -BEGIN GEEK CODE BLOCK- Version: 3.1 GCM/IT d-(++)@ s+(++):-+++ a-- C+++$ UL+++$ P+++$+ L+++()$+ E+++ W++?$ N o? K-? !w++ !O !M !V !PS@ !PE@ Y+ PGP=+++ t? 5? !X R? tv-- b+@ DI D+ G-- e+ h--++ !r y? --END GEEK CODE BLOCK-- signature.asc Description: Digital signature
Re: Comments/questions on High Performance MySQL
Hi, Sasha Pachev wrote: Hans-Peter Grimm wrote: I just finished reading High Performance MySQL. Congratulations to the authors, it's a great book and I enjoyed reading it. Would be great if someone now started to write MySQL Internals ;-) I actually do have a fairly long chapter on that subject in MySQL Enterprise Solutions. Additionally, I wrote the optimizer discussion while periodically peeking at the code. But I agree, having a dedicated book on it would be nice. The biggest problem I see is that it is so much easier to sell a book that talks about MySQL from the DBA or application programmer's point of view than the one that would go into the details of how things work inside. So I imagine it would be difficult to get a publisher interested. But as far as I am concerned, I would be willing if the opportunity presented itself. If somebody could find a good way to sell it, I would write it. I see your point. When I think about it, it's actually not all the details I'd be interested in, but rather a practical guide that helps to understand MySQL's internals in order to use it to full advantage. Below find a sample TOC which might give you an idea of what I mean. I'm aware that you can find information on some of the items in the manual but I'm sure having a guide with some examples could speed up the process of learning. I'll also try to have a look at your book (which I wasn't aware of thus far). Hans-Peter TOC - MySQL source code layout organization, directory contents - Processes and threads mysqld, connections, replication threads, ... - Memory usage memory allocation functions, buffers, shared memory, ... - A journey through query processing lex/yacc, parse tree, optimization, disk access, sending the result - Implementing feature X Let's say I want to implement a CREATE CRONJOB (interval, cmd) command. What steps will have to be taken to achieve that? - Patches and Bitkeeper repositories How do I maintain a specific feature (not in the official MySQL tree) but stay aligned with recent MySQL versions? - How does MySQL achieve portability, how do you keep your code portable Thread creation, network functions, what functions to avoid, ... - Writing (portable) UDF functions Is there a difference between built-in functions and UDF functions? How do I implement REGEXP_REPLACE? ... - Best practices Things like: if you create your own thread, is there some registry in MySQL where you should register it? How do you avoid memory leaks? ... - 2 or 3 case studies for the above tasks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Comments/questions on High Performance MySQL
Sasha Pachev: I actually do have a fairly long chapter on that subject in MySQL (...) would be willing if the opportunity presented itself. If somebody could find a good way to sell it, I would write it. Maybe you can publish something on a website as MySQL Enterprise Solutions - The Lost Chapters ? :-) If there is no way to find a publisher who wants to print it, that wouldn't mean that the world could not see your work! Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL and NPTL
SQL I have the problem like this under FreeBSD 5.2 with libkse (kernel threads). Server with high loading doesn't want to shutdown. But server with low loading does. Steve, I have had this same problem exept the entire server does not hang, only one thread which experiences a signficiat slowdown. This happens a couple times a week for our 4 servers. I believe this is a thread scheduling issue. The mysql support team suggests against using NPTL. However, I use mysqld-max on redhat linux 9.0 and have found a 10-20% performance difference at HIGH (hundreds of connections) load levels. The support team claims the difference in performance between linux threads and NPTL is never significant. Incidentally I have experinced this same thread starvation issue on redhat as 3.0 amd64 while using Linux threads. Theory is that glic 2.3 is to blame ( redhat 9.0 - glibc-2.3.2-11.9 , redhat as 3.0 - glibc-2.3.2-95.6 ). So the version of glibc looks to be the culprit. As I understand it Mysql is working with Redhat to determine the issues with glibc 2.3. I haven't tried the export line. Trevor Steve Meyers wrote: Has anyone else experienced this bug? http://bugs.mysql.com/bug.php?id=868 We've been seeing this problem on several of our servers (see the last comment to the bug). MySQL just hangs occasionally, it happens about 3-4 times per month. We have 13 database servers, so that unfortunately increases our odds. I just wondered how many other people have seen it, and if the suggested export LD_ASSUME_KERNEL=2.2.5; mysqld_safe has worked for anyone. In order to turn that on, I will need to take our site down completely, which is (of course) not desirable. Thanks! Steve Meyers -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] Best regards. ___ Andrey Kotrekhov [EMAIL PROTECTED] ISP Alkar Teleport . +380 562 34-00-44 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: help uploading
sbraun [EMAIL PROTECTED] wrote: I have many tables to upload, but each time I try I get a syntax error 1064. What format should my table be in? I've been attempting to upload tab delimited text, but I can't get past an error in line 1. I can't see any errors. How did you try to upload data? Which statement did you use? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Please help me optimize a MySQL server.
Mohammed Sameer [EMAIL PROTECTED] wrote: I'm running a MySQL server on a duel P III 1G, with 2 GB RAM. MySQL 4.0.18 compiled from source. We have 2 webservers running apache, And this is the backend database server. The server is really slow. a select on a table with 138,247 rows takes about 1.6 - 2+ seconds, I have about 50% CPU idle. using MyISAM table types. sure I can provide any statistics about the running server. Do you think that switching to innodb'd help ? Is it a problem with Debian woody ?? Any suggestions are welcomed. Does MySQL use indexes? You can check it with EXPLAIN SELECT: http://dev.mysql.com/doc/mysql/en/EXPLAIN.html Some optimization tips you can find at: http://dev.mysql.com/doc/mysql/en/MySQL_Optimisation.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: Select on Multiple columns
Rocco Castino [EMAIL PROTECTED] writes: I would like, for example, to get the records starting from row number 6 (without, of course, working with the primary key, where the numbers could not necessarily be sorted as here): +++---+---+ | id_example | x_uno | x_due | x_desc | +++---+---+ | 6 | 2004-01-02 | 3 | a | | 7 | 2004-01-02 | 4 | | | 8 | 2004-01-03 | 2 | 0 | | 9 | 2004-01-03 | 5 | | +++---+---+ In fact, a select with the following syntax: SELECT * FROM `example` where x_uno=20040102 and x_due = 3 ORDER BY x_uno ASC, x_due ASC does not return me row number 8 (as it is 23 on column x_due). Another solution is a concatenation of the x_uno and x_due columns, something like: SELECT * FROM example where concat(x_uno,x_due) = '200401023' If x_due is an integer that can be more than 1 digit in length, then you may need to be more creative in your concatenation. You might even do something like SELECT * FROM example where (100*x_uno + x_due) = 2004010203 which will work only for values of x_due less than 100. - seb -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
[uniball@gmx.net: Re: Please help me optimize a MySQL server.]
sorry Egor Egorov, Mutt sent the mail to your private inbox ;) resending to the mailing list. On Wed, May 19, 2004 at 02:02:29PM +0300, Egor Egorov wrote: Mohammed Sameer [EMAIL PROTECTED] wrote: I'm running a MySQL server on a duel P III 1G, with 2 GB RAM. MySQL 4.0.18 compiled from source. We have 2 webservers running apache, And this is the backend database server. The server is really slow. a select on a table with 138,247 rows takes about 1.6 - 2+ seconds, I have about 50% CPU idle. using MyISAM table types. sure I can provide any statistics about the running server. Do you think that switching to innodb'd help ? Is it a problem with Debian woody ?? Any suggestions are welcomed. Thanks for replying... Does MySQL use indexes? You can check it with EXPLAIN SELECT: http://dev.mysql.com/doc/mysql/en/EXPLAIN.html It's phpBB and phpnuke still fighting with them to trash phpnuke default tables. The website is really active, with about 67000 visits/day. We are using a RAID controller for the server. Some optimization tips you can find at: http://dev.mysql.com/doc/mysql/en/MySQL_Optimisation.html I tried all this, but no use. CPU idle is usually 0% a 1.5 gig log file was generated in about 12h or so. move to innodb from MyISAM ? Is it a Debian woody problem ? load balancing ? I tried to do what I can, but nothing improved the performance. -- -- Katoob Main Developer Linux registered user #224950, ICQ #58475622 -- Don't send me any attachment in Micro$oft (.DOC, .PPT) format please Read http://www.fsf.org/philosophy/no-word-attachments.html Preferable attachments: .PDF, .HTML, .TXT Thanx for adding this text to Your signature -- -BEGIN GEEK CODE BLOCK- Version: 3.1 GCM/IT d-(++)@ s+(++):-+++ a-- C+++$ UL+++$ P+++$+ L+++()$+ E+++ W++?$ N o? K-? !w++ !O !M !V !PS@ !PE@ Y+ PGP=+++ t? 5? !X R? tv-- b+@ DI D+ G-- e+ h--++ !r y? --END GEEK CODE BLOCK-- signature.asc Description: Digital signature
MySQL Cluster - queries execute with 6.60sec delay when one DB node is dead
Hi, I have configured MySQL Cluster on two machines with 2 DB nodes (NoOfReplicas = 2) and 2 MySQL API nodes, one of each node type on both systems. The config is almost the same as the one of 2-node demo. The cluster is working fine as long as all DB nodes are operational, but if one of them is gone (i.e. I shut it down), all queries that are sent to the MySQL API nodes seem to hang for about 6.60sec before they are actually executed. As soon as the dead DB node becomes available again, everything starts to work as it supposed to. If one of DB nodes is dead: mysql SELECT * FROM t; ++---+ | id | name | ++---+ | 2 | test2 | | 1 | test1 | ++---+ 2 rows in set (6.60 sec) with all DB nodes working: mysql SELECT * FROM t; ++---+ | id | name | ++---+ | 2 | test2 | | 1 | test1 | ++---+ 2 rows in set (0.00 sec) It looks like MySQL is waiting for the dead node to respond, gets timed out after about 6 seconds and then requests the answer from the other node. I did not find anything in the Administrator Guide that would say about such behaviour, which makes the cluster rather useless in case of a node crash. Any ideas how to fix it? Regards, Maciek -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fully compliant GIS
Trevor Price [EMAIL PROTECTED] wrote: What is the roadmap for fully compliant GIS? The OpenGIS specification defines the following functions, which MySQL does not yet implement. They should appear in future releases. When implemented, they will provide full support for spatial analysis, not just MBR-based support. GIS isn't high priority. Some features are planned for 5.0. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: which table type does not have the maximum columns limitation?
I think if you are looking for unlimited columns, you may want to rethink your database structure. Databases are designed for unlimited rows, not columns. Whatever data you were looking to put into unlimited columns, should be put into rows in a related table. I usually do this by adding a qualifier column that describes what data is contained in that row. This structure allows unlimited data sets and allows you to add new data sets without having to change the structure of a table. Not knowing your data, I don't know if this is not suitable for any particular reason. On May 18, 2004, at 6:08 PM, Hongyu Sun wrote: Dear List: I wonder if there exist a type of table which can allow for unlimited or at least more than 1024 columns? I know MaxDB has 1024 max columns. Please give me a hint if you could. Thanks in advance! Hongyu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Please help me optimize a MySQL server.
Something that small shouldn't really need optimizing. What is the size of your data (mb?, gb?) and what does your query look like? If you are doing a wild card search on a large text field without a full text index, then those times may be the best you're going to get. Many times it's about optimizing your query statement or you indexes rather than MySQL settings. For instance, I have a test machine (Mac G4 400Mhz, 512MB, MySQL 4.0.18) that does a three table join with each table having just over 100,000 rows. My initial query took about 12 seconds, but changing my query statement around a little I got it to just under 1 second. Which is pretty good considering the hardware it's on. What does you explain for the query look like? It's probably doing a full table scan, which means you're bottleneck is the disk. On May 19, 2004, at 3:26 AM, Mohammed Sameer wrote: Hi, I'm running a MySQL server on a duel P III 1G, with 2 GB RAM. MySQL 4.0.18 compiled from source. We have 2 webservers running apache, And this is the backend database server. The server is really slow. a select on a table with 138,247 rows takes about 1.6 - 2+ seconds, I have about 50% CPU idle. using MyISAM table types. sure I can provide any statistics about the running server. Do you think that switching to innodb'd help ? Is it a problem with Debian woody ?? Any suggestions are welcomed. Many thanks! -- -- Katoob Main Developer Linux registered user #224950, ICQ #58475622 -- Don't send me any attachment in Micro$oft (.DOC, .PPT) format please Read http://www.fsf.org/philosophy/no-word-attachments.html Preferable attachments: .PDF, .HTML, .TXT Thanx for adding this text to Your signature -- -BEGIN GEEK CODE BLOCK- Version: 3.1 GCM/IT d-(++)@ s+(++):-+++ a-- C+++$ UL+++$ P+++$+ L+++()$+ E+++ W++?$ N o? K-? !w++ !O !M !V !PS@ !PE@ Y+ PGP=+++ t? 5? !X R? tv-- b+@ DI D+ G-- e+ h--++ !r y? --END GEEK CODE BLOCK-- -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: lookup tables, populating automatically
Taylor, You seem to be confusing your user interface with your database. You can use a variety of techniques to get data from a user via a web page. The exact methods available to you are dependent on your platform (the web server and its operating system) and your processing language( ASP, PHP, Perl, Python, CGI script, etc.). It has nothing to do with forming a proper SQL statement to get your data into or out of the database. To add records to the database use the INSERT statement. To change records use the UPDATE statement. And to get data from the database you use the SELECT statement. It's a left hand/right hand issue: the website/user interface is on one side, the database on the other with your code in the middle communicating between the two. I highly recommend you look for online tutorials for your particular web scripting language as they are sure to provide examples of retrieving data from your sites visitors. Respectfully, Shawn Green Database Administrator Unimin Corporation - Spruce Pine Taylor Lewick [EMAIL PROTECTED]To: [EMAIL PROTECTED] m cc: [EMAIL PROTECTED] Fax to: 05/18/2004 08:51 Subject: lookup tables, populating automatically PM I asked a question earlier about how to handle lookup tables, I think this is a little more clear as to what I was trying to ask. if I want to relate a contact to an organization, I know I can create a table that contains contact_ids and org_ids. But, how do I enter that information gracefully, i.e. from a webpage? Assuming someone has already entered a persons contact info, and I have info for several organizations how would I go about assigning a contact to that organization? It isn't feasible for someone to remember all of the ids for each business and each contact. I could create a webpage that is used to assign contacts. Would I want to query each table and present two pull down boxes one with contact names and one with organization names, and they would make thier selection and hit enter... In the background i.e. perl or php, once they hit submit I would then enter the org_id and contact_id for each selection into the relation table? IF this is correct, can someone share some code examples (perl preferably) on how this could be accomplished? Thanks, Taylor -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problem with UTF8 and upper case german umlaute
Hallo,
I am trying to migrate a IBM-DB2 database to MySQL (4.1.1-alpha,
Gentoo-Linux and Win32) and all is working fine, except the UTF8 tables.
So I have tried a simple example
create table test (
text varchar(100) character set utf8
) default charset=utf8;
insert into test values('ü Ã~\ ö Ã~V ä Ã~D Ã~_');
This should generate ü Ü ö Ö ä Ä ß. The upper case umlaute and s-sharp
are not created correctely.
The text of the database field is ü � ö � ä � �
Now, what' s that
Best Regards / Mit freundlichen Grüßen
R. Rohmfeld
EMUGE - Werk Richard Glimpel GmbH Co. KG
Fabrik für Präzisionswerkzeuge
D-91207 Lauf, Germany
Dr. Rainer Rohmfeld
Tel: +49 9123/186-562
e-mail: [EMAIL PROTECTED]
http://www.emuge.de
Re: which table type does not have the maximum columns limitation?
Hongyu Sun [EMAIL PROTECTED] wrote: I wonder if there exist a type of table which can allow for unlimited or at least more than 1024 columns? I know MaxDB has 1024 max columns. Each table type has limitation on the number of columns. According crash-me page it's 3398 columns per table: http://dev.mysql.com/tech-resources/crash-me.php?res_id=49 -- 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]
query string too long?
I'm using MySql version 4.1.1. When I issue a query like (SELECT * FROM PARTITIONED_1_1 AS PARTITIONED, PARTITIONED2_1 AS PARTITIONED2 WHERE PARTITIONED2.ID=PARTITIONED.ID1) UNION (SELECT * FROM PARTITIONED_2_1 AS PARTITIONED, PARTITIONED2_1 AS PARTITIONED2 WHE RE PARTITIONED2.ID=PARTITIONED.ID1) UNION (SELECT * FROM PARTITIONED_3_1 AS PARTITIONED, PARTITIONED2_1 AS PARTITIONED2 WHERE PARTITIONED2.ID=PARTITIONED.ID1) UNION (SELECT * FROM PARTITIONED_4_1 AS PARTITIONED, PARTITIONED2_1 AS PARTITIONE D2 WHERE PARTITIONED2.ID=PARTITIONED.ID1) UNION (SELECT * FROM PARTITIONED_1_2 AS PARTITIONED, PARTITIONED2_1 AS PARTITI ONED2 WHERE PARTITIONED2.ID=PARTITIONED.ID1) UNION (SELECT * FROM PARTITIONED_2_2 AS PARTITIONED, PARTITIONED2_1 AS PART ITIONED2 WHERE PARTITIONED2.ID=PARTITIONED.ID1) UNION (SELECT * FROM PARTITIONED_3_2 AS PARTITIONED, PARTITIONED2_1 AS P ARTITIONED2 WHERE PARTITIONED2.ID=PARTITIONED.ID1) UNION (SELECT * FROM PARTITIONED_4_2 AS PARTITIONED, PARTITIONED2_1 A S PARTITIONED2 WHERE PARTITIONED2.ID=PARTITIONED.ID1) UNION (SELECT * FROM PARTITIONED_1_1 AS PARTITIONED, PARTITIONED2_ 2 AS PARTITIONED2 WHERE PARTITIONED2.ID=PARTITIONED.ID1) UNION (SELECT * FROM PARTITIONED_2_1 AS PARTITIONED, PARTITIONE D2_2 AS PARTITIONED2 WHERE PARTITIONED2.ID=PARTITIONED.ID1) UNION (SELECT * FROM PARTITIONED_3_1 AS PARTITIONED, PARTITI ONED2_2 AS PARTITIONED2 WHERE PARTITIONED2.ID=PARTITIONED.ID1) UNION (SELECT * FROM PARTITIONED_4_1 AS PARTITIONED, PART ITIONED2_2 AS PARTITIONED2 WHERE PARTITIONED2.ID=PARTITIONED.ID1) UNION (SELECT * FROM PARTITIONED_1_2 AS PARTITIONED, P ARTITIONED2_2 AS PARTITIONED2 WHERE PARTITIONED2.ID=PARTITIONED.ID1) UNION (SELECT * FROM PARTITIONED_2_2 AS PARTITIONED , PARTITIONED2_2 AS PARTITIONED2 WHERE PARTITIONED2.ID=PARTITIONED.ID1) UNION (SELECT * FROM PARTITIONED_3_2 AS PARTITIO NED, PARTITIONED2_2 AS PARTITIONED2 WHERE PARTITIONED2.ID=PARTITIONED.ID1) UNION (SELECT * FROM PARTITIONED_4_2 AS PARTI TIONED, PARTITIONED2_2 AS PARTITIONED2 WHERE PARTITIONED2.ID=PARTITIONED.ID1) ORDER BY 1,2,3,4,5 (sorry for the format, but it is what I get from telnet) mysql crashes. I started mysql with --max_allowed_packet=100M, but nothing changes: it keeps crashing. Should I change a variable? Which? (if I'm asking in the wrong place, point me in the right direction) thank you -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Simple table, 1.7 million rows, very slow SELECTs
To honestly answer your question, I would say try both and use whichever method gives you the best performance in your application. I don't actually know if the ZEROFILL option forces those zeroes onto the disk during the write (so that the column is stored that way) or if it is a signal to the engine to LPAD() that column during retrieval. Storing the leading zeroes could take up more space (or not if the engine stores the numbers in fixed widths) but would definitely save time on post-read processing (adding the leading zeroes to the retrieved number for every row returned) Shawn Green Database Administrator Unimin Corporation - Spruce Pine 'Jacob Elder ' [EMAIL PROTECTED] To: '''[EMAIL PROTECTED] ' ' ' [EMAIL PROTECTED] cc: 05/18/2004 05:02 Fax to: PM Subject: Re: Simple table, 1.7 million rows, very slow SELECTs On Tue 18 May 02004 at 04:53:52PM -0400, [EMAIL PROTECTED] wrote: Jacob, Don't worry about storing the leading the zeroes. Just left pad the subscriber column to be 4 digits on output and you should be golden. MySQL has a function just to generate left-padded numbers: LPAD(subscriber,4,'0') Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine I just came across ZEROFILL in the manual. When would I want to use LPAD rather than ZEROFILL? -- Jacob Elder -- 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: Problem with UTF8 and upper case german umlaute
[EMAIL PROTECTED] wrote on 19/05/2004 14:28:02:
Hallo,
I am trying to migrate a IBM-DB2 database to MySQL (4.1.1-alpha,
Gentoo-Linux and Win32) and all is working fine, except the UTF8 tables.
So I have tried a simple example
create table test (
text varchar(100) character set utf8
) default charset=utf8;
insert into test values('ü Ã~\ ö Ã~V ä Ã~D Ã~_');
This should generate ü Ü ö Ö ä Ä ß. The upper case umlaute and s-sharp
are not created correctely.
The text of the database field is ü � ö � ä � �
Now, what' s that
At a guess, the problem wthe the upper case U-umlaut is that it contains
the backslash character, which is a string escape character to the
transport layer. I think you need to double it. Is the underscore also
special? Try preceding it by backslash.
Alec
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL on embedded systems (Part 2)
Hi, I sent an email yesterday on this topic, but I just wanted to more clearly and concisely re-phrase the question. Is it possible for MySQL to run using less than 15Mb of memory? If so, is it done using: a) compile options / hacked code b) configuration options c) both If I know the answer to this question I can probably figure out the rest. But I can't afford to spend the time trying to get it right and turn up nothing. Thanks! - Joe -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= [ Joseph Monti] [ [EMAIL PROTECTED] ] [ http://www.smartrobots.com/ ] =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Antwort: Re: Problem with UTF8 and upper case german umlaute
[EMAIL PROTECTED] 19.05.2004 15:46 An: [EMAIL PROTECTED] Kopie: [EMAIL PROTECTED] Thema: Re: Problem with UTF8 and upper case german umlaute At a guess, the problem wthe the upper case U-umlaut is that it contains the backslash character, which is a string escape character to the transport layer. I think you need to double it. Is the underscore also special? Try preceding it by backslash. Alec This is not the problem! I am using UTF8, hence the backslash and _ are not single characters, ~\ and ~_ are the characters. On IBM-DB2 this is not a problem! Rainer
Re: query string too long?
Leonardo Francalanci [EMAIL PROTECTED] wrote: I'm using MySql version 4.1.1. When I issue a query like (SELECT * FROM PARTITIONED_1_1 AS PARTITIONED, PARTITIONED2_1 AS PARTITIONED2 WHERE PARTITIONED2.ID=PARTITIONED.ID1) UNION (SELECT * FROM PARTITIONED_2_1 AS PARTITIONED, PARTITIONED2_1 AS PARTITIONED2 WHE RE PARTITIONED2.ID=PARTITIONED.ID1) UNION (SELECT * FROM PARTITIONED_3_1 AS PARTITIONED, PARTITIONED2_1 AS PARTITIONED2 WHERE PARTITIONED2.ID=PARTITIONED.ID1) UNION (SELECT * FROM PARTITIONED_4_1 AS PARTITIONED, PARTITIONED2_1 AS PARTITIONE D2 WHERE PARTITIONED2.ID=PARTITIONED.ID1) UNION (SELECT * FROM PARTITIONED_1_2 AS PARTITIONED, PARTITIONED2_1 AS PARTITI ONED2 WHERE PARTITIONED2.ID=PARTITIONED.ID1) UNION (SELECT * FROM PARTITIONED_2_2 AS PARTITIONED, PARTITIONED2_1 AS PART ITIONED2 WHERE PARTITIONED2.ID=PARTITIONED.ID1) UNION (SELECT * FROM PARTITIONED_3_2 AS PARTITIONED, PARTITIONED2_1 AS P ARTITIONED2 WHERE PARTITIONED2.ID=PARTITIONED.ID1) UNION (SELECT * FROM PARTITIONED_4_2 AS PARTITIONED, PARTITIONED2_1 A S PARTITIONED2 WHERE PARTITIONED2.ID=PARTITIONED.ID1) UNION (SELECT * FROM PARTITIONED_1_1 AS PARTITIONED, PARTITIONED2_ 2 AS PARTITIONED2 WHERE PARTITIONED2.ID=PARTITIONED.ID1) UNION (SELECT * FROM PARTITIONED_2_1 AS PARTITIONED, PARTITIONE D2_2 AS PARTITIONED2 WHERE PARTITIONED2.ID=PARTITIONED.ID1) UNION (SELECT * FROM PARTITIONED_3_1 AS PARTITIONED, PARTITI ONED2_2 AS PARTITIONED2 WHERE PARTITIONED2.ID=PARTITIONED.ID1) UNION (SELECT * FROM PARTITIONED_4_1 AS PARTITIONED, PART ITIONED2_2 AS PARTITIONED2 WHERE PARTITIONED2.ID=PARTITIONED.ID1) UNION (SELECT * FROM PARTITIONED_1_2 AS PARTITIONED, P ARTITIONED2_2 AS PARTITIONED2 WHERE PARTITIONED2.ID=PARTITIONED.ID1) UNION (SELECT * FROM PARTITIONED_2_2 AS PARTITIONED , PARTITIONED2_2 AS PARTITIONED2 WHERE PARTITIONED2.ID=PARTITIONED.ID1) UNION (SELECT * FROM PARTITIONED_3_2 AS PARTITIO NED, PARTITIONED2_2 AS PARTITIONED2 WHERE PARTITIONED2.ID=PARTITIONED.ID1) UNION (SELECT * FROM PARTITIONED_4_2 AS PARTI TIONED, PARTITIONED2_2 AS PARTITIONED2 WHERE PARTITIONED2.ID=PARTITIONED.ID1) ORDER BY 1,2,3,4,5 (sorry for the format, but it is what I get from telnet) mysql crashes. I started mysql with --max_allowed_packet=100M, but nothing changes: it keeps crashing. Should I change a variable? Which? Could you provide structure of tables and some data for testing? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Please help me optimize a MySQL server.
On Wed, May 19, 2004 at 08:47:28AM -0400, Brent Baisley wrote: Something that small shouldn't really need optimizing. What is the size of your data (mb?, gb?) and what does your query look like? If you are doing a wild card search on a large text field without a full text index, then those times may be the best you're going to get. Many times it's about optimizing your query statement or you indexes rather than MySQL settings. mainly we are using 2 databases: 675M for phpbb and 77M for phpnuke. That's not much. Ok, It seems that the actual queries performed by phpbb are optimized. For instance, I have a test machine (Mac G4 400Mhz, 512MB, MySQL 4.0.18) that does a three table join with each table having just over 100,000 rows. My initial query took about 12 seconds, but changing my query statement around a little I got it to just under 1 second. Which is pretty good considering the hardware it's on. What does you explain for the query look like? It's probably doing a full table scan, which means you're bottleneck is the disk. but the server is running on a duel P III with 2 GB RAM and a RAID controller. Do you think there is much to be done, or shall I look for better hardware/or clustering ? On May 19, 2004, at 3:26 AM, Mohammed Sameer wrote: Hi, I'm running a MySQL server on a duel P III 1G, with 2 GB RAM. MySQL 4.0.18 compiled from source. We have 2 webservers running apache, And this is the backend database server. The server is really slow. a select on a table with 138,247 rows takes about 1.6 - 2+ seconds, I have about 50% CPU idle. using MyISAM table types. sure I can provide any statistics about the running server. Do you think that switching to innodb'd help ? Is it a problem with Debian woody ?? Any suggestions are welcomed. Many thanks! -- -- Katoob Main Developer Linux registered user #224950, ICQ #58475622 -- Don't send me any attachment in Micro$oft (.DOC, .PPT) format please Read http://www.fsf.org/philosophy/no-word-attachments.html Preferable attachments: .PDF, .HTML, .TXT Thanx for adding this text to Your signature -- -BEGIN GEEK CODE BLOCK- Version: 3.1 GCM/IT d-(++)@ s+(++):-+++ a-- C+++$ UL+++$ P+++$+ L+++()$+ E+++ W++?$ N o? K-? !w++ !O !M !V !PS@ !PE@ Y+ PGP=+++ t? 5? !X R? tv-- b+@ DI D+ G-- e+ h--++ !r y? --END GEEK CODE BLOCK-- -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- -- Katoob Main Developer Linux registered user #224950, ICQ #58475622 -- Don't send me any attachment in Micro$oft (.DOC, .PPT) format please Read http://www.fsf.org/philosophy/no-word-attachments.html Preferable attachments: .PDF, .HTML, .TXT Thanx for adding this text to Your signature -- -BEGIN GEEK CODE BLOCK- Version: 3.1 GCM/IT d-(++)@ s+(++):-+++ a-- C+++$ UL+++$ P+++$+ L+++()$+ E+++ W++?$ N o? K-? !w++ !O !M !V !PS@ !PE@ Y+ PGP=+++ t? 5? !X R? tv-- b+@ DI D+ G-- e+ h--++ !r y? --END GEEK CODE BLOCK-- signature.asc Description: Digital signature
R: query string too long?
Could you provide structure of tables and some data for testing? How? I tried to post a couple of zip files (2 mails, 10k each) but it doesn't work. Some data (and schema) CREATE TABLE IF NOT EXISTS PARTITIONED2_1 ( ID int(3) unsigned NOT NULL DEFAULT '0' , DATA1 timestamp , PRIMARY KEY (ID) ); INSERT INTO PARTITIONED2_1 VALUES(1,2003-01-13 01:12:27); INSERT INTO PARTITIONED2_1 VALUES(2,2003-09-08 22:04:41); INSERT INTO PARTITIONED2_1 VALUES(3,2003-06-06 08:10:28); INSERT INTO PARTITIONED2_1 VALUES(4,2003-09-03 20:58:29); INSERT INTO PARTITIONED2_1 VALUES(5,2003-06-11 06:59:17); INSERT INTO PARTITIONED2_1 VALUES(6,2003-01-19 17:53:23); INSERT INTO PARTITIONED2_1 VALUES(7,2003-04-20 03:10:52); CREATE TABLE IF NOT EXISTS PARTITIONED2_2 ( ID int(3) unsigned NOT NULL DEFAULT '0' , DATA1 timestamp , PRIMARY KEY (ID) ); INSERT INTO PARTITIONED2_2 VALUES(501,2003-08-10 12:10:53); INSERT INTO PARTITIONED2_2 VALUES(502,2003-07-09 21:17:49); INSERT INTO PARTITIONED2_2 VALUES(503,2003-06-25 20:48:16); INSERT INTO PARTITIONED2_2 VALUES(504,2003-04-29 23:15:19); INSERT INTO PARTITIONED2_2 VALUES(505,2004-05-19 13:34:10); INSERT INTO PARTITIONED2_2 VALUES(506,2003-05-01 05:14:06); INSERT INTO PARTITIONED2_2 VALUES(507,2003-02-17 11:35:42); INSERT INTO PARTITIONED2_2 VALUES(508,2003-09-12 01:56:10); INSERT INTO PARTITIONED2_2 VALUES(509,2003-03-14 23:13:25); INSERT INTO PARTITIONED2_2 VALUES(510,2003-04-09 01:10:04); INSERT INTO PARTITIONED2_2 VALUES(511,2003-01-30 17:53:46); INSERT INTO PARTITIONED2_2 VALUES(512,2003-08-04 02:45:48); CREATE TABLE IF NOT EXISTS PARTITIONED_1_1 ( ID1 int(10) unsigned NOT NULL DEFAULT '0' , ID2 datetime NOT NULL DEFAULT '-00-00 00:00:00' , DATA1 varchar(10) , DATA2 double(5,4) , DATA3 datetime , PRIMARY KEY (ID1,ID2) ); INSERT INTO PARTITIONED_1_1 VALUES(1,2003-01-06 07:09:11,tremor s,3555.5240,2003-01-11 00:04:54); INSERT INTO PARTITIONED_1_1 VALUES(1,2003-01-08 05:48:18,mecha,5333.7749,2003-01-17 02:11:18); INSERT INTO PARTITIONED_1_1 VALUES(1,2003-01-10 06:04:51,,5744.7958,2003-01-12 05:46:39); INSERT INTO PARTITIONED_1_1 VALUES(1,2003-01-11 23:28:40,clon,3341.9492,2003-01-09 08:01:09); INSERT INTO PARTITIONED_1_1 VALUES(1,2003-01-26 06:36:33,stopping,6437.8025,2003-01-04 20:21:06); INSERT INTO PARTITIONED_1_1 VALUES(1,2003-02-07 05:56:35,dis,7767.9386,2003-01-15 03:10:04); etc etc (tables PARTITIONED_*_* have all the same schema but different data) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Please help me optimize a MySQL server.
I wouldn't upgrade until you know where the bottleneck is (CPU, disk, network, or RAM). Since you are using professional software, I wouldn't try to change the queries. Have you made changes to your my.cnf file? Since you have enough ram to hold all the data, ram is probably not your bottleneck. The question is whether you have your system configured to use it. I forget if phpbb and phpnuke use PEAR::DB to access the underlying database. If they do, there is a certain amount of overhead involved that you just can't get around without changing the database access code to use native calls. Are your database and web server on the same machine (a security no-no, but performance yes-yes)? If they are on separate machines, make sure you have a fast connection between them, at least 100MB and make sure it's full duplex. On May 19, 2004, at 10:34 AM, Mohammed Sameer wrote: On Wed, May 19, 2004 at 08:47:28AM -0400, Brent Baisley wrote: Something that small shouldn't really need optimizing. What is the size of your data (mb?, gb?) and what does your query look like? If you are doing a wild card search on a large text field without a full text index, then those times may be the best you're going to get. Many times it's about optimizing your query statement or you indexes rather than MySQL settings. mainly we are using 2 databases: 675M for phpbb and 77M for phpnuke. That's not much. Ok, It seems that the actual queries performed by phpbb are optimized. For instance, I have a test machine (Mac G4 400Mhz, 512MB, MySQL 4.0.18) that does a three table join with each table having just over 100,000 rows. My initial query took about 12 seconds, but changing my query statement around a little I got it to just under 1 second. Which is pretty good considering the hardware it's on. What does you explain for the query look like? It's probably doing a full table scan, which means you're bottleneck is the disk. but the server is running on a duel P III with 2 GB RAM and a RAID controller. Do you think there is much to be done, or shall I look for better hardware/or clustering ? On May 19, 2004, at 3:26 AM, Mohammed Sameer wrote: Hi, I'm running a MySQL server on a duel P III 1G, with 2 GB RAM. MySQL 4.0.18 compiled from source. We have 2 webservers running apache, And this is the backend database server. The server is really slow. a select on a table with 138,247 rows takes about 1.6 - 2+ seconds, I have about 50% CPU idle. using MyISAM table types. sure I can provide any statistics about the running server. Do you think that switching to innodb'd help ? Is it a problem with Debian woody ?? Any suggestions are welcomed. Many thanks! -- -- Katoob Main Developer Linux registered user #224950, ICQ #58475622 -- Don't send me any attachment in Micro$oft (.DOC, .PPT) format please Read http://www.fsf.org/philosophy/no-word-attachments.html Preferable attachments: .PDF, .HTML, .TXT Thanx for adding this text to Your signature -- -BEGIN GEEK CODE BLOCK- Version: 3.1 GCM/IT d-(++)@ s+(++):-+++ a-- C+++$ UL+++$ P+++$+ L+++()$+ E+++ W++?$ N o? K-? !w++ !O !M !V !PS@ !PE@ Y+ PGP=+++ t? 5? !X R? tv-- b+@ DI D+ G-- e+ h--++ !r y? --END GEEK CODE BLOCK-- -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- -- Katoob Main Developer Linux registered user #224950, ICQ #58475622 -- Don't send me any attachment in Micro$oft (.DOC, .PPT) format please Read http://www.fsf.org/philosophy/no-word-attachments.html Preferable attachments: .PDF, .HTML, .TXT Thanx for adding this text to Your signature -- -BEGIN GEEK CODE BLOCK- Version: 3.1 GCM/IT d-(++)@ s+(++):-+++ a-- C+++$ UL+++$ P+++$+ L+++()$+ E+++ W++?$ N o? K-? !w++ !O !M !V !PS@ !PE@ Y+ PGP=+++ t? 5? !X R? tv-- b+@ DI D+ G-- e+ h--++ !r y? --END GEEK CODE BLOCK-- -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Subquery and LIMIT
I have a subquery where I am retrieving Shipment information from the DB. I want to LIMIT the result to '1' but I first need the results in 'ID' order. When I use this subquery I don't get the FIRST row because the data is not in ID order: (SELECT TextValue FROM tblQuoteItems WHERE (Type = 6 AND (tblQuoteItems.QuoteID = tblQuotes.ID)) LIMIT 1) AS Shipment When I use this subquery I only get the 'Shipment' data for the first row of the whole query and NULL for the other rows.: (SELECT TextValue FROM tblQuoteItems WHERE (Type = 6 AND (tblQuoteItems.QuoteID = tblQuotes.ID)) ORDER BY tblQuoteItems.ID LIMIT 1) AS Shipment For this subquery I want to first put in ID order then LIMIT the results to 1. How do I do this? If you need the whole query here it is. I cleaned it up a little: SELECT products.lnglaborhrs AS LaborHRS, Products.Category, (SELECT TextValue FROM tblQuoteItems WHERE (Type = 6 AND (tblQuoteItems.QuoteID = tblQuotes.ID)) LIMIT 1) AS Shipment FROM (((tblQuotes INNER JOIN tblQuoteItems ON tblQuotes.ID = tblQuoteItems.QuoteID) LEFT JOIN Products ON tblQuoteItems.ModelNo = Products.ModelNo) INNER JOIN tblOppLog ON tblQuotes.ID = tblOppLog.QuoteID) WHERE ((tblQuoteItems.Type=0) AND tblQuotes.id IN (SELECT DISTINCT max(id) FROM tblQuotes GROUP BY concat(Date_Format(Date,'%y'), '-' , QuoteNumber))) ORDER BY Date_Format(tblquotes.date,%y), tblQuotes.QuoteNumber,if(tblQuotes.Revision= Initial, , tblQuotes.Revision);
Re: Running more than one level of MySQL
At 15:09 -0500 on 05/18/2004, Paul DuBois wrote about Re: Running more than one level of MySQL: At 15:46 -0400 5/18/04, Robert A. Rosenberg wrote: I have a site that is being hosted by an ISP which is running version 3.23.52. When I questioned why that downlevel version and not a 4.0 version (such as 4.0.18 or the just released 4.0.20), I was told Unfortunately, when versions change on MySQL, they also drop features and change security settings. This can cause many problems system wide. Before I go further with my discussion and renew my request for a 4.0 Database, I would like to know if it is even possible to have more than one level active (and if so, what is involved in the set-up). It's perfectly possible. I have dozens of versions installed, though not all necessarily running at the same time. :-) http://dev.mysql.com/doc/mysql/en/Multiple_servers.html Thank you for your reply. I am passing it on to the ISP in the hope that they will use it to provide an option to offer current level support to those who need/want it (by just adding the new port number to the Host Name in MySQL connection request). I have one suggestion to be added to the base code to assist in running multiple levels - Allow the --port=port_number parameter to take a list as opposed to only one number (so the server monitors more than one port and treats them as if they were the same). This would allow a migration to the newer versions to proceed by having the base port number as well as the alternate one be available for the use after the migration while those who were using the alternate port number in their calls are removing the number so as to again use the standard port number. Initially, the downlevel version would have ownership of the standard port as well as a private port (for those who do not want to be migrated after the move) which would be reassigned on Roll Day to the newer version. The alternate port could then be scheduled to be removed after a period of time to allow for its removal from the Host Name calls by those who were using the newer version prior to the Roll. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Password error
Hello MySQL Listers,
Using myODBC 3.51.07, Win2k SP4, ADO 2.7 mySQL5
If I make the connection when user account does not have a password it works.
If I set a password I get the error below ???
Which MySQL client is it referring to??
Any suggestions regarding connection string ??
err No -2147467259
[MySQL][ODBC 3.51 Driver]Client does not support authentication protocol
requested by server; consider upgrading MySQL client
cnn.ConnectionString = DRIVER={MySQL ODBC 3.51 Driver}; _
SERVER= strServerName ; _
DATABASE= strDBName ; _
UID= strLogin ; _
PWD= strPassword ; _
OPTION=3
also tried ' OPTION= 1 + 2 + 8 + 32 + 2048 + 163841
--
Best regards,
Arthur mailto:[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Running more than one level of MySQL
In the last episode (May 19), Robert A. Rosenberg said: I have one suggestion to be added to the base code to assist in running multiple levels - Allow the --port=port_number parameter to take a list as opposed to only one number (so the server monitors more than one port and treats them as if they were the same). This You can do similar tricks with your OS'es NAT or firewall software if it supports port forwarding. One plus with doing it this way is you don't have to bounce mysql to make changes. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqld sock conflicts
I have two mysql apps running on the same machine (OS X 10.3.3). A mysql 3.23.54 on port 14551, and a mysql 4.0.16 on 3306. Each has a config file specifying the port and a unique socket name in /tmp. They have coexisted just peachy for a very long time. Now however, w/o any changes to either MySQL3, MySQL4, or the OS, every time I issue a terminal command to one of the MySQL3 bin apps preceded by the usual cd /x/y/z/bin, the commands are being sent to the /usr/local/mysql bin apps on 3306. If I shut mysqld 3306 down (which closes the sock file), then any commands to mysql 14551 gripes that there is no socket file even though the one it should be using is still available. Removing and reinstalling both mysql's (now I have 4.0.18) does not fix it. Each does in fact create its own sock file in /tmp, and each mysqld server runs just fine. I can manually specify the --socket for the 14551 bin apps and they'll work, but I've used mysql3 and mysql4 side by side since one of the later 4.0 betas and I've never had to specify the socket when launching any of the mysql3 utils. There has to be some other kind of socket related config file somewhere that has something to do with this? I have no idea what could have changed all of a sudden and on its own. Thanks for any clues. -- greg willits -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
vb.net and mysql training
I am looking for a course in North America that provides mysql and vb.net or c# training or a good text book or dvds. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Password error
You can try using the old-passwords option in the my.cnf file or you can try
building your ODBC driver from the bitkeeper source. I would check the
documentation to verify that the ODBC build you have supports the 4.1.+
servers.
-Original Message-
From: Arthur Maloney
To: [EMAIL PROTECTED]
Sent: 5/19/04 12:28 PM
Subject: Password error
Hello MySQL Listers,
Using myODBC 3.51.07, Win2k SP4, ADO 2.7 mySQL5
If I make the connection when user account does not have a password it
works.
If I set a password I get the error below ???
Which MySQL client is it referring to??
Any suggestions regarding connection string ??
err No -2147467259
[MySQL][ODBC 3.51 Driver]Client does not support authentication protocol
requested by server; consider upgrading MySQL client
cnn.ConnectionString = DRIVER={MySQL ODBC 3.51 Driver}; _
SERVER= strServerName ; _
DATABASE= strDBName ; _
UID= strLogin ; _
PWD= strPassword ; _
OPTION=3
also tried ' OPTION= 1 + 2 + 8 + 32 + 2048 + 163841
--
Best regards,
Arthur mailto:[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: BLOB's - General Guidance
On May 19, 2004, at 1:19 PM, David Blomstrom wrote: I'd like to get some feedback on storing images in MySQL databases. The stuff I've read so far suggests that it's fairly difficult to work with images in MySQL, and they also slow down databases. I've also read that there isn't much you can do with BLOB's that you can't do with PHP manipulating images stored in an ordinary folder. So I just wondered if BLOB's are worth my time. For example, I'm working on a database with information about the 50 states. If I have maps of each state, pictures of each state's capital, etc., is there some BLOB feature that I would find really useful? All conventional wisdom I've ever come across for this type of application is that there's no advantage to keeping the image in the db itself. Just keep them as files on the server, store a filename /or location in the db if necessary, and use your middleware to display the images. Its faster, easier to maintain, and easier to backup. IMO, storing images in the db just bloats the file and complicates all the backup issues. -- greg willits -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: BLOB's - General Guidance
In a lot of cases storing images on the filesystem makes the most sense. We store images in a database because we have multiple webservers that need to both read and write images. Keeping them in MySQL makes this easy. NFS isn't an option for us. Having images in the database also makes scaling pretty simple in that we can add replicated slave machines for reading images. David Blomstrom wrote: --- Greg Willits [EMAIL PROTECTED] wrote: On May 19, 2004, at 1:19 PM, David Blomstrom wrote: All conventional wisdom I've ever come across for this type of application is that there's no advantage to keeping the image in the db itself. Just keep them as files on the server, store a filename /or location in the db if necessary, and use your middleware to display the images. Its faster, easier to maintain, and easier to backup. IMO, storing images in the db just bloats the file and complicates all the backup issues. That's the advice I wanted to hear; one less thing for me to learn. :) Thanks. __ Do you Yahoo!? SBC Yahoo! - Internet access at a great low price. http://promo.yahoo.com/sbc/ -- Senior Programmer, Tufts University Sciences Knowledgebase [EMAIL PROTECTED] 617.636.0959 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: BLOB's - General Guidance
I have an application where I serve out blobs. I store the PDF files in blob fields for two reasons. 1. I want to make an interface where staff can upload pdf's into the server but not actually give them access to the server. I use my code to maintain the security of who gets to write to what. 2. The pdf's are date sensitive press releases and public notices. They must show up on our web site on a certain date and they MUST not be able to be accessed on our site after that time. Having them stored as blobs I use a cfm page to fetch them out of the database and it is easy for me to write the sql to ~WHERE date or date If they were on the file system someone could have bookmarked the location of the pdf and unless I have something written to go in and prune files out, it could still be gotten. --ja On Wed, 19 May 2004, David Blomstrom wrote: --- Greg Willits [EMAIL PROTECTED] wrote: On May 19, 2004, at 1:19 PM, David Blomstrom wrote: All conventional wisdom I've ever come across for this type of application is that there's no advantage to keeping the image in the db itself. Just keep them as files on the server, store a filename /or location in the db if necessary, and use your middleware to display the images. Its faster, easier to maintain, and easier to backup. IMO, storing images in the db just bloats the file and complicates all the backup issues. That's the advice I wanted to hear; one less thing for me to learn. :) Thanks. __ Do you Yahoo!? SBC Yahoo! - Internet access at a great low price. http://promo.yahoo.com/sbc/ -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Large inserts, chunking and Concat
Part of the problem was the way the server settings were allocating memory. The concat would work until a certain size and then the suddenly failed to insert (inserted NULL). After the memory configuration change, in terms of concatenating (with CONCAT) in pieces, it seems to work at least in my test to about 240MBs. There may still be a problem as the data grows beyond this. John Harald Fuchs wrote: In article [EMAIL PROTECTED], John Ling [EMAIL PROTECTED] writes: Hello, realizing that there is a max_allowed_packet setting that limits the size of the insert statement, is there a way around it by chunking the query? In particular, if the query is to insert a large text or blob, can I simply concatenate smaller pieces of the data in succession using the Concat command? My concern is whether this will still in someway cause me other MySQL resource problems? I want to be able to insert a large text or blob of over 200-400MBs. I tried INSERTing in chunks with concat() a few months ago and found out that it didn't work; thus effectively your blob size is limited by max_allowed_packet. But since you can increase max_allowed_packet up to 1G since version 4.0, this should not be a problem any more. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Connector/J 3.0.12 (Production) Has Been Released
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, MySQL Connector/J 3.0.12, a new version of the Type-IV all-Java JDBC driver for MySQL has been released. Version 3.0.12 is a bugfix release for the production tree that is suitable for use with any MySQL version including MySQL-4.1 or MySQL-5.0. It is now available in source and binary form from the Connector/J download pages at http://dev.mysql.com/downloads/connector/j/3.0.html and mirror sites (note that not all mirror sites may be up to date at this point of time - if you can't find this version on some mirror, please try again later or choose another download site.) -Mark - From the changelog: - Add unsigned attribute to DatabaseMetaData.getColumns() output in the TYPE_NAME column. - Added 'failOverReadOnly' property, to allow end-user to configure state of connection (read-only/writable) when failed over. - Backported 'change user' and 'reset server state' functionality from 3.1 branch, to allow clients of MysqlConnectionPoolDataSource to reset server state on getConnection() on a pooled connection. - Don't escape SJIS/GBK/BIG5 when using MySQL-4.1 or newer. - Allow 'url' parameter for MysqlDataSource and MysqlConnectionPool DataSource so that passing of other properties is possible from inside appservers. - Map duplicate key and foreign key errors to SQLState of '23000'. - Backport documentation tooling from 3.1 branch. - Return creating statement for ResultSets created by getGeneratedKeys() (BUG#2957) - Allow java.util.Date to be sent in as parameter to PreparedStatement.setObject(), converting it to a Timestamp to maintain full precision (BUG#3103). - Don't truncate BLOBs/CLOBs when using setBytes() and/or setBinary/CharacterStream() (BUG#2670). - Dynamically configure character set mappings for field-level character sets on MySQL-4.1.0 and newer using 'SHOW COLLATION' when connecting. - Map 'binary' character set to 'US-ASCII' to support DATETIME charset recognition for servers = 4.1.2 - Use 'SET character_set_results during initialization to allow any charset to be returned to the driver for result sets. - Use charsetnr returned during connect to encode queries before issuing 'SET NAMES' on MySQL = 4.1.0. - Add helper methods to ResultSetMetaData (getColumnCharacterEncoding() and getColumnCharacterSet()) to allow end-users to see what charset the driver thinks it should be using for the column. - Only set character_set_results for MySQL = 4.1.0. - Fixed BUG#3511, StringUtils.escapeSJISByteStream() not covering all eastern double-byte charsets correctly. - Renamed StringUtils.escapeSJISByteStream() to more appropriate escapeEasternUnicodeByteStream(). - Fixed BUG#3554 - Not specifying database in URL caused MalformedURL exception. - Auto-convert MySQL encoding names to Java encoding names if used for characterEncoding property. - Added encoding names that are recognized on some JVMs to fix case where they were reverse-mapped to MySQL encoding names incorrectly. - Use junit.textui.TestRunner for all unit tests (to allow them to be run from the command line outside of Ant or Eclipse). - Fixed BUG#3557 - UpdatableResultSet not picking up default values for moveToInsertRow(). - Fixed BUG#3570 - inconsistent reporting of column type. The server still doesn't return all types for *BLOBs *TEXT correctly, so the driver won't return those correctly. - Fixed BUG#3520 -- DBMD.getSQLStateType() returns incorrect value. - Fixed regression in PreparedStatement.setString() and eastern character encodings. - Made StringRegressionTest 4.1-unicode aware. -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.3 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFAq7tLtvXNTca6JD8RAoaJAJ4ulqzTCr6AGYMj3cin+6OAhmLnBQCeJzx2 ErH3NfgSHz604cANPYH5/PM= =N1Pu -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
BLOB's - General Guidance
I'd like to get some feedback on storing images in MySQL databases. The stuff I've read so far suggests that it's fairly difficult to work with images in MySQL, and they also slow down databases. I've also read that there isn't much you can do with BLOB's that you can't do with PHP manipulating images stored in an ordinary folder. So I just wondered if BLOB's are worth my time. For example, I'm working on a database with information about the 50 states. If I have maps of each state, pictures of each state's capital, etc., is there some BLOB feature that I would find really useful? Thanks. __ Do you Yahoo!? SBC Yahoo! - Internet access at a great low price. http://promo.yahoo.com/sbc/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Simple table, 1.7 million rows, very slow SELECTs
Thanks to everyone who helped me with this. I settled on breaking it down into area code, exchange and subscriber. This is one of the most generous lists I've ever had the pleasure of begging for help on. -- Jacob Elder -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
GRANT semantics...
I'm trying to use a grant statement to grant privileges to a user on a set of database names. e.g. for some user k, I want them to be able to have complete access to any database named k_*. I know this can be done. The test database is setup this way by default. Here is what I tried: grant all on k_*.* to 'user'@'somedomain' identified by 'somepassword' It replies with an 'you have an error in you SQL syntax somewhere.' However, I can do this: grant all on k_db.* to 'user'@'somedomain' identified by 'somepassword' update db set db = 'k\_%' where user = 'user' This is how the test database looked in the mysql.db table, so I mirrored it. And that works. But, can this be done in a grant statement? I was fairly sure I did this before, but this is a new installation and it doesn't work. I've tried variations of the *. I tried k_%.* k_%, k\_%.*, etc. None of these work. I have looked in the mysql manual in the user creation section, but I haven't found anything similar to what I'm trying to do. The grant/revoke syntax also tells nothing. It just says db_name.* if you want to grant privileges on a database. My mysql version is 4.0.18. Thanks, John Ratliff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: BLOB's - General Guidance
--- Greg Willits [EMAIL PROTECTED] wrote: On May 19, 2004, at 1:19 PM, David Blomstrom wrote: All conventional wisdom I've ever come across for this type of application is that there's no advantage to keeping the image in the db itself. Just keep them as files on the server, store a filename /or location in the db if necessary, and use your middleware to display the images. Its faster, easier to maintain, and easier to backup. IMO, storing images in the db just bloats the file and complicates all the backup issues. That's the advice I wanted to hear; one less thing for me to learn. :) Thanks. __ Do you Yahoo!? SBC Yahoo! - Internet access at a great low price. http://promo.yahoo.com/sbc/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Installing Mysql binary distribution
First of all check the *.err log in your data dir. It will likely give you some clue as to what is happening. -Original Message- From: Laercio Xisto Braga Cavalcanti [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 19, 2004 3:08 PM To: [EMAIL PROTECTED] Subject: Installing Mysql binary distribution Hi all, I trying to install and use a MySQL binary distribution doing the folowing steps: Download the file mysql-standard-4.0.20-pc-linux-i686.tar.gz groupadd mysql useradd -g mysql mysql cd /usr/local gunzip /path/to/mysql-VERSION-OS.tar.gz | tar xvf - ln -s full-path-to-mysql-VERSION-OS mysql cd mysql scripts/mysql_install_db chown -R root . chown -R mysql data chgrp -R mysql . bin/mysqld_safe --user=mysql and gotr the following result [EMAIL PROTECTED] local]# ln -s mysql-standard-4.0.20-pc-linux-i686 mysql [EMAIL PROTECTED] local]# cd mysql [EMAIL PROTECTED] mysql]# scripts/mysql_install_db Preparing db table Preparing host table Preparing user table Preparing func table Preparing tables_priv table Preparing columns_priv table Installing all prepared tables 040519 15:38:26 Warning: Asked for 196608 thread stack, but got 126976 040519 15:38:26 ./bin/mysqld: Shutdown Complete To start mysqld at boot time you have to copy support-files/mysql.server to the right place for your system PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER ! To do so, start the server, then issue the following commands: ./bin/mysqladmin -u root password 'new-password' ./bin/mysqladmin -u root -h localhost.localdomain password 'new-password' See the manual for more instructions. You can start the MySQL daemon with: cd . ; ./bin/mysqld_safe You can test the MySQL daemon with the benchmarks in the 'sql-bench' directory: cd sql-bench ; perl run-all-tests Please report any problems with the ./bin/mysqlbug script! The latest information about MySQL is available on the web at http://www.mysql.com Support MySQL by buying support/licenses at https://order.mysql.com [EMAIL PROTECTED] mysql]# chown -R root . [EMAIL PROTECTED] mysql]# chown -R mysql data [EMAIL PROTECTED] mysql]# chgrp -R mysql . [EMAIL PROTECTED] mysql]# bin/mysqld_safe --user=mysql [1] 11928 [EMAIL PROTECTED] mysql]# Starting mysqld daemon with databases from /var/lib/mysql 040519 15:41:10 mysqld ended [1]+ Donebin/mysqld_safe --user=mysql Can anybody help me??? -- Using Opera's revolutionary e-mail client: http://www.opera.com/m2/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] ** The information contained in this communication is confidential, is intended only for the use of the recipient named above, and may be legally privileged. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution, or copying of this communication is strictly prohibited. If you have received this communication in error, please re-send this communication to the sender and delete the original message or any copy of it from your computer system. Thank You. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Installing Mysql binary distribution
Hi all, I trying to install and use a MySQL binary distribution doing the folowing steps: Download the file mysql-standard-4.0.20-pc-linux-i686.tar.gz groupadd mysql useradd -g mysql mysql cd /usr/local gunzip /path/to/mysql-VERSION-OS.tar.gz | tar xvf - ln -s full-path-to-mysql-VERSION-OS mysql cd mysql scripts/mysql_install_db chown -R root . chown -R mysql data chgrp -R mysql . bin/mysqld_safe --user=mysql and gotr the following result [EMAIL PROTECTED] local]# ln -s mysql-standard-4.0.20-pc-linux-i686 mysql [EMAIL PROTECTED] local]# cd mysql [EMAIL PROTECTED] mysql]# scripts/mysql_install_db Preparing db table Preparing host table Preparing user table Preparing func table Preparing tables_priv table Preparing columns_priv table Installing all prepared tables 040519 15:38:26 Warning: Asked for 196608 thread stack, but got 126976 040519 15:38:26 ./bin/mysqld: Shutdown Complete To start mysqld at boot time you have to copy support-files/mysql.server to the right place for your system PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER ! To do so, start the server, then issue the following commands: ./bin/mysqladmin -u root password 'new-password' ./bin/mysqladmin -u root -h localhost.localdomain password 'new-password' See the manual for more instructions. You can start the MySQL daemon with: cd . ; ./bin/mysqld_safe You can test the MySQL daemon with the benchmarks in the 'sql-bench' directory: cd sql-bench ; perl run-all-tests Please report any problems with the ./bin/mysqlbug script! The latest information about MySQL is available on the web at http://www.mysql.com Support MySQL by buying support/licenses at https://order.mysql.com [EMAIL PROTECTED] mysql]# chown -R root . [EMAIL PROTECTED] mysql]# chown -R mysql data [EMAIL PROTECTED] mysql]# chgrp -R mysql . [EMAIL PROTECTED] mysql]# bin/mysqld_safe --user=mysql [1] 11928 [EMAIL PROTECTED] mysql]# Starting mysqld daemon with databases from /var/lib/mysql 040519 15:41:10 mysqld ended [1]+ Donebin/mysqld_safe --user=mysql Can anybody help me??? -- Using Opera's revolutionary e-mail client: http://www.opera.com/m2/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Cluster - queries execute with 6.60sec delay when one DB node is dead
Hi, This behaviour is due to a bug (#3657) which has been solved and the fix is on its way out. Rgrds Mikael 2004-05-19 kl. 13.35 skrev Maciek Dobrzanski: Hi, I have configured MySQL Cluster on two machines with 2 DB nodes (NoOfReplicas = 2) and 2 MySQL API nodes, one of each node type on both systems. The config is almost the same as the one of 2-node demo. The cluster is working fine as long as all DB nodes are operational, but if one of them is gone (i.e. I shut it down), all queries that are sent to the MySQL API nodes seem to hang for about 6.60sec before they are actually executed. As soon as the dead DB node becomes available again, everything starts to work as it supposed to. If one of DB nodes is dead: mysql SELECT * FROM t; ++---+ | id | name | ++---+ | 2 | test2 | | 1 | test1 | ++---+ 2 rows in set (6.60 sec) with all DB nodes working: mysql SELECT * FROM t; ++---+ | id | name | ++---+ | 2 | test2 | | 1 | test1 | ++---+ 2 rows in set (0.00 sec) It looks like MySQL is waiting for the dead node to respond, gets timed out after about 6 seconds and then requests the answer from the other node. I did not find anything in the Administrator Guide that would say about such behaviour, which makes the cluster rather useless in case of a node crash. Any ideas how to fix it? Regards, Maciek -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] Mikael Ronström, 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 General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Simple table, 1.7 million rows, very slow SELECTs
Jacob Elder wrote: Thanks to everyone who helped me with this. I settled on breaking it down into area code, exchange and subscriber. This is one of the most generous lists I've ever had the pleasure of begging for help on. I don't want to deter you from making the changes above, but it's pretty easy to figure out if the key-buffer is too small; it's much less work to try that out first, and then if it doesn't work, making radical changes to your data. Also, keep in mind that MySQL will use only one index per table in a query. If you break a single row into three columns, and then index the three columns, MySQL will now have to decide which of the three indexes to use. This means that because you are indexing a subset of your data, MySQL will end up doing more work because the index will not be as exact. For example, SELECT area_code, prefix, postfix FROM phone_numbers WHERE area_code=402 and prefix=232 and postfix=4222; will force MySQL to pick the index on area_code, prefix, or postfix. Since postfix is the most discriminating index, it will reduce the number of rows down the fastest. Say there are 800 rows with the postfix 4222. It now has to scan those 800 rows to match the area code and prefix. No index will be used there; it's a small scan, but it will add overhead to each search. If you keep all the data in one column, then the one and only index should be chosen, and it should go straight to the row. One other thing to consider is the length of your index. For char and varchar indexes, you can tell MySQL how many of the characters you want included in your index. For example, CREATE INDEX index_name ON table(column(4)); will only index the first four characters of the column. You may want to specify an index that matches the length of your data (10 characters, I believe). Also, keep in mind that a char is faster than a varchar if you have exactly 10 characters for each row (rather than 7 sometimes, and 10 other times). I am not sure what the default index length is, but it could be something like 32 or 64. Anyone know? David -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: BLOB's - General Guidance
--- Michael Kruckenberg [EMAIL PROTECTED] wrote: Having images in the database also makes scaling pretty simple in that we can add replicated slave machines for reading images. I'm not yet familiar with scaling and replicated slave machines, but I'm beginning to see more uses for storing image LINKS in MySQL. Suppose I have a field that stores links that look like this: img src=?php echo $seg ?images/states/ak.gif width=100 height=75 / img src=?php echo $seg ?images/states/wy.gif width=100 height=75 / and another field that stores image links that look like this: img src=?php echo $seg ?images/maps/ak.gif width=100 height=75 / img src=?php echo $seg ?images/maps/wy.gif width=100 height=75 / then all I have to do is replace $seg with the path to the image folder to display my images. The images don't even have to be the same size, since I have to enter each state's image individually, anyway. I guess that's pretty simplistic, but it's something I hadn't thought of before. I was thinking of databases in terms of text only. Actually, it would be more convenient if I could enter image links without the width and height values. I suppose that would work if I used them as background images. And I could even create a field that anticipates a future series of images in an as yet unspecified folder: img src=?php echo $seg ?images/?php echo $WHAT ?/ak.gif width=100 height=75 / If I then create a series of images in a folder named landscapes - or nature/landscapes - then I would use PHP to replace WHAT with nature/landspaces. __ Do you Yahoo!? Yahoo! Domains Claim yours for only $14.70/year http://smallbusiness.promotions.yahoo.com/offer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: BLOB's - General Guidance
On May 19, 2004, at 1:19 PM, David Blomstrom wrote: All conventional wisdom I've ever come across for this type of application is that there's no advantage to keeping the image in the db itself. Just keep them as files on the server, store a filename /or location in the db if necessary, and use your middleware to display the images. Its faster, easier to maintain, and easier to backup. IMO, storing images in the db just bloats the file and complicates all the backup issues. On Wed, 19 May 2004, David Blomstrom wrote: That's the advice I wanted to hear; one less thing for me to learn. :) Thanks. On May 19, 2004, at 2:19 PM, jabbott wrote: I have an application where I serve out blobs. I store the PDF files in blob fields for two reasons. 1. I want to make an interface where staff can upload pdf's into the server but not actually give them access to the server. I use my code to maintain the security of who gets to write to what. Middleware should be able to control that. 2. The pdf's are date sensitive press releases and public notices. They must show up on our web site on a certain date and they MUST not be able to be accessed on our site after that time. Having them stored as blobs I use a cfm page to fetch them out of the database and it is easy for me to write the sql to ~WHERE date or date If they were on the file system someone could have bookmarked the location of the pdf and unless I have something written to go in and prune files out, it could still be gotten. Again, middleware ought to be able to control this a few different ways. One is to use a protected file area to which a server side process has privileged access to, then passed the file to the user for download. Your app determines whether the current web visitor can trigger the script that delivers the file. The file can't be bookmarked. Another method is to change the name as the file is delivered, so that the file received by the user doesn't even match the one on the server. Anyway, I'm sure there's legit uses for storing web resources in BLOBs, but so far I've never seen an advantage over use server config and the web app itself (not that I've made any monster apps mind you, all of mine have been simple enough for single or dual server setup). -- greg willits -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
selecting by average
I have a table called ratings. It has 3 rows of interest id foreign key to another table quala quality rating u the user who gave that rating (also a foreign key into the users table) Now I would like to select all the unique ids for which the average quality is below 1.5 so if we have id qual u 1 5 999 1 4 888 2 1 999 2 1 888 3 3 777 3 2 888 it would tell me that id 2 has average ratings below 1.5 it doesn't like SELECT id from ratings where AVG(qual) 1.5 group by id unfortch this server is still 3.23 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: BLOB's - General Guidance
On May 19, 2004, at 3:12 PM, David Blomstrom wrote: Suppose I have a field that stores links that look like this: img src=?php echo $seg ?images/states/ak.gif width=100 height=75 / img src=?php echo $seg ?images/states/wy.gif width=100 height=75 / and another field that stores image links that look like this: img src=?php echo $seg ?images/maps/ak.gif width=100 height=75 / img src=?php echo $seg ?images/maps/wy.gif width=100 height=75 / It's better to have a central config file that defines all your paths as variables. No need to embed hard paths in your links either in code or stored in the db. I do mockup with a GUI which creates paths, but then apply a gobal search replace to change the src to variable plus the file name. So, even your images/maps/ component can be a var. then all I have to do is replace $seg with the path to the image folder to display my images. The images don't even have to be the same size, since I have to enter each state's image individually, anyway. If the images are pre-defined standards, then yeah, entering the size in the db is probably as good a way as any if they're different sizes. However, if you have a bucnh of standard image slots to be filled with images of all the same size, then you can set those as PHP vars defined in config file. Another method is to embed the size in the file name, and parse the file name when it is retrieved from the db. Finally, if the images are being uploaded, use a tool like imagemagick to acquire the image properties at that time and store them. Then your sizes can be vars too. And I could even create a field that anticipates a future series of images in an as yet unspecified folder: img src=?php echo $seg ?images/?php echo $WHAT ?/ak.gif width=100 height=75 / If I then create a series of images in a folder named landscapes - or nature/landscapes - then I would use PHP to replace WHAT with nature/landspaces. Yep. That can be done in the db or in a univeral config file your middleware reads depending on what makes the most sense. -- greg willits -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: BLOB's - General Guidance
David Blomstrom wrote: then all I have to do is replace $seg with the path to the image folder to display my images. The images don't even have to be the same size, since I have to enter each state's image individually, anyway. Actually, it would be more convenient if I could enter image links without the width and height values. I suppose that would work if I used them as background images. Actually, if you include the dimensions in your image *names* -- which is a great timesaver -- you can store them as, say ak.200x150.gif and retrieve as SELECT img , SUBSTRING(img,4,3) AS width , SUBSTRING(img,8,3) AS height FROM images WHERE LEFT(img,2) = 'ak' You can put that into your own function and write the entire image tag out with the proper width and height, in one swell foop :-) FWIW! -- Hassan Schroeder - [EMAIL PROTECTED] Webtuitive Design === (+1) 408-938-0567 === http://webtuitive.com dream. code. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: BLOB's - General Guidance
--- Hassan Schroeder [EMAIL PROTECTED] wrote: Actually, if you include the dimensions in your image *names* -- which is a great timesaver -- you can store them as, say ak.200x150.gif and retrieve as SELECT img , SUBSTRING(img,4,3) AS width , SUBSTRING(img,8,3) AS height FROM images WHERE LEFT(img,2) = 'ak' You can put that into your own function and write the entire image tag out with the proper width and height, in one swell foop :-) * * * * * * * * * * It will take me a while to understand this, as I haven't even begun to learn about strings, let alone substrings, yet. But I have a couple questions, for future reference: 1. When you talk about including dimensions in image names, do you mean it literally? For example, if I create an image in photoshop that measures 150px X 125px, I could save it as horse.jpg, or save it as horse.150X125.jpg? 2. Are you talking about storing the actula image in MySQL (as a BLOB), or just placing links to image names (that include dimensions) in a field?... ak.200x150.gif wy.225X110.jpg __ Do you Yahoo!? Yahoo! Domains Claim yours for only $14.70/year http://smallbusiness.promotions.yahoo.com/offer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re[2]: BLOB's - General Guidance
Hello David, Thursday, May 20, 2004, 2:21:37 AM, you wrote: DB --- Hassan Schroeder [EMAIL PROTECTED] wrote: Actually, if you include the dimensions in your image *names* -- which is a great timesaver -- you can store them as, say ak.200x150.gif and retrieve as SELECT img , SUBSTRING(img,4,3) AS width , SUBSTRING(img,8,3) AS height FROM images WHERE LEFT(img,2) = 'ak' You can put that into your own function and write the entire image tag out with the proper width and height, in one swell foop :-) DB * * * * * * * * * * DB It will take me a while to understand this, as I DB haven't even begun to learn about strings, let alone DB substrings, yet. DB But I have a couple questions, for future reference: DB 1. When you talk about including dimensions in image DB names, do you mean it literally? For example, if I DB create an image in photoshop that measures 150px X DB 125px, I could save it as horse.jpg, or save it as DB horse.150X125.jpg? DB 2. Are you talking about storing the actula image in DB MySQL (as a BLOB), or just placing links to image DB names (that include dimensions) in a field?... DB ak.200x150.gif DB wy.225X110.jpg php + mysql 1 define path to store files in conf file 2 upload there with unique name (use php uniqueid) 3 to show image use php getimagesize to get width, height and image type (even for swf) that's the way only minus is more work to backup/copy database with images -- Best regards, artemmailto:[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: BLOB's - General Guidance
David Blomstrom wrote: 1. When you talk about including dimensions in image names, do you mean it literally? For example, if I create an image in photoshop that measures 150px X 125px, I could save it as horse.jpg, or save it as horse.150X125.jpg? Yep, horse.150X125.jpg And feel free to add any other metadata you think you'd be using frequently :-) You might, for instance, have icons like button.go.20x20.red.transparent.png 2. Are you talking about storing the actula image in MySQL (as a BLOB), or just placing links to image names (that include dimensions) in a field?... Just the file names -- then generate your links from the DB data. -- Hassan Schroeder - [EMAIL PROTECTED] Webtuitive Design === (+1) 408-938-0567 === http://webtuitive.com dream. code. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Please help me optimize a MySQL server.
On Wed, May 19, 2004 at 11:36:38AM -0400, Brent Baisley wrote: I wouldn't upgrade until you know where the bottleneck is (CPU, disk, network, or RAM). Since you are using professional software, I wouldn't try to change the queries. Have you made changes to your my.cnf file? Since you have enough ram to hold all the data, ram is probably not your bottleneck. The question is whether you have your system configured to use it. Here is the uncommented parts in the /etc/my.cnf: [client] port= 3306 socket = /tmp/mysql.sock [mysqld] port= 3306 socket = /tmp/mysql.sock key_buffer = 384M skip-locking set-variable= max_connections=3500 max_allowed_packet = 1M table_cache = 512 sort_buffer_size = 2M read_buffer_size = 2M myisam_sort_buffer_size = 64M thread_cache = 8 query_cache_size = 32M thread_concurrency = 2 server-id = 1 [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash [isamchk] key_buffer = 256M sort_buffer_size = 256M read_buffer = 2M write_buffer = 2M [myisamchk] key_buffer = 256M sort_buffer_size = 256M read_buffer = 2M write_buffer = 2M I forget if phpbb and phpnuke use PEAR::DB to access the underlying database. If they do, there is a certain amount of overhead involved that you just can't get around without changing the database access code to use native calls. Are your database and web server on the same machine (a security no-no, but performance yes-yes)? If they are on separate machines, make sure you have a fast connection between them, at least 100MB and make sure it's full duplex. I think they don't use it, The database on a separate server, with a 1000MB LAN connection, Same for the 2 webservers. I verified this now. On May 19, 2004, at 10:34 AM, Mohammed Sameer wrote: On Wed, May 19, 2004 at 08:47:28AM -0400, Brent Baisley wrote: Something that small shouldn't really need optimizing. What is the size of your data (mb?, gb?) and what does your query look like? If you are doing a wild card search on a large text field without a full text index, then those times may be the best you're going to get. Many times it's about optimizing your query statement or you indexes rather than MySQL settings. mainly we are using 2 databases: 675M for phpbb and 77M for phpnuke. That's not much. Ok, It seems that the actual queries performed by phpbb are optimized. For instance, I have a test machine (Mac G4 400Mhz, 512MB, MySQL 4.0.18) that does a three table join with each table having just over 100,000 rows. My initial query took about 12 seconds, but changing my query statement around a little I got it to just under 1 second. Which is pretty good considering the hardware it's on. What does you explain for the query look like? It's probably doing a full table scan, which means you're bottleneck is the disk. but the server is running on a duel P III with 2 GB RAM and a RAID controller. Do you think there is much to be done, or shall I look for better hardware/or clustering ? On May 19, 2004, at 3:26 AM, Mohammed Sameer wrote: Hi, I'm running a MySQL server on a duel P III 1G, with 2 GB RAM. MySQL 4.0.18 compiled from source. We have 2 webservers running apache, And this is the backend database server. The server is really slow. a select on a table with 138,247 rows takes about 1.6 - 2+ seconds, I have about 50% CPU idle. using MyISAM table types. sure I can provide any statistics about the running server. Do you think that switching to innodb'd help ? Is it a problem with Debian woody ?? Any suggestions are welcomed. Many thanks! -- -- Katoob Main Developer Linux registered user #224950, ICQ #58475622 -- Don't send me any attachment in Micro$oft (.DOC, .PPT) format please Read http://www.fsf.org/philosophy/no-word-attachments.html Preferable attachments: .PDF, .HTML, .TXT Thanx for adding this text to Your signature -- -BEGIN GEEK CODE BLOCK- Version: 3.1 GCM/IT d-(++)@ s+(++):-+++ a-- C+++$ UL+++$ P+++$+ L+++()$+ E+++ W++?$ N o? K-? !w++ !O !M !V !PS@ !PE@ Y+ PGP=+++ t? 5? !X R? tv-- b+@ DI D+ G-- e+ h--++ !r y? --END GEEK CODE BLOCK-- -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- -- Katoob Main Developer Linux registered user #224950, ICQ #58475622 -- Don't send me any attachment in Micro$oft (.DOC, .PPT) format please Read http://www.fsf.org/philosophy/no-word-attachments.html Preferable attachments: .PDF, .HTML, .TXT Thanx for adding this text to Your signature -- -BEGIN GEEK CODE BLOCK- Version: 3.1 GCM/IT d-(++)@ s+(++):-+++ a-- C+++$ UL+++$ P+++$+ L+++()$+ E+++ W++?$ N o? K-? !w++ !O !M !V !PS@ !PE@ Y+ PGP=+++ t? 5? !X R? tv-- b+@ DI D+ G-- e+ h--++ !r y? --END GEEK
Re: Please help me optimize a MySQL server.
on 05/19/2004 08:36 AM, Brent Baisley at [EMAIL PROTECTED] wrote: I wouldn't upgrade until you know where the bottleneck is (CPU, disk, network, or RAM). Since you are using professional software, I wouldn't try to change the queries. Have you made changes to your my.cnf file? Since you have enough ram to hold all the data, ram is probably not your bottleneck. The question is whether you have your system configured to use it. I forget if phpbb and phpnuke use PEAR::DB to access the underlying database. If they do, there is a certain amount of overhead involved that you just can't get around without changing the database access code to use native calls. Are your database and web server on the same machine (a security no-no, but performance yes-yes)? If they are on separate machines, make sure you have a fast connection between them, at least 100MB and make sure it's full duplex. PhpBB is just php access to mysql, no PEAR::DB involved. I can say from eperience, the phpBB code is kinda bad in regards to how it talks to mysql, on some pages it is not at all uncommon for there to be 10 or more complex querries. I would be willing to bet there are many installed mods on phpBB, they offer stuff like, show all online users which though a nice feature, is heavy on the DB. There are hundreds of these mods, people just copy and paste and install them, before they know it the database is taking 30 selects on every single page load, just so you can say happy birthday to: someuser on every page. -- - 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]
Help with Joins
Greetings. I have a table that contains procedures and a table that contains forms. Each procedure may have 1 or more forms or it may have no associated forms. Each form may be relevant to more than 1 procedure. The procedure table has 1 entry per procedure. The forms table may have more than 1 entry per form depending on how many procedures use that form. Each form entry has a foriegn key tying it to the record number of the procedure. I want to compose a query that will pull out each distinct instance of a form (in other words, no duplicates) and then list which procedures use that form So: Form Name Procedure(s) Name Is this a clear enough explanation for folks? I'm willing to RTFM if somebody will point me to the chapter(s) that discuss different join syntax and how it's used in MySQL. I'm using 3.23.54 in my production environment at the moment and my tables are MyISAM. Thanks in Advance = Robert Reed 512-869-0063 home 512-818-2460 cell __ Do you Yahoo!? Yahoo! Domains Claim yours for only $14.70/year http://smallbusiness.promotions.yahoo.com/offer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help with Joins
Robert Reed wrote: Greetings. I have a table that contains procedures and a table that contains forms. Each procedure may have 1 or more forms or it may have no associated forms. Each form may be relevant to more than 1 procedure. The procedure table has 1 entry per procedure. The forms table may have more than 1 entry per form depending on how many procedures use that form. Each form entry has a foriegn key tying it to the record number of the procedure. I want to compose a query that will pull out each distinct instance of a form (in other words, no duplicates) and then list which procedures use that form So: Form Name Procedure(s) Name Something like: SELECT Form.Name, Procedure.Name FROM Form, Procedure WHERE Form.Procedure_id = Procedure.id ORDER BY Form.Name; You'll have to substitute your actual table and column names, of course. By the way, it appears that you have redundant data in your forms table. Normally, you'd make a forms table with one row per form, and a forms-to-procedures relation table with just the procedure and form keys. Is this a clear enough explanation for folks? I'm willing to RTFM if somebody will point me to the chapter(s) that discuss different join syntax and how it's used in MySQL. I'm using 3.23.54 in my production environment at the moment and my tables are MyISAM. http://dev.mysql.com/doc/mysql/en/SELECT.html http://dev.mysql.com/doc/mysql/en/JOIN.html Thanks in Advance = Robert Reed Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: selecting by average
Gerald Taylor wrote: I have a table called ratings. It has 3 rows of interest id foreign key to another table quala quality rating u the user who gave that rating (also a foreign key into the users table) Now I would like to select all the unique ids for which the average quality is below 1.5 so if we have id qual u 1 5 999 1 4 888 2 1 999 2 1 888 3 3 777 3 2 888 it would tell me that id 2 has average ratings below 1.5 it doesn't like SELECT id from ratings where AVG(qual) 1.5 group by id unfortch this server is still 3.23 The WHERE clause is used to determine which rows to look at in order to calculate the value of the aggregate function (AVG in this case), so you can't use the result of the calculation there. The HAVING clause, on the other hand, is used to filter rows near the end, after the calculations have been done. So, you need something like: SELECT id FROM ratings GROUP BY id HAVING AVG(qual) 1.5 If you want to actually see the averages, you'd change this to something like: SELECT id, AVG(qual) AS average FROM ratings GROUP BY id HAVING average 1.5 Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
