[BUG] SHOW INDEX bug for fulltext indexes in MySQl 4.0.17
Here's a recipe: create table foo (foo text, bar text); create fulltext index foo on foo (foo, bar); mysql> show index from foo; +---++--+--+-+---+-+--++--++-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +---++--+--+-+---+-+--++--++-+ | foo | 1 | foo |1 | foo | A | NULL |1 | NULL | YES | FULLTEXT | | | foo | 1 | foo |2 | bar | A | NULL |1 | NULL | YES | FULLTEXT | | +---++--+--+-+---+-+--++--++-+ Sub_part should be NULL for both of these columns. The same thing happens for a single column fulltext index. -dave /*=== House Absolute Consulting www.houseabsolute.com ===*/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: relational is relational is relational, but ...
On Tue, 3 Dec 2002, Jon Frisby wrote: > However, from a DB design standpoint, it is generally considered > MASSIVELY unwise for your PRIMARY KEY value to have any > "business-meaning". If it has no "business-meaning", it never needs to > change. Ever. Really, the issue is that you don't want a primary key that is volatile. It doesn't matter if it has business meaning. For example, a social security number is a pretty decent pk for a table of people. Of course, the whole primary key concept is not really part of relational theory, which just talks about candidate keys. > As for what constitutes a "relational" database, the closest thing to an > authoritative source on the subject would be E.F. Codd's 12 rules for a > relational database. Since he invented the concept, he should > theoretically be the authority. Apparently, not one database on the > market conforms to all 12 rules. Not Oracle, or PostgreSQL, and > certainly not MySQL. I think that Codd's 12 rules are not considered entirely sufficient for a system to be truly relational any more, but they're probably a good start. > For example, any database that allows you to define a table without > defining a PRIMARY KEY on that table is immediately disqualified. Actually, the absence of any keys is the problem, not a primary key specifically. In other words, you must say that at least one column/column group is sufficient for unique indentification of individual rows. Or put more simply, allowing duplicates breaks relational theory. > Generally speaking, a database can be considered "effectively" > relational if it allows JOINs. If it supports FOREIGN KEYs (which > InnoDB does -- ON CHANGE CASCADE is an optional behavior whose function > is a convenience and not implicitly part of what FOREIGN KEYs are all > about) then it is for all intents and purposes, relational. That really depends on who you ask. The folks at dbdebunk.com would beg to differ, and I lean towards their interpretation. -dave /*== www.urth.org we await the New Sun ==*/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
ANSI mode determination?
Is there any programmatic way (SQL, C-level API) way to determine whether or not a given mysql server is running in ANSI mode or not? If not, this would be most helpful, as otherwise it is impossible to know how to quote identifiers (` or "). -dave /*== www.urth.org we await the New Sun ==*/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: INSERTing into joined tables?
On Wed, 9 Jan 2002, Rick Emery wrote: > First, you are describing a "one-to-many" relationship, bceasue one "file" > record can be referenced by many "people" records. If this is the case, you > may wish to re-design your tablse such that a "people record" contains a > "file_id" field. You can then do away with the "filespeople" table > altogether. Normalization is a good thing; but not when it is at the > detriment of good design including how one processes it. That last statement makes no sense. Normalization is good design. To say that there could be too much normalization at the expense of design is a non-sequitur. To the original poster, it looks like your design is just fine. Stick with it. > FYI. Your "filespeople" file indicates that both fields are PRIMARY keys. > That cannot be. Only one field may be PRIMARY. I just now tried it to be > certain. What the heck are you talking about?! Multi-column primary keys are certainly allowed in relational databases. A primary key is simple the set of one or more columns by which any row in a table can be uniquely identified. In the case of the table you're referencing, it is certainly necessary to have both column as part of the primary key, as neither is sufficient by itself to uniquely identify a single row. I'm not sure what you tried but I assure you that this statement works for MySQL, and variants thereof will work for pretty much any RDBMS you care to name. create table test (t1 int not null, t2 int not null, primary key(t1, t2)); -dave /*== www.urth.org we await the New Sun ==*/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: 3-letter stop words in FULLTEXT
On Fri, 4 Jan 2002, Chris Lott wrote: > How is full-text working for you otherwise? Is it excruciatingly slow to > generate indexes and optimize tables as some have said? Oh god, yes. Running 3.23.46 on Linux with a 1Ghz PIII and 4 GB of RAM it _still_ takes a heck of a long time to do optimize/repair tables. -dave /*== www.urth.org we await the New Sun ==*/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Selecting unknown columns
On Mon, 17 Dec 2001, Alex Kirk wrote: > I've got a 700-question multiple choice quiz that I've got imported into > MySQL, with one small problem: the answers to the quiz are not a separate > column. I have three columns for each of the possible answers; the correct > answer is denoted by a "*" in the appropriate field. For example, if I have > columns A, B, and C for the three answers, they would contain data such as > "George Washington", "*Abraham Lincoln", and "Bill Clinton". > > I'd like to be able to select out all the fields with a * in them, and then > place them in a new, blank column called Answer. However, I'm really not > sure how to select these out, since I can't say "where like "*%". > Further complicating the matter is the fact that, in a few of the questions, > there are multiple answers, and thus multiple columns with the asterisk in > them. > > How would I go about selecting out these fields? Even if I can only get > those with one answer, I'd be immensley appreciative. _If_ you can re-import I'd _strongly_ suggest you change your schema. You'd have one table for questions: Question question_id int PK question text Answer question_id int PK (part 1) display_order int PK (part 2) answertext is_correcttinyint (or bool, or whatever) Then it would _really_ easy to find all the correct answers, no matter how many you had. Ok, that's not necessarily the answer you were looking for, but if you did the import once maybe you can do it again. And if not at least its something to think about next time. -dave /*== www.urth.org We await the New Sun ==*/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Problem with GROUP BY ... DESC
On Tue, 4 Dec 2001, AJ wrote: > Ver 11.15 Distrib 3.23.46, for pc-linux-gnu (i686) > > The problem that has cropped up I have is that in all GROUP BY column DESC > statements, the DESC is now not being recognized, and the query is being > returned in ascending order. If I use ORDER BY... DESC that works fine. I'm seeing the same bug on 3.23.45 (and maybe others but I can't remember all the versions I've played with recently). It does the same thing where it accepts the syntax but does not respect it (and returns rows in the default ASC ordering). -dave /*== www.urth.org We await the New Sun ==*/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: insert BLOB in perl
On Mon, 29 Oct 2001, Jindo Soul wrote: > Why this error exists is beyond my knowledge though. > I am guessing that Perl DBI escapes ' \ in bind_param call. > And MySQL escapes the data again when using big5 charset, > which ultimate causes an error because of double escaping. Well, its not that MySQL does any escaping but you're basically right. The DBD::mysql driver will escape all instances of the backslash. If the backslash is the last character in a string _and_ the last part of the string represents a multibyte big5 character, there's a problem. Perl turns 'A\' into 'A\\'. But MySQL will see that as 'A\' plus '\''. In other words, the second backslash is seen as escaping the single quote, leaving the quote not terminated. I tried patching the DBD::mysql code a year or so back but my C skills are very weak and I couldn't quite get it. I still have the patches somewhere if someone more skilled than me wants to take a crack at it. Basically, I was looking to add a 'mysql_wide_charset' option that could be passed to the driver. Nowadays, it might be better if instead it just checked with the server as soon as it connects (though that only works for newer MySQLs). -dave /*== www.urth.org We await the New Sun ==*/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: SELECT .. OR from multiple tables
On Tue, 14 Aug 2001, Bruce Stewart wrote: > How about a UNION query...? Too bad MySQL doesn't support them yet ;) -dave /*== www.urth.org We await the New Sun ==*/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Data modeling tool
On Mon, 12 Feb 2001 [EMAIL PROTECTED] wrote: > Moving back and forth is a manual process, which is a bit frustrating. > Since dia stores everything in XML, I'm considering what it would take to > make some sort of syncronization. > > I've been planning on looking into Alzabo too, which was already > mentioned, but I haven't had the time for that either. :-( One feature I've been considering for Alzabo is to be able to somehow process dia's XML and generate a schema from it. Alzabo will (sometime soon, I hope) be able to read/write DBML, an XML language Barrie Slaymaker is working on (with some input from me and others). If it is possible to transform from dia's XML to DBML and vice versa that would be extremely slick, I think. -dave /*== www.urth.org We await the New Sun ==*/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Data modeling tool
On Sat, 10 Feb 2001, Cal Evans wrote: > As much as I love creating models by hand, my databases are growing to the > point where I need a tool to help me "see" them. Does anyone know of a tool > like ERWin or ER/Studio that supports MySQL? I have a project called Alzabo (alzabo.sourceforge.net) that is a combination data modeller/RBDMS-OO mapper (for Perl). The data modeller is web based (though I plan to eventually have other UIs) and requires Perl and HTML::Mason (www.masonhq.com). Currently it supports Mysql and Postgres, with the Mysql support being a bit more mature. -dave /*== www.urth.org We await the New Sun ==*/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
