Aw: Re: Retrieve most recent of multiple rows
not all the rows, only the distinct q_id, The subquery will give all distinct q_ids, but the select from in(subquery) will give everything, because each row must have a q_id which is returned by the subquery. The query after all says select all rows where the q_id is one of the q_ids occuring in the table and this, of course, gives everything. Also your select qid, max(atimestamp) is not doing what I think you want to do. I think you want all raws where atimestamp is somehow a maximum. Now in your query max(atimesamp) will return one single value and this will be used as a sort of constant. So if max(atimestamp) is e. g. 5, you get effictively select qid, 5 from ... Qou will get all rows with the constant becoming a row. You want to filter rows, so your condition must be in where (this is a basic concept of sql). Somehow (this is not correct sql, just a hint) it must be like select qid from kkk where atimestamp=max(atimestamp). If you want to filter the condition must be in where, a function on a column behins select will not filter. I think you need to get a basic understanding of sql first, sorry if that sounds harsh Stefan On Wed, Mar 13, 2013 at 8:28 PM, Johan De Meersman vegiv...@tuxera.bewrote: -- *From: *Ananda Kumar anan...@gmail.com *Subject: *Re: Retrieve most recent of multiple rows select qid,max(atimestamp) from kkk where qid in (select distinct qid from kkk) group by qid; What use is that where statement? It just says to use all the rows in the table. -- Unhappiness is discouraged and will be corrected with kitten pictures. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Aw: SELECT subquery problem
You cannot do this. A sql result alwas has the same number of columns in each row. You could have null or in the column, though. This could be done via the if(,,)-statement of mysql or by using a union and two selects, one for pub_email=n and the other for the rest. Gesendet: Dienstag, 05. Februar 2013 um 15:49 Uhr Von: cl c...@nimbleeye.com An: mysql@lists.mysql.com Betreff: SELECT subquery problem De-lurking here. I am trying to figure out how to return results from a query. What I need to do is to return 4 columns from a database. This is easy: SELECT last_name, first_name, phone, email FROM `mydatabasetable` WHERE `current_member` = Y AND `pub_name` = Y ORDER BY last_name ASC This works fine, as expected. But, I want to only display the value in `email` if the value in another field, `pub_email` = Y So, the resultant output would look like this, for instance, if the value of `pub_email` =N for Mr. Wills: Jones John 555-555- johnjo...@nowhere.com Smith Jim 555-222- jimsm...@nothing.com Wills Chill 555-111- Zorro Felicity 555-999- felicityzo...@madeup.com Can't quite figure out how to express this. TIA for your suggestions! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql[http://lists.mysql.com/mysql] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: UDF behaves non-deterministic
Hi everybody, it looks like the reason for the problem was me not handling string arguments properly (I did not use the provided lengths, but relied on string being null-terminated, it's in the doc, but ...). It seems this became a problem specifically in the parallel situation, misleading me into believing it had to do with this. Thanks to everybody for help! Even though the solution was not directly provided, the comments made me think about my code and so were still helpfull. Stefan On Monday 05 November 2012 15:08:51 Michael Dykman wrote: C is not an inherently thread-safe language. Several of the standard library functions use static data, which gets stepped on during concurrent operation. Many of those do have thread-safe equivalents on many platforms such as strtok/strtok_r (the latter being the safe one). If you are confident you are not using statics or globals in your code directly, you will need to identify each function you do call. Start by reading the man page for that function (if it's in the C stdlib, there is a man page for it) which should tell you if it is safe or not; for those which are not, the man page will likely suggest a threadsafe alternative if one is available. If none are available, you might have to consider a mutex. - michael dykman On Mon, Nov 5, 2012 at 9:28 AM, Stefan Kuhn stef...@web.de wrote: Hi Dan, thanks for your answer. The UDF only contains functions (the one called in sql plus two functions called in it). There are no variables outside them and nothing is declared static. All variables inside the functions are declared just like double x=0; etc. I am not an expert on C, but my understanding is that these values are separate for each call of the function and don't influence each other. Do you have a suggestion what I should look for in my c code? Or do I need to make the code thread-safe in that sense that concurrent executions are prevented by monitors or semaphors or so (no idea about what this is called in c)? Stefan The first thing I would do is examine your UDF and ensure that it is thread-safe. No global variables, no static variables within functions, etc. Also make sure that any libc functions you call that are documented as non-threadsafe are wrapped by a mutex or otherwise protected against multiple simultaneous access. http://dev.mysql.com/doc/refman/5.5/en/adding-udf.html As for debugging, you should be able to write things to stderr which will show up in the mysql logfile, or you could open your own logfile and write to that. -- Dan Nelson dnel...@allantgroup.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Aw: Re: UDF behaves non-deterministic
Hi Dan, thanks for your answer. The UDF only contains functions (the one called in sql plus two functions called in it). There are no variables outside them and nothing is declared static. All variables inside the functions are declared just like double x=0; etc. I am not an expert on C, but my understanding is that these values are separate for each call of the function and don't influence each other. Do you have a suggestion what I should look for in my c code? Or do I need to make the code thread-safe in that sense that concurrent executions are prevented by monitors or semaphors or so (no idea about what this is called in c)? Stefan The first thing I would do is examine your UDF and ensure that it is thread-safe. No global variables, no static variables within functions, etc. Also make sure that any libc functions you call that are documented as non-threadsafe are wrapped by a mutex or otherwise protected against multiple simultaneous access. http://dev.mysql.com/doc/refman/5.5/en/adding-udf.html As for debugging, you should be able to write things to stderr which will show up in the mysql logfile, or you could open your own logfile and write to that. -- Dan Nelson dnel...@allantgroup.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: UDF behaves non-deterministic
On Monday 05 November 2012 18:02:28 h...@tbbs.net wrote: 2012/11/04 22:23 +, Stefan Kuhn select * from table order by udf(column, 'input_value') desc; For my understanding, this should give the same result always. But if for your data function udf returns the same for more arguments there is not enough to fix the order. In that case I have found that other accidental things affect the order, things that one would not suspect: howmuch store is used and needed for the ordering, ... a further reason for showing what the function returns. If the order varies, although the function returns the same in all cases, well, True, but I am missing records from the top ten which should definitly be in, so this should not be the problem here. I am investigating things further... Stefan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
UDF behaves non-deterministic
Hi all, I have a weired (for me at least) problem with a user defined function, written in C. The function seems to return different results in different runs (the code of the function does not contain random elements). Basically, the function calculates a score based on a column in a table and an input value. So I do something like this: select * from table order by udf(column, 'input_value') desc; For my understanding, this should give the same result always. But if I run many statements (execution is from a java program and I can do it in parallel threads) so that they overlap (the udf on a large table takes 5-10 s on a slow machine), the results of some queries are different. If I have enough time between statements, it seems to work, i. e. the result is always the same. I would have thought the statements are independent, even if executed on different jdbc connections in parallel. Does somebody have an idea? Or could somebody give an idea on debugging? Normally I would try to debug the code to see what goes on, but how can I do this in a udf? Can I log in the udf? Thanks for any hints, Stefan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: UDF behaves non-deterministic
On Sunday 04 November 2012 22:34:22 Michael Dykman wrote: A couple of questions present. You mention that selecting from the whole table takes 5-10s so I assume you have a lot of records. Yes, and the calculation of the score is fairly complicated. Plust the test server is slow (Pentium III machine, old, but working) is the data not in flux? are you sure? Yes, I am. I have a test server, where nothing happens. these conflict queries are all on the same server? Yes, one mysql instance on one server i would have structured the query like so: select *, udf(column,'value') AS u from table order by u; I tried this and whilst it gives a speedup (around 25%, I would say), it does not solve the problem (but thanks for the hint, I didn't think this makes a difference). I suspect it might reduce the number of udf invocations.. the order by clause is frequently referred to in the process of sorting.. keeping that static instead of dynamic might sanitize your issue. On 2012-11-04 4:24 PM, Stefan Kuhn stef...@web.de wrote: Hi all, I have a weired (for me at least) problem with a user defined function, written in C. The function seems to return different results in different runs (the code of the function does not contain random elements). Basically, the function calculates a score based on a column in a table and an input value. So I do something like this: select * from table order by udf(column, 'input_value') desc; For my understanding, this should give the same result always. But if I run many statements (execution is from a java program and I can do it in parallel threads) so that they overlap (the udf on a large table takes 5-10 s on a slow machine), the results of some queries are different. If I have enough time between statements, it seems to work, i. e. the result is always the same. I would have thought the statements are independent, even if executed on different jdbc connections in parallel. Does somebody have an idea? Or could somebody give an idea on debugging? Normally I would try to debug the code to see what goes on, but how can I do this in a udf? Can I log in the udf? Thanks for any hints, Stefan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: get a partial dump
Ok, but I can't trace foreign key references, can I? Plus I can't run this from Java (well, I can, but it's cumbersome)? But thanks for the hint Stefan On Wednesday 24 October 2012 05:33:22 Eric Bergen wrote: mysqldump has a --where argument that allows you to pass in a where clause. On Thursday, October 18, 2012, Stefan Kuhn wrote: Hi everybody, I want to get insert statements from a mysql database for a number of rows of a table, specified by a where clause. I also need the inserts for the datasets linked via foreign keys in other tables. So I need a sort of partial dump, a bit like mysqldump, but restricted to a (small) set of data. This needs to be done in a Java program, using mysql via jdbc. Does anybody know an easy way to do this? Of course I could build the statements in the java code, but I thought mysql might offer that or at least parts of it. Does anybody have an idea? Thanks, Stefan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
get a partial dump
Hi everybody, I want to get insert statements from a mysql database for a number of rows of a table, specified by a where clause. I also need the inserts for the datasets linked via foreign keys in other tables. So I need a sort of partial dump, a bit like mysqldump, but restricted to a (small) set of data. This needs to be done in a Java program, using mysql via jdbc. Does anybody know an easy way to do this? Of course I could build the statements in the java code, but I thought mysql might offer that or at least parts of it. Does anybody have an idea? Thanks, Stefan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Simple Query Question
On Saturday 14 April 2012 09:51:11 Willy Mularto wrote: Hi, Please help what is wrong with this simple query SELECT COUNT(key_agent) total FROM agents_consolidated WHERE total = 180 Thanks. You need to use having instead of where, see the documentation. Stefan Willy Mularto F300HD+MR18DE (NLC1725) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Putting two queries in one
Hi all, I have to create a view. For this, a table needs to transformed like this: So the original table is 1 a b 2 c d I want 1 a 1 b 2 c 2 d I can do two queries giving me this, but how do I combine them in a view? If a new table would need to be created, I could do a select into - but in a view? It would need to be done in one query, if I understood it right. Anybody an idea? Thanks, Stefan -- Stefan Kuhn BSc MA IPB Halle AG Bioinformatik Massenspektrometrie Weinberg 3 06120 Halle http://www.ipb-halle.de http://msbi.bic-gh.de [EMAIL PROTECTED] +49 (0) 345 5582 1474 Fax.+49 (0) 345 5582 1409 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Putting two queries in one
Thanks! I didn't remember UNION - that does the trick. On Monday 16 July 2007 15:49, Rolando Edwards wrote: drop table if exists test.flipdata; drop view if exists test.RotatedData; create table test.flipdata (f1 int,f2 char(1),f3 char(1)); insert into test.flipdata values (1,'a','b'),(2,'c','d'); create view test.RotatedData as select f1,f2 from test.flipdata union select f1,f3 from test.flipdata order by 1; select * from test.RotatedData; These are the results I got running MySQL 5.0.45 in Windows: mysql drop table if exists test.flipdata; Query OK, 0 rows affected (0.00 sec) mysql drop view if exists test.RotatedData; Query OK, 0 rows affected (0.00 sec) mysql create table test.flipdata (f1 int,f2 char(1),f3 char(1)); Query OK, 0 rows affected (0.06 sec) mysql insert into test.flipdata values (1,'a','b'),(2,'c','d'); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql create view test.RotatedData as select f1,f2 from test.flipdata union select f1,f3 from test.flipdata order by 1; Query OK, 0 rows affected (0.00 sec) mysql select * from test.RotatedData; +--+--+ | f1 | f2 | +--+--+ |1 | a| |1 | b| |2 | c| |2 | d| +--+--+ 4 rows in set (0.00 sec) - Original Message - From: Rolando Edwards [EMAIL PROTECTED] To: Stefan Kuhn [EMAIL PROTECTED] Cc: MySQL mysql@lists.mysql.com Sent: Monday, July 16, 2007 9:44:48 AM (GMT-0500) America/New_York Subject: Re: Putting two queries in one drop table if exists test.flipdata; create table test.flipdata (f1 int,f2 char(1),f3 char(1)); insert into test.flipdata values (1,'a','b'),(2,'c','d'); create view test.RotatedData as select f1,f2 from test.flipdata union select f1,f3 from test.flipdata order by 1; select * from test.RotatedData; - Original Message - From: Stefan Kuhn [EMAIL PROTECTED] To: MySQL mysql@lists.mysql.com Sent: Monday, July 16, 2007 8:58:58 AM (GMT-0500) America/New_York Subject: Putting two queries in one Hi all, I have to create a view. For this, a table needs to transformed like this: So the original table is 1 a b 2 c d I want 1 a 1 b 2 c 2 d I can do two queries giving me this, but how do I combine them in a view? If a new table would need to be created, I could do a select into - but in a view? It would need to be done in one query, if I understood it right. Anybody an idea? Thanks, Stefan -- Stefan Kuhn BSc MA IPB Halle AG Bioinformatik Massenspektrometrie Weinberg 3 06120 Halle http://www.ipb-halle.de http://msbi.bic-gh.de [EMAIL PROTECTED] +49 (0) 345 5582 1474 Fax.+49 (0) 345 5582 1409 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Stefan Kuhn BSc MA IPB Halle AG Bioinformatik Massenspektrometrie Weinberg 3 06120 Halle http://www.ipb-halle.de http://msbi.bic-gh.de [EMAIL PROTECTED] +49 (0) 345 5582 1474 Fax.+49 (0) 345 5582 1409 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Find records not in many-to-many table?
Obviously you can do a join when the names of id columns are different as well. Look in the doc for that. This has nothing to do with your problem of finding rows not in another table - it is a basic sql thing. Stefan Am Sunday 26 March 2006 00:47 schrieb barney: Thanks, Stefan, But that only works if both tables have the same field name, doesn't it? If I use select FileKey from dl_files left join dl_merges using (FileID) where FileID is null MySQL returns Unknown column 'articles.dl_files.FileID' in 'on clause'. Correct me if I'm wrong, but wouldn't I have to modify the column name in one of the tables in order for that to work? Or is there a syntax in the join lexicon that I can use to alias one of the column names within the query? I can't modify the existing table structure(s). Apologies if I'm being dense. Make a good day ... ... barn Use select first_table.id from first_table left join second_table using (id) where second_table.id is null Stefan Am Saturday 25 March 2006 19:10 schrieb barney: Folk, This may be off-base for this list, but I've run out of places to look, sorry. I can't seem to find this anywhere, although I'm certain I've seen it before. How can I identify all the records in a table that are not referenced in a many-to-many table? I have a [unique] table of files and a [unique] table of attributes. These are linked in a merge table which is many-to-many. I need to find all items in the file table that are not referenced in the merge table in order to add appropriate attributes for those records. The attribute list is 26-30 records and the file table is currently about 3,200 records, which could make for a merge table of 96,000 records. I tried using an IN statement against a sub-select of unique file ids in the merge table, but either that will not work or I did not craft it properly The query hit 6 million records before I aborted it sigh /. I'm certain this can be done ... I seem to remember a similar process from the DB2 corporate days ... but I just cannot wrap my head around it. Anybody have any ideas, please? Make a good day ... ... barn -- Stefan Kuhn M. A. Cologne University BioInformatics Center (http://www.cubic.uni-koeln.de) Z�lpicher Str. 47, 50674 Cologne Tel: +49(0)221-470-7428 Fax: +49 (0) 221-470-7786 My public PGP key is available at http://pgp.mit.edu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Stefan Kuhn M. A. Cologne University BioInformatics Center (http://www.cubic.uni-koeln.de) Zülpicher Str. 47, 50674 Cologne Tel: +49(0)221-470-7428 Fax: +49 (0) 221-470-7786 My public PGP key is available at http://pgp.mit.edu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Find records not in many-to-many table?
Use select first_table.id from first_table left join second_table using (id) where second_table.id is null Stefan Am Saturday 25 March 2006 19:10 schrieb barney: Folk, This may be off-base for this list, but I've run out of places to look, sorry. I can't seem to find this anywhere, although I'm certain I've seen it before. How can I identify all the records in a table that are not referenced in a many-to-many table? I have a [unique] table of files and a [unique] table of attributes. These are linked in a merge table which is many-to-many. I need to find all items in the file table that are not referenced in the merge table in order to add appropriate attributes for those records. The attribute list is 26-30 records and the file table is currently about 3,200 records, which could make for a merge table of 96,000 records. I tried using an IN statement against a sub-select of unique file ids in the merge table, but either that will not work or I did not craft it properly The query hit 6 million records before I aborted it sigh /. I'm certain this can be done ... I seem to remember a similar process from the DB2 corporate days ... but I just cannot wrap my head around it. Anybody have any ideas, please? Make a good day ... ... barn -- Stefan Kuhn M. A. Cologne University BioInformatics Center (http://www.cubic.uni-koeln.de) Zülpicher Str. 47, 50674 Cologne Tel: +49(0)221-470-7428 Fax: +49 (0) 221-470-7786 My public PGP key is available at http://pgp.mit.edu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Circular Replication
Am Thursday 27 October 2005 12:56 schrieb Raphaël 'SurcouF' Bordet: Le vendredi 16 septembre 2005 à 18:14 +0200, Stefan Kuhn a écrit : I'm using it with four machines (geographically separate) and it works fine. Stefan And can writes on each server in simultaneous time ? I don't understand the question. Replication is pretty fast, but is not guaranteed to happen in a certain time (not real-time). Stefan -- Raphaël 'SurcouF' Bordet http://debianfr.net/ | surcouf at debianfr dot net -- Stefan Kuhn M. A. Cologne University BioInformatics Center (http://www.cubic.uni-koeln.de) Zülpicher Str. 47, 50674 Cologne Tel: +49(0)221-470-7428 Fax: +49 (0) 221-470-7786 My public PGP key is available at http://pgp.mit.edu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help with fulltext search and left join
For me, it looks as if you confused the order of join and where. It should be:
select ... from ... join ... where ... order by ...
Stefan
Am Sunday 23 October 2005 13:21 schrieb Grant Giddens:
Hi,
I keep getting errors on this query and I'm not sure
why. I'm using mysql version 4.0.22.
Any ideas?
SELECT pn_coupons_store.store_name,
pn_coupons_store.store_name_short,
pn_coupons_coupons.store_id,
pn_coupons_coupons.coupon_id,
pn_coupons_dealtype.dealtype_name,
pn_coupons_coupons.coupon_desc,
pn_coupons_coupons.coupon_link,
pn_coupons_coupons.use_preurl,
pn_coupons_coupons.coupon_tracking,
pn_coupons_coupons.coupon_banner,
pn_coupons_coupons.coupon_drm,
pn_coupons_coupons.coupon_code,
pn_coupons_coupons.coupon_legal_text,
pn_coupons_coupons.coupon_submitter,
pn_coupons_coupons.coupon_added,
pn_coupons_coupons.coupon_start,
pn_coupons_coupons.coupon_exp,
pn_coupons_coupons.coupon_hits,
pn_coupons_network.network_id, MATCH (coupon_desc)
AGAINST ('electronics' IN BOOLEAN MODE) AS score FROM
pn_coupons_coupons WHERE MATCH (store_name) AGAINST
('electronics' IN BOOLEAN MODE), LEFT JOIN
pn_coupons_store ON (pn_coupons_store.store_id =
pn_coupons_coupons.store_id) LEFT JOIN
pn_coupons_dealtype ON
(pn_coupons_dealtype.dealtype_id =
pn_coupons_coupons.dealtype_id) LEFT JOIN
pn_coupons_network ON (pn_coupons_network.network_id =
pn_coupons_store.store_network_id) ORDER BY score DESC
Thanks,
Grant
__
Yahoo! FareChase: Search multiple travel sites in one click.
http://farechase.yahoo.com
--
Stefan Kuhn M. A.
Cologne University BioInformatics Center (http://www.cubic.uni-koeln.de)
Zülpicher Str. 47, 50674 Cologne
Tel: +49(0)221-470-7428 Fax: +49 (0) 221-470-7786
My public PGP key is available at http://pgp.mit.edu
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Circular Replication
I'm using it with four machines (geographically separate) and it works fine. Stefan Am Friday 16 September 2005 17:31 schrieb Jeff: Does anyone use circular replication with MySQL 4.x? For instance: A to B B to A I know it's possible as long as you're carefull with your client applications and the way they write/update the db. Just wondering if anyone has had success or problems with this type of situation. Thanks, Jeff -- Stefan Kuhn M. A. Cologne University BioInformatics Center (http://www.cubic.uni-koeln.de) Zülpicher Str. 47, 50674 Cologne Tel: +49(0)221-470-7428 Fax: +49 (0) 221-470-7786 My public PGP key is available at http://pgp.mit.edu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
EOFException with jdbc driver
Hi all, I have a Java webapp using a mysql database. It all used to run well, but I now frequently get the following error: 2005-09-15 11:33:32,115 [Thread-23] ERROR rotation - com.mysql.jdbc.CommunicationsException: Communications link failure due to underlying exception: ** BEGIN NESTED EXCEPTION ** java.io.EOFException STACKTRACE: java.io.EOFException at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:1842) at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2288) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2784) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1531) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1622) at com.mysql.jdbc.Connection.execSQL(Connection.java:2370) at com.mysql.jdbc.Connection.execSQL(Connection.java:2297) at com.mysql.jdbc.Statement.executeQuery(Statement.java:1183) I cannot really say when this started, but I did not get the impression that I changed anything in my software. It also seems that this is not restricted to certain queries, but is accidental. My configuration is Suse Linux 9.1, MySQL-Max-4.1.5-0 (the binary rpm from mysql.com), mysql-connector-java-3.1.7, Java 1.4.2. I did not find anything about how to resolve this and also not a real explanation what the error means. Any ideas? Thanks, Stefan -- Stefan Kuhn M. A. Cologne University BioInformatics Center (http://www.cubic.uni-koeln.de) Zülpicher Str. 47, 50674 Cologne Tel: +49(0)221-470-7428 Fax: +49 (0) 221-470-7786 My public PGP key is available at http://pgp.mit.edu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: EOFException with jdbc driver
Hi Mark, thanks for your answer. See comments. Stefan, Something is closing the network connection abrubtly between JDBC and your MySQL server. Things to check are: 1) Connections don't sit longer than 'wait_timeout' on the server. I am actually using connection pooling (the torque system, in case you know this). After I get this error, my app is still running fine, and also doing dtabase queries. Something interesting: When I do a query ... where x like %y% the error occurs. Doing where x regexp y works fine, even if I do the regular expression query after the like one, without restarting server or similar things. 2) If the mysql server is on a different machine than your application: Same machine 2a) There are no network hardware issues 2b) There isn't a firewall between the server and your application that times-out connections 3) MySQL isn't crashing in between queries (see your mysql server error log) Nothing in the log -Mark -- Mark Matthews MySQL AB, Software Development Manager - Connectivity www.mysql.com -- Stefan Kuhn M. A. Cologne University BioInformatics Center (http://www.cubic.uni-koeln.de) Zülpicher Str. 47, 50674 Cologne Tel: +49(0)221-470-7428 Fax: +49 (0) 221-470-7786 My public PGP key is available at http://pgp.mit.edu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: free MySQL conversion to MSSQL tool
Am Thursday 28 July 2005 11:19 schrieb [EMAIL PROTECTED]: Does anyone know a free tool to convert MySQL to MSSQL mysqldump? Seriously, what do you expect? A tool which transforms mysql files on disk to mssql files? I don't think this exists and would probably be hardly possible. Perhaps there are tools reading from a (mysql) db and writing to another (mssql) database via a programming language. But conversion of database files? I don't think this exists. Stefan Thank you Andrew -- Stefan Kuhn M. A. Cologne University BioInformatics Center (http://www.cubic.uni-koeln.de) Zülpicher Str. 47, 50674 Cologne Tel: +49(0)221-470-7428 Fax: +49 (0) 221-470-7786 My public PGP key is available at http://pgp.mit.edu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Primary and Foreign Keys (Follow Up)
If your FK really is unique, you don't need two tables. Example First table Second Table ID FK 1 1 2 2 3 3 ... So you can make this one table. On other words, it would be a one-to-one relation. And this would be one table. Only with a one-to-many relation two tables make sense, but then your FK can't be unique. Stefan Am Wednesday 15 June 2005 12:41 schrieb Asad Habib: As a follow up to my question, I did want to mention that the foreign key I am using is unique. - Asad -- Stefan Kuhn M. A. Cologne University BioInformatics Center (http://www.cubic.uni-koeln.de) Zülpicher Str. 47, 50674 Cologne Tel: +49(0)221-470-7428 Fax: +49 (0) 221-470-7786 My public PGP key is available at http://pgp.mit.edu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ERROR 1111 (HY000): Invalid use of group function
I would guess it's because you can't use an aggregate function in where, but only in having. So use select group by PRACT_NUMBER having COUNT(SUBSTRING(PRACT_NUMBER, 1, 4)) 1. The reason is that where is applied before the count is done, whereas having after that. And you can't select by something which you will know after you have done the select. Stefan Am Wednesday 11 May 2005 20:36 schrieb shaun thornburgh: Hi, I keep getting thw following error when i include this line: AND COUNT(SUBSTRING(PRACT_NUMBER, 1, 4)) 1 mysql SELECT PRACT_NUMBER, - COUNT(SUBSTRING(PRACT_NUMBER, 1, 4)) - FROM CSV_Upload_Data - WHERE CSV_File = 'ICS' - AND CHAR_LENGTH(PRACT_NUMBER) 4 - AND COUNT(SUBSTRING(PRACT_NUMBER, 1, 4)) 1 - AND Booking_ID = 6030 - GROUP BY PRACT_NUMBER; ERROR (HY000): Invalid use of group function mysql Can anyone tell me why this is happening? Thanks for your advice -- Stefan Kuhn M. A. Cologne University BioInformatics Center (http://www.cubic.uni-koeln.de) Zlpicher Str. 47, 50674 Cologne Tel: +49(0)221-470-7428 Fax: +49 (0) 221-470-7786 My public PGP key is available at http://pgp.mit.edu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LIKE question - is it possible?
Not sure if your query works. But what should work is SELECT * FROM table WHERE c LIKE %word1% and c LIKE %word2% and c LIKE %word3%; But might be slow ... Am Thursday 14 April 2005 13:53 schrieb Micha Berdichevsky: Hi group. I have a table with a varchar(250) column in it (let's call it c) I want to select values that contain a number of given words in them (three or more), in any words order I currently use SELECT * FROM table WHERE c LIKE %word1%word2%word3%; I was wandering if it is possible to use a query where the LIKE (or anything else) searches for my given strings in any order. I'm using MySQL 4.1.11 on windows XP, if it matters. Thanks. Micha. -- Stefan Kuhn M. A. Cologne University BioInformatics Center (http://www.cubic.uni-koeln.de) Zülpicher Str. 47, 50674 Cologne Tel: +49(0)221-470-7428 Fax: +49 (0) 221-470-7786 My public PGP key is available at http://pgp.mit.edu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Setting up a Chemical database
I'm afraid we can't tell you anything with virtually no information given by you. What is a product? Which are the informations you want to hold about a product? How do they relate to each other? What is the purpose of the database? Which sort of informations are people supposed to get out of it? If you answer these questions for yourself, the design should become visible. If any doubts, ask, but please be specific. Stefan P.S: If interested, the ER diagram of a chemical database can be found here: http://cvs.sourceforge.net/cgi-bin/viewcvs.cgi/*checkout*/nmrshiftdb/nmrshiftdb/doc/ER-Diagram_for_NMRShiftDB.ps?rev=HEADcontent-type=aplication/ps But chemical is a wide field ... Am Monday 04 April 2005 23:47 schrieb Dave Shariff Yadallee - System Administrator a.k.a. The Root of the Problem: I have a customer who has sent my close to 300 chemical products in word format? How do I translate this into mysql tables? -- Stefan Kuhn M. A. Cologne University BioInformatics Center (http://www.cubic.uni-koeln.de) Zülpicher Str. 47, 50674 Cologne Tel: +49(0)221-470-7428 Fax: +49 (0) 221-470-7786 My public PGP key is available at http://pgp.mit.edu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Queries taking 60 seconds+
It is a property of Mysql that such a query will benefit greatly from a composite index. So I would not consider anything else without having tried this. Am Thursday 11 November 2004 16:29 schrieb John Smith: On Thu, 2004-11-11 at 14:59, Victor Pendleton wrote: If you build the composit indexes as suggested, does your performance improve? Erm, do you think it would? Its just that with such a large table and it being compressed it takes ages? -- Stefan Kuhn M. A. Cologne University BioInformatics Center (http://www.cubic.uni-koeln.de) Zlpicher Str. 47, 50674 Cologne Tel: +49(0)221-470-7428 Fax: +49 (0) 221-470-7786 My public PGP key is available at http://pgp.mit.edu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysqldump very slow
You won't get any reasonable answers like this. How big is your db? How long does mysqldump actually take? If you tell this, people can tell you if this is like expected or too slow. Good answers depend on good questions. But a hint: You can't expect mysqldump to run in fractions of seconds. Depending on database size, it is quite normal for it to take some minutes. Stefan Am Thursday 28 October 2004 13:48 schrieb Anil Doppalapudi: mysqldump running very slow. what might be the reason Server details === Dell RAM : 4 GB Innodb_buffer_pool_size : 1000MB i am using --quick option in mysqldump Thanks Anil DBA -- Stefan Kuhn M. A. Cologne University BioInformatics Center (http://www.cubic.uni-koeln.de) Zülpicher Str. 47, 50674 Cologne Tel: +49(0)221-470-7428 Fax: +49 (0) 221-470-7786 My public PGP key is available at http://pgp.mit.edu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: great problem with questions
= klientmiejscowosci.klientid AND klientmiejscowosci.klientmiejscowoscid= klientmiejscowosc.klientmiejscowoscid AND branzaslowo.branzaslowo like'sam%' AND klient.klientid = klientbranza.klientid AND klientbranza.branzaid=branzaslowa.branzaid AND branzaslowa.branzaslowoid= branzaslowo.branzaslowoid AND (klientslowo.klientslowo LIKE 'sam%') AND klient.klientid = klientslowa.klientid AND klientslowa.klientslowoid= klientslowo.klientslowoid can anyone help me with this? with best regards Darek -- Stefan Kuhn M. A. Cologne University BioInformatics Center (http://www.cubic.uni-koeln.de) Zlpicher Str. 47, 50674 Cologne Tel: +49(0)221-470-7428 Fax: +49 (0) 221-470-7786 My public PGP key is available at http://pgp.mit.edu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: great problem with questions
Am Thursday 23 September 2004 13:22 schrieb Stefan Kuhn:
This is probably due to having indexed only single columns. MySQL uses only
one index per column, meaning if you have a where with two conditions you
Sorry, this should read one index per table...
need a combined index for the columns used. If there are only indeces for
the single columns, only one index for one condition will be used, the rest
requires a full table scan (this is explained somewhere in the docs, have a
look).
Stefan
Am Thursday 23 September 2004 11:50 schrieb DeRyl:
hello,
I have database with 30 tables [some have over 2000k some other over
4000k rec]
when I want to run a sql with a few conditions the answer is dramatically
slow [over 70 seconds!]
sql-s with one condition usually works well..
how is the corrcet way to optimize the database and sql questions?
the answer from sql should be under 1 second...
SHOW VARIABLES gives these informations:
Variable_name Value
back_log 50
basedir /usr/local/mysql/
bdb_cache_size 8388600
bdb_log_buffer_size 32768
bdb_home /dysk/mysql/data/
bdb_max_lock 1
bdb_logdir
bdb_shared_data OFF
bdb_tmpdir /tmp/
bdb_version Sleepycat Software: Berkeley DB 3.2.9a: (May 14, 2...
binlog_cache_size 32768
bulk_insert_buffer_size 8388608
character_set latin2
character_sets latin1 big5 czech euc_kr gb2312 gbk latin1_de sjis...
concurrent_insert ON
connect_timeout 5
convert_character_set
datadir /dysk/mysql/data/
default_week_format 0
delay_key_write ON
delayed_insert_limit 100
delayed_insert_timeout 300
delayed_queue_size 1000
flush OFF
flush_time 0
ft_boolean_syntax + -()~*:|
ft_min_word_len 4
ft_max_word_len 254
ft_max_word_len_for_sort 20
ft_stopword_file (built-in)
have_bdb YES
have_crypt YES
have_innodb YES
have_isam YES
have_raid YES
have_symlink YES
have_openssl NO
have_query_cache YES
init_file
innodb_additional_mem_pool_size 1048576
innodb_buffer_pool_size 8388608
innodb_data_file_path ibdata1:10M:autoextend
innodb_data_home_dir
innodb_file_io_threads 4
innodb_force_recovery 0
innodb_thread_concurrency 8
innodb_flush_log_at_trx_commit 1
innodb_fast_shutdown ON
innodb_flush_method
innodb_lock_wait_timeout 50
innodb_log_arch_dir ./
innodb_log_archive OFF
innodb_log_buffer_size 1048576
innodb_log_file_size 5242880
innodb_log_files_in_group 2
innodb_log_group_home_dir ./
innodb_mirrored_log_groups 1
innodb_max_dirty_pages_pct 90
interactive_timeout 28800
join_buffer_size 131072
key_buffer_size 8388600
language /usr/local/mysql/share/mysql/polish/
large_files_support ON
license GPL
local_infile ON
locked_in_memory OFF
log OFF
log_update OFF
log_bin OFF
log_slave_updates OFF
log_slow_queries OFF
log_warnings ON
long_query_time 10
low_priority_updates OFF
lower_case_file_system OFF
lower_case_table_names 0
max_allowed_packet 1048576
max_binlog_cache_size 4294967295
max_binlog_size 1073741824
max_connections 100
max_connect_errors 10
max_delayed_threads 20
max_insert_delayed_threads 20
max_heap_table_size 16777216
max_join_size 18446744073709551615
max_relay_log_size 0
max_seeks_for_key 4294967295
max_sort_length 1024
max_user_connections 0
max_tmp_tables 32
max_write_lock_count 4294967295
myisam_max_extra_sort_file_size 268435456
myisam_max_sort_file_size 2147483647
myisam_repair_threads 1
myisam_recover_options OFF
myisam_sort_buffer_size 8388608
net_buffer_length 16384
net_read_timeout 30
net_retry_count 10
net_write_timeout 60
new OFF
open_files_limit 1024
pid_file /dysk/mysql/data/mysqld.pid
log_error
port 3306
protocol_version 10
query_alloc_block_size 8192
query_cache_limit 1048576
query_cache_size 0
query_cache_type ON
query_prealloc_size 8192
range_alloc_block_size 2048
read_buffer_size 131072
read_only OFF
read_rnd_buffer_size 262144
rpl_recovery_rank 0
server_id 0
slave_net_timeout 3600
skip_external_locking ON
skip_networking OFF
skip_show_database OFF
slow_launch_time 2
socket /tmp/mysql.sock
sort_buffer_size 2097144
sql_mode 0
table_cache 64
table_type MYISAM
thread_cache_size 0
thread_stack 196608
tx_isolation REPEATABLE-READ
timezone CEST
tmp_table_size 33554432
tmpdir /tmp/
transaction_alloc_block_size 8192
transaction_prealloc_size 4096
version 4.0.20-max
version_comment Official MySQL-max binary
version_compile_os pc-linux
wait_timeout 28800
a good example of question is:
select distinct logo, klient.klientid, klientnazwa, struktura,
concat(kodpocztowy,' ',miejscowosc) miasto, aparatnumer,
concat(ulicaskrot,' ',ulicanazwa,' ',posesja) ulica,concat('woj.
',wojewodztwo,' powiat: ',powiat) wojpow, klientbranza branza, email,
www, wizytowka
from klient, klientulice, klientulica, klientmiejscowosci,
klientmiejscowosc, branzaslowa, branzaslowo, klientbranza
Re: JOIN/WHERE and index confusion
I did not follow the discussion, but I would say you need to have a combined index on a.timestamp, a.a_id and a.c_id. Sorry if you already tried this. Stefan Am Friday 30 July 2004 21:34 schrieb Eamon Daly: So, to confirm, short of indexing a.timestamp (which I've done) there's no way to speed up the original query? SELECT * FROM a LEFT JOIN b ON a.a_id = b.a_id JOIN c ON a.c_id = c.c_id JOIN d ON c.d_id = d.d_id JOIN e ON c.e_id = e.e_id WHERE a.timestamp BETWEEN 2004010100 AND 20040101235959 GROUP BY c.d_id, c.e_id This report takes over an hour when looking at just one month's worth of data. It's brutal. Any and all suggestions would be appreciated. Eamon Daly - Original Message - From: gerald_clark [EMAIL PROTECTED] To: Eamon Daly [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Thursday, July 29, 2004 10:54 AM Subject: Re: JOIN/WHERE and index confusion No. a.a_id is used in the join to b, not for selecting records in a. Eamon Daly wrote: I would assume it would use reporting_id_t, since the WHERE clause has both a.a_id and a.timestamp in it. Eamon Daly - Original Message - From: gerald_clark [EMAIL PROTECTED] To: Eamon Daly [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Thursday, July 29, 2004 8:04 AM Subject: Re: JOIN/WHERE and index confusion Why should it use any other? Timestamp is the only field in the where clause, and you are selecting all fields. The second index would require more index reads, and the third can't be used to satisfy the where clause. Eamon Daly wrote: Okay, now I'm even /more/ confused. I whittled everything down like so: CREATE INDEX reporting_t ON a (timestamp); CREATE INDEX reporting_t_id ON a (timestamp, a_id); CREATE INDEX reporting_id_t ON a (a_id, timestamp); EXPLAIN SELECT * FROM a, b WHERE a.a_id = b.a_id AND a.timestamp BETWEEN 2004010100 AND 20040101235959 and it /still/ only uses reporting_t! What the heck am I missing? Eamon Daly - Original Message - From: Eamon Daly [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, July 28, 2004 10:58 AM Subject: JOIN/WHERE and index confusion Hi, all. I think I'm misunderstanding something basic about indexes. I have a SELECT like so: SELECT * FROM a LEFT JOIN b ON a.a_id = b.a_id JOIN c ON a.c_id = c.c_id JOIN d ON c.d_id = d.d_id JOIN e ON c.e_id = e.e_id WHERE a.timestamp BETWEEN 2004010100 AND 20040101235959 GROUP BY c.d_id, c.e_id All of the id fields are primary indexes. I've already created an index on a.timestamp, and that works all right. I tried creating an index on a for the SELECT: KEY `reporting` (`a_id`,`c_id`,`timestamp`) and an index on c for the GROUP BY: KEY `reporting` (`c_id`,`d_id`,`e_id`) But EXPLAIN shows that MySQL isn't even considering the key on a, and chooses the primary key on c over my index. Clearly I'm confused about how indexes are used in a JOIN/WHERE situation: can anyone enlighten me? Eamon Daly -- Stefan Kuhn M. A. Cologne University BioInformatics Center (http://www.cubic.uni-koeln.de) Zülpicher Str. 47, 50674 Cologne Tel: +49(0)221-470-7428 Fax: +49 (0) 221-470-7786 My public PGP key is available at http://pgp.mit.edu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: newbie join issue
I would expect that the speed problems are due to missing indices. Did you do proper indexing? If unsure, post your table structures and query. Stefan Am Tuesday 20 July 2004 17:45 schrieb Edward Ritter: I've got a task that's gonna require me to compare one table to another and remove the rows from the first table that are found in the second table that match email_address. I'm running 4.0.20a-nt-log. The first table has 10 colomns and about 50K records, and the second table has 46 columns and has about 16K records. I've attempted a number of selects that just sat and hung the computer. I know I must be doing something wrong. I figure I'll need to do a left join on it, but I've not had much experience with joins as such and I need a little assistance. Can anyone help me work this out? If you need more info, let me know. Ed -- Stefan Kuhn M. A. Cologne University BioInformatics Center (http://www.cubic.uni-koeln.de) Zülpicher Str. 47, 50674 Cologne Tel: +49(0)221-470-7428 Fax: +49 (0) 221-470-7786 My public PGP key is available at http://pgp.mit.edu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sql Query Issue
This is an index problem. Your tables don't contain any indices except on PKs. This can't work, given the number of joins and table sizes. Read the doc about indices. Stefan Am Monday 12 July 2004 09:55 schrieb Jeyabalan Murugesan Sankarasubramanian: Hi All, I migrated the data from Oracle to MySQL.The following query works fine with Oracle whereas in MySql its hanging. SELECT distinct caty.name, caty.c_id, caty.notes, count(distinct segs.in_id) as num FROM segs, caty, st_mbers, t_mbers, p_mbrs, pr_mbers where segs.c_id = caty.c_id and caty.c_id=st_mbers.c_id and st_mbers.st_id = t_mbers.st_id and t_mbers.t_id = p_mbers.t_id and p_mbers.p_id = pr_mbers.p_id and pr_mbers.p_id = 1 group by st_caty.c_id, st_caty.name, st_caty.notes order by st_caty.name Following are the table structures with Row count in each table. CREATE TABLE segs( s_id INT(12) NOT NULL AUTO_INCREMENT PRIMARY KEY, c_id INT(12) NULL , text VARCHAR(255) NULL, lookup VARCHAR(255) NULL, in_id INT(12) NULL, prr_d VARCHAR(12) NULL, nxt_d VARCHAR(12) NULL, descn VARCHAR(255) NULL, notes VARCHAR(255) NULL, s_st_idINT(12) NULL, versn FLOAT(10,4) NULL, mesg_type VARCHAR(50) NULL ); Row count 34700 CREATE TABLE caty( c_id INT(12) NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255) NULL, notes VARCHAR(255) NULL, versionFLOAT(10,4) ); Row count 281 CREATE TABLE st_mbers( st_id INT(12) NULL, c_id INT(12) NULL, version FLOAT(10,4) NULL, st_mber_id INT(12) NOT NULL ); Row count 1362 CREATE TABLE t_mbers( t_id INT(12) NOT NULL, st_id INT(12) NULL, seq_nbrINT(12) NULL, versionFLOAT(10,4) NULL, t_mber_id INT(12) NOT NULL ); Row count 1260 CREATE TABLE p_mbers ( p_id INT(12) NOT NULL , t_id INT(12) NULL , seq_nbr INT(12) NULL, notes TEXT NULL, version FLOAT(10,4) NULL, p_mber_id INT(12) NOT NULL ); Row Count 2198 CREATE TABLE pr_mbers( pr_id INT(12) NULL, p_id INT(12) NULL, seq_nbrINT(12) NULL, pr_mbr_id INT(12) NOT NULL ); Row Count 294 Help me in solving this hanging issue. I tried the same with both Linux and Windows XP but without any success. I tried with one record in each table mentioned above and its working fine. Kindly guide me in this regard. Thanks in advance. regards msjeyabalan ** CONFIDENTIAL INFORMATION ** This e-mail transmission and any attachments may contain confidential information belonging to the sender. The information is intended solely for the use of the individual(s) or entities addressed. If you are not the intended recipient, you are hereby notified that any copying, disclosing, distributing, or use of this e-mail and/or attachment is strictly prohibited. If you received this transmission in error please notify the sender immediately and delete the message and all attachments. -- Stefan Kuhn M. A. Cologne University BioInformatics Center (http://www.cubic.uni-koeln.de) Zlpicher Str. 47, 50674 Cologne Tel: +49(0)221-470-7428 Fax: +49 (0) 221-470-7786 My public PGP key is available at http://pgp.mit.edu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Upgrade 4.1.1 to 4.1.2
Am Tuesday 01 June 2004 16:54 schrieb Larry Lowry: I'm running 4.1.1 on RH 9. I want to upgrade this to 4.1.2. I'm using the rpm from the MySQL site. If I just try to install it with rpm -i then I get a bunch of errors about conflicting files from the 4.1.1-1 install. If I try to uninstall first with rpm -e the version Probably you are running rpm -e Mysql-4.1.1.1 ? You only need rpm -e Mysql. Version number is not needed for uninstall (since there can be only one version anyway). 4.1.1 then it tells me that it is not installed. I seem to always have this problem with rpm packages. What is the best way to get this installed? TIA Larry Lowry -- Stefan Kuhn M. A. Cologne University BioInformatics Center (http://www.cubic.uni-koeln.de) Zülpicher Str. 47, 50674 Cologne Tel: +49(0)221-470-7428 Fax: +49 (0) 221-470-7786 My public PGP key is available at http://pgp.mit.edu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fwd: Re: Another Performance query
-- Weitergeleitete Nachricht -- Subject: Re: Another Performance query Date: Mon, 22 Mar 2004 16:13:29 +0100 From: Stefan Kuhn [EMAIL PROTECTED] To: A Z [EMAIL PROTECTED] Indices can never be used with like %x (but with like x%). This question won't use indeces, neither in myisam nor in innodb. If you think about the principles behind indices, you will see why indices can't work with like starting with a joker. Stefan Am Monday 22 March 2004 15:39 schrieb A Z: here we go: explain select * from properties where reference like '%2332' +-+ Table ¦ type ¦ possible keys ¦ key ¦ key_len ¦ ref ¦ rows ¦ Extra +-+ Properties ¦ All ¦ NULL ¦ NULL ¦ NULL ¦ NULL ¦ NULL ¦ 2923 ¦ Using Where describe properties; +-+ Field ¦ Type ¦ Null ¦ Key ¦ Default ¦ Extra +-+ Reference ¦ VarChar(7) ¦ ¦ PRI ¦ ¦ show index from properties; +-+ Table ¦ Non_unique ¦ Key_name ¦ Seq_in_index ¦ Column_name ¦ Collation ¦ Cardinality ¦ Sub_part ¦ Pakced ¦ Null ¦ Index_type ¦ Comment +-+ properties ¦ 0 ¦ PRIMARY ¦ 1 ¦ Reference ¦ A ¦ 2923 ¦ NULL ¦ NULL ¦ ¦ BTREE regards --- Benoit St-Jean [EMAIL PROTECTED] wrote: A Z wrote: Thanks for your replies regarding to my previous query. We have encountered another problem: MySQL 4.0.14, INNODB. A table does have an Index on Field1, this field (Field1) is also the Primary Key. Querying on this field takes a long time, running along with Explain command it displays that it does not use the index. Prior converting to INNODB it had been working fine in MYISAM. What could cause this problem? Can you provide us with the EXPLAIN of the query, a DESCRIBE TABLE and a SHOW INDEX to help us pinpoint what the problem is? ___ Yahoo! Messenger - Communicate instantly...Ping your friends today! Download Messenger Now http://uk.messenger.yahoo.com/download/index.html -- Stefan Kuhn M. A. Cologne University BioInformatics Center (http://www.cubic.uni-koeln.de) Zülpicher Str. 47, 50674 Cologne Tel: +49(0)221-470-7428 Fax: +49 (0) 221-470-7786 My public PGP key is available at http://pgp.mit.edu --- -- Stefan Kuhn M. A. Cologne University BioInformatics Center (http://www.cubic.uni-koeln.de) Zülpicher Str. 47, 50674 Cologne Tel: +49(0)221-470-7428 Fax: +49 (0) 221-470-7786 My public PGP key is available at http://pgp.mit.edu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Horizontal list...
Bonjour, you simply cannot do this. In SQL, every column has one value, everything else is not normalized, and normalization is required in relational dbms. If you want to have such a list, you would need to do it in you application, given it's a (PHP,Java etc) application accessing the database. Stefan Am Wednesday 17 March 2004 17:09 schrieb Richard Carlier: Bonjour, Horizontal is not the word but I don't know how to explain... and by extension how to find in archive of list... Maybe an example ? I have this +---+---+ | a | b | +---+---+ | 1 | 1 | | 1 | 2 | | 1 | 3 | | 2 | 1 | | 2 | 2 | | 3 | 1 | +---+---+ Their is any why to obtain something like that : +---++ | a | b | +---++ | 1 | 1,2,3 | | 2 | 1,2| | 3 | 1 | +---++ Based on a group by or something... A + -- Stefan Kuhn M. A. Cologne University BioInformatics Center (http://www.cubic.uni-koeln.de) Zülpicher Str. 47, 50674 Cologne Tel: +49(0)221-470-7428 Fax: +49 (0) 221-470-7786 My public PGP key is available at http://pgp.mit.edu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication broken after upgrade from 4 to 4.1
Just for the records: The reason was a misconfiguration in the firewall. The admin had changed the setting in the very moment I upgraded from 4.0 to 4.1 ... that lead me to the assumption broken replication had to do with the upgrade. Took me a while till I found it's firewall problem. Thanks, Stefan Am Friday 05 March 2004 19:23 schrieb Sasha Pachev: Stefan Kuhn wrote: Hi everybody, I have three mysql servers doing a circular replication. They (still) run Redhat 7.3. I started with Mysql 3.23, upgraded to 4 and yesterday I tried 4.1. I'm using official mysql rpms. Now the replication is broken. One server always says Connecting to master. The error log says: 040304 19:11:58 Slave I/O thread: error connecting to master '[EMAIL PROTECTED] 47.62:3306': Error: 'Lost connection to MySQL server during query' errno: 2013 retry-time: 60 retries: 86400 The server which is supposed to replicate fromt this server says: Waiting for master to send event. The log says: 040304 16:47:59 Got fatal error 1236: 'Could not find first log file name in binary log index file' from master when reading data from binary log The third replication finally seems to work. I did not change anything in configuration, passwords etc. It's all the same. Something to note: The two servers able to replicate are in the same network, the one which makes trouble is in another, firewall protected network. Port 3306 is open in both firewalls and it always worked. Does anybody have any tips? Thanks a lot (I'm really desparate) Stefan For simplicity, make sure all servers have the same version and start all together in sync with fresh logs. Newer versions might be confused by the old logs from the old versions. -- Stefan Kuhn M. A. Cologne University BioInformatics Center (http://www.cubic.uni-koeln.de) Zülpicher Str. 47, 50674 Cologne Tel: +49(0)221-470-7428 Fax: +49 (0) 221-470-7786 My public PGP key is available at http://pgp.mit.edu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Replication broken after upgrade from 4 to 4.1
Hi everybody, I have three mysql servers doing a circular replication. They (still) run Redhat 7.3. I started with Mysql 3.23, upgraded to 4 and yesterday I tried 4.1. I'm using official mysql rpms. Now the replication is broken. One server always says Connecting to master. The error log says: 040304 19:11:58 Slave I/O thread: error connecting to master '[EMAIL PROTECTED] 47.62:3306': Error: 'Lost connection to MySQL server during query' errno: 2013 retry-time: 60 retries: 86400 The server which is supposed to replicate fromt this server says: Waiting for master to send event. The log says: 040304 16:47:59 Got fatal error 1236: 'Could not find first log file name in binary log index file' from master when reading data from binary log The third replication finally seems to work. I did not change anything in configuration, passwords etc. It's all the same. Something to note: The two servers able to replicate are in the same network, the one which makes trouble is in another, firewall protected network. Port 3306 is open in both firewalls and it always worked. Does anybody have any tips? Thanks a lot (I'm really desparate) Stefan -- Stefan Kuhn M. A. Cologne University BioInformatics Center (http://www.cubic.uni-koeln.de) Zlpicher Str. 47, 50674 Cologne Tel: +49(0)221-470-7428 Fax: +49 (0) 221-470-7786 My public PGP key is available at http://pgp.mit.edu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: auto sorting
Am Sunday 25 January 2004 23:17 schrieb Lorderon: Hello All, I got a table with a VARCHAR(255) field as a primary key, but when I insert a new row it inserts it ordered by the key. Then, when I select rows without doing any order, it returns the rows ordered by the primary key. That's by accident. If you don't use order by, there is no guaranteed order and it might well change from select to select. How can I insert a new row to the end of the table, and select records will not use auto sort, but without doing an ORDER BY clause or making an auto_increment primary key? You can't get a defined order without order by. I do actually not understand your problem. Why not use order by? Why not use auto increment? In order to retrieve results in the order you inserted them (that's what you want, right?) you need two things: First, a column which records the insert order (auto increment, timestamp ...) and, secondly, an order by in your select. No chance without these. thanks in advance, -Lorderon -- Stefan Kuhn M. A. Cologne University BioInformatics Center (http://www.cubic.uni-koeln.de) Zlpicher Str. 47, 50674 Cologne Tel: +49(0)221-470-7428 Fax: +49 (0) 221-470-7786 My public PGP key is available at http://pgp.mit.edu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with 'OR' statement
Am Friday 09 January 2004 17:22 schrieb Hunter, Jess: Could someone have a look at this syntax and give me some guidance what I may be overlooking? SELECT * from $TableName WHERE machinename != 'FIND_ME' OR machinename != 'OPEN' A query like where x=a or s=b will always return all values. Why? Because the value is always differen from at least a or b. So such a query is nonsense. You want all records unequal to a and b, i. e. everything except a and b? Use and. Stefan I can make the statement work individually, but when I try to add the 'OR' statement it fails to 'remove' the designated records from the display page. I have tried moving the 'FIND_ME' and 'OPEN' around and still get the same results. Any help would be appreciated Jess --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.558 / Virus Database: 350 - Release Date: 1/2/04 -- Stefan Kuhn M. A. Cologne University BioInformatics Center (http://www.cubic.uni-koeln.de) Zlpicher Str. 47, 50674 Cologne Tel: +49(0)221-470-7428 Fax: +49 (0) 221-470-7786 My public PGP key is available at http://pgp.mit.edu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with 'OR' statement
Am Friday 09 January 2004 17:22 schrieb Hunter, Jess: Could someone have a look at this syntax and give me some guidance what I may be overlooking? SELECT * from $TableName WHERE machinename != 'FIND_ME' OR machinename != 'OPEN' Some other thing: Instead of saying where x!=a and x!=b you could also say where !(x==a or x==b). That's known as de Morgan's law. (sometimes the math course at university pays off, surprise!) Stefan I can make the statement work individually, but when I try to add the 'OR' statement it fails to 'remove' the designated records from the display page. I have tried moving the 'FIND_ME' and 'OPEN' around and still get the same results. Any help would be appreciated Jess --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.558 / Virus Database: 350 - Release Date: 1/2/04 -- Stefan Kuhn M. A. Cologne University BioInformatics Center (http://www.cubic.uni-koeln.de) Zlpicher Str. 47, 50674 Cologne Tel: +49(0)221-470-7428 Fax: +49 (0) 221-470-7786 My public PGP key is available at http://pgp.mit.edu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with 'OR' statement
Am Friday 09 January 2004 17:57 schrieb Stefan Kuhn: Am Friday 09 January 2004 17:22 schrieb Hunter, Jess: Could someone have a look at this syntax and give me some guidance what I may be overlooking? SELECT * from $TableName WHERE machinename != 'FIND_ME' OR machinename != 'OPEN' A query like where x=a or s=b will always return all values. Why? Because Sorry, should read where x!=a or s!=b as in your mail. Stefan the value is always differen from at least a or b. So such a query is nonsense. You want all records unequal to a and b, i. e. everything except a and b? Use and. Stefan I can make the statement work individually, but when I try to add the 'OR' statement it fails to 'remove' the designated records from the display page. I have tried moving the 'FIND_ME' and 'OPEN' around and still get the same results. Any help would be appreciated Jess --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.558 / Virus Database: 350 - Release Date: 1/2/04 -- Stefan Kuhn M. A. Cologne University BioInformatics Center (http://www.cubic.uni-koeln.de) Zlpicher Str. 47, 50674 Cologne Tel: +49(0)221-470-7428 Fax: +49 (0) 221-470-7786 My public PGP key is available at http://pgp.mit.edu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to Uninstall Mysql
/mysqlbug script! The latest information about MySQL is available on the web at http://www.mysql.com Support MySQL by buying support/licenses at https://order.mysql.com [EMAIL PROTECTED] root]# mysql ERROR 2002: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) [EMAIL PROTECTED] root]# mysqld 031219 22:41:58 Can't start server: Bind on TCP/IP port: Address already in use 031219 22:41:58 Do you already have another mysqld server running on port: 3306 ? 031219 22:41:58 Aborting 031219 22:41:58 mysqld: Shutdown Complete I tried to stop running mysql as follows: [EMAIL PROTECTED] root]# /var/lib/mysql stop ERROR 2002: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) I tried to restart as follows ; [EMAIL PROTECTED] mysql]# mysql restart ERROR 2002: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) It's confusing for me. Please guide me step by step to use mysql in this case. I tried to go through documentation but bit difficult to understand for newbe like me. Check if MySQL server is running: ps ax | grep mysqld If so, find where mysql.sock is located. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do you Yahoo!? New Yahoo! Photos - easier uploading and sharing. http://photos.yahoo.com/ -- Stefan Kuhn M. A. Cologne University BioInformatics Center (http://www.cubic.uni-koeln.de) Zülpicher Str. 47, 50674 Cologne Tel: +49(0)221-470-7428 Fax: +49 (0) 221-470-7786 My public PGP key is available at http://pgp.mit.edu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication
Am Wednesday 03 December 2003 18:21 schrieb rubn ruvalcaba: You guys are right, as Cuck said I need each machine acts as server and slave, I did it with two servers in a circular replication and works pretty fine, but when add a third machine, there comes the problems, because the central server must be the master (no problem) and the slave of two servers (here is the problem). If you want to do a circular replication, it needs to be like this: A is slave of B B is slave of C C is slave of A Important: You need to set log-slave-updates in my.cnf, so that B logs what it gets from C and A gets it then from B. I have such a configuration in production use and all works fine. Stefan P. S: Note your two server configuration is just a special case of this. From: Lloyd Kvam [EMAIL PROTECTED] To: Chuck Gadd [EMAIL PROTECTED] CC: rubn ruvalcaba [EMAIL PROTECTED], [EMAIL PROTECTED] Subject: Re: Replication Date: Wed, 03 Dec 2003 09:59:29 -0500 You can setup a circular replication stream. Make sure that the replicated data gets written to the output binlogs. From the manual: `log-slave-updates' Tells the slave to log the updates from the slave thread to the binary log. Off by default. You will need to turn it on if you plan to daisy-chain the slaves. Chuck Gadd wrote: rubn ruvalcaba wrote: I want to know how could solve the next replication scenario: I have a master. I have 5 slaves. At start the slaves has a master snapshot. Now imagine slave 1, inserts a record. When it gets connected to the lan, it must replicate it's changes to the master. No, a slave receives changes that occur at the master. That's why it's a slave. I suspect you want each machine to be a Master and a slave. -- Lloyd Kvam Venix Corp. 1 Court Street, Suite 378 Lebanon, NH 03766-1358 voice: 603-653-8139 fax: 801-459-9582 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] _ MSN. Más Útil Cada Día http://www.msn.es/intmap/ -- Stefan Kuhn M. A. Cologne University BioInformatics Center (http://www.cubic.uni-koeln.de) Zülpicher Str. 47, 50674 Cologne Tel: +49(0)221-470-7428 Fax: +49 (0) 221-470-7786 My public PGP key is available at http://pgp.mit.edu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Security Question
Well, I'm not an expert on security, but I don't think this is a database issue. It is really a file/operating system issue. I don't think you can do anything in the database against copying the files. If somebody has access on file system level, the dbms is powerless. So I think you need to think about the OS. Stefan Am Wednesday 26 November 2003 14:22 schrieb Curley, Thomas: thanks for reply - the requirement comes from a security audit - so try to think in terms of a hacker Obviously and (I had assumed) 1.- the files would have tight unix security file permissions applied 2.- indeed the key would be stored on an internal tightly managed box (or device) Another Assumption -- Encrypting / decrypting all data on the fly would be too expensive and grind the app to a halt So the question again :- Any ideas on how to avoid having data files stored with absolutely no protection against copying If there is no solution to this then MySql should not be used on internet accessible boxes for dynamic web sites Thomas -Original Message- From: Fagyal, Csongor [mailto:[EMAIL PROTECTED] Sent: 26 November 2003 12:51 To: Curley, Thomas Cc: [EMAIL PROTECTED] Subject: Re: Security Question Thomas, I am trying to find a solution to the following security issue with MySql DB on linux - Someone copies the DB files to another box, starts a mysql instance, loads the DB and presto - views the 'private' data !!! Well, someone should not have access rights to the DB files on the first hand. Ideally I would like to know if there is any option in MySql to store the DB files in a secure format and one that needs a key or similiar to open the DB If someone was able to access your DB files, he would probably also be able to access that key (that you must store _somewhere_), wouldn't he? - Csongor *** ** This email and any attachments are confidential and intended for the sole use of the intended recipient(s).If you receive this email in error please notify [EMAIL PROTECTED] and delete it from your system. Any unauthorized dissemination, retransmission, or copying of this email and any attachments is prohibited. Euroconex does not accept any responsibility for any breach of confidence, which may arise from the use of email. Please note that any views or opinions presented in this email are solely those of the author and do not necessarily represent those of the Company. This message has been scanned for known computer viruses. *** ** -- Stefan Kuhn M. A. Cologne University BioInformatics Center (http://www.cubic.uni-koeln.de) Zülpicher Str. 47, 50674 Cologne Tel: +49(0)221-470-7428 Fax: +49 (0) 221-470-7786 My public PGP key is available at http://pgp.mit.edu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Security Question
To the chap who siad its not a DB issue - I will check with Oracle but I'm sure that dropping in a directory in oracle will not give you full access to a database (a clear one that is) The chap was me :-) I'm sure it does on oracle. Once you have an Oracle installation and got hold of all database files (which is easy once an intruder got root on the machine) you have access to all data. Even oracle can't do anything about this, but there might be two difficulties with oracle compared to mysql: You need the oracle software (expensive, but do hackers buy software?) and it might be that the files are spread all over the computer and hard to find. But basically, it is the same with oracle (but I never used oracle, this is common sense). Stefan -- Stefan Kuhn M. A. Cologne University BioInformatics Center (http://www.cubic.uni-koeln.de) Zülpicher Str. 47, 50674 Cologne Tel: +49(0)221-470-7428 Fax: +49 (0) 221-470-7786 My public PGP key is available at http://pgp.mit.edu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 2-way replication
Am Friday 14 November 2003 14:51 schrieb nm: I set a 2-way replication with 2 servers One is accessed and used for both select and updates. The other one is backup set to work through a failover software.. if the first server is down. So the backup server can easily update the other server when it comes up again. Do you see any collision possibilities? Only one server is really used. I have been using 2-way replication on production machines for quite a while and never had any problems. In our case, all machines are used, primary key creation is done in the application and there will never be same pks used on different machines. HTH Stefan -- Stefan Kuhn M. A. Cologne University BioInformatics Center (http://www.cubic.uni-koeln.de) Zülpicher Str. 47, 50674 Cologne Tel: +49(0)221-470-7428 Fax: +49 (0) 221-470-7786 My public PGP key is available at http://pgp.mit.edu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: insert ... select .. order by, problem
Hi, well, I'm not totally sure about your question. Which order is reversed ? The order you get the entries with a select after the insert ? If it is this, then I think it's not a problem with the insert. The order is then given by the select, and if no order by is in the select, it is arbitrary. Since the physical order of entries in your temp table isn't of any relevance, I can't see any point in using order by in this statement. Stefan Am Tuesday 02 September 2003 10:06 schrieb Alejandro Paz: Hallo everyone ! I already sent this, but I think some people think is not clear enough ;-) Im using Mysql 4.0.12 on RedHat 7.3 x86 I know it's not the last binary but I cannot upgrade now. (And i saw nothing about this in the changelog for 4.013 and 4.0.14) I found the following : I have two tables : Stock (InnoDB, primary key on d): a char (16) b char (20) c char (20) d int e decimal (9,2) h int i int PTemp (MyISAM, no keys): d int e decimal f int g char (1) And the statement I am using is : INSERT INTO PTemp SELECT d,e,32,'E' FROM Stock WHERE hi ORDER BY a,b,c; I am doing an insert/select with order by, in both cases I am using the same statemant. When I use the same statement in my application (built with C, and statically linked to libmysqlclient.a) I get the reversed order (the records that start with '0' are at the end). When I test the statement in the mysql cli and I get the results well sorted (the records that start with '0' are at the begining). I checked the log and both statements are equal, (but the two users used to access the DB are different, the mysql cli user is root, and the other just have enough permissions to select, update,delete and insert in the tables). I would like to know (if that is possible) what happens. Thanks in advance. Ale __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Stefan Kuhn M. A. Cologne University BioInformatics Center (http://www.cubic.uni-koeln.de) Zülpicher Str. 47, 50674 Cologne Tel: +49(0)221-470-7428 Fax: +49 (0) 221-470-7786 My public PGP key is available at http://pgp.mit.edu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: insert ... select .. order by, problem
Hi, I think you can't do this. There is no order in the table, so there is no point in using order by with insert. You always have to do this when retrieving the records (the order you get with select without order by is accidential). HTH Stefan Am Tuesday 02 September 2003 11:49 schrieb Alejandro Paz: Hi Stephan, Let's see the case : I use ORDER BY, because I want that order in PTemp table, so I do not have to order them later (because they are retrieved several times later). 1. Inserting with mysql c.l.i. : I get the records well sorted : first by a, secondly by b and finally by c (ascendig order). I'm using d to relink both tables in a join. 2. Inserting with the application : I get the records well sorted : first by a, secondly by b and finally by c, but in descending order. As all records has the same value in a, so the records that in case 1 start with '0' are at the beginning, the same records here are at the end. I'm using d to relink both tables in a join. I thought that was clear when I said reverse order, sorry. So you see what I mean ? thanks ! __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Stefan Kuhn M. A. Cologne University BioInformatics Center (http://www.cubic.uni-koeln.de) Zülpicher Str. 47, 50674 Cologne Tel: +49(0)221-470-7428 Fax: +49 (0) 221-470-7786 My public PGP key is available at http://pgp.mit.edu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: insert ... select .. order by, problem
Hi Albert, you are not misunderstanding me :-) Tables can indeed not be sorted, it's output which gets sorted. The difference is not academic, but important: It's not the table which gets an order, but the output. Take a command like: insert into x ... select from y ... order by z. Here the output of select gets sorted and inserted into the table x. In this table, there is not order, so if you do then a select from x, the order is arbitrary again and you need to do select from x order by z. And this means you could have dropped the order by in insert totally. Hope it became clear. Stefan Am Tuesday 02 September 2003 13:20 schrieb Albert: Stefan, Do you imply that tables cannot be sorted desc or asc based on one of the columns e.g. a last name? or am I misunderstanding you. Albert Atlanta (anyone else in Atlanta?) - Original Message - From: Stefan Kuhn [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, September 02, 2003 6:57 AM Subject: Re: insert ... select .. order by, problem Hi, I think you can't do this. There is no order in the table, so there is no point in using order by with insert. You always have to do this when retrieving the records (the order you get with select without order by is accidential). HTH Stefan Am Tuesday 02 September 2003 11:49 schrieb Alejandro Paz: Hi Stephan, Let's see the case : I use ORDER BY, because I want that order in PTemp table, so I do not have to order them later (because they are retrieved several times later). 1. Inserting with mysql c.l.i. : I get the records well sorted : first by a, secondly by b and finally by c (ascendig order). I'm using d to relink both tables in a join. 2. Inserting with the application : I get the records well sorted : first by a, secondly by b and finally by c, but in descending order. As all records has the same value in a, so the records that in case 1 start with '0' are at the beginning, the same records here are at the end. I'm using d to relink both tables in a join. I thought that was clear when I said reverse order, sorry. So you see what I mean ? thanks ! __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Stefan Kuhn M. A. Cologne University BioInformatics Center (http://www.cubic.uni-koeln.de) Zülpicher Str. 47, 50674 Cologne Tel: +49(0)221-470-7428 Fax: +49 (0) 221-470-7786 My public PGP key is available at http://pgp.mit.edu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Stefan Kuhn M. A. Cologne University BioInformatics Center (http://www.cubic.uni-koeln.de) Zülpicher Str. 47, 50674 Cologne Tel: +49(0)221-470-7428 Fax: +49 (0) 221-470-7786 My public PGP key is available at http://pgp.mit.edu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Replication with multiple servers in Mysql ?
Hi everybody, I want to establish replication between multiple (currently three) servers. Updates/inserts/deletes may happen on all of them. The idea I came up with was to do a circle-replication. The problem with this is, that if one server fails the rest may not get updates (until the server is back, obviously). Does anybody have an idea how to establish such a replication in a fail-save manner ? Thanks, Stefan -- Stefan Kuhn M. A. Cologne University BioInformatics Center (http://www.cubic.uni-koeln.de) Zlpicher Str. 47, 50674 Cologne Tel: +49(0)221-470-7428 Fax: +49 (0) 221-470-7786 My public PGP key is available at http://pgp.mit.edu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Doing a count of a count in mysql
Hi everybody, I do a query like select count(*) from SPECTRUM where SPECTRUM.REVIEW_FLAG =true group by MOLECULE_ID which gives back a lot of figures, mainly 1 and 2, perhaps 3. What I actually want is the count how many 1s and 2s (and 3s ...) are in this result set. What would be the best way to do ? I found 3 possible solutions, all not working or not good :-) -Subqueries, probably best, but not possible in MySQL currently. -Doing a loop over the first result set in my Java code. Possible, but slow. -I tried a nested count query like: select count(count(*)) from SPECTRUM where SPECTRUM.REVIEW_FLAG =true group by count(*),MOLECULE_ID; but this seems not possible. Anybody any idea ? Thanks a lot Stefan -- Stefan Kuhn M. A. Cologne University BioInformatics Center (http://www.cubic.uni-koeln.de) Zlpicher Str. 47, 50674 Cologne Tel: +49(0)221-470-7428 Fax: +49 (0) 221-470-5092 My public PGP key is available at http://pgp.mit.edu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Long text with index in InnoDB
Hi everybody, I'm thinking about switching to innodb, but face the following problem: I've got a column with texts longer than 256 chars and I need an index on it (for having acceptable times for a query). So i can't use varchar and indexes are not possible in InnoDB in text/blob, so I can't use this either. Is there a solution, i.e. is there a data type for text longer 256 chars and with indexing in InnoDB ? Thanks for your answers Stefan -- Stefan Kuhn M. A. MPI of Chemical Ecology, Winzerlaer Str. 10, Beutenberg Campus, 07745 Jena, Germany Tel: +49(0)3641 571261 - Fax: +49(0)3641 571202 --- -- Stefan Kuhn M. A. MPI of Chemical Ecology, Winzerlaer Str. 10, Beutenberg Campus, 07745 Jena, Germany Tel: +49(0)3641 571261 - Fax: +49(0)3641 571202 - 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: Re: Re: Re: UNIQUE on blob
Hi everybody, my humble excuse to all I bothered with that ... mysql type mediumblob is working perfectly, but there's another column in the tabel of type varchar containing mostly the same values. So that's it Stefan P. S: I do the select first because I need the ID anyway. Hi! First of all, MEDIUMBLOB type is case-sensitive. I checked this sequence and everything worked as expected. I think you have a mistake in your table. What does SHOW CREATE TABLE table give? Second, there is no needs to check that the value is already presents using SELECT just to avoid an attempt of double key value inserting. It is safe to insert without selecting. After inserting you have just to check that really happened, either record was inserted or error happened. Why do you want to do iy - 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
UNIQUE on blob
Hi everybody, I've got a MEDIUMBLOB column in a table, where I want values to be unique. So I made it UNIQUE. When my program wants to enter a value, it first does a select * from table where column =x in order to see if x already exists, if not, it does an insert. Now the interesting point: If there is c1c1 in the column and I want to insert C1C1, the select says, that this is not existing, so the insert is done. Then mySQL complains about double keys. It seems that the UNIQUE comparision is not done case-sensitive in BLOB-columns, which is bad. Does anybody have an idea about how to solve this (I need the column to be case-sensitive). Many thanks Stefan -- Stefan Kuhn M. A. MPI of Chemical Ecology, Winzerlaer Str. 10, Beutenberg Campus, 07745 Jena, Germany Tel: +49(0)3641 571261 - Fax: +49(0)3641 571202 - 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
