Re: DateTime Select optimised
Select * from TABLE where date _2005-01-07_ and date DATE_ADD(_2005-01-07_, INTERVAL 24 HOUR) However is there a simpler way of doing it by just passing one date like Select * from TABLE where date = _2005-01-07_ you may also go for ... WHERE year(foo)=a AND month(foo)=b AND dayofmonth(foo)=c; t -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fedora
Hello. You may use netstat -nlpt to find open tcp ports and the name of the program to which each socket belongs. If you see a listener on port 3306, then this is the source of the problem. i?aki [EMAIL PROTECTED] wrote: Hi, in a fedora i install mysql 4, i upgrading to mysql 4, but when i put mysqld, say can?t start server: bind on tcp/ip port, before restart computer when i put ps, appear an mysql_safe but now no, with the same error. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: myodbc on mandrake
Hello. If you can't find rpms with this driver, you may install it manually. At first you should install unixODBC or iODBC. Then install MyODBC using binary tarball. See: http://dev.mysql.com/doc/mysql/en/myodbc-unix-tarball-installation.html Put the correct information in configuration files. See: http://dev.mysql.com/doc/mysql/en/dsn-on-unix.html symbulos partners [EMAIL PROTECTED] wrote: Dear friends, I cannot locat the odbc driver for mysql in mandrake. Does anyone have an idea about the whereabouts of the thing? thanks in advance -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication problem
Hello. Error: 'Unknown MySQL server host 'server5.domain.com' (1)' errno: 2005 Does the problem remain if you remove the following line from the my.cnf on the server8: skip-networking Chris Mason [EMAIL PROTECTED] wrote: I have two servers, server5.mydomain.com and server8.mydomain.com. I = want to replicate one database on server5 to server 8. I did the whole proceedure as recommended in http://dev.mysql.com/doc/mysql/en/replication-howto.html - 6.4. How to = Set Up Replication I setup the GRANT statement on server5 for the slave. mysql SHOW GRANTS FOR [EMAIL PROTECTED]; += --- ---+ | Grants for [EMAIL PROTECTED] | += --- ---+ | GRANT RELOAD, SUPER, REPLICATION SLAVE ON *.* TO = 'server8'@'MyServer8IP' IDENTIFIED BY PASSWORD 'xxx' | += --- ---+ 1 row in set (0.00 sec) But when I stop and start the slave, I get: 050316 8:56:42 [Note] Slave SQL thread initialized, starting = replication in log 'mysql-bin.04' at position 79, relay log './server8-relay-bin.01' position: 4 050316 8:56:42 [ERROR] Slave I/O thread: error connecting to master '[EMAIL PROTECTED]:3306': Error: 'Unknown MySQL server host 'server5.domain.com' (1)' errno: 2005 retry-time: 60 retries: 86400 When I try from the command line, I get it to work without problem: [EMAIL PROTECTED] mysql]# mysql -h server5.domain.com -u server8 -p Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 30 to server version: 4.1.10-standard-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql=20 Both /etc/my.cnf files are listed below [EMAIL PROTECTED] mysql]# cat /etc/my.cnf=20 [mysqld] datadir=3D/var/lib/mysql socket=3D/var/lib/mysql/mysql.sock old_passwords skip-locking key_buffer =3D 256M max_allowed_packet =3D 1M table_cache =3D 256 sort_buffer_size =3D 1M read_buffer_size =3D 1M read_rnd_buffer_size =3D 4M myisam_sort_buffer_size =3D 64M thread_cache =3D 8 query_cache_size=3D 16M skip-networking sort_buffer=3D2M log-bin server-id=3D2 log-slave-updates=20 log-warnings=20 replicate-ignore-db=3Dmysql replicate-do-db=3Danguillaguide [isamchk] key_buffer =3D 128M sort_buffer_size =3D 128M read_buffer =3D 2M write_buffer =3D 2M [myisamchk] key_buffer =3D 128M sort_buffer_size =3D 128M read_buffer =3D 2M write_buffer =3D 2M [mysqlhotcopy] interactive-timeout =20 [mysql.server] user=3Dmysql basedir=3D/var/lib [safe_mysqld] err-log=3D/var/log/mysqld.log pid-file=3D/var/run/mysqld/mysqld.pid [EMAIL PROTECTED] mysql]# cat /etc/my.cnf=20 [mysqld] old-passwords max_connections =3D 500 key_buffer =3D 16M myisam_sort_buffer_size =3D 64M join_buffer_size =3D 2M read_buffer_size =3D 2M sort_buffer_size =3D 3M table_cache =3D 1500 thread_cache_size =3D 128 wait_timeout =3D 14400 connect_timeout =3D 10 max_allowed_packet =3D 16M max_connect_errors =3D 10 query_cache_limit =3D 1M query_cache_size =3D 32M query_cache_type =3D 1 skip-innodb log-bin=3Dmysql-bin server-id=3D1 [mysqld_safe] open_files_limit =3D 8192 [mysqldump] quick max_allowed_packet =3D 16M [myisamchk] key_buffer =3D 64M sort_buffer =3D 64M read_buffer =3D 16M write_buffer =3D 16M Chris Mason Anguilla =20 --=20 No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.7.4 - Release Date: 3/18/2005 =20 -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL and phpMyAdmin
Hello. I think you should rebuild php with the 4.1.10 libraries. Search in the archives at http://lists.mysql.com/mysql about successful solution for similar problem(s). See: http://dev.mysql.com/doc/mysql/en/old-client.html Asad Habib [EMAIL PROTECTED] wrote: I just installed phpMyAdmin, made the appropriate changes in the config.inc.php file, and got the following error when accessing it via both IE and Safari on Mac OS X Panther client. #1251 - Client does not support authentication protocol requested by server; consider upgrading MySQL client I am using MySQL 4.1.10, the latest stable release, with phpMyAdmin-2.6.1-pl3, which the documentation says are compatible. Any help would be greatly appreciated. Thanks. - Asad -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: DateTime Select optimised
In article [EMAIL PROTECTED], Pete Moran [EMAIL PROTECTED] writes: Hi All, Is there a simpler way of doing a select for a given date, for instance if I have a datetime field called date And so its populated with a load of values such as 2005-01-07 09:00 2005-01-07 10:00 2005-01-07 11:00 2005-01-07 12:00 If I wanted all records which fall on 2005-01-07 I could of course do Select * from TABLE where date 2005-01-07 and date DATE_ADD(2005-01-07, INTERVAL 24 HOUR) However is there a simpler way of doing it by just passing one date like Select * from TABLE where date = 2005-01-07 You could use WHERE cast(date AS DATE) = '2005-01-07' but this would preclude using an index. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Queries inside UDF
sguazt sguazt wrote: Hi! From: Philippe Poelvoorde [EMAIL PROTECTED] Reply-To: 'mysql@lists.mysql.com ' mysql@lists.mysql.com To: mysql@lists.mysql.com Subject: Re: Queries inside UDF Date: Thu, 17 Mar 2005 08:22:46 + .. You can actually access a DB within a UDF, but you should do the same than when you're using the C api of mysql. So you need a way to get database name, user/passwd and port without user-input, and do mysql_init, mysql_connect, mysql_query, and so on, like when you are accessing that database using a 'external' C program. Also bear in mind that it should be thread-safe ;) a way to extend udf, is ... stored procedure ? Yes I did it ... look my first post (http://lists.mysql.com/mysql/181361) There you can find and example of a C-code I used to write my UDF ... but when using the UDF inside MySQL I've got Can't connect to MySQL server on 'localhost' (111) you should check the permission. Instead compiling the C-code as stand-alone program (removing the xxx_init/xxx_deinit functions and adding a main) all is OK, the query is executed and the program normally exits. did you execute it on your DB server ? (not on your WS) I can't use stored procedure because the target DB is the 4.0.21 Currently I'm working on MySQL 4.1.10a on my devel station ... but the production station is 4.0.21 (all installed as RPM x86). So please look at the C-code and tell me if it's wrong ... as alternative you can post me a working example of UDF containing MySQL query execution. Thanks! -- Marco -- Philippe Poelvoorde COS Trading Ltd. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] _ Personalizza MSN Messenger con sfondi e fotografie! http://www.ilovemessenger.msn.it/ -- Philippe Poelvoorde COS Trading Ltd. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
A complex normalisation problem in MySQL 4.0.20 (innodb engine)
Dear friends we have a problem here. We have 3 tables - table language (id int not null auto_increment primarykey, languageName tinytext) - table country (id int not null auto_increment primarykey, defaultName tinytext, defaultLanguage tinytext) - table countryToLanguage ((languageID int, countryID int) primary key, countryNameInLanguage tinytext) in the table countryToLanguage, the column languageID is a foreign key referencing language (id) in the table countryToLanguage, the column countryID is a foreign key referencing country(id) in the table country, the column defaultLanguage is a foreign key referencing language(id) In order for the database to be perfectly normalised, we think it the column defaultName in the table country should be a foreign key referencing back countryToLanguage ((languageID int, countryID int)). What do you think? If you do that then there are lot of problems in updating the tables. -- symbulos partners -.- symbulos - ethical services for your organisation http://www.symbulos.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: A complex normalisation problem in MySQL 4.0.20 (innodb engine)
symbulos partners [EMAIL PROTECTED] wrote on 03/21/2005 08:08:53 AM: Dear friends we have a problem here. We have 3 tables - table language (id int not null auto_increment primarykey, languageName tinytext) - table country (id int not null auto_increment primarykey, defaultName tinytext, defaultLanguage tinytext) - table countryToLanguage ((languageID int, countryID int) primary key, countryNameInLanguage tinytext) in the table countryToLanguage, the column languageID is a foreign key referencing language (id) in the table countryToLanguage, the column countryID is a foreign key referencing country(id) in the table country, the column defaultLanguage is a foreign key referencing language(id) In order for the database to be perfectly normalised, we think it the column defaultName in the table country should be a foreign key referencing back countryToLanguage ((languageID int, countryID int)). What do you think? If you do that then there are lot of problems in updating the tables. -- symbulos partners -.- symbulos - ethical services for your organisation http://www.symbulos.com I don't think so. You already have the country's name in it's own language on the countryToLanguage table, don't you? What name in which language is the defaultName column supposed to represent? If you can define what the *contents* of that field is supposed to be, then you should be able to decide on which table it should reside. Normalization has a lot to do about reducing duplication. It appears that you have two fields that represent the name of a country. How many names does each country get to have in your database? What is the best way of storing the name(s) of a country that meets YOUR business needs? Answer those questions and you will solve your problem. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: DateTime Select optimised
Pintér Tibor (tibyke) wrote: Select * from TABLE where date _2005-01-07_ and date DATE_ADD(_2005-01-07_, INTERVAL 24 HOUR) However is there a simpler way of doing it by just passing one date like Select * from TABLE where date = _2005-01-07_ you may also go for ... WHERE year(foo)=a AND month(foo)=b AND dayofmonth(foo)=c; t That will work, but it will not use the index on foo, forcing a full table scan. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Converting Integer values to date type
Mahmoud Badreddine wrote:
I got it working , thank you.
I only could do what I wanted to do in multisteps however. I couldn't
figure out the nested querying , and it's bugging me.
Here's how I issued it.
mysql update tableDummy set newDate=(select
str_to_date('(concat(DayVal,.,MonthVal,.,YearVal))','%d.%m.%Y'));
That's because there is no nested querying needed here. The syntax is SET
col = newvalue. This would have worked if you'd simply left out the select.
Also, there would be no need for STR_TO_DATE() if you use CONCAT() to put
the string in the correct form in the first place.
Try
UPDATE tableDummy
SET newDate = concat(YearVal,-,MonthVal,-,DayVal)
instead.
Michael
And although I got an error, it did give all zero values for the
date(e.g -00-00)
no big deal now that I have what I want, but if someone can pick out
my error, would be greatly apreciated.
On Fri, 18 Mar 2005 13:31:14 -0600, Dan Nelson [EMAIL PROTECTED] wrote:
In the last episode (Mar 18), Mahmoud Badreddine said:
Hello,
I have a table with separate integer values for the day, month and year.
I would like to group them all under one field of type date.
I tried a few commands but I haven't captured the right syntax yet.
so if the field names are dayVal,monthVal and YearVal in talbeDummy
I am doing the following
select str_to_date(DayVal.MonthVal.YearVal,'%d.%m.%Y') from tableDummy;
CONCAT(DayVal, ., MonthVal, ., YearVal)
--
Dan Nelson
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: A complex normalisation problem in MySQL 4.0.20 (innodb engine)
I don't think so. You already have the country's name in it's own language on the countryToLanguage table, don't you? What name in which language is the defaultName column supposed to represent? If you can define what the *contents* of that field is supposed to be, then you should be able to decide on which table it should reside. Normalization has a lot to do about reducing duplication. It appears that you have two fields that represent the name of a country. How many names does each country get to have in your database? What is the best way of storing the name(s) of a country that meets YOUR business needs? You pointed our the problem. The country has different names in different languages. One of the names is the default name. So you could introduce a flag default yes/no in table countryToLanguage. Is that normalised? The answer should be yes, but you have to introduce an additional column, which increases the space hold by the table. Then if you normalise, you introduce un useful information (i.e. the information that a language is not the default language, which means filling the column with 0 when it is not the default). What do you think? Answer those questions and you will solve your problem. Shawn Green Database Administrator Unimin Corporation - Spruce Pine -- symbulos partners -.- symbulos - ethical services for your organisation http://www.symbulos.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
functions md5, crypt
Dear friends, where is the description of the functions md5 in the manual? where is the description of the function crypt()? are there are good alternatives? -- symbulos partners -.- symbulos - ethical services for your organisation http://www.symbulos.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Driver
Does anyone know anything about the MySQL (3.x) Driver that comes installed with coldfusion? No luck with yahoo search or coldfusion documentation. Hi all, I am trying to create a datasource for mysql database. I tried using the mysql (3.x) driver but i got this error: Connection verification failed for data source: DTD_Test java.sql.SQLException: Communication failure during handshake. Is there a server running on 127.0.0.1:3306? The root cause was that: java.sql.SQLException: Communication failure during handshake. Is there a server running on 127.0.0.1:3306? I then found this post http://www.easycfm.com/forums/viewmessages.cfm?Forum=32Topic=5625 and i have been doing as it says: ColdFusion MX: Configuring the MySQL JDBC driver (MySQL Connector) http://www.macromedia.com/cfusion/knowledgebase/index.cfm?event=viewid=KC.tn_19170extid=tn_19170dialogID=391691iterationID=1sessionID=4830d081ed2f$7E$3F$3stateID=0%200%20399545mode=simple I downloaded the mysql-connector-java-3.1.7.zip file from and extracted ONLY the Executable jar file mysql-connector-java-3.1.7-bin to here: C:\CFusionMX7\wwwroot\CFIDE\classes\mysql-connector-java-3.1.7\mysql-connector-java-3.1.7-bin.jar Then in the ColdFusion Administrator, I clicked on the Java and JVM page. Entered the full path to the JAR file in the Class Path field. C:\CFusionMX7\wwwroot\CFIDE\classes\mysql-connector-java-3.1.7\mysql-connector-java-3.1.7-bin.jar -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: functions md5, crypt
On Monday, March 21, 2005 09:27, symbulos partners wrote: Dear friends, where is the description of the functions md5 in the manual? where is the description of the function crypt()? are there are good alternatives? http://dev.mysql.com/doc/mysql/en/encryption-functions.html -- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: A complex normalisation problem in MySQL 4.0.20 (innodb engine)
symbulos partners [EMAIL PROTECTED] wrote on 03/21/2005 10:25:27 AM: I don't think so. You already have the country's name in it's own language on the countryToLanguage table, don't you? What name in which language is the defaultName column supposed to represent? If you can define what the *contents* of that field is supposed to be, then you should be able to decide on which table it should reside. Normalization has a lot to do about reducing duplication. It appears that you have two fields that represent the name of a country. How many names does each country get to have in your database? What is the best way of storing the name(s) of a country that meets YOUR business needs? You pointed our the problem. The country has different names in different languages. One of the names is the default name. So you could introduce a flag default yes/no in table countryToLanguage. Is that normalised? The answer should be yes, but you have to introduce an additional column, which increases the space hold by the table. Then if you normalise, you introduce un useful information (i.e. the information that a language is not the default language, which means filling the column with 0 when it is not the default). What do you think? Answer those questions and you will solve your problem. Shawn Green Database Administrator Unimin Corporation - Spruce Pine -- symbulos partners -.- symbulos - ethical services for your organisation http://www.symbulos.com I think you are on the right track. There can be several languages within a country and several countries for each language. That's why you chose the table model you have. For each language-country association, it makes sense to qualify that association with additional information. Depending on my business needs I would probably go FARTHER than just marking an association as default or not and adding the native representation of the country's name. Some countries have multiple official languages (for instance, Canada has declared both French and English as their official languages. I think Switzerland has 3 or 4 official languages but I am not completely sure). In other countries the Official language may not be the one used most commonly (I think that Spanish is the Official language of the Philippines but Tagalog is a very common (if not the most common) dialect. The official Cantonese of China may not be the most commonly used language within that country). I guess how detailed you get with the data depends on how detailed the international information needs to be. I would ask the people who are going to use this database what they need it for and how much detail your data needs to support. That way you get a sense of the granularity you need to provide. This is one of those cases where relational databases are flexible enough to provide far more information than a situation may require. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: functions md5, crypt
Tom Crimmins wrote: On Monday, March 21, 2005 09:27, symbulos partners wrote: Dear friends, where is the description of the functions md5 in the manual? where is the description of the function crypt()? are there are good alternatives? http://dev.mysql.com/doc/mysql/en/encryption-functions.html Which is the first hit if you enter md5 crypt in the manual's search box. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Wait for data to change
Dear MySQL, I have an application which need to wait for some data to change, then act on this change. I am polling the MySQL once a second using SELECT ... I believe there is an alternate method where a thread can be made to wait for some change, therefore avoiding the expensive polling and improving performance. If any member knows what this may be, I would be very interested. Regards, Ben Clewett. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: help on query/group by
Hi,
Sorry for the late reply, out for the week-end!
Here is the information you asked for (I modified the columns' type as you
suggested)
mysql show create table matches\G
*** 1. row ***
Table: matches
Create Table: CREATE TABLE `matches` (
`protID` varchar(50) default NULL,
`drugID` int(11) default NULL,
`sentID` int(11) default NULL,
KEY `sentenceId` (`sentID`),
KEY `drugID` (`drugID`),
KEY `protID` (`protID`)
) TYPE=MyISAM
1 row in set (0.00 sec)
mysql show create table sentence\G
*** 1. row ***
Table: sentence
Create Table: CREATE TABLE `sentence` (
`id` int(10) unsigned NOT NULL auto_increment,
`text` text,
`pmid` int(11) default NULL,
PRIMARY KEY (`id`)
) TYPE=MyISAM
1 row in set (0.00 sec)
mysql show create table synonyms\G
*** 1. row ***
Table: synonyms
Create Table: CREATE TABLE `synonyms` (
`nameID` varchar(50) default NULL,
`syn` text,
UNIQUE KEY `c` (`nameID`(20),`syn`(20))
) TYPE=MyISAM
1 row in set (0.01 sec)
I think I used int as much as possible,here some data samples:
INSERT INTO `matches` VALUES ('Q9UP51', 202531, 4);
INSERT INTO `matches` VALUES ('SVC2_HUMAN', 202037, 5);
INSERT INTO `matches` VALUES ('PF2R_SHEEP', 202096, 6);
INSERT INTO `matches` VALUES ('CAQS_RAT', 202037, 7);
INSERT INTO `sentence` VALUES (4, 'Of NP10 contraceptives /NPtested ,
NPspan class=\'drug\'Ortho-Gynol/span /NPwas found to be the most
rapidly acting , followed by NPCooper Creme /NP, a
href=\'http://srs.ebi.ac.uk/srsbin/cgi-bin/wgetz?[UNIPROT-acc:(Q9UP51)]+-id+008+-view+UniprotDateView\'Lactikol-
B/a\n, NPVagi-Serol /NP, NPMarvosan /NP, NPClinicol /NP,
NPJelly-X /NP, NPBor-Oxyquin /NP, NPCellikol /NP, and
NPLanteen Blue Jelly /NPin NPthat order /NP. ', 12305459);
(the text is usually longer than 255 characters, so I think text is the only
choice, except longtext which can only be worst for indexation in my
opinion)
INSERT INTO `synonyms` VALUES ('202037', 'testosterone');
INSERT INTO `synonyms` VALUES ('Q91XV1', 'gonadotropin');
INSERT INTO `synonyms` VALUES ('202037', 'estradiol');
INSERT INTO `synonyms` VALUES ('Q91XV1', 'gonadotropin-suppressing');
I tried to run the query you gave me (with temporary tables) it is still too
long so I guess that my only solution now is to use indexes.
When modifying the columns' type from text to varchar, even if the text was
indexed as unique mysql complained about duplicates, and actually there
were few duplicates, I thought it was as you said because only the beginning
of the text field is indexed but I had 2 cases were the names were short
(less than 15 characters) and the same (even the spaces).Removing one and
the index on varchar was created. Any idea why?
I will try to play with the indexes, if I understand well I'd better index
the three columns in once, because that will automatically index each of
them?or am I wrong?
Anyway, thank you for all your advices, I'm really learning a lot of things
with that case!
Melanie
From: [EMAIL PROTECTED]
To: mel list_php [EMAIL PROTECTED]
CC: mysql@lists.mysql.com
Subject: Re: help on query/group by
Date: Fri, 18 Mar 2005 12:43:06 -0500
mel list_php [EMAIL PROTECTED] wrote on 03/18/2005 10:35:30 AM:
Hi again,
Thanks for the explanation about the join and the group by.
I wanted to test your query (almost a simple copy/paste :-)) ).
The first 2 queries are ok, but the third one still is too long :
mysql CREATE TEMPORARY TABLE tmpSynDrug( KEY ( nameID (20)) )
SELECT
nameID, max( syn ) AS drugSyn
- FROM synonyms
- WHERE syn
- LIKE 'a%'
- GROUP BY nameID;
Query OK, 9693 rows affected (1.07 sec)
Records: 9693 Duplicates: 0 Warnings: 0
mysql
mysql CREATE TEMPORARY TABLE tmpSynProt (key (nameID(20)))
- SELECT nameID, max(syn) as protSyn
- FROM synonyms
- WHERE syn LIKE 'a%'
- GROUP BY nameID;
Query OK, 9693 rows affected (1.03 sec)
Records: 9693 Duplicates: 0 Warnings: 0
mysql
mysql CREATE TEMPORARY TABLE tmpMatch( KEY ( sentID) ) SELECT
m.sentID,
m.drugID, m.protID, tsd.drugSyn, tsp.protSyn
- FROM matches m
- INNER JOIN tmpSynDrug tsd ON tsd.nameID = m.drugID
- INNER JOIN tmpSynProt tsp ON tsp.nameID = m.protID;
I've tried once this morning (GMT time), it ran for one hour nothing I
killed mysql (btw, how can I kill only one query when mysql freezes?I
aborted but then mysqladmin shutdown didn't work anymore..)
I thought it was maybe because I have other heavy stuff running, but I
tried
a second time now and it's been running for 2 hours now, with almost
nothing
else on the desktop.
So apparently the join between matches and the other tables is still too
heavy
Any idea?
snip
OK, I reviewed what you have posted so far and I found a performance
killer. On the table matches, the columns protID and drugID are
declared as text. This is
Re: Wait for data to change
Ben Clewett [EMAIL PROTECTED] wrote on 03/21/2005 11:36:45 AM: Dear MySQL, I have an application which need to wait for some data to change, then act on this change. I am polling the MySQL once a second using SELECT ... I believe there is an alternate method where a thread can be made to wait for some change, therefore avoiding the expensive polling and improving performance. If any member knows what this may be, I would be very interested. Regards, Ben Clewett. I can't think of any way to do this efficiently without a trigger, a UDF, and a custom daemon. I assume based on your description that you have some sort of client application that needs to know if some data value has changed. However, you don't want to keep pinging the server to continuously ask has it changed yet, has it changed yet, has it changed yet... Here's how I think you can make this work with some sort of efficiency. You need to setup your application to connect to a daemon on some server somewhere. The only purpose of this daemon is to broadcast to those clients who connect to it a message to the effect that some data has changed on the table you are interested in. If you wanted to get really fancy, the message could say what data changed and possibly what the new value is. That way your client could possibly act on the data without needing to poll the server. How does the daemon know that data just changed? That's where the trigger and UDF come in. Triggers are fired during certain database events (adding records, changing records, or deleting records). I don't know for certain but I believe triggers can use UDFs in their code. The UDF that is called by the trigger detecting a change in the table you are interested in is what notifies the daemon (described earlier) that the data has changed. And, because the UDF is only called from within a trigger, you only bother the daemon when a change actually occurs. The downside to this design is that triggers are not supported until 5.x (still in testing) so I don't know if you have that option. User-defined functions (UDFs) may be able to cover the job by themselves IF and only if you can control data changes to ALWAYS use your UDF. Otherwise some changes may slip through the cracks. Anyway, that was my idea. Is there any other way for you to communicate a change in data than a passive detect like you are doing? How much control do you have over what can and cannot change your data (specifically the field you keep polling)? What other application-based options are open to you? Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: help on query/group by
My reply below:
mel list_php [EMAIL PROTECTED] wrote on 03/21/2005 11:49:26 AM:
Hi,
Sorry for the late reply, out for the week-end!
Here is the information you asked for (I modified the columns' type as
you
suggested)
mysql show create table matches\G
*** 1. row ***
Table: matches
Create Table: CREATE TABLE `matches` (
`protID` varchar(50) default NULL,
`drugID` int(11) default NULL,
`sentID` int(11) default NULL,
KEY `sentenceId` (`sentID`),
KEY `drugID` (`drugID`),
KEY `protID` (`protID`)
) TYPE=MyISAM
1 row in set (0.00 sec)
mysql show create table sentence\G
*** 1. row ***
Table: sentence
Create Table: CREATE TABLE `sentence` (
`id` int(10) unsigned NOT NULL auto_increment,
`text` text,
`pmid` int(11) default NULL,
PRIMARY KEY (`id`)
) TYPE=MyISAM
1 row in set (0.00 sec)
mysql show create table synonyms\G
*** 1. row ***
Table: synonyms
Create Table: CREATE TABLE `synonyms` (
`nameID` varchar(50) default NULL,
`syn` text,
UNIQUE KEY `c` (`nameID`(20),`syn`(20))
) TYPE=MyISAM
1 row in set (0.01 sec)
I think I used int as much as possible,here some data samples:
INSERT INTO `matches` VALUES ('Q9UP51', 202531, 4);
INSERT INTO `matches` VALUES ('SVC2_HUMAN', 202037, 5);
INSERT INTO `matches` VALUES ('PF2R_SHEEP', 202096, 6);
INSERT INTO `matches` VALUES ('CAQS_RAT', 202037, 7);
INSERT INTO `sentence` VALUES (4, 'Of NP10 contraceptives /NPtested
,
NPspan class=\'drug\'Ortho-Gynol/span /NPwas found to be the
most
rapidly acting , followed by NPCooper Creme /NP, a
href=\'http://srs.ebi.ac.uk/srsbin/cgi-bin/wgetz?[UNIPROT-acc:
(Q9UP51)]+-id+008+-view+UniprotDateView\'Lactikol-
B/a\n, NPVagi-Serol /NP, NPMarvosan /NP, NPClinicol /NP,
NPJelly-X /NP, NPBor-Oxyquin /NP, NPCellikol /NP, and
NPLanteen Blue Jelly /NPin NPthat order /NP. ', 12305459);
(the text is usually longer than 255 characters, so I think text is the
only
choice, except longtext which can only be worst for indexation in my
opinion)
INSERT INTO `synonyms` VALUES ('202037', 'testosterone');
INSERT INTO `synonyms` VALUES ('Q91XV1', 'gonadotropin');
INSERT INTO `synonyms` VALUES ('202037', 'estradiol');
INSERT INTO `synonyms` VALUES ('Q91XV1', 'gonadotropin-suppressing');
I tried to run the query you gave me (with temporary tables) it is still
too
long so I guess that my only solution now is to use indexes.
When modifying the columns' type from text to varchar, even if the text
was
indexed as unique mysql complained about duplicates, and actually there
were few duplicates, I thought it was as you said because only the
beginning
of the text field is indexed but I had 2 cases were the names were short
(less than 15 characters) and the same (even the spaces).Removing one
and
the index on varchar was created. Any idea why?
I will try to play with the indexes, if I understand well I'd better
index
the three columns in once, because that will automatically index each of
them?or am I wrong?
Anyway, thank you for all your advices, I'm really learning a lot of
things
with that case!
Melanie
From: [EMAIL PROTECTED]
To: mel list_php [EMAIL PROTECTED]
CC: mysql@lists.mysql.com
Subject: Re: help on query/group by
Date: Fri, 18 Mar 2005 12:43:06 -0500
mel list_php [EMAIL PROTECTED] wrote on 03/18/2005 10:35:30
AM:
Hi again,
Thanks for the explanation about the join and the group by.
I wanted to test your query (almost a simple copy/paste :-)) ).
The first 2 queries are ok, but the third one still is too long :
mysql CREATE TEMPORARY TABLE tmpSynDrug( KEY ( nameID (20)) )
SELECT
nameID, max( syn ) AS drugSyn
- FROM synonyms
- WHERE syn
- LIKE 'a%'
- GROUP BY nameID;
Query OK, 9693 rows affected (1.07 sec)
Records: 9693 Duplicates: 0 Warnings: 0
mysql
mysql CREATE TEMPORARY TABLE tmpSynProt (key (nameID(20)))
- SELECT nameID, max(syn) as protSyn
- FROM synonyms
- WHERE syn LIKE 'a%'
- GROUP BY nameID;
Query OK, 9693 rows affected (1.03 sec)
Records: 9693 Duplicates: 0 Warnings: 0
mysql
mysql CREATE TEMPORARY TABLE tmpMatch( KEY ( sentID) ) SELECT
m.sentID,
m.drugID, m.protID, tsd.drugSyn, tsp.protSyn
- FROM matches m
- INNER JOIN tmpSynDrug tsd ON tsd.nameID = m.drugID
- INNER JOIN tmpSynProt tsp ON tsp.nameID = m.protID;
I've tried once this morning (GMT time), it ran for one hour nothing
I
killed mysql (btw, how can I kill only one query when mysql
freezes?I
aborted but then mysqladmin shutdown didn't work anymore..)
I thought it was maybe because I have other heavy stuff running, but
I
tried
a second time now and it's been running for 2 hours now, with
Re: Wait for data to change
You could make your polling query as light weight as possible. Create a new table with a single integer. When an update happens increment that integer. Your polling query will then just be a select from that table to see if the number has been incremented from the last time it performed the operation. You can have several threads polling this table server times per second without any noticable performance impact (especially with the query cache). -Eric On Mon, 21 Mar 2005 11:57:00 -0500, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Ben Clewett [EMAIL PROTECTED] wrote on 03/21/2005 11:36:45 AM: Dear MySQL, I have an application which need to wait for some data to change, then act on this change. I am polling the MySQL once a second using SELECT ... I believe there is an alternate method where a thread can be made to wait for some change, therefore avoiding the expensive polling and improving performance. If any member knows what this may be, I would be very interested. Regards, Ben Clewett. I can't think of any way to do this efficiently without a trigger, a UDF, and a custom daemon. I assume based on your description that you have some sort of client application that needs to know if some data value has changed. However, you don't want to keep pinging the server to continuously ask has it changed yet, has it changed yet, has it changed yet... Here's how I think you can make this work with some sort of efficiency. You need to setup your application to connect to a daemon on some server somewhere. The only purpose of this daemon is to broadcast to those clients who connect to it a message to the effect that some data has changed on the table you are interested in. If you wanted to get really fancy, the message could say what data changed and possibly what the new value is. That way your client could possibly act on the data without needing to poll the server. How does the daemon know that data just changed? That's where the trigger and UDF come in. Triggers are fired during certain database events (adding records, changing records, or deleting records). I don't know for certain but I believe triggers can use UDFs in their code. The UDF that is called by the trigger detecting a change in the table you are interested in is what notifies the daemon (described earlier) that the data has changed. And, because the UDF is only called from within a trigger, you only bother the daemon when a change actually occurs. The downside to this design is that triggers are not supported until 5.x (still in testing) so I don't know if you have that option. User-defined functions (UDFs) may be able to cover the job by themselves IF and only if you can control data changes to ALWAYS use your UDF. Otherwise some changes may slip through the cracks. Anyway, that was my idea. Is there any other way for you to communicate a change in data than a passive detect like you are doing? How much control do you have over what can and cannot change your data (specifically the field you keep polling)? What other application-based options are open to you? Shawn Green Database Administrator Unimin Corporation - Spruce Pine -- Eric Bergen [EMAIL PROTECTED] http://www.ebergen.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: functions md5, crypt
On Monday 21 Mar 2005 16:32, Michael Stassen wrote: Which is the first hit if you enter md5 crypt in the manual's search box. I use the manual offline, because my connection is too slow. I wonder why the encryption-function chapter is not in my manual. -- symbulos partners -.- symbulos - ethical services for your organisation http://www.symbulos.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Merge tables,trigger support, and table rotation
How does one insert records based on some kind of meta data or key in
particular table belonging to a merge table?
I have a network logging program and would like to partition the table so that
analysts can query certain tables belonging
to the merge table instead of the whold table to corelate events.
/*merge table section of email */
Based on my limited research I thought that I would create a merged table such
as: /*from the web site */
mysql CREATE TABLE t1 (
-a INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
-message CHAR(20));
mysql CREATE TABLE t2 (
-a INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
-message CHAR(20));
mysql INSERT INTO t1 (message) VALUES ('Testing'),('table'),('t1');
mysql INSERT INTO t2 (message) VALUES ('Testing'),('table'),('t2');
mysql CREATE TABLE total (
-a INT NOT NULL AUTO_INCREMENT,
-message CHAR(20), INDEX(a))
-TYPE=MERGE UNION=(t1,t2) INSERT_METHOD=LAST;
/* trigger section of email */
Question(s): I will assume of that when 5.02 becomes stable and triggers are
available
then
mysql CREATE TRIGGER ins_sum INSERT ON total
IF message='happy ending' Then
INSERT INTO t1 (message) VALUES ('goldie locks'),('mama
bear'),('eat her up')
ELSE INSERT INTO t2 (message) VALUES ('KILLED Mama bear'),('papa
bear and baby bear'),
('in cirus');
Will triggers support insert, update, and delete verbs(actions) on a merged
table?
/* table rotation */
Second question: As tables grow can can a new table be created and
automatically added to the
merged table, at or on a particular date or time or when the size of the table
reaches a
certain limit?
Which leads to the third question: if myisam tables can be created dynamically
based
on size of table or time of day, can the oldest table be compressed with
myisampack
automatically?
Thank you,
Raymond
Re:ERROR 2013: Lost connection to MySQL server during query
Hi, I'm having a similar problem using the LOAD DATA. I get an intermittent error: ERROR 2013 (HY000) at line 1: Lost connection to MySQL server during query The command is: LOAD DATA local infile 'pipefile' into table FACT_TABLE fields terminated by '~' lines terminated by '\n'; I cannot seem to replicate it all the time. I have a PERL program that writes to the PIPE and MySQL reads the pipe. Did you have any success in figuring out the problem? Regards, George sol beach [EMAIL PROTECTED] wrote: I've tried looking up this error code on www.mysql.org via Google. I'm not sure it matters but if you look closely at the SQL below, you can see it is working on file page_path.tab4. It has already successfully loaded files page_path.tab1 thru page_path.tab3 I seriously could use a CLUE on what needs to be changed to avoid this error. TIA! Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 183 to server version: 3.23.58-max Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql LOAD DATA CONCURRENT LOCAL INFILE '../data/mysql/initial_20050318_102517/page_path.tab4' - REPLACE INTO TABLE page_path - FIELDS TERMINATED BY '^' OPTIONALLY ENCLOSED BY '' LINES TERMINATED BY '\n' - (PAGE_PATH_ID,NAME,DATE_CREATED); ERROR 2013: Lost connection to MySQL server during query mysql [EMAIL PROTECTED]:/b/martgen/mysql/bingrep -i large show-variables.log large_files_support ON [EMAIL PROTECTED]:/b/martgen/mysql/binuname -a Linux sdb2.hitbox.com 2.4.21-4.ELsmp #1 SMP Fri Oct 3 17:52:56 EDT 2003 i686 i686 i386 GNU/Linux [EMAIL PROTECTED]:/b/martgen/mysql/bin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Switch to Netscape Internet Service. As low as $9.95 a month -- Sign up today at http://isp.netscape.com/register Netscape. Just the Net You Need. New! Netscape Toolbar for Internet Explorer Search from anywhere on the Web and block those annoying pop-ups. Download now at http://channels.netscape.com/ns/search/install.jsp -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: functions md5, crypt
Please stop writing high priority e-mails to a mailinglist. Although the problem might be important for you, it's in no way important for the receiver (which is quite a lot of people). People will answer mailinglist questions for free, in their time. I hope you understand. -- Martijn Tonies -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Data Standards on Database Export-Import
Hello, Are there any good rfcs or other documents that would assist in the discussion I'm having with our organization's Oracle programmers in regards the following: Oracle DB - MySQL They recommend: ~ col~col ~col~ col',~col NOTE::I placed the ' in for a reason. I am looking for: col,col,col,col\'\,,col Of course the non-Oracle system is being tasked as the problem in this case for the abnormal data request. Any help on this would be appreciated. Rob. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Recreate orphaned table from innodb
Dear all, I have InnoDB files which I thought belonged to a certain database within the DATA directory. However when I re-installed these files and managed to restart MySQL, any query will produce the following error in mysq.err 050321 23:42:00 InnoDB error: Cannot find table urlmonitor/requestpath from the internal data dictionary of InnoDB though the .frm file for the table exists. Maybe you have deleted and recreated InnoDB data files but have forgotten to delete the corresponding .frm files of InnoDB tables, or you have moved .frm files to another database? Look from section 15.1 of http://www.innodb.com/ibman.html how you can resolve the problem. 050321 23:42:00 [ERROR] E:\Arquivos de programas\MySQL\MySQL Server 4.1\bin\mysqld-nt: Can't open file: 'requestpath.InnoDB' (errno: 1) As far as I know, I haven't either deleted/recreated InnoDB or moved FRM files... They were all bunched up together. The question is: how can I see the data stored in the InnoDB datafile so that I can try and rebuild the FRM files or something like that?? Any ideas? Thanks a lot... Paulo Bernardo Lindoso Systems Engineer Datacraft do Brasil [EMAIL PROTECTED] Tel +55 11 3708-2324 Fax +55 11 3708-2301 http://www.datacraft.com.br BLOCKED::BLOCKED::http://www.datacraft.com.br
