RE: how do i encrypt the .frm file with a password
if the data is the concern, not the data structure, why not encrypt the data itself? -Original Message- From: David Crane [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 11, 2004 2:15 PM To: [EMAIL PROTECTED] Subject: Re: how do i encrypt the .frm file with a password Since this database will be distributed, our users will be administrators. If they are able to copy the files to another computer and set up mysql themselves, they could get access to the entire database directly and export any or all data. Configuring the service to run as a different user would not prevent this. And, they would have access to the files as administrator. As I see it, the passwords for users are only used by the service itself and passwords are not applied to the files themselves. As for operating systems, we are going to support Windows NT, 2000, XP. David Crane [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] I need to provide some security to a database that I am working on. This database will be distributed and I need to prevent users from being able to simply copy the files and being able to have complete access to it. I want to do this: Encrypt the `.frm' file with a password. This option doesn't do anything in the standard MySQL version. (http://dev.mysql.com/doc/mysql/en/CREATE_TABLE.html) I have recompiled mysql to enable 64 indexes on a table. So, recompiling it is not a problem. Do I need a custom version or MaxDB? -- 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: Hierarchical data design
One of the ways around this that I've seen is to maintain an additional field for the 'full path' info. so that you have: table_name --- node_id integer auto_increment node_name varchar(50) not null parent_id integer full_path varchar(255) the obvious downside is that your application needs to maintain this info. the other option is to parse your info from the URL, tokenizing per '/' character, then do look ups recursively for each entry, finding the node you're looking for. -Original Message- From: Justin French [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 16, 2004 8:39 AM To: MySQL Subject: Hierarchical data design Hi all, I've been playing around with the concepts mentioned in this article: http://www.sitepoint.com/article/hierarchical-data-database/ (Short summary: Using Modified Preorder Tree Traversal, resulting in left and right values for each tree node to describe the tree structure) With all this in mind, I'm hoping to emulate a folders and pages hierarchical structure for a CMS, without relying on the file system at all. Here's where I get stuck: In a simple tree, one can easily see that using the title of a node as it's primary key is not smart... names can easily collide: Root Products ProductOne About FAQ Support ProductTwo About FAQ Support Services About As the writer of the article suggests, numeric IDs are the way to go. However, I want to call the tree via the URL with name-based ID's (eg /products/product-one/about/) rather than numeric IDs (eg /2/17/44/). A further complication is that this data design would allow two nodes in the same parent node to have the same title, since the numeric key is the ID, rather than the title. When we look at a traditional file system, it's based on unique keys AT EACH TREE LEVEL, not unique keys for the entire tree. As such, I don't think the above data model is right for this application. The only catch is I have no idea where to look next. Hours of Googling has returned very little. Any hints on where to look next would be great. --- Justin French http://indent.com.au -- 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: any select statement like uniq in unix
you can do something like: select phone_number, count(1) from your_table_name_here group by phone_number -Original Message- From: Jan Blomqvist [mailto:[EMAIL PROTECTED] Sent: Monday, March 15, 2004 8:27 AM To: [EMAIL PROTECTED] Subject: any select statement like uniq in unix Hi! Is there a select statement , wich works like the command uniq in unix, if I shall list a table with a column wich for example is telephonenumber and the same number occurs like 1000 times, and I wan,t i present just once and also the count how many times it occurs in the table. /Jan Blomqvist -- 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: How to determine when a MySQL database was last modified?
I'm not 100% sure on this, but what about the .myd file timestamp? -Original Message- From: gerald_clark [mailto:[EMAIL PROTECTED] Sent: Friday, February 06, 2004 9:09 AM To: Phil Cc: [EMAIL PROTECTED] Subject: Re: How to determine when a MySQL database was last modified? Add a timestamp field to each table. Phil wrote: Hi, I have many smallish, discrete MySQL databases, each of which I would like to backup individually (mysqldump seems fine for this). However, there's no point re-backing up a database that has not changed since the last time it was backed up. So how can I tell if when a MySQL database was last modified, so that I can decide whether to run mysqldump on it again or not? Any help with this would be much appreciated. Thanks, Phil -- 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: SQL and productivity
From what I've read ( I think in the MySQL docs, might have been here in the list), technically it will take less time to add the indexes after the table creation, than the overhead of index updating per-insert. Either way, it's gonna take a loong time. -Original Message- From: Peter J Milanese [mailto:[EMAIL PROTECTED] Sent: Friday, January 30, 2004 9:15 AM To: Krasimir_Slaveykov Cc: [EMAIL PROTECTED] Subject: Re: SQL and productivity I'd start with the indexes in place. 5+mil records will take quite some time to index after the fact. P -Krasimir_Slaveykov [EMAIL PROTECTED] wrote: - To: [EMAIL PROTECTED] From: Krasimir_Slaveykov [EMAIL PROTECTED] Date: 01/30/2004 09:14AM Subject: SQL and productivity Hello , I must create a temp table with 5 or more million records. I need it for creating some reports, so I need to create indexes too. My question is: When total time for work will be smaller? : 1-st case: I create table and create indexes immediately before filling data. 2-nd case: I create table, filling data and then create indexes? -- Best regards, Krasimir_Slaveykov mailto: [EMAIL PROTECTED] [EMAIL PROTECTED] |-| |/ * * *** * ** /| | *** *** *** *** *** *** ** *** //| |/// *** *** * * *** *** * *** ///| |// *** *** *** *** *** *** ** *** *** | |/ *** *** *** * *** /| |--- www.office1.bg --| -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Paging!
another option is to set your pager option: mysql pager more then re-run your query -Original Message- From: Mike Johnson [mailto:[EMAIL PROTECTED] Sent: Thursday, January 29, 2004 11:01 AM To: Adel Ardalan; [EMAIL PROTECTED] Subject: RE: Paging! From: Adel Ardalan [mailto:[EMAIL PROTECTED] When I select a rather large table to be shown, the rows are going very fast and I can't see the rows at the beginning. Also, I can't scroll up. How can I view the results page by page? I can only assume you're talking about the native mysql client. In that case, there are a few suggestions to help you. If possible, first weed out rows you don't need with a WHERE clause. If there's still too much data, add a `LIMIT offset,count` clause to the end of the query. SELECT * FROM table LIMIT 0,30; SELECT * FROM table LIMIT 30,30; SELECT * FROM table LIMIT 60,30; etc Or, if possible, increase the scrollback buffer for your shell client (if you're shelled in, that is, and not on the server directly). As it stands, though, the native mysql client really isn't meant to be used to view such large amounts of data. Are you using any sort of frontend with this database? PHP, Perl, Java, etc? -- Mike Johnson Web Developer Smarter Living, Inc. phone (617) 886-5539 -- 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: does mysqldump take care of stored procedures?
Stored procedure versioning/backup/restoring has always been a pain in the butt for all dbs that support them. What is done 9/10 times is the sql script that creates them is stored, versioned, and used for backup... now a 'show create stored procedure blah_blah' function would be nice... ;) -Original Message- From: Mikhail Entaltsev [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 21, 2004 10:49 AM To: Bing Du; [EMAIL PROTECTED] Subject: Re: does mysqldump take care of stored procedures? Hi, since MySQL stores stored procs in mysql db you need to make backup of mysql db. But it would be nice to have a possibility to make backup of stored procs in readable format. Best regards, Mikhail. - Original Message - From: Bing Du [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, January 21, 2004 4:42 PM Subject: does mysqldump take care of stored procedures? I'm testing backup and restore on MySQL 5.0. I did mysqldump first and then dropped a table and a stored procedure on purpose. After doing 'mysql -h host -u user -p database dump-file', the dropped table could be restored back in the database. But the dropped procedure stayed missing. How should I do to get the stored procedures backed up as well? Any suggestions/ideas appreciated. Bing -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MIN with negative numbers in VARCHAR
Could you CAST them first, then apply MIN? -Original Message- From: Eldon Ziegler [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 13, 2004 9:56 AM To: [EMAIL PROTECTED] Subject: MIN with negative numbers in VARCHAR We have a table with floating point measurement values stored in VARCHAR's. How can I get MIN in SELECT to evaluate these as numbers instead of strings so negative value sort correctly? Right now I've kludged it as MIN(SIGN(Value) * ABS(Value)). Is there a more straight forward way? -- 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: MIN with negative numbers in VARCHAR
ok... you might have two options: 1- (don't know if this will work) do a min(cast(Value * 100 as signed integer) / 100 2- or min(Value + 0.0) and see what happens. -Original Message- From: Eldon Ziegler [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 13, 2004 11:24 AM To: [EMAIL PROTECTED] Subject: RE: MIN with negative numbers in VARCHAR DOUBLE doesn't seem to be an option with CAST At 10:31 am 1/13/2004, you wrote: Could you CAST them first, then apply MIN? -Original Message- From: Eldon Ziegler [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 13, 2004 9:56 AM To: [EMAIL PROTECTED] Subject: MIN with negative numbers in VARCHAR We have a table with floating point measurement values stored in VARCHAR's. How can I get MIN in SELECT to evaluate these as numbers instead of strings so negative value sort correctly? Right now I've kludged it as MIN(SIGN(Value) * ABS(Value)). Is there a more straight forward way? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Bet the Business
I've found, in my many years of app dev work with Oracle, MSSQL, and now a little MySQL, that stored procedures are a very mixed blessing. Depending on your application architecture, they be just what you want, a means of encapsulating a complicated, data-centric function. Database stored procedures are notiriously difficult to manage in terms of version management, mostly due to the advance of excellent GUIs for editing stored procedures directly in the DB (TOAD). The other major drawback is that unless your entire application in based in the database (e.g. Oracle's web toolkit) then it adds another language to your application, as well as another location for code. This obviously increases maintainance time/cost, which is acceptable sometimes, but not others. I am definately _for_ stored procedures. Especially in MySQL -- between SP and subqueries, most of the limiting features of MySQL are going away, and Oracle and MSSQL folks will have less and less ground from which to point fingers. Just remember that they are not a magic bullet, and try not to mix too much of your application and/or business logic directly in them. [stepping down from soap box] -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 06, 2004 10:01 AM To: [EMAIL PROTECTED] Subject: Re: Bet the Business Quoting robert_rowe [EMAIL PROTECTED]: I'm lead developer for a company that writes custom software for the mining industry. We support MSSQL and MySQL. I've found that from a programming aspect (VB + ADO) there is relatively little difference between MSSQL and MySQL. There is some sight syntax differences and MySQL versions 5.0 do not support stored procedures. We use the InnoDB table type for MySQL as it provides row level locking and transactions. Our largest client has about 1 gig of data and averages 125 users. I've found that MySQL usua lly out performs MSSQL if you tune it properly and use good programming techniques. It is less integrated with Microsoft products though so if your clients will be accessing the data via MS Office applications then MSSQL will seem easier. We offer both platforms mainly because a lot of IT managers are convinced that Microsoft solutions are the best even when benchmarks say different. I admit to dome degree I am one of the IT Managers - the it 'sounds to good to be true' syndrome I suppose. But I'm coming around. The decision will be for MSSQL Server due to us using other MS products and the supporting of one product, but I'm interested for future reference when it does become an option (probably other jobs). As an aside, stored procedures seem to be a big thing with some people, namely the MS people I encounter (the ASP.NET mantra of using stored procedures for all databases access and even processing tasks), yet people seem to get along with them fine, until recently, in MySQL. This makes me thing they may not be the holy grail people say they are...in MySQL, until recently, all SQL must have been done at the code level rather than at the database server level - is that a major issue? Does it even provide some advantages? -- 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]
XML Datatype for MySQL?
I poked around the docs and mailing list for a bit, and didn't see any references to ideas/future of this? I'm not looking so much just to store xml in MySQL, obviously I can just use a text column for that. I was more thinking along the lines of eXist and dbXML and the XML:DB initiative in particular. Being able to effectively query the content of the XML document, relatating it to other standard column types. Other possibilities is to name an xml schema during column creation, to force validation of the incoming data. Specialized indexes would be needed, as likely you would need to not just index the column, but an xml path (e.g. XPath) within the data itself. Then of course, updating part of the data based on paths you get the idea Anyway, I was wondering if MySQL or the community was thinking/doing anything like this... I would volunteer to help, but as my strong suit is Java, I don't think I'd be too much help... ;) Thanks, Dan Greene
RE: Sleeping Processes
Is it possible that some portions of your code do not disconnect thier db connections when they are done? I've seen issues like this with platforms that do not use db pool managers to ensure release of db connections when they are idle for a while. Particularly if there are error scenerios where the normal connection closure may be skipped... My $0.02... Dan Greene -Original Message- From: Mike Morton [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 06, 2004 2:15 PM To: [EMAIL PROTECTED] Subject: Sleeping Processes I have a mysql database that is running in conjunction with a PHP website, and I am experiencing some significant slowdowns in times of moderate traffic. Now, by moderate, I mean moderate for them, but fairly light traffic in terms of actual visitors. The library the is causing the slowdowns simple updates a 'last_active' variable in a sessions table in mysql which normally has no affect on loading speed at all, but what I am noticing during this high traffic period, is anywhere from 50 - 60 processes that are 'sleeping' in the database when I do a show processlist. E.g. +++---++-+--+- -+ -- + | Id | User | Host | db | Command | Time | State| Info | +++---++-+--+- -+ -- + | 680849 | tienda | localhost | tienda | Sleep | 22 | | NULL | | 680859 | tienda | localhost | tienda | Sleep | 154 | | NULL | | 680878 | tienda | localhost | tienda | Sleep | 101 | | NULL | | 680942 | tienda | localhost | tienda | Sleep | 96 | | NULL | But upwards of as I said, 50 - 60 of them at any given time. I do not have a slow day comparison to offer, but I have done many a sight and never seen sleeping processes like this. The question is, could these be affecting the slowness of the database, as I have determined that it is the database that is slowing down the site. The largest table holds only 42K rows, and the most accessed table is indexed with the most accessed fields and holds only 16K rows, so there should be no problem there. The max connections is set to 300, and the version is 3.23.47 The PHP version is 4.2.3 and I am NOT using mysql_pconnect but rather mysql_connect: [EMAIL PROTECTED](localhost,blocked,blocked); @mysql_select_db(tienda); Because it is a shared box, I do not have access to make a lot of changes, but the company is very co-operative and I am sure would be helpful for making small setting changes, as long as we are not talking major version updates, as they do have other clients using the server. I cannot think of anything else that I can pass on, if anyone has any suggestions or ideas I would appreciate it as I am plumb out of them! :) TIA -- Cheers Mike Morton * * Tel: 905-465-1263 * Email: [EMAIL PROTECTED] * Indeed, it would not be an exaggeration to describe the history of the computer industry for the past decade as a massive effort to keep up with Apple. - Byte Magazine Given infinite time, 100 monkeys could type out the complete works of Shakespeare. Win 98 source code? Eight monkeys, five minutes. -- NullGrey -- 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: sub select equivalent
left joins are your key select * from a left join b on id = tbl_id and b.tbl = a where b.tbl_id is null (not 100% sure on my syntax, but note the join, and the limitation on 'left' result set is specified within the left join clause) -Original Message- From: Dean A. Hoover [mailto:[EMAIL PROTECTED] Sent: Friday, December 12, 2003 2:04 PM To: [EMAIL PROTECTED] Subject: sub select equivalent I am using version 3.23.58 and need to do the following: select * from a where id not in (select tbl_id from b where tbl=a); Given that my version does not support sub selects, how can I re-write the statement to get the desired results? Thanks. Dean Hoover -- 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: How to READ/WRITE directly on MyISAM data files ?
Without a unique identifier, the only way you're gonna get to update right is to use other data in the row to identifiy the record... so with a table structure of name address state your users can select anything they want, filtering w/ a where clause. If you grant them update on the table, then they'll just have to: update your_table set name = 'Dan' where name = 'Daniel' and address = '55 main' and state = 'Virginia'; I don't agree with this approach, mind you, it breaks all kinds of best practices maybe it would help if you explained _why_ you don't want your users to see a numerical id field for each record -Original Message- From: Stéphane Bischoff [mailto:[EMAIL PROTECTED] Sent: Thursday, December 11, 2003 8:43 AM To: 'Jay Blanchard'; MySQL (E-mail) Subject: RE: How to READ/WRITE directly on MyISAM data files ? Thank you, but I already know the basics of SQL SELECT statements. What I am trying to say is, if a User writes a SELECT clause, I do not want him to see the RowID field. I do not want him to write a long SELECT statement, especially if my table has 20 FIELDS or more. (Can you imagine the user writing these queries all the time). I want him to be able to write SELECT * FROM ATABLE. Thank you, Stéphane. -Original Message- From: Jay Blanchard [mailto:[EMAIL PROTECTED] Sent: 11 décembre, 2003 08:41 To: Stéphane Bischoff; Martijn Tonies; MySQL (E-mail) Subject: RE: How to READ/WRITE directly on MyISAM data files ? [snip] So my first question should be : Is there a way to hide a field ?? [/snip] SELECT only the information you want. Let's say I have RowID Name Address City And I only want Name Address and City SELECT Name, Address, City FROM table WHERE RowID = 'foo' UPDATE table SET Name = 'foo' WHERE RowID = '12' etcetera A good book on SQL basics will get you a long way on things like this. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to READ/WRITE directly on MyISAM data files ?
So if I follow you, you allow your clients to execute direct sql on the database, both select and update, but _they_ simply don't want to see the row id data? If that's the case, then too bad for them if they are capable of writing sql, then they have to handle the result, or omit the field from the query. The only other option is for you to truss up your application interface, so that you give them an interface that shows just the fields _you_ want them to see. They can't have their cake and eat it too. -Original Message- From: Stéphane Bischoff [mailto:[EMAIL PROTECTED] Sent: Thursday, December 11, 2003 9:00 AM To: 'Jay Blanchard'; MySQL (E-mail) Subject: RE: How to READ/WRITE directly on MyISAM data files ? Hello, Here's an example : I have a table named Product. TABLE : PRODUCT +-+-+-+-+- + |RowID | Name| Company| Price | Warranty | | | | | | | +-+-+-+-+- + | 1 | PC 1000 | MyCom Inc. | 1000.00| 1 year| | 2 | PC 1000 | MyCom Inc. | 1200.00| 2 year| | 3 | PC 1000 | MyCom Inc. | 1300.00| 3 year| | 4 | PC 2000 | MyCom Inc. | 1200.00| 1 year| | 4 | PC 2000 | MyCom Inc. | 1300.00| 2 year| | 4 | PC 2000 | MyCom Inc. | 1400.00| 3 year| | 4 | PC 3000 | MyCom Inc. | 1500.00| 1 year| | 4 | PC 3000 | MyCom Inc. | 1600.00| 2 year| | 4 | PC 3000 | MyCom Inc. | 1700.00| 3 year| | 4 | PC AR3| SPCom Inc. | 1200.00| 2 year| | 4 | PC AR3| SPCom Inc. | 1300.00| 3 year| | 4 | PC AR4| SPCom Inc. | 1400.00| 4 year| +-+-+-+-+- + From My Server Side Application (C code) I can Update my Rows using my RowID. Example : UPDATE TABLE product SET Price=2000.00 WHERE RowID=3; But For My Client Side Applications : User logs in my Client app. User types in SELECT * FROM product. User does NOT WANT TO SEE RowID numbers. User wants to see this output : +-+-+-+-+ | Name| Company| Price | Warranty | | | | | | +-+-+-+-+ | PC 1000 | MyCom Inc. | 1000.00| 1 year| | PC 1000 | MyCom Inc. | 1200.00| 2 year| | PC 1000 | MyCom Inc. | 1300.00| 3 year| | PC 2000 | MyCom Inc. | 1200.00| 1 year| | PC 2000 | MyCom Inc. | 1300.00| 2 year| | PC 2000 | MyCom Inc. | 1400.00| 3 year| | PC 3000 | MyCom Inc. | 1500.00| 1 year| | PC 3000 | MyCom Inc. | 1600.00| 2 year| | PC 3000 | MyCom Inc. | 1700.00| 3 year| | PC AR3| SPCom Inc. | 1200.00| 2 year| | PC AR3| SPCom Inc. | 1300.00| 3 year| | PC AR4| SPCom Inc. | 1400.00| 4 year| +-+-+-+-+ I know there are ways to bypass this problem, but it involves much more coding in my Delphi applications on my Client side. So my question is, Is there a way to hide a field from select statements. Of course, if my user wrote SELECT RowId, Name, Company, Price, Warranty FROM product he would get the RowId in his query output. Best Regards, Stéphane. -Original Message- From: Jay Blanchard [mailto:[EMAIL PROTECTED] Sent: 11 décembre, 2003 08:49 To: Stéphane Bischoff; MySQL (E-mail) Subject: RE: How to READ/WRITE directly on MyISAM data files ? [snip] Thank you, but I already know the basics of SQL SELECT statements. What I am trying to say is, if a User writes a SELECT clause, I do not want him to see the RowID field. I do not want him to write a long SELECT statement, especially if my table has 20 FIELDS or more. (Can you imagine the user writing these queries all the time). I want him to be able to write SELECT * FROM ATABLE. [/snip] Well, that pretty much misses the point then, doesn't it? Are your records not unique? There is no way that you can write an update statement that would perform the operation on the proper record? Can you show us a bit of the table? With more information we can help. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:
RE: using (hard?) links for tables
What if you were to make a 3rd database, containing shared elements, such as your user
table (I presume we're not talking the MySQL system user table) and then have
necessary permissions granted between your other database users to read that table
jointly, as you can query cross databases...
-Original Message-
From: Moritz von Schweinitz [mailto:[EMAIL PROTECTED]
Sent: Thursday, December 11, 2003 9:26 AM
To: [EMAIL PROTECTED]
Subject: using (hard?) links for tables
hi there!
i have two databases on the same server, and one of the
tables ('users')
should be the same in both databases. since this is very specific to
this one server, and other servers running similar databases
don't need
that functionality, i'm looking for the easiest way to do
this, so i was
wondering whether mysql is smart enough not to couse any
mayor mess if i
simply replace the users.* file in one of the datbase's
directory with
hardlinks to the other databses files for that table.
anyone know whether this works, and if not, what would be the easiest
way of keeping two tables in differnt databases synced? a
cronjob, maybe
(it's not THAT time-critiva).
thanks,
M.
--
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: SQL Tutorial Trouble in MySQL
you can use the concat function: http://www.mysql.com/doc/en/String_functions.html SELECT concat(vend_city,', ',vend_state,' ',vend_zip) FROM Vendors ORDER BY vend_name; -Original Message- From: Gilbert Wilson [mailto:[EMAIL PROTECTED] Sent: Wednesday, December 10, 2003 2:42 PM To: [EMAIL PROTECTED] Subject: SQL Tutorial Trouble in MySQL Hi all, I am following the lessons in Sams Teach Yourself SQL in 10 Minutes using MySQL as the databse app. I am having some trouble with Lesson 7: Creating Calculated Fields where you are suppose to concatenate several fields. The Input looks like this: SELECT vend_city+', '+vend_state+' '+vend_zip FROM Vendors ORDER BY vend_name; The output should look like this: --- Anytown,OH44333 Bear Town ,MI4 Dollsville ,CA9 New York ,NY1 London , N16 6PS Paris , 45678 The output that I get is this: +-+ | vend_city+', '+vend_state+', '+vend_zip | +-+ | 44333 | | 4 | | 9 | |NULL | | 1 | |NULL | +-+ According to the text: The solution is to concatenate the three columns. In SQL SELECT statements, you can concatenate columns using a special operator. Depending on what DBMS you are using, this can be a plus sign (+) or two pipes (||). Neither seem to work. Can anyone point me in the right direction? Thanks. Gilbert Wilson -- 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: Bug in WinMySQLadmin 1.4
Having a multi-display system myself, I disagree with this being a bug. It's more of a lack of a feature, being multi-display aware Also, some multi-display software (matrox) tricks windows into it thinking it's one display with a _very_ weird pixel width (2048 x 768), skipping over window's internal multi-display support. My $0.02, Dan Greene -Original Message- From: Ray Ragan [mailto:[EMAIL PROTECTED] Sent: Tuesday, December 09, 2003 3:10 PM To: [EMAIL PROTECTED] Subject: Bug in WinMySQLadmin 1.4 My SQL Team, When launching WinMySQLadmin 1.4 on multi-displayed systems, the admin application launches center between both displays, not display 1, as it should. Thanks, Ray -- 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: LOAD DATA LOCAL INFILE
If it's an option, I would run your datafile through a processor (sed on unix, ultraedit on windows) to search and replace the string with \ and try it with fields terminated by ',' optionally enclosed by '' as mentioned by Mike Johnson's posting (escaped by '\' is default) -Original Message- From: Daniel Kiss [mailto:[EMAIL PROTECTED] Sent: Tuesday, December 09, 2003 3:02 PM To: [EMAIL PROTECTED] Subject: LOAD DATA LOCAL INFILE Hi all, How should I set the parameters of the LOAD DATA LOCAL INFILE command for a comma separated text file like this: Text field,.4,123 Text field with included quote,,45 Text field with , a comma between quotes,1.2,44 Thanks, Dan -- 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: Tree-like structure: make it simply
how about recreating the table w/o the autoincrement, then reload the data, then alter
table to reimplement the autoincrement?
-Original Message-
From: Matthew [mailto:[EMAIL PROTECTED]
Sent: Thursday, December 04, 2003 7:12 PM
To: Alex E.Wintermann; [EMAIL PROTECTED]
Subject: Re: Tree-like structure: make it simply
I think I can help with questions 1 and 3... see below,
- Original Message -
From: Alex E.Wintermann [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Thursday, December 04, 2003 11:44 PM
Subject: Tree-like structure: make it simply
Hello mysql,
I have some table:
/* BEGIN DUMP */
CREATE TABLE `sp_tovar_vid` (
`id` int(11) NOT NULL auto_increment,
`id_tovar_vid` int(11) NOT NULL default '0',
`name` varchar(100) NOT NULL default '',
`description` varchar(255) NOT NULL default '',
UNIQUE KEY `id` (`id`)
);
INSERT INTO `sp_tovar_vid` VALUES (0, 0, 'root', 'root category');
INSERT INTO `sp_tovar_vid` VALUES (7, 0, 'cat.1', '');
INSERT INTO `sp_tovar_vid` VALUES (8, 0, 'cat.2', '');
INSERT INTO `sp_tovar_vid` VALUES (9, 0, 'cat.3', '');
INSERT INTO `sp_tovar_vid` VALUES (10, 0, 'cat.4', '');
INSERT INTO `sp_tovar_vid` VALUES (11, 0, 'cat.5', '');
INSERT INTO `sp_tovar_vid` VALUES (12, 7, 'subcat 1', '(to cat.1)');
INSERT INTO `sp_tovar_vid` VALUES (13, 7, 'subcat 2', '(to cat.1)');
INSERT INTO `sp_tovar_vid` VALUES (14, 7, 'subcat 3', '(to cat.1)');
INSERT INTO `sp_tovar_vid` VALUES (15, 9, 'subcat 1', '(to cat.3)');
INSERT INTO `sp_tovar_vid` VALUES (16, 9, 'subcat 2', '(to cat.3)');
INSERT INTO `sp_tovar_vid` VALUES (17, 9, 'subcat 3', '(to cat.3)');
INSERT INTO `sp_tovar_vid` VALUES (18, 13, 'subsubcat 1',
'(to subcat.2
to cat.1)');
/* END DUMP /
_QUESTION 1_: in what way should i run query to restore table with
`id`' like in dump? (`id` is auto_increment)
either omit the `id` field when re-loading the data, e.g.
INSERT INTO `sp_tovar_vid`
(`id_tovar_vid`,`name`,`description`) VALUES (0,
'root', 'root category');
or, replace the `id` values with an empty string when
re-loading the table
data, e.g.
INSERT INTO `sp_tovar_vid` VALUES ('', 0, 'cat.1', '');
both techniques prompt mysql to reassign the `id` values.
note that I do not think it is wise/possible to run a query
to restore (or
clean up) the auto_increment values without re-loading the table data.
_QUESTION 2_: how to display table with columns=
('parent_category.name' ,'category.name')?
i tried this:
/***/
SELECT t1.`name`,t2.`name` FROM `sp_tovar_vid` AS t1, `sp_tovar_vid`
AS t2 WHERE t1.`id`=t2.`id_tovar_vid` ORDER BY t1.`name`,t2.`name`;
/***/
How to make it more simply?
_QUESTION 3_: how to display string:
subsubcat 1 - subcat.2 - cat.1
if we have only `id`=18 ?
use SELECT IF(id=18, true_expression, false expression) FROM table...
I'm not clear what you mean by subsubcat 1 - subcat.2 -
cat.1... minus, or
some sort of string concatenation?
true expression might be field1 - field2, or CONCAT(field1,
field2, ...)
--
Best regards,
Alex 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]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: unixtime update syntax
The time zone matters... your results are exactly 8 hours off... PST is gmt -8. So it
looks like the from_unixtime function is converting to what the time was locally at
that moment in GMT. Not what I would have expected either
What do you get when you run-
select unix_timestamp(urtime) from t_test;
-Original Message-
From: Ron McKeever [mailto:[EMAIL PROTECTED]
Sent: Tuesday, December 02, 2003 10:58 AM
To: [EMAIL PROTECTED]
Subject: unixtime update syntax
Anyone have a response to the following:
Hi
I have a db that gets data dumped into it. One of the columns
gets unix
timestamp data utime.
I what to covert that into a datetime column so I can utlize
indexes and
such.
But I still what the unixtime to remain.
I know I can get the data I want with php or mysql to convert
it, but I need
both columns for this.
One with the unixtime, and one with it converted.
I believe I have a good way to do this but I'm not sure why it's not
converting the date right ( see at bottom ):
mysql desc t_test;
++-+--+-+-+---+
| Field | Type| Null | Key | Default | Extra |
++-+--+-+-+---+
| a | int(11) | | PRI | 0 | |
| b | varchar(10) | YES | | NULL| |
| utime | varchar(10) | YES | | NULL| |
| urtime | datetime| YES | | NULL| |
the data being inserted:
insert into t_test (a,b,utime) values ('1','test','1070296560');
insert into t_test (a,b,utime) values ('2','test','1070292960');
Monday, December 1st 2003, 16:36:00 (GMT) = 1070296560
Monday, December 1st 2003, 15:36:00 (GMT) = 1070292960
mysql select * from t_test;
+---+--+++
| a | b| utime | urtime |
+---+--+++
| 1 | test | 1070296560 | NULL |
| 2 | test | 1070292960 | NULL |
+---+--+++
2 rows in set (0.00 sec)
Syntax I'm using to update the datetime column from the
varchar columnis:
mysql UPDATE t_test SET urtime = FROM_UNIXTIME(utime) WHERE
urtime is NULL;
mysql select * from t_test;
+---+--++-+
| a | b| utime | urtime |
+---+--++-+
| 1 | test | 1070296560 | 2003-12-01 08:36:00 |
| 2 | test | 1070292960 | 2003-12-01 07:36:00 |
+---+--++-+
2 rows in set (0.01 sec)
the urtime I thought should read:
2003-12-01 16:36:00
2003-12-01 15:36:00
Does it matter if im on the PST if I get the data from GMT???
Help
Ron
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysql remote access on linux
from what I've read in the docs, if you use 'grant' you don't have to flush, but if you insert into user tables directly, you do -Original Message- From: Skippy [mailto:[EMAIL PROTECTED] Sent: Tuesday, December 02, 2003 11:56 AM To: [EMAIL PROTECTED] Subject: Re: mysql remote access on linux On Tue, 2 Dec 2003 10:17:24 -0500 [EMAIL PROTECTED] wrote: You should not need to restart , you will need to 'flush privileges' though. has that been done? I was under the impression that the latest versions don't even need 'flush privileges' anymore, that any modifications to the mysql database is taken into account immediately. -- Skippy - Romanian Web Developers - http://ROWD.ORG -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Export in XML
I think that to answer Todd's question, no there is no inherent way in MySQL to export results as XML the way that sqlserver and oracle do. Another solution that comes to mind (if you're using java) is to write a simple class that converts a result set to an array of hashmaps (row in array is row of data, hashmap for column name - data value mapping), then send that to either castor (pre-java 1.4) or to the xml- object APIs (1.4 on). -Original Message- From: Todd Cary [mailto:[EMAIL PROTECTED] Sent: Monday, December 01, 2003 9:48 AM To: Jay Blanchard Cc: [EMAIL PROTECTED] Subject: Re: Export in XML Jay - I am not an expert with XML, however I use it with Delphi and Delphi's TClientDataset. In this usage, I use the builtin SaveTo and LoadFrom methods. Todd Jay Blanchard wrote: [snip] Is there a way to export/import MySQL tables in XML format? [/snip] Well Todd, it is a little more complex than this. How much do you know about XML? -- -- 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: Export in XML
The most guarenteed way to have a feature in a product pointed out is to make a public statement that it's not available :) keyboard in mouth, Dan Greene -Original Message- From: John Griffin [mailto:[EMAIL PROTECTED] Sent: Monday, December 01, 2003 10:49 AM To: Dan Greene; Todd Cary; Jay Blanchard Cc: [EMAIL PROTECTED] Subject: RE: Export in XML Look at http://www.mysql.com/doc/en/mysql.html. The -X option may give you what you need. -Original Message- From: Dan Greene [mailto:[EMAIL PROTECTED] Sent: Monday, December 01, 2003 9:57 AM To: Todd Cary; Jay Blanchard Cc: [EMAIL PROTECTED] Subject: RE: Export in XML I think that to answer Todd's question, no there is no inherent way in MySQL to export results as XML the way that sqlserver and oracle do. Another solution that comes to mind (if you're using java) is to write a simple class that converts a result set to an array of hashmaps (row in array is row of data, hashmap for column name - data value mapping), then send that to either castor (pre-java 1.4) or to the xml- object APIs (1.4 on). -Original Message- From: Todd Cary [mailto:[EMAIL PROTECTED] Sent: Monday, December 01, 2003 9:48 AM To: Jay Blanchard Cc: [EMAIL PROTECTED] Subject: Re: Export in XML Jay - I am not an expert with XML, however I use it with Delphi and Delphi's TClientDataset. In this usage, I use the builtin SaveTo and LoadFrom methods. Todd Jay Blanchard wrote: [snip] Is there a way to export/import MySQL tables in XML format? [/snip] Well Todd, it is a little more complex than this. How much do you know about XML? -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Searching for a realtime progress bar that uses mySQL and Flash?
yo yo yo,
another phat option is to create another page on your system that returns the %
number, and using flash, call that url and parse the results, and update your flash
chart accordingly. We've done similar things to have flash talk to our systems (set
up web pages that return xml rather than html, then the flash works off the xml).
Think of it as web service eye for the lazy guy... :)
-Original Message-
From: TheMechE [mailto:[EMAIL PROTECTED]
Sent: Tuesday, November 25, 2003 10:24 AM
To: Tom Horstmann; [EMAIL PROTECTED]
Subject: RE: Searching for a realtime progress bar that uses mySQL and
Flash?
Ok here is the soluction. Feel me, yall, this is the winner
of the prize.
You see, it is an uncommon operation, but you CAN have a JavaApplet
call a method on your window object. By having the java applet gain
access to its window object, you can drill down into the
if(window.methodName==true){
window.methodName(); // then call
}
THUS... you have an invisible java applet on the page
tunneling on an http
port to get
the data dynamically, totally secure because its hitting an http port.
(Well, no less secure than anything else coming out of a
browser. 'wink')
THEN,
you have a simple table on the page ( and this can have
several methods of
display ) you can
have a table with 100 1pix cells that you change the bgcolor on. Or A
string of graphics that
you toggle their source, it doesn't matter, the point is that
the DISPLAY is
controlled by
the javascript method
function update(myPercentageComplete){Change display here
based on imput of
it}
... which is CALLED by the applet. But you don't use the applet
to actually hold anything in the HTML, ( it exists in a blank
cell at the
bottom of the page. )
heh. Dig it. yup yup.
-Original Message-
From: Tom Horstmann [mailto:[EMAIL PROTECTED]
Sent: Tuesday, November 25, 2003 4:26 AM
To: [EMAIL PROTECTED]
Subject: RE: Searching for a realtime progress bar that uses mySQL and
Flash?
I disagree, you can restrict access to SELECT only
Sure, but no access is much more secure than restricted access.
and plus
any normal form
on a web page has access to a DB in much more insecure ways
(SQL injection,
etc.),
What you consider insecure should not been written directly to
a database. The form is processed by a server-side executable
which should check incoming data before writing.
and as I said, it must be a 'real-time' progress meter without
refreshing the .php/.html page.
Javascript is not able to query the DB.
Of course it is not:) As Flash is not. Nothing client-side is able
to without something server-side. But it is able to connect to a
server and therefore able to load data from there. Only makes
sense with http-connections - what result in some kind of refresh -
but refreshing the whole page is not needed.
Considering Flash you should have a look at
http://www.macromedia.com/support/flash/action_scripts/actions
cript_dictiona
ry/actionscript_dictionary425.html
Regards,
TomH
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: My.cnf
don't forget to change the port number that the server is listening on if you plan on running them simultaneously -Original Message- From: Peter Sap [mailto:[EMAIL PROTECTED] Sent: Monday, November 24, 2003 4:59 PM To: [EMAIL PROTECTED] Subject: Re: My.cnf You could install version 3.23 under a different username (like mysql323) than the 4.0 version (like username mysql40). Then put each .my.cnf in the ~ directory. Regards, Peter Sap - Original Message - From: Arnoldus Th.J. Koeleman [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, November 24, 2003 10:33 PM Subject: My.cnf I wanna deploy two different Mysql versions 3.23 and 4.0 As far as I understand this is not possible am I correct?? Since both version have different needs in case of my.cnf -- 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: Suggestions on querying parent/child records
well... I'm not 100% sure of your table structure, but if the children are in same tables, then join to each: select * from action_items a, child_table b, child_table c where a.owner_id = b.person_id and a.creator_id = c.person_id now if the values in the action_items table could be null (not assigned yet), then you'll want to do an outer join: select * from action_items left join child_table as a on action_items.owner_id = a.person_id left join child_table as b on action_items.creator_id = b.person_id [not 100% sure on my left join syntax... double check with the docs, or the many posts to this list...] -Original Message- From: Brent Baisley [mailto:[EMAIL PROTECTED] Sent: Friday, November 21, 2003 3:06 PM To: [EMAIL PROTECTED] Subject: Suggestions on querying parent/child records I'm trying to figure out the best approach for doing this query. I have a list of action items, typically about 50, that I need to display. Each action item will have two related sets of child items (two different groups of people). So I need to query three main databases, a parent and two children. One obvious approach would be to query the action items and then run two queries for each action item to pull the two separate related child items. Then just join then in php for output. But that would always mean 2*actions+1 queries every time the page would be viewed. That's a lot of database connections. Another approach is to use a join query on the parent and one child. Then run a query for each action to pull the other child data. Again joining the data together in php for output. But this means a lot of redundant parent data due to the one to many join. I'm sure there would be a third approach to do it all in one query with lots of redundant data, but I haven't sat down with my brain yet (it's out to lunch) to talk about that approach. I'm thinking this one may be the best for performance even though a lot more data would need to be transfered. Any other ideas or suggestions on optimizing these approaches? Thanks. -- 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: piping blob into shell command (tar)
This may be simplistic, but is mysql putting any text before / after blob content, such as column name, '1 row processed OK', that may be 'corrupting' the tar data? using a very small tar file, and run your command, piping to more instead of tar to see if there is any extra text that mysql is adding in when it's not explictly running 'into dumpfile' -Original Message- From: Denis Mercier [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 19, 2003 1:55 PM To: [EMAIL PROTECTED] Subject: Re: piping blob into shell command (tar) On Wed, 2003-11-19 at 12:26, Paul DuBois wrote: At 11:03 -0500 11/19/03, Denis Mercier wrote: i also tried: use my_db; select * from my_table; so when i try shell mysql --pager test1 | tar x the tar file does not get written to /usr/local/test1 but i still dont get my untared files? write permissions are ok? what am i missing? Have you verified that the mysql command actually writes any output, for example, with this command: shell mysql --pager test1 | wc Do you get any output from tar at all, such as an error message? Maybe the output from mysql isn't suitable. Couple of things to try: The output from mysql seems fine because when it does write the value of my blob I get a well formatted tar file, I'm thinking that maybe tar cannot handle a stream as it's input? That may be. In that case, you should invoke tar as tar xf - I have tried tar xf but tar gives me an error, it wants a file specified. But I didn't say to use tar xf. I said to use tar xf -. That dash is highly significant. It means The file to read is the standard input. tried tar xf - mysql test1 | tar xf - tar: This does not look like a tar archive tar: Skipping to next header tar: Error exit delayed from previous errors I dont get it, if i do this command: mysql select * into dumpfile /usr/local/test1.tar from my_table I get a tar archive written to /usr/local/ I'm using mysql client to store my tar file as a blob, mysql insert into my_table values(LOAD_FILE(/usr/local/test1.tar)); could this function cause my problem? Thanks -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- 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: piping blob into shell command (tar)
ok... try this: mysql --skip-column-names test1 mytestoutput.tar tar xvf mytestoutput.tar and if it works, try cat mytestoutput.tar | tar xf - to see if it works -Original Message- From: Denis Mercier [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 19, 2003 2:41 PM To: [EMAIL PROTECTED] Subject: Re: piping blob into shell command (tar) On Wed, 2003-11-19 at 14:02, Paul DuBois wrote: At 13:55 -0500 11/19/03, Denis Mercier wrote: On Wed, 2003-11-19 at 12:26, Paul DuBois wrote: At 11:03 -0500 11/19/03, Denis Mercier wrote: i also tried: use my_db; select * from my_table; so when i try shell mysql --pager test1 | tar x the tar file does not get written to /usr/local/test1 but i still dont get my untared files? write permissions are ok? what am i missing? Have you verified that the mysql command actually writes any output, for example, with this command: shell mysql --pager test1 | wc Do you get any output from tar at all, such as an error message? Maybe the output from mysql isn't suitable. Couple of things to try: The output from mysql seems fine because when it does write the value of my blob I get a well formatted tar file, I'm thinking that maybe tar cannot handle a stream as it's input? That may be. In that case, you should invoke tar as tar xf - I have tried tar xf but tar gives me an error, it wants a file specified. But I didn't say to use tar xf. I said to use tar xf -. That dash is highly significant. It means The file to read is the standard input. tried tar xf - mysql test1 | tar xf - tar: This does not look like a tar archive tar: Skipping to next header tar: Error exit delayed from previous errors I dont get it, if i do this command: mysql select * into dumpfile /usr/local/test1.tar from my_table I get a tar archive written to /usr/local/ I'm using mysql client to store my tar file as a blob, mysql insert into my_table values(LOAD_FILE(/usr/local/test1.tar)); could this function cause my problem? No. The output when you retrieve the column and send it into the pipe contains something that is not part of the column contents itself. Likely the column header. Try using the --skip-column-names option to suppress the header. I tarred test1 recreated my_table, using LOAD_FILE put in test1.tar 30 B file before tarred, after tarred 10 K mysql --skip-column-names test1 | tar xf - tar: This does not look like a tar archive tar: Skipping to next header tar: Error exit delayed from previous errors mysql --skip-column-names test1 | more ./test1\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\ 0\0\0\0\0\0\0\0\0\0\0\0\0\0 \0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0 \0\0\0\0\0\0\0\0\0\0\0\0\0\0 \0\0\0\0\0\00100644\\\0036\00775674053 0\0011034\0 0\0\0\0\0\0\0\0\ 0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\ 0\0\0\0\0\0\0\0\0\0\0\0\0\0\ 0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\ 0\0\0\0\0\0\0\0\0\0\0\0\0\0\ 0\0\0ustar \0root\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0 root\0\0\0\0\0\0 \0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0 \0\0\0\0\0\0\0\0\0\0\0\0\0\0 \0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0 \0\0\0\0\0\0\0\0\0\0\0\0\0\0 \0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0 \0\0\0\0\0\0\0\0\0\0\0\0\0\0 \0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0 \0\0\0\0\0\0\0\0\0\0\0\0\0\0 \0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0use test;\nselect * from test;\n\0\0\0\ lot more but all \0's I dont know if tarring adds all this extra data? but tarring did increase the file size from 30 B to 10 K, I used vi to create test1, -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- 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: piping blob into shell command (tar)
one more idea: try: mysql --skip-column-names --raw test1 | tar xf - -Original Message- From: Denis Mercier [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 19, 2003 2:41 PM To: [EMAIL PROTECTED] Subject: Re: piping blob into shell command (tar) On Wed, 2003-11-19 at 14:02, Paul DuBois wrote: At 13:55 -0500 11/19/03, Denis Mercier wrote: On Wed, 2003-11-19 at 12:26, Paul DuBois wrote: At 11:03 -0500 11/19/03, Denis Mercier wrote: i also tried: use my_db; select * from my_table; so when i try shell mysql --pager test1 | tar x the tar file does not get written to /usr/local/test1 but i still dont get my untared files? write permissions are ok? what am i missing? Have you verified that the mysql command actually writes any output, for example, with this command: shell mysql --pager test1 | wc Do you get any output from tar at all, such as an error message? Maybe the output from mysql isn't suitable. Couple of things to try: The output from mysql seems fine because when it does write the value of my blob I get a well formatted tar file, I'm thinking that maybe tar cannot handle a stream as it's input? That may be. In that case, you should invoke tar as tar xf - I have tried tar xf but tar gives me an error, it wants a file specified. But I didn't say to use tar xf. I said to use tar xf -. That dash is highly significant. It means The file to read is the standard input. tried tar xf - mysql test1 | tar xf - tar: This does not look like a tar archive tar: Skipping to next header tar: Error exit delayed from previous errors I dont get it, if i do this command: mysql select * into dumpfile /usr/local/test1.tar from my_table I get a tar archive written to /usr/local/ I'm using mysql client to store my tar file as a blob, mysql insert into my_table values(LOAD_FILE(/usr/local/test1.tar)); could this function cause my problem? No. The output when you retrieve the column and send it into the pipe contains something that is not part of the column contents itself. Likely the column header. Try using the --skip-column-names option to suppress the header. I tarred test1 recreated my_table, using LOAD_FILE put in test1.tar 30 B file before tarred, after tarred 10 K mysql --skip-column-names test1 | tar xf - tar: This does not look like a tar archive tar: Skipping to next header tar: Error exit delayed from previous errors mysql --skip-column-names test1 | more ./test1\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\ 0\0\0\0\0\0\0\0\0\0\0\0\0\0 \0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0 \0\0\0\0\0\0\0\0\0\0\0\0\0\0 \0\0\0\0\0\00100644\\\0036\00775674053 0\0011034\0 0\0\0\0\0\0\0\0\ 0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\ 0\0\0\0\0\0\0\0\0\0\0\0\0\0\ 0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\ 0\0\0\0\0\0\0\0\0\0\0\0\0\0\ 0\0\0ustar \0root\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0 root\0\0\0\0\0\0 \0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0 \0\0\0\0\0\0\0\0\0\0\0\0\0\0 \0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0 \0\0\0\0\0\0\0\0\0\0\0\0\0\0 \0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0 \0\0\0\0\0\0\0\0\0\0\0\0\0\0 \0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0 \0\0\0\0\0\0\0\0\0\0\0\0\0\0 \0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0use test;\nselect * from test;\n\0\0\0\ lot more but all \0's I dont know if tarring adds all this extra data? but tarring did increase the file size from 30 B to 10 K, I used vi to create test1, -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- 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: db maint
There recently was a thread discussing this with a very nice summary by the person who had the issue... do a search on the archives for 'maintaining the size of a db' to find the thread -Original Message- From: M.D. DeWar [mailto:[EMAIL PROTECTED] Sent: Tuesday, November 18, 2003 10:51 AM To: [EMAIL PROTECTED] Subject: db maint Hello WARNING::Newbie. I have snort running and putting alerts into a mysql database. I see that its starting to get big. How does one go about cleaning the db ? or deleting old data ? Thanks Mark -- 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: 50 000 tables - table_cache
I can't believe I'm saying this, but MySQL may not be the db of choice for your particular app... I figure you have few options: 1- keep db structure same, keep mysql, suffer performance issues 2- keep db structure, switch db, suffer migration costs 3- change db structure, keep mysql, suffer app modification 4- change db structure, switch db, suffer migration and app mod I know it sounds like nihlistic (sp?), that all of your options involve suffering, but I'm guessing that this is an organically grown app architecture, that it didn't start off with 50,000 tables. IMHO, your app is at a classic architecture inflection point, where you need to either redesign it, or truss it up with hardware changes. PS What about the idea of setting up a MySQL cluster, so that hopefully, various access will be spread out, and therefore file access... don't know about this one, but it's a though -Original Message- From: Jörgen Winqvist [mailto:[EMAIL PROTECTED] Sent: Tuesday, November 18, 2003 11:02 AM To: Chris Nolan Cc: [EMAIL PROTECTED] Subject: Re: 50 000 tables - table_cache Hi Chris, I know its not good to have that many tables but that's the way its done. I can't see why a merge would help. It still has to open all the tables. regards /jorgen Chris Nolan wrote: Hi! Looking through the docs, MySQL's internals don't seem to lend themselves well to having this many tables. Is there any chance you could use MERGE tables to chuck a few of them together (admittedly I'm guessing here). Regards, Chris Jörgen Winqvist wrote: I'm working with an application that uses a lot of tables. Apx 50k tables are accessed every 5 minuts and they are openened/closed constantly. There is a performance problem and i've changed the filesystem to reiserfs for better handling many files. I also increased the open-file limit in both mysql and linux but i can't increase table_cache more than 16384 tables. Any ideas? Regards - Jorgen -- 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: replace query + RTFM?
Andrew, Please allow a small amount of grief to come from these lists... People are usually glad to help, and Mike sent the info that he knew to you. There is a certain amount of frustration that develops when people repeatedly ask questions of the mailing list that are clearly documented in the mysql documentation. Suggesting to read the manual is not a bad thing, and RTFM is a long stading acronym that nobody on this list invented, and I guarentee everyone on this list is guilty of asking a question that is in the documentation for some product / project they were working on. The MySQL documentation is, IMHO (in my humble opinion - another oldie acronym), incredibly good for a free product. Searching it for answers, particularly about syntax, should be everyone's, including my, first step in solving an issue that we're having. [getting down off of soap box] Dan Greene -Original Message- From: Andrew [mailto:[EMAIL PROTECTED] Sent: Monday, November 17, 2003 1:16 PM To: MySQL-Lista; Rodolphe Toots Subject: RE: replace query + RTFM? amazing, and you guys are errr intelligent!!! can't actually help but you can make an abbreviation for RTFM how f#$#$ng sad is that, what a bunch of pathetic losers. -Original Message- From: Rodolphe Toots [mailto:[EMAIL PROTECTED] Sent: 17 November 2003 18:11 To: [EMAIL PROTECTED] Subject: SV: replace query + RTFM? RTFM = read the fing manual.. -Ursprungligt meddelande- Från: Andrew [mailto:[EMAIL PROTECTED] Skickat: den 17 november 2003 18:55 Till: Mikael Fridh; [EMAIL PROTECTED]; MySQL-Lista Ämne: RE: replace query + RTFM? Hi Mike I really appreciate you getting back to me so quickly :) Can I just clarify this becuse I have over 4000 records and not all of the records have anything in items - description but there are about 2500 records with this exact text- br /A-Z Business Directory br /br / Please call 0116 27 960 41 for further details br /br / Replace these details with yours simply by calling 0116 27 960 41 br /br / Unlike YELL we only list a maximum of 5 businesses per Business Category br /br / The chances of your business being called has been increased already. Be Seen by calling 0116 27 960 41 or br /br /A HREF=http://www.punterspower.co.uk/register_online.php;registering online/Abr /br /ulliNo Web Site needed/liliNo Internet knowledge required/liliAll administration carried out by us/liliAll contacts forwarded to you/liliComprehensive Company details/liliLink to your own web site/liliYou can add html within your description making your profile suit/liliLogin access to administer and change you details whenever you need to/li/ul and it just the telephone I want to change :) so is the correct query REPLACE items SET description=new telphone number WHERE (old telephone number) Thank you so much Andrew PS what does RTFM mean? -Original Message- From: Mikael Fridh [mailto:[EMAIL PROTECTED] Sent: 17 November 2003 17:12 To: [EMAIL PROTECTED]; MySQL-Lista Subject: Re: replace query On Monday 17 November 2003 18.04, Andrew wrote: I have a MySQL database with records in a table that i would like to replace. The table is items and the field is description. I would like to replace the existing text within this filed with some new text. I did this before and it was successful but I have lost the query to do it :( Could someone shed some light in this please :) Andrew http://www.mysql.com/doc/en/Data_Manipulation.html REPLACE items SET description=RTFM WHERE ...(insert your where clause here) Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] --- Incoming mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.541 / Virus Database: 335 - Release Date: 14/11/2003 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] --- Incoming mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.541 / Virus Database: 335 - Release Date: 14/11/2003 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: replace query + RTFM?
you need to tell mysql what field to equate to the value in your where clause: update items set ItemDescription = 'new text' where ItemDescription = 'old text' replacing the items with the text you have below -Original Message- From: Andrew [mailto:[EMAIL PROTECTED] Sent: Monday, November 17, 2003 4:19 PM To: [EMAIL PROTECTED]; Mikael Fridh; MySQL-Lista Subject: RE: replace query + RTFM? Can someone kindly tell me what I doing wrong and help with this query? I want to replace / update the text for every record within table items field ItemDescription. This is what I have so far and its isn't corroect :( UPDATE items SET ItemDescription='A-Z Business Directory br /br / Please call 0870 199 4080 for further details br /br / Replace these details simply by calling 0870 199 4080 br /br / Unlike YELL we only list a maximum of 5 businesses per Business Category br /br / The chances of your business being called has been increased already. Be Seen by calling 0870 199 4080' WHERE 'A-Z Business Directory br /br / Please call 0116 27 960 41 for further details br /br / Replace these details simply by calling 0116 27 960 41 br /br / Unlike YELL we only list a maximum of 5 businesses per Business Category br /br / The chances of your business being called has been increased already. Be Seen by calling 0116 27 960 41' Andrew -- 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: maintaining size of a db
two last gotchas I thought of... 1- the routine will erase all the old records, but the day's logs will increment between executions, so you may want to give it a day's worth of 'padding' if the 20GB is a hard limit (disk size). No worries if it's flexible 2- until you have filled to your size limit, you may want to run it manually as you won't have your 250,000 records in the table yet, so your initial query will return null (I think), which is very likely to mess up the delete statement following it -Original Message- From: Scott H [mailto:[EMAIL PROTECTED] Sent: Thursday, November 13, 2003 12:30 PM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: maintaining size of a db Got it Harald, thanks. OK, I've got this working now, so I'll do a quick overview of what I've learned... for the archives: I am setting up mysql with msyslog to be a centralized logging server. My servers (Windows Red Hat) will send their logs to this mysql box. To keep the mysql db from growing beyond a certain size, I first estimate the size of my average record and divide by the total byte size I want to allow on disk, to determine about how many records I want as a maximum (of course, one must look at and consider the size of any indexes for your db, also, and leave some extra room for error). For this example, I'll say I've figured out that I can allow a maximum of 250,000 records, and I have: - a db named msyslog - and a table within it named syslogTB syslogTB has an autoincrement field seq. What I do is set up a cron job to run a scan of the database periodically, and yank out all records beyond 250,000. The cron job runs as sql-user with password PASSWORD and calls a plain text file /root/delete_old.sql for it's input. The cron job will thus run this as its command: /path/mysql -u sql-user --password=PASSWORD msyslog /root/delete_old.sql ...and in /root/delete_old.sql, there is only this text (2 lines): select (@aa:=seq) as low_seq from syslogTB order by seq DESC limit 25,1; delete from syslogTB where seq @aa; Thanks everyone for your help!! Scott --- Harald Fuchs wrote: Scott H wrote: That's fine. Thus if have seq as an autoincrement field, and I wanted to stay around say 1000 records, deleting the oldest records, I would need to run a cron job that would somehow nest or relate these 2 sql statements: select (@aa:=seq) as low_seq from logtable order by seq limit 1000,1 delete from logtable where seq @aa I've tried putting this into a subquery format but no luck. But I'm a noob, so I keep trying, thinking I might hit on the right syntax. Or, is there some way to pull the value of low_seq into an environment variable and use it in a script file to run the 2nd statement? That's exactly what the two statements above do, except that MySQL has user variables (the @aa shown above) instead of environment variables. = -- To announce that there must be no criticism of the President, or that we are to stand by the President, right or wrong, is not only unpatriotic and servile, but is morally treasonable to the American public. -- Theodore Roosevelt, 1918 . __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- 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: Database-design
I think that I must be missing something, as I agree with all the suggestions that to change the seperate date element columns to a single date field, but Meli's original post had a date falling into multiple quarters. Now to my knowledge, a date can only be in one quarter, from a calendar point of view, so maybe there's something more to Meli's issue... to store the info more efficiently for what you're saying, you could also use binary as a guide 1 2 3 4 s n r t t d d h ___ 8 4 2 1 - - - - 1000 = 8 0100 = 4 0010 = 2 0001 = 1 1100 = 12 1010 = 10 1001 = 9 0110 = 6 0101 = 5 0011 = 3 1110 = 14 1101 = 13 1011 = 11 0111 = 7 = 15 = 0 (which you don't have below but here for completeness) and store a single number that represents the pattern you have below, replacing 'null' with 0 and x as 1 xnullnull null null x null null null null xnull null nullnullx x x null null x null xnull x null null x null x xnull null x nullx null null x x x x xnull x x null x x null x x null xx x x xx x Ladies and Gentlemen, the first real use of the bitwise section of the java certification exam I have ever used -Original Message- From: Brent Baisley [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 12, 2003 9:01 AM To: Meli Meli Cc: [EMAIL PROTECTED] Subject: Re: Database-design Why would you created separate fields for each quarter? Create a field called quarter and store a number in it. You could also combine year, month and day into a date field, which would make it easier to search on ranges. So, I think your table should look like this: id quarter eventdate week On Tuesday, November 11, 2003, at 02:33 PM, Meli Meli wrote: I have a table with following structure: Id first quarter second quarter third quarter last quarter year month week day On an entry not all fields of the four quarter fields are covered with values. Following combinations are possible: first quarter | second quarter | third quarter | last quarter xnullnull null null x null null null null xnull null nullnullx x x null null x null xnull x null null x null x xnull null x nullx null null x x x x xnull x x null x x null x x null xx x x xx x The table will receive many thousands of entry's. Would it be better to divide the table in to 15 small tables in order to not register fields with null values? Thanks for helping Regards Martin - Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Backing up all databases
you could put a shell script as the actual cron job, and make the file only read-able by root, using an environment variable as the password passed (defined in shell script file), so that way even if someone 'sniffs' the process via 'ps -ef' they don't see the actual password (if they happen to catch the setting of the env var that's another story, but _much_ less likely) -Original Message- From: Randall Perry [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 12, 2003 9:10 AM To: Cormac Tiernan Cc: [EMAIL PROTECTED] Subject: Re: Backing up all databases Well, this command is running in a cron job -- so that's not an option. Be aware that you password is visible (unix anyway with a ps -ef..) when you pass the password like -ppassword. Usually you can enter the password later if you use usr/local/mysql/bin/mysqldump --opt --all-databases -p which avoids the password being visible.. Cormac. On 12-Nov-2003 Randall Perry wrote: Ok, I was confused about the password thing. It works now that I'm passing root's password in the command: /usr/local/mysql/bin/mysqldump --opt --all-databases -prootpassword /usr/local/mysql/data/mysqldump You're joking, right? (Perhaps you thought the original question was a joke, too, as root normally has access to all dbs?) As I understand the manual http://www.mysql.com/doc/en/GRANT.html, that will give root access to every db, from every host except localhost, with no password! I can't imagine that's a good idea. And even so, I don't think this will help, as he's connecting from localhost. If we take the question at face value, it appears he has some dbs that root can't access. (I've never tried it, but I supppose it's possible to revoke root's access to a particular db.) If we assume [EMAIL PROTECTED] has a password we don't want to change, the correct command would be GRANT ALL ON *.* to [EMAIL PROTECTED]; If he wants to change root's password at the same time, he would need to add the IDENTIFIED BY clause GRANT ALL ON *.* to [EMAIL PROTECTED] IDENTIFIED BY 'newpassword'; Am I missing something? -- Randall Perry sysTame Xserve Web Hosting/Co-location Website Development/Promotion Mac Consulting/Sales http://www.systame.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- E-Mail: Cormac Tiernan [EMAIL PROTECTED] Date: 12-Nov-2003 Time: 15:01:53 This message was sent by XFMail -- -- Randall Perry sysTame Xserve Web Hosting/Co-location Website Development/Promotion Mac Consulting/Sales http://www.systame.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: maintaining size of a db
cronjob a sql script that runs a delete statement for old jobs daily -Original Message- From: Scott H [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 12, 2003 10:40 AM To: MySQL Mailing List Subject: Re: maintaining size of a db --- Egor Egorov wrote: Scott H wrote: Can't seem to find this one in the manual or archives - how do I control a db to maintain its size to an arbitrary value, say 20 GB? I want to just rotate records, deleting those that are oldest. You can't restrict size of the database only with MySQL, use disk quotas. No! That would just stop mysql right in its tracks (so to speak...) when it got too large. But I want old records sloughed off and the db to continue running. (This is for a central syslog box.) __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- 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: maintaining size of a db
What I would do is a classical guesstimate find the average size per record (data file size + index file(s) size / # records in table) using that, find the data used per day using that, figure out how many days, on average it takes to hit 20GB let's say it's 89 days. right off the top, take 10% off for safety, now we're at 80 days presuming your table has a timestamp field: delete from log_table WHERE TO_DAYS(NOW()) - TO_DAYS(date_col) 80 if you don't have a timestamp field, but you do have an autoincrement id field: figure out number of records on average = 20gb (say it's 2M) again, use 10% for safety (1.8M) select (@aa:=id) as low_id from logtable order by id limit 1800,1 delete from logtable where id @aa (do subqueries work with a limit clause?) -Original Message- From: Scott H [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 12, 2003 11:19 AM To: Dan Greene; MySQL Mailing List Subject: RE: maintaining size of a db Yes sir, exactly. It's just that's what I'm looking for, and can't figure out. I can set up a cron job, but what exactly would the SQL delete statement be that would allow me to delete old records in such a way that the db maintains an approximately constant size on disk? (Failing that perhaps a delete statement that would just have it maintain a constant # of records? ...maybe this would be much simpler?) --- Dan Greene wrote: cronjob a sql script that runs a delete statement for old jobs daily --- Egor Egorov wrote: Scott H wrote: Can't seem to find this one in the manual or archives - how do I control a db to maintain its size to an arbitrary value, say 20 GB? I want to just rotate records, deleting those that are oldest. You can't restrict size of the database only with MySQL, use disk quotas. No! That would just stop mysql right in its tracks (so to speak...) when it got too large. But I want old records sloughed off and the db to continue running. (This is for a central syslog box.) . __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: maintaining size of a db
you may be able to put both statements to a text file, let's call it deleteold.sql then your cron job would be : mysql (put your connect stuff here) deleteold.sql -Original Message- From: Scott H [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 12, 2003 3:17 PM To: Michael McTernan; Dan Greene Cc: MySQL Mailing List Subject: RE: maintaining size of a db Well, it sort of helps. But that section is about future enhancements intended for mysql. I need to set something up now, with the current stable version. One thing I read (can't find it now) indicated that the current version (I'm actually running 4.0.15a) has limited support for subqueries - but I don't know exactly how far that goes. So let me set the stage a bit more - I'll assume for now there is no reasonably simple way to work with the actual size of the database on disk, and instead will go with the idea that I can expect the size of any one record to be of some average. So, according to Dan's suggestion, if I do a little math, and control the number of records, I can control the size of the db -- approximately. That's fine. Thus if have seq as an autoincrement field, and I wanted to stay around say 1000 records, deleting the oldest records, I would need to run a cron job that would somehow nest or relate these 2 sql statements: select (@aa:=seq) as low_seq from logtable order by seq limit 1000,1 delete from logtable where seq @aa I've tried putting this into a subquery format but no luck. But I'm a noob, so I keep trying, thinking I might hit on the right syntax. Or, is there some way to pull the value of low_seq into an environment variable and use it in a script file to run the 2nd statement? Other ideas? thanks, scott --- Michael McTernan wrote: From the manual: 1.8.4.1 Subqueries Subqueries are supported in MySQL version 4.1. See section 1.6.1 Features Available in MySQL 4.1. Hope that helps, Mike From: Scott H OK, I *THINK* I follow you here. Couple of questions. I'm reading an online tutorial trying to figure this out, and I am led to believe mysql can't do nested queries, aka sub-queries. But you say it can? Is this recent? And I don't have a timestamp field, I have an autoincrement field, but what do you mean by the (@aa:=id) thing? I don't follow that. thanks. --- Dan Greene [EMAIL PROTECTED] wrote: What I would do is a classical guesstimate find the average size per record (data file size + index file(s) size / # records in table) using that, find the data used per day using that, figure out how many days, on average it takes to hit 20GB let's say it's 89 days. right off the top, take 10% off for safety, now we're at 80 days presuming your table has a timestamp field: delete from log_table WHERE TO_DAYS(NOW()) - TO_DAYS(date_col) 80 if you don't have a timestamp field, but you do have an autoincrement id field: figure out number of records on average = 20gb (say it's 2M) again, use 10% for safety (1.8M) select (@aa:=id) as low_id from logtable order by id limit 1800,1 delete from logtable where id @aa (do subqueries work with a limit clause?) -Original Message- From: Scott H [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 12, 2003 11:19 AM To: Dan Greene; MySQL Mailing List Subject: RE: maintaining size of a db Yes sir, exactly. It's just that's what I'm looking for, and can't figure out. I can set up a cron job, but what exactly would the SQL delete statement be that would allow me to delete old records in such a way that the db maintains an approximately constant size on disk? (Failing that perhaps a delete statement that would just have it maintain a constant # of records? ...maybe this would be much simpler?) --- Dan Greene wrote: cronjob a sql script that runs a delete statement for old jobs daily --- Egor Egorov wrote: Scott H wrote: Can't seem to find this one in the manual or archives - how do I control a db to maintain its size to an arbitrary value, say 20 GB? I want to just rotate records, deleting those that are oldest. You can't restrict size of the database only with MySQL, use disk quotas. No! That would just stop mysql right in its tracks (so to speak...) when it got too large. But I want old records sloughed off and the db to continue running. (This is for a central syslog box.) -- MySQL General Mailing List For list archives: http
RE: Having MySQL listen on multiple(2) ports at the same time
your best bet would be to do a port-forwarding methodology, at the os level, not in mysql itself I've used some for SOAP tracing, to intercept and view http traffic, but I'm sure there are some out there that just forward traffic w/o a gui -Original Message- From: Misaochankun [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 12, 2003 4:06 PM To: [EMAIL PROTECTED] Subject: Having MySQL listen on multiple(2) ports at the same time Can this be done? No, I do not mean running multiple MySQL servers. I need to have MySQL listen on two separate ports at the same time. Reason being, the new port is needed for a routing issue, and the default port 3306 needs to stay up to respond to normal traffic. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Last 75 entries from a table
you could do select article_num from $table order by article_num desc LIMIT 75 -Original Message- From: Mark [mailto:[EMAIL PROTECTED] Sent: Thursday, November 06, 2003 8:40 AM To: [EMAIL PROTECTED] Subject: Last 75 entries from a table Hello, Using MySQL 3.23.58, what I want is to select the last 75 entries from a table. Like so: SELECT article_num FROM $table LIMIT 75 Except, of course, that this gives me the first 75, whereas I need the last 75. It sounds trivial; and it probably is, but I could not find it. article_num, by the way, is not necessarily sequentially numbered, so doing something from X-75 to X, will not work. Thanks, - Mark -- 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: This is confusing..?
I seem to remember someone saying that if a query would end up returning more than x% of a table (I think it was either 30% or 50%), then mysql just ends up doing a full scan regardless... -Original Message- From: Eric Anderson [mailto:[EMAIL PROTECTED] Sent: Thursday, November 06, 2003 2:08 PM To: [EMAIL PROTECTED] Subject: This is confusing..? Given the following table: CREATE TABLE campaign_t ( acct_id int(11) unsigned NOT NULL default '0', site_id tinyint(3) unsigned NOT NULL default '0', ref_id int(11) unsigned NOT NULL default '0', datestamp char(10) NOT NULL default '', raws int(11) unsigned NOT NULL default '0', uniques int(11) unsigned NOT NULL default '0', trial_signups int(11) NOT NULL default '0', full_signups int(11) NOT NULL default '0', annual_signups int(11) unsigned NOT NULL default '0', PRIMARY KEY (acct_id,site_id,ref_id,datestamp), KEY acct_id (acct_id), KEY site_id (site_id), KEY ref_id (ref_id), KEY datestamp (datestamp) ) TYPE=MyISAM; How come it doesn't use the 'datestamp' index on this query: mysql explain SELECT * FROM campaign_t WHERE datestamp 20041105\g ++--+---+--+-+--+- ---++ | table | type | possible_keys | key | key_len | ref | rows | Extra | ++--+---+--+-+--+- ---++ | campaign_t | ALL | datestamp | NULL |NULL | NULL | 438166 | where used | ++--+---+--+-+--+- ---++ 1 row in set (0.00 sec) -- 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: Table design help
you are going to want a 'buster' table... also known as a many-to-many table so you have: contacts Contact_Key Industries - Industry_Key Contact_Industry_assoc -- Contact_Key Industry_Key -Original Message- From: DeBerry, Casey [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 29, 2003 1:09 PM To: '[EMAIL PROTECTED]' Subject: Table design help I have a small table that contains company contact information: Table: Contacts Key Name Address Phone Email Website etc.. What I need to do is associate the type of industry the business servers from a list of about 40 industries... My initial thinking was to create another table that contains all of the industries along with unique key for each. So table would look like Table: Industries Industry Key The problem is, some businesses server many different industries. So my question is, can I add another column to the contacts table that would associate many different industry key's to the contact? What is the best way to design this database to talk with PHP? Thanks, Casey DeBerry -- 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: Insert happens twice
I just recently helped someone else with a similar issue
is your PHP code (and therefore your insert) getting executed when the form is being
displayed to the user initially, and then again when the form is submitted?
-Original Message-
From: Erich C. Beyrent [mailto:[EMAIL PROTECTED]
Sent: Tuesday, October 28, 2003 10:19 AM
To: [EMAIL PROTECTED]
Subject: Insert happens twice
Hey folks,
I am having a dreadful problem here, and I cannot get to the
root of it. It
appears that every time I do an INSERT, the insert happens
twice. I have
spent several days on Google, but with no luck.
Some background - I am using PHP and Smarty on a site. Here
is one of the
tables in my database that is having the problem:
CREATE TABLE composers (
ComposerID bigint(20) unsigned NOT NULL auto_increment,
ComposerFname varchar(50) default NULL,
ComposerLname varchar(60) default NULL
DiscountID int(11) default NULL,
PRIMARY KEY (ComposerID)
)
So I pass some values in from a form, and I end up with this:
$sql = insert into composers(ComposerFname, ComposerLname) values('',
'Bach');
which then gets executed. My debug statements indicate that
this is only
getting executed once. However, I end up with two new entries in my
composers table. I know I can solve this by making the
fields unique, but
I'd have to do that for every table in the database, when
what I'd really
like to do is find out why the insert is happening twice.
Any ideas?
-Erich-
--
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: Howto reduce size of MYISAM files after deleting records?
Is there a way to do this on a live running (i.e. production) server? -Original Message- From: Dathan Vance Pattishall [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 28, 2003 1:49 PM To: 'Iago Sineiro'; 'MySql Mail List' Subject: RE: Howto reduce size of MYISAM files after deleting records? Shutdown the mysql server Goto the datadir (/var/lib/mysql) Goto the db directory Type myisamchk -r -S -a *.MYI this will reclaim the bad blocks caused by the delete. - Dathan Vance Pattishall - Sr. Programmer and mySQL DBA for FriendFinder Inc. - http://friendfinder.com/go/p40688 ---Original Message- --From: Iago Sineiro [mailto:[EMAIL PROTECTED] --Sent: Tuesday, October 28, 2003 9:52 AM --To: MySql Mail List --Subject: Howto reduce size of MYISAM files after deleting records? -- --Hi. -- --I delete a lot of records of one MyISAM table in MySQL 4.0.16. After that --I --execute mysqladmin refresh and now the size of the files of the table is --the --same than before. -- --Is something wrong or is necessary doing something more to reduce the --size --of the files? -- --Thanks in advance. -- --Iago. -- -- -- --MySQL General Mailing List --For list archives: http://lists.mysql.com/mysql --To unsubscribe: --http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Ancestry program
well... when I do db design, I tend to start with the objects of my system. The one that comes to mind in your case is people. so you'll need a people table. well what are the details of a person? first_name Last_name Middle_name1 Middle_name2 Maiden_name [any other basic bio data] so you'll need those columns Well to keep track of each person, each one will need an ID... id's are usually numbers, so now you add a: person_id field. This field would likely have an auto_increment attribute to help number them for you ok... now that we have people, what else do we need? relationships between them well... in terms of human beings, everyone has one biological mother and one biological father, so we add in mother_id father_id leaving the values of these as null would be equivalent of being 'unknown' and we now have, data-wise, a system that can trace biological heritage, can handle siblings and half-siblings. Other ideas for objects: Marrages - this one would be tricky/interesting, as marrages can change over time, and people can have multiple marrages (although usually not two at a time, unless bigamy is allowed in your user's state/country). Strictly speaking, marrages are not necessary to trace heritage, but are good info... --From: Nitin [mailto:[EMAIL PROTECTED] --Sent: Monday, October 27, 2003 10:46 PM --To: [EMAIL PROTECTED] --Subject: Ancestry program -- --Hi all, -- --I'm developing a web based ancestry program. The user wants it to be --static, that means, it isn't for all to use, but his family. Better to --say, it'll contain only his family tree. -- --Now, I cant think of the proper db design, which will help any user to --find his or her relationship with any other person in the tree. Though, I --can design a simple database, where everything will have to be done --through queries and scripts, but I want those queries to keep as simple --as possible. -- --Any help will be appreciated, as I'm new to such a problem. -- --Thanx in advance --Nitin -- 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: My Company DB Wars
It's because you forgot to put the new cover sheet on your TPS (transaction per second, in this case) report Did you get the memo? Similar thing happened to my college... they doubled their tuition over 5 years because as the president of the univeristy put it, 'good colleges are expensive' I'll send you that memo :D Dan Greene -Original Message- From: Randy Chrismon [mailto:[EMAIL PROTECTED] Sent: Monday, October 27, 2003 2:51 PM To: [EMAIL PROTECTED] Subject: My Company DB Wars My old Micro-Economics professor must be chortling in his grave... The bosses told me a few minutes ago to quit pusing MySQL for an internal project and to move my proof-of-concept tables from MySQL running on a Linux desktop with 512mb of ram and the bloody DB on an external USB to a DB2 database running on a Win2K server with 1GB ram and a 120 GB raid system. The reason? TPTB simply can't believe that a licensed MySQL system at $450 for the base license, no connected user fees and $2,500 per year for advanced support (we need InnoDB, FK constraints and transaction safe tables) can possibly be as good as DB2 at a minimum of 5 times the software cost. Afterall, DB2 has triggers and stored procedures (although nobody -- including the contractors actually doing the coding -- can point to a single stored procedure or trigger that is essential to the project). The contractors ARE making fairly extensive use of views but I've already figured out three ways around them. Go figger. I will continue to use MySQL for testbedding. Also, since I'm the gate-keeper for the contractor's code, I'm going to keep their stuff as generic as possible. BTW, one question, does MySQL run on the AS400? Randy -- 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: Really slow query (compared with Visual FoxPro)
Indexes are your friend. Perhaps your best friend (as far as databases go) --- To give light on your question... you can index any field... if the entries are distinct, it's called a 'unique index' which are the best kind to use. Otherwise you have a 'non-unique index', which can also be handy Think of indexes, like... well... book indexes... to prevent you from looking all over the book for an answer, you can find the exact page the topic is on (unique index), or if it's covered in multiple pages, you reduce your searching to just that list of pages (non-unique index). Then there's compound indexes, which is an index on the value of two seperate fields (which can be unique, or non-unique). As a rule of thumb, you want indexes on your unique key (primary key) (call record id) Most db's make indexes automatically on keys you say are the primary key. Also on your most commonly searched fields (usually non-unique) such as foreign keys (call record id in your record notes table for example) -- If you're that new to db design, I would recommend going out and looking for some books/sites on the subject... There have been a few mentioned on this mailing list recently (Michael Kofler and Paul Dubois), http://www.databaseanswers.com/, and Database Design for Mere Mortals and finally Mike ([EMAIL PROTECTED]) mentioned the following online tutorials: Try one of these MySQL tutorials: http://www.mysql.com/doc/en/Tutorial.html http://www.analysisandsolutions.com/code/mybasic.htm http://www.devshed.com/Server_Side/MySQL http://www.sqlcourse.com/ http://www.w3schools.com/sql/default.asp http://www.juicystudio.com/tutorial/mysql/ http://www.justphukit.com/mysql/mysql-tutorials-1.php http://sqlzoo.net/ http://www.troobloo.com/tech/mysql.shtml http://perl.about.com/cs/mysql/index.htm http://www.anu.edu.au/web/authors/mysql/manual_Tutorial.html Reference: http://www.mysql.com/doc/en/ (the MySQL manual is quite good too) http://tiger.la.asu.edu/Quick_Ref/MySQL_QuickRef.pdf (MySQL Quick Reference Card) Related links http://dmoz.org/Computers/Software/Databases/MySQL/Tutorials/ -Original Message- From: Héctor Villafuerte D. [mailto:[EMAIL PROTECTED] Sent: Friday, October 24, 2003 1:40 PM To: [EMAIL PROTECTED] Subject: Re: Really slow query (compared with Visual FoxPro) Chris Nolan wrote: Hi! Given VFP's internals (I have to support FoxPro 2.6 apps, oh the pain of it all!), MySQL should be capable of much better performance, considering the only thing FoxPro has in terms of a possible advantage is Rushmore. Which indexes do you have on the tables in the query? Regards, Chris Indexes? hmmm... I knew those were useful for something :) In Visual FoxPro I don't use indexes for this table... so I didn't considered them necessary in MySQL (now I think I need to get to the basics of RDBMS). This table I'm talking about is a CDR (call detail record), so a record represents a call from a given telephone. I'm surely wrong but, ain't the index suppossed to be on a field with distinct entries? Does VFP automagically creates indexes depending on the query? As you can see, I would greatly appreciate any pointers to some database theory. Thanks again! Hector -- 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: question.
You probably want to restructure your tables (if that's an option)
if it's a heirachal menu system (one parent only), then you'll need to put a
parent_id column, and then map each child to the parent.
then once you have your parent menu (158), you select * from menus where parent_id =
158
If it's a cross-menu system (multiple parents per item), you'll need another table,
say menu_relations (source_id, item_id) and put a row in there for each relationship.
then you select * from menus, menu_relations where id = item_id and source_id = 158)
Dunno if this helps
-Original Message-
From: Craig Harding [mailto:[EMAIL PROTECTED]
Sent: Friday, October 24, 2003 12:36 PM
To: [EMAIL PROTECTED]
Subject: question.
I have a table (menus) with the following fields:
TABLE: MENUS
idint(11)
url varchar(100)
items varchar(35)
An example row:
IDURL ITEMS
158 programs/graduate 21,22,23,24,25,26,27,28,160
I want to select the ITEMS and then do another subselect that
can uses
each ITEM as the ID in the subselect.
'select items from menus where id = 158' returns:
21,22,23,24,25,26,27,28,160
Each of these ITEMS is an id in the table also. I want to get
each row
that corresponds to id = 21, 22, 23, 24, 25 ...
I know how to do this in php, but I need to do this on the
command line.
Remember that ITEMS is a varchar type, so I'm not sure if
there's a way
to do some kind of loop within this to select the items or create the
long query string with OR id = 21 OR id = 22 OR id = 23 ... which I
started to do:
select CONCAT('id = ', substring(items,1,LOCATE(',', items,
1)-1), ' OR
id = ', substring(items, 4,locate(',', items,2)-1)) AS STR from menus
where items != '' and id = 158;
but this can be pretty long since it only grabs the first two digits
from ITEMS!
The ITEMS always has NO spaces between commas and numbers and some
numbers may be single digit, two digits or even three in the row.
thanks in advance,
craig.
--
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: Really slow query (compared with Visual FoxPro)
As you are selecting all records (no where clause), it will scan the whole table every time, I believe... does anyone know if he added the other columns to his index, or had 4 seperate indexes (one per column) would they be used in this operation? Ok, I've found the optimization chapter in the manual and I'm still reading it. Here's what I've done so far: mysql alter table traf_oper add index (tel), add index (telefb); mysql explain select tel, telefb, rutaentran, rutasalien, sum(minutos) as minutos from traf_oper group by 1, 2, 3, 4; ++-+---+--+---+--+ -+--+-+-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows| Extra | ++-+---+--+---+--+ -+--+-+-+ | 1 | SIMPLE | traf_oper | ALL | NULL | NULL |NULL | NULL | 5014313 | Using temporary; Using filesort | ++-+---+--+---+--+ -+--+-+-+ 1 row in set (0.50 sec) mysql describe traf_oper; ++--+---+--+-+ -+---+ | Field | Type | Collation | Null | Key | Default | Extra | ++--+---+--+-+ -+---+ | tel| char(8) | latin1_swedish_ci | YES | MUL | NULL| | | fecha | char(8) | latin1_swedish_ci | YES | | NULL| | | hora | char(6) | latin1_swedish_ci | YES | | NULL| | | telefb | char(14) | latin1_swedish_ci | YES | MUL | NULL| | | tiempotasa | char(6) | latin1_swedish_ci | YES | | NULL| | | rutasalien | char(7) | latin1_swedish_ci | YES | | NULL| | | rutaentran | char(7) | latin1_swedish_ci | YES | | NULL| | | serie | char(3) | latin1_swedish_ci | YES | | NULL| | | tipotraf | int(1) | binary| YES | | NULL| | | minutos| int(4) | binary| YES | | NULL| | ++--+---+--+-+ -+---+ 10 rows in set (0.00 sec) So, why isn't SELECT using indexes (key = NULL, key_len = NULL)? Where else do I need to add indexes? Thanks for your help! -- 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: Changing multiple records dynamically
Well, once they are avialable, triggers and/or stored procedures may be your answer, however, most trigger design (don't know about MySQL's forthcoming implementation) won't let you modify the same table on an update or insert, as you could end up in an infinite loop very easily. For now, I think that you're stuck doing it in your application. Just encapsulate how that data can be updated, and have all code that updates it call your function/method/etc... My $0.02... Dan Greene -Original Message- From: Mumba Chucks [mailto:[EMAIL PROTECTED] Sent: Thursday, October 23, 2003 9:53 AM To: Rob Cc: [EMAIL PROTECTED] Subject: RE: Changing multiple records dynamically Hi Rob, Definitely trying to do a value swap. Regards, David --- Rob [EMAIL PROTECTED] wrote: Are you trying to do a value swap or are you just updating? If you are updating you could simply do UPDATE table SET DEFG = 11, HIKJ = 12 -Original Message- From: Mumba Chucks [mailto:[EMAIL PROTECTED] Sent: Thursday, October 23, 2003 3:11 PM To: [EMAIL PROTECTED] Subject: Changing multiple records dynamically Hi, Is there any way to automatically update several rows as a result of a change to information being changed in another row in the same table? |--|---| | NAME | VALUE | |--|---| | ABCD | 10| | DEFG | 12| | HIJK | 11| |--|---| So if I changed DEFG to 11, how would I swap the value with that of HIJK or swap ABCD with DEFG? Thanks, Mumba __ __ Want to chat instantly with your online friends? Get the FREE Yahoo! Messenger http://mail.messenger.yahoo.co.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ __ Want to chat instantly with your online friends? Get the FREE Yahoo! Messenger http://mail.messenger.yahoo.co.uk -- 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: making specific query for big data
I may be missing something, but wouldn't a substring(bigdata, 0,instring(--header end--)) suffice? Of course, my syntax is all kinds of wrong... but you get the idea... -Original Message- From: Jan Magnusson [mailto:[EMAIL PROTECTED] Sent: Thursday, October 23, 2003 10:56 AM To: Mysql General mailing list Subject: RE: making specific query for big data I think he is looking for a way to just retrieve some data prior to a specific (although dynamic) point of the full data saved in a longtext datatype column. Like retrieving just the header of email messages saved with attachments etc in a longtext column. Perhaps having that header data saved in a separate column would fulfill the purpose... Jan -Original Message- From: Director General: NEFACOMP [mailto:[EMAIL PROTECTED] Sent: Thursday, October 23, 2003 17:16 To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: making specific query for big data I don't understand well. Are you looking for the MAX value of a column? Or its data size? Thanks Emery - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, October 23, 2003 12:21 Subject: making specific query for big data Hello all.. so Is it possible to do this: make query on mysql ask to select from blabla where bigdata= but tell to get only all data before --header end-- string into it so it will give in result only strings what are found before --header end-- becouse after this string goes very big data ps. bigdata field is longtext ok.. there is whole picture of this: I have longtext field I need to get only data before --header end-- (or any other pattern) so in result it will give only info before that pattern is it possible to as mysql to get some data from field not alll -- Tavs bezmaksas pasts Inbox.lv ___ FLASH GAMES - http://games.inbox.lv -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: select statement question
from one dan to another. select man.description as man_description, cat.description as cat_description from main_table as main, manufacturer_table as man, categories_table as cat where main.manufacturer_id = man.manufacturer_id and main.category_id = cat.category_id and title_id = 3 Dan ***Greene*** -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 22, 2003 4:18 PM To: [EMAIL PROTECTED] Subject: select statement question how do I get the values of foreign keys, that I have used within a database example Main_DB Main_Table PK title_id3 FK manufacturer_id 5 FK category_id 4 Manufacturer_Table PK manufacturer_id 5 description man. description Categories_Table PK category_id 4 description cat. description my goal is to get choice 3 's man. description and cat description I am probably over looking an obvious answer here, but am making the call to the bullpen anyway. Thank you in advance for your help. **DAN** -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: newbie Q: How to display Search Results in a secure way?
To make this back to being a MySQL question
What if you used the encode or md5 functions when you retrieve the list of results,
and then do your lookup on the item details by matching it to decoding the same id?
ex. get list-
select fid as open_fid, MD5(fid) as crypt_id
from your_table
where whatever your criteria is
Which when you make url will look like:
echo a href=\FacDetails.php?fid=$crypt_id\$fname/a
which will generate something like:
a href=FacDetails.php?fid=23rh23kjne2323j4k23n234lJordan/a
and on your lookup, do
select col1, col2, col3
from FacDetails
where MD5(fid) = $fid;
My $0.02 cents...
At 01:32 PM 10/17/2003, Jordan Morgan wrote:
Hi,
I'm still learning on this. Pls. excuse me if I simply overlooked
something.
Originally I had this:
?
// list matches
while (list($fid, $title, $lname, $fname) =
mysql_fetch_row($result))
{
echo table border=2 width=400 cellpadding=2
cellspacing=2tr;
echo td width=200a
href=FacDetails.php?fid=$fid$fnamenbsp;$lname/a/tdtd
width=200$title/td;
echo /tr/table;
}
?
to list the search results and display them as a link
which will take
the user to a detailed page. However, the fid has to show
up as part of
the URL. I want to hide the fid as I don't want people to
view the other
detailed records just by randomly entering the number for
the fid. So I
tried to modify it and came up with the following:
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: DB Design
Mahesh, The best advice, from what I've heard around the list is to base your decision on this based on your filesystem. Some filesystems handle large # of files well (1 db, many tables) some don't. Some handle many directories well (many db's 1 tbl each), some don't (ala your example). Personally, I would try to come to some happy medium. If you can group your databases by, let's say: 0- mysql data (of course) 1- collection system metadata (tables holding system listings, collection types, frequencies) then have a database per collection type. so that way you don't have a crazy number of files (tables) or directories (databases) finally, be aware that may systems have filesize limitation, so you may want to create archive tables (by month?) to roll data into so that the specific files don't hit system limits (typically 2gb or so) My $0.02 Dan G -Original Message- From: Mahesh Tailor [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 15, 2003 5:05 PM To: [EMAIL PROTECTED] Subject: DB Design New to the list . . . Running MySQL Server 3.23.58-1.72 on RedHat Enterprise AS. System has four 3GHz processors and 6GB RAM. I need some advise on what would be best way to approach this problem. This system is using snmpcollect to collect network statistics from about 1500 devices. The collections are configured to get data every 5-30 minutes depending on the collection type. Given this I am collecting approximately 170K records per hour. I have to keep this collected data for at least 365 days. This works out to approximately 1.50B records/year. After setting up the database, each record is 42 bytes [which would yield, if my math is correct, a database of approximately 62GB]. So my question is: is it better to create one database one table or one database many tables? If I use the many tables option, I will have about 1500 tables. Or, it is better to create 1500 databases with one table each. BTW, I tried the 1-DB-1-table approach the the server came to a crawl. Thanks for any opinions. Mahesh -- 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: When inserting data it insert 2 records one correct and one blank!
Emilio, Can you supply the full insert statement? Also, is this happening for all users, or one in particular? I ask, as I've seen issues with web app users double-clicking the submit button causing similar issues -Original Message- From: Emilio Ruben Estevez [mailto:[EMAIL PROTECTED] Sent: Thursday, October 09, 2003 10:02 AM To: [EMAIL PROTECTED] Subject: When inserting data it insert 2 records one correct and one blank! Hi, I have a page on a intranet where workers can insert data into a database, the thing is that when they type the info an press submit the page use the INSERT INTO db then when i check the data in the database i can see the record that the user entered and a second record that is all in blank, exept for the date and time field that the value is -00-00 and 00:00:00. What´s wrong? maybe something of the date and time format?? Any Clue. Thanks. Emilio. _ Add MSN 8 Internet Software to your existing Internet access and enjoy patented spam protection and more. Sign up now! http://join.msn.com/?page=dept/byoa -- 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]
FW: When inserting data it insert 2 records one correct and one blank!
sending on to list while I think... ;)
-Original Message-
From: Emilio Ruben Estevez [mailto:[EMAIL PROTECTED]
Sent: Thursday, October 09, 2003 10:34 AM
To: Dan Greene
Subject: RE: When inserting data it insert 2 records one correct and one
blank!
Ok, here it is the full statement:
$sql = INSERT INTO llamadas
(destinatario,fecha,hora,apellido,nombre,telefono,mail,receptor,mensaje)
VALUES
('$destinatario','$fecha','$hora','$apellido','$nombre','$telefono','$mail','$receptor','$mensaje');
$result = mysql_query($sql);
And, yes it happens to all users, and i ve tested pressing only once the
submit button but i allways got 2 records add the first one is a blank one
and the second the correct one.
From: Dan Greene [EMAIL PROTECTED]
To: Emilio Ruben Estevez [EMAIL PROTECTED],
[EMAIL PROTECTED]
Subject: RE: When inserting data it insert 2 records one correct and one
blank!
Date: Thu, 9 Oct 2003 10:09:00 -0400
Emilio,
Can you supply the full insert statement? Also, is this happening for all
users, or one in particular? I ask, as I've seen issues with web app users
double-clicking the submit button causing similar issues
-Original Message-
From: Emilio Ruben Estevez [mailto:[EMAIL PROTECTED]
Sent: Thursday, October 09, 2003 10:02 AM
To: [EMAIL PROTECTED]
Subject: When inserting data it insert 2 records one correct and one
blank!
Hi, I have a page on a intranet where workers can insert data into a
database, the thing is that when they type the info an press
submit the page
use the INSERT INTO db then when i check the data in the
database i can
see the record that the user entered and a second record that
is all in
blank, exept for the date and time field that the value is
-00-00 and
00:00:00.
What´s wrong? maybe something of the date and time format?? Any Clue.
Thanks.
Emilio.
_
Add MSN 8 Internet Software to your existing Internet access
and enjoy
patented spam protection and more. Sign up now!
http://join.msn.com/?page=dept/byoa
--
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]
_
High-speed Internet access as low as $29.95/month (depending on the local
service providers in your area). Click here. https://broadband.msn.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: FW: FW: When inserting data it insert 2 records one correct and one blank!
I think I got it
What look like what is happening happening is that your page is running the insert
when you present the form to the user, which is why the php variables are empty. when
they submit the page, you are inserting the correct values. To test this, load the
page up, do not submit it, and see if you have your empty row.
If that is the case, then all you need to do is wrap the execution statement in an if
condition to only process when the form is submitted
-Original Message-
From: Emilio Ruben Estevez [mailto:[EMAIL PROTECTED]
Sent: Thursday, October 09, 2003 3:39 PM
To: Dan Greene
Subject: Re: FW: FW: When inserting data it insert 2 records
one correct
and one blank!
Im lost, im new on php and mysql so i may be omitting some
statements or
logics i send you the entire script, its an lbi that i use on
DreamWeaber MX
if you can just take a look at it. Thanks.
meta http-equiv=Content-Type content=text/html;
charset=iso-8859-1
?php
$db = mysql_connect(localhost, root);
mysql_select_db(qllamo,$db);
$sql = INSERT INTO llamadas
(destinatario,fecha,hora,apellido,nombre,telefono,mail,recepto
r,mensaje)
VALUES
('$destinatario','$fecha','$hora','$apellido','$nombre','$tele
fono','$mail','$receptor','$mensaje');
$result = mysql_query($sql);
?
form method=post action=?php echo $PHP_SELF?
input type=hidden name=id value=?php echo $id ?
table bgcolor=#F2F0E0
tr
tdDestinatario:/tdtd align=centerinput type=Text
name=destinatario value=?php echo $destinatario ?br/td
/tr
tr
tdFecha:/tdtd align=centerinput type=Text name=fecha
value=?php print (date(Y-m-d)) ?br/td
/tr
tr
tdHora:/tdtd align=centerinput type=Text name=hora
value=?php print (date(h-i-d)) ?br/td
/tr
tr
td align=leftApellido:/tdtd align=centerinput type=Text
name=apellido value=?php echo $apellido ?br/td
/tr
tr
tdNombre:/tdtd align=centerinput type=Text name=nombre
value=?php echo $nombre ?br/td
/tr
tr
tdTeléfono:/tdtd align=centerinput type=Text
name=telefono
value=?php echo $telefono ?br/td
/tr
tr
tdMail:/tdtd align=centerinput type=Text name=mail
value=?php echo $mail ?br/td
/tr
tr
tdReceptor:/tdtd align=centerinput type=Text
name=receptor
value=?php echo $receptor ?br/td
/tr
tr
tdMensaje:/tdtd align=centertextarea
name=mensaje cols=70
rows=5 value=?php echo $mensaje ?/textareabr/td
/tr
tr
center td input name=reset type=reset
value=Borrar/td/center
center td input type=Submit name=submit
value=Grabar/td/center
/tr
/table
/form
From: Dan Greene [EMAIL PROTECTED]
To: Emilio Ruben Estevez [EMAIL PROTECTED]
Subject: FW: FW: When inserting data it insert 2 records one
correct and
one blank!
Date: Thu, 9 Oct 2003 13:27:45 -0400
-Original Message-
From: Matt Davies [mailto:[EMAIL PROTECTED]
Sent: Thursday, October 09, 2003 11:10 AM
To: Dan Greene
Subject: Re: FW: When inserting data it insert 2 records one
correct and
one blank!
Emilio-
I ran into this problem just yesterday using some PHP code.
The problem was
in
control logic. I executed the insert within an if
statement, but failed to
see
that there was another mysql_query statement outside the if
block. This
caused
the same insert to happen again.
Basically, in psuedo code
if (found){
query = INSERT
mysql_query (query)
} else {
query = UPDATE
mysql_query (query)
}
mysql_query(query)
Stupid logic on my part. This doesn't adress the date being
blank, but I
would
suggest reviewing your logic to see if there is a
mysql_query that is being
executed 2 times.
My 2 cents...
_
Instant message in style with MSN Messenger 6.0. Download it
now FREE!
http://msnmessenger-download.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Adding indexes on large tables
an option for A- no idea if this will work, but what if you moved your actual data file to new drive, and soft linked it from the other drive? -Original Message- From: Brendan J Sherar [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 07, 2003 7:27 AM To: [EMAIL PROTECTED] Subject: Adding indexes on large tables Greetings to all, and thanks for the excellent resource! I have a question regarding indexing large tables (150M+ rows, 2.6G). The tables in question have a format like this: word_id mediumint unsigned doc_id mediumint unsigned Our indexes are as follows: PRIMARY KEY (word_id, doc_id) INDEX (doc_id) The heart of the question is this: When calling ALTER IGNORE TABLE doc_word ADD PRIMARY KEY(doc_id, word_id), ADD INDEX(doc_id), MySQL proceeds to create a working copy of the table. This process takes over an hour to perform. During this time, disk I/O for the rest of the database (live) reaches a bottleneck, and slows to an unacceptable crawl. Once the copy has been created, MySQL is able to do the actual index build very quickly and efficiently. This process must occur three times daily. A) MySQL creates these temporary tables in the same directory as the original datafile. Is there a way to cause it to use an alternate directory (i.e., on a separate mounted disk)? B) Is there a way to nice this process in such a way that the amount of I/O it consumes in performing the copy is restricted to a manageable level so that other requests to the disks can be served in a timely fashion? C) Would abandoning ext3 in favor of ext2 create a substantial difference? D) We're reluctant to upgrade to 4.0 at this point, but were we do so, are there any significant gains in this situation? E) The ALTER TABLE query is performed using perl DBI. Is there a lower level call available which would improve performance? F) Any other ideas or suggestions? The system in question has the following setup: Dual Xeon 2.8, 4G RAM, 2 x 146GB U160 SCSI (10,000 RPM) on RAID 1 (hardware). Redhat 8.0, 2.4.18 kernel, using ext3 fs. MySQL 3.23.56, with myisam tables. Relevant variables: myisam_sort_buffer_size=512M tmp_table_size=128M This is a master, so bin_log is on Thanks in advance for your help, and please keep up the excellent work! Best, Brendan -- 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: Help With a DATETIME Query
I know it's not the answer you're looking for... :( but dealing with overnights has caused me so much aggravation in past apps I've written, I've tended to make the client create two (or more) 'bookings' for the covered time... don't know if it's an option for you, but it's my $0.02. -Original Message- From: shaun thornburgh [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 07, 2003 4:33 PM To: [EMAIL PROTECTED] Subject: Help With a DATETIME Query Hi, I have a table called Bookings which has two important columns; Booking_Start_Date and Booking_End_Date. These columns are both of type DATETIME. The following query calculates how many hours are available between the hours of 09.00 and 17.30 so a user can see at a glance how many hours they have unbooked on a particular day (i.e. 8.5 hours less the time of any bookings on that day). However, when a booking spans more than one day the query doesn't work, for example if a user has a booking that starts on day one at 09.00 and ends at 14.30 on the next day, the query returns 3.5 hours for both days. The query is run for each day i.e day 1, day 2 day 10. Any help here would be greatly appreciated. SELECT 8.5 - (SUM(((DATE_FORMAT(B.Booking_End_Date, '%k') * 60 ) + DATE_FORMAT(B.Booking_End_Date, '%i')) - ((DATE_FORMAT(B.Booking_Start_Date, '%k') * 60 ) + DATE_FORMAT(B.Booking_Start_Date, '%i'))) / 60) AS Available_Hours FROM WMS_Bookings B WHERE B.User_ID = '16' AND B.Booking_Status '1' AND NOT ( '2003-10-07' DATE_FORMAT(Booking_Start_Date, %Y-%m-%d) OR '2003-10-07' DATE_FORMAT(Booking_End_Date, %Y-%m-%d) ) _ On the move? Get Hotmail on your mobile phone http://www.msn.co.uk/msnmobile -- 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: sorting/grouping
try select commentid, commentref from comments ORDER by commentref ,commentid DESC; -Original Message- From: Cummings, Shawn (GNAPs) [mailto:[EMAIL PROTECTED] Sent: Thursday, October 02, 2003 2:37 PM To: [EMAIL PROTECTED] Subject: sorting/grouping I have a very simple table. commentid, commentref (each field is an INT) Each record is a comment... commentid is the ID (each new record is a higher #).. and the commentref field if the story the comment refers to. I want to be able to list the stories in order from most recent comment onward. I've tried select commentid, commentref from comments GROUP BY commentref ORDER by commentid DESC; However, the results are NOT showing me the stories in order from most active comments onward... Maybe too much soda and sugar - but any thoughts? -- 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: Tomcat, Connection Pooling, and MySQL
I got it working... unfournately it's on my laptop at home, not here at work with me I think that the issues was that the class names given in the documentation for the jdbc driver for MySQL were wrong look at the listing of the contents of the jar file, and see if you can find the right one... Sorry that I'm being incredibly vague I set it up months ago Dan Greene -Original Message- From: Steven Nakhla [mailto:[EMAIL PROTECTED] Sent: Friday, October 03, 2003 9:42 AM To: MySQL Subject: Tomcat, Connection Pooling, and MySQL Has anyone managed to setup Tomcat to use MySQL for database connection pooling? I've found this document which gives information on it: http://jakarta.apache.org/tomcat/tomcat-4.1-doc/jndi-datasourc e-examples-howto.html However, when I try and run it I get messages about not being able to find the hsql driver class. From searching on Google, it seems that this is a common error, but there are no solutions posted. Has anyone managed to get it up and running successfully? I'd really appreciate any advice! Thanks! Steve Nakhla - Do you Yahoo!? The New Yahoo! Shopping - with improved product search -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Sql question
you want to do insert into mytable (column1, column2, column3) (select thiscolumn, '1', now() from anotherTable); Mysql 3.. I can't figure this one out I need to move data from one mysql table to another The hurdle for me is adding additional column values. Here is what I have. insert into mytable (column1, column 2, column3) (Select thiscolumn From anotherTable), '1', now(); It's the 1 and the now() I can't insert. Anyone have an idea how to do this? --- Keith Schuster Schuster Company LLC ph:704-799-2438 fx:704-799-0779 iChat/AIM:FSHSales WWW.FlagShipHosting.com WWW.Schusterandcompany.com WWW.Vsheet.net -- 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: Representing time sheet data in Mysql
In the last timetracking system I built, I used a 'timeentry' table which was basically timeentry_id (pk) employee_id date num_hours_worked task_id I put a few other indexes in there based on the searching that I had to do, but to each their own... -Original Message- From: Syed Ali [mailto:[EMAIL PROTECTED] Sent: Monday, September 29, 2003 11:04 AM To: [EMAIL PROTECTED] Subject: Representing time sheet data in Mysql Hello, I am trying to store our timesheet data in the Mysql database. I would appreciate suggestions on the database scheme to use. One of the possible ways to do it is to have a table indexed with the employee ID and then having as many columns as there are days in the year. This approach would mean that the table has 200 rows if we have 200 employees and it has 365 columns for each day of the year. Another approach would be to index based on the number of days, i.e., have 365 rows and have as many columns as employees, i.e., 200 columns. If an employee takes half a day off, then I can store an array in the value for that employee ID and that particular day. I am sure there are other approaches to, and I would appreciate any suggestions. Thank you...
RE: Representing time sheet data in Mysql
In the system, there was a task_id associated with pretty much anything an employee could work on per project... In addition there was a project for 'overhead', which had tasks like vacation, sick, maternity, etc... so when the person entered their time, they put 8 hours toward their vacation time task you could tweak the task_id column to become time_type enumeration allowing p,v,s as values if you like -Original Message- From: Syed Ali [mailto:[EMAIL PROTECTED] Sent: Monday, September 29, 2003 2:46 PM To: [EMAIL PROTECTED] Subject: RE: Representing time sheet data in Mysql So if you needed to know status of a work day for an employee day (say 9/1/2003), how did you go about looking it up in your table? Assuming that a employee can be present (p), on vacation (v), sick day (s)? Your table seems to store only the number of hours worked Thank you, Syed Ali (609) 951-2989 -Original Message- From: Dan Greene [mailto:[EMAIL PROTECTED] Sent: Monday, September 29, 2003 11:12 AM To: Syed Ali; [EMAIL PROTECTED] Subject: RE: Representing time sheet data in Mysql In the last timetracking system I built, I used a 'timeentry' table which was basically timeentry_id (pk) employee_id date num_hours_worked task_id I put a few other indexes in there based on the searching that I had to do, but to each their own... -Original Message- From: Syed Ali [mailto:[EMAIL PROTECTED] Sent: Monday, September 29, 2003 11:04 AM To: [EMAIL PROTECTED] Subject: Representing time sheet data in Mysql Hello, I am trying to store our timesheet data in the Mysql database. I would appreciate suggestions on the database scheme to use. One of the possible ways to do it is to have a table indexed with the employee ID and then having as many columns as there are days in the year. This approach would mean that the table has 200 rows if we have 200 employees and it has 365 columns for each day of the year. Another approach would be to index based on the number of days, i.e., have 365 rows and have as many columns as employees, i.e., 200 columns. If an employee takes half a day off, then I can store an array in the value for that employee ID and that particular day. I am sure there are other approaches to, and I would appreciate any suggestions. Thank you...
RE: GROUP BY performance on large tables
a minor tweak should result from doing count(1) instead of count(*) From what I know (not much) the * causes the db to do a secondary lookup for the names of the columns, even though you're not using it at all. As you're selecting every record in the table, I'm pretty sure indexing won't help you... What I've done in the past, depending on how 'real time' the data needs to be, is either 1- schedule a job that runs the query, and puts results in another table, then app hits that table 2- when a record is put into that table, update a second table with new numbers. Ideally, this would be done in a trigger (coming soon to a MySQL version near you) on insert update count_table set total_count = total_count + 1, je_total = je_total + :new.je_total; on update update count_table set je_total = je_total + :new.je_total - :old.je_total; on delete update count_table set total_count = total_count - 1, je_total = je_total - :old.je_total; hope this helps, Dan Greene -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Friday, September 26, 2003 7:34 AM To: [EMAIL PROTECTED] Subject: GROUP BY performance on large tables Hi: Issuing a simple group by like this: select C_SF, count(*), sum(je) as sum_je from kp_data group by C_SF; against a large (1.4G) table holding a 5 mln records with 60 columns takes about 330 secs on my Win2000 development box, a 2.0GHz P4 w/ 1G RAM and an IDE MAXTOR drive. Reducing the column count helped cut time down to 20 secs, but that is not exactly what we need for this OLAP web app. I tried the following optimization hints from the ref manual with moderate results: * adding 'order by null' to avoid the final filesort pass. * increasing buffer sizes to support in-memory operations key_buffer=64M table_cache=64 sort_buffer=64M read_buffer_size=16M How have you been optimizing your queries and DB setups in comparable situations? TIA Jan Torres -- 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: Ideas on creating connections
The general rule of thumb is that connection creation/destruction is the most costly part of approach #2. However, it is unlikely that each connection needs a dedicated connection, and therefore memory resources on the server for the entirity of the client's lifespan... if your clients are firing off many queries back-to-back, buy a lot of memory for your server, and go w/ your option #1. if it's a typical app, where the user queries data, looks through it for a bit, then queries more, yadda, yadda, yadda, then what you're probably going to want to do is #2. Without a central 'app server' as such, you can't really take advantage of connection pooling. The closest you can do is write into your app that if the connection is idle for x amount of time, then it closes the connection, and reestablishes it upon next data request. Hope this helps... Dan Greene -Original Message- From: Director General: NEFACOMP [mailto:[EMAIL PROTECTED] Sent: Thursday, September 25, 2003 11:57 AM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Ideas on creating connections Hi groups, I need some experienced users to guide me on this issue. I am developping an Application that will be accessing a MySQL database through MyODBC. The application is developped using Visual Basic. Since my application will be used simultaneously by more than 200 users, I want to know the implication of using one of the following methods for connecting to the server: 1. At logon, I create a connection to the server and maintain it throught the application life. By the application life I mean that the connection stays open as long as the application is loaded in the memory. So, for a user who is online for 3 hours, the connection is on for those three hours, even if the user goes out for a coffee. 2. I create a connection only when a query is about to be sent to the server. That means, I create a connection when I want to create an ADO recordset and run a query against the server. After running my query, I distroy the connection. Currently I am using the first option since I don't exactly what it is required (time and resources) to make a connection to the server. Will you please tell me how fast is to connect to the server. If you advise me that this method is the best, I will add functions to reconnect a dead connection. Any ideas and advices are highly welcomed Thanks, __ NZEYIMANA Emery Fabrice NEFA Computing Services, Inc. P.O. Box 5078 Kigali Office Phone: +250-51 11 06 Office Fax: +250-50 15 19 Mobile: +250-08517768 Email: [EMAIL PROTECTED] http://www.nefacomp.net/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: select data from two tables without join
it depends on how you want it if you want them consecutively, you probably want to UNION two queries together, but you need to make sure that the column types are identical... i.e. select meeting_name as event_name, meet_date as event_date from meetings where meet_date = curdate() union select train_name as event_name, train_date as event_date from trainings where train_date = curdate() order by event_date -Original Message- From: Tony Thomas [mailto:[EMAIL PROTECTED] Sent: Tuesday, September 16, 2003 9:01 AM To: [EMAIL PROTECTED] Subject: select data from two tables without join I have two tables that are similar, but not related. One is for meetings and the other for training. I'd like to run a query to select data from both based on the date so I can display the information on a web page. Is that possible? It seems unnecessary to run a separate query for each. I'd like to do something like this: SELECT * FROM meetings, trainings WHERE (meet_date = CURDATE()) AND (train_date = CURDATE()) ORDER BY meet_date DESC, train_date DESC; But this doesn't work. Is something like this possible? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: UNION
It's actually mentioned in the user comments in the online manual (was there after the other reply looking for MINUS support), that in a union, in any column that is a literal, the top most query defines the datatype (non-literals obviously use the column type) for the column. I agree it's not expected behavior, but it is documented (classical 'works as designed, but you may not like the design' scenerio) -Original Message- From: Andy Jefferson [mailto:[EMAIL PROTECTED] Sent: Friday, September 12, 2003 6:19 AM To: [EMAIL PROTECTED] Subject: Re: UNION If I do SELECT THIS.PRODUCT_ID,net.ajsoft.WebShop.Inventory.Product AS JPOXMETADATA,THIS.ID FROM PRODUCT THIS LEFT OUTER JOIN BOOK SUBCLASS0 ON THIS.PRODUCT_ID = SUBCLASS0.BOOK_ID WHERE SUBCLASS0.BOOK_ID IS NULL AND THIS.PRICE .15E3 i get ++--+---+ | PRODUCT_ID | JPOXMETADATA | ID| ++--+---+ | 3 | net.ajsoft.WebShop.Inventory.Product | P_005 | ++--+---+ If I also do SELECT THIS.PRODUCT_ID,net.ajsoft.WebShop.Inventory.Products.Book AS JPOXMETADATA,THIS.ID FROM PRODUCT THIS INNER JOIN BOOK SUBCLASS ON THIS.PRODUCT_ID= SUBCLASS.BOOK_ID WHERE THIS.PRICE .15E3; i get +++---+ | PRODUCT_ID | JPOXMETADATA | ID| +++---+ | 1 | net.ajsoft.WebShop.Inventory.Products.Book | P_003 | | 2 | net.ajsoft.WebShop.Inventory.Products.Book | P_004 | +++---+ Yet when I do SELECT THIS.PRODUCT_ID,net.ajsoft.WebShop.Inventory.Product AS JPOXMETADATA,THIS.ID FROM PRODUCT THIS LEFT OUTER JOIN BOOK SUBCLASS0 ON THIS.PRODUCT_ID = SUBCLASS0.BOOK_ID WHERE SUBCLASS0.BOOK_ID IS NULL AND THIS.PRICE .15E3 UNION SELECT THIS.PRODUCT_ID,net.ajsoft.WebShop.Inventory.Products.Book AS JPOXMETADATA,THIS.ID FROM PRODUCT THIS INNER JOIN BOOK SUBCLASS ON THIS.PRODUCT_ID= SUBCLASS.BOOK_ID WHERE THIS.PRICE .15E3; i get ++--+---+ | PRODUCT_ID | JPOXMETADATA | ID| ++--+---+ | 3 | net.ajsoft.WebShop.Inventory.Product | P_005 | | 1 | net.ajsoft.WebShop.Inventory.Product | P_003 | | 2 | net.ajsoft.WebShop.Inventory.Product | P_004 | ++--+---+ Notice that the JPOXMETADATA column is incorrect for PRODUCT_ID=1 and 2. JPOXMETADATA in the first SELECT is shorter than in the second SELECT. So, MySQL just truncate net.ajsoft.WebShop.Inventory.Products.Book, because type of JPOXMETADATA is defined from the first query in the UNION. Swap the queries for correct result. Thanks, thats a workaround and I can move on but certainly is NOT accepted behaviour in RDBMS. Is there a plan for fixing this ? i.e Is there a list of known bugs with MySQL that I can see somewhere so I can report it or monitor it ? -- Andy -- 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: Has anyone heard of SafeKit?
I believe it, as there's an open source project c-jdbc (clustered jdbc) that can do it for x number of machines running any jdbc compatible db's (and they don't even need to be the same type...) -Original Message- From: Neil Aggarwal [mailto:[EMAIL PROTECTED] Sent: Friday, September 12, 2003 12:53 PM To: [EMAIL PROTECTED] Cc: 'Jeff Patterson' Subject: Has anyone heard of SafeKit? Hello: According to this company: http://www.evidian.com/safekit/index.htm They have a way to create a cluster out of two distinct machines running MySQL. Has anyone heard of this? Has anyone used it? Is it just pure marketing hype? Thanks, Neil -- Neil Aggarwal, JAMM Consulting, (972)612-6056, www.JAMMConsulting.com FREE! Valuable info on how your business can reduce operating costs by 17% or more in 6 months or less! = http://newsletter.JAMMConsulting.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SELECT only unique records
SELECT state_ID FROM financial_master WHERE category_ID = '1' becomes SELECT unique state_ID FROM financial_master WHERE category_ID = '1' -or- SELECT distinct state_ID FROM financial_master WHERE category_ID = '1' 2 points ! -Original Message- From: Comcast [mailto:[EMAIL PROTECTED] Sent: Friday, September 12, 2003 2:03 PM To: [EMAIL PROTECTED] Subject: SELECT only unique records I am sure this is a slam-dunk, but I am new to this and stumped ... thanks. I have the following statement, but I need it to pull only unique listings - I get repeated items. SELECT state_ID FROM financial_master WHERE category_ID = '1' -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: RE: joinig tables(non-equal join)
ok... I think this'll work... select unique t1.* from table1 as t1 left outer join table2 as t2 on t2.name = t1.name where t2.name is null -Original Message- From: xander xxx [mailto:[EMAIL PROTECTED] Sent: Thursday, September 11, 2003 2:56 AM To: Dan Greene Subject: Re: RE: joinig tables(non-equal join) Yes, that´s exactly what i get, but that´s not what i want. I wanna get all nanes in table1 that don´t appear in table2, and all names in table2 that don´t appear in table1. That´s why i use in the query. If i use = instead of i get all names in table1 that appear in table2, then, How can i get the oposite? Thanks, and please, forgive my bad english. Alex Sent by Medscape Mail: Free Portable E-mail for Professionals on the Move http://www.medscape.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Foreign key update?
What most data structures do is use foreign keys to the unique numerical id column, in your case, company_id. That way, if the company code changes, the id does not, and therefore, no issues on update... -Original Message- From: Daevid Vincent [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 10, 2003 3:29 PM To: [EMAIL PROTECTED] Subject: Foreign key update? I see there is a way to DELETE or NULL a cascade, but is there a way to UPDATE? Here's what I mean. Given these rough table schemas. I'd like to be able to UPDATE the company_code in the company_table, and have it update the same rep_company_code in the rep_table. Ie. So a company has a certain code, then we change it. I want all the reps to get their codes updated too automagically... Is this possible now or in a future mySQL version? CREATE TABLE company_table ( company_id mediumint(8) unsigned NOT NULL auto_increment, company_name varchar(255) NOT NULL default '', company_code varchar(15) NOT NULL default '', company_referal_code varchar(15) NOT NULL default '', company_phone varchar(20) NOT NULL default '', company_fax varchar(20) NOT NULL default '', company_url varchar(50) NOT NULL default '', company_address1 varchar(70) NOT NULL default '', company_address2 varchar(70) NOT NULL default '', company_city varchar(50) NOT NULL default '', company_state varchar(50) NOT NULL default '', company_zip varchar(50) NOT NULL default '', ) TYPE=InnoDB; CREATE TABLE rep_table ( rep_id smallint(5) unsigned NOT NULL auto_increment, rep_login varchar(15) NOT NULL default '', rep_password varchar(15) NOT NULL default '', rep_company_code varchar(15) NOT NULL default '', rep_fname varchar(20) NOT NULL default '', rep_lname varchar(20) NOT NULL default '', rep_title varchar(50) NOT NULL default '', ) TYPE=InnoDB; -- 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: How To Create Users In MySQL?
not to respond with, 'read the manual', but There is an entire section of the manual devoted to user management, I would start at www.mysql.org click on the documentation link on top, and start there... (user account management is : http://www.mysql.com/doc/en/User_Account_Management.html ) -Original Message- From: Caroline Jen [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 10, 2003 4:33 PM To: [EMAIL PROTECTED] Subject: How To Create Users In MySQL? I have the MySQL-3.23.55 installed in my PC. Therefore, I am the DBA without the required DBA knowledge. First, how do I create users in the MySQL database? Second, how do I grant table creation privilege to users? Is GRANT ALL PRIVILEGES ON databasename TO someuser IDENTIFIED BY 'somepassword'; the correct command? Thanks for your guidance. __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to get previous and next result
Having developed such an app myself (albeit in Oracle where the wonder of 'connect by'
exists which according to 'to-do' lists is coming for MySQL yippie!), this is what
I recommend...
1- if your forum is 'threaded', i.e. a message is in response to another, you can use
that linking to get your next/previous post based on post date, and the necessary
'in-reply-to-id' field.
2- if not, use a post_date field (which you should probably have anyway) and pull up
the next record with post_date {current msg post_date} in same forum, and last
record by post_date {current msg post_date} in same forum
-Original Message-
From: Maria Garcia Suarez [mailto:[EMAIL PROTECTED]
Sent: Tuesday, September 09, 2003 11:53 AM
To: [EMAIL PROTECTED]
Subject: How to get previous and next result
Hi there!
I'm currently developing a web where we let users
create their own forums. All the messages (wherever
they are posted) are stored in the same mysql table.
When users read a certain message I would like to
display the previous and next message in that forum.
Since all the messages (of the different forums) are
stored in the very same table I don't find how to
guess what's the ID of the previous and next message.
Is there any way to find those IDs?
Thanks.
Kisses,
Maria
__
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
http://sitebuilder.yahoo.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Fulltext search from multiple tables...
I have this strange feeling of deja-vu This was just posted to the list recently (last week?), and currently, you cannot do cross-table full-text indexes, excepting boolean text searches, which would be slow. I have this strange feeling of deja-vu Dan Greene -Original Message- From: Kutt Niinepuu [mailto:[EMAIL PROTECTED] Sent: Tuesday, September 09, 2003 2:40 PM To: [EMAIL PROTECTED] Subject: Fulltext search from multiple tables... Hello everybody! Fulltext search fulfills all my needs, only it would be great if someone walked me through using multiple tables with this feature. How to address this MATCH to indexes on different tables? Things like MATCH(table1.column, table2.column) give me errors. Thanx in advance, -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Query pages by Alphabet
And although it blows out your single-query theory further out of the water, you could query your listing for a count of each starting letter of the last name, getting something like this: [syntax will be wrong (closer to Oracle syntax), but I don't have a mysql installation at work to get it right] select substr(upper(ln),0,1), count(1) from directory group by substr(upper(lname),0,1) (I don't think MySQL needs the group by clause (it implies any non-specified non-group columns), but it's good practice for others to be able to maintain your code) A 2 B 15 C 4 E 2 (note skipping D, as there may be some letters that don't appear...) you could use your front/middle-end to go through this list first, grouping out your letters for the letter-specific queries (supplied by Brent below). I would reccomend caching out results of this query, as it won't change often enough to skew the results (likely) if you want to limit to 10 per page, you are going to need further pagination for entries with more than 10 entries per letter... -Original Message- From: Brent Baisley [mailto:[EMAIL PROTECTED] Sent: Tuesday, September 09, 2003 3:38 PM To: Dave Dash Cc: [EMAIL PROTECTED] Subject: Re: Query pages by Alphabet I'm not quite following what you are asking. If you want to limit the result to only 10 items per page, you're going to need to come up with some paginating code (it actually isn't that hard). You could easily get more than 10 names starting with a single letter. If you want to create specific links that show only name beginning with a letter or set of letters, then you need to do a search: SELECT fn, ln FROM directory WHERE ln like A% ORDER BY ln,fn LIMIT 10 or for a group of letters SELECT fn, ln FROM directory WHERE ln between BAAA AND CZZZ ORDER BY ln,fn LIMIT 10 That's actually kind a fudge on the search for a group of letters. Technically you should search for between A and D to get all names beginning with B-C, but I think it reads better this way from a coding readability standpoint. On Tuesday, September 9, 2003, at 02:55 PM, Dave Dash wrote: I have a page that is a directory of names ordered by lastname, firstname (e.g. SELECT fn, ln FROM directory ORDER BY ln, fn LIMIT 0,10). I have it paginated so that there are 10 results per page. What I'd like to do instead of having page numbers (which can be unhelpful when trying to page through people's names) is be more like a phone book and let people click on links that are the first letters of their last names For example, let's say my result set for SELECT fn, ln FROM directory ORDER BY ln, fn LIMIT 30, 10 is Jackson Johnson Knutson Kraig Liver Lombard Marx Maxx Milton Nixon The page link would be J-N I know how to get the letters for one page (well I think I do at least), but I want to get them for all pages So basically I'd have something like this for my page list: A B-C D E-G F-H I J-N O-Z and clicking on each page would result in entries only from that letter. The trick is I don't want more than 10 entries a page. Is there an easy way to do this? Possibly in a single query? Thanks -dd -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Distributing a DB
If you're accessing your db through JDBC, an idea that I've been following is the c-jdbc project... http://c-jdbc.objectweb.org/ it's software raid clustering for databases... it's still in beta, but it looks very promising for easy clustering. Combined w/ MySQL's master/slave setup, it could be a very robust solution... it basically creates a virtual db out of the connected machines, to the point where you can have different tables on different boxes. It's at least worth looking into -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, September 09, 2003 4:36 PM To: [EMAIL PROTECTED] Subject: Distributing a DB Hi, We are trying to find a way to distribute a large MySQL database across several systems, each configured as a master to a slave. At this point we are tossing architectural ideas around and here is where we are right now: Primary (Master) MySQL DB | +++---+ | | | | partitionA-G partitionH-M partitionN-SpartitionT-Z | | | | | | | | (MySQL Replication) | | | | VV V V slaveA-GslaveH-M slaveN-S slaveT-Z (slaves) Machines - Primary DB dual 2.2+ Ghz/1Gb RAM and 250Gb of RAID 1 storage, dual Gb eth (Gb Ethernet Switch) PartitionA-G dual 2.2+ Ghz/2Gb RAM and 2Tb of RAID 5 storage, dual Gb eth PartitionH-M dual 2.2+ Ghz/2Gb RAM and 2Tb of RAID 5 storage, dual Gb eth PartitionN-S dual 2.2+ Ghz/2Gb RAM and 2Tb of RAID 5 storage, dual Gb eth PartitionT-Z dual 2.2+ Ghz/2Gb RAM and 2Tb of RAID 5 storage, dual Gb eth (Gb Ethernet Switch) SlaveA-G dual 2.2+ Ghz/2Gb RAM and 2Tb of RAID 5 storage, dual Gb eth SlaveH-M dual 2.2+ Ghz/2Gb RAM and 2Tb of RAID 5 storage, dual Gb eth SlaveN-S dual 2.2+ Ghz/2Gb RAM and 2Tb of RAID 5 storage, dual Gb eth SlaveT-Z dual 2.2+ Ghz/2Gb RAM and 2Tb of RAID 5 storage, dual Gb eth The idea is that users would typically connect to the PartitionA-Z for normal read access. Overflow queries would connect to the SlaveA-Z. Update processes would connect to the Primary DB machine. For what its worth, we will be running RH 9.0, MySQL 4.0??? (depending on features we need to accomplish this); no two-phase commit transactional support required, no stored procs. I am not certain about how to split the database across multiple machines (or is can be done). we are also toying with the idea of using a hardware load balancer as a fabric of sorts to route traffic and possibly bi-directional replication shudder. Has anyone ever tried this? Have any thoughts? Thanks in advance. Tony -- 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: Need help with the download of the Mysql GUI
I would use MySQL CC (command center, I think...)... I seem to remember someone mentioning that mysql gui is discontinued... CC is available from the mysql.org site, and is very easy to install on windows xp (it's on my laptop...) -Original Message- From: Liwen Han [mailto:[EMAIL PROTECTED] Sent: Monday, September 08, 2003 2:34 PM To: [EMAIL PROTECTED] Subject: Need help with the download of the Mysql GUI To whom it may concerned, I am a web development support person working for North Carolina AT State University. I am involved in a library project right now which requires a MySQL free software installed on my computer so I can create a mysql database for the project. By searching the internet I found that MySQL GUI would be the perfect software for me to try. But I am not quite sure how to download it on my computer which has windows XP operating system. Could you give me instructions on how to download the software so I can start using it as soon as possible because the project deadline is getting closer. Thanks a lot! Looking forward to hearing from you! Liwen -- 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: Round Question
what I've done is select ceil(value) (not sure if ceil is the function on MySQL, but there is a ceiling function, I'm sure...) -Original Message- From: Fabio Bernardo [mailto:[EMAIL PROTECTED] Sent: Friday, September 05, 2003 2:59 PM To: Mysql (E-mail) Subject: Round Question I write this command: Select round(1.1) and obtain 1 as answer Is there a round command to obtain 2 as answer. I mean, in Excel this command is knwon as RoundUp! thanks a lot -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Auto Increment ID of Inserted Row
well, that'll teach me not to update my java api bookmarks to 1.4 the getGeneratedKeys() calls works like a charm! Thanks for the help -Original Message- From: Mark Matthews [mailto:[EMAIL PROTECTED] Sent: Tuesday, September 02, 2003 4:09 PM To: Dan Greene Cc: [EMAIL PROTECTED] Subject: Re: Auto Increment ID of Inserted Row -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Dan Greene wrote: (newbie to MySQL) I've been banging my head against the wall on this one for a bit now, and I understand that last_insert_id() is per-connection based, but most webapps are connection pooled (simple) or clustered (harder). What are my options to get the id of the inserted row in a webapp? As a side note, I'm using JDBC to access the DB. my thoughts: 1- use an innoDB table, start a txn (lock the table), insert, select max(id_column), end txn (unlock the table) 2- make an id pool table (innodb), have app server grab pool of ids at startup, and when pool is empty in similar manner (lock, update, select, unlock) 3- look to other product (don't make me do this one ;) ) 4- continue to bang head against the wall please cc me on any replies, as although I sent a subscription request, I'm not on list yet... Is there a reason you don't hold on to the same connection during the lifespan of one of your web 'transactions'? Also, to avoid a round trip to the server you should use Statement.getGeneratedKeys(). -Mark - -- Mr. Mark Matthews MySQL AB, Software Development Manager, J2EE and Windows Platforms Office: +1 708 557 2388 www.mysql.com Are you MySQL Certified? http://www.mysql.com/certification/ -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.3 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQE/VPjstvXNTca6JD8RAs3RAKC6T+RQnStioRLAJ1cwHHsBMwuPxgCeJU+N co0jO0c6pCDxIwxMAHaHkCk= =Nkgp -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL 4.0.14 stops responding to PHP 4.3.2
not knowing anything about PHP (java geek, myself), I'm guessing that your connections are timing out, and php is not configured to try to reconnect Another option is that you are not closing your connections, so you can't get a new connection to do anything with. Also new to MySQL, I'm not sure of a command that you can use to show current connections but I'm sure it's out there... with the popularity of PHP, I have to think it has built-in connection pooling facilities that may help resolve these issues... Realizing that a Java/Oracle knowledgebase is somewhat lacking in PHP/MySQL, Dan Greene -Original Message- From: Parker Morse [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 03, 2003 11:43 AM To: [EMAIL PROTECTED] Subject: MySQL 4.0.14 stops responding to PHP 4.3.2 I've been using MySQL and PHP for a while in a shared hosting environment, but recently we shifted to a co-located server, so I am new to administering mysqld. Periodically our PHP sites will fail to connect using mysql_pconnect(). We currently have three sites on the colo box (two more are waiting on shared hosting until I can solve this problem) and they all fail at once. mysqld is still running, though. If I shut down mysqld and restart, they are able to connect again. This makes me think the problem is with how I have MySQL configured. However, nothing useful is being logged anywhere in the /var/log heirarchy, so I can't figure out what's going wrong. Here's the configuration: MySQL Ver 12.21 Distrib 4.0.14, for pc-linux (i686) PHP 4.3.2 Red Hat 9.0 I am starting MySQLd with mysqld_safe --user=mysql --bind-address=127.0.0.1. I don't have a my.conf file, so I seem to be running with defaults. It's hard to get a picture of what's happening right before these lockups, but when I look after a lockup, load on the server doesn't appear to be an issue. (I haven't seen load average go over .50 except during the initial fcheck run, and most of the time it's 0.00.) I have some mysql status snapshots from before and after a lockup. I've also been running mytop thanks to a suggestion on this list yesterday. If anyone thinks that information would be helpful, I can supply them. Thanks for anything that might give me a toehold on this problem. pjm -- 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: How much user LOAD can Mysql bear???
Ahh... the proverbial 'how many licks does it take to core the database' There is no straightforward answer. That's the bad news. However, the basics are identical for all databases basically, though, the more memory you have, the more concurrent connections you can handle. The more CPU power you have, the more complicated queries you can perform. Extra memory, after covering maintaining your connections, goes to assisting query performance. -- MOST IMPORTANT -- No amount of hardware can make up for crappy SQL. I've seen single queries lock down an enterprise level Oracle database. Always tune your queries for performance, and index your tables on frequently where'ed columns (primary keys, common lookups) Taking that, and realizing that in most applications with user interfaces, your connections are idle most of the time, connection pooling was created. This way, you maintain a set number of connections (most systems allow flexibility, such as keep at least 5 connections open, and grow the pool as needed, up to 15 connections). You app server (tomcat, php, etc...), or connection manager (PoolMan), then maintains those connections, not your code directly. You 'borrow' an existing idle connection, use it, and return it to the pool for another user/component to use. With connection pooling, you 'virtual' number of connections skyrockets, without giving up more memory than needed, which will help query performance. Hope this helps somewhat... Last comment... What do you mean by 'reasonable hardware'? I think my old C-64 is reasonable hardware (heck... it ran a windows environment on 2 low-density floppys, no hard drive, and 64k of memory... ahhh Geos) -Original Message- From: William R. Mussatto [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 03, 2003 6:13 PM To: [EMAIL PROTECTED] Subject: Re: How much user LOAD can Mysql bear??? Tariq Murtaza said: Thanks Fortuno, Adam Actually, I was thinking about concurrent users that mysql can handle, provided with reasonable hardware. Looking for comments / suggestions. Regards, Tariq Fortuno, Adam wrote: Tariq, Check the list's history - this is actually a frequent question. The answer everyone is about to give you is generic. MySQL's ability is havily based on the hardware and OS it runs on. Therefore, a multi-processor machine with lots of memory can handle more than a single processor workstation with 256 MB of RAM. The faster the machine the faster MySQL... etc. Be more specific, whats the hardware and software. How much information are you storing in the DB? Any replication? Regards, Adam -Original Message- From: Tariq Murtaza [mailto:[EMAIL PROTECTED] Sent: Tuesday, September 02, 2003 8:40 AM To: [EMAIL PROTECTED] Subject: How much user LOAD can Mysql bear??? Hi All! How much user LOAD can Mysql bear before die. Regards, Tariq You haven't changed the question. The number of concurrent users is the same as the number of connections...if they are TRUELY concurrent. That is is they all hit the submit button at the same time. You set the number on connections. Each connection takes up a bit of memory, even if idle (php and mod_perl hold open connections. .jsp may or may not. Your response time (how long is acceptable) will depend on you actual database and questions you ask it as well as the hardware. It might help if you told us what hardware you are planning to use or what kinds of questions or environment it will support. Then the list can get more specific. William R. Mussatto, Senior Systems Engineer Ph. 909-920-9154 ext. 27 FAX. 909-608-7061 -- 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: Auto Increment ID of Inserted Row
(newbie to MySQL) I've been banging my head against the wall on this one for a bit now, and I understand that last_insert_id() is per-connection based, but most webapps are connection pooled (simple) or clustered (harder). What are my options to get the id of the inserted row in a webapp? As a side note, I'm using JDBC to access the DB. my thoughts: 1- use an innoDB table, start a txn (lock the table), insert, select max(id_column), end txn (unlock the table) 2- make an id pool table (innodb), have app server grab pool of ids at startup, and when pool is empty in similar manner (lock, update, select, unlock) 3- look to other product (don't make me do this one ;) ) 4- continue to bang head against the wall please cc me on any replies, as although I sent a subscription request, I'm not on list yet... previous info- In the last episode (Sep 19), Steven Kreuzer said: What is the SQL to get the created AutoInc ID from a row that I have just inserted? SELECT MAX(id_field) FROM table Nope. If someone else inserted a record between the time you inserted yours and the time you run that select, your answer will be wrong. Use LAST_INSERT_ID(), or whatever construct your language provides for retrieving it without doing another query. -- Dan Nelson [EMAIL PROTECTED] -- Daniel Greene Manager, Software Development Chelsea Interactive [EMAIL PROTECTED] (571)203-4105 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Re-establishing nuked log file
I don't know the answer to your question, but as a side note, I've always found cat'ing /dev/null into a file to be safer if the file may be in use cat /dev/null foo.log -Original Message- From: Jesse Sheidlower [mailto:[EMAIL PROTECTED] Sent: Tuesday, September 02, 2003 10:27 AM To: [EMAIL PROTECTED] Subject: Re-establishing nuked log file I recently restarted my MySQL server (4.0.10 in this case) with the general query log enabled, to help out with some debugging and optimization issues. After looking at a batch of these, I then deleted the log file directly, with rm foo.log, assuming that it would be re-generated as soon as the next query came in. It was not. Is there any way to get logging restarted without stopping and restarting the server itself, which is live and which I'd prefer not to interrupt? Thanks. Jesse Sheidlower -- 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: Large query techniques
I may be missing something, but why not just do: SELECT CustomerName, ApplicationName, Status, COUNT(1) AS Count FROM LogMessage GROUP BY Status, CustomerName, ApplicationName with rollup; which should return all the data you need in 1 query, which has got to run faster than 4 seperate queries... -Original Message- From: Stephen McMullan [mailto:[EMAIL PROTECTED] Sent: Tuesday, September 02, 2003 12:59 PM To: [EMAIL PROTECTED] Subject: Large query techniques Assuming that I had a database containing a single table used to record an audit trail of messages originated from customers and their applications like so: CREATE TABLE `LogMessage` ( `MessageID` int(11) NOT NULL auto_increment, `CustomerName` varchar(100) default '', `ApplicationName` varchar(100) default '', `MessageText` text, `Status` int(11) default '0', PRIMARY KEY (`MessageID`) ) TYPE=MyISAM; Each message could be in 4 different states (according to the value of the Status column) 0, 1, 2, 3 What would be the best way to query the table in order to generate counts of the number of messages in each state PER customer and application? I was thinking about: SELECT CustomerName, ApplicationName, '0' AS CountType, COUNT(*) AS Count FROM LogMessage WHERE Status = 0 GROUP BY CustomerName, ApplicationName UNION SELECT CustomerName, ApplicationName, '1' AS CountType, COUNT(*) AS Count FROM LogMessage WHERE Status = 1 GROUP BY CustomerName, ApplicationName UNION SELECT CustomerName, ApplicationName, '2' AS CountType, COUNT(*) AS Count FROM LogMessage WHERE Status = 2 GROUP BY CustomerName, ApplicationName UNION SELECT CustomerName, ApplicationName, '3' AS CountType, COUNT(*) AS Count FROM LogMessage WHERE Status = 3 GROUP BY CustomerName, ApplicationName What would be the correct way to index the table? KEY `comboindex1` (`CustomerName`,`Status`), KEY `comboindex2` (`CustomerName`,`ApplicationName`,`Status`), KEY `status` (`Status`), KEY `customername` (`CustomerName`), KEY `applicationname` (`ApplicationName`) I could have up to 30million rows in my table and am looking for some fundamental techniques in order to query such a large table in the manner described above. Any pointers or help would be much appreciated. Regards Stephen McMullan ANAM Wireless Internet Solutions http://www.anam.com +353 1 284 7555 Castle Yard, Saint Patrick's Road, Dalkey, County Dublin, Ireland -- 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]
