ANN: Gtk2::Ex::DBI-0.4
Hi all. I'm pleased to announce the 4rd release of Gtk2::Ex::DBI ... http://enthalpy.homelinux.org/Gtk2-Ex-DBI/ Gtk2::Ex::DBI is a helper object that makes your gtk2-perl apps data aware. This is mainly a bugfix release ... the previous version was BADLY broken. Changes in this release: - Fixed 2 *nasty* bugs in apply method that were preventing the fieldlist from being processed and applied to DB Server or in-memory recordset - Added 'delete' method Dan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql optimizing large table
Hi, On Thursday 21 October 2004 22:00, Dathan Vance Pattishall wrote: My.cnf.huge is not good enough for your system specs. Calculate the Key efficiency from the show status command. I bet the key efficiency is less then 90% or so. By my count it is 99.88, the status output is: Key_blocks_used 375052 Key_read_requests 1022090447 Key_reads 1219141 Key_write_requests 262155905 Key_writes 47437589 In this case increase the key_buffer_size try 512M. A good stat for a proper key_buffer_size in the sum of all index files block size. This would be optimal since the index remains in memory. Increase your tmp_table_size to 64 MB your prob going to tmp_table and mysql uses this buffer for some internal optimizations. Also try increasing range alloc block size a little bit, you might see a 5% perf boost. ok, i changed these settings. When I restart mysqld I get some error: 041021 0:09:05 Warning: setrlimit couldn't increase number of open files to more than 1024 (request: 1134) 041021 0:09:05 Warning: Changed limits: max_connections 100 table_cahce 457 Mysql runs normally though. Would they be relevant here? It seems to be quite a bit faster already actually... Thanks for your help, Richard . -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysql optimizing large table
If you don't want to have those error messages: Increase the value of Open File using ulimit shell command. Do a ulimit -a to see the current value Do a ulimit -n newvalue to increase it You can also modify the /etc/limits file Marc. -Message d'origine- De : Richard Bennett [mailto:[EMAIL PROTECTED] Envoyé : vendredi 22 octobre 2004 00:48 À : [EMAIL PROTECTED] Objet : Re: mysql optimizing large table Hi, On Thursday 21 October 2004 22:00, Dathan Vance Pattishall wrote: My.cnf.huge is not good enough for your system specs. Calculate the Key efficiency from the show status command. I bet the key efficiency is less then 90% or so. By my count it is 99.88, the status output is: Key_blocks_used 375052 Key_read_requests 1022090447 Key_reads 1219141 Key_write_requests 262155905 Key_writes 47437589 In this case increase the key_buffer_size try 512M. A good stat for a proper key_buffer_size in the sum of all index files block size. This would be optimal since the index remains in memory. Increase your tmp_table_size to 64 MB your prob going to tmp_table and mysql uses this buffer for some internal optimizations. Also try increasing range alloc block size a little bit, you might see a 5% perf boost. ok, i changed these settings. When I restart mysqld I get some error: 041021 0:09:05 Warning: setrlimit couldn't increase number of open files to more than 1024 (request: 1134) 041021 0:09:05 Warning: Changed limits: max_connections 100 table_cahce 457 Mysql runs normally though. Would they be relevant here? It seems to be quite a bit faster already actually... Thanks for your help, Richard . -- 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: mysql optimizing large table
On Friday 22 October 2004 11:10, Mechain Marc wrote: If you don't want to have those error messages: Increase the value of Open File using ulimit shell command. Do a ulimit -a to see the current value Do a ulimit -n newvalue to increase it You can also modify the /etc/limits file Marc. Thanks a lot - that fixed those errors. Richard. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Partial Row Reads?
In article [EMAIL PROTECTED], Ken Gieselman [EMAIL PROTECTED] writes: The second issue is query performance. It seems that regardless of what fields are selected, it reads the entire row? Since a monthly table averages 840GB, this takes a while, even on a well-organized query like 'Select c1 from raddata_2004_10 where granID between 147568 and 15' (granID tracks the granules from the start of the mission, and is a calculated value based on the date/time of the data in the file -- incrementing over time. The 2500 granules in the example above represent about 1/3 of a month -- 10 days or so). Even making sure it's using the index to do the query (which it does anyhow, for the above query, according to explain), the current behavior makes it reads 29548800 complete rows, which is 275.4 GB of data to read, even though the desired return is about 1/2500th of that (112.7 MB). Is there a kind of access pattern? Do you access c1 often in conjunction with (e.g.) c2 and c3, but hardly ever with c4 or c5? If so, you might use vertical partitioning in addition to the horizontal partitioning you're currently doing. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Commercial Licensing Question
You may direct your question to [EMAIL PROTECTED] -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [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: Help with ALTER TABLE error
John Stile [EMAIL PROTECTED] wrote: I have Mysql Cluster (version 4.1.16-gama) running, and now I need to convert database tables from engine MyISAM to NDBCLUSTER, but ALTER TABLE fails on some tables. Does anyone know what the error means or how to get around it? mysql use database1; mysql alter table attr engine=NDB; ERROR 1005: Can't create table './database1/#sql-4627_3a.frm' (errno: 4009) Seems weird. Can you check permissions in datadir/database1 and/or create a testcase for us? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [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_config (4.0.21) returning the wrong socket location
No it's not a bug. Look up the DBI documentation and change the mysql_sock= location in your code OR in /etc/my.cnf define the mysqld.sock file to be stored in /tmp. Hmmm... It's not a bug but I have to adapt to it? There is something wrong with mysql_config not returning the right location of the socket, unless mysql_config has been designed to explicitely return the location of the socket at the configuration time (which will be the default one if none has been specified with --with-unix-socket-path). There we go: --socket The default Unix socket file, defined when configuring MySQL. So we better use --with-unix-socket-path when we don't want to use the default socket. Christian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql-administrator
spiv007 [EMAIL PROTECTED] wrote: Has anyone seen this before with mysql-administrator? /usr/local/bin/mysql-administrator: line 9: 19856 Segmentation fault $MYPATH/mysql-administrator-bin I get this everytime i go to schema privileges to assian a user rights. Can you create a simple testcase for this and send it to me? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [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: Sharing database files between EMBEDDED MYSQL and MySQL Server
Valdir Henrique Dias Leite [EMAIL PROTECTED] wrote: I have an application that uses mysql embedded (performance issues ...). The app itself works fine, but data adminsitration (issuing DDL, DML statements ) is very complicated using C API... So, I created an entry in my.cnf pointing to directory where database used by embedded is, aiming to use mysql interface to perform queries, updates, et cetera. The problem is: Every changes I make using mysql interface, don=B4t reflect inside embedded scope (for example, inserts made by mysql don=B4t exists in queries run by embedded application). My questions are: Is it possible to have a situation like mine ? There is an isolation between two aplications (mysqld and mysqlembedded) ? May I mix scenarios as I wonder ? What is wrong ? Read manual on flush tables and lock tables: http://dev.mysql.com/doc/mysql/en/LOCK_TABLES.html http://dev.mysql.com/doc/mysql/en/FLUSH.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [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: Odd MySQL error
Rich West [EMAIL PROTECTED] wrote: Check limits for the MySQL user account in Fedora. Check that the init script is original from MySQL, not the Fedora one. Tail the dmesg output and look for suspictious messages. I have searched the archives (well, the search never came back...), and I have searched the web, but found very little that could help with the scenario that we are currently experiencing. We have the 4.0.21 RPM's installed direct from MySQL.com on a Fedora Core 2 server. The previous incarnation of this server was a Fedora Core 1 server which ran flawlessly. Since the new server has been put in to production, on a daily basis (random times), we are forced to restart mysql because all processes which attempt to get data out of the databases fail. We access the database through Apache+PHP+MySQL as well as via the Perl DBI interface.. Everything comes back after a restart... In the logs, all we see is: 041018 20:32:50 mysqld started 041018 20:32:51 Warning: Asked for 196608 thread stack, but got 126976 041018 20:32:51 InnoDB: Started /usr/sbin/mysqld: ready for connections. Version: '4.0.21-standard' socket: '/var/lib/mysql/mysql.sock' port: 3306 Official MySQL RPM 041020 16:20:35 Error in accept: Too many open files 041020 16:33:23 /usr/sbin/mysqld: Normal shutdown 041020 16:33:24 InnoDB: Starting shutdown... 041020 16:33:27 InnoDB: Shutdown completed 041020 16:33:27 /usr/sbin/mysqld: Shutdown Complete 041020 16:33:27 mysqld ended I did find that the Warning can be safely ignored. However, the problem definitely is at the Error in accept: Too many open files line... the rest of the system is behaving happily, so I am not sure where to go from here.. /proc/sys/fs/file-max comes back with an astronomical 76949, and sysctl shows nominal activity: fs.file-max = 76949 fs.file-nr = 3760 0 76949 fs.inode-state = 17709 26290 0 0 0 0 fs.inode-nr = 17709 2629 A couple of related postings seemed to indicat that it had to do with the mysql process and/or the limits set upon the mysql user account, but that was all speculation. Ideas, Comments, Suggestions are all more than welcome! -Rich -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [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: Error Starting MySQL 4.0.21
Jason Williard [EMAIL PROTECTED] wrote: I have a new linux server that I just installed the mysql-max-4.0.21-pc-linux-i686 binary on. The installation had no errors. However, it is not starting on startup because of the following error: Can't execute ./bin/mysqld_safe from dir /var/lib I can start MySQL manually running '/usr/local/mysql/bin/mysqld_safe '. Does anyone know why I am getting this error message? Because you have to start it using the full path: /usr/local/mysql/bin/mysqld_safe If you are running rpm-based linux, better take the RPM version. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [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: Revert a column to its default value
Jonathan Kart [EMAIL PROTECTED] wrote: Hi all, Is it possible in mysql 3.23 to update a row and revert a column back to its default value. Not the default value for the type, but the value defined as the column default. [] UPDATE pictures SET picture_name=DEFAULT; -- don't think this is possible It works in 4.1. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [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 server is taking all my hardrive space
Most likely, binary logs are populating the space. Read http://dev.mysql.com/doc/mysql/en/PURGE_MASTER_LOGS.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [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: Importing Excel Data in MySql
See http://dev.mysql.com/doc/mysql/en/LOAD_DATA.html Hope that helps. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [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 innoDB
Stefan Gnann [EMAIL PROTECTED] wrote: I have a mysql database 4.0.15 on a suse linux 9.x system running. First, upgrade to .20 version officially built by MySQL AB (http://dev.mysql.com/). RPM version is easy to install and run. Now we have to use the features of InnoDB tables (rollback, a.s.o.). Second, setup InnoDB: http://dev.mysql.com/doc/mysql/en/InnoDB_configuration.html http://dev.mysql.com/doc/mysql/en/InnoDB_start.html Up to now we use the standard table type MyISAM. Now I want to change the tabel type with the command ALTER TABLE x TYPE = InnoDB. The command doesn´t show any effect !!! Third, check that InnoDB is enabled: SHOW VARIABLES LIKE 'have_innodb' and look for InnoDB to be enabled. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [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: SELECT... INTO OUTFILE problem
Jay Blanchard [EMAIL PROTECTED] wrote: [snip] Exception occured in Microsoft OLE DB Provider for ODBC Drivers, [MySQL][ODBC 3.51 Driver][mysqld-4.0.20a-nt]Can't create/write to file 'C:\data_out\day234\data_out.txt' (Errcode: 2) [EMAIL PROTECTED] egor]$ perror 2 System error: 2 = No such file or directory -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [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]
Index not used for select distinct?
We have a slight opimization problem here.
Given this table:
CREATE TABLE `test` (
`IP` varchar(15) collate latin1_german1_ci NOT NULL default '',
`Type` enum('WARN','ERROR','FATAL','UNKNOWN') collate latin1_german1_ci NOT NULL
default 'WARN',
`epoch` int(10) unsigned NOT NULL default '0',
KEY `Trap` (`IP`,`Type`,`epoch`),
KEY `IP` (`IP`)
)
... containing ten million records; the IP column holds only a handful
of distinct values. Given this, I would expect a select distinct ip
to return immediately.
However,
explain select distinct ip from test;
++-+---+---+---+--+-+--+--+-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows
| Extra |
++-+---+---+---+--+-+--+--+-+
| 1 | SIMPLE | test | index | NULL | IP | 15 | NULL | 10991123
| Using index |
++-+---+---+---+--+-+--+--+-+
takes a *long* time and obviously scans the whole table.
Ideas, anybody?
MyISAM vs. InnoDB behave identically.
4.0 or 4.1.5 also didn't make a difference; I didn't test 5.0, as this
is supposed to be a production system.
--
Matthias Urlichs | {M:U} IT Design @ m-u-it.de | [EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Index not used for select distinct?
[snip]
We have a slight opimization problem here.
Given this table:
CREATE TABLE `test` (
`IP` varchar(15) collate latin1_german1_ci NOT NULL default '',
`Type` enum('WARN','ERROR','FATAL','UNKNOWN') collate
latin1_german1_ci NOT NULL default 'WARN',
`epoch` int(10) unsigned NOT NULL default '0',
KEY `Trap` (`IP`,`Type`,`epoch`),
KEY `IP` (`IP`)
)
... containing ten million records; the IP column holds only a handful
of distinct values. Given this, I would expect a select distinct ip
to return immediately.
Ideas, anybody?
[/snip]
Because IP is not an INDEX, only a KEY, just a guess...
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
PRODUCT ENQUIRY
HELLO SALES, THIS IS KGLOBAL SERVICE INC.I WANT YOUR COMPANY TO QOUTE THE FOLLOWING ITEMS FOR MY COMPANY: 1. 200 PIECES OF C6578DN INKJET CATRIDGES (OEM) 2. 2 PIECES OF HPNX9010 PENTIYM 4 NOTEBOOK. 3. 50 PIECES OF 512MB DDR PC2700 33MHz MEMORY(KINGSTON). AND ALSO I WANT YOU TO CALCULATE THE SHIPPING COST TO LONDON,UK, SE8 3QY.MOREOVER SEND ALONG YOUR COMPANY PAYMENT METHODS,HOPE TO HEAR FROM YOU SOONEST. BEST REGARDS. - ALL-NEW Yahoo! Messenger - all new features - even more fun!
Re: Index not used for select distinct?
Matthias Urlichs wrote:
We have a slight opimization problem here.
Given this table:
CREATE TABLE `test` (
`IP` varchar(15) collate latin1_german1_ci NOT NULL default '',
`Type` enum('WARN','ERROR','FATAL','UNKNOWN') collate latin1_german1_ci NOT NULL
default 'WARN',
`epoch` int(10) unsigned NOT NULL default '0',
KEY `Trap` (`IP`,`Type`,`epoch`),
KEY `IP` (`IP`)
Your second key is redundant.
Your Key file is larger than your data file.
It would have to scan the whole keyfile anyway, so it is faster to scan
the table.
)
... containing ten million records; the IP column holds only a handful
of distinct values. Given this, I would expect a select distinct ip
to return immediately.
However,
explain select distinct ip from test;
++-+---+---+---+--+-+--+--+-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows
| Extra |
++-+---+---+---+--+-+--+--+-+
| 1 | SIMPLE | test | index | NULL | IP | 15 | NULL | 10991123
| Using index |
++-+---+---+---+--+-+--+--+-+
takes a *long* time and obviously scans the whole table.
Ideas, anybody?
MyISAM vs. InnoDB behave identically.
4.0 or 4.1.5 also didn't make a difference; I didn't test 5.0, as this
is supposed to be a production system.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Index not used for select distinct?
Hi,
gerald_clark:
KEY `Trap` (`IP`,`Type`,`epoch`),
KEY `IP` (`IP`)
Your second key is redundant.
True (there's a UNIQUE missing in there).
Deleting the first key, however, doesn't change anything.
--
Matthias Urlichs | {M:U} IT Design @ m-u-it.de | [EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Conditional statement: IF
Hi, I'm trying to write a conditional statement in my query. Is the following possible in MySQL? IF(@SomeValue = 1) THEN SELECT The value is 1; ELSE SELECT The value is not 1; END IF I've tried different variations and nothing seems to be working (I'm getting syntax errors). I'm using MySQL 4.0.18. Any help would be greatly appreciated. Thanks, Luke Venediger. -- Get Firefox Browser! Reclaim the web. http://getfirefox.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: **[SPAM]** Conditional statement: IF
[snip] I'm trying to write a conditional statement in my query. Is the following possible in MySQL? IF(@SomeValue = 1) THEN SELECT The value is 1; ELSE SELECT The value is not 1; END IF I've tried different variations and nothing seems to be working (I'm getting syntax errors). I'm using MySQL 4.0.18. Any help would be greatly appreciated. [/snip] Have you RTFM? SELECT IF(@SomeValue = 1, 'The value is 1', 'The value is not 1') http://dev.mysql.com/doc/mysql/en/Control_flow_functions.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Conditional statement: IF
Are you trying to accomplish this in a program, a script or from the command line? If you are using a program, what language are you using? If you are using a script, what platform and shell are you using? (For example, Linux with the bash shell or Windows.) If you are using the command line, I'm pretty sure you can't put ifs around your SQL. Rhino - Original Message - From: Luke Venediger [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, October 22, 2004 10:31 AM Subject: Conditional statement: IF Hi, I'm trying to write a conditional statement in my query. Is the following possible in MySQL? IF(@SomeValue = 1) THEN SELECT The value is 1; ELSE SELECT The value is not 1; END IF I've tried different variations and nothing seems to be working (I'm getting syntax errors). I'm using MySQL 4.0.18. Any help would be greatly appreciated. Thanks, Luke Venediger. -- Get Firefox Browser! Reclaim the web. http://getfirefox.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Simple SQL Question
I have a simple problem and I'm just wondering the BEST query to solve it. I want to return all the rows of a table whose foreign key value exists more than once in that table. IE... MyTable Region(foreign key)City EastBaltimore EastPhilly EastNewark MidwestCleveland SouthFort Lauderdale West Phoenix WestLos Angeles I want a query that returns only the rows where there are more than one of that particular Region in MyTable. The values returned would be EastBaltimore EastPhilly EastNewark WestPhoenix WestLos Angeles Here is what I'd like to do (but can't because the current stable build of MySQL doesn't support subqueries) SELECT MyTable.* FROM (SELECT Region, Count(*) as cnt FROM MyTable GROUP BY Region HAVING cnt = 2) as Duplicates, MyTable WHERE Duplicates.Region = MyTable.Region Here is what I'm actually doing: CREATE TEMPORARY TABLE Duplicates SELECT Region, Count(*) as cnt FROM MyTable GROUP BY Region HAVING cnt = 2; SELECT MyTable.* FROM MyTable, Duplicates WHERE MyTable.Region = Duplicates.Region; Can anybody tell me if there is a more efficient way of doing this query? Thanks! Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: PRODUCT ENQUIRY
Do you suppose these guys bothered to determine that the MySQL mailing list DOESN'T sell ink cartridges or memory before they sent this email? I wonder if they spammed this 'enquiry' to every mailing list in the world in the hope that some of them actually sold ink cartridges or memory? Rhino - Original Message - From: kglobal inc. [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Friday, October 22, 2004 8:43 AM Subject: PRODUCT ENQUIRY HELLO SALES, THIS IS KGLOBAL SERVICE INC.I WANT YOUR COMPANY TO QOUTE THE FOLLOWING ITEMS FOR MY COMPANY: 1. 200 PIECES OF C6578DN INKJET CATRIDGES (OEM) 2. 2 PIECES OF HPNX9010 PENTIYM 4 NOTEBOOK. 3. 50 PIECES OF 512MB DDR PC2700 33MHz MEMORY(KINGSTON). AND ALSO I WANT YOU TO CALCULATE THE SHIPPING COST TO LONDON,UK, SE8 3QY.MOREOVER SEND ALONG YOUR COMPANY PAYMENT METHODS,HOPE TO HEAR FROM YOU SOONEST. BEST REGARDS. - ALL-NEW Yahoo! Messenger - all new features - even more fun! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Simple SQL Question
Sorry, I forgot to mention I am using version 4.0.20a (no subqueries supported) Jeff Burgoon [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] I have a simple problem and I'm just wondering the BEST query to solve it. I want to return all the rows of a table whose foreign key value exists more than once in that table. IE... MyTable Region(foreign key)City EastBaltimore EastPhilly EastNewark MidwestCleveland SouthFort Lauderdale West Phoenix WestLos Angeles I want a query that returns only the rows where there are more than one of that particular Region in MyTable. The values returned would be EastBaltimore EastPhilly EastNewark WestPhoenix WestLos Angeles Here is what I'd like to do (but can't because the current stable build of MySQL doesn't support subqueries) SELECT MyTable.* FROM (SELECT Region, Count(*) as cnt FROM MyTable GROUP BY Region HAVING cnt = 2) as Duplicates, MyTable WHERE Duplicates.Region = MyTable.Region Here is what I'm actually doing: CREATE TEMPORARY TABLE Duplicates SELECT Region, Count(*) as cnt FROM MyTable GROUP BY Region HAVING cnt = 2; SELECT MyTable.* FROM MyTable, Duplicates WHERE MyTable.Region = Duplicates.Region; Can anybody tell me if there is a more efficient way of doing this query? Thanks! Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sort Problem
Nothing? Not even a You're out of luck? Thanks. Albert On Oct 21, 2004, at 9:48 PM, Albert Padley wrote: I've inherited a problem for a youth soccer league. Their standings are computed by adding 3 columns (game_pts, ref_pts and adjust_ref_pts) together. However, the sum of ref_pts plus adjust_ref_pts cannot exceed 15. Here is the current query which obviously allows total_ref_pts to exceed 15 and for total_pts to possibly be incorrect. These potential errors are handled by PHP after the query is run and results in correct numbers and totals being displayed. SELECT lname, teamno, game_pts, sport_pts, ref_pts, adjust_ref_pts, (game_pts + ref_pts + adjust_ref_pts) AS total_pts, (ref_pts + adjust_ref_pts) AS total_ref_pts FROM points WHERE division = 'U14B' ORDER BY total_pts DESC The problem is that the sort order will occasionally be incorrect because of total_pts being incorrect. Can the query be fixed to handle this? If so, how? If not, that is important to know also. Unfortunately, I don't have the luxury of being able to change the table structure. Using mysql 4.0.18 Thanks. Albert Padley -- 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: Sort Problem
try this. It won't be as fast but it will sort correctly: SELECT lname, teamno, game_pts, sport_pts, ref_pts, adjust_ref_pts, if ((ref_pts+adjust_ref_pts)15 ,game_pts + 15, game_pts + ref_pts + adjust_ref_pts) AS total_pts, (ref_pts + adjust_ref_pts) AS total_ref_pts FROM points WHERE division = 'U14B' ORDER BY total_pts DESC Shawn Green Database Administrator Unimin Corporation - Spruce Pine Albert Padley [EMAIL PROTECTED] wrote on 10/21/2004 11:48:09 PM: I've inherited a problem for a youth soccer league. Their standings are computed by adding 3 columns (game_pts, ref_pts and adjust_ref_pts) together. However, the sum of ref_pts plus adjust_ref_pts cannot exceed 15. Here is the current query which obviously allows total_ref_pts to exceed 15 and for total_pts to possibly be incorrect. These potential errors are handled by PHP after the query is run and results in correct numbers and totals being displayed. SELECT lname, teamno, game_pts, sport_pts, ref_pts, adjust_ref_pts, (game_pts + ref_pts + adjust_ref_pts) AS total_pts, (ref_pts + adjust_ref_pts) AS total_ref_pts FROM points WHERE division = 'U14B' ORDER BY total_pts DESC The problem is that the sort order will occasionally be incorrect because of total_pts being incorrect. Can the query be fixed to handle this? If so, how? If not, that is important to know also. Unfortunately, I don't have the luxury of being able to change the table structure. Using mysql 4.0.18 Thanks. Albert Padley -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sort Problem
This will solve your problem and remove the need for the PHP correction. SELECT lname, teamno, game_pts, sport_pts, ref_pts, adjust_ref_pts, CASE WHEN ref_pts + adjust_ref_pts 15 THEN game_pts + 15 ELSE game_pts + ref_pts + adjust_ref_pts END AS total_pts, CASE WHEN ref_pts + adjust_ref_pts 15 THEN 15 ELSE ref_pts + adjust_ref_pts END AS total_ref_pts FROM points WHERE division = 'U14B' ORDER BY total_pts DESC Albert Padley [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Nothing? Not even a You're out of luck? Thanks. Albert On Oct 21, 2004, at 9:48 PM, Albert Padley wrote: I've inherited a problem for a youth soccer league. Their standings are computed by adding 3 columns (game_pts, ref_pts and adjust_ref_pts) together. However, the sum of ref_pts plus adjust_ref_pts cannot exceed 15. Here is the current query which obviously allows total_ref_pts to exceed 15 and for total_pts to possibly be incorrect. These potential errors are handled by PHP after the query is run and results in correct numbers and totals being displayed. SELECT lname, teamno, game_pts, sport_pts, ref_pts, adjust_ref_pts, (game_pts + ref_pts + adjust_ref_pts) AS total_pts, (ref_pts + adjust_ref_pts) AS total_ref_pts FROM points WHERE division = 'U14B' ORDER BY total_pts DESC The problem is that the sort order will occasionally be incorrect because of total_pts being incorrect. Can the query be fixed to handle this? If so, how? If not, that is important to know also. Unfortunately, I don't have the luxury of being able to change the table structure. Using mysql 4.0.18 Thanks. Albert Padley -- 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: optimizing database
Hey, I'm interested. I currently run a few websites with tables of this size and larger. Thanks. On Thu, 21 Oct 2004 15:19:16 -0700 (PDT), Razor Fish [EMAIL PROTECTED] wrote: i need consulting help for optimizing a database with 1.2 million records to handle 3-4 million hits a day. if interested please contact me off the list for details. im willing to pay for your time. ___ Do you Yahoo!? Declare Yourself - Register online to vote today! http://vote.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Determining if query will work
Sorry for the delay in this. Still having problems with this one.
The second query is going to produce one row for every resume. Based on
your results, I assume you have 1000 resumes that have candidates
associated with them.
Let's say I have 256 resulting rows for table 'resume', Section_ID 1,
where the word 'peoplesoft' was found. What I'm really trying to do is
get the 256 that come up in the first query, and then work with only
that 256. The way the table 'resume' is set up is with the following
fields:
Candidate_ID
Section_ID
Section_Value
Section_ID 1 is where all basic text in a resume is stored; there are a
total of 6 sections, each section holding something different. I must
search first on Section 1 for references to 'peoplesoft'. Ultimately,
each candidate will have 6 total sections in resume - in this case, 256
x 6 = 1536. Therefore, I would need the second query to return 1536,
returning results for all candidates matching for Section 1, then using
the Candidate_ID to find all resulting rows in table 'resume'.
I also notice that ALL of the fields in your select statement come from
the candidate table. If that's truly the case, why do you need to JOIN
to
the resume table?
See above. :) Resume material is boolean-searched, then I have to copy
that Candidate_ID from *both* tables 'candidate' and 'resume' into
'candidate_erp' and 'resume_erp'.
Thanks,
Eve
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Wednesday, October 20, 2004 3:50 PM
To: [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Subject: RE: Determining if query will work
SELECT *
FROM wow.resume r
INNER JOIN wow.candidate c
ON c.Candidate_ID = r.Candidate_ID
WHERE r.Section_ID = '1'
AND MATCH (r.Section_Value) AGAINST ('+peoplesoft' IN BOOLEAN
MODE);
INSERT INTO wow.resume_erp (Candidate_ID, Section_ID, Section_Value)
SELECT SQL_CALC_FOUND_ROWS c.candidate_id, r.Section_ID,
r.Section_Value
FROM wow.resume r
INNER JOIN wow.candidate c
ON r.Candidate_ID = c.Candidate_ID;
INSERT INTO wow.candidate_erp (Candidate_ID, Vendor_ID, Last_Name,
First_Name, Middle_Initial, Condition_Type, Employer, Country_ID,
Visa_Status, Dt_Visa, MMDD_Birth, SSN, CSG_Comments, Working,
Available, Start_Date, Location, HoldOnPeriod, Relocation,
Tech_Ranking, Comm_Ranking, Availability, Cert_Comments, Dt_Submitted,
Def_Rate, Def_Rate_Unit, Other_Country, Currency_id,
Interview_Availability, Interview_Contact, US_Experience,
Location_Country) SELECT SQL_CALC_FOUND_ROWS c.candidate_id,
c.Vendor_ID, c.Last_Name, c.First_Name, c.Middle_Initial,
c.Condition_Type, c.Employer, c.Country_ID, c.Visa_Status, c.Dt_Visa,
c.MMDD_Birth, c.SSN, c.CSG_Comments, c.Working, c.Available,
c.Start_Date, c.Location, c.HoldOnPeriod, c.Relocation,
c.Tech_Ranking, c.Comm_Ranking, c.Availability, c.Cert_Comments,
c.Dt_Submitted, c.Def_Rate_Unit, c.Other_Country, c.Currency_id,
c.Interview_Availability, c.Interview_Contact, c.US_Experience,
c.Location_Country FROM wow.resume r INNER JOIN wow.candidate c
ON r.Candidate_ID = c.Candidate_ID;
I changed the boolean search to what I was matching against (this time
'peoplesoft') with a resulting 256 rows.
I ran the first two queries, which resulted in part 1 returning 256
rows, part 2 affecting 1000 rows, and nothing entering into the
database candidate_erp. The third resulted in ERROR 1136: Column count
doesn't match value count at row 1.
The second query is going to produce one row for every resume. Based on
your results, I assume you have 1000 resumes that have candidates
associated with them.
In the third query you left out a field (c.def_rate, perhaps?). Here are
your fields lined up vertically
INSERT fieldsSELECT fields
Candidate_ID,c.candidate_id,
Vendor_ID, c.Vendor_ID,
Last_Name, c.Last_Name,
First_Name, c.First_Name,
Middle_Initial, c.Middle_Initial,
Condition_Type, c.Condition_Type,
Employer, c.Employer,
Country_ID, c.Country_ID,
Visa_Status, c.Visa_Status,
Dt_Visa,c.Dt_Visa,
MMDD_Birth, c.MMDD_Birth,
SSN,c.SSN,
CSG_Comments, c.CSG_Comments,
Working,c.Working,
Available, c.Available,
Start_Date, c.Start_Date,
Location, c.Location,
HoldOnPeriod, c.HoldOnPeriod,
Relocation, c.Relocation,
Tech_Ranking, c.Tech_Ranking,
Comm_Ranking,c.Comm_Ranking,
Availability, c.Availability,
Cert_Comments, c.Cert_Comments,
Dt_Submitted, c.Dt_Submitted,
Def_Rate,
Def_Rate_Unit, c.Def_Rate_Unit,
Other_Country, c.Other_Country,
Currency_id,c.Currency_id,
Interview_Availability, c.Interview_Availability, Interview_Contact,
c.Interview_Contact, US_Experience, c.US_Experience, Location_Country
c.Location_Country
I also notice that ALL of the fields in your select statement come from
the candidate table. If that's truly the case, why do you need to JOIN
to
the resume table?
I
Re: Conditional statement: IF
I don't know why you want to do this, but looking at your sybtax, it seems like you come from a Sybase or SQL Server environment. What you are trying to achieve is the way it is done in Transact SQL, where the conditional statements and stuff like that which are typically used in stored procedures, may also be used outside stored procedures. As someone else suggested, a real programming environment might be more appropriate, with a MySQL connection. Like Perl or so. Depite this, the simple example you may well be done with reasonably normal SQL in MySQL like this: select IF(@val = 1,'It is 1', 'It is not one'); But I suspect you want something more advanced than this, as this is not the most useful program the world has seen. (But not the least useful either). /Karlsson Luke Venediger wrote: Hi, I'm trying to write a conditional statement in my query. Is the following possible in MySQL? IF(@SomeValue = 1) THEN SELECT The value is 1; ELSE SELECT The value is not 1; END IF I've tried different variations and nothing seems to be working (I'm getting syntax errors). I'm using MySQL 4.0.18. Any help would be greatly appreciated. Thanks, Luke Venediger. -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Anders Karlsson ([EMAIL PROTECTED]) / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Sales Engineer /_/ /_/\_, /___/\___\_\___/ Stockholm ___/ www.mysql.com Cellphone: +46 708 608121 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sort Problem SOLVED
Jeff and Shawn, Thanks for coming up with similar solutions. Jeff, I have used yours because it was more complete. I wasn't aware of the Case statement in mysql. I guess I still have a lot to learn. Thanks again. Albert On Oct 22, 2004, at 10:10 AM, Jeff Burgoon wrote: This will solve your problem and remove the need for the PHP correction. SELECT lname, teamno, game_pts, sport_pts, ref_pts, adjust_ref_pts, CASE WHEN ref_pts + adjust_ref_pts 15 THEN game_pts + 15 ELSE game_pts + ref_pts + adjust_ref_pts END AS total_pts, CASE WHEN ref_pts + adjust_ref_pts 15 THEN 15 ELSE ref_pts + adjust_ref_pts END AS total_ref_pts FROM points WHERE division = 'U14B' ORDER BY total_pts DESC Albert Padley [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Nothing? Not even a You're out of luck? Thanks. Albert On Oct 21, 2004, at 9:48 PM, Albert Padley wrote: I've inherited a problem for a youth soccer league. Their standings are computed by adding 3 columns (game_pts, ref_pts and adjust_ref_pts) together. However, the sum of ref_pts plus adjust_ref_pts cannot exceed 15. Here is the current query which obviously allows total_ref_pts to exceed 15 and for total_pts to possibly be incorrect. These potential errors are handled by PHP after the query is run and results in correct numbers and totals being displayed. SELECT lname, teamno, game_pts, sport_pts, ref_pts, adjust_ref_pts, (game_pts + ref_pts + adjust_ref_pts) AS total_pts, (ref_pts + adjust_ref_pts) AS total_ref_pts FROM points WHERE division = 'U14B' ORDER BY total_pts DESC The problem is that the sort order will occasionally be incorrect because of total_pts being incorrect. Can the query be fixed to handle this? If so, how? If not, that is important to know also. Unfortunately, I don't have the luxury of being able to change the table structure. Using mysql 4.0.18 Thanks. Albert Padley -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Change master on replication
We have an environment where we will have multiple mysql masters, and multiple slaves hanging off each master (the masters are actually slaves from another data store updated by a script). The table we are replicating is about 6GB in size, so somewhat large. If one of the masters goes down, we want to repoint the slaves on that master to another one. Problem is, it's possible the masters aren't in sync, so in the scenario: Master A has Slave 1 and Slave 2 Master B has Slave 3 and Slave 4 Master A is ahead of Master B, and Master A goes down, we want to repoint Slave 3 and Slave 4 to Master B. Since the data is large, we don't want to do a fresh resync of Slave 3 and 4 from scratch. And A and B may not have been updated at the same time (or in same order), so their binary logs won't be in same order (so it's not as simple as saying go to the last position you were in when your Master died on your new master and continue from there). Anyone had a similar situation and found a solution? Thanks, Sean -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
multiple mysql installations?
I managed to get multiple instances of mysql to install and run(via mysqld_multi), but only by compiling the source. The system I had was Redhat9 and I used the Mysql RPM, but I couldn't get that to work, so I opted to download and compile the source. Is this the ONLY way to get multiple instances of mysql to run on one machine?
RE: Determining if query will work
I would simplify this search by breaking the query into two steps, just as
you described. First locate all of the resumes where section 1 contains
the bit of text you are looking for then use those results to get the full
resume (all 6 sections)
There are 3 ways to do this. One is a single-query method but it will
require a rather complex set of JOINS and I don't think the response from
it will be good enough (it should be quite slow, based on my experience),
one uses a subquery which your current version of MySQL may not support
(last I read, you were on 3.23.58), and the third which I show below uses
a TEMP TABLE:
CREATE TEMPORARY TABLE tmpCandidates
SELECT DISTINCT Candidate_ID
FROM resume
WHERE Section_ID = '1'
AND MATCH (r.Section_Value) AGAINST ('+peoplesoft' IN BOOLEAN
MODE);
SELECT SQL_CALC_FOUND_ROWS c.candidate_id, c.Vendor_ID, c.Last_Name,
c.First_Name, c.Middle_Initial, c.Condition_Type, c.Employer,
c.Country_ID, c.Visa_Status, c.Dt_Visa, c.MMDD_Birth, c.SSN,
c.CSG_Comments, c.Working, c.Available, c.Start_Date, c.Location,
c.HoldOnPeriod, c.Relocation, c.Tech_Ranking, c.Comm_Ranking,
c.Availability, c.Cert_Comments, c.Dt_Submitted, c.Def_Rate_Unit,
c.Other_Country, c.Currency_id, c.Interview_Availability,
c.Interview_Contact, c.US_Experience, c.Location_Country
FROM tmpCandidates tc
INNER JOIN wow.candidate c
ON tc.Candidate_ID = c.Candidate_ID;
If you had 200 resumes from 150 candidates where section 1 matched
peoplesoft and each resume had 6 sections, this will give you 150
candidate returns (you didn't get all 200 resume hits because I eliminated
the duplicate candidate matches with the DISTINCT keyword). If you want to
get each set of candidate information along with their resume information,
you will end up with all columns of the candidate information being
duplicated 6 times, once for each resume section. That query would look
like:
SELECT SQL_CALC_FOUND_ROWS c.candidate_id, c.Vendor_ID, c.Last_Name,
c.First_Name, c.Middle_Initial, c.Condition_Type, c.Employer,
c.Country_ID, c.Visa_Status, c.Dt_Visa, c.MMDD_Birth, c.SSN,
c.CSG_Comments, c.Working, c.Available, c.Start_Date, c.Location,
c.HoldOnPeriod, c.Relocation, c.Tech_Ranking, c.Comm_Ranking,
c.Availability, c.Cert_Comments, c.Dt_Submitted, c.Def_Rate_Unit,
c.Other_Country, c.Currency_id, c.Interview_Availability,
c.Interview_Contact, c.US_Experience, c.Location_Country, r2.section_id,
r2.section_value
FROM tmpCandidates tc
INNER JOIN resume r
on r.candidate_ID = tc.candidate_id
INNER JOIN wow.candidate c
ON c.Candidate_ID = tc.Candidate_ID;
This will return the full 1200 rows (200 resume matches x 6 sections per
resume) because we re-introduced (by joining to the Resume table) the
multiple resumes for each candidates.
Once you have tuned your select queries to return what you want, it's
trivially simple to add the INSERT (...) clause to the beginning of it to
get the results to go into a table instead of to your client.
Keep me informed on your progress, please. Thank you for being so patient.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Eve Atley [EMAIL PROTECTED] wrote on 10/22/2004 12:43:26 PM:
Sorry for the delay in this. Still having problems with this one.
The second query is going to produce one row for every resume. Based on
your results, I assume you have 1000 resumes that have candidates
associated with them.
Let's say I have 256 resulting rows for table 'resume', Section_ID 1,
where the word 'peoplesoft' was found. What I'm really trying to do is
get the 256 that come up in the first query, and then work with only
that 256. The way the table 'resume' is set up is with the following
fields:
Candidate_ID
Section_ID
Section_Value
Section_ID 1 is where all basic text in a resume is stored; there are a
total of 6 sections, each section holding something different. I must
search first on Section 1 for references to 'peoplesoft'. Ultimately,
each candidate will have 6 total sections in resume - in this case, 256
x 6 = 1536. Therefore, I would need the second query to return 1536,
returning results for all candidates matching for Section 1, then using
the Candidate_ID to find all resulting rows in table 'resume'.
I also notice that ALL of the fields in your select statement come from
the candidate table. If that's truly the case, why do you need to JOIN
to
the resume table?
See above. :) Resume material is boolean-searched, then I have to copy
that Candidate_ID from *both* tables 'candidate' and 'resume' into
'candidate_erp' and 'resume_erp'.
Thanks,
Eve
previous responses snipped -- see list archives for details
Re: Conditional statement: IF
Hi Anders, Thanks for that. Yes, I have come from an MSSQL environment, and I'm using .Net 1.1 with the MySQL Connector/Net. The idea behind using conditional statements was to assign a query to a business task. For example, I could write a query to handle adding items to a shopping cart. In the query, before I add the item, I need to check if the item is in stock. If it's not in stock, I need to return a result set that indicates there has been an error, i.e. There is no stock of the requested item. Putting this logic in my code means having to execute a number of statements with code checks in-between, where I would rather only execute one query. Cheers, Luke Venediger. On Fri, 22 Oct 2004 18:49:54 +0200, Anders Karlsson [EMAIL PROTECTED] wrote: I don't know why you want to do this, but looking at your sybtax, it seems like you come from a Sybase or SQL Server environment. What you are trying to achieve is the way it is done in Transact SQL, where the conditional statements and stuff like that which are typically used in stored procedures, may also be used outside stored procedures. As someone else suggested, a real programming environment might be more appropriate, with a MySQL connection. Like Perl or so. Depite this, the simple example you may well be done with reasonably normal SQL in MySQL like this: select IF(@val = 1,'It is 1', 'It is not one'); But I suspect you want something more advanced than this, as this is not the most useful program the world has seen. (But not the least useful either). /Karlsson Luke Venediger wrote: Hi, I'm trying to write a conditional statement in my query. Is the following possible in MySQL? IF(@SomeValue = 1) THEN SELECT The value is 1; ELSE SELECT The value is not 1; END IF I've tried different variations and nothing seems to be working (I'm getting syntax errors). I'm using MySQL 4.0.18. Any help would be greatly appreciated. Thanks, Luke Venediger. -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Anders Karlsson ([EMAIL PROTECTED]) / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Sales Engineer /_/ /_/\_, /___/\___\_\___/ Stockholm ___/ www.mysql.com Cellphone: +46 708 608121 -- Get Firefox Browser! Reclaim the web. http://getfirefox.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: **[SPAM]** Conditional statement: IF
Hi, Apologies, yes I have RTFM, and should've clarified my question. I actually need to do something more like this: IF(@SomeValue = 1) BEGIN THEN SELECT Data as Column1, MoreData as Column2 ELSE SELECT SomeValue is not = 1 as Error END IF Thanks, Luke Venediger. On Fri, 22 Oct 2004 09:39:21 -0500, Jay Blanchard [EMAIL PROTECTED] wrote: [snip] I'm trying to write a conditional statement in my query. Is the following possible in MySQL? IF(@SomeValue = 1) THEN SELECT The value is 1; ELSE SELECT The value is not 1; END IF I've tried different variations and nothing seems to be working (I'm getting syntax errors). I'm using MySQL 4.0.18. Any help would be greatly appreciated. [/snip] Have you RTFM? SELECT IF(@SomeValue = 1, 'The value is 1', 'The value is not 1') http://dev.mysql.com/doc/mysql/en/Control_flow_functions.html -- Get Firefox Browser! Reclaim the web. http://getfirefox.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: **[SPAM]** Conditional statement: IF
T-SQL style procedural scripting will not be initially available outside of stored procedures. Stored procedures are new to 5.0 (still under development) so what you want to code is not available, yet. You will have to make program branch choices in a programming language as the SQL engine currently does not support that construction in ad hoc SQL scripts. However, you were shown two methods by which a query can return optional values. Perhaps you can minimize your round-trips to the server by using one of those methods. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Luke Venediger [EMAIL PROTECTED] wrote on 10/22/2004 01:48:41 PM: Hi, Apologies, yes I have RTFM, and should've clarified my question. I actually need to do something more like this: IF(@SomeValue = 1) BEGIN THEN SELECT Data as Column1, MoreData as Column2 ELSE SELECT SomeValue is not = 1 as Error END IF Thanks, Luke Venediger. On Fri, 22 Oct 2004 09:39:21 -0500, Jay Blanchard [EMAIL PROTECTED] wrote: [snip] I'm trying to write a conditional statement in my query. Is the following possible in MySQL? IF(@SomeValue = 1) THEN SELECT The value is 1; ELSE SELECT The value is not 1; END IF I've tried different variations and nothing seems to be working (I'm getting syntax errors). I'm using MySQL 4.0.18. Any help would be greatly appreciated. [/snip] Have you RTFM? SELECT IF(@SomeValue = 1, 'The value is 1', 'The value is not 1') http://dev.mysql.com/doc/mysql/en/Control_flow_functions.html -- Get Firefox Browser! Reclaim the web. http://getfirefox.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Conditional statement: IF
That's what I though. In Transact SQL this is valid code to run on the server, and you may optionally choose to put it all into a single stored procedure. In most other databases, a stored procedure has a language that is not executable outside the procedure code itself. With MySQL there will be stored procedure support in version 5.0 of the Server, but in your case the 5.0 alpha will not help, as stored procedure calls probably has an issue or two with the .NET provider. Frankly, the way Sybase does this (which was later inherited by SQL Server) was the way you did things at that time. It's just procedural code, the only difference being that is runs on the server. Today, you would probably put this type of logic in a stored procedure or an appserver. None of these is an option for you, so I guess that you have to put it in your code. Really, it's not that much of an issue, and the performance gains from those days or doing things this way are way less now. In some cases, cleaver SQL constructs can be used for simple conditional processing, but it's not generally applicable. Good luck to you /Karlsson Luke Venediger wrote: Hi Anders, Thanks for that. Yes, I have come from an MSSQL environment, and I'm using .Net 1.1 with the MySQL Connector/Net. The idea behind using conditional statements was to assign a query to a business task. For example, I could write a query to handle adding items to a shopping cart. In the query, before I add the item, I need to check if the item is in stock. If it's not in stock, I need to return a result set that indicates there has been an error, i.e. There is no stock of the requested item. Putting this logic in my code means having to execute a number of statements with code checks in-between, where I would rather only execute one query. Cheers, Luke Venediger. On Fri, 22 Oct 2004 18:49:54 +0200, Anders Karlsson [EMAIL PROTECTED] wrote: I don't know why you want to do this, but looking at your sybtax, it seems like you come from a Sybase or SQL Server environment. What you are trying to achieve is the way it is done in Transact SQL, where the conditional statements and stuff like that which are typically used in stored procedures, may also be used outside stored procedures. As someone else suggested, a real programming environment might be more appropriate, with a MySQL connection. Like Perl or so. Depite this, the simple example you may well be done with reasonably normal SQL in MySQL like this: select IF(@val = 1,'It is 1', 'It is not one'); But I suspect you want something more advanced than this, as this is not the most useful program the world has seen. (But not the least useful either). /Karlsson Luke Venediger wrote: Hi, I'm trying to write a conditional statement in my query. Is the following possible in MySQL? IF(@SomeValue = 1) THEN SELECT The value is 1; ELSE SELECT The value is not 1; END IF I've tried different variations and nothing seems to be working (I'm getting syntax errors). I'm using MySQL 4.0.18. Any help would be greatly appreciated. Thanks, Luke Venediger. -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Anders Karlsson ([EMAIL PROTECTED]) / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Sales Engineer /_/ /_/\_, /___/\___\_\___/ Stockholm ___/ www.mysql.com Cellphone: +46 708 608121 -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Anders Karlsson ([EMAIL PROTECTED]) / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Sales Engineer /_/ /_/\_, /___/\___\_\___/ Stockholm ___/ www.mysql.com Cellphone: +46 708 608121 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sort Problem
Juan, Good question. ref_pts are given for completing referee assignments and are tracked throughout the season and can exceed 15 for tracking purposes. However, only a max of 15 total_ref_pts are used in determining the standings. adust_ref_pts (up or down) are given by the Referee Assignor for situations not covered in the normal automatic system. Again, they want to track this total throughout the season, but only use a max of 15 total_ref_pts for computing the standings. In your example, they want to know the true values of 10, 9 and 7, but total_pts would only add up to 25 for computing the standings (10 game_pts and 15 total_ref_pts). Make sense? Albert On Oct 22, 2004, at 11:46 AM, Juan M. Quiroz wrote: I have some questions. Is the information on those fields used as their true value? Meaning are you always have to manipulate the data, then why not enter the correct information instead of manipulating. If this is a special case I can see is necessary. so if the values for game_pts, ref_pts, adjust_ref_pts are 10, 9, 7 can it take its true add of 26 or should it be 25? Then why not have the right values to begin with. Just curious. Juan Albert Padley on 10/21/04 at 9:48 PM -0600 wrote about: Sort Problem I've inherited a problem for a youth soccer league. Their standings are computed by adding 3 columns (game_pts, ref_pts and adjust_ref_pts) together. However, the sum of ref_pts plus adjust_ref_pts cannot exceed 15. Here is the current query which obviously allows total_ref_pts to exceed 15 and for total_pts to possibly be incorrect. These potential errors are handled by PHP after the query is run and results in correct numbers and totals being displayed. SELECT lname, teamno, game_pts, sport_pts, ref_pts, adjust_ref_pts, (game_pts + ref_pts + adjust_ref_pts) AS total_pts, (ref_pts + adjust_ref_pts) AS total_ref_pts FROM points WHERE division = 'U14B' ORDER BY total_pts DESC The problem is that the sort order will occasionally be incorrect because of total_pts being incorrect. Can the query be fixed to handle this? If so, how? If not, that is important to know also. Unfortunately, I don't have the luxury of being able to change the table structure. Using mysql 4.0.18 Thanks. Albert Padley -- 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: Determining if query will work
Incidentally, we are using a newer 4 version of MySQL now, if there's
another way apart from a temp table to do this?
Let me try with the temp table meanwhile. Thanks!
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Friday, October 22, 2004 1:27 PM
To: [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Subject: RE: Determining if query will work
I would simplify this search by breaking the query into two steps, just
as
you described. First locate all of the resumes where section 1 contains
the bit of text you are looking for then use those results to get the
full
resume (all 6 sections)
There are 3 ways to do this. One is a single-query method but it will
require a rather complex set of JOINS and I don't think the response
from
it will be good enough (it should be quite slow, based on my
experience),
one uses a subquery which your current version of MySQL may not support
(last I read, you were on 3.23.58), and the third which I show below
uses
a TEMP TABLE:
CREATE TEMPORARY TABLE tmpCandidates
SELECT DISTINCT Candidate_ID
FROM resume
WHERE Section_ID = '1'
AND MATCH (r.Section_Value) AGAINST ('+peoplesoft' IN BOOLEAN
MODE);
SELECT SQL_CALC_FOUND_ROWS c.candidate_id, c.Vendor_ID, c.Last_Name,
c.First_Name, c.Middle_Initial, c.Condition_Type, c.Employer,
c.Country_ID, c.Visa_Status, c.Dt_Visa, c.MMDD_Birth, c.SSN,
c.CSG_Comments, c.Working, c.Available, c.Start_Date, c.Location,
c.HoldOnPeriod, c.Relocation, c.Tech_Ranking, c.Comm_Ranking,
c.Availability, c.Cert_Comments, c.Dt_Submitted, c.Def_Rate_Unit,
c.Other_Country, c.Currency_id, c.Interview_Availability,
c.Interview_Contact, c.US_Experience, c.Location_Country
FROM tmpCandidates tc
INNER JOIN wow.candidate c
ON tc.Candidate_ID = c.Candidate_ID;
If you had 200 resumes from 150 candidates where section 1 matched
peoplesoft and each resume had 6 sections, this will give you 150
candidate returns (you didn't get all 200 resume hits because I
eliminated
the duplicate candidate matches with the DISTINCT keyword). If you want
to
get each set of candidate information along with their resume
information,
you will end up with all columns of the candidate information being
duplicated 6 times, once for each resume section. That query would look
like:
SELECT SQL_CALC_FOUND_ROWS c.candidate_id, c.Vendor_ID, c.Last_Name,
c.First_Name, c.Middle_Initial, c.Condition_Type, c.Employer,
c.Country_ID, c.Visa_Status, c.Dt_Visa, c.MMDD_Birth, c.SSN,
c.CSG_Comments, c.Working, c.Available, c.Start_Date, c.Location,
c.HoldOnPeriod, c.Relocation, c.Tech_Ranking, c.Comm_Ranking,
c.Availability, c.Cert_Comments, c.Dt_Submitted, c.Def_Rate_Unit,
c.Other_Country, c.Currency_id, c.Interview_Availability,
c.Interview_Contact, c.US_Experience, c.Location_Country, r2.section_id,
r2.section_value
FROM tmpCandidates tc
INNER JOIN resume r
on r.candidate_ID = tc.candidate_id
INNER JOIN wow.candidate c
ON c.Candidate_ID = tc.Candidate_ID;
This will return the full 1200 rows (200 resume matches x 6 sections per
resume) because we re-introduced (by joining to the Resume table) the
multiple resumes for each candidates.
Once you have tuned your select queries to return what you want, it's
trivially simple to add the INSERT (...) clause to the beginning of it
to
get the results to go into a table instead of to your client.
Keep me informed on your progress, please. Thank you for being so
patient.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Eve Atley [EMAIL PROTECTED] wrote on 10/22/2004 12:43:26 PM:
Sorry for the delay in this. Still having problems with this one.
The second query is going to produce one row for every resume. Based
on
your results, I assume you have 1000 resumes that have candidates
associated with them.
Let's say I have 256 resulting rows for table 'resume', Section_ID 1,
where the word 'peoplesoft' was found. What I'm really trying to do is
get the 256 that come up in the first query, and then work with only
that 256. The way the table 'resume' is set up is with the following
fields:
Candidate_ID
Section_ID
Section_Value
Section_ID 1 is where all basic text in a resume is stored; there are
a total of 6 sections, each section holding something different. I
must search first on Section 1 for references to 'peoplesoft'.
Ultimately, each candidate will have 6 total sections in resume - in
this case, 256 x 6 = 1536. Therefore, I would need the second query
to return 1536, returning results for all candidates matching for
Section 1, then using the Candidate_ID to find all resulting rows in
table 'resume'.
I also notice that ALL of the fields in your select statement come
from
the candidate table. If that's truly the case, why do you need to
JOIN
to
the resume table?
See above. :) Resume material is boolean-searched, then I have to copy
that Candidate_ID from *both* tables 'candidate' and 'resume' into
RE: Change master on replication
Master A has Slave 1 and Slave 2 Master B has Slave 3 and Slave 4 Master A is ahead of Master B, and Master A goes down, we want to repoint Slave 3 and Slave 4 to Master B. Since the data is large, we don't want to do a fresh resync of Slave 3 and 4 from scratch. And A and B may not have been updated at the same time (or in same order), so their binary logs won't be in same order (so it's not as simple as saying go to the last position you were in when your Master died on your new master and continue from there). I am not sure that I understand what you mean by repoint slave 3 and slave 4 when Master B is already behind than Master A. is it a typo mistake. Anyway, you can implement Heart beat mechanism at master level and run it every min or 30 sec whatever you like. You can take a look at replication chapter from Jeremy book. http://dev.mysql.com/books/hpmysql-excerpts/ch07.html Regards,
Re: Change master on replication
Yeah - sorry, a typo. Master A is down, so I need to repoint Slave 1 and Slave 2 to Master B. Same problem though. I know how to tell it's down, my question is in the best way to sync Slave 1 and Slave 2 with Master B since Master A and Master B weren't completely in sync. Sanjeev Sagar wrote: Master A has Slave 1 and Slave 2 Master B has Slave 3 and Slave 4 Master A is ahead of Master B, and Master A goes down, we want to repoint Slave 3 and Slave 4 to Master B. Since the data is large, we don't want to do a fresh resync of Slave 3 and 4 from scratch. And A and B may not have been updated at the same time (or in same order), so their binary logs won't be in same order (so it's not as simple as saying go to the last position you were in when your Master died on your new master and continue from there). I am not sure that I understand what you mean by repoint slave 3 and slave 4 when Master B is already behind than Master A. is it a typo mistake. Anyway, you can implement Heart beat mechanism at master level and run it every min or 30 sec whatever you like. You can take a look at replication chapter from Jeremy book. http://dev.mysql.com/books/hpmysql-excerpts/ch07.html Regards, -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SQL command timeout
Hi all. I have a lasso page with various Inlines to execute, updates, searches, deletes and SQL commands to summarize data from a database with several tables that have grown large and the query to the database lasts for several minutes during this process. I have just started noticing that it is no longer summarizing all the records. When I took out some inlines to shorten the length of time it takes to execute, all the records once again show up (just some of the data for each record is not summarized because I took out the respective inline). It looks like MySQL has a timeout for length of time a complex query can be made to it. If this is the case, what do other people do who have run into this situation? We run MySQL 4.0.15 on Mac OS X Server (10.3) Thanks. Rick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SQL command timeout
[snip] I have a lasso page with various Inlines to execute, updates, searches, deletes and SQL commands to summarize data from a database with several tables that have grown large and the query to the database lasts for several minutes during this process. I have just started noticing that it is no longer summarizing all the records. When I took out some inlines to shorten the length of time it takes to execute, all the records once again show up (just some of the data for each record is not summarized because I took out the respective inline). It looks like MySQL has a timeout for length of time a complex query can be made to it. If this is the case, what do other people do who have run into this situation? [/snip] Check your mysql system variables for timeout variables (SHOW VARIABLES;) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SQL command timeout
Check your mysql system variables for timeout variables (SHOW VARIABLES;) Thanks Jay. After I do this I see there are a number of timeout values, none which refer to a sql command timeout. Are you able to pinpont which value I need to manipulate? Also, if I increase this value, what are the drawbacks of doing so (i.e. performance, etc.) Thank you again. Rick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Determining if query will work
I think we're on to something. I've got one table importing data
correctly, resume to resume_erp. However, 'candidate' to 'candidate_erp'
appears goofy. I get the following error:
[mysql.loosefoot.com] ERROR 1062: Duplicate entry '1185' for key 1
With the following queries, query 3 being the problematic one:
# Connection: mysql.loosefoot.com
# Host: mysql.loosefoot.com
# Saved: 2004-10-22 14:51:15
#
CREATE TEMPORARY TABLE wow.tmpCandidates
SELECT DISTINCT r.Candidate_ID
FROM wow.resume r
WHERE r.Section_ID = '1'
AND MATCH (r.Section_Value) AGAINST ('+baan' IN BOOLEAN MODE);
INSERT INTO wow.resume_erp (Candidate_ID, Section_ID, Section_Value)
SELECT SQL_CALC_FOUND_ROWS c.Candidate_ID, r.Section_ID,
r.Section_Value
FROM wow.tmpCandidates tc
INNER JOIN wow.resume r
on r.Candidate_ID = tc.Candidate_ID
INNER JOIN wow.candidate c
ON c.Candidate_ID = tc.Candidate_ID;
INSERT INTO wow.candidate_erp (Candidate_ID, Vendor_ID, Last_Name,
First_Name, Middle_Initial, Condition_Type, Employer, Country_ID,
Visa_Status, Dt_Visa, MMDD_Birth, SSN, CSG_Comments, Working, Available,
Start_Date, Location, HoldOnPeriod, Relocation, Tech_Ranking,
Comm_Ranking, Availability, Cert_Comments, Dt_Submitted, Def_Rate,
Def_Rate_Unit, Other_Country, Currency_id, Interview_Availability,
Interview_Contact, US_Experience, Location_Country)
SELECT SQL_CALC_FOUND_ROWS c.Candidate_ID, c.Vendor_ID, c.Last_Name,
c.First_Name, c.Middle_Initial, c.Condition_Type, c.Employer,
c.Country_ID, c.Visa_Status, c.Dt_Visa, c.MMDD_Birth, c.SSN,
c.CSG_Comments, c.Working, c.Available, c.Start_Date, c.Location,
c.HoldOnPeriod, c.Relocation, c.Tech_Ranking, c.Comm_Ranking,
c.Availability, c.Cert_Comments, c.Dt_Submitted, c.Def_Rate,
c.Def_Rate_Unit, c.Other_Country, c.Currency_id,
c.Interview_Availability, c.Interview_Contact, c.US_Experience,
c.Location_Country
FROM wow.tmpCandidates tc
INNER JOIN wow.resume r
on r.candidate_ID = tc.Candidate_ID
INNER JOIN wow.candidate c
ON c.Candidate_ID = tc.Candidate_ID;
But I think we're almost there:)
Thanks,
Eve
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SQL command timeout
[snip] Check your mysql system variables for timeout variables (SHOW VARIABLES;) Thanks Jay. After I do this I see there are a number of timeout values, none which refer to a sql command timeout. Are you able to pinpont which value I need to manipulate? Also, if I increase this value, what are the drawbacks of doing so (i.e. performance, etc.) [/snip] In the areas where we have increased timeouts we have not noticed any degredation of performance. I am not sure which value you will need to manipulate, as it has been a while since I have had to deal with it. A visit here http://dev.mysql.com/doc/mysql/en/Server_system_variables.html might help -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Determining if query will work
What is the definition of resume_erp (what is the result of: SHOW CREATE
TABLE resume_erp) ? What that error is telling you is that you are
attempting to add a record that matches a set of conditions that you said
could only exist once on the entire table (either your primary key or a
unique key). By looking at the definition of that table we can determine
where you should look for the duplication.
continued below...
Eve Atley [EMAIL PROTECTED] wrote on 10/22/2004 03:27:47 PM:
I think we're on to something. I've got one table importing data
correctly, resume to resume_erp. However, 'candidate' to 'candidate_erp'
appears goofy. I get the following error:
[mysql.loosefoot.com] ERROR 1062: Duplicate entry '1185' for key 1
With the following queries, query 3 being the problematic one:
# Connection: mysql.loosefoot.com
# Host: mysql.loosefoot.com
# Saved: 2004-10-22 14:51:15
#
CREATE TEMPORARY TABLE wow.tmpCandidates
SELECT DISTINCT r.Candidate_ID
FROM wow.resume r
WHERE r.Section_ID = '1'
AND MATCH (r.Section_Value) AGAINST ('+baan' IN BOOLEAN MODE);
INSERT INTO wow.resume_erp (Candidate_ID, Section_ID, Section_Value)
SELECT SQL_CALC_FOUND_ROWS c.Candidate_ID, r.Section_ID,
r.Section_Value
FROM wow.tmpCandidates tc
INNER JOIN wow.resume r
on r.Candidate_ID = tc.Candidate_ID
INNER JOIN wow.candidate c
ON c.Candidate_ID = tc.Candidate_ID;
The only reason you need to JOIN the table candidate to this query is to
make sure that you don't add any records to resume_erp unless they have a
corresponding record in candidate. If you are sure that all resumes still
have their corresponding candidate records then you can drop the second
INNER JOIN and just use the candidate_ID column from either resume or
tmpCandidates (your choice) in your SELECT clause.
INSERT INTO wow.candidate_erp (Candidate_ID, Vendor_ID, Last_Name,
First_Name, Middle_Initial, Condition_Type, Employer, Country_ID,
Visa_Status, Dt_Visa, MMDD_Birth, SSN, CSG_Comments, Working, Available,
Start_Date, Location, HoldOnPeriod, Relocation, Tech_Ranking,
Comm_Ranking, Availability, Cert_Comments, Dt_Submitted, Def_Rate,
Def_Rate_Unit, Other_Country, Currency_id, Interview_Availability,
Interview_Contact, US_Experience, Location_Country)
SELECT SQL_CALC_FOUND_ROWS c.Candidate_ID, c.Vendor_ID, c.Last_Name,
c.First_Name, c.Middle_Initial, c.Condition_Type, c.Employer,
c.Country_ID, c.Visa_Status, c.Dt_Visa, c.MMDD_Birth, c.SSN,
c.CSG_Comments, c.Working, c.Available, c.Start_Date, c.Location,
c.HoldOnPeriod, c.Relocation, c.Tech_Ranking, c.Comm_Ranking,
c.Availability, c.Cert_Comments, c.Dt_Submitted, c.Def_Rate,
c.Def_Rate_Unit, c.Other_Country, c.Currency_id,
c.Interview_Availability, c.Interview_Contact, c.US_Experience,
c.Location_Country
FROM wow.tmpCandidates tc
INNER JOIN wow.resume r
on r.candidate_ID = tc.Candidate_ID
INNER JOIN wow.candidate c
ON c.Candidate_ID = tc.Candidate_ID;
But I think we're almost there:)
Yes, we are.
Thanks,
Eve
You're welcome,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
RE: Determining if query will work
What is the definition of resume_erp (what is the result of: SHOW CREATE TABLE resume_erp) ? What that error is telling you is that you are Glad you added more info, or I would have been asking you, 'what do you mean by definition?' :) CREATE TABLE `resume_erp` ( `Candidate_ID` int(10) NOT NULL default '0', `Section_ID` int(10) default NULL, `Section_Value` longtext ) TYPE=MyISAM DEFAULT CHARSET=latin1 And if you need candidate_erp...I had figured candidate_erp was the problem?... CREATE TABLE `candidate_erp` ( `Candidate_ID` int(11) NOT NULL auto_increment, `Vendor_ID` longtext, `Last_Name` longtext, `First_Name` longtext, `Middle_Initial` longtext, `Condition_Type` longtext, `Employer` longtext, `Country_ID` longtext, `Visa_Status` longtext, `Dt_Visa` longtext, `MMDD_Birth` longtext, `SSN` longtext, `CSG_Comments` longtext, `Working` longtext, `Available` longtext, `Start_Date` longtext, `Location` longtext, `HoldOnPeriod` longtext, `Relocation` longtext, `Tech_Ranking` longtext, `Comm_Ranking` longtext, `Availability` longtext, `Cert_Comments` longtext, `Dt_Submitted` longtext, `Def_Rate` longtext, `Def_Rate_Unit` longtext, `Other_Country` longtext, `Currency_id` longtext, `Interview_Availability` longtext, `Interview_Contact` longtext, `US_Experience` longtext, `Location_Country` longtext, PRIMARY KEY (`Candidate_ID`) ) TYPE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO wow.resume_erp (Candidate_ID, Section_ID, Section_Value) SELECT SQL_CALC_FOUND_ROWS c.Candidate_ID, r.Section_ID, r.Section_Value FROM wow.tmpCandidates tc INNER JOIN wow.resume r on r.Candidate_ID = tc.Candidate_ID INNER JOIN wow.candidate c ON c.Candidate_ID = tc.Candidate_ID; The only reason you need to JOIN the table candidate to this query is to make sure that you don't add any records to resume_erp unless they have a corresponding record in candidate. If you are sure that all resumes still have their corresponding candidate records then you can drop the second INNER JOIN and just use the candidate_ID column from either resume or tmpCandidates (your choice) in your SELECT clause. Hmm, ok, thanks for this. Tells me I can safely drop te second INNER JOIN then. INSERT INTO wow.resume_erp (Candidate_ID, Section_ID, Section_Value) SELECT SQL_CALC_FOUND_ROWS c.Candidate_ID FROM wow.tmpCandidates tc INNER JOIN wow.resume r on r.Candidate_ID = tc.Candidate_ID; - Eve -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
select from table hangs?
Hello, I am having a problem with a mysql (myisam) table that I've never had before, and I'm completely stumped! I am using mysql version 4.0.13. myisamchk -e table.MYI returns no errors. Yet, when I try to do any kind of select from the mysql prompt, it just hangs and never returns an error code. Does anyone have any thoughts on what might be wrong or other ways to debug this? Thanks! -- Kristina Clair Datarealm Internet Services www.serve.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Union query help
Is this possible? Is it possible to have three queries in a UNION? Sorry about the PHP in there. I just wanted to know if this is allowed? If only the 1st two queries it works OK, add the 3rd and it breaks. How can I accomplish this? Thanks. Lee G. I'm using 4.1.3beta $result = mysql_query((SELECT p.page_id, p.page_url AS url, p.title AS title, p.descrip AS descrip FROM page p, word w, occurrence o WHERE p.page_id = o.page_id AND w.word_id = o.word_id AND w.word_word LIKE '%$keyword%') UNION (SELECT p.page_id, p.page_url AS url, p.title AS title, p.descrip AS descrip FROM page p WHERE p.descrip LIKE '%$keyword%' OR p.titleLIKE '%$keyword%') UNION (SELECT p.page_id, p.page_url AS url, p.title AS title, p.descrip AS descrip FROM url_pages u WHERE u.page_url LIKE '%$keyword%')); -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Union query help
I don't think there's anything conceptually wrong with what you're doing. Most relational databases let you UNION together many more than 3 queries; I assume MySQL also allows this. I don't see any obvious syntax error jumping out at me. What error message are you getting? I suspect that you are not lining up the three queries correctly. The datatypes of the columns have to be consistent: for instance, if the first column is numeric in the first query, the first column in the other queries have to be numeric too. Is it possible that the datatypes of the columns in the third query don't match their counterparts in the first two queries? I see that the column names are the same in each query but if, for example, page_id in the url_pages table is character while the page_id columns returned by the first two queries are numeric, you would almost certainly have a problem. Rhino - Original Message - From: leegold [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, October 22, 2004 11:14 PM Subject: Union query help Is this possible? Is it possible to have three queries in a UNION? Sorry about the PHP in there. I just wanted to know if this is allowed? If only the 1st two queries it works OK, add the 3rd and it breaks. How can I accomplish this? Thanks. Lee G. I'm using 4.1.3beta $result = mysql_query((SELECT p.page_id, p.page_url AS url, p.title AS title, p.descrip AS descrip FROM page p, word w, occurrence o WHERE p.page_id = o.page_id AND w.word_id = o.word_id AND w.word_word LIKE '%$keyword%') UNION (SELECT p.page_id, p.page_url AS url, p.title AS title, p.descrip AS descrip FROM page p WHERE p.descrip LIKE '%$keyword%' OR p.titleLIKE '%$keyword%') UNION (SELECT p.page_id, p.page_url AS url, p.title AS title, p.descrip AS descrip FROM url_pages u WHERE u.page_url LIKE '%$keyword%')); -- 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]
