Question: One to One
I may have asked this before, at least to some degree. Anyway I'd like to clarify. I have two entities that need a 1:1 relationship. Table1(Innodb)Table2(myISAM) PostID (int) (auto inc) PostID (int) DateEntered (timestamp) VendorID (int) ReferenceID (varchar) Details (text) MemberID (int) .(other fields) *The data must be tied together by the PostID. The *Details column in Table2 (which is the reason Table2 exists must relate back to the PostID in Table1 Dilemma: Table1 is innodb due to the anticipated transactional activity. Table2 is myISAM due to needed full text, case insensitive capabilities. Now I realize at the application level I can make the connection (PostID is consistent across both transactions). I would like the added security of having PostID foreign key to Table1. Obviously it can't be done without changing the Table2 table type. Is there suggestions or something I maybe missing here ? Thank you , Stuart -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
client timeout value
Hi, Is this possible to set a timeout value for mysql_query() call? Regards, - Joe
Re: Revoking select on a single table
GRANT usage on samp.* to 'permtest'@'localhost' identified by 'password'; GRANT insert, update, delete on samp.secrettable to 'permtest'@'localhost'; GRANT select, insert, update, delete on samp.Account to 'permtest'@'localhost'; This worked much better for me, though it's not a perfect solution (see below) This worked for me on 4.1.1a-alpha-nt-log. By explicitly denying access at the database level, the only tables I could see in my test database were the ones I specifically granted myself right to (even though there were others in the database). In my case I was able to see all the tables but could not select any data from the restricted table (mysql-server 4.0.18-5 from debian testing) I was blocked from running a SELECT query against secrettable. I could INSERT values but not DELETE them. I reason that this is because DELETE ... WHERE ... requires a SELECT to be run on the table to identify the rows to get rid of. The error I got when trying to delete was: ERROR 1143 (42000): SELECT command denied to user: 'permtest'@'localhost' for column 'col1' in table 'secrettable' Yes, this is the problem I ran into when trying to update any records in that table. So it appears to be insert only. Let us know if this works or what errors you get. Thanks for all your help. This may be the best solution available. Would it be possible to get it posted to a faq or mentioned in the docs? I imagine it's a situation that may come up frequently for users. Also, if you have any ideas on how one might perform updates on specific entries in that table without select privileges I'd be glad to hear them. (though it sounds like it may be impossible) Thanks again, Todd -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Shared column name
Hi, I know what the deal is supposed to be but can't seem to fix it. Two tables, VendorID exists in both tables (neither are primary keys) I'm getting a VendorID in where clause is ambiguous Sometimes it actually processes the SQL weird. I think this is because same column name in both tables, yet I have other same name in tables with no effect. I tried alias on VendorJobs.VendorId AS Ven , etc but it spit it back at me in the where clause with an unknown. Here is the SQL: SELECT `VendorJobs`.`JobID`, `VendorJobs`.`Contact`, `VendorJobs`.`Conmail`, `StaIndTypes`.`CareerCategories`, `StaUSCities`.`City`, `USStates`.`States`, `VendorJobs`.`AreaCode`, `VendorJobs`.`PayRate1`, `VendorJobs`.`PayRate2`, `staTravelReq`.`TravelReq`, `VendorJobs`.`VendorID` FROM `VendorJobs` INNER JOIN `StaIndTypes` ON (`VendorJobs`.`Industry` = `StaIndTypes`.`CareerIDs`) INNER JOIN `StaUSCities` ON (`VendorJobs`.`LocationState` = `StaUSCities`.`CityID`) INNER JOIN `USStates` ON (`StaUSCities`.`StateID` = `USStates`.`StateID`) INNER JOIN `staTravelReq` ON (`VendorJobs`.`TravelReq` = `staTravelReq`.`TravelReqID`), `VendorSignUp` WHERE (`VendorID` = 13) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Revoking select on a single table
I think that INSERT-only would be as good as we could hope for as I have been having a very hard time trying to think of a valid business reason why a user would be allowed to either UPDATE or DELETE rows from a table where they weren't allowed to even see the data. However I can think of several business reasons for an INSERT-only table: A suggestion box Sales figures TimeCard entries Anywhere that people need to add data to a common table but not see what anyone else had added to that same table. I didn't test the situation but thought of two cases you might want to test for (I have already dropped my test tables and users) Try running a whole table UPDATE or whole table DELETE. I wondered if you run those statements without a WHERE clause, would the engine allow them even if the user doesn't have SELECT rights? UPDATE secrettable SET column = 'value'; DELETE FROM secrettable; That could be a dangerous situation for you if you leave those two permissions on the 'hidden' table. Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine Todd Charron [EMAIL PROTECTED] wrote on 08/30/2004 03:55:00 PM: GRANT usage on samp.* to 'permtest'@'localhost' identified by 'password'; GRANT insert, update, delete on samp.secrettable to 'permtest'@'localhost'; GRANT select, insert, update, delete on samp.Account to 'permtest'@'localhost'; This worked much better for me, though it's not a perfect solution (see below) This worked for me on 4.1.1a-alpha-nt-log. By explicitly denying access at the database level, the only tables I could see in my test database were the ones I specifically granted myself right to (even though there were others in the database). In my case I was able to see all the tables but could not select anydata from the restricted table (mysql-server 4.0.18-5 from debian testing) I was blocked from running a SELECT query against secrettable. I could INSERT values but not DELETE them. I reason that this is because DELETE ... WHERE ... requires a SELECT to be run on the table to identify the rows to get rid of. The error I got when trying to delete was: ERROR 1143 (42000): SELECT command denied to user: 'permtest'@'localhost' for column 'col1' in table 'secrettable' Yes, this is the problem I ran into when trying to update any records in that table. So it appears to be insert only. Let us know if this works or what errors you get. Thanks for all your help. This may be the best solution available. Would it be possible to get it posted to a faq or mentioned in the docs? I imagine it's a situation that may come up frequently for users. Also, if you have any ideas on how one might perform updates on specific entries in that table without select privileges I'd be glad to hear them. (though it sounds like it may be impossible) Thanks again, Todd
Re: Shared column name
Hi, I know what the deal is supposed to be but can't seem to fix it. Two tables, VendorID exists in both tables (neither are primary keys) I'm getting a VendorID in where clause is ambiguous Sometimes it actually processes the SQL weird. I think this is because same column name in both tables, yet I have other same name in tables with no effect. I tried alias on VendorJobs.VendorId AS Ven , etc but it spit it back at me in the where clause with an unknown. Here is the SQL: SELECT `VendorJobs`.`JobID`, `VendorJobs`.`Contact`, --8-- snip (`VendorJobs`.`TravelReq` = `staTravelReq`.`TravelReqID`), `VendorSignUp` WHERE (vendorjobs.`VendorID` = 13) With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Shared column name
Try specifying the source tables more explicitly in your SELECT statement. SELECT table_a.VendorJobs opt_a , table_b.VendorJobs opt_b ... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Shared column name
You have to say which table's VendorID column you want to evaluate your WHERE clause against (just as the error message says). Try this WHERE (VendorJobs.`VendorID` = 13) As a shortcut , and to keep you from getting typist's cramp, you only _need_ to specify the table name for columns that are NOT unique within the set of all columns in the tables you have joined into your query. For all uniquely named columns, you can drop the table specifier. Don't get me wrong, the way you are specifying your columns IS the **correct** method of being specific. I am merely suggesting a way for you to save yourself a few keystrokes and maybe a few typos along the way. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Stuart Felenstein [EMAIL PROTECTED] wrote on 08/30/2004 04:09:03 PM: Hi, I know what the deal is supposed to be but can't seem to fix it. Two tables, VendorID exists in both tables (neither are primary keys) I'm getting a VendorID in where clause is ambiguous Sometimes it actually processes the SQL weird. I think this is because same column name in both tables, yet I have other same name in tables with no effect. I tried alias on VendorJobs.VendorId AS Ven , etc but it spit it back at me in the where clause with an unknown. Here is the SQL: SELECT `VendorJobs`.`JobID`, `VendorJobs`.`Contact`, `VendorJobs`.`Conmail`, `StaIndTypes`.`CareerCategories`, `StaUSCities`.`City`, `USStates`.`States`, `VendorJobs`.`AreaCode`, `VendorJobs`.`PayRate1`, `VendorJobs`.`PayRate2`, `staTravelReq`.`TravelReq`, `VendorJobs`.`VendorID` FROM `VendorJobs` INNER JOIN `StaIndTypes` ON (`VendorJobs`.`Industry` = `StaIndTypes`.`CareerIDs`) INNER JOIN `StaUSCities` ON (`VendorJobs`.`LocationState` = `StaUSCities`.`CityID`) INNER JOIN `USStates` ON (`StaUSCities`.`StateID` = `USStates`.`StateID`) INNER JOIN `staTravelReq` ON (`VendorJobs`.`TravelReq` = `staTravelReq`.`TravelReqID`), `VendorSignUp` WHERE (`VendorID` = 13) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT, ORDER one DISTINCT kolumn?
Hi Per, Thank you for the additional information. It helps me understand your situation a lot better. Unfortunately, it also raises more questions ;-) My first comment is that you really ought to have a primary key on your table. From the sound of it, you are new to MySQL at least and possibly to databases in general. Are you familiar with Normalization, which is the central activity of proper database design? I suspect you are not because one of the main results of normalization is the selection of primary keys. Normalization is not particularly difficult to do and I recommend that anyone building a database use normalization in its design, even if it just for a web page that supports one of their hobbies. It looks to me like your 'ettan' table is a completely un-normalized collection of data. If I'm right, then you are going to have lots of problems with queries until you normalize the design properly. Unfortunately, although normalization is not terribly hard to do, it is too involved to teach it to someone in a note in a mailing list. I suggest that you use your favourite search engine and try to find a good tutorial on Data Normalization. Or maybe someone here can suggest one. I looked for a good normalization tutorial a while back but I'm fairly demanding when it comes to tutorials and have never found one that really impressed me. (I just did a Google web search and found over a million hits so you will have lots to choose from! Maybe you can even find a good tutorial in Swedish.) If you have questions about normalization in your chosen tutorial, be sure to post here with specific questions and people will likely be quite helpful. If you and I were to lock ourselves in a room without distraction, I could probably get the information I needed out of you and normalize your data for you in an hour. But I've done quite a bit of normalizing over the years. It's going to take you longer because you've never done it before you and have to learn how to do it first. But I think you will find it well worth it to learn normalization because it will save you a lot of time down the road since your database designs will be much better. I realize that isn't the sort of specific help you had in mind. I would rather have given you a specific answer, like Michael Stassen did, but I think you need to get a proper database design first. I think you'll find that a proper design makes your queries simpler and more obvious. By the way, it was interesting to hear that you have drag racing in Sweden; I'll have to share information with my friends who like car racing. I find it very interesting that these races happen on the streets and with police permission. We have a problem with street racing in this country (Canada). It is very illegal in this country but some individuals and groups have races anyway and there have been several people killed and badly injured in these races. Although there are quite a few formal (and legal!) race tracks in this country, for some reason, many people won't use them; I don't know why. How did you ever persuade your police to permit street racing? What precautions do they take to prevent people being hurt? Rhino - Original Message - From: Per Lonnborg [EMAIL PROTECTED] To: Rhino [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Monday, August 30, 2004 2:46 PM Subject: Re: SELECT, ORDER one DISTINCT kolumn? Hi, I´ll try to tell you a bit more; It's very hard to answer your questions, particularly the first one, since you haven't provided the definition of the 'ettan' table (especially the identity of the primary key) nor shown a sampling of what rows would typically be in the table. mysql DESCRIBE ettan; +--+--+--+-+-+---+ | Field| Type | Null | Key | Default | Extra | +--+--+--+-+-+---+ | stad | varchar(20) | YES | | NULL| | | ort | varchar(20) | YES | | NULL| | | datum| date | YES | | NULL| | | plats| int(3) | YES | | NULL| | | nr | int(3) | YES | | NULL| | | namn | varchar(25) | YES | | NULL| | | fabrikat | varchar(25) | YES | | NULL| | | reakt| decimal(5,3) | YES | | NULL| | | 60fot| decimal(5,3) | YES | | NULL| | | hast | decimal(5,2) | YES | | NULL| | | tid | decimal(5,3) | YES | | NULL| | +--+--+--+-+-+---+ 11 rows in set (0.00 sec) mysql As you can see, I have no primary key...I went by the MySQL tutorial creating a database for the first time and I don´t belive they mention the Primary Key there. Also, you haven't identified what version of MySQL you have. Newer versions have more capabilities than older ones so the answer to your questions will depend on the MySQL version. For example, if you are
Re: Revoking select on a single table
On Monday 30 August 2004 4:08 pm, [EMAIL PROTECTED] wrote: I think that INSERT-only would be as good as we could hope for as I have been having a very hard time trying to think of a valid business reason why a user would be allowed to either UPDATE or DELETE rows from a table where they weren't allowed to even see the data. However I can think of several business reasons for an INSERT-only table: The situation I have is like this. End User enters data in web page. Web page data is encrypted using mysql's aes_encrypt and stored in remote DB (not the same machine as the web server). If End User wants to update the data the new data is encrypted and overwrites the old (End User cannot see what old data was). If the db user account gets compromised via the web server (username and/or password gets disclosed/cracked/etc), the data (encrypted or not) cannot be retrieved via that account, only overwritten. UPDATE secrettable SET column = 'value'; DELETE FROM secrettable; That may be possible, but I can't really see anywhere where that would be useful. Thanks. Todd -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Shared column name
Typist's cramp ? Can you say visual query builder. ;) Stuart --- [EMAIL PROTECTED] wrote: You have to say which table's VendorID column you want to evaluate your WHERE clause against (just as the error message says). Try this WHERE (VendorJobs.`VendorID` = 13) As a shortcut , and to keep you from getting typist's cramp, you only _need_ to specify the table name for columns that are NOT unique within the set of all columns in the tables you have joined into your query. For all uniquely named columns, you can drop the table specifier. Don't get me wrong, the way you are specifying your columns IS the **correct** method of being specific. I am merely suggesting a way for you to save yourself a few keystrokes and maybe a few typos along the way. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Stuart Felenstein [EMAIL PROTECTED] wrote on 08/30/2004 04:09:03 PM: Hi, I know what the deal is supposed to be but can't seem to fix it. Two tables, VendorID exists in both tables (neither are primary keys) I'm getting a VendorID in where clause is ambiguous Sometimes it actually processes the SQL weird. I think this is because same column name in both tables, yet I have other same name in tables with no effect. I tried alias on VendorJobs.VendorId AS Ven , etc but it spit it back at me in the where clause with an unknown. Here is the SQL: SELECT `VendorJobs`.`JobID`, `VendorJobs`.`Contact`, `VendorJobs`.`Conmail`, `StaIndTypes`.`CareerCategories`, `StaUSCities`.`City`, `USStates`.`States`, `VendorJobs`.`AreaCode`, `VendorJobs`.`PayRate1`, `VendorJobs`.`PayRate2`, `staTravelReq`.`TravelReq`, `VendorJobs`.`VendorID` FROM `VendorJobs` INNER JOIN `StaIndTypes` ON (`VendorJobs`.`Industry` = `StaIndTypes`.`CareerIDs`) INNER JOIN `StaUSCities` ON (`VendorJobs`.`LocationState` = `StaUSCities`.`CityID`) INNER JOIN `USStates` ON (`StaUSCities`.`StateID` = `USStates`.`StateID`) INNER JOIN `staTravelReq` ON (`VendorJobs`.`TravelReq` = `staTravelReq`.`TravelReqID`), `VendorSignUp` WHERE (`VendorID` = 13) -- 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: Revoking select on a single table
Thinking about this some more, it might be possible to achieve what my last email suggests by allowing select on the primary key column. Or would that set us back again? Thoughts? Todd -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
OT - Gmail invite
Could one of you send me an invite to gmail? I screwed mine up by admitting I sold some on ebay. As soon as I did that I stopped getting any mail. You may recall me as [EMAIL PROTECTED] Thanks! R. Whitney Transcend Development Producing the next phase of your internet presence http://xend.net Premium Quality Web Hosting http://hosting.xend.net [EMAIL PROTECTED] 310-943-6498 602-288-5340 The day this country abandons God is the day God will abandon this country -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Business Rule lacking technical process
Slightly off the main topic:
The point about retaining the data from abandoned carts is a very good one.
The idea of per-user discount based on a previous 'almost purchase' makes me
feel a little uncomfortable though.
Perhaps if more than one particular item appeared in (let's say) 10 shoppers
abandoned carts within a 14 day period, you might think about reducing the
price.
If you _do_ like the idea of per-user discount, then please think about
involving more criteria. E.g.
Did the shopper read the full item description (for more than 10 seconds)?
Did the shopper also look at similar items in the same category (looked at
yellow, blue and green 'widgets' - at least you know they definitely want
that 'widget')?
Did the user navigate to your site from a click-through price comparison
site?
I could go on. I'm sure the likes of Amazon have an almost scary amount of
statistical analysis driving their pricing structure and marketing.
-- ___
| |
|--+--
| |.HushFriend (you'll see).
.Stefan Holmes.
-Original Message-
From: Joe Audette [mailto:[EMAIL PROTECTED]
Sent: 30 August 2004 15:46
To: [EMAIL PROTECTED]
Subject: Re: Business Rule lacking technical process
I think the database is a better place to store a cart for a number of
reasons. For one thing you can do analysis of shopping behaviour on
abandoned carts and if a user was logged in while adding items to a cart
then abandons the cart you can send a discount offer taylored to the items
he almost bought. If you store this in session data its lost forever. I
also think in terms of scaling to large numbers of users it is better to
avoid using session state.
Just another opinion.
Joe
Fagyal Csongor [EMAIL PROTECTED] wrote:
Hi,
Stuart,
(I am trying to continue using the same analogy you started) A cart is
usually just a table where lots of users accumulate purchases until
they
are ready to buy. That implies 3 basic operations:
1) The users can browse through a list of somethings and pick and choose
what they would like to have
2) As the users add and delete things from their cart, database changes
occur. These changes should NOT affect the actual inventory numbers
until the purchase is complete.
All that changes is the cart table and it's contents **AS IT RELATES
SPECIFICALLY TO THAT USER**. Typically there is only 1 cart table per
design and a column is used to uniquely identify which user has shopped
for which items.
3) The actual purchase of the contents of the cart , at least for
most
merchandise purchases, WILL involve external systems (generally EMAIL
for verification and FINANCIAL for the purchase transaction itself.) and
some sort of lock (reservation) on inventory so that you can ensure
that
you will be able to deliver what the person is purchasing. This is all
wrapped in a very critical transaction to make sure that if you get their
money, they get their purchase
IMHO the cart is something you store in a session, and not in the
database. You save a lot of programming this way.
In Apache::ASP (perl) this would be something like (a little
simplified, no verification, etc.):
if ($form-{'addtocart'}) {
$Session-{'cart'}-{$form-{'itemid'}} += $form-{'pieces'};
}
3 lines and you have a shopping cart. At checkout, you do the DB
transactions all.
I might have been a little off-topic here, though :-)
- Csongor
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
[EMAIL PROTECTED]
http://www.joeaudette.com
http://www.mojoportal.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Revoking select on a single table
Sorry to reply yet again, but I think I have the solution. After doing all we have said above I added grant select(ID_Num) on sampdb.secrettable to 'user'@'localhost' identified by 'password'; and of course updates and deletes are done via update secrettable set secretinfo=blah where ID_Num=2; So far as I've been testing this seems to work while preventing the user from ever selecting what's in the other secrettable columns. Thanks again! Todd -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Revoking select on a single table
Yes, I think that _may_ solve your problem. Try granting only INSERT and UPDATE on the table then grant only SELECT permission on the ID column (I assume it's autoincrementing?) and the user_id column. If you don't make the user_id column visible, how will you ever discover the correct ID to update? (Unless there is only ever 1 record per user_id in which case you won't need an auto_incrementing ID column and you will already know which user_id to updatehmmmmaybe I should think just a bit more before I start typing... :-) ) You only need to expose what would be public knowledge anyway, right? I think your encrypted data will still be protected by the database so long as the invaders do not get ahold of a privileged account. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Todd Charron [EMAIL PROTECTED] wrote on 08/30/2004 04:30:24 PM: Thinking about this some more, it might be possible to achieve what my last email suggests by allowing select on the primary key column. Or would that set us back again? Thoughts? Todd
Re: very simple query but strange results
- Original Message -
From: Kapoor, Nishikant [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Monday, August 30, 2004 2:41 PM
Subject: very simple query but strange results
This little sql has me puzzled. Would appreciate your help.
mysql drop table if exists T;
Query OK, 0 rows affected (0.00 sec)
mysql create table T (title text, fulltext(title)) type=MyISAM;
Query OK, 0 rows affected (0.00 sec)
mysql insert into T values ('01 test'), ('test resource');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql -- not working
mysql SELECT title FROM T WHERE MATCH(title) AGAINST ('test');
Empty set (0.00 sec)
Why 'Empty set'? I expect to see two rows. What am I missing?
Thanks
Nishi
mysqladmin Ver 8.40 Distrib 4.0.15, for mandrake-linux-gnu on i586
Server version 4.0.15
Protocol version10
I'm not sure.
I haven't worked with fulltext searches myself but your question intrigued
me. I tried creating the same table you did and then ran the same query: I
had the same result you did.
I went to this page of the MySQL manual
http://dev.mysql.com/doc/mysql/en/Fulltext_Search.html, but couldn't see any
obvious error in your query based on the rules I saw there. I tried setting
up the table that they used in their examples and it gave the same result
that they predicted.
The big difference between your example and theirs was that their example
had the fulltext() function applied to TWO columns, title and body, while
yours applied only to title.
I wonder if there is an unstated rule that the match() function must always
be used against at least two columns? That strikes me as a very odd design
for a function so it doesn't seem likely. However, I am at a loss to suggest
another explanation.
Perhaps someone with more experience with MATCH() or one of the MySQL
developers could enlighten us on the correct technique for searching a
single fulltext column.
Rhino
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: very simple query but strange results
Rhino wrote:
- Original Message -
From: Kapoor, Nishikant [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Monday, August 30, 2004 2:41 PM
Subject: very simple query but strange results
This little sql has me puzzled. Would appreciate your help.
mysql drop table if exists T;
Query OK, 0 rows affected (0.00 sec)
mysql create table T (title text, fulltext(title)) type=MyISAM;
Query OK, 0 rows affected (0.00 sec)
mysql insert into T values ('01 test'), ('test resource');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql -- not working
mysql SELECT title FROM T WHERE MATCH(title) AGAINST ('test');
Empty set (0.00 sec)
Why 'Empty set'? I expect to see two rows. What am I missing?
Thanks
Nishi
mysqladmin Ver 8.40 Distrib 4.0.15, for mandrake-linux-gnu on i586
Server version 4.0.15
Protocol version10
I'm not sure.
I haven't worked with fulltext searches myself but your question intrigued
me. I tried creating the same table you did and then ran the same query: I
had the same result you did.
I went to this page of the MySQL manual
http://dev.mysql.com/doc/mysql/en/Fulltext_Search.html, but couldn't see any
obvious error in your query based on the rules I saw there. I tried setting
up the table that they used in their examples and it gave the same result
that they predicted.
The big difference between your example and theirs was that their example
had the fulltext() function applied to TWO columns, title and body, while
yours applied only to title.
I wonder if there is an unstated rule that the match() function must always
be used against at least two columns? That strikes me as a very odd design
for a function so it doesn't seem likely. However, I am at a loss to suggest
another explanation.
Perhaps someone with more experience with MATCH() or one of the MySQL
developers could enlighten us on the correct technique for searching a
single fulltext column.
Rhino
The answer is in the middle of that document. Words which appear in 50% or
more of your rows become stopwords, because they aren't very useful for
narrowing your search. One consequence is that you must have at least 3
rows in a table before full-text indexing will find anything. Full-text
indexing is designed for large collections, not tiny test tables.
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: very simple query but strange results
I skimmed the whole article twice, including the user comments, and still
missed that :-(
Thanks for catching that Michael! That explanation makes a lot of sense, a
lot more sense than forcing there to be at least two columns in the
fulltext() column.
Rhino
- Original Message -
From: Michael Stassen [EMAIL PROTECTED]
To: Rhino [EMAIL PROTECTED]
Cc: Kapoor, Nishikant [EMAIL PROTECTED];
[EMAIL PROTECTED]
Sent: Monday, August 30, 2004 7:12 PM
Subject: Re: very simple query but strange results
Rhino wrote:
- Original Message -
From: Kapoor, Nishikant [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Monday, August 30, 2004 2:41 PM
Subject: very simple query but strange results
This little sql has me puzzled. Would appreciate your help.
mysql drop table if exists T;
Query OK, 0 rows affected (0.00 sec)
mysql create table T (title text, fulltext(title)) type=MyISAM;
Query OK, 0 rows affected (0.00 sec)
mysql insert into T values ('01 test'), ('test resource');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql -- not working
mysql SELECT title FROM T WHERE MATCH(title) AGAINST ('test');
Empty set (0.00 sec)
Why 'Empty set'? I expect to see two rows. What am I missing?
Thanks
Nishi
mysqladmin Ver 8.40 Distrib 4.0.15, for mandrake-linux-gnu on i586
Server version 4.0.15
Protocol version10
--
--
I'm not sure.
I haven't worked with fulltext searches myself but your question
intrigued
me. I tried creating the same table you did and then ran the same query:
I
had the same result you did.
I went to this page of the MySQL manual
http://dev.mysql.com/doc/mysql/en/Fulltext_Search.html, but couldn't see
any
obvious error in your query based on the rules I saw there. I tried
setting
up the table that they used in their examples and it gave the same
result
that they predicted.
The big difference between your example and theirs was that their
example
had the fulltext() function applied to TWO columns, title and body,
while
yours applied only to title.
I wonder if there is an unstated rule that the match() function must
always
be used against at least two columns? That strikes me as a very odd
design
for a function so it doesn't seem likely. However, I am at a loss to
suggest
another explanation.
Perhaps someone with more experience with MATCH() or one of the MySQL
developers could enlighten us on the correct technique for searching a
single fulltext column.
Rhino
The answer is in the middle of that document. Words which appear in 50%
or
more of your rows become stopwords, because they aren't very useful for
narrowing your search. One consequence is that you must have at least 3
rows in a table before full-text indexing will find anything. Full-text
indexing is designed for large collections, not tiny test tables.
Michael
--
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 logging level(s)
Hi - I'm new to this list and I didn't see a FAQ (I apologize if I missed it somewhere). How do I set MySQL's logging level? And, where are MySQL's logs (i.e. is there anything in addition to /var/lib/host.err)? Thanks! Gary -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL logging level(s)
At 17:42 -0600 8/30/04, Newell, Gary wrote: Hi - I'm new to this list and I didn't see a FAQ (I apologize if I missed it somewhere). How do I set MySQL's logging level? And, where are MySQL's logs (i.e. is there anything in addition to /var/lib/host.err)? The logs are discussed in the Reference Manual: http://dev.mysql.com/doc/mysql/en/Log_Files.html Since you mention that you're new to the list, I'll mention this tip: The manual is your friend. Time spent getting to know it is time well spent. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problem running MySQL in high school lab
I'm trying to teach my students how to use MySQL, and have installed it on all the lab machines along with Cygwin. Originally, I had the permissions set wrong and my students couldn't start the server, but I fixed that, and now mysqld works fine. Unfortunately, if you then mysql -u root, after a rather short period of time, the program crashes and pops an error message to the screen. The message, which I should have written down but didn't, says that an assertion has failed in ftell.c (not sure about the filename, but the gist is right) and stream != NULL (that I'm sure of) and then the program dies. I don't have similar problems when I'm logged in as me (which has Administrator privileges) or the machine Administrator. It must be a permissions problem, but I don't know what I need to give the students to prevent it. The MySQL stuff on the local machines need not be secure, so I've given full access to all users in the entire /cygwin/usr/local/ directory and its subdirectories, which is where I installed MySQL and all the other packages we're going to be playing with. There are some kids in there who don't need the temptation of being logged in as an Administrator, and since we're going to be using JDBC later for which the MySQL server will need to be running almost constantly in the background, I'd like to get this resolved with the least amount of temptation. The lab is all Windows XP Professional machines, and the students log into a domain hosted by a server in another teacher's lab. Any ideas appreciated, Todd P.S. If you could cc me any replies, I'd appreciate it, since I read the list on digest. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ERROR 1005 - Please help
I have posted this in a subject called - InnoDB table creation. I am just trying to be specific. Please forgive me if this is not allowed. I have searched the online help and this site. I can not find out why I am getting this error: ERROR 1005 at line 33: Can't creat table '.\enrollment1\enrolls.frm' (errno: 150) Here is what I am trying to do: CREATE TABLE ENROLLS (courseID SMALLINT NOT NULL, sectionID SMALLINT NOT NULL, studentID SMALLINT NOT NULL, grade SMALLINT, PRIMARY KEY(courseID,sectionID,studentID), FOREIGN KEY(courseID) REFERENCES COURSES(courseID) ON UPDATE CASCADE ON DELETE CASCADE, INDEX(sectionID), FOREIGN KEY(sectionID) REFERENCES SECTIONS(sectionID) ON UPDATE CASCADE ON DELETE CASCADE, INDEX(studentID), FOREIGN KEY(studentID) REFERENCES STUDENTS(studentID) ON UPDATE CASCADE ON DELETE CASCADE)TYPE=INNODB; Pleas help if you can. Thank you. Maru __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Problem running MySQL in high school lab
Todd, I don't use Windows XP as a production machine, but I do run MySQL on my personal machine running Windows XP, I run the Windows version of MySQL. Is there any reason that you are using Cygwin to run MySQL when you can run the MySQL windows binaries without any problems? The only thing I can think of is you are trying to teach them linux as well. I know in the MySQL training classes offered by MySQL they are always taught using Windows 2000 or XP, and they use the standard MySQL windows installer. Donny -Original Message- From: Todd O'Bryan [mailto:[EMAIL PROTECTED] Sent: Monday, August 30, 2004 8:09 PM To: [EMAIL PROTECTED] Subject: Problem running MySQL in high school lab I'm trying to teach my students how to use MySQL, and have installed it on all the lab machines along with Cygwin. Originally, I had the permissions set wrong and my students couldn't start the server, but I fixed that, and now mysqld works fine. Unfortunately, if you then mysql -u root, after a rather short period of time, the program crashes and pops an error message to the screen. The message, which I should have written down but didn't, says that an assertion has failed in ftell.c (not sure about the filename, but the gist is right) and stream != NULL (that I'm sure of) and then the program dies. I don't have similar problems when I'm logged in as me (which has Administrator privileges) or the machine Administrator. It must be a permissions problem, but I don't know what I need to give the students to prevent it. The MySQL stuff on the local machines need not be secure, so I've given full access to all users in the entire /cygwin/usr/local/ directory and its subdirectories, which is where I installed MySQL and all the other packages we're going to be playing with. There are some kids in there who don't need the temptation of being logged in as an Administrator, and since we're going to be using JDBC later for which the MySQL server will need to be running almost constantly in the background, I'd like to get this resolved with the least amount of temptation. The lab is all Windows XP Professional machines, and the students log into a domain hosted by a server in another teacher's lab. Any ideas appreciated, Todd P.S. If you could cc me any replies, I'd appreciate it, since I read the list on digest. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ERROR 1005 - Please help
The problem is in table SECTIONS. From the manual, In the referencing table, there must be an index where the foreign key columns are listed as the first columns in the same order. In the referenced table, there must be an index where the referenced columns are listed as the first columns in the same order. http://dev.mysql.com/doc/mysql/en/InnoDB_foreign_key_constraints.html In SECTIONS, you have an unnecessary index on courseID (it's the first column in the primary key) and no index where sectionID comes first (it's second in the primary key). The former is simply inefficient, the latter is causing the error. Most likely, you should drop the index on courseID and add one on sectionID: ALTER TABLE SECTIONS DROP INDEX courseID, ADD INDEX (sectionID); After you do that, the ENROLLS table creation should work without error. Keeping the courseID index and reversing the primary key should also work, but a primary key on (sectionID, courseID) makes less logical sense, I think. Michael Mulugeta Maru wrote: I have posted this in a subject called - InnoDB table creation. I am just trying to be specific. Please forgive me if this is not allowed. I have searched the online help and this site. I can not find out why I am getting this error: ERROR 1005 at line 33: Can't creat table '.\enrollment1\enrolls.frm' (errno: 150) Here is what I am trying to do: CREATE TABLE ENROLLS (courseID SMALLINT NOT NULL, sectionID SMALLINT NOT NULL, studentID SMALLINT NOT NULL, grade SMALLINT, PRIMARY KEY(courseID,sectionID,studentID), FOREIGN KEY(courseID) REFERENCES COURSES(courseID) ON UPDATE CASCADE ON DELETE CASCADE, INDEX(sectionID), FOREIGN KEY(sectionID) REFERENCES SECTIONS(sectionID) ON UPDATE CASCADE ON DELETE CASCADE, INDEX(studentID), FOREIGN KEY(studentID) REFERENCES STUDENTS(studentID) ON UPDATE CASCADE ON DELETE CASCADE)TYPE=INNODB; Pleas help if you can. Thank you. Maru -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Revoking select on a single table
[EMAIL PROTECTED] wrote: I was blocked from running a SELECT query against secrettable. I could INSERT values but not DELETE them. I reason that this is because DELETE ... WHERE ... requires a SELECT to be run on the table to identify the rows to get rid of. The error I got when trying to delete was: I don't know that I would have put it in those words, but yes, you have to have the SELECT priv to match rows with WHERE. ERROR 1143 (42000): SELECT command denied to user: 'permtest'@'localhost' for column 'col1' in table 'secrettable' Let us know if this works or what errors you get. Then, in another message, [EMAIL PROTECTED] wrote: I think that INSERT-only would be as good as we could hope for as I have been having a very hard time trying to think of a valid business reason why a user would be allowed to either UPDATE or DELETE rows from a table where they weren't allowed to even see the data. However I can think of several business reasons for an INSERT-only table: A suggestion box Sales figures TimeCard entries Anywhere that people need to add data to a common table but not see what anyone else had added to that same table. I didn't test the situation but thought of two cases you might want to test for (I have already dropped my test tables and users) Try running a whole table UPDATE or whole table DELETE. I wondered if you run those statements without a WHERE clause, would the engine allow them even if the user doesn't have SELECT rights? UPDATE secrettable SET column = 'value'; Yes, this works. UPDATE priv allows updates. No WHERE clause, no need for SELECT priv. DELETE FROM secrettable; This works as well, for the same reason. That could be a dangerous situation for you if you leave those two permissions on the 'hidden' table. Absolutely. Yours, Shawn Green Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Tricky Date Query
Hello, Not sure this is possible to do with SQL or if needs to be done with in the application programming language we're using(Perl), but I need to get the a weekdate, IE SELECT EXTRACT(DAY FROM NOW()) which would return 30 Now what I need to do, is if the query above is greater or less then 15, get the next month or current month date for the 15th. IE if I ran this query today, and the value is 30(15), I need to generate the date 2004-09-15. If the query was run on 2004-09-14(15) generate the date 2004-09-15, if the query was run on 2004-12-25(15), generate the date for 2005-01-15 Is this possible to do with MySQL ?? Thx's Mickalo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Data loading and foreign key constraints - help please
Thank you very much. I really appreciate your analogy to the waterfall. This helped me out tremendously. I was able to sort out the problem and all is now well! It appears that this wonderful little GUI tool the lets you create ER diagrams that auto-generate CREATE scripts assumes that you won't be including foreign keys in your entities. It expects you to build the relationship graphically and point out the primary keys, but it takes care of creating the foreign keys for you. My mistake. Since I put in foreign keys with the same name as the primary key in the related table, the GUI tool had no choice but to create new foreign keys with the same name appended with the number 1. The end result... total chaos. Fixed it though. I really appreciate your help. Todd On Aug 30, 2004, at 6:20 AM, [EMAIL PROTECTED] wrote: Foreign keys are used to enforce foreign relationships. Translated: Certain data values must exist in one table before another table can contain those values in columns that participate in foreign keys. Because data must first exist in one table before it can be used as data in another, you are required to fill in your FK-related structures from the top down. Start with your top-most table(s) in your structure (these are the ones that the foreign keys are referencing but have no foreign keys of their own). I think you said that you called them joblevel and jobtitile. Fill those tables with data. With those values in place you can create rows in the jobcode table that re-use certain values. You will not be able to assign a value to any row in jobcode that does not exist in either joblevel or jobtitle (for the columns that reference those tables as foreign keys). Keep filling in values in each layer of your structure until you get to the bottommost table(s). (These are the tables that FK reference other tables but have no tables that reference them.) It's kind of like a waterfall, you can't get data into some tables until it exists in other tables so it's like the data sort of trickles down the structure. (This analogy could also help to visualize how the use of the word cascade describes the auto-propagation of a delete or update to the dependent tables) HTH, Shawn Green Database Administrator Unimin Corporation - Spruce Pine Todd Cranston-Cuebas [EMAIL PROTECTED] wrote on 08/29/2004 04:09:15 AM: I'm a total newbie to mySQL, but was hoping someone could answer a question regarding adding a record into a database that has foreign key constraints. Remember, I'm a total newbie so I'm hoping I'm using the right words to express this. I'm taking a class that required us to use an ER diagramming tool. This tool generates SQL table create scripts in mySQL. After a little tweaking I got the scripts to work. An example is as follows: # Create Table: 'Jobdesc' Job Description for Requisition # desccode: # jobdescription : # levelcode : (references JobCode.levelcode) # jobcode1: (references JobCode.jobcode) # jobcode : # titlecode : (references JobCode.titlecode) # CREATE TABLE Jobdesc ( desccode CHAR(8) NOT NULL UNIQUE, jobdescription MEDIUMTEXT NOT NULL, levelcode CHAR(2) NOT NULL, jobcode1 CHAR(8) NOT NULL, jobcodeCHAR(8) NOT NULL, titlecode CHAR(7) NOT NULL, PRIMARY KEY (desccode,jobcode), INDEX idx_fk_Jobdesc (titlecode,jobcode1,levelcode), CONSTRAINT fk_Jobdesc FOREIGN KEY (titlecode,jobcode1,levelcode) REFERENCES JobCode (titlecode,jobcode,levelcode) ON DELETE CASCADE ON UPDATE CASCADE) TYPE=INNODB; This is a create script for a job description table. Job descriptions are related to a jobcode table. That table in turn is related to joblevel and jobtitle tables (i.e., the job title and job level determine the job code). The jobcode is needed for each job description. One problem I have is that the create scripts generated from the ER tool makes all fields in the job description entity NOT NULL. If I try to insert the description code (desccode), the job description (jobdescription) and the associated job code (jobcode) I get the following error: #1216 - Cannot add or update a child row: a foreign key constraint fails This happens if I just try to insert the desccode, the jobdescription, and jobcode data. I think this is happening because jobcode1, levelcode, and titlecode are NOT NULL so when I update the record it attempts to enter data (NULL) into these fields which are child rows. Can someone explain what I should do? Should I just change these fields of data into NULL? I'm literally just trying to populate the tables with enough data to run some test queries. Any suggestions? Todd -- 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
Re: Tricky Date Query
Mike Blezien wrote: Hello, Not sure this is possible to do with SQL or if needs to be done with in the application programming language we're using(Perl), but I need to get the a weekdate, IE SELECT EXTRACT(DAY FROM NOW()) which would return 30 Now what I need to do, is if the query above is greater or less then 15, get the next month or current month date for the 15th. IE if I ran this query today, and the value is 30(15), I need to generate the date 2004-09-15. If the query was run on 2004-09-14(15) generate the date 2004-09-15, if the query was run on 2004-12-25(15), generate the date for 2005-01-15 Is this possible to do with MySQL ?? Thx's Mickalo Yes. How about SELECT IF(DAYOFMONTH(CURDATE()) = 15, CONCAT(YEAR(CURDATE()), '-', MONTH(CURDATE()), '-15'), CONCAT(YEAR(CURDATE() + INTERVAL 1 MONTH), '-', MONTH(CURDATE() + INTERVAL 1 MONTH),'-15')) AS next15; or better yet SELECT IF(DAYOFMONTH(CURDATE()) = 15, DATE_FORMAT(CURDATE(), '%Y-%m-15'), DATE_FORMAT(CURDATE() + INTERVAL 1 MONTH, '%Y-%m-15')) AS next15; If you have mysql 4.1, there are more date functions, which would allow this alternative: SELECT CURDATE() + INTERVAL MOD(DAY(LAST_DAY(CURDATE())) + 15 - DAY(CURDATE()), DAY(LAST_DAY(CURDATE( DAY AS next15; There may be others. See the manual for all the date and time functions http://dev.mysql.com/doc/mysql/en/Date_and_time_functions.html. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
multiple table query
Can anyone help me optimise the way I do this?
I have two tables, 'article' and 'user'. Article has a user_id which
related to the user table.
When selecting all articles from the the article table, I'd like to be
able to get the username of the user_id. Currently I'm doing this as a
separate query whilst looping through the articles, but this is
obviously causing way too many DB calls.
some pseudo code:
SELECT * FROM article
foreach article
{
SELECT username FROM user WHERE id=$user_id
}
Perhaps I need a join, or maybe just a more complex query -- can any
one lend a hand?
My guess is maybe something like
SELECT article.title, article.user_id, user.username
FROM article, user
WHERE user.id = article.user_id
---
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]
