Re: Passing single quote within a query
Nestor,
You would do better to use prepared statements.
When a parameter is substituted into the prepared statement it is
automatically quoted -- and you are protected from SQL Injection
attacks. The reason your script is failing is that the single quote in
$field is being interpreted as the SQL statement's closing single quote
in MySQL. Thus, data in $field is being treated as potential SQL code.
Very dangerous.
In your example, if someone is able to insert ');update user set
password=PASSWORD('cracked into $field you're in trouble. That's a
simple but poor example of what can be done. People intent on breaking
your system don't need to know what your schema is to do you harm.
Prepared statements and parameter substitution is a function of your
language's DB library, so you need to find out how your language does this.
HTH,
Robert J Taylor.
There's an article at MySQL.com on Prepared Statements for more reading:
http://dev.mysql.com/tech-resources/articles/4.1/prepared-statements.html
Nestor Florez wrote:
Hello world,
I am passing a a string Joe's car as part of the SQL query to be inserted
to a String field.
$field=Joe's car;
$query=insert into mytable values('$field');
Do you generally change the quote to a double quote before inserting
or what is the recomended way, because otherwise the quote will
terminate the query statement.
Thanks,
:-)
Néstor Alberto Flórez Torres
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: IMPORTING
On Friday 23 July 2004 11:14 am, [EMAIL PROTECTED] wrote: Dear friends, When I import records fron csv file.I window pops up, stating total number of records imported, however when I check table nothing has been imported. You are using some kind of middle-man program to populate a database. In fact, your description sounds awfully similar to how Microsoft Access works. What is the program you use to do the import? Can you describe the precise steps you take? Any guidance, Tell us where you are so we can tell you how to get where you want to go. Robert J Taylor -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: IMPORTING
On Friday 23 July 2004 03:02 pm, you wrote: sqlog program What is sqlog program? Google shows me nothing familiar: http://www.google.com/search?q=sqlog+program You need to provide more information if you want help. How do you run this? What OS? What options are you selecting? How do you connect the sqlog program to MySQL? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Question
On Sat, 26 Jun 2004 22:19:02 +0200, Schalk [EMAIL PROTECTED] wrote: Why is the following query retuning doctype's different to what is asked for? SELECT * FROM documents WHERE jstate = 'California: State Court' AND doctype = 'Verdict' Any ideas? What is being returned? Not knowing what it is that is being returned, how the documents table is defined or what the data types for the fields are it is hard to guess, except to note: 'Verdict' will also match 'verdict' and 'vErDicT' using default settings, see: http://dev.mysql.com/doc/mysql/en/Case_sensitivity.html Kind Regards Schalk Neethling With a bit more info perhaps I could help more. Robert J Taylor [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Stop Scrolling
[EMAIL PROTECTED] wrote: Ultimately simple, but I don't find the answer: how to stop a list from scrolling to the bottom when the command SHOW TABLES is entered. I assume you are referring to the command line of the mysql client? I use: mysql\P more or mysql\P less depending on my mood. Other client tidbits can be found using \h or \? at the * prompt. Ken HTH, Robert J Taylor [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Feature Request: UPDATE 'error codes' or mysql_affected_rows() to be more accurate
Does REPLACE INTO not work in your case? | | |REPLACE| works exactly like |INSERT|, except that if an old record in the table has the same value as a new record for a |PRIMARY KEY| or a |UNIQUE| index, the old record is deleted before the new record is inserted. See section 14.1.4 |INSERT| Syntax http://dev.mysql.com/doc/mysql/en/INSERT.html. http://dev.mysql.com/doc/mysql/en/REPLACE.html HTH, Robert J Taylor [EMAIL PROTECTED] Daevid Vincent wrote: I'm developing a program where I try an UPDATE ... LIMIT 1 and if mysql_affected_rows == 0, then I know nothing was updated and so I do an INSERT. I find this is much cleaner and the majority of the time, I'm going to do UPDATES, so I didn't want to waste a SELECT (even though I hear they're cheap). I'm doing these queries several times per second. however... Of course UPDATE doesn't 'ERROR if the record doesn't exist, it just didn't do anything (therefore that's why I use the mysql_num_rows() to check). The problem is that if I am actually doing an UPDATE to a record where nothing actually changed in the existing record, I still get mysql_affected_rows() equal to 0. *grrr*. It would be extremely useful to somehow get a result of maybe -1 if I tried to update a record that didn't exist, versus a result of -2 if I tried to update a record that did exist, but mySQL didn't change anything. I don't know exactly what I'm asking for other than a way to know the difference... At the very least, it seems to me that if I update a record that exists already (even if no data changed), I should still get mysql_affected_rows() 0 (since in theory I matched something, even if mySQL behind the scenes didn't change the data). Out of curiosity, if I have a TIMESTAMP column, would that solve my problem, since mySQL should be forced to update that TIMESTAMP right?? [btw, I know I could try this idea, but I'm home and my code is at work right now and I just had the idea! ;-] http://daevid.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Foreign Key Constraints
[EMAIL PROTECTED] wrote: Hi, I am trying to use the foreign key constraints from InnoDB and creating indexes is a requirement for foreign key. The problem is that by creating index for my foreign key, it does not allow my foreign key to have null or blank values which my records will have. For eg. a BorrowerID is a foreign key on a Book table, but when the book is not borrowed, the BorrowerID will be null and I can't seem to import the data containing null values for the foreign key. Is there a way to solve this? Thanks. If you have a foreign key constraint that requires the foreign key field to be populated then you effectively have a MANY TO ONE with Min of 1 and Max of 1 relationship between the book table and the borrower table -- thus you cannot have a book without a borrower. That is a logical problem, and the one you are describing. Separate the BorrowerID from table `book`. Make a table called, oh, `book_borrower` and put BookID BorrowerID DateOut DateDue DateReturned ... Then you can query for borrowed books using a join like: SELECT a.BookID, b.BorrowerID, c.FullName from ( book a inner join book_borrower b on a.BookID = b.BookID ) inner join borrower c on b.BorrowerID = c.BorrowerID WHERE b.DateReturned is null Available books could be found: SELECT a.BookID from book a left join book_borrower b on a.BookID=b.BookID WHERE b.BookID is null and b.DateReturned is null (Which says show me all the book.BookID that fail to have a non-returned book in the book_borrower table. Usually I put the and b.DateReturned is null with the join statement, instead of the WHERE clause.) Now you can have a book without a borrower and can easily track borrowing history for books and borrowers. Be sure to index the fields you'll be using as selection criteria! HTH, Robert J Taylor [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Could not start MySQL after reinstall - Can't open privilege tabl es: Table 'mysql.host' doesn't exist
Check permissions on the mysql\data directory and files/folders below for the MySQL process/user (sorry for not knowing the right Windows terminology, I'm not a Windows user). HTH, Robert J Taylor [EMAIL PROTECTED] michael_wu[§d§»¹F] wrote: Hello, I run into a problem after re-installing mySQL 4.1.1 today. When I tried to start the service on my Windows2K, I got the following error: 040527 15:37:49 Fatal error: Can't open privilege tables: Table 'mysql.host' doesn't exist Can some one tell me how to solve the problem? Thanks in advance! Michael Wu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: BLOB's - General Guidance
Joshua J. Kugler wrote: There is one instance in which it is *not* convenient to store in seperate files: when you are exporting to another machine (maybe a sub set of data from an internal server to an external web server) or doing replication. Very true. We use MySQL to store our blobs for a JBoss application for this reason (among others; the main one being the system architect likes it that way -- it's good to be king!). j- k- On Wednesday 19 May 2004 01:01 pm, Greg Willits said something like: On May 19, 2004, at 1:19 PM, David Blomstrom wrote: I'd like to get some feedback on storing images in MySQL databases. The stuff I've read so far suggests that it's fairly difficult to work with images in MySQL, and they also slow down databases. One thing to remember when you have a blob (or text, iirc) column in a tuple (row, sorry; just Codding around and Dating myself) is that when MySQL examines the row the entire blob is loaded into memory even if that particular column isn't referenced in the query. So, use a split table for blobs -- a main table with the blob attributes for searching and a dependent table with its primary key set as a foreign key to the main table holding the blob for direct access to the blob as needed. This helped us emmensely. I've also read that there isn't much you can do with BLOB's that you can't do with PHP manipulating images stored in an ordinary folder. So I just wondered if BLOB's are worth my time. For example, I'm working on a database with information about the 50 states. If I have maps of each state, pictures of each state's capital, etc., is there some BLOB feature that I would find really useful? In your case, a field holding the data particulars with a middleware parsible URN (a local filesystem path or remote URL, et al) to the blob or text body should suffice. All conventional wisdom I've ever come across for this type of application is that there's no advantage to keeping the image in the db itself. Just keep them as files on the server, store a filename /or location in the db if necessary, and use your middleware to display the images. Its faster, easier to maintain, and easier to backup. IMO, storing images in the db just bloats the file and complicates all the backup issues. -- greg willits Luckily, MySQL handles the bloat quite well in our experience, with the caveat that we don't include the blob in the search details table HTH, Robert J Taylor [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: FW: Could not start MySQL after reinstall - Can't open privilege tables: Table 'mysql.host' doesn't exist
Did you change the permissions for the files to include read/write/change for the user/account MySQL uses on your system? This doesn't require deleting, as far as I recall Windows permissions... Oh, can you verify that the file host under directory mysql DOES exist? If so, it's really most likely a permissions problem. MICHAEL_WU wrote: Since the database I created was not critical, I could simply throw it away. Therefore, delete the whole MySql installation directory and unzip the alpha release zip file again to create the installation directory tree. However, the following error persists: Fatal error: Can't open privilege tables: Table 'mysql.host' doesn't exist Everything should be wiped out I thought. Does MySQL saves some data somewhere other than the installation directory? More help, please? Michael Wu -Original Message- From: Robert J Taylor [mailto:[EMAIL PROTECTED] Sent: Thursday, May 27, 2004 4:52 PM To: michael_wu[¡±d¡±?1F] Cc: [EMAIL PROTECTED] Subject: Re: Could not start MySQL after reinstall - Can't open privilege tabl es: Table 'mysql.host' doesn't exist Check permissions on the mysql\data directory and files/folders below for the MySQL process/user (sorry for not knowing the right Windows terminology, I'm not a Windows user). HTH, Robert J Taylor [EMAIL PROTECTED] michael_wu[§d§»¹F] wrote: Hello, I run into a problem after re-installing mySQL 4.1.1 today. When I tried to start the service on my Windows2K, I got the following error: 040527 15:37:49 Fatal error: Can't open privilege tables: Table 'mysql.host' doesn't exist Can some one tell me how to solve the problem? Thanks in advance! Michael Wu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Duplicate does not exist
fr0g wrote: Good evening all. I'm facing a problem with a specific table and the uniques that it has. I have a table, hosting data of peoples names. Some of it's columns are, name_english, surname_english, name_original, surname_original, name_greek, surname_greek. I have as unique each combination of columns of the same language (i.e. name_english, surname_english, unique, etc). As I am Robert James Taylor I can give anecdotal evidence that full name does by no means make a good candidate key for a database of any population larger than...sayGeorge Foreman's immediate family (he named all his boys the exact same name, if you didn't catch the reference). More than once I have entered confusing periods of mis-identification due to database designers relying on name alone to match people. In fact, a short tale that is true. I moved from one West Coast state to another in 1998 and was denied a drivers license after passing the tests will flying colors because I had a DUI conviction in an East Coast state. The other Robert James Taylor, who was born the same year and day I was, fortunately had a different Social Security Number (which is not a panacea either...but I digress). This may sound far-fetched and unlikely to happen again but it was real, scary and made me curse database designers at the DMV. Please don't do that. Thanks. Robert James Taylor West Coast and Sober [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysqld stalls
Mark Susol|Ultimate Creative Media wrote: I've been trying to fine tune my mysqld settings in my.cnf on a very busy server. It seems to be doing fine, as in the server loading. BUT every so often I see the number of processes spike and then it sesms mysqld is unresponsive through httpd and I end up having to restart mysqld to get it going again. Regarding the I see the number of processes spike phrase what processes spike and how do you observe this happening? (Oh, and Linux, UNIX, Windows..?). If you mean the load average seen in w, uptime or top spikes, that is one thing. If you mean the number of MySQL threads grows suddenly, that could be another. Tells us about your machine (cpu, hd, memory, OS). Then, did you enable the slow query log? How about binary logs? Can you isolate the SQL statements in effect at the time of the process spike? Depending on what you mean I've seen servers broght to their knees by single inefficient queries that don't use indexes for joins or select criteria (where clauses) or use open ended like queries on varchar fields for joins, etc. Indexing join fields (keys) has dramatic impact on server performance, positively (indexing too much causes other problems, but I see that much less than not enough or improper indexing). What settings should I be tweaking ? None, yet. Let's nail down the description of the problem and then see if we need to investigate some query optimization. Mark Susol HTH, Robert J Taylor [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Determine table type and comments
Scott Haneda wrote: I can not find this in the docs. I can see in phpmyadmin what a table comment is and what type of table it is, such as MyISAM etc. From the mysql shell, how do I get to this info? Try mysql SHOW CREATE TABLE tablename; (Not sure if that covers comments since I've, yes I admit it, never used comments on tables directly.) HTH, Robert J Taylor [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Used EXPLAIN, have indexes - query still surprisingly slow
Andy Henshaw wrote: I have the following query that takes anywhere from 1 to 3 seconds to run. I would expect it to run in less than 1/2 a second (and I really need it to do so). I've added the appropriate indices and tried EXPLAIN and ANALYZE; but, I'm not getting anywhere. SELECT message.id FROM message, message_thread WHERE message.id = message_thread.message_id ANDmessage_thread.thread_id = SOME_CONSTANT_NUMBER where SOME_CONSTANT_NUMBER is an integer literal, like 16400 (and no, I'm not putting quotes around the number). Here are the two tables involved: message table -- Field TypeNULLKey Default Extra -- id int(11)PRI NULL auto_increment msgtype_id int(11)MUL NULL content blobYES precedence varchar(255)YES Not sure if this is the cause, but from personal experience (lots of rows and big blobs) consider the following: Every time you search the message table the BLOB is being loaded into memory even if it is not being returned in the query. Instead, break apart this table into 2: message(id, msgtype_id,precedence) message_blob(id, content) Then select the row you want from message_blob from a list of rows from message. message_thread table -- Field TypeNULLKey Default Extra -- id int(11)PRI NULL auto_increment message_id int(11)MUL -1 thread_id int(11)MUL -1 contact varchar(255)YES The message table has 1,117,213 records and the message_thread table has 2,563,893 records. At most, each query will return 200 records. Here is the output of the explain function (turned sideways): --- table : message_thread message --- type : ref eq_ref --- possible_keys : thread_id, PRIMARY message_id --- key : thread_id PRIMARY --- key_len : 4 4 --- ref : const message_thread.message_index --- rows : 200 1 --- Extra : Using where Using index --- This is running on a 2.2 GHz Pentium 4, NTFS File system, MS Windows 2000, MySQL 4.0.18. Each table is an InnoDB type. I've tried a combined thread_id/message_id index in the message_thread table; but, that did not seem to help at all. Any help would be appreciated. HTH, Robert J Taylor [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT almost every column
John Mistler wrote:
Hmmm. The reason I asked was that the last column in the table is TEXT, and
might contain up to 5000 text characters. I'm trying to make the query as
efficient as possible, and I don't know if that much data will make a
noticeable speed difference?
Thanks,
John
on 5/14/04 2:31 PM, Justin Swanhart at [EMAIL PROTECTED] wrote:
John,
In that case even if you only grab one field in the select from the row
the entire Text and/or Blob will be loaded into memory as a side benefit
(/sarcasm).
Assuming a table like:
my_table(id, field1, field2, ..., field49, TEXTField)
You'll do much better by making two tables:
my_table(id_field, field1, field2, ..., field49)
my_table_text(id_field, TEXTField)
Then search on my_table and only pull my_table_text rows by exact match
on the id_field (making both id_fields primary keys in their
respective tables, of course).
HTH,
Robert J Taylor
[EMAIL PROTECTED]
--- John Mistler [EMAIL PROTECTED] wrote:
Is there a SELECT statement, if I have a table with
50 columns, to select
every column EXCEPT the last one? Can I do this
without typing the name of
all 49 columns?
If so, then what if I want to exclude the last TWO
columns?
Thanks,
John
There is no construct in SQL to select X number of
columns from a table.
The traditional answer to this question would normally
be use views, but since MySQL doesn't support them
that doesn't help you very much.
Unless the extra columns are long text columns or
contain BLOBS, then I see no harm in just selecting
them along with the rest of the other columns by using
select * from
If you are accessing the database from a programming
environment then you could do the following:
[pseudo code]
$sql = desc $NAME_OF_TABLE
$result = exec($sql)
$rows = fetch_result_into_array($result)
destroy($result)
$cnt = count($rows) - $NUMBER_OF_COLUMNS_TO_OMIT
if ($cnt = 0)
{ error(to few columns);
return;
}
$sql = select
for ($i=0;$i $cnt-1;$i++)
{ $sql = $sql + $ary[$i][Field] + ,
}
$sql = $sql + $ary[$cnt][Field]
$sql = $sql + FROM $NAME_OF_TABLE_TO_SELECT_FROM
$sql = $sql + WHERE $WHERE_CLAUSE
$sql = $sql + HAVING $HAVING_CLAUSE
$sql = $sql + GROUP BY $GROUP_BY_CLAUSE
$sql = $sql + ORDER BY $ORDER_BY_CLAUSE
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Using a hardware load balancer in from of MySQL
Sounds like you might be interested in Emic Networks' Application Cluster 2.0 for MySQL. We've begun taking a look at it ourselves -- without arriving at any conclusions at this point. (However, it is not strictly a hardware solution.) General Info: http://www.emicnetworks.com/products/mysql.html Features: http://www.emicnetworks.com/products/mysql_features.html --Fail-over Clustering --Multi-Master Architecture Robert J Taylor [EMAIL PROTECTED] PARTHA DUTTA, BLOOMBERG/ 499 PARK wrote: Hello all, I would like to find out if anyone has implemented an architecture where a hardware load balancer is placed in front of some MySQL servers in a Multi-master replication scheme. I want to use the load balancer more for high availability, than for load balancing. All connections to the database server would go the mysql server 1. If server1 fails, the load balancer should send all connections to server 2, etc. Thanks for any insight on any implementation gotchas. Partha Dutta Bloomberg, L.P. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: startup problems with linux
Why use root? How about creating a dummy database (CREATE DATABASE dummy) and a dummy user that only has rights to access that database and have your init.d script connect as the user dummy to db dummy? Much better than leaving MySQL root passwords around in easy to find places. (I know, I know...) HTH, Robert J Taylor [EMAIL PROTECTED] gerald_clark wrote: TK Banks wrote: The startup script provided with Fedora linux uses the mysqladmin ping command to verify that the server is up after the safe_mysqld command has been issued; however, once I changed the password for the root account, this no longer works: it sits there and tries this command 10 times on one second intervals and finally declares failure for the startup procedure (even though the server is actually up and as happy as can be). I'm sure I could remedy the problem by encoding the mysql root password in the /etc/init.d/mysqld file, but this seems sort of stupid. Should I just nix the ping glop? Or perhaps creating a ping account with no password but no privledges would do the trick? - Do you Yahoo!? Yahoo! Movies - Buy advance tickets for 'Shrek 2' Perhaps you could create a .my.cnf file for root that contains the password? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ESRI GIS products support lacking in MySQL???
Cynically, MySQL is probably not prohibitively expensive enough to hide the prohibitive expense of their add-on product. :) In any event, MySQL is up-front with its features and drawbacks as can be seen here: http://dev.mysql.com/doc/mysql/en/Compatibility.html (Especially scroll down to the subsection: http://dev.mysql.com/doc/mysql/en/Open_bugs.html) However, at the recent MySQL Users' Conference in Orlando, FL, there was at least one vendor present touting MySQL and its GIS data solution, vis-a-vis: http://dev.mysql.com/doc/mysql/en/GIS_introduction.html That link will also lead you to the GIS implementation in MySQL and its limitations (I see the word subset). HTH, Robert J Taylor [EMAIL PROTECTED] Douglas Phillipson wrote: I hope this isn't an inappropriate list for this... I have been trying to get ESRI (ww.esri.com) to consider supporting MySQL as a backend Database. They informed me that MySQL doesn't have all the features required to support ESRI's products. Since the alternatives are Oracle and SQL Server, and Oracle is prohibitively expensive, I'm wondering if your MySQL AB has considered contacting ESRI to see what their requirements are. ESRI is the standard for GIS systems. It would seem to me that if your database could be used by ESRI that you might get tens of thousands of new customers all over the world. I'm wondering if anyone else has probed exactly what is missing in MySQL that makes it inappropriate for use by ESRI. Regards Doug P -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ESRI GIS products support lacking in MySQL???
Wow. I'd stop listening to whomever it is that's telling you these stories. Really. The difference between the MyISAM and InnoDB storage engines centers on InnoDB's excellent support for transactions (MyISAM has none), InnoDB's row versus full table locking with MyISAM, and general ACID compliance versus non-ACID compliance. But these features aren't related (as far as I have ever heard or seen in practice) to basic SQL syntax. Check out the excellent MySQL documentation on-line that succinctly explains what MySQL can and cannot do today with each of the storage engines available to it: General MySQL Features and Limitations http://dev.mysql.com/doc/mysql/en/Features.html MySQL SQL Syntax http://dev.mysql.com/doc/mysql/en/SQL_Syntax.html MySQL Table Types, their features and drawbacks http://dev.mysql.com/doc/mysql/en/Table_types.html etc. This is by no means a RTFM but an encouragement to quickly find answers to many of these incorrect concepts others seem to have given you about MySQL. Read the documentation MySQL provides and you'll know first hand what the real problems are! :) But you'll also see what's useful, perhaps, and find something worthwhile. HTH, Robert J Taylor [EMAIL PROTECTED] As an aside: The freely accessible, transparent On-line documentation MySQL AB provides for its products is one of the telling traits of the company and its products as a whole. I personally have rarely seen such candor about one's own products in publicly accessible documentation. Helpful and refreshing. David Blomstrom wrote: It says, Currently, these features are available for MyISAM tables only. Does that mean you can't join MySQL tables that use these features? I haven't even learned how to make a join yet, but I was told that it can only be done with InnoDB tables. __ Do you Yahoo!? Yahoo! Movies - Buy advance tickets for 'Shrek 2' http://movies.yahoo.com/showtimes/movie?mid=1808405861 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: fastest filesystem for MySQL
Completely depends on your situation -- big files, small files? Lots of writes or reads or both? Growing tables/files or lots of large dropped tables? If you're into small files, go Reiser. Big data, JFS or XFS. EXT3 is slow, but, IIRC, it also is a true data journaling filesystem while some of the others aren't (and that's as deep as I go without a life-vest, if you know what I mean!). Just saw this on LWN.net: Benchmarks of EXT2, EXT3, JFS, XFS in various unreal stressful scenarios. Interesting, anyway: http://209.81.41.149/~jpiszcz/index.html Robert J Taylor [EMAIL PROTECTED] JFL wrote: I've heard and read that the Reiser filesystem should be better for MySQL than Ext3. Is this still true? We will be running MySQL on either Red Hat ES 3, Suse or Debian. Thanks, Jacob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how do i encrypt the .frm file with a password
Exactly. Think HIPAA and the methods used to secure data from the priying eyes of DBAs (supposedly) under HIPAA/Security. Dan Greene wrote: 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: how do i encrypt the .frm file with a password
How would password protecting without encrypting it be meaningful? (Answer: It wouldn't) Look at PGP/GPG encryption, as an example of private/public key encryption. Feed the encryption program uncompressed data and get either keyed or password protected data that is encrypted and compressed. This isn't a MySQL issue, strictly speaking, but I hope this helps you find an answer. references: http://www.pgp.com http://gnupg.org Robert J Taylor [EMAIL PROTECTED] David Crane wrote: I was considering encrypting the data itself. However, that would impact performance and our ability to compress it. We are using access now and it is a 5 cd install. I was hoping I could get away with password protecting the files to provide some security. 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]
Re: Features for Evaluation
There's a bunch. Check out: http://dev.mysql.com/doc/mysql/en/Introduction.html HTH, Robert J Taylor [EMAIL PROTECTED] On Thu, 2004-05-06 at 20:09, [EMAIL PROTECTED] wrote: Hi, I am setting up a lab to evaluate main features of MySQL so that provide advise for other departments to consider for their choice of database. Do you have a list of such features? Or do you have similar evaluation reports for my reference? Thanks, Joseph -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Case Sensitive on Windows
shaun thornburgh wrote: Hi, I am trying to synchronize two databases - the source is on a FreeBSD web server and the target is on our local Windows 2000 Server machine. I am trying to do this using Webyog (www.webyog.com) which tells Windows to sync with the source machine every hour. Side note: I don't know anything about webyog, but MySQL's built-in replication works so well, I'd recommend looking into using it. However, this is producing errors because the MySQL installation on the local machine isn't case sensitive, therefore it isn't recognizing the same tables. How can I rectify this, I can't find any documentation on the MySQL site regarding this...? Your answer may be here, in the online docs where MySQL case sensitivity is detailed. http://dev.mysql.com/doc/mysql/en/Name_case_sensitivity.html Thanks for your help HTH, Robert Taylor [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT HELP
Andre MATOS wrote: Hi, Is it possible to create a Select performing a math formula? For example: First I need to add two values come from the same table but from different records. The result will be divided from one number got from another table. Now, the new result will be added with another value got from another table creating the final result. Like this: ((value_from_record_1_from_table_A + value_from_record_15_from_table_A) / value_from_table_B ) + value_from_table_C Not knowing what the criteria for selecting the different records from table_A (1 and 15), I'll forgo a join clause and just illustrate a simple alias with where clause: SELECT (( a1.value + a2.value ) / b.value ) + c.value AS final_result FROM table_A a1, table_A a2, table_B b, table_C c WHERE a1.key = 1 AND a2.key = 15 AND /* guessing here */ b.key = a1.key AND c.key = a2.key AND a1.key a2.key AND a2.key IS NOT NULL AND a1.key IS NOT NULL; That's not correct as I am guessing your actual criteria, etc., but it gives the idea. Can you be more specific on the criteria for relating (joining) tables A, records 1 and 15, with themselves and with tables B and C? Is this possible? Is there anyone who can help me to create this SELETC? Thanks. HTH, Robert Taylor [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Order by price?
Yoed Anis wrote:
I'm having trouble with this query:
SELECT
CONCAT('$',ROUND(($varA*(establishments^2)+$varB*(establishments)+$varC),2))
as price FROM table ORDER BY price
Hint: you've made this a string comparison using CONCAT, i.e.,
alphabetical, not numerical, sorting rules apply.
How about
SELECT ROUND(($varA*(establishments^2)+$varB*(establishments)+$varC),2) as price FROM table ORDER BY price;
Then using a printf/sprintf type formatting statement on the application/presentation layer to show to the user?
What is the application language y
A note first. The column establishments is an int(10), as you see I am
taking this number through a formula, and I want the result of this formula
to be rounded to two decimal places, and to stick a $ sign in front of it
so I can easily display it on a table.
However here is the problem, in ASC order I get these results:
$110.46
$173.86
$208.02
$22.62
$22.62
And in DESC order I get these results:
$90.44
$79.94
$50.10
$48.33
(with the max result 208 for instance at the 3rd from very bottom, 110 is at
very bottom)
Any help in solving this would be greatly appreciated.
Thanks,
Yoed
HTH,
Robert Taylor
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Order by price?
Paul DuBois wrote: *snip* Sounds like you want: ORDER BY ROUND(($varA*(establishments^2)+$varB*(establishments)+$varC),2) Perfect. (I'm saying, duh over here to myself!) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: User Conference Presentations
You don't mean at the Discovery Cove near the Jimmy Buffet cover band do you? Speaking of which, Lars... there's an English expression that uses the word Cluster...but has nothing to do with databases... :) (Kidding! Just kidding!) Robert Reed wrote: Yes, I'm anxious to get my hands on some of the presentations myself. I think I saw a couple of brief mentions of a url but it wasn't written down anywhere. I'd also like to see some of the pictures I saw David snapping everywhere. :) --- David Perron [EMAIL PROTECTED] wrote: Does anyone know if the presentations from the User Conference are available online? Thanks dp = Robert Reed 512-869-0063 home 512-818-2460 cell __ Do you Yahoo!? Yahoo! Photos: High-quality 4x6 digital prints for 25¢ http://photos.yahoo.com/ph/print_splash -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Speed
Craig Gardner wrote: Thank you very much. That's what fixed my problem. Robert J Taylor wrote: Can you restrict to Not Null instead of != ? (I.e, can you scrub the data not to have empty strings?). The explain shows 3 extra where calculations per row...that's painful. Great! Glad that solved your problem. Robert Taylor [EMAIL PROTECTED] P.S. I didn't respond to the list initially, so I'm correcting that now. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [book announcement] High Performance MySQL (O'Reilly) is now shipping...
I got mine (even autographed) at the MySQL Users Conference. Excellent reading -- definitely required for anyone whose livelihood depends on MySQL. (this was a non-paid endorsement) Robert Taylor [EMAIL PROTECTED] Jeremy Zawodny wrote: MySQL users, I just wanted to send a quick note to let anyone interested know that High Performance MySQL (the book I started a long time ago) is now available and shipping. O'Reilly brought copies to the MySQL Conference last week and several folks have reported Amazon.com shipping, so it's time to say something. :-) If you were one who pre-ordered on Amazon.com, thanks! Your copy should show up soon. Book site: http://HighPerformanceMySQL.com/ On Amazon.com: http://www.amazon.com/exec/obidos/ASIN/0596003064/jeremydzawodny/ref=nosim On the O'Reilly site: http://www.oreilly.com/catalog/hpmysql/ There's a sample chapter (Replication) on-line as well as the TOC and description. The description says... As users come to depend on MySQL, they find that they have to deal with issues of reliability, scalability, and performance--issues that are not well documented but are critical to a smoothly functioning site. This book is an insider's guide to these little understood topics. Author Jeremy Zawodny has managed large numbers of MySQL servers for mission-critical work at Yahoo!, maintained years of contacts with the MySQL AB team, and presents regularly at conferences. Jeremy and Derek have spent months experimenting, interviewing major users of MySQL, talking to MySQL AB, benchmarking, and writing some of their own tools in order to produce the information in this book. In High Performance MySQL you will learn about MySQL indexing and optimization in depth so you can make better use of these key features. You will learn practical replication, backup, and load-balancing strategies with information that goes beyond available tools to discuss their effects in real-life environments. And you'll learn the supporting techniques you need to carry out these tasks, including advanced configuration, benchmarking, and investigating logs. /shameless_plug Jeremy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Modelling specialized M:N relationships?
Leandro Melo wrote: Hi, i have a M:N relationship between PRODUCT and PRICE. Is Price an Entity or an Attribute? I'm not accustomed to using price as a Thing. What is it that causes a Product to have one, exactly, or more prices? Is it its relationship to some other thing? Or is it a special kind of Product? Except during a couple years under US President Nixon, Prices are usually not things in themselves... can you flesh out what you are trying to model a little more? 1 product may be associated to N prices and 1 prices may belong to N products. I got special cases the some kinda product MUST have only 1 price associated with it. I don't what would be the best way to model this flag for the special case. So far, i got 3 tables. - PRODUCT (PRODUCT_ID as PK) - PRICE (PRICE_ID as PK) - PRODUCT_PRICE (with fields PRODUCT_ID and PRICE_ID) Where should i put this flg or should tie the relations??? Let's see what relationship to a another entity or classifying attribute of Product might invoke the rule on one-and-only-one or M:N. Then we'll know how better to proceed. Thanks, ltcmelo __ HTH, Robert Taylor [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: varchar 4 = char 4? Why?
Robert A. Rosenberg wrote: At 13:34 -0400 on 04/18/2004, Stormblade wrote about varchar 4 = char 4? Why?: This has been puzzling me. At first I thought it was something that Navicat was doing but I also tried in EMS MySQL and it does the same. If I set the type of a field to varchar and set the length to anything less than 4 it will get converted to a char type of the same length. Now I know that char is faster. I read it can be up to 50% faster but I am curious why I am prevented from having a varchar of length less than 4? Is it prohibitively expensive to do this in MySQL? Is it such a bad idea that they simply don't allow you to do it? Inquiring minds want to know. Taking a wild guess here so I might be wrong. Varchar needs a length to be appended to the start of the character data so MySQL can tell how long it is. This length field is probably 2 bytes long. Thus if the max length of the string is 1-3 bytes long, the field will be from 3-5 bytes so you might as well bite-the-bullet and just declare the field as char(4). If the length field is 4 bytes, then ALL Varchars under max=3 will be 5-8 bytes for max=1-3 so again char(4) is shorter. No need to guess, this is a documented behavior of MySQL: http://dev.mysql.com/doc/mysql/en/Silent_column_changes.html VARCHAR columns with a length less than four are changed to CHAR. Further explanation is also given in the documentation: http://dev.mysql.com/doc/mysql/en/CHAR.html The following table illustrates the differences between the two types of columns by showing the result of storing various string values into CHAR(4) and VARCHAR(4) columns: Value CHAR(4) Storage Required VARCHAR(4) Storage Required '' ' ' 4 bytes '' 1 byte 'ab' 'ab ' 4 bytes 'ab' 3 bytes 'abcd' 'abcd' 4 bytes 'abcd' 5 bytes 'abcdefgh' 'abcd' 4 bytes 'abcd' 5 bytes The values retrieved from the CHAR(4) and VARCHAR(4) columns will be the same in each case, because trailing spaces are removed from CHAR columns upon retrieval. HTH, Robert J Taylor [EMAIL PROTECTED] P.S. Robert Rosenberg -- happen to go to high school in San Antonio, TX, by chance? :) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: sql scripts
Dudley F. Cañas wrote: hi ppl, im a newbie to mysql but been using oracle for quite sometime now and i found that mysql is not bad at all :) i was just wondering if is there an equivalent scripting method of coding like plsql scripts in mysql... ? TIA Stored Proceedures are currently implemented in version 5 of MySQL, which is very much in development. There are no such solutions available in current production releases of MySQL. For more information on version 5's features: http://dev.mysql.com/doc/mysql/en/TODO_MySQL_5.0.html HTH, Robert Taylor [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
