Re: Getting SQL errors porting databases between MySQL v4 and v5
The 'group' column needs to be quoted (use --quote-names with mysqldump). cheers, --bemansell On 3/26/07, Rob Tanner <[EMAIL PROTECTED]> wrote: Hi, I am porting over 6 databases from a MySQL v4 installation to a MySQL v5 installation and getting an SQL error in the process. I am using the following command to dump the data in the v4 installation: mysqldump -u root --password=secret --add-drop-table --databases db1 db2 db3 db4 db5 db6 > db.sql and using the following command to upload the databases on to the v5 installation: mysql -u root -p < db.sql But then I get this error: ERROR 1064 (42000) at line 140784: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'group varchar(5) default NULL, PRIMARY KEY (id) ) TYPE=MyISAM' at line 8 The whole create table sequence from the db.sql file is: DROP TABLE IF EXISTS admission_quotes; CREATE TABLE admission_quotes ( id int(4) NOT NULL auto_increment, quote text, author text, category text, class text, active text, group varchar(5) default NULL, PRIMARY KEY (id) ) TYPE=MyISAM; Any idea what the issue is? Is there something special I need to do when porting the databases between MySQL v4 and v5? Thanks, Rob -- Rob Tanner UNIX Services Manager Linfield College, McMinnville OR -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: getting a " Duplicate entry" error when inserting a new record - but there is no duplicate record
Jerad - First of all, please reply all with a SHOW CREATE TABLE of the table you're inserting the row into, so that we can identify the indexes you've established for the table. thanks, --bemansell On 3/7/07, jerad sloan <[EMAIL PROTECTED]> wrote: when i try to insert the string " http://vids.myspace.com/index.cfm?fuseaction=vids.individual&videoid=2012774576";, it returns "error : Duplicate entry ' http://vids.myspace.com/index.cfm?fuseaction=vids.individual&videoid=' for key 3" when i check the table and do a search for the string, there is no match...it seems that they index is not using the full field to index and finding a duplicate index key even though there is not a dupliate field - is there a 70 character limit on varchar unique indexes or something weird?. this is an InnoDB table with 2 unique keys and a few other keys...i'm assuming key 3 deals with this data since it is one of the unique keys and it shows this data in the error...but how do i confirm which is 'key 3'? i've been searching around and can't find any mention known issues around this. any help/suggestions would be greatly appreciated. thanks, jerad -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT single row from 2 tables with WHERE clause
It sounds to me like you're needing to use a left outer join on t2. Give that a shot instead of the inner join you're currently using. Cheers, -bemansell On 2/17/07, Kerry Frater <[EMAIL PROTECTED]> wrote: I am trying to select a particular row from a table and include a column for aq second table but I cannot get the result I am after. I have "table1" with 1000 rows and "table2" with 12 rows. The relationship between the tables is a column "linkedfield". Table1 has a unique key called "lookup" If I use the code SELECT t1.*,t2.thedescription AS t2desc FROM table1 AS t1, table2 AS t2 where (t1.lookup='Z') I get a result of 12 rows (as expected) SELECT t1.*,t2.thedescription AS t2desc FROM table1 AS t1, table2 AS t2 where (t1.linkfield=t2.linkfield) I get 1000 rows as expected SELECT t1.*,t2.thedescription AS t2desc FROM table1 AS t1, table2 AS t2 where (t1.lookup='Z') and (t1.linkfield=t2.linkfield) returns 1 row if there is an active link between the two tables and returns 0 rows if there isn't. This is where I am stuck. In the last example I would like the 1 row whether there is an "active" link or not. The difference will be simply that the "t2desc" rsulting column will be blank or contain a value. Can anyone help me with the logic? Kerry -- 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: Numeric sorting within a string
This is totally possible... Chris say your table is test, and the column is test_col.. use the following, and if the number is always at the end.. and always has a space in front of it this will work: SELECT test_col, SUBSTRING_INDEX(test_col, '', 1) as test_col_str, SUBSTRING_INDEX(test_col, ' ', -1) + 0 as test_col_num FROM test ORDER BY test_col_str, test_col_num; good luck, --bemansell On 2/15/07, Chris White <[EMAIL PROTECTED]> wrote: I'm wondering if there is a way to do a numeric sort when the number exists in a string. More clearly, take for example: Radius 1200 Radius 1500 Radius 1800 Radius 300 Radius 600 Radius 900 Being that character wise 1 is before 3, I'm wondering if there's a way through the database to achieve: Radius 300 Radius 600 Radius 900 Radius 1200 Radius 1500 Radius 1800 If not I'll try and handle it through PHP instead. -- 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: Looking for advice on how to store and query some data
Russell - This should basically work for selecting the most recently selected preference... SELECT DISTINCT personID, classification FROM results_table ORDER BY date DESC On 4/10/06, Russell Horn <[EMAIL PROTECTED]> wrote: > > Hi, > > I'm storing data against a bunch of people and want to track how it > changes. So, I have a person table where everyone has a person ID and a > results table a bit like this: > > | personID | classification | date | > | 1| 0 | 2005-11-10 | > | 2| 3 | 2005-11-10 | > | 3| 1 | 2005-11-10 | > | 4| 0 | 2005-11-10 | > | 1| 3 | 2005-12-01 | > | 4| 2 | 2005-12-03 | > | 1| 2 | 2005-12-23 | > | 5| 1 | 2006-01-03 | > | 2| 2 | 2006-12-03 | > > This lets me see how things change as a pattern, for example comparing a > a > > SELECT classification WHERE DATE < '2006-01-01' GROUP BY classification > > and comparing it to: > > SELECT classification WHERE DATE >= '2006-01-01' AND DATE <= > '2006-01-31' GROUP BY classification > > But is there a way I can select every personID's most recently expressed > preference? > > I hope this makes sense - in the table above, person 1 had a > classification of 0 at 10th November, but this changed to 2 on 23rd > Decembner. Can I write a query to select personID once together with > their latest preference, or indeed their preference as expressed at a > specific point in time? > > Thanks as ever for any suggestions. > > Russell. > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > >
Re: MySQL Control Center works with v4.0.23 -- how about V5?
>From what I recall MysqlCC has been deprecated and has been replaced by MySQL Administrator (which does support v5). I don't know think mysqlcc supports v5. good luck, --bemansell On 8/30/05, Siegfried Heintze <[EMAIL PROTECTED]> wrote: > > [Siegfried Heintze] I love MySQL Control center. I can make it work for > MySQL v4.0 but not the latest (v5). It simply does not connect to the V5 > Mysql server I just installed. It seems to hang on the connection. > > Am I doing something wrong or does MySQL Control center not support 5? > > Assuming it does not work with MySQL v5, is there a similar substitute > that > does work with V5 that will enable me to look at my data and try out SQL > statements interactively? > > I was using the GUI program that comes with MySQL V5 (I think it is the > MySQL Administrator) and that looks very nice too. However, I could not > figure out how to make it view the contents of my tables. It looked like > it > was supposed to be able to do that from the screen shots. > > Thanks, > Siegfried > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > >
Re: Fulltext Simple Question
Scott -
Check this excerpt out (
http://dev.mysql.com/doc/mysql/en/fulltext-search.html ) from the MySQL
Documentation. I hope it helps!
--bemansell
...
"Every correct word in the collection and in the query is weighted according
to its significance in the collection or query. This way, a word that is
present in many documents has a lower weight (and may even have a zero
weight), because it has lower semantic value in this particular collection.
Conversely, if the word is rare, it receives a higher weight. The weights of
the words are then combined to compute the relevance of the row.
Such a technique works best with large collections (in fact, it was
carefully tuned this way). For very small tables, word distribution does not
adequately reflect their semantic value, and this model may sometimes
produce bizarre results. For example, although the word ``MySQL'' is present
in every row of the articles table, a search for the word produces no
results:
mysql> SELECT * FROM articles
-> WHERE MATCH (title,body) AGAINST ('MySQL');
Empty set (0.00 sec)
The search result is empty because the word ``MySQL'' is present in at
least 50% of the rows. As such, it is effectively treated as a stopword. For
large datasets, this is the most desirable behavior---a natural language
query should not return every second row from a 1GB table. For small
datasets, it may be less desirable.
A word that matches half of rows in a table is less likely to locate
relevant documents. In fact, it most likely finds plenty of irrelevant
documents. We all know this happens far too often when we are trying to find
something on the Internet with a search engine. It is with this reasoning
that rows containing the word are assigned a low semantic value for *the
particular dataset in which they occur*. A given word may exceed the 50%
threshold in one dataset but not another.
The 50% threshold has a significant implication when you first try full-text
searching to see how it works: If you create a table and insert only one or
two rows of text into it, every word in the text occurs in at least 50% of
the rows. As a result, no search returns any results. Be sure to insert at
least three rows, and preferably many more."
On 5/25/05, Scott Purcell <[EMAIL PROTECTED]> wrote:
>
> Hello,
> I am running 4.0.15 for Win95/98 and am working through the docs.
>
> I created a "text" type field with a 'fulltext' index. As I am
> experimenting, I have run into a couple of questions:
>
> First off, I was having trouble getting results. So I added the word
> "foobar" to one of the descriptions:
> and that worked with this query:
> select * from item where match(name, description) against('foobar')
>
>
>
> I have a word 'red' that appears 5-10 times, in a tmp table of 60 records.
> If I run that query with 'red'
> select * from item where match(name, description) against('red');
> it returns empty set
>
> Upon reading, it looks like it is really trying to only get "unique" names
> from the index. But in my case the 'red' is a description that I would like
> to get back. Anyway to force this to return results?
>
> Any info would be helpful. I have read, but it gets a little confusing
> first time through.
>
> Thanks,
> Scott
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
>
>
Re: Fulltext search string less than 4 characters
Lee, establish the fulltext minimum word length system variable as follows... [mysqld] ft_min_word_len=3 reference: http://dev.mysql.com/doc/mysql/en/Fulltext_Fine-tuning.html cheers, --bemansell Brian E. Mansell MySQL Professional On Thu, 06 Jan 2005 20:59:23 -0500, leegold <[EMAIL PROTECTED]> wrote: > I want to know on Solaris how I could lower the the minimum fulltext > search string from 4 to 3. Right now using the FullText search any > string less than 4 chars is ignored. I'm sure there's a link explaining > how. Maybe UNIX help in general on his would be good as well. > > Thanks, Lee G. > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: table types
SHOW TABLE STATUS (it includes type/storage engine as one of the returned columns) --bemansell Brian E. Mansell MySQL Professional On Wed, 15 Dec 2004 19:21:24 -0800, sol beach <[EMAIL PROTECTED]> wrote: > How do I find out what table type is associated with each of the > tables in MYSQL? > > -- > 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: Trying to download database
Julie - Please specify which database binaries you are downloading. If you could provide us with the url's that would be much appreciated in helping you troubleshoot this. For Windows binaries, I would refer to the HTTP / FTP mirror links listed at the bottom of this page: http://dev.mysql.com/get/Downloads/MySQL-4.1/mysql-4.1.7-win.zip/from/pick I hope that helps! --bemansell Brian E. Mansell MySQL Professional On Tue, 14 Dec 2004 12:30:08 -0500, Woo, Julie <[EMAIL PROTECTED]> wrote: > I've downloaded the database from several different servers today and > each time I try to uncompress the file, I get the error message that the > file is not a valid archive. > I've tried using the built in compression tools in win 2003, and I've > also tried downloading WinZip 9.0 eval to attempt the unzip. Neither > work. > Please advise. > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 4.1 table/column names in UTF8
>From http://dev.mysql.com/doc/mysql/en/Upgrading-from-4.0.html : "# Important note: MySQL 4.1 stores table names and column names in UTF8. If you have table names or column names that use characters outside of the range from `A' to `Z', you may have to do a mysqldump of your tables in MySQL 4.0 and restore them after upgrading to MySQL 4.1. The symptom for this problem is that you get a table not found error when trying to access your tables. In this case, you should be able to downgrade back to MySQL 4.0 and access your data." In our MySQL 4.0 databases today, we have log tables with names like 'system_log_day_20041026'. Based upon the `A` to `Z` conditional in the above statement, are we in any way at risk of not being able to access our tables? thanks much, --bemansell Brian E. Mansell MySQL Professional On Tue, 26 Oct 2004 13:34:49 -0500, Matt Wagner <[EMAIL PROTECTED]> wrote: > Hi, > > MySQL 4.1.7, a new version of the popular Open Source/Free Software > Database Management System has been released. It is now available in > source and binary form for a number of platforms from our download pages > at http://dev.mysql.com/downloads/ and mirror sites. > > Note that not all mirror sites may be up-to-date at this point. If you > cannot find this version on a particular mirror, please try again later or > choose another download site. > > This is the first 4.1 production release. > > Please refer to our bugs database at http://bugs.mysql.com/ for more > details about the individual bugs fixed in this version. > > News from the ChangeLog: > > Changes in release 4.1.7 > >Functionality added or changed: > * InnoDB: Made LOCK TABLES behave by default like it did before >MySQL 4.0.20 or 4.1.2: no InnoDB lock will be taken. Added a >startup option and settable system variable innodb_table_locks for >making LOCK TABLE acquire also InnoDB locks. See section >"Restrictions on InnoDB Tables" in the manual. >(Bug #3299, Bug #5998) > >Bugs fixed: > * Fixed a bug with FOUND_ROWS() used together with LIMIT clause in >prepared statements. (Bug #6088) > * Fixed a bug with NATURAL JOIN in prepared statements. (Bug #6046). > * Fixed a bug in join of tables from different databases having >columns with identical names (prepared statements). (Bug #6050) > * Now implicit access to system time zone description tables (which >happens when you set time_zone variable or use CONVERT_TZ() >function) does not require any privileges. (Bug #6116) > * Fixed a bug which caused the server to crash when the deprecated >libmysqlclient function mysql_create_db() was called. (Bug #6081) > * Fixed REVOKE ALL PRIVILEGES, GRANT OPTION FROM user so that all >privileges are revoked correctly. (Bug #5831). This corrects a >case that the fix in 4.1.6 could miss. > * Fixed a bug that could cause MyISAM index corruption when key >values start with character codes below BLANK. This was caused by >the new key sort order in 4.1. (Bug #6151) > * Fixed a bug in the prepared statements protocol when wrong >metadata was sent for SELECT statements not returning a result set >(such as SELECT ... INTO OUTFILE). (Bug #6059) > * Fixed bug which allowed one to circumvent missing UPDATE privilege >if one had INSERT and SELECT privileges for table with primary key. >(Bug #6173) > * Fixed a bug in libmysqlclient with wrong conversion of negative >time values to strings. (Bug #6049). > * Fixed a bug in libmysqlclient with wrong conversion of zero date >values (-00-00) to strings. (Bug #6058) > * Fixed a bug that caused the server to crash on attempt to prepare >a statement with RAND(?). (Bug #5985) > * Fixed a bug with handling of DATE, TIME, and DATETIME columns in >the binary protocol. The problem is compiler-specific and could >have been observed on HP-UX, AIX, Solaris9, when compiling with >native compiler. (Bug #6025) > * Fixed a bug with handling of TINYINT columns in the binary >protocol. The problem is specific to platforms where the C >compiler has the char data type unsigned by default. (Bug #6024) > * Fixed problem introduced in MySQL 4.0.21 where a connection >starting a transaction, doing updates, then FLUSH TABLES WITH READ >LOCK, then COMMIT, would cause replication slaves to stop >complaining about error 1223. Bug surfaced when using the InnoDB >innobackup script. (Bug #5949) > > Enjoy! > >Matt > > -- > Matt Wagner, Production Engineer > MySQL AB, www.mysql.com > Northfield, MN, USA > > -- > 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.
Re: Dyna III electronic ignition install questions R80/7
What build of MySQL are you running? I don't believe an electronic ignition is included in 4.0 or 4.1. --bemansell On Mon, 4 Oct 2004 17:07:07 -0700 (PDT), Don Dachner <[EMAIL PROTECTED]> wrote: > Anyone have experience with installing the Dyna III electronic ignition? > > I put it on my R80/7, but since it wasn't running before I did the install, and it's > not running now, I'm not sure if I installed it correctly or not. > > But, after I did the install, I tried to do the static timing as per the > instructions that came with it, and i can't get the test light to come on at all > which says something, I think. > > Any help appreciated. > > Thanks, > > Don > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump overflow?
24 = Too many open files. I think you can resolve this issue by increasing the number of file descriptors available to mysqld by setting an appropriately higher 'open-files-limit' in your configuration. --bmansell Brian E. Mansell MySQL Professional On Thu, 22 Jul 2004 13:20:24 +0200, Wolfgang Riedel <[EMAIL PROTECTED]> wrote: > Hi, > > I want to save a database with 'mysqldump' and get the following error: > mysqldump: Got error: 1105: File '/var/lib/mysql/mydb/J99.MYD' not found > (Errcode: 24) when using LOCK TABLES > > But the file *does* exist: > > ls -l /var/lib/mysql/mydb/J99.MYD > -rw-r-1 mysqlmysql 224 Feb 24 2002 > /var/lib/mysql/mydb/J99.MYD > > What's the reason for the error? > Is there a possibility, that the database is to large? It has about 1000 > tables, but the database altogether has an amount of 35 MB. > Could I tell mysqldump to access the database in portions? > > Regards, > Wolfgang > > > -- > 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: foreign key constraints
Sergei, Check out Paul DuBois' book MySQL - The Definitive Guide (2nd edition). It has a few good chapters that discuss foreign key constraints. --bmansell Brian E. Mansell MySQL Professional On Thu, 22 Jul 2004 13:06:07 -0700, Sergei Skarupo <[EMAIL PROTECTED]> wrote: > Hi everyone, > > I'd like to learn more about foreign key constraints... > > I read this section of the manual... > > http://dev.mysql.com/doc/mysql/en/InnoDB_foreign_key_constraints.html > > Where can I find a more detailed description with some examples? > > Thanks, > > Sergei > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ENUM vs TINYINT
Cemal,
I recall hearing a similar question mentioned in a previous email
thread. In fact, here it is:
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&threadm=c6h60e%2419dd%241%40FreeBSD.csie.NCTU.edu.tw&rnum=1&prev=/groups%3Fq%3D%2522enum%2Bor%2Btinyint%2522%26hl%3Den%26lr%3D%26ie%3DUTF-8%26selm%3Dc6h60e%252419dd%25241%2540FreeBSD.csie.NCTU.edu.tw%26rnum%3D1
(thread is titled "enum or tinyint")
I hope that helps!
On Thu, 24 Jun 2004 21:17:09 +0300, Cemal Dalar <[EMAIL PROTECTED]> wrote:
>
> Hi all,
>
> I need a boolean column and at to this time I always used ENUM('Y','N')
> for this. I'am wondering that will there be a performance difference between
> using ENUM('Y','N') and TINYINT(1) or BOOLEAN?. And put 0 or 1 to TINYINT
> column.
>
> Best Regards,
> Cemal Dalar a.k.a Jimmy
> System Administrator & Web Developer
> http://www.dalar.net
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Professional certification
I took the certification exam this morning and passed. When should I expect to receive the certificate (and other items) in the mail? --bmansell -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Correcting Time
This page may help: http://dev.mysql.com/doc/mysql/en/Timezone_problems.html --bmansell On Wed, 16 Jun 2004 14:32:22 -0500, Mike Blezien <[EMAIL PROTECTED]> wrote: > > Hello, > > Somehow the mysql server time is set 3 hours earlier than the system time(CDT), > is there a way to correct this so they both are the same ? > > TIA > -- > MikeBlezien > =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= > Thunder Rain Internet Publishing > Providing Internet Solutions that work! > http://www.thunder-rain.com > =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: general qn
Devshed has a good tutorial: http://www.devshed.com/c/a/MySQL/MySQL-Installation-and-Configuration/ --bmansell On Wed, 16 Jun 2004 16:37:44 +0100, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > > Hi folks, > > I'm trying to install mysql on linux and it's giving me trouble. I'm new > to linux and new to databases so my downfall isn't too surprising. > I've been going by the readme file in the package but I can't seem to get > my head around it. Does anyone have a step by step approach they could post? > > Thanks. > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to COUNT rows when they have a COUNT in them
Dave - I would recommend (if not done so already) adding an index on owner. That should improve the speed quite a bit. On Tue, 15 Jun 2004 21:06:47 +, Dave Torr <[EMAIL PROTECTED]> wrote: > > Thanks - that is basically what I used to do (it works fine now on 4.1.2) > but it was very slow as there are a LOT of rows and this method returned all > of them. > > >From: Garth Webb <[EMAIL PROTECTED]> > >To: Dave Torr <[EMAIL PROTECTED]> > >CC: [EMAIL PROTECTED] > >Subject: Re: How to COUNT rows when they have a COUNT in them > >Date: Tue, 15 Jun 2004 09:54:19 -0700 > > > >You could also try: > > > > SELECT owner, COUNT(*) FROM pet GROUP BY owner; > > SELECT FOUND_ROWS(); > > > >On Mon, 2004-06-14 at 20:41, Dave Torr wrote: > > > Thanks - this did not work for me as I am on 4.0.17 - presumably this > >works > > > on 4.1 (seems to need the SubQuery feature)? If so I will upgrade > > > immediately! > > > > > > > > > >From: Yayati Kasralikar <[EMAIL PROTECTED]> > > > >To: Dave Torr <[EMAIL PROTECTED]> > > > >CC: [EMAIL PROTECTED] > > > >Subject: Re: How to COUNT rows when they have a COUNT in them > > > >Date: Mon, 14 Jun 2004 23:37:15 -0400 > > > > > > > >Following query does what you want: > > > > > > > >SELECT COUNT(*) from (SELECT COUNT(*) as c FROM pet GROUP BY owner > >HAVING > > > >c>1) as temp > > > > > > > >-Yayati > > > > > > > >Dave Torr wrote: > > > > > > > >>Probably simple but I can't figure it out! > > > >> > > > >>THe manual section 3.3.4.8 has the example > > > >> > > > >>SELECT owner, COUNT(*) FROM pet GROUP BY owner > > > >> > > > >>which is fine. Now what I want to do is count the number of rows this > > > >>returns. Actually of course this is trivial - I can just count how > >many > > > >>owners there are. > > > >> > > > >>What I actually have is something similar to > > > >> > > > >>SELECT owner, COUNT(*) as c FROM pet GROUP BY owner HAVING c>1 > > > >> > > > >>(ie I want to see the owners who have more than one pet). And I just > >want > > > >>to know how many there are - at the moment I am having to retreive the > > > >>full data set (which is large in my case). > > > >> > > > >>What I want is something like > > > >> > > > >>SELECT COUNT(SELECT owner, COUNT(*) FROM pet GROUP BY owner HAVING > >c>1) > > > >> > > > >>but that doesn't work > > > >> > > > >> > > > >> > > > > > > > > > > > > > > > > > >-- > >MySQL General Mailing List > >For list archives: http://lists.mysql.com/mysql > >To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Max
Just a minor correction...
select YourField from YourTable order by YourField DESC limit 1;
('DESC' in order to return the greatest value first)
On Tue, 11 May 2004 17:28:05 +0200, Mikhail Entaltsev
<[EMAIL PROTECTED]> wrote:
>
> Hi,
>
> select YourField from YourTable order by YourField limit 1;
>
> Best regards,
> Mikhail.
>
>
> - Original Message -
> From: "A Z" <[EMAIL PROTECTED]>
> To: <[EMAIL PROTECTED]>
> Sent: Tuesday, May 11, 2004 3:01 PM
> Subject: Max
>
> >
> > Hi,
> >
> > A field of type VarChar() with following syntax:
> > ABA1.
> > How to get the Max value. Bearing in mind that value
> > ABA10 comes before ABA2.
> >
> > regards
> >
> >
> >
> >
> >
> >
> >
> > Yahoo! Messenger - Communicate instantly..."Ping"
> > your friends today! Download Messenger Now
> > http://uk.messenger.yahoo.com/download/index.html
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>
>
> >
> >
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: newbie data import question
Luther - I would recommend picking up Paul DuBois' book MySQL - The Definitive Guide. The MySQL.com website has docs which will answer your question, but I think a good text always helps with learning MySQL. If you have any specific questions or issues. Please feel free to ask. Thanks, --bmansell -Original Message- From: luther van dam [mailto:[EMAIL PROTECTED] Sent: Thursday, May 06, 2004 7:24 AM To: [EMAIL PROTECTED] Subject: newbie data import question I am new to MySQL but I some basic database experience. I have about 50,000 rows of data in a CSV file. Where I can find some examples of SQL scripts that show how to: a. Create a database X. b. Create a table Y. c. Import the 50,000 rows of CSV data into table Y. I am running MySQL on Red Hat 9 -- ___ Sign-up for Ads Free at Mail.com http://promo.mail.com/adsfreejump.htm -- 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: Title Case Problem
As far as I know INITCAP() isn't a valid function in MySQL. I believe it is supported in Oracle (sqlplus). --bmansell -Original Message- From: Michael J. Pawlowsky [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 05, 2004 1:00 PM To: [EMAIL PROTECTED] Subject: RE: Title Case Problem Use INITCAP. SELECT name, INITCAP(name) new_name FROM customer WHERE firstname IS NULL *** REPLY SEPARATOR *** On 5/5/2004 at 12:43 PM Brian Mansell wrote: >I may be wrong, but there isn't an easy method for completing this in >SQL alone. -- 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: Title Case Problem
I may be wrong, but there isn't an easy method for completing this in SQL alone. If you're using PHP, process the field with ucwords( ) http://www.php.net/ucwords on the other hand, capwords() in Python does something similar, but replaces multiple spaces with individual spaces. --bmansell -Original Message- From: Andrew Braithwaite [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 05, 2004 11:25 AM To: '[EMAIL PROTECTED]' Subject: Title Case Problem Hi All, I have a table with upper case text. I want to use a function in my select statement the puts this text in title case so "MORE FOO YOU WIBBLE" becomes "More Foo You Wibble". Thanks for any help Cheers, Andrew Sql, query -- 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: Is it possible to get a timestamp from the server ?
To get the current year, use: SELECT DATE_FORMAT(NOW(), '%Y'); Basically you can use the DATE_FORMAT to show the present time as desired. --bmansell -Original Message- From: Greg Hosler [mailto:[EMAIL PROTECTED] Sent: Friday, April 23, 2004 5:08 PM To: [EMAIL PROTECTED] Subject: Is it possible to get a timestamp from the server ? Hi, I have a database application that I am porting to MySQL. one of the things that the application needs to do is to get a timestamp from the server, so that all instances of the application across a network can timestamp cewrtain records using a common source for the timestamp. e.g. the sql server. In the past, under Informix, we did the following sql: SELECT distinct current year to second FROM systables I've looked around, and I'm not seeing anything equivalent under MySQL. Have I hopefully missed something ? thx for any suggestions / hints / pointers. best rgds, -Greg Hosler +-+ You can release software that's good, software that's inexpensive, or software that's available on time. You can usually release software that has 2 of these 3 attributes -- but not all 3. | Greg Hosler [EMAIL PROTECTED]| +-+ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Replication question
Jonas, It's recommended in the docs that you upgrade your slaves to 5.0.0 before your Master (http://www.mysql.com/doc/en/Replication_upgrade_5.0.html) You shouldn't run into any problems with replicating InnoDB tables.(http://www.mysql.com/doc/en/InnoDB_and_MySQL_Replication.html) good luck! --bmansell From: Jonas Lindén [mailto:[EMAIL PROTECTED] Sent: Thu 3/25/2004 11:56 PM To: [EMAIL PROTECTED] Subject: Replication question Hello list, I am about to embark on a little repliction mission;) I was thinking about setting up a MySQL slave on my Windows box and replicate my MySQL 5.0 Master which runs on my Linux box. Could the mix of architectures become a problem? I also use innodb tables alot, are they replicateble? or are just MyISM tables replicatable. I have not tried anything yet just thought I ask around abit first:) Best regards /Jonas Lindén
