What's better with assumption
Hi, All I've CGI Application using C/C++ Assume that connection establishment by client and MySQL database server is very fast, what's better between : - Persistent Connection - Non Persistent Connection Because as CGI application it's difficult to use persistent connection, except use some tools like SQLRelay And what's the reason for your info ? Thanks -- ady -- email: ady at ebdesk.com adiwicaksono at yahoo.com ady at students.if.itb.ac.id homepage: http://ady97.hypermart.net/ - 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
table corrupted after an error free load
Hi, Sorry to disturb, but I cannot find any answer in online doc and web archive. Description: I insert 34000 rows in a simple table with all text fields, and myisamchk tells the table is corrupted, while a select into outfile does not give any error compared to the original file. How-To-Repeat: 1) create the database under mysql console : create database test; use test; create table kompass( cyid text, dirtitre text, dirnom text, dirfonc text, sigle text, adr1 text, adr2 text, codepost text, ville text, telephone text, telefax text, siren text, siret text, nafcode text, effectif text, jurcode text, email text, cinsee text, posx text, posy text ); load data infile importk.txt into table kompass; 2) check the table under unix shell : local/mysql/bin ./myisamchk ../data/cgctest/kompass.MYI Checking MyISAM file: ../data/cgctest/kompass.MYI Data records: 34715 Deleted blocks: 0 ./myisamchk: warning: 1 clients is using or hasn't closed the table properly - check file-size - check key delete-chain - check record delete-chain - check index reference - check record links MyISAM-table '../data/cgctest/kompass.MYI' is usable but should be fixed a 3Mb zip can be sent to you (I could not upload it at ftp://support.mysql.com/pub/mysql/secret/ because I have no password) Fix: none Submitter-Id: none Originator:Riccardo Cohen Organization: articque Les Roches 37230 Fondettes MySQL support: none Synopsis: table corrupted after an error free load Severity: serious Priority: high Category: mysql Class: Release: mysql-3.23.36 (Official MySQL binary) Environment: === mysqlbug output : === System: Linux jsp 2.2.14-5.0 #1 Tue Mar 7 21:07:39 EST 2000 i686 unknown Architecture: i686 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/egcs-2.91.66/specs gcc version egcs-2.91.66 19990314/Linux (egcs-1.1.2 release) Compilation info: CC='gcc' CFLAGS='-O6 -mpentium ' CXX='gcc' CXXFLAGS='-O6 -mpentium -felide-constructors\ ' LDFLAGS='-static' LIBC: lrwxrwxrwx1 root root 13 Apr 10 2001 /lib/libc.so.6 - libc-2.1.3.so -rwxr-xr-x1 root root 4101324 Feb 29 2000 /lib/libc-2.1.3.so -rw-r--r--1 root root 20272704 Feb 29 2000 /usr/lib/libc.a -rw-r--r--1 root root 178 Feb 29 2000 /usr/lib/libc.so Configure command: ./configure --prefix=/usr/local/mysql '--with-comment=Official MySQL binary' --with-extra- charsets=complex --enable-assembler --with-mysqld-ldflags=-all-static --with-client-ldflags=-all-static --disa ble-shared Perl: This is perl, version 5.005_03 built for i386-linux === it is a Redhat 6.2 system, on an PC/ celeron 466 processor, 128Mb memory, 5Go disk Filesystem 1k-blocks Used Available Use% Mounted on /dev/hda7 254244 37883203234 16% / /dev/hda115856 2442 12584 16% /boot /dev/hda6 2679268129876 2413292 5% /home /dev/hda5 2679268 1012324 1530844 40% /usr /dev/hda8 254244 40187200930 17% /var running a web server apache, with JSP engine on java 1.2 with several applications but none overloaded. I installed the binary version (mysql-3.23.36-pc-linux-gnu-i686.tar.gz). The problem appears also on my own machine, 2 processors NT 4 Sp6a / mysql 3.23.24-beta binary version mysqldump : # MySQL dump 8.13 # # Host: localhostDatabase: cgctest # # Server version3.23.36 # # Table structure for table 'kompass' # CREATE TABLE kompass ( cyid text, dirtitre text, dirnom text, dirfonc text, sigle text, adr1 text, adr2 text, codepost text, ville text, telephone text, telefax text, siren text, siret text, nafcode text, effectif text, jurcode text, email text, cinsee text, posx text, posy text ) TYPE=MyISAM; There is nothing wrong in data/host.err This is the my.cnf configuration file : [mysqld] port= 3306 server-id = 1 skip-locking skip-name-resolve skip-show-database skip-thread-priority # cannot use that one on pthreaded system #skip-networking ## # to accept many many connections arriving at the same time # (do the same in apache) set-variable= back_log=500 # max = 64M for a 256M ram (these megs are shared by all threads) set-variable= key_buffer_size=64M # pool is about 100/300 connections, multiply by the number of apps set-variable= max_connections=2000 # prepare threads before connections arrive set-variable= thread_cache_size=100 # sorting : not too big because it is
Re: Alphabetizing book titles
Hi. Try this: SELECT title FROM titles ORDER BY IF(SUBSTRING(title,1,4)=The ,SUBSTRING(title,5), IF(SUBSTRING(title,1,2)=A ,SUBSTRING(title,3), IF(SUBSTRING(title,1,3)=An ,SUBSTRING(title,4), title))); But in version 3.23.36 the next: SELECT title FROM titles ORDER BY CASE WHEN SUBSTRING(title,1,4)=The THEN SUBSTRING(title,5) WHEN SUBSTRING(title,1,2)=A THEN SUBSTRING(title,3) WHEN SUBSTRING(title,1,3)=An THEN SUBSTRING(title,4) ELSE title END; works right. Goodbye. Dannis. On Thu, Nov 01, 2001 at 01:09:52PM -0500, Ian M. Evans wrote: Back when I was working with MSSQL I needed to alphabetize movie titles in the proper library format where 'A' 'An and 'The are ignored. For MSSQL I was told to use: select * from titles order by case when title like 'The %' then substring (title, 5, 255) when title like 'A %' then substring (title, 3, 255) when title like 'An %' then substring (title, 4, 255) else Title end That worked like a charm, yet MySQL doesn't seem to accept that. Any solutions or advice? -- Ian Evans Digital Hit Entertainment http://www.digitalhit.com - 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 - 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: innodb inserts/select crash
Hi! You are getting a lock wait timeout error, not a crash. In the newest version 3.23.44 code 101 has been replaced by a native MySQL error number 1205 and a descriptive message. Hi there. Can anyone offer a solution to this problem. CREATE TABLE `raw` ( `cid` int(11) default NULL, `agent` char(255) default NULL, `referer` char(255) default NULL, `addr` char(15) default NULL, `via` char(255) default NULL, `forward` char(15) default NULL, `ctime` datetime default NULL, `uniq` int(1) default NULL, KEY `age` (`ctime`)) TYPE=InnoDB I have a process inserting 150 rows a second into this database. select cid,referer,count(*) as hits, ctime from raw WHERE uniq=1 group by cid,referer; takes 1.39 seconds to do. however if i add a simple insert statement to put those selects into a table i get this error mysql create table pagehits_tmp select cid,referer,count(*) as hits, ctime from raw WHERE uniq=1 group by cid,referer; ERROR 1030: Got error 101 from table handler I don't understand why it cannot do this when the select statement takes no time at all. perror(101) just returns unknown error. Any suggestions appreciated Ric The reason why CREATE TABLE ... SELECT ... sets shared locks on the rows in the SELECT table is that the MySQL binlog logs complete SQL strings as they are. If we do not lock the rows we read, then in recovery we do not know what rows actually were inserted. An ordinary SELECT does not set any locks: it is a consistent read. Hmm... what to do? You could do SELECT INTO OUTFILE + LOAD DATA INFILE to your table pagehits_tmp. Then MySQL no locks need to be set. Or you could try to figure out why the inserts may keep locks for a long time, and commit more often. The default for a lock wait timeout is 50 seconds. Regards, Heikki http://www.innodb.com/ibman.html - 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
update from table x to table y
Hi does anyone know of a way of taking some data from one table and updating another table with it? If MySQL doesn't support it i'm gonna have to go back to Access or something! thanks Rich _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp - 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: order by, group by
Looks like no one knows the answer to this question... - Original Message - From: Christian Andersson [EMAIL PROTECTED] To: MySQL Mailing list [EMAIL PROTECTED] Sent: Monday, November 12, 2001 10:30 PM Subject: order by, group by I asked before, but got no answer, so I ask again, but refrase myself a little Is it possible in myslq to do an order by before an group by? as far as I know, the order by will happen after the group by, and sort all the grouped rows(I assume that when grouping it just takes the first found unique row), but what I'd really would like to do is sort the results before grouping so that I can controle which rows are to be presented back to me... the only way right now for me is to skip the group by in mysql, and do an own group by in the program, but I guess that if it is possible to do in mysql it would be faster since there would atleast be less to transfer from mysql to my application (java/jdbc based) or are there other tricks I can use to get what I want? /Christian Andersson - 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 - 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: myisamchk: error: Checksum for key....
On Mon, Nov 12, 2001 at 07:21:07PM +0200, Sinisa Milivojevic wrote: Grzegorz Paszka writes: Yes, I say more, that I created new database and filled it by perl script from data source and I have the same situation. I think that is the best way of rebuild index file :) See in our manual what to do when index file is not there, so try to follow those instructions. Backup your table first !! Before I wrote to this list I read manual. Especially chapter 4.4 . I've done what You suggested but it didn't help. (Stage 3: Difficult repair). That's one problem. Another is as I wrote above. New database with content from data source is also broken. I know that is sounds strange but it's true. I'll try to set up database on another computer and I'll see if it's data related problem or hardware (I don't believe) or configuration. If I will have more information I'll write on list and to You. If You have another suggestions don't hesitate to write it to me. Best regards. -- Grzegorz - 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: myisamchk: error: Checksum for key....
Grzegorz Paszka writes: On Mon, Nov 12, 2001 at 07:21:07PM +0200, Sinisa Milivojevic wrote: Before I wrote to this list I read manual. Especially chapter 4.4 . I've done what You suggested but it didn't help. (Stage 3: Difficult repair). That's one problem. Another is as I wrote above. New database with content from data source is also broken. I know that is sounds strange but it's true. I'll try to set up database on another computer and I'll see if it's data related problem or hardware (I don't believe) or configuration. If I will have more information I'll write on list and to You. If You have another suggestions don't hesitate to write it to me. Best regards. -- Grzegorz If restoring tables from mysqldumps also leads to corruption, then you should definitely check your hardware, OS and filesystem. -- Regards, __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Sinisa Milivojevic [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer /_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus ___/ www.mysql.com - 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: Fulltext search variable
Hi!
On Nov 13, Christopher Thorpe wrote:
Hi Sergei
Sorry to post off list to you, not sure what else to do... I've posted to
the list twice but received no reply at all to my query. I'm doing a whole
load of MATCH AGAINST queries, but have no luck at all in using the
truncation operator. I can use the +word just fine to make sure that words
are present, so I know that 4.0 is working fine for me (I'm on Win2K), but
when I do the following query I get 239 records
SELECT arx_title FROM arx WHERE MATCH (arx_authors) AGAINST ('Jonathan')
when I do this I get none
SELECT arx_title FROM arx WHERE MATCH (arx_authors) AGAINST ('Jon*')
and when I do this (just to check!!) I get an error
SELECT arx_title FROM arx WHERE MATCH (arx_authors) AGAINST ('Jon'*)
Please can you tell me how to use the truncation operator, because it's
really pretty vital for what we're trying to do with the Fulltext search
(which we all think performs way better than Oracle's by the way)
best wishes and sorry again
Chris
Try ...AGAINST ('Jonat*'), for example.
I think 'Jon' is discarded by the engine because it's less then
ft_min_len (not very smart for truncation operator, indeed).
I cannot verify it, though, as in the development source tree fulltext
search is completely rewritten now, so to test that for you I have to
download and build 4.0.0 sources.
Regards,
Sergei
--
MySQL Development Team
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED]
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, http://www.mysql.com/
/_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany
___/
-
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: update from table x to table y
Rich, Hi does anyone know of a way of taking some data from one table and updating another table with it? If MySQL doesn't support it i'm gonna have to go back to Access or something! thanks Rich Sinisa is right now writing a multi-table update to 4.0. Some updates can be handled already in 3.23 with REPLACE INTO ... SELECT ... Regards, Heikki http://www.innodb.com - 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
Solution for testing PHP/MS Access on Linux
Hello, some days ago I asked help on this list because I have to develop and test on Linux/Apache some PHP pages that will have to run on on an IIS/NT box. On the real server the pages must manage a MS Access database via ODBC (this mixed setup cannot be changed: explanations in the original message). Now, first of all I would like to thank everybody for their help, and ask about the following solutions. I know they're awkward, but I must make it work ASAP (and performance is not a problem, even the real server is a relatively low traffic site) 1) I could convert the Access DB to Mysql and then write PHP pages which use a wrapper layer for all standard db management calls (as explained in the PHP Developer's Cookbook, chapter 6). Then the only change needed before uploading on the server would be the include of the file with the wrapper functions. In this way I could test all the DB management part of PHP on Linux/MySQL and be sure that it will work on NT/Access also. 2) I could recreate a Mysql database with the same tables on Linux, and access it indirectly from PHP using the ODBC/MySQL interface. In this way the I would be using ODBC in both cases. I guess this would give bet ter performance, as the wrapper layer is not needed anymore, right? What do you think about: which solution is easier to set up? which gives more guarantees that the PHP code will work transparently? did anybody do it before? any other issue (performance, stability, whatever) TIA, mweb -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] - 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: order by, group by
Looks like no one knows the answer to this question... =or we don't want to state the obvious/seem rude when saying RTFM 7.19 SELECT Syntax All keywords used must be given in exactly the order shown above. For example, a HAVING clause must come after any GROUP BY clause and before any ORDER BY clause. =dn - Original Message - From: Christian Andersson [EMAIL PROTECTED] To: MySQL Mailing list [EMAIL PROTECTED] Sent: Monday, November 12, 2001 10:30 PM Subject: order by, group by I asked before, but got no answer, so I ask again, but refrase myself a little Is it possible in myslq to do an order by before an group by? as far as I know, the order by will happen after the group by, and sort all the grouped rows(I assume that when grouping it just takes the first found unique row), but what I'd really would like to do is sort the results before grouping so that I can controle which rows are to be presented back to me... the only way right now for me is to skip the group by in mysql, and do an own group by in the program, but I guess that if it is possible to do in mysql it would be faster since there would atleast be less to transfer from mysql to my application (java/jdbc based) or are there other tricks I can use to get what I want? /Christian Andersson - 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 - 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 - 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: update from table x to table y
Hi does anyone know of a way of taking some data from one table and updating another table with it? If MySQL doesn't support it i'm gonna have to go back to Access or something! Rich =only go back to Access if you're a glutton for punishment! =try MySQL/HTMLmanual/manual_Reference.html#INSERT_SELECT =dn - 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: order by, group by
mysql On Mon, 12 Nov 2001, Christian Andersson wrote: Is it possible in myslq to do an order by before an group by? For me it is not clear why would you want to do that. Can you give an example? -- , M A R I O data miner, LIACC, room 221 tel 351+226078830, ext 121 A M A D O Rua Campo Alegre, 823 fax 351+226003654 A L V E S P-4150 PORTO, Portugalmob 351+939354002 - 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
mysql 4 alpha
hi! is there anybody, who knows of a site or documentation about the new features of mysql? maybe also articles or whatever? or is there even someone who already made some testing with the new alpha version? greetings christian _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com - 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: order by, group by
DL Neil writes: All keywords used must be given in exactly the order shown above. For example, a HAVING clause must come after any GROUP BY clause and before any ORDER BY clause. As far as I understood the original post, the question was if there's a way to get MySQL to perform an ORDER BY prior to doing the GROUP BY. In ANSI SQL this would be pointless, since you have to group by all returned fields that are not 'functions for use with group by'. In MySQL you can do something like SELECT a,b FROM table1 GROUP BY a to get the value of b for an arbitrary row for each a. This is mostly useful when you want a large number of columns returned and you know that whatever you're grouping by is unique, i.e. COUNT(*) would be 1 for every returned row. However, it would sometimes be useful to be able to extract the top value of something for each value of something else, _along with additional information about that top value_. For instance, you might want to know the top score for every week's quiz, and along with that the name of the best student. This would typically be done with a subselect, along the lines of SELECT date,score,name FROM quiz WHERE (date,score) IN (SELECT date,MAX(score) FROM quiz GROUP BY date) but this will give you more than one row per date if several people have the same score that day. I think what the poster was looking for was a way to do this with something like (and this is very broken): SELECT date,MAX(score),(name with score=MAX(score)) FROM quiz GROUP BY date which could be written as something like SELECT date,MAX(score),name FROM quiz ORDER BY score GROUP BY date ORDER BY date since you want to pick the name that matches the highest score. Unfortunately there is no way to do this in MySQL right now. You will have to first SELECT date,MAX(score) FROM quiz GROUP BY date and then loop over the result and SELECT name FROM quiz WHERE date=... ORDER BY SCORE DESC LIMIT 1 Once MySQL gets subselect you'll probably be able to do it like SELECT date,score,name FROM quiz WHERE (date,score) IN (SELECT date,MAX(score) FROM quiz GROUP BY date) GROUP BY date,score which would return an arbitrary person for each date's top score. I hope this makes sense and clears things up. //C -- Carl Troein - Círdan / Istari-PixelMagic - UIN 16353280 [EMAIL PROTECTED] | http://pixelmagic.dyndns.org/~cirdan/ Amiga user since '89, and damned proud of it too. - 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: Fulltext search variable
Hi Sergei
Thanks for the prompt reply... I've just tried AGAINST ('Jonat*') but get
the same results... we've actually got the ft_min_length set to 2 as we have
a few authors whose surnames are Li (and we've found no major performance
issues).
I'd tried a few longer words before like 'genom*' for genome/genomics etc
but still get no results in our full length article fulltext index... we
just can't seem to get the truncation operator to work at all... glad I'm
trying to use it correctly!! It would be really cool if we could fix this
soon... it's pretty critical to what we want to do with our search engine
thanks
Chris
-Original Message-
From: 'Sergei Golubchik' [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, November 13, 2001 11:37 AM
To: Christopher Thorpe
Cc: [EMAIL PROTECTED]
Subject: Re: Fulltext search variable
Hi!
On Nov 13, Christopher Thorpe wrote:
Hi Sergei
Sorry to post off list to you, not sure what else to do... I've posted to
the list twice but received no reply at all to my query. I'm doing a whole
load of MATCH AGAINST queries, but have no luck at all in using the
truncation operator. I can use the +word just fine to make sure that words
are present, so I know that 4.0 is working fine for me (I'm on Win2K), but
when I do the following query I get 239 records
SELECT arx_title FROM arx WHERE MATCH (arx_authors) AGAINST ('Jonathan')
when I do this I get none
SELECT arx_title FROM arx WHERE MATCH (arx_authors) AGAINST ('Jon*')
and when I do this (just to check!!) I get an error
SELECT arx_title FROM arx WHERE MATCH (arx_authors) AGAINST ('Jon'*)
Please can you tell me how to use the truncation operator, because it's
really pretty vital for what we're trying to do with the Fulltext search
(which we all think performs way better than Oracle's by the way)
best wishes and sorry again
Chris
Try ...AGAINST ('Jonat*'), for example.
I think 'Jon' is discarded by the engine because it's less then
ft_min_len (not very smart for truncation operator, indeed).
I cannot verify it, though, as in the development source tree fulltext
search is completely rewritten now, so to test that for you I have to
download and build 4.0.0 sources.
Regards,
Sergei
--
MySQL Development Team
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED]
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, http://www.mysql.com/
/_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany
___/
-
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: order by, group by
On Tue, 13 Nov 2001, Carl Troein wrote: As far as I understood the original post, the question was if there's a way to get MySQL to perform an ORDER BY prior to doing the GROUP BY. Yes it was but prior to does not make much sense and that is why I asked the original poster for an example. But anyway I think Carl has guessed correctly what was on the poster's mind and yes Carl the rest of your message makes it quite clear. -- , M A R I O data miner, LIACC, room 221 tel 351+226078830, ext 121 A M A D O Rua Campo Alegre, 823 fax 351+226003654 A L V E S P-4150 PORTO, Portugalmob 351+939354002 - 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
how do you find out which table fields are foreign keys?
I created the persons and shirts tables as described in the MySQL
tutorial:
http://www.mysql.com/doc/e/x/example-Foreign_keys.html
such that the owner field in shirts is a foreign key referencing the
persons table. However, describe shirts does not indicate that the
field is a foreign key: (may have to widen mail viewer window to view the
following table)
mysql describe shirts;
+---+-+--+-+-++
| Field | Type| Null | Key |
Default | Extra |
+---+-+--+-+-++
| id| smallint(5) unsigned| | PRI |
NULL| auto_increment |
| style | enum('t-shirt','polo','dress') | | |
t-shirt ||
| color | enum('red','blue','orange','white','black') | | |
red ||
| owner | smallint(5) unsigned| | |
0 ||
+---+-+--+-+-++
4 rows in set (0.00 sec)
How do I find out which fields are foreign keys?
For that matter, in a MyISAM table, what difference does it make whether
you specify that a given field is a foreign key referencing another table
-- as opposed to just an integer field with the same data type as the key
field of another table? Since referential integrity is not enforced with
MyISAM tables, does it not make any difference whether I tell it that a
field is a foreign key? Is that information discarded entirely? (Which
would explain why describe doesn't show it.)
The only reason I wanted to know whether a given field was a foreign key,
was because I'm hammering together my own Web-based interface to MySQL
tables, and I was hoping that when the table contents are rendered in an
HTML table and a given field is a foreign key, I can have its value
hyperlinked to the appropriate row in the table that it references.
-Bennett
[EMAIL PROTECTED] http://www.peacefire.org
(425) 649 9024
-
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
why ever use TINYBLOB/TEXT -- isn't VARCHAR same size?
http://www.mysql.com/doc/n/o/node_357.html explains the different string data types and the storage requirements: Column Type Storage required [...] VARCHAR(M) L+1 bytes, where L = M and 1 = M = 255 [...] TINYBLOB, TINYTEXT L+1 bytes, where L 2^8 [...] These two storage requirements are exactly the same, so isn't a TINYBLOB almost exactly like a VARCHAR(255) BINARY, and a TINYTEXT almost exactly like a VARCHAR(255)? There are some differences listed at: http://www.mysql.com/doc/B/L/BLOB.html Apparently, in MySQL 3.23.2, the only difference between a TINYTEXT and a VARCHAR(255) is that TEXT/BLOB fields can't have default values and trailing spaces are not removed as they are in VARCHAR values. But are those really the only differences? -Bennett [EMAIL PROTECTED] http://www.peacefire.org (425) 649 9024 - 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: Fulltext search variable
Hi!
On Nov 13, Christopher Thorpe wrote:
Hi Sergei
Thanks for the prompt reply... I've just tried AGAINST ('Jonat*') but get
the same results... we've actually got the ft_min_length set to 2 as we have
a few authors whose surnames are Li (and we've found no major performance
issues).
I'd tried a few longer words before like 'genom*' for genome/genomics etc
but still get no results in our full length article fulltext index... we
just can't seem to get the truncation operator to work at all... glad I'm
trying to use it correctly!! It would be really cool if we could fix this
soon... it's pretty critical to what we want to do with our search engine
thanks
Chris
Then, I cannot help.
The code you're tryung to use was removed from MySQL source tree several
months ago. Wait for MySQL 4.0.1.
Regards,
Sergei
--
MySQL Development Team
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED]
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, http://www.mysql.com/
/_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany
___/
-
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: update from table x to table y
thanks for that, but that will add a row as opposed to updating a column won't it? I don't want to insert a new row. All I want to do is update a value in table1 if it is present in table2 cheers Rich From: DL Neil [EMAIL PROTECTED] Reply-To: DL Neil [EMAIL PROTECTED] To: Richard Dobson [EMAIL PROTECTED], [EMAIL PROTECTED] Subject: Re: update from table x to table y Date: Tue, 13 Nov 2001 11:45:45 - Hi does anyone know of a way of taking some data from one table and updating another table with it? If MySQL doesn't support it i'm gonna have to go back to Access or something! Rich =only go back to Access if you're a glutton for punishment! =try MySQL/HTMLmanual/manual_Reference.html#INSERT_SELECT =dn - 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 _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp - 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: Hash Tables / Indexes
From: Karl J. Stubsjoen Hello, Can someone explain hash tables or hash indexes and if we can take advantage of them in MySQL? Thanks! Karl A few words on a meaty subject... Hashing is a search method based on an arithmetic or algorithmic transformation of the key. Hashing is a means to speed up table searching by calculating the position for a record in the actual table (hash table) or in the index (hash index) for a table based on the value of the key. Hash tables or hash indexes are best suited for applications where the number of records that will populate a table is relatively small. This technique is often used in compilers and assemblers or in applications where the tables will completely fit into main memory. Most in-memory database applications (such as real-time data collection) use hashing. To understand hashing, assume f(key) = table address. Some function or algorithmic procedure can be applied to the key that will yield the address of the record in the table. You directly calculate the address in the table instead of searching by comparing the key. A very simple example is to assume that the key is a unique number in the range of 1 - N. The size of the table is (N x record size). The records are stored in the hash table at ((key * record size) - record size). Given a key the record's location in the table can be easily calculated. Real world applications are a bit more complicated. The simple example only works as an example. Real world keys are not always a simple number and if the key is numeric its range most often exceeds the available storage. The solution is to use a hashing function that will create an address in the range of the available addresses in the table and also provides a means to handle collisions on insertions. Collisions are cases where an existing record already occupies the calculated address. In practice the hashing function calculates the target address in the table. A re-hash function if needed recalculates addresses to handle collisions and for searching beyond the first hashed address if that address does not hold the target of the search. If you are interested in the messy details I suggest you refer to one or all of the following: The Art of Computer Progamming: Volume 3 Sorting and Searching, by Donald E. Knuth Making Hash With Tables by Terry Dollhoff, Programming Techniques: Program Design Full Table Quadratic Searching for Scatter Storage by Colin A. Day Weighted Increment Linear Search for Scatter Tables by F. Luccio Scatter Storage Techniques by Robert Morris, Communication of the ACM January 1968 Hashing Methods for Direct Access Files by Terry Dollhoff, Auerbach Computer Programming Management, Folio 15-02-02 Hashing is an old technique, thus most current database management systems use more modern methods. Before relational database theory was adapted, network model databases often used hashing for indexing. Hashing is still used in those special cases where it is the best solution. See 7.6.11.3 Adaptive hash indexes in the MySQL Manual. I don't think that you can directly take advantage of hashing in MySQL. With Innodb tables MySQL uses hashing where it is useful. I hope this is helpful to you. Regards, Norman L. Smith - 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: Fulltext search variable
Hi Sergei and everyone...
Thanks for the help... looking forward to 4.0.1!!
best wishes
Chris
-Original Message-
From: Sergei Golubchik [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, November 13, 2001 1:30 PM
To: Christopher Thorpe
Cc: [EMAIL PROTECTED]
Subject: Re: Fulltext search variable
Hi!
On Nov 13, Christopher Thorpe wrote:
Hi Sergei
Thanks for the prompt reply... I've just tried AGAINST ('Jonat*') but get
the same results... we've actually got the ft_min_length set to 2 as we
have
a few authors whose surnames are Li (and we've found no major performance
issues).
I'd tried a few longer words before like 'genom*' for genome/genomics etc
but still get no results in our full length article fulltext index... we
just can't seem to get the truncation operator to work at all... glad I'm
trying to use it correctly!! It would be really cool if we could fix this
soon... it's pretty critical to what we want to do with our search engine
thanks
Chris
Then, I cannot help.
The code you're tryung to use was removed from MySQL source tree several
months ago. Wait for MySQL 4.0.1.
Regards,
Sergei
--
MySQL Development Team
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED]
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, http://www.mysql.com/
/_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany
___/
-
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
-
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: update from table x to table y
Duplicating values (cf keys) in tables involves de-normalisation and is therefore not recommended. Your observation row cf column is correct. I felt the question was sufficiently broad to risk interpreting update as wider than UPDATE, sorry - the other response seemed to hit that nail on the head (have deleted it). If it doesn't suit, then another solution might be to use PHP to SELECT the data from table1 and then use a second query to UPDATE table-2 SET table2-col = table1-col WHERE table1-val = table2-val (which, in its present form, has the potential to modify numerous rows in one hit). =Regards, =dn thanks for that, but that will add a row as opposed to updating a column won't it? I don't want to insert a new row. All I want to do is update a value in table1 if it is present in table2 cheers Rich From: DL Neil [EMAIL PROTECTED] Reply-To: DL Neil [EMAIL PROTECTED] To: Richard Dobson [EMAIL PROTECTED], [EMAIL PROTECTED] Subject: Re: update from table x to table y Date: Tue, 13 Nov 2001 11:45:45 - Hi does anyone know of a way of taking some data from one table and updating another table with it? If MySQL doesn't support it i'm gonna have to go back to Access or something! Rich =only go back to Access if you're a glutton for punishment! =try MySQL/HTMLmanual/manual_Reference.html#INSERT_SELECT =dn - 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 _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp - 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: order by, group by
Im trying to make a data system that is language-independent, ie the data stored can have any language therefore the same information is stored several times but with different values (depending on language) lets say that I have this table.. articles(id,language,name,description) what I would like to do is retrieve 1 row per unique id in the chosen language select * from articles where language='en'; that is quite simple, unfourtunally, not all articles have the description or name written in english and the above query would not return these articles... PROBLEM is what I get. however doing a select * from articles group by (id); will return all articles even if they do not have an english translation, BUT here is the problem.. which language will be the one I recieve? Svedish? english? French? well I guess that the first one that is found inthe database will be the one retireved, so if I wrote the French translation before the english translation it would give me the french translation and not the english translation.. IF I however could somehow order the results before the group by was done, I could sort it so that first there would be english, then swedish, norwegian, etc... and at the bottom French ( :-) ) and then do the group by and the first language in this list would be the one I would get, English, and if there is no English I would get Swedish, and if there is no swedish, I would getnorwegian, and so on.. But since I cannot in mysql do an order by before an group by (will this be possible in a distant future?) the above sollution is not possible.. so what I do is the following select *,FIND_IN_SET(language,'no,dv,en') language2 from articlesorder by language2 desc; then ALL of these results are read into the application, and for each unique id I pulls out the first row, and forgets all other rows for that id. If the database could do that for me, I think it would be both faster and more efficient then what I'm doing now.. If I could do an group by on this one (I Know I cannot, but IF) select *,FIND_IN_SET(language,'no,dv,en') language2 from articlesorder by language2 desc group by id; I hope this explains better what I would like to do.. I just thought of something IF (and this I know nothing about) group by takes the first found row and skipps #2, #3, etc... (based an what to group by) is it then possible to take my query above (with the find in set functionallity) and insert the result in a temporary table so that the temporary table holds the ordered results and then do a select from that temporary table with an group by (and since the temporary table holds the ordered list and the group by returns the first row of each group I would get desired effect) hmmm,just tried it and I cant get it to work the insert into query complains about columns count (yes I have created 1 extr column for the find_in_set result and tried up to 4 extra columns but it does not work.. well my application works, I just want it faster and less to write (I have to make this loop for every table that is language dependent) and I'm not to stupid... the language-specific tables holds just that language specific data, (and the keys) the ordinary data that is not language specific, I have in a seperate table.. I hope this explains better what I want to do, and I would appreciate ANY ideas on how to do this better.. /Christian Andersson - Original Message - From: M. A. Alves [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, November 13, 2001 12:58 PM Subject: Re: order by, group by mysql On Mon, 12 Nov 2001, Christian Andersson wrote: Is it possible in myslq to do an order by before an group by? For me it is not clear why would you want to do that. Can you give an example? -- , M A R I O data miner, LIACC, room 221 tel 351+226078830, ext 121 A M A D O Rua Campo Alegre, 823 fax 351+226003654 A L V E S P-4150 PORTO, Portugalmob 351+939354002 - 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 - 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: Can't connect via IP, but can via hostname
I notice you didn't have the time to look it up either. Rick Emery wrote: Help the guy out by telling him WHERE in the manual to look. Answers, such as Please read mysql manual, your question covered by manual. are of no help. -Original Message- From: Abu @ Trabas Dot Com [mailto:[EMAIL PROTECTED]] Sent: Monday, November 12, 2001 11:33 PM To: [EMAIL PROTECTED] Subject: Re: Can't connect via IP, but can via hostname Please read mysql manual, your question covered by manual. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) Ady Wicaksono([EMAIL PROTECTED])@Tue, Nov 13, 2001 at 07:14:58AM +0700: Check your users and db table at mysql database probably the problem comes from there On Tuesday 13 November 2001 02:47 pm, Alex wrote: Dear Sirs, My MySQL is not configured properly... I can connect using localhost with -h flag , but when I'm using IP instead of hostname it doesn't connects at all... What'd I do? Thank you. -- ady -- email: ady at ebdesk.com adiwicaksono at yahoo.com ady at students.if.itb.ac.id homepage: http://ady97.hypermart.net/ To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - 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: order by, group by
Thank you for your responce, you have guessed it correctly the only difference is the usage of MAX which I cannot use (see my explenation on what I want to do in a seperate message) I'm not sure IF I can do it with sub-selects either, but when subselects is present in mysl, I could try that one.. ( do not have any other database to test with, so I cannot try it out) but thanks anyway.. - Original Message - From: Carl Troein [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, November 13, 2001 1:23 PM Subject: Re: order by, group by DL Neil writes: All keywords used must be given in exactly the order shown above. For example, a HAVING clause must come after any GROUP BY clause and before any ORDER BY clause. As far as I understood the original post, the question was if there's a way to get MySQL to perform an ORDER BY prior to doing the GROUP BY. In ANSI SQL this would be pointless, since you have to group by all returned fields that are not 'functions for use with group by'. In MySQL you can do something like SELECT a,b FROM table1 GROUP BY a to get the value of b for an arbitrary row for each a. This is mostly useful when you want a large number of columns returned and you know that whatever you're grouping by is unique, i.e. COUNT(*) would be 1 for every returned row. However, it would sometimes be useful to be able to extract the top value of something for each value of something else, _along with additional information about that top value_. For instance, you might want to know the top score for every week's quiz, and along with that the name of the best student. This would typically be done with a subselect, along the lines of SELECT date,score,name FROM quiz WHERE (date,score) IN (SELECT date,MAX(score) FROM quiz GROUP BY date) but this will give you more than one row per date if several people have the same score that day. I think what the poster was looking for was a way to do this with something like (and this is very broken): SELECT date,MAX(score),(name with score=MAX(score)) FROM quiz GROUP BY date which could be written as something like SELECT date,MAX(score),name FROM quiz ORDER BY score GROUP BY date ORDER BY date since you want to pick the name that matches the highest score. Unfortunately there is no way to do this in MySQL right now. You will have to first SELECT date,MAX(score) FROM quiz GROUP BY date and then loop over the result and SELECT name FROM quiz WHERE date=... ORDER BY SCORE DESC LIMIT 1 Once MySQL gets subselect you'll probably be able to do it like SELECT date,score,name FROM quiz WHERE (date,score) IN (SELECT date,MAX(score) FROM quiz GROUP BY date) GROUP BY date,score which would return an arbitrary person for each date's top score. I hope this makes sense and clears things up. //C -- Carl Troein - Círdan / Istari-PixelMagic - UIN 16353280 [EMAIL PROTECTED] | http://pixelmagic.dyndns.org/~cirdan/ Amiga user since '89, and damned proud of it too. - 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: Can't connect via IP, but can via hostname
Help the guy out by telling him WHERE in the manual to look. Answers, such as Please read mysql manual, your question covered by manual. are of no help. -Original Message- From: Abu @ Trabas Dot Com [mailto:[EMAIL PROTECTED]] Sent: Monday, November 12, 2001 11:33 PM To: [EMAIL PROTECTED] Subject: Re: Can't connect via IP, but can via hostname Please read mysql manual, your question covered by manual. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) Ady Wicaksono([EMAIL PROTECTED])@Tue, Nov 13, 2001 at 07:14:58AM +0700: Check your users and db table at mysql database probably the problem comes from there On Tuesday 13 November 2001 02:47 pm, Alex wrote: Dear Sirs, My MySQL is not configured properly... I can connect using localhost with -h flag , but when I'm using IP instead of hostname it doesn't connects at all... What'd I do? Thank you. -- ady -- email: ady at ebdesk.com adiwicaksono at yahoo.com ady at students.if.itb.ac.id homepage: http://ady97.hypermart.net/ To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- __ (oo) Open Solution Provider visit http://www.trabas.com / \/ \ GnuPg public information pub 1024/EBD26280 `V__V' A9A9 8F57 9E9D 14E3 05B4 3EDB C241 A313 EBD2 6280 You have a deep appreciation of the arts and music. - 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 - 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: Need to understand mysql mechanisms
Hello S! I am sending this mail in order to get things straight about table corruption which I am experiencing with 4.0(as well as previous versions). 1)System specs: PIII x1000MHz, 1GB RAM, HD 37GB SCSI, AHA29160N SCSI controller OK, so this is a possibly a 'fairly' new box, meaning it hasn't been in service for years with no other problems? 4) Corruption is a tossup. The import always works. However, when I run the queries sometimes they all run fine, while other times I get an error 127 on the table it is working on. myisamchk NEVER manages a complete recovery of all records(typically 25% of the records). This makes it 5)Before writing a bug report(the problem may be that this is erratic and even on my PC corruption is not consistent, e.g. I may import a file and get corruption, or I may import another file without corruption) and hence reproducibility may be problematic., I would appreciiate someone setting me then ulimit -a limit says 8192 but when I open a window as a regular user, and do ulimit -a limit it is still 1024. Rebooting (By 'window' do you mean an X-window? If yes, try shutting off xfs and doing things in a plain text terminal. Not a fix, just a bandaid, but it lightens the system load.) In any case my real suggestion is that I was having wacky unrepeatable system errors that that your descriptions remind me of, and memtest86 http://www.teresaudio.com/memtest86/ found bad RAM that -*NO*- other memory tester could confirm. Changed one DIMM and it purrs like a kitten. Have a :) day! jb -- jim barchuk [EMAIL PROTECTED] - 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: Can't connect via IP, but can via hostname
Gerald Clark writes: I notice you didn't have the time to look it up either. And neither did you. This is getting seriously silly. :-) To see the original question, look at the bottom (weird, I know) of this message. To see an answer, look here: http://www.mysql.com/doc/C/a/Can_not_connect_to_server.html //C Rick Emery wrote: Help the guy out by telling him WHERE in the manual to look. Answers, such as Please read mysql manual, your question covered by manual. are of no help. Please read mysql manual, your question covered by manual. Check your users and db table at mysql database probably the problem comes from there My MySQL is not configured properly... I can connect using localhost with -h flag , but when I'm using IP instead of hostname it doesn't connects at all... -- Carl Troein - Círdan / Istari-PixelMagic - UIN 16353280 [EMAIL PROTECTED] | http://pixelmagic.dyndns.org/~cirdan/ Amiga user since '89, and damned proud of it too. - 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: update from table x to table y
Hi, thanks dn, so, to confirm, there is no MySQL to represent the following?: update Raw,Unresolved_Duplicates_perm set Raw.Inactivate = 1 where Raw.Key_num = Unresolved_Duplicates_perm.MinOfKey and Raw.Peak1=0 Thanks Rich From: DL Neil [EMAIL PROTECTED] Reply-To: DL Neil [EMAIL PROTECTED] To: Richard Dobson [EMAIL PROTECTED], [EMAIL PROTECTED] Subject: Re: update from table x to table y Date: Tue, 13 Nov 2001 13:49:23 - Duplicating values (cf keys) in tables involves de-normalisation and is therefore not recommended. Your observation row cf column is correct. I felt the question was sufficiently broad to risk interpreting update as wider than UPDATE, sorry - the other response seemed to hit that nail on the head (have deleted it). If it doesn't suit, then another solution might be to use PHP to SELECT the data from table1 and then use a second query to UPDATE table-2 SET table2-col = table1-col WHERE table1-val = table2-val (which, in its present form, has the potential to modify numerous rows in one hit). =Regards, =dn thanks for that, but that will add a row as opposed to updating a column won't it? I don't want to insert a new row. All I want to do is update a value in table1 if it is present in table2 cheers Rich From: DL Neil [EMAIL PROTECTED] Reply-To: DL Neil [EMAIL PROTECTED] To: Richard Dobson [EMAIL PROTECTED], [EMAIL PROTECTED] Subject: Re: update from table x to table y Date: Tue, 13 Nov 2001 11:45:45 - Hi does anyone know of a way of taking some data from one table and updating another table with it? If MySQL doesn't support it i'm gonna have to go back to Access or something! Rich =only go back to Access if you're a glutton for punishment! =try MySQL/HTMLmanual/manual_Reference.html#INSERT_SELECT =dn - 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 _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp - 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 _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp - 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: why ever use TINYBLOB/TEXT -- isn't VARCHAR same size?
http://www.mysql.com/doc/n/o/node_357.html explains the different string data types and the storage requirements: Column Type Storage required [...] VARCHAR(M) L+1 bytes, where L = M and 1 = M = 255 [...] TINYBLOB, TINYTEXT L+1 bytes, where L 2^8 [...] These two storage requirements are exactly the same, so isn't a TINYBLOB almost exactly like a VARCHAR(255) BINARY, and a TINYTEXT almost exactly like a VARCHAR(255)? There are some differences listed at: http://www.mysql.com/doc/B/L/BLOB.html Apparently, in MySQL 3.23.2, the only difference between a TINYTEXT and a VARCHAR(255) is that TEXT/BLOB fields can't have default values and trailing spaces are not removed as they are in VARCHAR values. But are those really the only differences? No. There's another small, but important difference: TEXT/BLOB fields aren't stored directly in the row. Instead, a pointer is stored to the data, which is located somewhere else. This is important where you have a lot of data stored in each row, as each row is limited to about 64k. TEXT/BLOB fields only count towards this limit with 1-4 bytes; a VARCHAR uses the L+1 bytes as you write. I assume that there is a small speed penalty in using TEXT/BLOB fields, as compared to VARCHARs. / Carsten -- Carsten H. Pedersen keeper and maintainer of the bitbybit.dk MySQL FAQ http://www.bitbybit.dk/mysqlfaq - 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: Can't connect via IP, but can via hostname
Carl Troein writes: http://www.mysql.com/doc/C/a/Can_not_connect_to_server.html Oops. That only convers the difference between 'localhost' and anything else. If whoever posted the question is interested in actually getting things to work, read the manual chapter on how the privilege system works, followed by the one on GRANT. A lot to read, but it's stuff that you ought to know when setting up MySQL. //C - ought to work more and mail less. -- Carl Troein - Círdan / Istari-PixelMagic - UIN 16353280 [EMAIL PROTECTED] | http://pixelmagic.dyndns.org/~cirdan/ Amiga user since '89, and damned proud of it too. - 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: it shows columns when it should only show tables ( mysql client )
Hi. It's been a while, but here it goes... As far as I know, the mysql command line client does not know about commands, i.e. the name completion feature (via TAB) is _not_ context sensitive and will just present all completions which fit to the word fragment you typed. So, without knowledge what command that is at all, this is expected behaviour. Similarly you will notice, that it has no command completion. Bye, Benjamin. On Thu, Oct 25, 2001 at 05:36:49PM +0100, [EMAIL PROTECTED] wrote: Description: this is how i find the bug. under the mysql console, i tried to drop a table, and i know that the mysql console do the same thing like bash some of the command ... TABTAB and the command autocomplets. OK .. let me show mysql drop table categor; categories categories.checked_out categories.ordering categoryid categories.categoryidcategories.checked_out_time categories.published categoryimage categories.categoryimage categories.editorcategories.section categoryname categories.categoryname categories.image_positioncategory the strange thing is in the end [...] -- [EMAIL PROTECTED] - 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
QUERY HELP - IF THEN ELSE
Hi... I want to output results based on the query from 3 tables ( described here ) TABLE 1 SUBID (SAME AS IN TABLE 2) ID FILE DATE DISCIPLINE (SAME AS IN TABLE 3) TABLE 2 NAME SUBID (SAME AS IN TABLE 1) TABLE 3 NAME EMAIL DISCIPLINE (SAME AS IN TABLE 1) HERE IS MY QUERY, USED IF THEN ELSE to show what i am trying to do. What's the best/right way to accomplish the results. select t1.SUBID,t1.ID,t1.FILE,t1.DATE,t1.DISCIPLINE, t2.NAME,t2.SUBID from PROPOSALS AS t1, PROFILE AS t2 where t1.ID=0 and t1.SUBID=t2.SUBID IF(t1.DISCIPLINE='Other') THEN(select t3.DISCIPLINE,t3.NAME,t3.EMAIL from ASSOCIATES AS t3) ELSE(select t3.DISCIPLINE,t3.NAME,t3.EMAIL from ASSOCIATES AS t3 where t1.DISCIPLINE=t3.DISCIPLINE) order by t1.DATE - My output is going to show FILE FROM TABLE 1 NAME FROM TABLE 2 NAME FROM TABLE 3 EMAIL FROM TABLE 3 -tia - 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
why ever use TINYBLOB/TEXT -- isn't VARCHAR same size? (fwd)
Carsten H. Pedersen writes: I assume that there is a small speed penalty in using TEXT/BLOB fields, as compared to VARCHARs. I recall seeing some test where TEXT was actually faster, but it might have been in some special situation. On the whole, I think that being able to specify a length for CHAR is the most important difference and the reason I use them for names of things (I use TINYTEXT/TEXT for longer descriptions etc.) //C - with a runny nose, a cup of tea, and a glass of Laphroaig mysql, database, bloody, filter, die, die, die. :-P -- Carl Troein - Círdan / Istari-PixelMagic - UIN 16353280 [EMAIL PROTECTED] | http://pixelmagic.dyndns.org/~cirdan/ Amiga user since '89, and damned proud of it too. - 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: QUERY HELP - IF THEN ELSE
I'm trying to determine what you want displayed from your SELECT. I see 3 different displays that you want: a. SUBID,t1.ID,t1.FILE,t1.DATE,t1.DISCIPLINE,t2.NAME,t2.SUBID b. t3.DISCIPLINE,t3.NAME,t3.EMAIL c. table2.NAME,table3.NAME, table3.EMAIL Which do you want? -Original Message- From: Pawandeep Lamba [mailto:[EMAIL PROTECTED]] Sent: Tuesday, November 13, 2001 9:56 AM To: [EMAIL PROTECTED] Subject: QUERY HELP - IF THEN ELSE Hi... I want to output results based on the query from 3 tables ( described here ) TABLE 1 SUBID (SAME AS IN TABLE 2) ID FILE DATE DISCIPLINE (SAME AS IN TABLE 3) TABLE 2 NAME SUBID (SAME AS IN TABLE 1) TABLE 3 NAME EMAIL DISCIPLINE (SAME AS IN TABLE 1) HERE IS MY QUERY, USED IF THEN ELSE to show what i am trying to do. What's the best/right way to accomplish the results. select t1.SUBID,t1.ID,t1.FILE,t1.DATE,t1.DISCIPLINE, t2.NAME,t2.SUBID from PROPOSALS AS t1, PROFILE AS t2 where t1.ID=0 and t1.SUBID=t2.SUBID IF(t1.DISCIPLINE='Other') THEN(select t3.DISCIPLINE,t3.NAME,t3.EMAIL from ASSOCIATES AS t3) ELSE(select t3.DISCIPLINE,t3.NAME,t3.EMAIL from ASSOCIATES AS t3 where t1.DISCIPLINE=t3.DISCIPLINE) order by t1.DATE - My output is going to show FILE FROM TABLE 1 NAME FROM TABLE 2 NAME FROM TABLE 3 EMAIL FROM TABLE 3 -tia - 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 - 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
How to decrement int field?
Hello, I would really appreciate some help on this, thanks! I have a colomn with days_left, an integer. I have to write a Perl script to run on a cron job once a day and decrement all the fields in that column by 1. Is there a query to do this? Sincerely, Gil. -- http://planenews.com PGP public key at: keskydee.com/gil.asc ICQ: 3310801 - 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: order by, group by
Hi. If I understand correctly what you want to archieve, this is covered in the tutorial part of the manual: http://www.mysql.com/doc/e/x/example-Maximum-column-group-row.html and the short answer is no, only with temporary tables, until sub-queries are implemented. Bye, Benjamin. On Mon, Nov 12, 2001 at 10:30:24PM +0100, [EMAIL PROTECTED] wrote: I asked before, but got no answer, so I ask again, but refrase myself a little Is it possible in myslq to do an order by before an group by? as far as I know, the order by will happen after the group by, and sort all the grouped rows(I assume that when grouping it just takes the first found unique row), but what I'd really would like to do is sort the results before grouping so that I can controle which rows are to be presented back to me... the only way right now for me is to skip the group by in mysql, and do an own group by in the program, but I guess that if it is possible to do in mysql it would be faster since there would atleast be less to transfer from mysql to my application (java/jdbc based) or are there other tricks I can use to get what I want? /Christian Andersson [...] -- [EMAIL PROTECTED] - 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: order by, group by
Im trying to make a data system that is language-independent, ie the data stored can have any language therefore the same information is stored several times but with different values (depending on language) =Is this correct? data ~ news reports/magazine articles article ~ a news report (cf an item or product) language = spoken language (cf computer probgramming) different values (depending on language) = translations =Is name that of the author or the title of the article? =Is id a UNIQUE column, or if there are both English and French versions of the same article do they have the same id? Please describe id. lets say that I have this table.. articles(id,language,name,description) what I would like to do is retrieve 1 row per unique id in the chosen language select * from articles where language='en'; = this will select ALL of the articles written in English - if there were two such articles with the same 'id', and in English, then they would both appear - unless all articles (id-s) have an English language translation, then this will not produce a list of all unique ids amongst the 'hits' (I assume this is not possible - right?) =do you want ALL articles, or only the one with a matching id? that is quite simple, unfourtunally, not all articles have the description or name written in english and the above query would not return these articles... PROBLEM is what I get. however doing a select * from articles group by (id); will return all articles even if they do not have an english translation, =assuming that all the translations of the same article have the same id! The following will achieve a similar effect: SELECT * FROM articles ORDER BY id [ASC]; BUT here is the problem.. which language will be the one I recieve? Svedish? english? French? well I guess that the first one that is found inthe database will be the one retireved, so if I wrote the French translation before the english translation it would give me the french translation and not the english translation.. =you are correct here: the first one that is found, which has more to do with the way the rows are indexed/retrieved by MySQL, rather than the order in which they were INSERTed. IF I however could somehow order the results before the group by was done, I =or question the need to group the data - this clause is useful when you want to further analyse the data in those 'groups', but may not otherwise be useful. You do not appear to be using SQL to analyse the groups, you appear to only want the data collected/sequenced. could sort it so that first there would be english, then swedish, norwegian, etc... and at the bottom French ( :-) ) and then do the group by and the first language in this list would be the one I would get, English, and if there is no English I would get Swedish, and if there is no swedish, I would getnorwegian, and so on.. =so let's add language into the sequencing instruction: SELECT * FROM articles ORDER BY id, language; =now this does NOT answer your question because an article with a full set of translations will appear in the sequence dv, en, fr, no, se. =there are ways for you to dictate this sequence to be en, no, fr..., and if you're interested I'll try to recall the method... But since I cannot in mysql do an order by before an group by (will this be possible in a distant future?) the above sollution is not possible.. so what I do is the following select *,FIND_IN_SET(language,'no,dv,en') language2 from articlesorder by language2 desc; SELECT * FROM articles WHERE language = 'no' (etc or set construct) ORDER BY id; then ALL of these results are read into the application, and for each unique id I pulls out the first row, and forgets all other rows for that id. If the database could do that for me, I think it would be both faster and more efficient then what I'm doing now.. =indeed, the general rule is that doing calculations/organisations/presentations in SQL will be faster than post-processing the hits. If I could do an group by on this one (I Know I cannot, but IF) select *,FIND_IN_SET(language,'no,dv,en') language2 from articlesorder by language2 desc group by id; SELECT * FROM articles WHERE language = 'no' (etc or set construct) ORDER BY id ASC, language DESC; I hope this explains better what I would like to do.. =why ALL the articles and not just one? =what is your 'prime' language - or better, what is the order of language preference (from most prefered to least-used) I just thought of something... IF (and this I know nothing about) group by takes the first found row and skipps #2, #3, etc... (based an what to group by) is it then possible to take my query above (with the find in set functionallity) and insert the result in a temporary table so that the temporary table holds the ordered results and then do a select from that temporary table with an group by (and
Re: innodb inserts/select crash
What does this mean. How can I fix it. It seems silly that I have to read data out just to write it back in again instead of using the create...select command. Rich - Original Message - From: Heikki Tuuri [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, November 13, 2001 10:13 AM Subject: Re: innodb inserts/select crash Hi! You are getting a lock wait timeout error, not a crash. In the newest version 3.23.44 code 101 has been replaced by a native MySQL error number 1205 and a descriptive message. Hi there. Can anyone offer a solution to this problem. CREATE TABLE `raw` ( `cid` int(11) default NULL, `agent` char(255) default NULL, `referer` char(255) default NULL, `addr` char(15) default NULL, `via` char(255) default NULL, `forward` char(15) default NULL, `ctime` datetime default NULL, `uniq` int(1) default NULL, KEY `age` (`ctime`)) TYPE=InnoDB I have a process inserting 150 rows a second into this database. select cid,referer,count(*) as hits, ctime from raw WHERE uniq=1 group by cid,referer; takes 1.39 seconds to do. however if i add a simple insert statement to put those selects into a table i get this error mysql create table pagehits_tmp select cid,referer,count(*) as hits, ctime from raw WHERE uniq=1 group by cid,referer; ERROR 1030: Got error 101 from table handler I don't understand why it cannot do this when the select statement takes no time at all. perror(101) just returns unknown error. Any suggestions appreciated Ric The reason why CREATE TABLE ... SELECT ... sets shared locks on the rows in the SELECT table is that the MySQL binlog logs complete SQL strings as they are. If we do not lock the rows we read, then in recovery we do not know what rows actually were inserted. An ordinary SELECT does not set any locks: it is a consistent read. Hmm... what to do? You could do SELECT INTO OUTFILE + LOAD DATA INFILE to your table pagehits_tmp. Then MySQL no locks need to be set. Or you could try to figure out why the inserts may keep locks for a long time, and commit more often. The default for a lock wait timeout is 50 seconds. Regards, Heikki http://www.innodb.com/ibman.html - 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 - 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: How to decrement int field?
Gil G. writes: I have a colomn with days_left, an integer. I have to write a Perl script to run on a cron job once a day and decrement all the fields in that column by 1. Is there a query to do this? I'll leave the the cron job and perl script to you, but decrementing something is done the same way as incrementing something, except that you use '-' instead of '+' (I'm not implying that you didn't know this. I merely found it a good way to introduce the next sentence): UPDATE foo SET bar = bar-1 Forhead-slapping is voluntary. :-D //C - shame on me for not working. :-P Paranoid filter-bait: sql, database, nostril-hair -- Carl Troein - Círdan / Istari-PixelMagic - UIN 16353280 [EMAIL PROTECTED] | http://pixelmagic.dyndns.org/~cirdan/ Amiga user since '89, and damned proud of it too. - 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: How to decrement int field?
UPDATE mytable SET myvalue = myvalue-1 WHERE some_condition -Original Message- From: Gil G. [mailto:[EMAIL PROTECTED]] Sent: Tuesday, November 13, 2001 10:23 AM To: [EMAIL PROTECTED] Subject: How to decrement int field? Hello, I would really appreciate some help on this, thanks! I have a colomn with days_left, an integer. I have to write a Perl script to run on a cron job once a day and decrement all the fields in that column by 1. Is there a query to do this? Sincerely, Gil. -- http://planenews.com PGP public key at: keskydee.com/gil.asc ICQ: 3310801 - 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 - 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
Timezone offset question
I have a data collection script which rolls raw data into a formatted table, based on year/month/day. I've been requested to roll up this raw data based on a different timezone - 3 hours behind - to better coincide with reports from a company in that timezone. This is the rollup SQL query we currently run: SELECT DATE_FORMAT(date,'%Y%m%d'),sitecode,reseller,section,type,COUNT(DISTINCT ip), COUNT(ip) FROM rawdata WHERE YEAR(date) = YEAR(NOW()) AND MONTH(date) = MONTH(NOW()) AND (DAYOFMONTH(date) = DAYOFMONTH(NOW()) OR DAYOFMONTH(date) = DAYOFMONTH(DATE_SUB(NOW(),INTERVAL 1 DAY))) GROUP BY 1,2,3,4,5 ORDER BY 1,2,3,4,5 Basically it summerizes the totals for today and yesterday. My question is, given that query, what is the best method to rollup this offset data? use DATE_SUB on the SELECT, or perhaps on the DAYOFMONTH? Any suggestions would be appreciated. - 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: How to decrement int field?
Update table set days_left = days_left- 1 where days_left 0 [and other conditions]. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] m] On Behalf Of Gil G. Sent: Tuesday, November 13, 2001 11:23 AM To: [EMAIL PROTECTED] Subject: How to decrement int field? Hello, I would really appreciate some help on this, thanks! I have a colomn with days_left, an integer. I have to write a Perl script to run on a cron job once a day and decrement all the fields in that column by 1. Is there a query to do this? Sincerely, Gil. -- http://planenews.com PGP public key at: keskydee.com/gil.asc ICQ: 3310801 - 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 - 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: Hash Tables / Indexes
From: Karl J. Stubsjoen Imagine: a table with over 200,000 records in it and one of the fields in the table is a keywords field. Keywords seperated by commas. The keyword field is a collection of keywords that our clients use to identify the content of their web pages. What we need to do is search this field, however it is a field of somewhere between 5 and 50 comma seperated keywords. The question is: how to search within this field effectively. Doing a like %findthis%, is 100% NOT optmizable. Do you have any suggestions? Note: I'm looking into the FULLTEXT indexing features of MySQL. Thanks! Karl The new features (6.9.3 New Features of Full-text Search to Appear in MySQL 4.0) seemed to be geared to solve your problem. The advantage of using a feature which is part of the DBMS is that of a general solution. It may not be optimal but it is more likely to be robust and maintainable than a specialized solution. You may want to consider other options that are available such as those at http://www.google.com/services/index.html or from other commerical sources. I have not researched this subject recently but I am sure that there are many search engine solutions to be found. If you want to roll your own I suspect you won't go wrong using MySQL. Regards, Norman L. Smith - 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: why ever use TINYBLOB/TEXT -- isn't VARCHAR same size?
At 4:07 PM + 11/13/01, Carl Troein wrote:
Carsten H. Pedersen writes:
I assume that there is a small speed penalty in using TEXT/BLOB
fields, as compared to VARCHARs.
I recall seeing some test where TEXT was actually faster, but
it might have been in some special situation. On the whole,
I think that being able to specify a length for CHAR is the
most important difference and the reason I use them for names
of things (I use TINYTEXT/TEXT for longer descriptions etc.)
I have a little perl script that I use to benchmark my own system
when I am looking to find speed. The speed may be different on your
own system, but here are my results when testing TINYBLOB vs
VARCHARs. All other instances are the same, only the column type
changed. The tests were done on a table with only one column, and
that being whatever was being tested. I don't claim these tests to be
fault proof, just my own results.
Averages are taken from 50,000 rows inserted/selected. No other
access to the database was taking place. Tests were done on a pretty
stock version of MySQL on my desktop Sparc 20.
Table: CREATE TABLE benchmark_test (col1 TINYBLOB)
Insert: INSERT INTO benchmark_test (col1) VALUES ('$c') # $c is a
random 25 digit string
Select: SELECT col FROM benchmark_test
The tinyblob averaged: 0.000414 seconds per row while inserting, via
perl dbi, dbh-do
The tinyblob averaged: 0.000126 seconds per row while selecting, via
perl dbi, fetchrow_array
Table: CREAT TABLE benchmark_test (col1 VARCHAR(255))
Insert: INSERT INTO benchmark_test (col1) VALUES ('$c') # $c is a
random 25 digit string
Select: SELECT col1 FROM benchmark_test
The varchar averaged: 0.000369 seconds per row while inserting, via
perl dbi, dbh-do
The varchar averaged: 0.000124 seconds per row while selecting, via
perl dbi, fetchrow_array
As you can see in the CREATE syntax, no indexes are used, this is
just straight speed comparison testing. The actual speeds will depend
on your hardware and any indexes you use on your columns. But, in my
own personal experience, I have found that the speed -differences- in
the types usually remain constant, while the actual speed itself can
be changed with tweaking various other parts of the setup.
If anyone (for some strange reason) wants a copy of my script, just
email me off-list.
--
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Aaron Williams[EMAIL PROTECTED]
Black Raven.com http://www.backraven.com
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
-
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: order by, group by
Im trying to make a data system that is language-independent, ie the data stored can have any language therefore the same information is stored several times but with different values (depending on language) =Is this correct? data ~ news reports/magazine articles article ~ a news report (cf an item or product) language = spoken language (cf computer probgramming) different values (depending on language) = translations =Is name that of the author or the title of the article? =Is id a UNIQUE column, or if there are both English and French versions of the same article do they have the same id? Please describe id. ID is a unique column (primary key on the table could be (id,language) the same Article in different languages have the same id but different language codes. lets say that I have this table.. articles(id,language,name,description) what I would like to do is retrieve 1 row per unique id in the chosen language select * from articles where language='en'; = this will select ALL of the articles written in English - if there were two such articles with the same 'id', and in English, then they would both appear - unless all articles (id-s) have an English language translation, then this will not produce a list of all unique ids amongst the 'hits' (I assume this is not possible - right?) =do you want ALL articles, or only the one with a matching id? In some cases all, in some cases only one, in other cases some of them... You should know that the table I'm talking about is not the REAL table, but only something to talk about posting the real table-information would be breach of contract.. so the table I described is an example the real tables (there are many) have more keys, etc... that is quite simple, unfourtunally, not all articles have the description or name written in english and the above query would not return these articles... PROBLEM is what I get. however doing a select * from articles group by (id); will return all articles even if they do not have an english translation, =assuming that all the translations of the same article have the same id! The following will achieve a similar effect: SELECT * FROM articles ORDER BY id [ASC]; Yes, but I would still see ALL the different translations, and if 1 article have 7 translations, it would be found by the application 7times (where only 1 of them will be presented, which is a waste of network/processing time) IF I however could somehow order the results before the group by was done, I =or question the need to group the data - this clause is useful when you want to further analyse the data in those 'groups', but may not otherwise be useful. You do not appear to be using SQL to analyse the groups, you appear to only want the data collected/sequenced. that is correct, I want to collect the darta to the client and present them to the user.. could sort it so that first there would be english, then swedish, norwegian, etc... and at the bottom French ( :-) ) and then do the group by and the first language in this list would be the one I would get, English, and if there is no English I would get Swedish, and if there is no swedish, I would getnorwegian, and so on.. =so let's add language into the sequencing instruction: SELECT * FROM articles ORDER BY id, language; =now this does NOT answer your question because an article with a full set of translations will appear in the sequence dv, en, fr, no, se. And that is unfourtunally what I get today...(which I then have to programaticly have to discard several rows) =there are ways for you to dictate this sequence to be en, no, fr..., and if you're interested I'll try to recall the method... fins_in_set will help me with that... (see below) so what I do is the following select *,FIND_IN_SET(language,'no,dv,en') language2 from articlesorder by language2 desc; SELECT * FROM articles WHERE language = 'no' (etc or set construct) ORDER BY id; well I could build an where language='no' or language='se' or language but I'd prefere not to do it that way since I'm trying to make a system (standard within the company) for this so that I can use the system on several different tables with complex where clauses, etc.. I hope this explains better what I would like to do.. =why ALL the articles and not just one? Well getting a List of articles for example (that displays only the heading, or the name, etc) =what is your 'prime' language - or better, what is the order of language preference (from most prefered to least-used) Well that depends on the user, for me it would be swedish, english, norwegian, for a partner of mine it would be norwegian, swedish, english.. I am after all not doing this program for me, but for the users, and the users in this case will be very international! =it's getting too complicated for me - but someone who's good at this stuff might be able to solve it
UTF-8
If I understand correctly I can load data in UTF-8 to a MySql database that was compiled with character set (Latin-1). In all of the documentation that I looked at there is an indication that due to the lack of direct Unicode support in MySQL the use of the data which is stored in UTF-8 will be problematic. Can you tell me in which way will this be problematic? Did anyone use UTF-8 in a mysql database? Thanks Dana - 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: innodb inserts/select crash
Rich, a fix is to analyze the locking behavior of your database and try to find out why the locking SELECT has to wait long times. But I think the OUTFILE - INFILE trick is easier. Inside MySQL the fix would be to log individually rows inserted by CREATE ... SELECT ... But I am not responsible for the logging code in MySQL, and am afraid that we will not get that capability soon. The reason why we cannot just let CREATE TABLE ... SELECT ... to proceed without appropriate locking and logging is that you may later use the contents of your temp table to insert some rows to ordinary tables. In a roll-forward recovery the operations logged in the MySQL binlog must be executed in the exact same way they happened the first time. If we have incomplete information about the inserted rows, we may end up with a database which is different from the original one. What does this mean. How can I fix it. It seems silly that I have to read data out just to write it back in again instead of using the create...select command. Rich Date: Tue, 13 Nov 2001 12:14:55 To: [EMAIL PROTECTED] From: Heikki Tuuri [EMAIL PROTECTED] Subject: Re: innodb inserts/select crash Hi! You are getting a lock wait timeout error, not a crash. In the newest version 3.23.44 code 101 has been replaced by a native MySQL error number 1205 and a descriptive message. Hi there. Can anyone offer a solution to this problem. CREATE TABLE `raw` ( `cid` int(11) default NULL, `agent` char(255) default NULL, `referer` char(255) default NULL, `addr` char(15) default NULL, `via` char(255) default NULL, `forward` char(15) default NULL, `ctime` datetime default NULL, `uniq` int(1) default NULL, KEY `age` (`ctime`)) TYPE=InnoDB I have a process inserting 150 rows a second into this database. select cid,referer,count(*) as hits, ctime from raw WHERE uniq=1 group by cid,referer; takes 1.39 seconds to do. however if i add a simple insert statement to put those selects into a table i get this error mysql create table pagehits_tmp select cid,referer,count(*) as hits, ctime from raw WHERE uniq=1 group by cid,referer; ERROR 1030: Got error 101 from table handler I don't understand why it cannot do this when the select statement takes no time at all. perror(101) just returns unknown error. Any suggestions appreciated Ric The reason why CREATE TABLE ... SELECT ... sets shared locks on the rows in the SELECT table is that the MySQL binlog logs complete SQL strings as they are. If we do not lock the rows we read, then in recovery we do not know what rows actually were inserted. An ordinary SELECT does not set any locks: it is a consistent read. Hmm... what to do? You could do SELECT INTO OUTFILE + LOAD DATA INFILE to your table pagehits_tmp. Then MySQL no locks need to be set. Or you could try to figure out why the inserts may keep locks for a long time, and commit more often. The default for a lock wait timeout is 50 seconds. Regards, Heikki http://www.innodb.com/ibman.html - 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: why ever use TINYBLOB/TEXT -- isn't VARCHAR same size? (fwd)
At 16:07 + 2001/11/13, Carl Troein wrote: //C - with a runny nose, a cup of tea, and a glass of Laphroaig ...for medicinal purposes, of course. : My choice was McClellands, when reduced last week to a sorry-ass blob of protoplasm by the worst cold I've had in years. Feeling much better now. Hope you are, too. Yours in fine malts, /Rob -- Robert Alexander, Alpha Geek, Workmate.ca WWW Database Applications and Web Hosting http://www.workmate.ca 416-823-6599 mailto:[EMAIL PROTECTED] Life's unfair - but root password helps! - 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: UTF-8
Hi Yes I do. There is no problem with this, except sorting etc. Bu search is ok. Regards Daniel Las -Original Message- From: Dana Sharvit [mailto:[EMAIL PROTECTED]] Sent: Tuesday, November 13, 2001 6:35 PM To: '[EMAIL PROTECTED]' Subject: UTF-8 If I understand correctly I can load data in UTF-8 to a MySql database that was compiled with character set (Latin-1). In all of the documentation that I looked at there is an indication that due to the lack of direct Unicode support in MySQL the use of the data which is stored in UTF-8 will be problematic. Can you tell me in which way will this be problematic? Did anyone use UTF-8 in a mysql database? Thanks Dana - 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 - 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: how to do increments in parallel
The solution is fairly easy. In you update statement you place something like this : update table set valuefield = newvalue where keyfield = key and valuefield = oldvalue after the update you check affected rows. if affected rows = 0 then it means that somebody else already changed the valuefield. Then you have to do a reread and try again. Most of the time this is incorporated in a loop that gives the possibility for a number of retries. then you have something like this while retry 20 do update table if affected rows 0 then break retry ++ enddo if retry = 20 then errormessage - Original Message - From: Bennett Haselton [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, November 13, 2001 7:52 AM Subject: how to do increments in parallel Say I have two running programs and both of them periodically want to increment a value in a database. How can I do this so that the increments will be performed correctly even if the two programs try to do them at the same time? If I have code like this: $x = read_value_from_database(); ++$x; write_value_to_database($x); then the problem is that both programs might try and do their reads at the same time, then increment their own copies of the number, and then write back the same, incremented number. If the database system queues requests properly, then it won't give any error messages, but the final value of the number in the database will be 1 greater than what it was before, instead of what it should be, which is 2 greater. Is there a single command to increment a numeric value in a database? That way, the database program could queue those requests, run them in order, and always end up with the stored number having the right value. -Bennett [EMAIL PROTECTED] http://www.peacefire.org (425) 649 9024 - 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 - 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
Same table aliased twice causes infinite loop
I am running MySQL 3.23 on Windows 2000. I have a SQL statement which looks fine to me, although it does reference the same table twice. Here it is: SELECT DISTINCT u.inserted_usr_id, g.inserted_ugp_id FROM raw_users u, raw_data d, raw_groups g, raw_groups gg WHERE u.forename = d.forename AND u.surname = d.surname AND g.group_name = CONCAT(d.subject,' ',d.set_name) AND d.yr_and_reg = gg.group_name AND gg.parent_ugp_id = g.parent_ugp_id; When I try to run this, either from the Java servlet where it lives, or just via the mysql command line, my PC goes into a tailspin. Does MySQL not support aliasing the same table twice in one SQL statement? Thanks Nick - 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
File permissions
Hi All!
i tried the load_file after changing the File_priv in the user table of
mysql database for a particular user 'user1'...
insert into ecg_datafile values(1,1,load_file('/home/Venu/fr1w.jpg'));
ERROR 13: Can't get stat of '/home/Venu/fr1w.jpg' (Errcode: 13)
i get the following error, can anyone explain it!
regards
venu
=
Venugopal Allavatam Ph: (res.): 949-361-6604
1100 Calle Del Cerro, (mobile): 949-842-1767
Apt.# 123-J,
San Clemente, CA-92672 E-mail: [EMAIL PROTECTED]
[EMAIL PROTECTED]
__
Do You Yahoo!?
Find the one for you at Yahoo! Personals
http://personals.yahoo.com
-
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
binlog to a full disk loses updates
Description: Writing to the binlog doesn't stall the update process when the disk is full. How-To-Repeat: Turn on binlogging. Fill the disk. Do a few updates. Flush logs. Observe binlogs looking like this: -rw-rw1 mysqlmysql 360448 Nov 13 16:10 mysql.20766 -rw-rw1 mysqlmysql 0 Nov 13 16:20 mysql.20767 -rw-rw1 mysqlmysql 0 Nov 13 16:30 mysql.20768 -rw-rw1 mysqlmysql 0 Nov 13 16:40 mysql.20769 Note that the last record in mysql.20766 is NOT the one which forwards the slave's read pointer. Fix: Updating the standard log already blocks the database. Do the same for the binlog. Submitter-Id: submitter ID Originator:root Organization: noris network AG, Nuernberg, Germany MySQL support: licence Synopsis: Stall when the binlog can't be written due to disk full. Severity: serious Priority: high Category: mysql Class: sw-bug Release: mysql-3.23.34 and later Environment: System: Linux data3 2.4.2-noris-5r #3 SMP Fri Oct 5 14:22:25 CEST 2001 i686 unknown Architecture: i686 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/2.96/specs gcc version 2.96 2731 (Red Hat Linux 7.1 2.96-81) Compilation info: CC='gcc' CFLAGS='' CXX='c++' CXXFLAGS='-DTHREAD_SAFE_CLIENT -felide-constructors -fno-exceptions -fno-rtti' LDFLAGS='' LIBC: lrwxrwxrwx1 root root 13 Oct 7 17:10 /lib/libc.so.6 - libc-2.2.2.so -rwxr-xr-x1 root root 1236396 Apr 6 2001 /lib/libc-2.2.2.so -rw-r--r--1 root root 26350254 Apr 6 2001 /usr/lib/libc.a -rw-r--r--1 root root 178 Apr 6 2001 /usr/lib/libc.so Configure command: ./configure --prefix=/usr --with-debug --enable-shared --without-mit-threads --libexecdir=/usr/sbin --localstatedir=/var/mysql --enable-thread-safe-client --sysconfdir=/etc --datadir=/usr/share --enable-large-files --without-readline --with-mysqld-user=mysql --with-unix-socket-path=/var/run/mysql.socket --enable-strcoll '--with-comment=noris network MySQL' --with-docs --with-bench --without-berkeley-db --without-bench - 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
Permission problem
I created a new database on mysql server using Telnet. Before creating it i could log onto telnet as su root and select,delete , insert data into the mysql database and every other database. After creating this new database now when i try to type in USE MYSQL, it changes to the mysql database . And when i type in mysql select * from user; i get error message: Select command denied to user 'root@localhost' for table 'user' Where did i go wrong .. please help... i get the same error message when i try to use ne other database even the one i created just now. Regards, Harpreet Kaur Software Developer Crispin Corporations Inc. - 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
max_connect_errors security behind a firewall
What is the security concerns about incresing the number of max_connect_errors? And if I am behind a firewall and the server is only accessed by only one machine, in the case it=B4s accessed only by the webserver. I have to admit that I dont understand the purpose of a too low default number for max_connect_errors. Locking the server after only 10 connections seens to be paranoidy precaution. Pedro Furlanetto database,sql,query,table - bypassing the filter - 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: order by, group by
Christan Andersson wrote: lets say that I have this table.. articles(id,language,name,description) what I would like to do is retrieve 1 row per unique id in the chosen language select * from articles where language='en'; that is quite simple, unfourtunally, not all articles have the description or name written in english and the above query would not return these articles... PROBLEM is what I get. however doing a select * from articles group by (id); will return all articles even if they do not have an english translation, BUT here is the problem.. which language will be the one I recieve? Svedish? english? French? There seems to be a little confusion here regarding grouping and ordering. What do you mean by the one I receive? Do you only expect one? Or do you expect articles sorted by language in some order? It looks like what you are *REALLY* trying to do is to sort by ID first, collecting all the articles of one ID together, and within those groups, sort by a language. No? You really want: select * from articles ORDER BY id, language ... GROUPing is the act of collecting rows together using collection functions like COUNT(), MAX(), etc., based on a grouping criterion. E.g. if you wanted a result of count of articles for each article ID, you'd do something like select count(*) from articles GROUP BY id; By definition, such a query returns *one* row for each *distinct* value represented by the set of group-by keys. Any columns that you have in your select statement that are not collection expressions must be in the GROUP BY clause. If this is not obvious to you, read an introductory database book for a good explanation of these basic concepts.. So why don't we do an ORDER before a GROUP? The problem (or fact) here is that grouping is inherently an order-destroying operation. When you group columns, you are inherently sorting and coalescing the data, but using buckets determined by the group-by keys. The result is one row per bucket that has data. It's meaningless to sort before the group-by, because the group-by *is* a sort of another kind. *After* you finish grouping the results, you can re-sort the groups based on whatever criteria you pick. -- Shankar. - 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: Timezone offset question
Start the server with the desired TZ set. Jeremy Wilson wrote: I have a data collection script which rolls raw data into a formatted table, based on year/month/day. I've been requested to roll up this raw data based on a different timezone - 3 hours behind - to better coincide with reports from a company in that timezone. This is the rollup SQL query we currently run: SELECT DATE_FORMAT(date,'%Y%m%d'),sitecode,reseller,section,type,COUNT(DISTINCT ip), COUNT(ip) FROM rawdata WHERE YEAR(date) = YEAR(NOW()) AND MONTH(date) = MONTH(NOW()) AND (DAYOFMONTH(date) = DAYOFMONTH(NOW()) OR DAYOFMONTH(date) = DAYOFMONTH(DATE_SUB(NOW(),INTERVAL 1 DAY))) GROUP BY 1,2,3,4,5 ORDER BY 1,2,3,4,5 Basically it summerizes the totals for today and yesterday. My question is, given that query, what is the best method to rollup this offset data? use DATE_SUB on the SELECT, or perhaps on the DAYOFMONTH? Any suggestions would be appreciated. - 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 - 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: Access to MYSQL via ODBC?
Hello all, I just installed a Suse 7.2 Linux, with MySql.. MySQL runs, but when i try to access it, i get the following message: cleopatra:/usr/bin # mysql -h localhost -u root -p Enter password: ERROR 1045: Access denied for user: 'root@localhost' (Using password: NO) cleopatra:/usr/bin # mysql -h localhost -u root -p Enter password: ERROR 1045: Access denied for user: 'root@localhost' (Using password: YES) is there any way to change/reset the password if any or am i doing something wrong ? -- Best regards, SAE's System Administrator [EMAIL PROTECTED] World Council of Hellenes Abroad 2 Komotinis str. 54655 - Thessaloniki GREECE --__--__--__-- This message was passed through SAE's Mailgate Server. Our MailGate Server employs the latest antivirus antispam technology. Please report any errors to [EMAIL PROTECTED] - 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: Timezone offset question
At 01:07 PM 11/13/01 -0600, Gerald Clark wrote: Start the server with the desired TZ set. That's all fine and good, but difficult switch back and forth every 10 minutes while that query runs for local data, then for the remote data. - 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: Permission problem
I typed show Grants for root@localhost; and saw that the root has all permissions other then select. I can delete , insert data into the tables of all the databases but cannot select. How can i give the root@localhost 'select' permissions. show grants for root@localhost displays: Grant insert,update, delete,create,drop, reload, shutdown,process, file,alter on *.* to 'root'@'localhost' with grant option Help is greatly appreciated, Regards, Harpreet Kaur Software Developer Crispin Corporations Inc. -Original Message- From: Harpreet [mailto:[EMAIL PROTECTED]] Sent: Tuesday, November 13, 2001 1:26 PM To: 'MySQL Mailing list' Subject: Permission problem I created a new database on mysql server using Telnet. Before creating it i could log onto telnet as su root and select,delete , insert data into the mysql database and every other database. After creating this new database now when i try to type in USE MYSQL, it changes to the mysql database . And when i type in mysql select * from user; i get error message: Select command denied to user 'root@localhost' for table 'user' Where did i go wrong .. please help... i get the same error message when i try to use ne other database even the one i created just now. Regards, Harpreet Kaur Software Developer Crispin Corporations Inc. - 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 - 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: Same table aliased twice causes infinite loop
Yes, MYSQL allows the same table to be aliased twice or more in a select -Original Message- From: Nick de Voil [mailto:[EMAIL PROTECTED]] Sent: Tuesday, November 13, 2001 12:35 PM To: [EMAIL PROTECTED] Subject: Same table aliased twice causes infinite loop I am running MySQL 3.23 on Windows 2000. I have a SQL statement which looks fine to me, although it does reference the same table twice. Here it is: SELECT DISTINCT u.inserted_usr_id, g.inserted_ugp_id FROM raw_users u, raw_data d, raw_groups g, raw_groups gg WHERE u.forename = d.forename AND u.surname = d.surname AND g.group_name = CONCAT(d.subject,' ',d.set_name) AND d.yr_and_reg = gg.group_name AND gg.parent_ugp_id = g.parent_ugp_id; When I try to run this, either from the Java servlet where it lives, or just via the mysql command line, my PC goes into a tailspin. Does MySQL not support aliasing the same table twice in one SQL statement? Thanks Nick - 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 - 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
Joins with priority
Hi,
I have two tables, and I want to join them based on some priority rules:
If some special values exist in the joined subset,
join all of those values,
otherwise
join the first occurence of some other special value,
if that value also does not exist,
join the first occurence of a third value, and
if that value also does not exist,
join the first occurence of any value.
# create test tables
create table a (aid int,name char(10));
create table b (bid int,aid int,code char(1));
# insert dummy data
insert into a values (1,'rec 1'),(2,'rec 2'),(3,'rec 3'),(4,'rec 4');
insert into b values (1,1,'A'),(2,1,'B'),(3,1,'C'),(4,1,'D'),(5,1,'E');
insert into b values (6,2,'C'),(7,2,'C'),(8,2,'D'),(9,2,'E');
insert into b values (10,3,'D'),(11,3,'D'),(12,3,'E');
insert into b values (13,4,'E'),(14,4,'E'),(15,4,'F');
# listing all combinations
select a.aid,a.name,b.bid,b.code from a,b
where b.aid=a.aid order by a.aid,b.code;
+--+---+--+--+
| aid | name | bid | code |
+--+---+--+--+
|1 | rec 1 |1 | A|
|1 | rec 1 |2 | B|
|1 | rec 1 |3 | C|
|1 | rec 1 |4 | D|
|1 | rec 1 |5 | E|
|2 | rec 2 |6 | C|
|2 | rec 2 |7 | C|
|2 | rec 2 |8 | D|
|2 | rec 2 |9 | E|
|3 | rec 3 | 10 | D|
|3 | rec 3 | 11 | D|
|3 | rec 3 | 12 | E|
|4 | rec 4 | 13 | E|
|4 | rec 4 | 14 | E|
|4 | rec 4 | 15 | F|
+--+---+--+--+
# priority rules:
# if codes A or B exist in b: show _all_ of _both_
# otherwise, show _first_ C if it exist
# otherwise, show _first_ D if it exist
# otherwise, show _first_ existing code
In the test data, each of these four rules apply to the corresponding record
in table a: record 1 match rule 1, record 2 match rule 2 and so on.
# What we want is this:
+--+---+--+--+
| aid | name | bid | code |
+--+---+--+--+
|1 | rec 1 |1 | A|
|1 | rec 1 |2 | B|
|2 | rec 2 |6 | C|
|3 | rec 3 | 10 | D|
|4 | rec 4 | 13 | E|
+--+---+--+--+
What I've got so far, is this:
select distinct a.aid,a.name,b.bid,b.code
from a,b
left join b as pri1 on
pri1.aid=a.aid and
pri1.code in('A','B')
left join b as pri2 on
pri2.aid=a.aid and
pri2.code in('C')
left join b as pri2b on
pri2b.aid=a.aid and
pri2b.code in('C') and
pri2b.bid b.bid
left join b as pri3 on
pri3.aid=a.aid and
pri3.code in('D')
left join b as pri3b on
pri3b.aid=a.aid and
pri3b.code in('D') and
pri3b.bid b.bid
left join b as pri4 on
pri4.aid=a.aid and
pri4.code = b.code
left join b as pri4b on
pri4b.aid=a.aid and
pri4b.bid b.bid
where b.aid=a.aid and
(
(not ISNULL(pri1.bid) and
b.code=pri1.code) or
(ISNULL(pri1.bid) and
not ISNULL(pri2.bid) and
ISNULL(pri2b.bid) and
b.code=pri2.code) or
(ISNULL(pri1.bid) and
ISNULL(pri2.bid) and
not ISNULL(pri3.bid) and
ISNULL(pri3b.bid) and
b.code=pri3.code) or
(ISNULL(pri1.bid) and
ISNULL(pri2.bid) and
ISNULL(pri3.bid) and
not ISNULL(pri4.bid) and
ISNULL(pri4b.bid))
)
order by a.aid,b.code;
This query works, but is there an easier way?
(version 3.23.39 on solaris)
TIA,
Roger Baklund,
Mobiliant AS
-
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: My Book (WAS Re: MySQL Developer's Handbook)
On Mon, Nov 12, 2001 at 01:36:45PM -0800, Jeremy Zawodny wrote: Just *today* I signed a contract with O'Reilly Associates to write Advanced MySQL which I've begun working on. The goal is to have it out in the 2nd half of next year. We've been discussing it for a few months now, and we're all convinced that it's a necessary book and that now is the time to do it. It will cover MySQL 4.x (probably 4.1 based on the schedules I've heard). It will cover Advanced topics meaning that it will hit some that the other books have not and it will go into greater depth on some of the topics that do appear in the more beginner-oriented books. Can I request that you include a chapter on using MySQL with FreeBSD? If I recall correctly, that's something you should be able to write about. I'd be glad to send you the working TOC outline for feedback. If you're looking for kibbitzers, why not just post the TOC to the mailing list? Bob Hall -- In the room women come and go, Speaking of database, query, and table.T-Sql Elliot - 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
Can't Compile DBD::mysql
Hi Everybody There! I'm being tryng to install DBD::mysql but I ever get this answer, even I recompile perl with the same gcc in this same box, I wasn't the problem so the file 'blib/arch/auto/DBD/mysql/mysql.so' is there on the source tree. Has someboby an idea? Thanks in advance. -Jose Albert These are the fist lines of the output** t/00baseinstall_driver(mysql) failed: Can't load 'blib/arch/auto/DBD/mysql/mysql.so' for module DBD::mysql: blib/arch/auto/DBD/mysql/mysql.so: undefined symbol: mysql_real_escape_string at /usr/local/lib/perl5/5.6.1/i686- linux/DynaLoader.pm line 206. at (eval 1) line 3 Compilation failed in require at (eval 1) line 3. - 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: Same table aliased twice causes infinite loop
Nick de Voil writes: I am running MySQL 3.23 on Windows 2000. I have a SQL statement which looks fine to me, although it does reference the same table twice. Here it is: SELECT DISTINCT u.inserted_usr_id, g.inserted_ugp_id FROM raw_users u, raw_data d, raw_groups g, raw_groups gg WHERE u.forename = d.forename AND u.surname = d.surname AND g.group_name = CONCAT(d.subject,' ',d.set_name) AND d.yr_and_reg = gg.group_name AND gg.parent_ugp_id = g.parent_ugp_id; When I try to run this, either from the Java servlet where it lives, or just via the mysql command line, my PC goes into a tailspin. Does MySQL not support aliasing the same table twice in one SQL statement? Thanks Nick Hi! MySQL allows as many aliases as there could be tables in the join. Look for the tailspin somewhere else ... Try executing the above query from mysql.exe program. -- Regards, __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Sinisa Milivojevic [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer /_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus ___/ www.mysql.com - 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: Hot Backups
If your database is large enough then any sort of hot backup will lock the tables for too long. The method I use is to use LVM to take a snapshot of the MySQL partition while MySQL is stopped. All my attempts to snapshot a live MySQL database resulted in inconsistent results... The following is being used in an on-line production environment with an 11GB database, and a bunch of smaller ones. This method works on both Solaris 2.6 and Linux 2.4. Although the example uses the Solairs mount command this is all encapsulated into a perl script that works on both Solaris and Linux. mysqladmin shutdown /usr/sbin/mount -Fvxfs -o snapof=$snap_volume $snap_device $snap_mount nohup $mysql_bin_dir/safe_mysqld /dev/null cp -p -r $snap_mount/$dbname $snapshot/$dbname /usr/sbin/umount $snap_mount Using this the database is down for approximately 15 seconds. The actual database copy takes 30 minutes, but because it is copying from a LVM snapshot the MySQL server can be running at the same time. There is no impact to the web site because our queueing system automatically retries queries when the server goes away; most read queries don't even wait the 15 seconds because they are redirected to a read-only copy of the database. --- Matthew Costello - 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: Access to MYSQL via ODBC?
cleopatra:/usr/bin # mysql -h localhost -u root -p Enter password: ERROR 1045: Access denied for user: 'root@localhost' (Using password: NO) cleopatra:/usr/bin # mysql -h localhost -u root -p Enter password: ERROR 1045: Access denied for user: 'root@localhost' (Using password: YES) Try running w/o -p is there any way to change/reset the password if any or am i doing something wrong ? http://www.bitbybit.dk/mysqlfaq/faq.html#ch7_2_0 / Carsten -- Carsten H. Pedersen keeper and maintainer of the bitbybit.dk MySQL FAQ http://www.bitbybit.dk/mysqlfaq - 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
Introducing hidden row having duplicated primary key
Hello,
I think I've found a bug. Just by playing with REPLACE and INSERT, the
following happened. I'm including full log, demonstrating that I don't
understand sql at all. ;-) Can someone explain me what 2 rows affected
means? How can I select the second row from the table? How can I delete it?
Please CC me in reply. Thanks.
mysql select * from Upd;
++-+
| Tab| Time|
++-+
| prot_data | 2001-07-20 00:45:44 |
| contig_data| 2001-07-20 00:45:47 |
| orf_data | 2001-07-20 00:45:48 |
| blast_data | 2001-11-12 15:19:13 |
| seg_data | 2001-11-12 15:19:29 |
| nonglob_data | 2001-11-12 15:19:35 |
| pfam_data | 2001-11-12 15:33:25 |
| scop_data | 2001-11-12 15:33:41 |
| mem_data | 2001-11-12 15:33:47 |
| coils_data | 2001-11-12 15:33:54 |
| funcat_data| 2001-11-12 15:34:15 |
| cogs_data | 2001-11-12 15:34:59 |
| known3d_data | 2001-11-12 15:35:11 |
| blimps_data| 2001-11-12 15:37:55 |
| pros_data | 2001-11-12 15:38:08 |
| prd_data | 2001-11-12 15:39:16 |
| scop1_data | 2001-09-02 16:34:01 |
| scop2_data | 2001-09-02 17:05:52 |
| blast_crossupdate_data | 2001-08-23 18:05:12 |
| blast_self_data| 2001-10-03 05:15:48 |
| intergenome_data | 2001-10-23 20:56:48 |
++-+
21 rows in set (0.02 sec)
mysql replace into Upd Tab=test Time=NOW();
ERROR 1064: You have an error in your SQL syntax near 'Tab=test Time=NOW()' at line 1
mysql replace into Upd Tab=test Time=2;
ERROR 1064: You have an error in your SQL syntax near 'Tab=test Time=2' at line 1
mysql replace into Upd Tab=test Time=NOW();
ERROR 1064: You have an error in your SQL syntax near 'Tab=test Time=NOW()' at line 1
mysql replace into Upd set Tab=test Time=NOW();
ERROR 1064: You have an error in your SQL syntax near 'Time=NOW()' at line 1
mysql replace into Upd (Tab,Time) values ('test', NOW());
Query OK, 1 row affected (0.02 sec)
mysql update into Upd (Tab,Time) values ('test', NOW());
ERROR 1064: You have an error in your SQL syntax near 'into Upd (Tab,Time) values
('test', NOW())' at line 1
mysql update Upd (Tab,Time) values ('test', NOW());
ERROR 1064: You have an error in your SQL syntax near '(Tab,Time) values ('test',
NOW())' at line 1
mysql update Upd set Time=NOW() where Tab=test;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql replace into Upd (Tab,Time) values ('test', NOW());
Query OK, 2 rows affected (0.02 sec)
mysql select * from Upd;
++-+
| Tab| Time|
++-+
| prot_data | 2001-07-20 00:45:44 |
| contig_data| 2001-07-20 00:45:47 |
| orf_data | 2001-07-20 00:45:48 |
| blast_data | 2001-11-12 15:19:13 |
| seg_data | 2001-11-12 15:19:29 |
| nonglob_data | 2001-11-12 15:19:35 |
| pfam_data | 2001-11-12 15:33:25 |
| scop_data | 2001-11-12 15:33:41 |
| mem_data | 2001-11-12 15:33:47 |
| coils_data | 2001-11-12 15:33:54 |
| funcat_data| 2001-11-12 15:34:15 |
| cogs_data | 2001-11-12 15:34:59 |
| known3d_data | 2001-11-12 15:35:11 |
| blimps_data| 2001-11-12 15:37:55 |
| pros_data | 2001-11-12 15:38:08 |
| prd_data | 2001-11-12 15:39:16 |
| scop1_data | 2001-09-02 16:34:01 |
| scop2_data | 2001-09-02 17:05:52 |
| blast_crossupdate_data | 2001-08-23 18:05:12 |
| blast_self_data| 2001-10-03 05:15:48 |
| intergenome_data | 2001-10-23 20:56:48 |
| test | 2001-11-13 22:39:40 |
++-+
22 rows in set (0.02 sec)
mysql insert into Upd (Tab,Time) values ('test', NOW());
ERROR 1062: Duplicate entry 'test' for key 1
mysql select * from Upd;
++-+
| Tab| Time|
++-+
| prot_data | 2001-07-20 00:45:44 |
| contig_data| 2001-07-20 00:45:47 |
| orf_data | 2001-07-20 00:45:48 |
| blast_data | 2001-11-12 15:19:13 |
| seg_data | 2001-11-12 15:19:29 |
| nonglob_data | 2001-11-12 15:19:35 |
| pfam_data | 2001-11-12 15:33:25 |
| scop_data | 2001-11-12 15:33:41 |
| mem_data | 2001-11-12 15:33:47 |
| coils_data | 2001-11-12 15:33:54 |
| funcat_data| 2001-11-12 15:34:15 |
| cogs_data | 2001-11-12 15:34:59 |
| known3d_data | 2001-11-12 15:35:11 |
|
Re[2]: Access to MYSQL via ODBC?
Hello Carsten, Tuesday, November 13, 2001, 9:48:56 PM, you wrote: CHP Try running w/o -p cleopatra:/usr/bin # mysql -h localhost -u root ERROR 1045: Access denied for user: 'root@localhost' (Using password: NO) ;( -- Best regards, SAE's System Administrator [EMAIL PROTECTED] World Council of Hellenes Abroad 2 Komotinis str. 54655 - Thessaloniki GREECE --__--__--__-- This message was passed through SAE's Mailgate Server. Our MailGate Server employs the latest antivirus antispam technology. Please report any errors to [EMAIL PROTECTED] - 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: order by, group by
will return all articles even if they do not have an english translation, BUT here is the problem.. which language will be the one I recieve? Svedish? english? French? There seems to be a little confusion here regarding grouping and ordering. What do you mean by the one I receive? Do you only expect one? Or do you expect articles sorted by language in some order? I know that using group by the way I talked about it is not really the way that group by is intended to work.. It looks like what you are *REALLY* trying to do is to sort by ID first, collecting all the articles of one ID together, and within those groups, sort by a language. No? nope :-) since that would send to mych data from mysql to the application what I really want is the following.. let say that the table (id,language,name,description) where id,language is the primary key so that 1 id can have several languages the data in the database looks like this 1'en''blue circle''this is a blue ' 1'no''bla cirkel' 'dette er ei bla cirkel' 2'en''green leaf' 'this is a green leaf from a tree' 2'sv''grönt löv' 'detta är ett grönt löv från ett träd' if the language priority is en,sv,no the selectwould return the following 1'en''blue circle''this is a blue ' 2'en''green leaf' 'this is a green leaf from a tree' if however the language prority was sv,no,en the select should return the following 1'no''bla cirkel' 'dette er ei bla cirkel' 2'sv''grönt löv' 'detta är ett grönt löv från ett träd' since for id 1 there is no svedish translation it should choose the norwegian translation You really want: select * from articles ORDER BY id, language ... this query would return all 4 rows, and my application would then have to discard 2 of them.. GROUPing is the act of collecting rows together using collection functions like COUNT(), MAX(), etc., based on a grouping criterion. E.g. if you wanted a result of count of articles for each article ID, you'd do something like select count(*) from articles GROUP BY id; By definition, such a query returns *one* row for each *distinct* value represented by the set of group-by keys. which is what I want, I only want 1 versionof the id returned although the query can return many different id:s 1 id should only be representated once.. which is what group by does, however since mysql allows one to do a group by with less columns then selected, from which rows are those data that are not unique for example making a group by on my table on the id-column, which row is the language,name and description taken from.. IF i could controle which row that was, then group by would definitly work for me.. It looks like the data in those columns are the datafrom the first row that mysql encounters.. so what if I could affect which row would be first (for example doing a order by before the group by) then the query would do exaclty what I wanted it to do, even if the functionallity fo the functions were not intended for that usage to begin with (a feature) however it looks to me that I cannot control the order of the rows before the group by Any columns that you have in your select statement that are not collection expressions must be in the GROUP BY clause. If this is not obvious to you, read an introductory database book for a good explanation of these basic concepts.. This is not the case with mysql, and it is that functionallity that I was trying to use.. you can in mysql do a group by on less columns then what are in the select statement the problem is what data are presented in the columns that are not in the group by (se above for this) So why don't we do an ORDER before a GROUP? The problem (or fact) here is that grouping is inherently an order-destroying operation. When you group columns, you are inherently sorting and coalescing the data, but using buckets determined by the group-by keys. The result is one row per bucket that has data. It's meaningless to sort before the group-by, because the group-by *is* a sort of another kind. well using mysql:s type of order by, I see no problem in using an order by before group by since it would allow me to tell what to retrieve in the columns not in the group by.. I have solved the problem in 2 different ways already, one returns to many rows to the application and I have to in the application discard data (not good) the other is using temporary tables, and is working, but if it is working inthe next versionof mysql, I do not know, since it is based onthe fact that the group by is taking the first row:s data for columns that are not in the group by, and the insertion order in the temporary table.. but this is a pure Hack! if I could find 1 sql-query to do the work for me, I woul dbe extremely happy, but sofar I have not managed to find it /Christian Andersson -
Re: Introducing hidden row having duplicated primary key
On Tue, Nov 13, 2001 at 08:53:09PM +0100, Martin MOKREJS wrote: Hello, I think I've found a bug. Just by playing with REPLACE and INSERT, the following happened. I'm including full log, demonstrating that I don't understand sql at all. ;-) Can someone explain me what 2 rows affected means? It usually means that replace found the row to replace do it deleted it (affecting one row) and then inserted the new one (affecting the second row). Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.41-max: up 68 days, processed 1,510,023,205 queries (254/sec. avg) - 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: My Book (WAS Re: MySQL Developer's Handbook)
On Tue, Nov 13, 2001 at 02:20:59PM -0500, Bob Hall wrote: Can I request that you include a chapter on using MySQL with FreeBSD? If I recall correctly, that's something you should be able to write about. I probably should talk about some specific OS issues. I'll have to look at the outline and find the logical place to do that. I'd be glad to send you the working TOC outline for feedback. If you're looking for kibbitzers, why not just post the TOC to the mailing list? Well, since I've had many private requests that I do just that, I'll post it later today. I was hesitant at first (for a few reasons), but the benefits probably outweight the possible problems. Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.41-max: up 68 days, processed 1,510,066,070 queries (254/sec. avg) - 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: Introducing hidden row having duplicated primary key
Looks correct to me.
What do you think is the problem?
2 rows were affected by the replace.
One row was deleted, and one was inserted,
Both rows had a value of 'test' for column 'Tab'.
Martin MOKREJ wrote:
Hello,
I think I've found a bug. Just by playing with REPLACE and INSERT, the
following happened. I'm including full log, demonstrating that I don't
understand sql at all. ;-) Can someone explain me what 2 rows affected
means? How can I select the second row from the table? How can I delete it?
Please CC me in reply. Thanks.
mysql select * from Upd;
++-+
| Tab| Time|
++-+
| prot_data | 2001-07-20 00:45:44 |
| contig_data| 2001-07-20 00:45:47 |
| orf_data | 2001-07-20 00:45:48 |
| blast_data | 2001-11-12 15:19:13 |
| seg_data | 2001-11-12 15:19:29 |
| nonglob_data | 2001-11-12 15:19:35 |
| pfam_data | 2001-11-12 15:33:25 |
| scop_data | 2001-11-12 15:33:41 |
| mem_data | 2001-11-12 15:33:47 |
| coils_data | 2001-11-12 15:33:54 |
| funcat_data| 2001-11-12 15:34:15 |
| cogs_data | 2001-11-12 15:34:59 |
| known3d_data | 2001-11-12 15:35:11 |
| blimps_data| 2001-11-12 15:37:55 |
| pros_data | 2001-11-12 15:38:08 |
| prd_data | 2001-11-12 15:39:16 |
| scop1_data | 2001-09-02 16:34:01 |
| scop2_data | 2001-09-02 17:05:52 |
| blast_crossupdate_data | 2001-08-23 18:05:12 |
| blast_self_data| 2001-10-03 05:15:48 |
| intergenome_data | 2001-10-23 20:56:48 |
++-+
21 rows in set (0.02 sec)
mysql replace into Upd Tab=test Time=NOW();
ERROR 1064: You have an error in your SQL syntax near 'Tab=test Time=NOW()' at
line 1
mysql replace into Upd Tab=test Time=2;
ERROR 1064: You have an error in your SQL syntax near 'Tab=test Time=2' at line 1
mysql replace into Upd Tab=test Time=NOW();
ERROR 1064: You have an error in your SQL syntax near 'Tab=test Time=NOW()' at
line 1
mysql replace into Upd set Tab=test Time=NOW();
ERROR 1064: You have an error in your SQL syntax near 'Time=NOW()' at line 1
mysql replace into Upd (Tab,Time) values ('test', NOW());
Query OK, 1 row affected (0.02 sec)
mysql update into Upd (Tab,Time) values ('test', NOW());
ERROR 1064: You have an error in your SQL syntax near 'into Upd (Tab,Time) values
('test', NOW())' at line 1
mysql update Upd (Tab,Time) values ('test', NOW());
ERROR 1064: You have an error in your SQL syntax near '(Tab,Time) values ('test',
NOW())' at line 1
mysql update Upd set Time=NOW() where Tab=test;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql replace into Upd (Tab,Time) values ('test', NOW());
Query OK, 2 rows affected (0.02 sec)
mysql select * from Upd;
++-+
| Tab| Time|
++-+
| prot_data | 2001-07-20 00:45:44 |
| contig_data| 2001-07-20 00:45:47 |
| orf_data | 2001-07-20 00:45:48 |
| blast_data | 2001-11-12 15:19:13 |
| seg_data | 2001-11-12 15:19:29 |
| nonglob_data | 2001-11-12 15:19:35 |
| pfam_data | 2001-11-12 15:33:25 |
| scop_data | 2001-11-12 15:33:41 |
| mem_data | 2001-11-12 15:33:47 |
| coils_data | 2001-11-12 15:33:54 |
| funcat_data| 2001-11-12 15:34:15 |
| cogs_data | 2001-11-12 15:34:59 |
| known3d_data | 2001-11-12 15:35:11 |
| blimps_data| 2001-11-12 15:37:55 |
| pros_data | 2001-11-12 15:38:08 |
| prd_data | 2001-11-12 15:39:16 |
| scop1_data | 2001-09-02 16:34:01 |
| scop2_data | 2001-09-02 17:05:52 |
| blast_crossupdate_data | 2001-08-23 18:05:12 |
| blast_self_data| 2001-10-03 05:15:48 |
| intergenome_data | 2001-10-23 20:56:48 |
| test | 2001-11-13 22:39:40 |
++-+
22 rows in set (0.02 sec)
mysql insert into Upd (Tab,Time) values ('test', NOW());
ERROR 1062: Duplicate entry 'test' for key 1
mysql select * from Upd;
++-+
| Tab| Time|
++-+
| prot_data | 2001-07-20 00:45:44 |
| contig_data| 2001-07-20 00:45:47 |
| orf_data | 2001-07-20 00:45:48 |
| blast_data | 2001-11-12 15:19:13 |
| seg_data | 2001-11-12 15:19:29 |
| nonglob_data | 2001-11-12 15:19:35 |
| pfam_data | 2001-11-12
Re: Introducing hidden row having duplicated primary key
On Tue, 13 Nov 2001, Gerald Clark wrote: Looks correct to me. What do you think is the problem? 2 rows were affected by the replace. One row was deleted, and one was inserted, Both rows had a value of 'test' for column 'Tab'. Cool, thanks to Jeremy Zawodny and you, now I know even more that I do not know sql. Well, I told you that. ;) I thought that REPLACE replaces only fields which are different, so if a line is missing, behaves as an insert, if the line is present, behaves as an updates on columns which differ. This means that I was hoping that UPDATE updates/rewrites all specified columns on line; REPLACE has some logic to figure out that we do not have to rewrite a column with the same data and so we do not have to recreate an index for such column. If I guess right, deleting a row means marking it as deleted, right, so the REPLACE in my case marked the old line/row deleted and appended a new row to the table? That would mean it's better to use UPDATE then REPLACE, right? ;) -- Martin Mokrejs - PGP5.0i key is at http://www.natur.cuni.cz/~mmokrejs MIPS / Institute for Bioinformatics http://mips.gsf.de GSF - National Research Center for Environment and Health Ingolstaedter Landstrasse 1, D-85764 Neuherberg, Germany tel.: +49-89-3187 3616 , fax:+49-89-3187 3585 - 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: Introducing hidden row having duplicated primary key
On Tue, Nov 13, 2001 at 12:45:17PM -0800, Jeremy Zawodny wrote: On Tue, Nov 13, 2001 at 08:53:09PM +0100, Martin MOKREJS wrote: Hello, I think I've found a bug. Just by playing with REPLACE and INSERT, the following happened. I'm including full log, demonstrating that I don't understand sql at all. ;-) Can someone explain me what 2 rows affected means? It usually means that replace found the row to replace do it deleted it (affecting one row) and then inserted the new one (affecting the second row). s/do/so/ :-( -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.41-max: up 68 days, processed 1,510,481,687 queries (254/sec. avg) - 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: how to do increments in parallel
On Mon, Nov 12, 2001 at 11:29:48PM -0800, Jeremy Zawodny wrote: On Mon, Nov 12, 2001 at 10:52:39PM -0800, Bennett Haselton wrote: Say I have two running programs and both of them periodically want to increment a value in a database. How can I do this so that the increments will be performed correctly even if the two programs try to do them at the same time? If I have code like this: $x = read_value_from_database(); ++$x; write_value_to_database($x); UPDATE mytable SET col = col+1; With an appropriate WHERE clause, of course. :-) -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.41-max: up 68 days, processed 1,510,494,591 queries (254/sec. avg) - 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: order by, group by
Christan Andersson wrote: what I really want is the following.. let say that the table (id,language,name,description) where id,language is the primary key so that 1 id can have several languages the data in the database looks like this 1'en''blue circle''this is a blue ' 1'no''bla cirkel' 'dette er ei bla cirkel' 2'en''green leaf' 'this is a green leaf from a tree' 2'sv''grönt löv' 'detta är ett grönt löv från ett träd' if the language priority is en,sv,no the selectwould return the following 1'en''blue circle''this is a blue ' 2'en''green leaf' 'this is a green leaf from a tree' if however the language prority was sv,no,en the select should return the following 1'no''bla cirkel' 'dette er ei bla cirkel' 2'sv''grönt löv' 'detta är ett grönt löv från ett träd' Ah. That's quite different. You can't do this using GROUP BY either, by the way. You'd have to compute some value column based on the language and the preference list such that the first choice language evaluates to 1, the second choice language evaluates to 2, etc. (See the CASE function under Control Flow functions). You'd then need to do one select with group-by to get the MIN of this value for each article number, which will give you the article ID and the mapped value that's the least, for each article, which you can shove into a temporary table. You then have to join that against the whole table to get the rest of the details. Or something along those lines. Or else, just order by such a map function, and use software logic to only select the first returned value for each article number, which may well be faster.. -- Shankar. - 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
Show query page by page
Hi, I have a query result with 50 register and i want to show them in 5 pages, every page show 10 register. Thanks , for your answers Rhony - 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: update from table x to table y
=No there isn't, because: 7.24 UPDATE Syntax UPDATE [LOW_PRIORITY] [IGNORE] tbl_name SET col_name1=expr1, [col_name2=expr2, ...] [WHERE where_definition] [ORDER BY ...] [LIMIT #] - there is only 'space' for one tbl_name in the UPDATE clause. =I've had a quick look around. I'm sure someone on the list mentioned this recently (but I can't find the msg/must have deleted it). This sort of functionality might not be very far away in the MySQL development plan... =coming back to your original question: can this dual-table update actually be done in Access? =coming back to my recent point, is the data properly normalised? I'm having difficulty (don't I always?) in visualising a reason for doing this. Would you like to discuss your application and put some sample data in front of us, in the hope that someone might be able to make a sensible (and successful) suggestion? Also what tool(s) are you using to access MySQL? =Sorry! =dn Hi, thanks dn, so, to confirm, there is no MySQL to represent the following?: update Raw,Unresolved_Duplicates_perm set Raw.Inactivate = 1 where Raw.Key_num = Unresolved_Duplicates_perm.MinOfKey and Raw.Peak1=0 Thanks Rich From: DL Neil [EMAIL PROTECTED] Reply-To: DL Neil [EMAIL PROTECTED] To: Richard Dobson [EMAIL PROTECTED], [EMAIL PROTECTED] Subject: Re: update from table x to table y Date: Tue, 13 Nov 2001 13:49:23 - Duplicating values (cf keys) in tables involves de-normalisation and is therefore not recommended. Your observation row cf column is correct. I felt the question was sufficiently broad to risk interpreting update as wider than UPDATE, sorry - the other response seemed to hit that nail on the head (have deleted it). If it doesn't suit, then another solution might be to use PHP to SELECT the data from table1 and then use a second query to UPDATE table-2 SET table2-col = table1-col WHERE table1-val = table2-val (which, in its present form, has the potential to modify numerous rows in one hit). =Regards, =dn thanks for that, but that will add a row as opposed to updating a column won't it? I don't want to insert a new row. All I want to do is update a value in table1 if it is present in table2 cheers Rich From: DL Neil [EMAIL PROTECTED] Reply-To: DL Neil [EMAIL PROTECTED] To: Richard Dobson [EMAIL PROTECTED], [EMAIL PROTECTED] Subject: Re: update from table x to table y Date: Tue, 13 Nov 2001 11:45:45 - Hi does anyone know of a way of taking some data from one table and updating another table with it? If MySQL doesn't support it i'm gonna have to go back to Access or something! Rich =only go back to Access if you're a glutton for punishment! =try MySQL/HTMLmanual/manual_Reference.html#INSERT_SELECT =dn - 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 _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp - 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 _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp - 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 - 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: Cannot add blob data to innodb table
On Mon, 12 Nov 2001, Heikki Tuuri wrote: Date: Mon, 12 Nov 2001 09:06:42 +0200 From: Heikki Tuuri [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: Re: Cannot add blob data to innodb table Steve, Date: Mon, 12 Nov 2001 08:52:54 To: [EMAIL PROTECTED] From: Heikki Tuuri [EMAIL PROTECTED] Subject: Re: Cannot add blob data to innodb table Stephen, Hi, I have an Access97 table containing binary fields (gifs) that I would like to migrate to an innodb format via MyODBC (2.50.39) and MySQL-Max (3.23.44). The MySQL query log shows binary-like characters being received but there never seems to be progression to the next record. After 5 or so minutes, the append query in Access97 quits and an ODBC error window complaining about a lost connection pops-up. When I convert the table to myisam type, the append query works. Is there something in my.cnf I need to adjust to fix thisproblem? inserting binary BLOBs should work. What is an 'append' query in MS Access? Is it translated to an INSERT in MySQL? Could you paste a copy of what the MySQL query logs shows? How big is the BLOB? What is the CREATE TABLE statement? Note that a BLOB bigger than 64 kB needs to be defined a MEDIUMBLOB or LONGBLOB in the CREATE TABLE statement. If you try inserting an ASCII text file does that work? How big you have set max_allowed_packet in my.cnf? That restricts the size of rows communicated from a client to the server. Thanks, Stephen Regards, Heikki http://www.innodb.com actually, are you running MySQL on Windows? Then the problem might be the bug introduced in 3.23.42: to access InnoDB tables you must use the same case of letters in the database name as you used in the CREATE TABLE statement. Make sure you consistently use lower case in database names. The bug is fixed in upcoming 3.23.45. Please don't fix this. Case insensitivity means that you can't use windows to test something which will run under Unix where CASE COUNTS. Case insensitivity in table names is one of the more annoying Gatesisms that limit the usability of windows for testing. Does mysql.err contain anything? Regards, Heikki - 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 Sincerely, William Mussatto, Senior Systems Engineer CyberStrategies, Inc ph. 909-920-9154 ext. 27 - 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
replication errors (broken binlog-do-db?)
Description: I have two DB servers that we will simply call MASTER and SLAVE. MASTER is started with --binlog-do-db=DB1 so that only DB1 will be replicated to SLAVE. When SLAVE is initially started everything seems normal, in that all update/delete/insert/etc. queries are propogated to SLAVE. However, if I issue a query similar to the one below, the SLAVE dies: Example Query: INSERT INTO DB2.sometable (field1,field2) SELECT field1,field2 FROM DB1.sometable WHERE (some condition); Query Explanation: Basically it moves all data from one table in DB1 to a table in DB2 Error: mysql SHOW SLAVE STATUS\G *** 1. row *** Master_Host: master.host Master_User: replication Master_Port: 3306 Connect_retry: 60 Log_File: master-bin.001 Pos: 31191383 Slave_Running: No Replicate_do_db: Replicate_ignore_db: Last_errno: 1146 Last_error: error 'Table 'DB2.sometable' doesn't exist' on query 'INSERT INTO DB2.sometable (field1,field2) SELECT field1,field2 FROM DB1.sometable WHERE (some condition);' Skip_counter: 0 1 row in set (0.00 sec) How-To-Repeat: I can reproduce this error with the above configuration at any time. All servers are binary distribution of 3.23.44 for linux (glibc). MySQLd startup options for each server are as follows: MASTER -- --set-variable back_log=100 --set-variable long_query_time=5 --set-variable delayed_insert_timeout=60 --set-variable delayed_insert_limit=30 --set-variable max_connections=256 --set-variable max_connect_errors=1 --set-variable table_cache=256 --set-variable wait_timeout=60 --set-variable thread_cache_size=10 --set-variable key_buffer_size=64M --log-bin --server-id=1 --binlog-do-db=DB1 --user=mysql SLAVE - --set-variable back_log=100 --set-variable long_query_time=5 --set-variable max_connections=256 --set-variable max_connect_errors=1 --set-variable table_cache=256 --set-variable wait_timeout=60 --master-host=master.host --master-user=someuser --master-password=XXX --server-id=2 --user=mysql Fix: Well I can rewrite my query, but that doesn't seem like a permanant solution. I'm open to suggestions. The query IMHO shouldn't be getting put into the binary log since the DB that is effected is not the one specified with --binlod-do-db flag. I suspect whatever --binlog-do-db does it at fault. This behavior may be by design, I don't know. Any response would be appreciated, I am not on the mysql list so please respond via personal email if possible. Even if this behavior is intentional I would appreciate knowing, so I can determine what needs redesigned in my system. Submitter-Id: submitter ID Originator:wrath Organization: MySQL support: none Synopsis: Replication errors with INSERT INTO..SELECT FROM Severity: serious Priority: medium Category: mysql Class: sw-bug Release: mysql-3.23.44 (Official MySQL binary) Environment: System: Linux XX 2.2.19 #2 Fri Jun 8 04:23:06 UTC 2001 i686 unknown Architecture: i686 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc-lib/i386-slackware-linux/egcs-2.91.66/specs gcc version egcs-2.91.66 19990314/Linux (egcs-1.1.2 release) Compilation info: CC='gcc' CFLAGS='-O3 -mpentium ' CXX='gcc' CXXFLAGS='-O3 -mpentium -felide-constructors' LDFLAGS='-static' LIBC: lrwxrwxrwx 1 root root 13 Apr 19 2001 /lib/libc.so.6 - libc-2.1.3.so -rwxr-xr-x 1 root root 1013224 Mar 21 2000 /lib/libc-2.1.3.so -rw-r--r-- 1 root root 20266642 Mar 20 2000 /usr/lib/libc.a -rw-r--r-- 1 root root 178 Mar 20 2000 /usr/lib/libc.so Configure command: ./configure --prefix=/usr/local/mysql '--with-comment=Official MySQL binary' --with-extra-charsets=complex --with-server-suffix= --enable-assembler --with-mysqld-ldflags=-all-static --with-client-ldflags=-all-static --disable-shared - 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:
myisamchk -a + indexes + hidden...?
It seems like myisamchk -a is hosing some statistic in a MyISAM table that gets re-created and stored permanently as once a query is run that uses that index, it always runs well until myisamchk -a is run again even between restarts of mysqld. It also seems that key_buffer_size has no effect on the results. Can someone explain this to me? There are a a bunch of tables merged into three main tables. The query does a two column join between the tables, e.g.: a.1=b.1 AND a.2=b.2 AND b.1=c.1 AND b.3=c.3 --Bill [root@host /usr/local/mysql-4.0/var]# ../bin/mysqladmin -uroot -p version ../bin/mysqladmin Ver 8.22 Distrib 4.0.0-alpha, for pc-linux-gnu on i686 Copyright (C) 2000 MySQL AB MySQL Finland AB TCX DataKonsult AB This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license Server version 4.0.0-alpha-log Protocol version10 Connection Localhost via UNIX socket UNIX socket /tmp/mysql-4.0.sock Uptime: 7 min 2 sec Threads: 3 Questions: 103 Slow queries: 0 Opens: 12 Flush tables: 46 Open tables: 3 Queries per second avg: 0.244 [root@host /usr/local/mysql-4.0/var]# uname -a Linux host.tqs.com 2.2.19 #6 SMP Wed Jul 11 10:55:03 PDT 2001 i686 unknown [root@host /usr/local/mysql-4.0/var]# vmstat procs memory swap io system cpu r b w swpd free buff cache si so bibo incs us sy id 2 1 0 4 2612 40752 1450688 0 0 4 33 6 10 2 6 [root@host /usr/local/mysql-4.0/var]# ldd ../libexec/mysqld librt.so.1 = /lib/librt.so.1 (0x2aac8000) libdl.so.2 = /lib/libdl.so.2 (0x2aacc000) libpthread.so.0 = /lib/libpthread.so.0 (0x2aad) libz.so.1 = /usr/lib/libz.so.1 (0x2aae3000) libcrypt.so.1 = /lib/libcrypt.so.1 (0x2aaf3000) libnsl.so.1 = /lib/libnsl.so.1 (0x2ab2) libstdc++-libc6.1-1.so.2 = /usr/lib/libstdc++-libc6.1-1.so.2 (0x2ab36000) libm.so.6 = /lib/libm.so.6 (0x2ab78000) libc.so.6 = /lib/libc.so.6 (0x2ab95000) /lib/ld-linux.so.2 = /lib/ld-linux.so.2 (0x2aaab000) [root@host /usr/local/mysql-4.0/var]# myisamchk -a (on all tables) set-variable= key_buffer=32M Time to start getting results: 127 seconds. Total Time: 146 seconds (18251 rows, 125 rows/sec overall) Run the query again: Time to start getting results: 11 seconds. Total Time: 30 seconds (18251 rows, 608 rows/sec) shutdown mysql set-variable = key_buffer=1M start mysql myisamchk -a (on all tables) Time to start getting results: 121 seconds. Total Time: 141 seconds (18251 rows, 129 rows/sec) Second Run: Run the query again: Time to start getting results: 10 seconds. Total Time: 29 seconds (18251 rows, 629 rows/sec) Shutdown and restart MySQL. Note: key_buffer still at 1M Time to start getting results: 12 seconds. Total Time: 31 seconds (18251 rows, 588 rows/sec) Shutdown MySQL Set key buffer size to 0 start mysql Time to start getting results: 10 seconds. Total Time: 29 seconds (18251 rows, 629 rows/sec) myisamchk -a Time to start getting results: 145 seconds. Total Time: 164 seconds (18251 rows, 111 rows/sec) -- Bill Adams TriQuint Semiconductor - 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: table corrupted after an error free load
Riccardo Cohen wrote: Hi, Sorry to disturb, but I cannot find any answer in online doc and web archive. Description: I insert 34000 rows in a simple table with all text fields, and myisamchk tells the table is corrupted, while a select into outfile does not give any error compared to the original file. select * into outfile... does not use any indexes (I think) so if it is only your index that is corrupted then there will be no problem. If the data file is corrupted such that there is an extra bad record, I would bet that it just gets skipped over. MySQL is good at giving you everything it possibly can even if there are 'issues' with the tables/indexes. Have you tried 'myisamchk -r table' to try to repair it? b. - 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: Timezone offset question
Jeremy Wilson wrote: At 01:07 PM 11/13/01 -0600, Gerald Clark wrote: Start the server with the desired TZ set. That's all fine and good, but difficult switch back and forth every 10 minutes while that query runs for local data, then for the remote data. The way I deal with it (and I don't know that it will help you) is that I store datetime as an integer from time() GMT. Then in the report I convert the time to the correct timezone based on the user's IP (internal network, different subnets for different TZ/locations). Of course this is a bad idea sometime in 2039 (?) or the end of Unix time as we know it. b. mysql - 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: Same table aliased twice causes infinite loop
Sinisa Milivojevic wrote: Nick de Voil writes: I am running MySQL 3.23 on Windows 2000. I have a SQL statement which looks fine to me, although it does reference the same table twice. Here it is: SELECT DISTINCT u.inserted_usr_id, g.inserted_ugp_id FROM raw_users u, raw_data d, raw_groups g, raw_groups gg WHERE u.forename = d.forename AND u.surname = d.surname AND g.group_name = CONCAT(d.subject,' ',d.set_name) AND d.yr_and_reg = gg.group_name AND gg.parent_ugp_id = g.parent_ugp_id; When I try to run this, either from the Java servlet where it lives, or just via the mysql command line, my PC goes into a tailspin. Does MySQL not support aliasing the same table twice in one SQL statement? Thanks Nick Hi! MySQL allows as many aliases as there could be tables in the join. Look for the tailspin somewhere else ... For instance, a missing join clause. It could be trying to do every permutation of the results wich N! gets big fast. b. mysql - 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: Show query page by page
Auri Net SAC wrote: Hi, I have a query result with 50 register and i want to show them in 5 pages, every page show 10 register. LIMIT (see the manual) will help you. b. mysql - 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: Show query page by page
On Tue, Nov 13, 2001 at 04:24:47AM -0500, Auri Net SAC wrote: Hi, I have a query result with 50 register and i want to show them in 5 pages, every page show 10 register. Then you want to use the LIMIT option on your SELECT queries as shown in the manual. Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.41-max: up 68 days, processed 1,510,824,704 queries (254/sec. avg) - 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: Search Engines
Karl J. Stubsjoen wrote: Hello, I need to create a search engine out of a few MySQL tables I should say: I need to search MySQL records like a search engin might. However, my first go ended up as a complete failure because it is highly un-optimized to search for (as an example) %apple% in all of the available text fields. Any ideas about where I can look to set up a database optimized for searching in this way? Read the manual and list threads on fulltext indexes. b. mysql - 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: Timezone offset question
Start the server with the desired TZ set. That's all fine and good, but difficult switch back and forth every 10 minutes while that query runs for local data, then for the remote data. =Agreed you must stick with either one or the other, right? =Wrong! To fix the problem of different offices spread out around the world, you either allow each office to report in local time, ie today's data, last month's data, etc; or you standardise all time-date oriented data into one common timebase/zone. =There are two 'standards': American = whichever time zone head office is; International = GMT/UTC/Zulu =PHP users are always happy at the latter because there are a neat set of built-in time functions that run at UTC [gm*()] regardless of the server/client computers' ToD clocks. =dn - 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: myisamchk -a + indexes + hidden...?
Analyzing a table requires looking at every record in the table. If the table you are dealing with is a large one then there is a good chance that after analyzing the table your disk cache will not contain the records that you are trying to grab. Once you run the query it will almost certainly contain those records. You could test this theory by rebooting your machine (if that is an option) and seeing if it takes the longer amount of time for the first query. Or I could be completely off base. It wouldn't be the first time. :-) Jon Gardiner. -Original Message- From: Bill Adams [mailto:[EMAIL PROTECTED]] Sent: Tuesday, November 13, 2001 2:47 PM To: Mysql List Subject: myisamchk -a + indexes + hidden...? It seems like myisamchk -a is hosing some statistic in a MyISAM table that gets re-created and stored permanently as once a query is run that uses that index, it always runs well until myisamchk -a is run again even between restarts of mysqld. It also seems that key_buffer_size has no effect on the results. Can someone explain this to me? There are a a bunch of tables merged into three main tables. The query does a two column join between the tables, e.g.: a.1=b.1 AND a.2=b.2 AND b.1=c.1 AND b.3=c.3 --Bill [root@host /usr/local/mysql-4.0/var]# ../bin/mysqladmin -uroot -p version ../bin/mysqladmin Ver 8.22 Distrib 4.0.0-alpha, for pc-linux-gnu on i686 Copyright (C) 2000 MySQL AB MySQL Finland AB TCX DataKonsult AB This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license Server version 4.0.0-alpha-log Protocol version10 Connection Localhost via UNIX socket UNIX socket /tmp/mysql-4.0.sock Uptime: 7 min 2 sec Threads: 3 Questions: 103 Slow queries: 0 Opens: 12 Flush tables: 46 Open tables: 3 Queries per second avg: 0.244 [root@host /usr/local/mysql-4.0/var]# uname -a Linux host.tqs.com 2.2.19 #6 SMP Wed Jul 11 10:55:03 PDT 2001 i686 unknown [root@host /usr/local/mysql-4.0/var]# vmstat procs memory swap io system cpu r b w swpd free buff cache si so bibo incs us sy id 2 1 0 4 2612 40752 1450688 0 0 4 33 6 10 2 6 [root@host /usr/local/mysql-4.0/var]# ldd ../libexec/mysqld librt.so.1 = /lib/librt.so.1 (0x2aac8000) libdl.so.2 = /lib/libdl.so.2 (0x2aacc000) libpthread.so.0 = /lib/libpthread.so.0 (0x2aad) libz.so.1 = /usr/lib/libz.so.1 (0x2aae3000) libcrypt.so.1 = /lib/libcrypt.so.1 (0x2aaf3000) libnsl.so.1 = /lib/libnsl.so.1 (0x2ab2) libstdc++-libc6.1-1.so.2 = /usr/lib/libstdc++-libc6.1-1.so.2 (0x2ab36000) libm.so.6 = /lib/libm.so.6 (0x2ab78000) libc.so.6 = /lib/libc.so.6 (0x2ab95000) /lib/ld-linux.so.2 = /lib/ld-linux.so.2 (0x2aaab000) [root@host /usr/local/mysql-4.0/var]# myisamchk -a (on all tables) set-variable= key_buffer=32M Time to start getting results: 127 seconds. Total Time: 146 seconds (18251 rows, 125 rows/sec overall) Run the query again: Time to start getting results: 11 seconds. Total Time: 30 seconds (18251 rows, 608 rows/sec) shutdown mysql set-variable = key_buffer=1M start mysql myisamchk -a (on all tables) Time to start getting results: 121 seconds. Total Time: 141 seconds (18251 rows, 129 rows/sec) Second Run: Run the query again: Time to start getting results: 10 seconds. Total Time: 29 seconds (18251 rows, 629 rows/sec) Shutdown and restart MySQL. Note: key_buffer still at 1M Time to start getting results: 12 seconds. Total Time: 31 seconds (18251 rows, 588 rows/sec) Shutdown MySQL Set key buffer size to 0 start mysql Time to start getting results: 10 seconds. Total Time: 29 seconds (18251 rows, 629 rows/sec) myisamchk -a Time to start getting results: 145 seconds. Total Time: 164 seconds (18251 rows, 111 rows/sec) -- Bill Adams TriQuint Semiconductor - 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 - 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
How to Get the New Number inserted in a AutoIncrement Field
I know that this is a old question, but I really need to know how can I get the number that Mysql use in a autoincrement field of a record I just inserted ... Is there any function like LAST_INSERTED or something like that ... Thanks a lot - 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: Show query page by page
Hi, I have a query result with 50 register and i want to show them in 5 pages, every page show 10 register. Thanks , for your answers Rhony http://www.mysql.com/doc/S/E/SELECT.html See 'LIMIT' /Rob -- Robert Alexander, Alpha Geek, Workmate.ca WWW Database Applications and Web Hosting http://www.workmate.ca 416-823-6599 mailto:[EMAIL PROTECTED] Life's unfair - but root password helps! - 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: Solution for testing PHP/MS Access on Linux
Hi, - Original Message - From: mweb [EMAIL PROTECTED] some days ago I asked help on this list because I have to develop and test on Linux/Apache some PHP pages that will have to run on on an IIS/NT box. Regarding wrappers, have a look at http://php.weblogs.com/ADODB The ADODB wrapper (PHP) is fast, and has many very good features. That way you can get to Access or MySQL directly (or via ODBC if you really want to ;-) Going to MySQL through ODBC incurs a performance penalty (because of the ODBC layer) that might actually be greater than going through a good wrapper. Regards, Arjen. -- MySQL Training Worldwide, http://www.mysql.com/training/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Arjen G. Lentz [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Technical Writer /_/ /_/\_, /___/\___\_\___/ Brisbane, QLD Australia ___/ www.mysql.com - 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: Search Engines
Hello Karl, Try the 'FULLTEXT' section of the manual. I've used the fulltext index to create a search engine for a mailing list archive, and I'm quite pleased with how it works. http://www.mysql.com/doc/F/u/Fulltext_Search.html /Rob Karl J. Stubsjoen wrote: Hello, I need to create a search engine out of a few MySQL tables I should say: I need to search MySQL records like a search engin might. However, my first go ended up as a complete failure because it is highly un-optimized to search for (as an example) %apple% in all of the available text fields. Any ideas about where I can look to set up a database optimized for searching in this way? -- Robert Alexander, Alpha Geek, Workmate.ca WWW Database Applications and Web Hosting http://www.workmate.ca 416-823-6599 mailto:[EMAIL PROTECTED] Life's unfair - but root password helps! - 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: order by, group by
let say that the table (id,language,name,description) where id,language is the primary key so that 1 id can have several languages the data in the database looks like this 1'en''blue circle''this is a blue ' 1'no''bla cirkel' 'dette er ei bla cirkel' 2'en''green leaf' 'this is a green leaf from a tree' 2'sv''grönt löv' 'detta är ett grönt löv från ett träd' if the language priority is en,sv,no the selectwould return the following 1'en''blue circle''this is a blue ' 2'en''green leaf' 'this is a green leaf from a tree' if however the language prority was sv,no,en the select should return the following 1'no''bla cirkel' 'dette er ei bla cirkel' 2'sv''grönt löv' 'detta är ett grönt löv från ett träd' =and if the requested language priority was sv, de, it would only: 2'sv''grönt löv' 'detta är ett grönt löv från ett träd' be returned? =and if the requested language priority was fr, sp, it would nothing be returned? =dn - 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: How to Get the New Number inserted in a AutoIncrement Field
http://www.mysql.com/doc/G/e/Getting_unique_ID.html HTH, /Rob I know that this is a old question, but I really need to know how can I get the number that Mysql use in a autoincrement field of a record I just inserted ... Is there any function like LAST_INSERTED or something like that ... Thanks a lot - 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
