Naming constraints
Hi, I have problem in naming foreign key constraints : Engine : mySql 4.1.1 and/or 5.0.0a System : Windows 2000 sp4 and / or win NT 4 sp6a Here is a script example of what I need to do : /* Script 1 */ CREATE TABLE tblphone ( nameid INT PRIMARY KEY AUTO_INCREMENT, fnameVARCHAR(30), lnameVARCHAR(30) NOT NULL ) TYPE = INNODB; CREATE TABLE tblnumbers( numid INT PRIMARY KEY AUTO_INCREMENT, nameid INT , phone VARCHAR(20), INDEX(nameid), CONSTRAINT fktblnumbFOREIGN KEY(nameid)REFERENCES tblphone(nameid) ) TYPE = INNODB; The aim of this script is to use a defined name for the foreign key constraint (here 'fktblnumb') and use it for dropping the constraints in another update scripts : /* Script 2 */ alter table tblnumbers drop foreign key fktblnumb; Actually, the name for the just created constraint is an 'internally generated id' (i.e :0_023), so the update script can't find the named constraint 'fktblnumb' The show create table tblnumbers outputs : CREATE TABLE `tblnumbers` ( `numid` int(11) NOT NULL auto_increment, `nameid` int(11) default NULL, `phone` varchar(20) default NULL, PRIMARY KEY (`numid`), KEY `nameid` (`nameid`), CONSTRAINT `0_23` FOREIGN KEY (`nameid`) REFERENCES `tblphone` (`nameid`), ) ENGINE=InnoDB DEFAULT CHARSET=latin1 Any idea about this behaviour ? thanks in advance... best regards -- Richard FURIC CEDRICOM Tel : 02 99 55 07 55 Fax : 02 99 55 08 64 E-mail : [EMAIL PROTECTED] site vitrine : www.cedricom.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Naming constraints
Hello, I have problem in naming foreign key constraints : --8-- Actually, the name for the just created constraint is an 'internally generated id' (i.e :0_023), so the update script can't find the named constraint 'fktblnumb' The show create table tblnumbers outputs : CREATE TABLE `tblnumbers` ( `numid` int(11) NOT NULL auto_increment, `nameid` int(11) default NULL, `phone` varchar(20) default NULL, PRIMARY KEY (`numid`), KEY `nameid` (`nameid`), CONSTRAINT `0_23` FOREIGN KEY (`nameid`) REFERENCES `tblphone` (`nameid`), ) ENGINE=InnoDB DEFAULT CHARSET=latin1 Any idea about this behaviour ? Yes, MySQL parses the named constraint, but doesn't apply it and creates its own constraint name. With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Naming constraints
So how is it possible to drop a foreign key in a master / slave replication ? the autogenerated-naming constraint should be different ? How automatic database structure update can be executed (from a script generetor like PowerDesigner) ? thanks... Martijn Tonies [EMAIL PROTECTED] a écrit dans le message news: [EMAIL PROTECTED] Hello, I have problem in naming foreign key constraints : --8-- Actually, the name for the just created constraint is an 'internally generated id' (i.e :0_023), so the update script can't find the named constraint 'fktblnumb' The show create table tblnumbers outputs : CREATE TABLE `tblnumbers` ( `numid` int(11) NOT NULL auto_increment, `nameid` int(11) default NULL, `phone` varchar(20) default NULL, PRIMARY KEY (`numid`), KEY `nameid` (`nameid`), CONSTRAINT `0_23` FOREIGN KEY (`nameid`) REFERENCES `tblphone` (`nameid`), ) ENGINE=InnoDB DEFAULT CHARSET=latin1 Any idea about this behaviour ? Yes, MySQL parses the named constraint, but doesn't apply it and creates its own constraint name. With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 2003 server problem
Dominic Take a look at the steps necessary to connect to MySQL 1)Server running 2)Setting up GRANTS 3)Setup users and access permissions http://dev.mysql.com/doc/mysql/en/Access_denied.html HTH, Martin- - Original Message - From: Dominic James [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, October 08, 2004 1:01 AM Subject: 2003 server problem I am having trouble intalling versions 4.02 and 4.1 onto server 2003. The issues are; 1. my.ini is not being written 2. WinMYSQLAmin is causing the following error; Access violation at address 0040289D in module 'winmysqladmin.exe'. read of address . 3. MySQL can can only be stared once as a service. Restarts fail with the following message; Could not start the MYSQL service on Local Computer. Error 1067: The process terminated unexpectedly 4:OBDC wont connect; Client does not support authentication protocol requested by server; Any advice would be appreciated. -- 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]
reduced installation
Hello, I am new to Mysql well relatively anyway. I want to install it on an embedded PC 104 linux machine but have a problem. -Limited space less than 8MB flash disk I have decided to look at installing the binary dstribution according to the instruction in the manual but then only loading the files that are strictly necessary. Eg without any documentation etc. Could someone tell me which files are absolutely necessary and which files and directories I can afford to ommit? Thanks in advance. Regards, Roland -- -I have not failed. I've just found 10,000 ways that won't work. -(Thomas Alva Edison 1847-1931) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Naming constraints
So how is it possible to drop a foreign key in a master / slave replication ? the autogenerated-naming constraint should be different ? I have no idea. How automatic database structure update can be executed (from a script generetor like PowerDesigner) ? With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Delete duplicate entry
Daniel Kasak wrote:
gerald_clark wrote:
Batara Kesuma wrote:
Hi,
I have a table that looks like:
CREATE TABLE `message_inbox` (
`member_id` mediumint(8) unsigned NOT NULL default '0',
`message_id` int(10) unsigned NOT NULL default '0',
`new` enum('y','n','replied') NOT NULL default 'y',
`datetime` datetime default NULL,
KEY `idx_1` (`member_id`,`new`),
KEY `idx_2` (`member_id`,`datetime`)
) TYPE=InnoDB
Now, I want to add a primary key to it.
ALTER TABLE message_inbox ADD PRIMARY KEY (member_id, message_id)
But there are already some duplicated entries. ERROR 1062: Duplicate
entry '10244-80871' for key 1
How can I tell MySQL to delete the duplicated entry and continue to
make primary key? Is there any efficient way to do this? Thank you
very much.
try ALTER IGNORE TABLE.
That hardly seems like a solution. If the above works, then I'd call
that 'feature' a bug.
What an odd statement.
The ignore option is there to allow you to create a unique index,
discarding duplicates.
Since it does exactly what was asked for, why doesn't it seem like a
solution.
Why is something that does what it was designed to do a bug?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: optimizing InnoDB tables
Hi, According to the manual - http://dev.mysql.com/doc/mysql/en/OPTIMIZE_TABLE.html http://dev.mysql.com/doc/mysql/en/InnoDB_File_Defragmenting.html running a null ALTER statement - ALTER TABLE tbl-name type=INNODB; will rebuild the table thus optimizing the way the table is written to the disk. It will fix the physical ordering of the index pages on the disk thus improving the time MySQL needs to perform an index seek. It will not decrease the space used by the INNODB file but it could speed things up. If you want to regain some of the space used by the INNODB file you will have to convert all INNODB tables to MYISAM (or dump them to a SQL file), recreate the INNODB file (s) and then recreate the original INNODB tables. This process could take a lot of time depending on the size of your tables so you should proceed with care. HTH -- Dobromir Velev [EMAIL PROTECTED] http://www.websitepulse.com/ On Thursday 07 October 2004 22:07, Boyd E. Hemphill wrote: The documentation is not clear on this point. Here is a quote: 'For BDB tables, OPTIMIZE TABLE is currently mapped to ANALYZE TABLE. It was also the case for InnoDB tables before MySQL 4.1.3; starting from this version it is mapped to ALTER TABLE.' What is meant by its being mapped to ALTER TABLE? Too, what exactly happens after 4.1.3? Is space, in fact, recovered and defragged? Thanks for your time! Best Regards, Boyd E. Hemphill MySQL Certified Professional [EMAIL PROTECTED] Triand, Inc. www.triand.com O: (512) 248-2278 M: (713) 252-4688 -Original Message- From: Christopher L. Everett [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 06, 2004 6:23 PM To: 'Mysql List' Subject: Re: Repeated corruption with MySQL 4.1.x using FULLTEXT indexes Ed Lazor wrote: -Original Message- From: Christopher L. Everett [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 06, 2004 1:47 AM To: Mysql List Subject: Repeated corruption with MySQL 4.1.x using FULLTEXT indexes I have an application where I create a faily large table (835MB) with a fulltext index. One of our development workstations and our production server will run the script to load the table, but afterwards we have a pervasive corruption, with out of range index index pointer errors. Oddly, my development workstation doesn't have those problems. My box and the ones having the problems have the following differences: - my box runs ReiserFS, the problem boxes run XFS - my box has a nice SCSI HD subsystem, the problem boxes do IDE. All three boxes run Linux 2.6.x kernels, and my workstation and production server share the same mobo. Come to think of it, I saw similar corruption issues under 2.4.x series kernels and MySQL v4.0.x, it just wasn't the show stopper it is now. Also, on all three boxes, altering the table to drop an index and create a new one requires a myisamchk -rq run afterwards when a fulltext index either exists or gets added or dropped, which I'd also call a bug. The problems you're describing are similar to what I've run into when there have been hardware related problems. One system had a problem with ram. Memory tests would test and report ram as ok, but everything started working when I replaced the ram. I think it was just brand incompatibility or something odd, because the ram never gave any problems in another system. I can generate the problem on much smaller data sets, in the mid tens of thousands of records rather than the millions of records. I'll do a memtest86 run on the development boxes overnight, but as I did that just after I installed linux on them and used the linux badram patch to exclude iffy sections of RAM, I don't think thats a problem. One system had hard drive media slowly failing and this wasn't obvious until we ran several full scan chkdsks. 3 hard drives all of different brand, model size, and the problem happening in the same place on both? Not likely. The funniest situation was where enough dust had collected in the CPU fan to cause slight over heating, which resulted in oddball errors. This isn't a problem on my box. I have a 1.5 pound copper heatsink with a 90mm heat sensitive fan and a fan+heatsink for the hard drive, and I saw myisamchk consistently generate the same error in the same place over and over. The sensors report my CPU running in the 45 degree centigrade range on my box pretty consistently. In each of these cases, everything would work fine until the system would start processing larger amounts of data. Small amounts of corruption began to show up that seemed to build on itself. This may or may not relate to what you're dealing with, but maybe it will help =) I'll look, but I don't think that's the problem. I'm going to see how small of a data set will cause this problem and file a bug report. -- Christopher L. Everett Chief Technology Officer
mysql@lists.mysql.com
* sh ./mysql.server start Starting mysqld daemon with databases from /usr/local/var 041008 22:05:29 mysqld ended 041008 22:05:29 mysqld started /usr/local/libexec/mysqld: unrecognized option `--key_buffer=16M' /usr/local/libexec/mysqld Ver 3.23.57 for pc-linux on i686 Copyright (C) 2000 MySQL AB, by Monty and others This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license Starts the MySQL server Usage: /usr/local/libexec/mysqld [OPTIONS] --ansiUse ANSI SQL syntax instead of MySQL syntax -b, --basedir=pathPath to installation directory. All paths are usually resolved relative to this --big-tablesAllow big result sets by saving all temporary sets on file (Solves most 'table full' errors) --bind-address=IPIp address to bind to --bootstrapUsed by mysql installation scripts --character-sets-dir=... Directory where character sets are --chroot=pathChroot mysqld daemon during startup --core-fileWrite core on errors -h, --datadir=pathPath to the database root --default-character-set=charset Set the default character set --default-table-type=type Set the default table type for tables --delay-key-write-for-all-tables Don't flush key buffers between writes for any MyISAM table --enable-lockingEnable system locking -T, --exit-infoUsed for debugging; Use at your own risk! --flushFlush tables to disk between SQL commands -?, --helpDisplay this help and exit --init-file=fileRead SQL commands from this file at startup -L, --language=...Client error messages in given language. May be given as a full path --local-infile=[1|0] Enable/disable LOAD DATA LOCAL INFILE -l, --log[=file]Log connections and queries to file --log-bin[=file] Log queries in new binary format (for replication) --log-bin-index=file File that holds the names for last binary log files --log-update[=file]Log updates to file.# where # is a unique number if not given. --log-isam[=file]Log all MyISAM changes to file --log-long-formatLog some extra information to update log --low-priority-updates INSERT/DELETE/UPDATE has lower priority than selects --log-slow-queries=[file] Log slow queries to this log file. Defaults logging to hostname-slow.log --pid-file=pathPid file used by safe_mysqld --myisam-recover[=option[,option...]] where options is one of DEAULT, BACKUP or FORCE. --memlockLock mysqld in memory -n, --newUse very new possible 'unsafe' functions -o, --old-protocolUse the old (3.20) protocol -P, --port=...Port number to use for connection -O, --set-variable var=option Give a variable an value. --help lists variables -Sg, --skip-grant-tables Start without grant tables. This gives all users FULL ACCESS to all tables! --safe-modeSkip some optimize stages (for testing) --safe-show-database Don't show databases for which the user has no privileges --safe-user-createDon't new users cretaion without privileges to the mysql.user table --skip-concurrent-insert Don't use concurrent insert with MyISAM --skip-delay-key-write Ignore the delay_key_write option for all tables --skip-host-cacheDon't cache host names --skip-lockingDon't use system locking. To use isamchk one has to shut down the server. --skip-name-resolveDon't resolve hostnames. All hostnames are IP's or 'localhost' --skip-networkingDon't allow connection with TCP/IP. --skip-newDon't use new, possible wrong routines. --skip-stack-traceDon't print a stack trace on failure --skip-show-database Don't allow 'SHOW DATABASE' commands --skip-thread-priority Don't give threads different priorities. --socket=...Socket file to use for connection -t, --tmpdir=pathPath for temporary files --sql-mode=option[,option[,option...]] where option can be one of: REAL_AS_FLOAT, PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, SERIALIZE, ONLY_FULL_GROUP_BY. --transaction-isolation Default transaction isolation level --temp-pool Use a pool of temporary files -u, --user=user_nameRun mysqld daemon as user -V, --versionoutput version information and exit -W, --warningsLog some not critical warnings to the log file Default options are read from the following files in the given order: /etc/my.cnf /usr/local/var/my.cnf ~/.my.cnf The following groups are read: mysqld server The following options may be given as the first argument: --print-defaultsPrint the program argument list and exit --no-defaultsDon't read default
Re: Optimize queries
Hi, Using sub-selects (MySQL 4.1 and higher) you can use something like select count(*)/(select count(*) from table where field1='myvalue') as percentage from table where category='myvalue' group by category; but I don't think you will gain much in performance this way. I'd rather use two queries - one for the total and one for the percentages. If field1 is indexed select count(*) from table where field1='myvalue' should be quite fast, so I don't think you should worry about having an additional query. HTH -- Dobromir Velev [EMAIL PROTECTED] http://www.websitepulse.com/ and from a script I calculated my percentage = bigToal/categoryTotal On Friday 08 October 2004 10:48, Jacques Jocelyn wrote: Hello there, Until I had query time restriction due to my web hoster. Meaning whenever the query was too long, it gets killed ! Now I have changed my webhoster, I'd like to optimize my queries and I would like to have your opinion on that. I wrote multiple queries to prevent any long query time duration such as : until now I did the following - to obtain the total of item which match requirements - select count(*) 'bigTotal' from table where field1='myvalue' then I selected total of category from the same table to get the percentage of the total such as : - select count(*) 'categoryTotal' from table where category1='myvalue' and from a script I calculated my percentage = bigToal/categoryTotal - select count(*) 'categoryTotal' from table where category2='myvalue' and from a script I calculated my percentage = bigToal/categoryTotal etc.. now, I have planned the following : create ONE query to do all this. is there a way then to merge the two previous queries in only one ? and calculate the percentage at the same time ? To merge all my category queries, I can use a GROUP BY, but what about the bigTotal, can have that in the same query ? Please advise. Thanks Best regards, Jacques Jocelyn -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Restoring few tables from all-databases dump file
Hi, Regularly we maintain backup of all databases using mysqldump utility. Most of the times i am getting requests from our QA department to restore a particular table or particular database to check purging scripts and other scripts. Is there any way to restore a particular table or databases from all-databases backup Thanks Anil DBA -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Innodb foreign keys names
Hi everybody, Still have a problem with naming foreign key constraints on innodb tables. Can't retrieve any of the name that was given to the constraints. Internally generated IDs are always given to the foreign keys... no way to apply a drop foreign key 0_ on a replicated server or gererate automatic update scripts. This is from mySql documentation : ALTER TABLE yourtablename ADD [CONSTRAINT [symbol]] FOREIGN KEY (...) REFERENCES anothertablename(...) What the symbol value is use for ? if it is not handled : is this a bug ? Is there any planned release of InnoDB that could handle the constraint foreign key names ? thanks in advance regards -- Richard FURIC CEDRICOM Tel : 02 99 55 07 55 Fax : 02 99 55 08 64 E-mail : [EMAIL PROTECTED] site vitrine : www.cedricom.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: data with dynamic schema stored in a column as a property list.
Have you considered a combination of Full-text indexing (to quickly locate
a subset of records that may match your criteria) and regular expression
matching (to eliminate the non-matching results from the results of the
full-text search)? I know it's two steps but your data is practically
opaque to the database engine. The field names and the values you want to
search for exist as content, not as standalone fields or name/value pairs
of columns.
Without somehow converting your data stream into some kind of relational
structure, I think that you will be quite restricted in your searching
options.
Sorry I couldn't be more helpful,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Elim Qiu [EMAIL PROTECTED] wrote on 10/07/2004 11:14:49 PM:
Hi, instead of xml, i stored arbitrary data of the form
(the actual usage of such mechanism is for more fancy stuff,
say, dynamic configuration, otherwise this is really not necessary)
{
name = Fn, Ln; // string value
gender = F; // single word string
interests = (reading,drive fast); // array
children = (
{ lastName = Howe; firstName = Sam; gender = M; dob =
1994-10-07 16:59:26; },
{ lastName = Howe; firstName = Ann; gender = F; dob =
1998-01-26 04:09:12; }
);
creditCards = {
visa = XXX-x;
master = YY-;
};
}
This is called plist and the depth of the hierarchy can go arbitrary
deep (unknown limit). And it can be converted back
and forth from dictionary object by a framework.
My task is to find out ways of querying a column holds such text
data? say, find out whether there is certain key or
whether a key has certain value. I got some solution via regular
expression feature of MySQL.
The column type that I use is text. My question now is how to make
the whole thing perform good. In other words,
for regular expression querying, should I index the column for
performance? If so, what kind of index should I use?
Thanks a lot.
Reg : eliminating duplicate entries
Try this Take dump of table using mysqldump utility eg : mysqldump db name tablename --quick --allow-keywords --no-create-info dumpfilename Then truncate table data eg: mysqltruncate table tablename; create primary or unique keys what ever you want on table Then restore the data from dump file with the following options mysql databasename --force dumpfilename Thanks Anil DBA -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re[2]: Optimize queries
Hello Dobromir, Friday, October 8, 2004, 3:47:06 PM, you wrote: DV Hi, DV Using sub-selects (MySQL 4.1 and higher) you can use something like DV select count(*)/(select count(*) from table where field1='myvalue') as DV percentage from table where category='myvalue' group by category; DV but I don't think you will gain much in performance this way. I'd rather use DV two queries - one for the total and one for the percentages. If field1 is DV indexed DV select count(*) from table where field1='myvalue' DV should be quite fast, so I don't think you should worry about having an DV additional query. You may be right. I was just wondering, thanks for the input. I will go for two queries. thanks Best regards, Jacques Jocelyn -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Need help optimizing full-text searches
Hi, I currently have a table with 2 text fields, both of which are marked as fulltext. I'm using the full-text boolean search method with fairly good results. My problems however are: 1. I am on a shared host provider so I can't tweak the full-text search options such as stop words or minimum query length. 2. I would like to be able to search for words as short as 2 characters, but I'm limited by the default mysql configuration to 4 characters. 3. I'd like to get around the stopwords/stop characters. A lot of people search for 802.11g , but that search always comes up empty because I think the . character is messing up my searches. 4. Since I'm using the full-text search with a small amount of data, I would like all the results to be returned, even if they keywords are in more or less than 50% of the rows. 5. I really like the +, -, and for searching in full-text boolean mode, so I'd like to preserve that functionality. Since I'm on a shared host provider and can't really optimize the mysql configuration, is there any suggestions to help me improve my search results? Thanks, Grant __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Loading MySQL on 2003 server
I am trying to load MySQL on my 2003 server and getting an error message Product: MySQL Configuration -- Error 1920.Service MySql (MySql) failed to start. Verify that you have sufficient privileges to start system services. I am logged in on the server as administrator so I don't understand why I don't have privileges. Has anyone ran into this issue. Thanks Jerry
Re: Optimize queries
You may still be able to do this as a single query: SELECT SUM(if(field1='myvalue',1,0)) as bigTotal, SUM(if(category1 = 'myvalue',1,0)) as categoryTotal, SUM(if(category1 = 'myvalue',1.0,0))/SUM(if(field1='myvalue',1.0,0)) as CategoryPercent FROM table It performs a table scan to calculate your values but it is one way to combine your statements into a single query. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Jacques Jocelyn [EMAIL PROTECTED] wrote on 10/08/2004 03:48:26 AM: Hello there, Until I had query time restriction due to my web hoster. Meaning whenever the query was too long, it gets killed ! Now I have changed my webhoster, I'd like to optimize my queries and I would like to have your opinion on that. I wrote multiple queries to prevent any long query time duration such as : until now I did the following - to obtain the total of item which match requirements - select count(*) 'bigTotal' from table where field1='myvalue' then I selected total of category from the same table to get the percentage of the total such as : - select count(*) 'categoryTotal' from table where category1='myvalue' and from a script I calculated my percentage = bigToal/categoryTotal - select count(*) 'categoryTotal' from table where category2='myvalue' and from a script I calculated my percentage = bigToal/categoryTotal etc.. now, I have planned the following : create ONE query to do all this. is there a way then to merge the two previous queries in only one ? and calculate the percentage at the same time ? To merge all my category queries, I can use a GROUP BY, but what about the bigTotal, can have that in the same query ? Please advise. Thanks Best regards, Jacques Jocelyn -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Multiple Databases or One?
David Blomstrom [EMAIL PROTECTED] wrote on 10/07/2004 05:37:08 PM: I'm working on several websites that will be driven primarily by two databases - Geography and Animals. The Geography database will feature information about nations, provinces and states, such as capitals, population, etc. The Animals database features lots of taxonomic tables (orders, families, species, etc.), along with information about diet, distribution, etc. I would guess each database could ultimately have as many as two dozen tables or more. Some of my sites will need a third database (or extra tables in one of the existing databases). For example, I'm working on a big Symbols database table. Anyway, I thought I was getting to the point where I'd better split all of my tables into two or more databases to help me keep organized. Then I realized that it could be a lot of trouble figuring out how to connect to and manipulate two or three databases. In the long run, it might be easier to just dump everything into one big database. It occurred to me that as I learn more about MySQL, there may be database-wide operations I'll want to perform on all my Animals tables, but not on my Geography tables. If I do put everything in one table, is there some naming scheme I could use to facilitate this? In other words, if all my Animals tables feature the same prefix or suffix, would it help me perform operations that affect only the Animals tables? Specific names (or name prefixes) are a common technique in organizing the lists of tables in larger databases into some kind of coherent order. Depending on what you want to do to your tables (you weren't very specific) you could leave them all in the same database and just operate on them by specific name. Some commands do take wildcards. If you did preface each table with something that identified which dataset that table belonged to (like a_genera, g_country) then those batch commands would be able to tell one set of tables apart from another. As you decide between one database and two, look at the pros and cons of each scheme compared to the other. For example: Two databases may mean that you can store them on separate drives (increasing throughput). However cross-database queries require extra maintenance. Get in the manuals and search the forums until you think you have identified all of the pros and cons for your situation. Then you can make an informed decision. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: MySQL Databases in Subdirectories?
We're trying to avoid the following scenarios: Using one database for every site, and having 100,000 subdirectories of the MySQL data directory; -or- Using one (or more) tables in a single database for each site, and having x00,000 table files. Either of the above would make maintenance (backups, etc.) a chore. Ruben Safir Secretary NYLXS wrote: How does that help? The database itself should be allowed to organize everything. Ruben On Thu, Oct 07, 2004 at 04:57:39PM -0700, Justin Smith wrote: Is it possible to create a database in a lower-level subdirectory of MySQL's data directory? We have almost 100,000 sites, and we would like to have a separate database for each site. However, it's very impractical from a filesystem maintenance standpoint to have 100,000 subdirectories of MySQL's data directory. What we would like to do is break up the directories into something like this: for site #12345: [mysql datadir]/01/23/45/[databasename] This would greatly improve the manageability of the table space. Is this possible? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: 2003 server problem
Have you checked the mysql log files in c:\mysql\data? -Original Message- From: Dominic James [mailto:[EMAIL PROTECTED] Sent: Thursday, October 07, 2004 10:01 PM To: [EMAIL PROTECTED] Subject: 2003 server problem I am having trouble intalling versions 4.02 and 4.1 onto server 2003. The issues are; 1. my.ini is not being written 2. WinMYSQLAmin is causing the following error; Access violation at address 0040289D in module 'winmysqladmin.exe'. read of address . 3. MySQL can can only be stared once as a service. Restarts fail with the following message; Could not start the MYSQL service on Local Computer. Error 1067: The process terminated unexpectedly 4:OBDC wont connect; Client does not support authentication protocol requested by server; Any advice would be appreciated. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication problem with a cross database query
On 10/06/2004 6:23 PM, Bill Thomason [EMAIL PROTECTED] wrote: I originally posted a query about a problem entitled Table doesn't exist on query replication problem... The original title might be a little misleading. The slave replication is halting on a transaction that contains a query that spans two databases - one that is being replicated and the other is not. Could anyone provide me with some general rules of thumb about breaking down such a query? This may sound like a vague or possibly stupid question since this predicament is probably very specific to the situation. I didn't write the original query but I am establishing the master/slave relationships and uncovered the bug in doing so. Other than altering your database schema or replicating the reporting database, your options appear to be very limited. You can't use 'SQL_SLAVE_SKIP_COUNTER = 1' unless your slave is not running, and bookending the troublesome query with 'SQL_LOG_BIN = 0' and 'SQL_LOG_BIN = 1' only works from within the mysql client. Paul -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
select/query from two tables
A popular question, how to select/query from two tables. I googled it
but am having trouble, wondered if anyone would answer this newbie
question. Here's my existing (PHP) query:
$query = SELECT page.* FROM `page` LEFT JOIN `keywords` USING
(`page_id`) WHERE MATCH (`keywords`.`keyword_txt`) AGAINST ('$keyword'
IN BOOLEAN MODE);
I want to SELECT from a new second table using page_id just like I'm
doing now...they all are related by that page_id field. So i'm already
selecting from a table called page and I want to select from a new
table called url the same i do for page at the same time. How do I
modifiy my statement?
Thank you sincerely,
Lee G.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
issues with mysql cluster
Hi all! When dealing with MySQL clusters using ndb... I have configured a 4 nodes under linux. I would like to access the 4nodes cluster using jdbc... how do i specify that the application should connect to any of the db servers in the cluster using connector/j? Vic. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: select/query from two tables
I think that this will work:
$query = SELECT page.*, url.* FROM `page` LEFT JOIN `keywords` USING
(`page_id`) LEFT JOIN URL USING (`page_id`) WHERE MATCH
(`keywords`.`keyword_txt`) AGAINST ('$keyword'
IN BOOLEAN MODE);
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
leegold [EMAIL PROTECTED] wrote on 10/08/2004 12:06:17 PM:
A popular question, how to select/query from two tables. I googled it
but am having trouble, wondered if anyone would answer this newbie
question. Here's my existing (PHP) query:
$query = SELECT page.* FROM `page` LEFT JOIN `keywords` USING
(`page_id`) WHERE MATCH (`keywords`.`keyword_txt`) AGAINST ('$keyword'
IN BOOLEAN MODE);
I want to SELECT from a new second table using page_id just like I'm
doing now...they all are related by that page_id field. So i'm already
selecting from a table called page and I want to select from a new
table called url the same i do for page at the same time. How do I
modifiy my statement?
Thank you sincerely,
Lee G.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Restarting Replication from Backup
Hey guys, I'm running a master/slave setup with v4.0.20. There are a hand full of databases being replicated. For backups, we stop replication on the slave and pull a text dump using mysqldump. I also record a 'SHOW SLAVE STATUS' from the time of the backup. My replica server crashed last night. It looks like it had something to do with the disk cache as the replica was trying to replay already committed transactions (lots of duplicate record errors). After running an integritty check on the servers, the row counts are out of sync for far more large tables than I care to manually fix. I'm thinking of: 1) deleting all the data on the replica 2) pulling a backup from a few days ago and re-importing it with replication disabled on the replica (ie, comment out all replication configuration directives). 3) artificially recreating master-info-file using the information from 'SHOW SLAVE STATUS' 4) restart the replica with replication turned back on With MySQL's two phase replication, will the IO thread automatically figure out what file to start downloading and where to resume? Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: select/query from two tables
On Fri, 8 Oct 2004 12:22:37 -0400, [EMAIL PROTECTED] said:
I think that this will work:
$query = SELECT page.*, url.* FROM `page` LEFT JOIN `keywords` USING
(`page_id`) LEFT JOIN URL USING (`page_id`) WHERE MATCH
(`keywords`.`keyword_txt`) AGAINST ('$keyword'
IN BOOLEAN MODE);
Sorry to bother I may be showing my lack, but the url table is a
different table from the page table so wouldn't it be folowing your
example:
$query = SELECT page.* FROM `page`, url.* FROM `url` LEFT JOIN
`keywords` USING??
Thanks again
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
leegold [EMAIL PROTECTED] wrote on 10/08/2004 12:06:17 PM:
A popular question, how to select/query from two tables. I googled it
but am having trouble, wondered if anyone would answer this newbie
question. Here's my existing (PHP) query:
$query = SELECT page.* FROM `page` LEFT JOIN `keywords` USING
(`page_id`) WHERE MATCH (`keywords`.`keyword_txt`) AGAINST ('$keyword'
IN BOOLEAN MODE);
I want to SELECT from a new second table using page_id just like I'm
doing now...they all are related by that page_id field. So i'm already
selecting from a table called page and I want to select from a new
table called url the same i do for page at the same time. How do I
modifiy my statement?
Thank you sincerely,
Lee G.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: select/query from two tables
No. You only get one FROM clause, so it's SELECT columns FROM tables
See the manual for complete details of SELECT syntax
http://dev.mysql.com/doc/mysql/en/SELECT.html.
Michael
leegold wrote:
On Fri, 8 Oct 2004 12:22:37 -0400, [EMAIL PROTECTED] said:
I think that this will work:
$query = SELECT page.*, url.* FROM `page` LEFT JOIN `keywords` USING
(`page_id`) LEFT JOIN URL USING (`page_id`) WHERE MATCH
(`keywords`.`keyword_txt`) AGAINST ('$keyword'
IN BOOLEAN MODE);
Sorry to bother I may be showing my lack, but the url table is a
different table from the page table so wouldn't it be folowing your
example:
$query = SELECT page.* FROM `page`, url.* FROM `url` LEFT JOIN
`keywords` USING??
Thanks again
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
leegold [EMAIL PROTECTED] wrote on 10/08/2004 12:06:17 PM:
A popular question, how to select/query from two tables. I googled it
but am having trouble, wondered if anyone would answer this newbie
question. Here's my existing (PHP) query:
$query = SELECT page.* FROM `page` LEFT JOIN `keywords` USING
(`page_id`) WHERE MATCH (`keywords`.`keyword_txt`) AGAINST ('$keyword'
IN BOOLEAN MODE);
I want to SELECT from a new second table using page_id just like I'm
doing now...they all are related by that page_id field. So i'm already
selecting from a table called page and I want to select from a new
table called url the same i do for page at the same time. How do I
modifiy my statement?
Thank you sincerely,
Lee G.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Restarting Replication from Backup
Gary, We go through the process of removing the slave DB and restoring from backup on a fairly regular basis (due to testing new functionality of our app). My first question would be about your backups - how are you doing them? If you are doing a filesystem backup (taring the entire mysql data directory and replication files, for instance) then your master info and relay-log will have the information on where the to start the replication from the master. This is what we are doing. I'm not 100% certain about using a mysqldump type program, but I suspect that you would need to reset the master logs after the backup to tell the slave to basically start from line 1. I dont know how you would ensure that the master would reset at the very last command that was backed up on the slave, perhaps someone using this type of slave/backup scenario could share some knowledge on the correct procedure. Regards, Scott Tanner Systems Administrator Rowe/AMi Gary Richardson [EMAIL PROTECTED] 10/08/2004 01:01 PM Please respond to Gary Richardson To: [EMAIL PROTECTED] cc: Subject:Restarting Replication from Backup Hey guys, I'm running a master/slave setup with v4.0.20. There are a hand full of databases being replicated. For backups, we stop replication on the slave and pull a text dump using mysqldump. I also record a 'SHOW SLAVE STATUS' from the time of the backup. My replica server crashed last night. It looks like it had something to do with the disk cache as the replica was trying to replay already committed transactions (lots of duplicate record errors). After running an integritty check on the servers, the row counts are out of sync for far more large tables than I care to manually fix. I'm thinking of: 1) deleting all the data on the replica 2) pulling a backup from a few days ago and re-importing it with replication disabled on the replica (ie, comment out all replication configuration directives). 3) artificially recreating master-info-file using the information from 'SHOW SLAVE STATUS' 4) restart the replica with replication turned back on With MySQL's two phase replication, will the IO thread automatically figure out what file to start downloading and where to resume? Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to extract Particular Tables from a Dump file taken with mysqldump
Hi, Please let me know how to extract few tables from a dump file, which is taken with mysqldump. I know it extracting a file. But the thing is I would like to do it with mysql. Thanks, Buchibabu
Re: data with dynamic schema stored in a column as a property list.
Thanks Shawn. I guess your suggestion maybe the only thing I can do about it.
But the problem itself has an interesting background:
I developed an web application handling dynamic online conference registrations;
including
a table BusinessSeason to hold the information about the registration specification
(one
record per event) and a table Participation to hold all the registration records. My
app reads
the registration spec and the registration record (the latter only exist for returning
users) to
generate web pages for user to edit/submit the registration data (preferences for
programs or
lodging etc.)
Records in BusinessSeason are for different events/registrations hence very different
in
terms of reg specification. And the future conference spec can be inserted to the
table and
you see why I just cannot have a fixed schema for the registration data. By using xml
or
plist or any kind of generic data storage, I can store the reg data into the
participation table
along with some standard attributes like event_id, submit_time, reg_id etc.
Now for the management reason, I need to get some statistics from the registration data
and that's why I have to query the column that holds the reg data as xml or plist text.
Things were not too bad as I tried for conferences around 500 people without index the
column. But I should make the database perform better whenver I can.
Thanks again for your help. Can your 2-step query can merge into 1?
Also, just out of curiosity, can oracle do such things? I'm kind of far away from
oracle but
not too long ago I learned there is no way that I can store long text and using sql
query
the text in oracle tables.
- Original Message -
From: [EMAIL PROTECTED]
To: Elim Qiu
Cc: MySQL mailing List
Sent: Friday, October 08, 2004 8:15 AM
Subject: Re: data with dynamic schema stored in a column as a property list.
Have you considered a combination of Full-text indexing (to quickly locate
a subset of records that may match your criteria) and regular expression
matching (to eliminate the non-matching results from the results of the
full-text search)? I know it's two steps but your data is practically
opaque to the database engine. The field names and the values you want to
search for exist as content, not as standalone fields or name/value pairs
of columns.
Without somehow converting your data stream into some kind of relational
structure, I think that you will be quite restricted in your searching
options.
Sorry I couldn't be more helpful,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Elim Qiu [EMAIL PROTECTED] wrote on 10/07/2004 11:14:49 PM:
Hi, instead of xml, i stored arbitrary data of the form
(the actual usage of such mechanism is for more fancy stuff,
say, dynamic configuration, otherwise this is really not necessary)
{
name = Fn, Ln; // string value
gender = F; // single word string
interests = (reading,drive fast); // array
children = (
{ lastName = Howe; firstName = Sam; gender = M; dob =
1994-10-07 16:59:26; },
{ lastName = Howe; firstName = Ann; gender = F; dob =
1998-01-26 04:09:12; }
);
creditCards = {
visa = XXX-x;
master = YY-;
};
}
This is called plist and the depth of the hierarchy can go arbitrary
deep (unknown limit). And it can be converted back
and forth from dictionary object by a framework.
My task is to find out ways of querying a column holds such text
data? say, find out whether there is certain key or
whether a key has certain value. I got some solution via regular
expression feature of MySQL.
The column type that I use is text. My question now is how to make
the whole thing perform good. In other words,
for regular expression querying, should I index the column for
performance? If so, what kind of index should I use?
Thanks a lot.
Re: select/query from two tables
You didn't try it, did you 8-).
In a nutshell a basic SELECT statement looks like:
SELECT /columns list/
FROM /tables list/
WHERE /conditions list/
The /columns list/ is where you specify all of the values you want from
the database, including constant and computed values
The /tables list/ is where you specify where the data comes from. If it
requires more than one table to provide your data, this is also where your
table JOINs occur.
The /where list/ defines the conditions each resulting row must meet in
order to be able to contribute it's values to those requested in the
/columns list/
Please refer to this URL for more details:
http://dev.mysql.com/doc/mysql/en/SELECT.html
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
leegold [EMAIL PROTECTED] wrote on 10/08/2004 01:12:17 PM:
On Fri, 8 Oct 2004 12:22:37 -0400, [EMAIL PROTECTED] said:
I think that this will work:
$query = SELECT page.*, url.* FROM `page` LEFT JOIN `keywords` USING
(`page_id`) LEFT JOIN URL USING (`page_id`) WHERE MATCH
(`keywords`.`keyword_txt`) AGAINST ('$keyword'
IN BOOLEAN MODE);
Sorry to bother I may be showing my lack, but the url table is a
different table from the page table so wouldn't it be folowing your
example:
$query = SELECT page.* FROM `page`, url.* FROM `url` LEFT JOIN
`keywords` USING??
Thanks again
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
leegold [EMAIL PROTECTED] wrote on 10/08/2004 12:06:17 PM:
A popular question, how to select/query from two tables. I googled
it
but am having trouble, wondered if anyone would answer this newbie
question. Here's my existing (PHP) query:
$query = SELECT page.* FROM `page` LEFT JOIN `keywords` USING
(`page_id`) WHERE MATCH (`keywords`.`keyword_txt`) AGAINST
('$keyword'
IN BOOLEAN MODE);
I want to SELECT from a new second table using page_id just like
I'm
doing now...they all are related by that page_id field. So i'm
already
selecting from a table called page and I want to select from a new
table called url the same i do for page at the same time. How do
I
modifiy my statement?
Thank you sincerely,
Lee G.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Re: Restarting Replication from Backup
Hey, The perl script that does the backup issues a SLAVE STOP just before it starts dumping. It also grabs SHOW SLAVE STATUS, which has a bunch of file positions and I'm pretty sure it's everything that is in the master.info file. The backup I'd be pulling is going to be at least a day old, so it will be out of sync and reseting the master will not help. Thanks. On Fri, 8 Oct 2004 13:24:00 -0400, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Gary, We go through the process of removing the slave DB and restoring from backup on a fairly regular basis (due to testing new functionality of our app). My first question would be about your backups - how are you doing them? If you are doing a filesystem backup (taring the entire mysql data directory and replication files, for instance) then your master info and relay-log will have the information on where the to start the replication from the master. This is what we are doing. I'm not 100% certain about using a mysqldump type program, but I suspect that you would need to reset the master logs after the backup to tell the slave to basically start from line 1. I dont know how you would ensure that the master would reset at the very last command that was backed up on the slave, perhaps someone using this type of slave/backup scenario could share some knowledge on the correct procedure. Regards, Scott Tanner Systems Administrator Rowe/AMi Gary Richardson [EMAIL PROTECTED] 10/08/2004 01:01 PM Please respond to Gary Richardson To:[EMAIL PROTECTED] cc: Subject:Restarting Replication from Backup Hey guys, I'm running a master/slave setup with v4.0.20. There are a hand full of databases being replicated. For backups, we stop replication on the slave and pull a text dump using mysqldump. I also record a 'SHOW SLAVE STATUS' from the time of the backup. My replica server crashed last night. It looks like it had something to do with the disk cache as the replica was trying to replay already committed transactions (lots of duplicate record errors). After running an integritty check on the servers, the row counts are out of sync for far more large tables than I care to manually fix. I'm thinking of: 1) deleting all the data on the replica 2) pulling a backup from a few days ago and re-importing it with replication disabled on the replica (ie, comment out all replication configuration directives). 3) artificially recreating master-info-file using the information from 'SHOW SLAVE STATUS' 4) restart the replica with replication turned back on With MySQL's two phase replication, will the IO thread automatically figure out what file to start downloading and where to resume? Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to extract Particular Tables from a Dump file taken with mysqldump
Why don't you use the mysqldump program to dump only those tables you want and not the entire database? http://dev.mysql.com/doc/mysql/en/mysqldump.html Cheers, --V Buchibabu wrote: Hi, Please let me know how to extract few tables from a dump file, which is taken with mysqldump. I know it extracting a file. But the thing is I would like to do it with mysql. Thanks, Buchibabu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: data with dynamic schema stored in a column as a property list.
Thanks for the history. It's always good to learn how these things come
into existence. There are some storage schemes for hierarchical
information that you may be able to apply to your needs.
Here is a good tutorial various methods:
http://www.sitepoint.com/article/hierarchical-data-database
(esp: the modified preorder method)
For performance reasons, I would not try to combine both queries into one.
Because you will be using regular expressions to locate substrings within
your stored data, indexes will be of no use to you for that portion of the
query. Preselecting those records that match a full-text index (maybe by
storing them in a temporary table) will greatly reduce the number of
records you will have to scan with your regular expressions. This way, at
least part of your query happens with an index assisting it.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Elim Qiu [EMAIL PROTECTED] wrote on 10/08/2004 01:41:33 PM:
Thanks Shawn. I guess your suggestion maybe the only thing I can do
about it.
But the problem itself has an interesting background:
I developed an web application handling dynamic online conference
registrations; including
a table BusinessSeason to hold the information about the
registration specification (one
record per event) and a table Participation to hold all the
registration records. My app reads
the registration spec and the registration record (the latter only
exist for returning users) to
generate web pages for user to edit/submit the registration data
(preferences for programs or
lodging etc.)
Records in BusinessSeason are for different events/registrations
hence very different in
terms of reg specification. And the future conference spec can be
inserted to the table and
you see why I just cannot have a fixed schema for the registration
data. By using xml or
plist or any kind of generic data storage, I can store the reg data
into the participation table
along with some standard attributes like event_id, submit_time, reg_id
etc.
Now for the management reason, I need to get some statistics from
the registration data
and that's why I have to query the column that holds the reg data as
xml or plist text.
Things were not too bad as I tried for conferences around 500 people
without index the
column. But I should make the database perform better whenver I can.
Thanks again for your help. Can your 2-step query can merge into 1?
Also, just out of curiosity, can oracle do such things? I'm kind of
far away from oracle but
not too long ago I learned there is no way that I can store long
text and using sql query
the text in oracle tables.
- Original Message -
From: [EMAIL PROTECTED]
To: Elim Qiu
Cc: MySQL mailing List
Sent: Friday, October 08, 2004 8:15 AM
Subject: Re: data with dynamic schema stored in a column as a property
list.
Have you considered a combination of Full-text indexing (to quickly
locate
a subset of records that may match your criteria) and regular
expression
matching (to eliminate the non-matching results from the results of
the
full-text search)? I know it's two steps but your data is
practically
opaque to the database engine. The field names and the values you want
to
search for exist as content, not as standalone fields or name/value
pairs
of columns.
Without somehow converting your data stream into some kind of
relational
structure, I think that you will be quite restricted in your searching
options.
Sorry I couldn't be more helpful,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Elim Qiu [EMAIL PROTECTED] wrote on 10/07/2004 11:14:49 PM:
Hi, instead of xml, i stored arbitrary data of the form
(the actual usage of such mechanism is for more fancy stuff,
say, dynamic configuration, otherwise this is really not necessary)
{
name = Fn, Ln; // string value
gender = F; // single word
string
interests = (reading,drive fast); // array
children = (
{ lastName = Howe; firstName = Sam; gender = M; dob =
1994-10-07 16:59:26; },
{ lastName = Howe; firstName = Ann; gender = F; dob =
1998-01-26 04:09:12; }
);
creditCards = {
visa = XXX-x;
master = YY-;
};
}
This is called plist and the depth of the hierarchy can go arbitrary
deep (unknown limit). And it can be converted back
and forth from dictionary object by a framework.
My task is to find out ways of querying a column holds such text
data? say, find out whether there is certain key or
whether a key has certain value. I got some solution via regular
expression feature of MySQL.
The column type that I use is text. My question now is how
Re: How to extract Particular Tables from a Dump file taken with mysqldump
They dump files are just mysql CREATE TABLE and INSERT statements. You can you a perl script to read the dump file line by line and switch output files when it hits a 'CREATE TABLE tablename' On Fri, 8 Oct 2004 23:14:07 +0530, Buchibabu [EMAIL PROTECTED] wrote: Hi, Please let me know how to extract few tables from a dump file, which is taken with mysqldump. I know it extracting a file. But the thing is I would like to do it with mysql. Thanks, Buchibabu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to extract Particular Tables from a Dump file taken with mysqldump
The actual problem is we maintain regular all-databases mysqldump.it is a automated script and after taking complete backup we purged data later we identified that some required data is missed in a particular table. so we require restore of only that particular table. we tried extracting only that particular table data using so awk and other stuff but it is taking very long time that table contain 120 million records. we want to know is there any mysql utility to extract only specified table from entire dump Thanks -Original Message- From: V. M. Brasseur [mailto:[EMAIL PROTECTED] Sent: Friday, October 08, 2004 11:41 PM To: Buchibabu Cc: [EMAIL PROTECTED] Subject: Re: How to extract Particular Tables from a Dump file taken with mysqldump Why don't you use the mysqldump program to dump only those tables you want and not the entire database? http://dev.mysql.com/doc/mysql/en/mysqldump.html Cheers, --V Buchibabu wrote: Hi, Please let me know how to extract few tables from a dump file, which is taken with mysqldump. I know it extracting a file. But the thing is I would like to do it with mysql. Thanks, Buchibabu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to extract Particular Tables from a Dump file taken with mysqldump
There is no MySQL-supplied utility for this purpose. Perl, awk, or some other scripting language will have to be called into play. --V Anil Doppalapudi wrote: The actual problem is we maintain regular all-databases mysqldump.it is a automated script and after taking complete backup we purged data later we identified that some required data is missed in a particular table. so we require restore of only that particular table. we tried extracting only that particular table data using so awk and other stuff but it is taking very long time that table contain 120 million records. we want to know is there any mysql utility to extract only specified table from entire dump Thanks -Original Message- From: V. M. Brasseur [mailto:[EMAIL PROTECTED] Sent: Friday, October 08, 2004 11:41 PM To: Buchibabu Cc: [EMAIL PROTECTED] Subject: Re: How to extract Particular Tables from a Dump file taken with mysqldump Why don't you use the mysqldump program to dump only those tables you want and not the entire database? http://dev.mysql.com/doc/mysql/en/mysqldump.html Cheers, --V Buchibabu wrote: Hi, Please let me know how to extract few tables from a dump file, which is taken with mysqldump. I know it extracting a file. But the thing is I would like to do it with mysql. Thanks, Buchibabu -- 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]
How would you make a smarter Search?
Hello all Here is my problem. I am searching titles in an article database. I have two titles: mouse cleaning and cleaning your computer Now If I do a search for cleaning mouse I get 0 results. If I do cleaning computer' I get 0 results. But If I do mouse cleaning, mouse, cleaning your,I get the articles. This is my query from a simple form input. The form value is called search_value $query = SELECT id,title, post, DATE_FORMAT(date,'%M %D, %Y') AS date FROM article WHERE title LIKE '%. $search_value .%' ORDER BY id DESC LIMIT $offset, $limit; My question is am I doing something wrong here? Do you have anytips on making a smart search work? This is the only way I was taught where you match the user input to something in the database. Thanks in advance. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How would you make a smarter Search?
I am interested in this too... Dan if you figure out a way I would be most interested... On Fri, 8 Oct 2004 14:50:49 -0400 (EDT), Dan Venturini [EMAIL PROTECTED] wrote: Hello all Here is my problem. I am searching titles in an article database. I have two titles: mouse cleaning and cleaning your computer Now If I do a search for cleaning mouse I get 0 results. If I do cleaning computer' I get 0 results. But If I do mouse cleaning, mouse, cleaning your,I get the articles. This is my query from a simple form input. The form value is called search_value $query = SELECT id,title, post, DATE_FORMAT(date,'%M %D, %Y') AS date FROM article WHERE title LIKE '%. $search_value .%' ORDER BY id DESC LIMIT $offset, $limit; My question is am I doing something wrong here? Do you have anytips on making a smart search work? This is the only way I was taught where you match the user input to something in the database. Thanks in advance. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How would you make a smarter Search?
Dan Venturini mailto:[EMAIL PROTECTED]
on Friday, October 08, 2004 11:51 AM said:
Now If I do a search for cleaning mouse I get 0 results. If I do
cleaning computer' I get 0 results. But If I do mouse cleaning,
mouse, cleaning your,I get the articles.
[snip]
My question is am I doing something wrong here? Do you have anytips on
making a smart search work? This is the only way I was taught where
you match the user input to something in the database.
well i think the principal is that you need to search for each word
individually, grouping them with AND. i had the same question but never
got around to working on it so i did a little investigation but came up
dry (so far).
i thought an easy way to do it would be to use the IN() function:
SELECT name
FROM products
WHERE name IN ('cleaning', 'computer')
but this doesn't work as it's looking for a name with exactly 'cleaning'
or exactly 'computer'. so i tried adding LIKE before the IN, but that's
just plain invalid. then i tried wrapping each item with % but although
it doesn't throw an error, that doesn't work either.
the only other thing i can think of (not that a better answer is not out
there of course) is to create a statement like the following:
SELECT name
FROM products
WHERE name LIKE '%cleaning%'
AND name LIKE '%computer%'
report back to the list if you find out anything else, or if anyone
would like to chime in and answer this.
chris.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Restarting Replication from Backup
I meant that it would be beneficial to do the reset on the master at the same time you issue the stop slave, so that the binary log file on the master would only contain updates that are not the backup. It would be very difficult to ensure data integrity though - if an update happened between the stop slave command and the reset master command. If your backup stores the information from the show slave status command ( Read_Master_Log_Pos ) then you should be recreate the master.info file, though I'm not sure what would need to be in the relay-log.info file. Scott Tanner Systems Administrator Rowe/AMi Gary Richardson [EMAIL PROTECTED] 10/08/2004 02:04 PM Please respond to Gary Richardson To: [EMAIL PROTECTED] [EMAIL PROTECTED] cc: [EMAIL PROTECTED] Subject:Re: Restarting Replication from Backup Hey, The perl script that does the backup issues a SLAVE STOP just before it starts dumping. It also grabs SHOW SLAVE STATUS, which has a bunch of file positions and I'm pretty sure it's everything that is in the master.info file. The backup I'd be pulling is going to be at least a day old, so it will be out of sync and reseting the master will not help. Thanks.
Re: How to extract Particular Tables from a Dump file taken with mysqldump
Anil Doppalapudi wrote: The actual problem is we maintain regular all-databases mysqldump.it is a automated script and after taking complete backup we purged data later we identified that some required data is missed in a particular table. so we require restore of only that particular table. we tried extracting only that particular table data using so awk and other stuff but it is taking very long time that table contain 120 million records. You could restore the database to a different machine and then use mysqldump to extract the table you want. Alternatively, restore it to the original machine as a different name, and copy the target table back to the original DB. HTH, -- Hassan Schroeder - [EMAIL PROTECTED] Webtuitive Design === (+1) 408-938-0567 === http://webtuitive.com dream. code. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How would you make a smarter Search?
Hello...
here you have, in my opinion, a nice solution. To see what kind of search options you
can
use, visit my site, click on Search Tips on the Internal Search Engine.
Here is the query:
select column1, match(column1) against ('+orange -fruit' IN BOOLEAN MODE) as score
from some_table where match(column1) against('+orange -fruit' IN BOOLEAN MODE)
order by score desc
This way you have your search results ordered by relevance, and you also get the
relevance value in
the result if you want to.
- If you use one word in your search, 1 is a probable score, because all the results
that
appear have the same relevance (they all contain that word!).
- If you use two words, where the second isn't present in all results, you shouldn't
get
relevance value 1 in all results, bacause some entries are more relevant than others
- If you use the example query (+orange -fruit) it's also natural that the relevance
value is 1,
because it's a very strict query.
With this simple method, MySQL takes care of everything that a basic search engine
needs.
Note that searching for words with 3 letters or less will not produce any result. You
could
take a look that the MySQL Manual for furher information (Match... Against).
Note that if you are using, say PHP, you should put '$search_string' in the place of
'+orange
-fruit', where $search_string is the search string the user inserted in the textfield
to perform
the search. It is important that you do NOT OMIT the ' '.
TIP. You should be able to perform this search on various columns at the same time, as
long as they belong to the SAME TABLE. This way, searching title, description, etc.
Again,
take a look at the MySQL Manual.
Remi Mikalsen
E-Mail: [EMAIL PROTECTED]
URL:http://www.iMikalsen.com
On 8 Oct 2004 at 14:50, Dan Venturini wrote:
Hello all Here is my problem.
I am searching titles in an article database.
I have two titles:
mouse cleaning
and
cleaning your computer
Now If I do a search for cleaning mouse I get 0 results. If I do
cleaning computer' I get 0 results. But If I do mouse cleaning, mouse,
cleaning your,I get the articles.
This is my query from a simple form input. The form value is called
search_value
$query = SELECT id,title, post, DATE_FORMAT(date,'%M %D, %Y') AS date
FROM article WHERE title LIKE '%. $search_value .%' ORDER BY id DESC
LIMIT $offset, $limit;
My question is am I doing something wrong here? Do you have anytips on
making a smart search work? This is the only way I was taught where you
match the user input to something in the database.
Thanks in advance.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How would you make a smarter Search?
Have you considered creating a full text index on that field?
http://dev.mysql.com/doc/mysql/en/CREATE_INDEX.html
http://dev.mysql.com/doc/mysql/en/Fulltext_Search.html
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Chris W. Parker [EMAIL PROTECTED] wrote on 10/08/2004 04:11:07 PM:
Dan Venturini mailto:[EMAIL PROTECTED]
on Friday, October 08, 2004 11:51 AM said:
Now If I do a search for cleaning mouse I get 0 results. If I do
cleaning computer' I get 0 results. But If I do mouse cleaning,
mouse, cleaning your,I get the articles.
[snip]
My question is am I doing something wrong here? Do you have anytips on
making a smart search work? This is the only way I was taught where
you match the user input to something in the database.
well i think the principal is that you need to search for each word
individually, grouping them with AND. i had the same question but never
got around to working on it so i did a little investigation but came up
dry (so far).
i thought an easy way to do it would be to use the IN() function:
SELECT name
FROM products
WHERE name IN ('cleaning', 'computer')
but this doesn't work as it's looking for a name with exactly 'cleaning'
or exactly 'computer'. so i tried adding LIKE before the IN, but that's
just plain invalid. then i tried wrapping each item with % but although
it doesn't throw an error, that doesn't work either.
the only other thing i can think of (not that a better answer is not out
there of course) is to create a statement like the following:
SELECT name
FROM products
WHERE name LIKE '%cleaning%'
AND name LIKE '%computer%'
report back to the list if you find out anything else, or if anyone
would like to chime in and answer this.
chris.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Restarting Replication from Backup
Hey guys, I think I have this figured out. I'm just doing some testing. If I manually recreate the master.info file, it sort of works, but I get the following error from the IO thread: Error reading relay log event: slave SQL thread aborted because of I/O error So, instead of manually creating the replicatin index and info files, just delete them. Then edit your my.cnf and make your master connection info invalid in some way -- use a bogus host or change the username or password. Basically, you want to be able to start your server with replication without replicating. From there, start your server, issue a SLAVE STOP and then a CHANGE MASTER TO statement to fix it all. This will change the master server and you can specify the log file position and the log file you are working on. This looks like it works -- I'm still using the corrupt data so I'm getting duplicate queries, but the slave seems to start and run. Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help for a query with MYSQL 3.23.58
Could you help me with a query? 1) I know some values of id_value in the table below : PRODUCTS_VALUE. 2) I'm looking for id_product that can contemporaneously satisfy more than an id_value (NOT ONLY ONE!!) For example I'm looking for all id_product that can contemporaneously satisfy (id_value=1 AND id_value=3 AND id_value=16) expected results :id_product = 1 and 2 but NON id_product= 3! (that can satisfy only id_value = 1 and 16 but not 3!) 3) How can I obtain Company's names (FROM TABLE Companies joining PRODUCTS joining(??) PRODUCTS_VALUE ) that sell product satisfying point 1) and 2) ? 4) My internet provider provide only MySQL 3.23.58 ... So not subqueries e so on ... I' think it was easy but I've tried in many way without result TIA. Sorry for my poor english. Michele.B ### TABLES ### PRODUCTS_VALUE id_product id_value 11 13 116 125 21 23 216 232 31 32 316 PRODUCTS id_product name id_company 1 prod_1 2 2 prod_2 3 3 prod_3 9 COMPANIES id_company name 1 company_1 2 company_2 3 company_3 4 company_4 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Help for a query with MYSQL 3.23.58
Michele mailto:[EMAIL PROTECTED] on Friday, October 08, 2004 3:23 PM said: 2) I'm looking for id_product that can contemporaneously satisfy more than an id_value (NOT ONLY ONE!!) contemporaneously? that's got be one of the best made up wor... wait what? you mean it's a real word? http://dictionary.reference.com/search?q=contemporaneously :\ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Restarting Replication from Backup
On Friday 08 October 2004 19.01, Gary Richardson wrote: Hey guys, I'm running a master/slave setup with v4.0.20. There are a hand full of databases being replicated. For backups, we stop replication on the slave and pull a text dump using mysqldump. I also record a 'SHOW SLAVE STATUS' from the time of the backup. My replica server crashed last night. It looks like it had something to do with the disk cache as the replica was trying to replay already committed transactions (lots of duplicate record errors). With MySQL's two phase replication, will the IO thread automatically figure out what file to start downloading and where to resume? Nothing is automagic but if you did 1. stop slave; 2. show slave status; and recorded the info before you did your backup you would restore the backup data and start replication after jumping to the correct position on the slave with the following: CHANGE MASTER TO MASTER_LOG_FILE='george-bin.5528', -- Relay_Master_Log_File MASTER_LOG_POS=290303997; -- Exec_master_log_pos this command will purge any relay logs and reset the slave thread to the requested position. It can be a good idea to always have skip-slave-start set in your .cnf file on the slaves. consider just copying/tar'ing the mysql datadir - raw file backup and restore are more efficient than mysqldumps. -- ___ |K | Ongame e-Solutions AB | /\| Mikael Fridh / Technical Operations |_\/| tel: +46 18 606 538 / fax: +46 18 694 411 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Restarting Replication from Backup
Yeah, that's exactly what I figured out.. We do record the SHOW SLAVE STATUS settings before each backup. I find we need individual tables restored far more frequently than whole databases. It's much easier using mysqldump and perl to dump each table in text to its own file. This is especially true when you are mixing table types or using InnoDB with table files. On Sat, 9 Oct 2004 00:48:37 +0200, Mikael Fridh [EMAIL PROTECTED] wrote: On Friday 08 October 2004 19.01, Gary Richardson wrote: Hey guys, I'm running a master/slave setup with v4.0.20. There are a hand full of databases being replicated. For backups, we stop replication on the slave and pull a text dump using mysqldump. I also record a 'SHOW SLAVE STATUS' from the time of the backup. My replica server crashed last night. It looks like it had something to do with the disk cache as the replica was trying to replay already committed transactions (lots of duplicate record errors). With MySQL's two phase replication, will the IO thread automatically figure out what file to start downloading and where to resume? Nothing is automagic but if you did 1. stop slave; 2. show slave status; and recorded the info before you did your backup you would restore the backup data and start replication after jumping to the correct position on the slave with the following: CHANGE MASTER TO MASTER_LOG_FILE='george-bin.5528', -- Relay_Master_Log_File MASTER_LOG_POS=290303997; -- Exec_master_log_pos this command will purge any relay logs and reset the slave thread to the requested position. It can be a good idea to always have skip-slave-start set in your .cnf file on the slaves. consider just copying/tar'ing the mysql datadir - raw file backup and restore are more efficient than mysqldumps. -- ___ |K | Ongame e-Solutions AB | /\| Mikael Fridh / Technical Operations |_\/| tel: +46 18 606 538 / fax: +46 18 694 411 -- 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]
Convert subselect query to pre-subselect query
Hi Everyone, I got excited when I discovered subselects, but quickly discovered that 4.1 is still in gamma and I can't put it onto my production server. The query I wanted to use would be great, so maybe there's a way to convert it - since the manual says most subselects can be done with joins. I'm not sure how I'd do it though and figured I'd see if anyone here knows how. Here's the subselect that I'd like to use: select ID from products where CategoryID = (select ID from categories where ID='21' OR ParentID = '21' ) How would I would I create a query using joins that accomplishes the same result? Here's what I was attempting, in case it's close, but it's erroring out: select ID from products right join categories where (categories.ID = '21' or categories.ParentID='21') AND (products.CategoryID = categories.ID or products.CategoryID = categories.ParentID) Thanks, Ed -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to extract Particular Tables from a Dump file taken with mysqldump
On Friday 08 October 2004 20.39, Anil Doppalapudi wrote:
we want to know is
there any mysql utility to extract only specified table from entire dump
This script will probably mess up on binary data and stuff but it works fine
for plain text dumps... try mysqldumpgrep mysql user a_dump.sql.
Even if the code sucks, i think you get the idea.
#!/usr/bin/perl -w
# Usage: ./mysqldumpgrep database table
# No warranty - Not even if it kills grandma's SAN storage solution.
# Author: [EMAIL PROTECTED]
if ($#ARGV != 1) {
print STDERR Usage: $0 database table\n;
exit 1;
}
$db=0;
$table=0;
$dbstring = qr/^CREATE DATABASE .* $ARGV[0];/;
$tablestring = qr/^CREATE TABLE $ARGV[1]\b/;
while (STDIN) {
if ($db==0) {
if (/$dbstring/) {
$db=1;
next;
}
}
if ($db) {
if ($table==0) {
if (/$tablestring/) {
$table=1;
print;
next;
}
}
if ($table) {
exit if /^CREATE/;
print;
}
}
}
--
___
|K | Ongame e-Solutions AB
| /\| Mikael Fridh / Technical Operations
|_\/| tel: +46 18 606 538 / fax: +46 18 694 411
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help for a query with MYSQL 3.23.58
2) I'm looking for id_product that can contemporaneously satisfy more than an id_value (NOT ONLY ONE!!) contemporaneously? that's got be one of the best made up wor... wait what? you mean it's a real word? http://dictionary.reference.com/search?q=contemporaneously I'm not sure to understand your answer. Sorry for my poor english. I meaned : contemporaneously = at the same time = only with a single query ( if possible) or otherwise in an other simple way I should handle the result data with php. Have I well understood the meaning of your answer ? TIA. MB -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Convert subselect query to pre-subselect query
Try this... select a.ID from products a, categories b where a.CategoryID=b.ID and (b.ID='21' OR b.ParentID='21') The query is pretty straigthforward and I believe it's quite easy to understand. Hope this is what you wanted! A tip... only use left and right joins whenever you want what is on the left or right side of the join in your results, even if you can't get any matches on the opposite side. For example (I'll try to make it simple!): imagine you have a Video Club.You have movies, and you have loans. Normally, movies in one table (the left table in this example) and loans in different table (the right table). Now, imagine you would like to print a list of ALL movies in your database, and ALSO list all loans for each movie. You could do a LEFT JOIN similar to this: select movies.*, loans.* from movies left join loans on (movies.id = loans.movieid) order by movies.title, loans.loan_date desc. The result would be a list with ALL movies in your database, ordered by the movie title. Each title would appear once for every loan; the loans would be ordered within the movie title, last first. Now, here is the particularity with LEFT JOINS: whenever a movie has NEVER been on loan, it will not be possible to get any values from the loans table. With an INNER JOIN (the type of join I suggest for your problem), the movie would be omitted in the query result, but with a LEFT JOIN, the movie and all its columns will appear once - but the loans columns corresponding to that movie would appear all NULL. Got it? hope I explained more or less well... Honestly, I almost never use LEFT and RIGHT joins, thus, the syntax MIGHT be wrong (I was too lazy to check in the manual :) ) - I'm not a pro in MySQL, so if I explained something wrong, I hope the more experienced members will correct it. Remi Mikalsen E-Mail: [EMAIL PROTECTED] URL:http://www.iMikalsen.com On 8 Oct 2004 at 16:30, Ed Lazor wrote: Hi Everyone, I got excited when I discovered subselects, but quickly discovered that 4.1 is still in gamma and I can't put it onto my production server. The query I wanted to use would be great, so maybe there's a way to convert it - since the manual says most subselects can be done with joins. I'm not sure how I'd do it though and figured I'd see if anyone here knows how. Here's the subselect that I'd like to use: select ID from products where CategoryID = (select ID from categories where ID='21' OR ParentID = '21' ) How would I would I create a query using joins that accomplishes the same result? Here's what I was attempting, in case it's close, but it's erroring out: select ID from products right join categories where (categories.ID = '21' or categories.ParentID='21') AND (products.CategoryID = categories.ID or products.CategoryID = categories.ParentID) Thanks, Ed -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Help for a query with MYSQL 3.23.58
Michele mailto:[EMAIL PROTECTED] on Friday, October 08, 2004 4:57 PM said: Have I well understood the meaning of your answer ? It was meant as a joke and not a serious response to your question. I apologize for the confusion. Chris. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Convert subselect query to pre-subselect query
Thanks Remi =) I just ran a test and it worked. Honestly, I'm still a little stumped on why it works, but I'll keep playing with it for a while to see if I can get it. I'll either eventually figure it out or come back in frustration asking for more help ;) hehe -Original Message- From: Remi Mikalsen [mailto:[EMAIL PROTECTED] Sent: Friday, October 08, 2004 5:07 PM To: [EMAIL PROTECTED] Subject: Re: Convert subselect query to pre-subselect query Try this... select a.ID from products a, categories b where a.CategoryID=b.ID and (b.ID='21' OR b.ParentID='21') The query is pretty straigthforward and I believe it's quite easy to understand. Hope this is what you wanted! A tip... only use left and right joins whenever you want what is on the left or right side of the join in your results, even if you can't get any matches on the opposite side. For example (I'll try to make it simple!): imagine you have a Video Club.You have movies, and you have loans. Normally, movies in one table (the left table in this example) and loans in different table (the right table). Now, imagine you would like to print a list of ALL movies in your database, and ALSO list all loans for each movie. You could do a LEFT JOIN similar to this: select movies.*, loans.* from movies left join loans on (movies.id = loans.movieid) order by movies.title, loans.loan_date desc. The result would be a list with ALL movies in your database, ordered by the movie title. Each title would appear once for every loan; the loans would be ordered within the movie title, last first. Now, here is the particularity with LEFT JOINS: whenever a movie has NEVER been on loan, it will not be possible to get any values from the loans table. With an INNER JOIN (the type of join I suggest for your problem), the movie would be omitted in the query result, but with a LEFT JOIN, the movie and all its columns will appear once - but the loans columns corresponding to that movie would appear all NULL. Got it? hope I explained more or less well... Honestly, I almost never use LEFT and RIGHT joins, thus, the syntax MIGHT be wrong (I was too lazy to check in the manual :) ) - I'm not a pro in MySQL, so if I explained something wrong, I hope the more experienced members will correct it. Remi Mikalsen E-Mail: [EMAIL PROTECTED] URL: http://www.iMikalsen.com On 8 Oct 2004 at 16:30, Ed Lazor wrote: Hi Everyone, I got excited when I discovered subselects, but quickly discovered that 4.1 is still in gamma and I can't put it onto my production server. The query I wanted to use would be great, so maybe there's a way to convert it - since the manual says most subselects can be done with joins. I'm not sure how I'd do it though and figured I'd see if anyone here knows how. Here's the subselect that I'd like to use: select ID from products where CategoryID = (select ID from categories where ID='21' OR ParentID = '21' ) How would I would I create a query using joins that accomplishes the same result? Here's what I was attempting, in case it's close, but it's erroring out: select ID from products right join categories where (categories.ID = '21' or categories.ParentID='21') AND (products.CategoryID = categories.ID or products.CategoryID = categories.ParentID) Thanks, Ed -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Convert subselect query to pre-subselect query
Doh... Guess I spoke too soon. I get it now. I wasn't seeing a.CategoryID=b.ID =) -Original Message- select a.ID from products a, categories b where a.CategoryID=b.ID and (b.ID='21' OR b.ParentID='21') -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How Do I Determine the Server's Version on Old Server?
Hey guys, I see there is a mailing list for all other API's except the built in one. Hmmm. Anyway, I'm writing an app in C that will connect to a MySQL database server and run some queries. I'd like to use prepared statements in some cases but since prepared statements are only available in 4.1 or higher I need to find out what the server's version is first. Normally I could just check MYSQL_VERSION_ID and if it is greater or equal to 40100 then I can use the prepared statements. But, in my case, I'm using the 4.1 client libraries but the test server I'm connecting to is 3.23.50 which will not support prepared statements. But if I use my MYSQL_VERSION_ID method, everything will seem ok and the compiler will put in the unusable 4.1 code. I found this function on the docs: mysql_get_server_version() However, the docs say this was added in 4.1. So, how do I find the server's version that I am connected to if the function I need to use won't work on pre 4.1 and the VERSION_ID check won't work for different client-server versions? Thanks, Matthew -- Matthew Boehm [EMAIL PROTECTED] The University of Texas at Austin, Department of Geography Why did the prison use Windows2K as a guard? Because it always locks up! ?PHP echo PHP kicks ASP!; ? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
