Where to store comments?
Other databases allow one to store comments describing each field in a table definition. These are stored in the database. In addition, one can store comments about the table. Examples include MS SQL Server and MS Access. Does MySQL have such a feature? Is there a GUI to support it? What perl or java functions does one use to store and retrieve these strings? Thanks, Siegfried -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Prepare v. Do functions: how to benefit from prepare
I'm using activestate perl on WinXP but I'm sure this applies to all languages on all platforms. I cannot remember the JDBC terms presently. There have been lots of discussion on the performance virtues of using the prepare function instead of the do function. Is it necessary to save the statement handle of the prepared statement to benefit from using prepare or does the prepare statement cache earlier calls to prepare and use those when available? I'm finding it difficult to implement the logic to determine if I need to call prepare and if not, where is that previous statement handle? Assuming the answer is yes: are we better off using do instead of prepare if we cannot save the statement handles? Thanks, Siegfried -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to migrate from V4.0.23 to V5?
My questions are probably addressed in the MySQL documentation somewhere. I've been reading the documentation on backup but I'm not sure if that is the right tool for this situation. Perhaps there is a better place to be reading? Apparently my 4.0.23 database on my notebook is corrupted as one large table appears to be empty. Fortunately, I had used zip to create a copy of the mysql/data directory on CD. I have a new V5 installation on my new desktop computer. I really don't care about having a copy of the data on my notebook anymore. What should I do? Should I restore the data directory to my notebook and create a backup (using the mysqldump) and then restore the backup on v5? Or should I just restore the V4.0.23 mysql/data directory that is on the CD directly to the v5 directory? Are the formats of the data files compatible? Or is there is there a special utility for copying from one mysql instance to another? I created all the tables my self using MySQL CC and I never did anything special like incorporate innodb (sp?). Thanks, Siegfried -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
how to design junction table?
I have 100K job posting records and 40K job title records. There is a M:M relationship here. I expect these tables to grow rapidly. What is the best way to design a junction or link table? Do I need to create a primary key? My thought was no primary key, just two indices on each foreign key (FK). Some folks feel you should always have a primary key. However, if I make fkJobTitle the primary key, can it still have duplicates? Some folks say I should have a composite primary key consisting of fkJobTitle and fkJobPosting. This does buy me uniqueness. However, what is the performance like when I only know the fkJobTitle and I want to find all job postings for that title. Is it a linear search? If not, why not? I don't know the fkJobPosting to exploit the primary key. Some folks say I say I should have a separate auto increment integer separate from fkJobPosting and fkJobTitle. Then I could make the id field (what is your favorite naming convention for this field?) the primary key and index fkJobTitle and fkJobPosting separately. But this means every time I insert into the junction table, I have to update three index structures. Is this a problem? What is your opinion? Lastly, I have learned that MySQL has a rich set of extensions for the SQL syntax. Let's suppose I have several hundred jobs which may or may not have been previously inserted into my data base already where each job contains a posting and a title. For each one I have to look it up, insert it if it does not already exist in the database, and, return the integer PK. So what SQL statements would I use to look up the job title, insert it if it does not exist and return the integer PK. I could use SELECT and if that fails, INSERT or vice versa. But a previous poster informed me there are other statements like REPLACE or INSERT ... ON DUPLICATE... and maybe there are others. (Apparently REPLACE will INSERT if it is not already there). I was using REPLACE and now that I am using V5 I can use INSERT... ON DUPLICATE. Which would be best? Thanks, Siegfried -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Control Center works with v4.0.23 -- how about V5?
[Siegfried Heintze] I love MySQL Control center. I can make it work for MySQL v4.0 but not the latest (v5). It simply does not connect to the V5 Mysql server I just installed. It seems to hang on the connection. Am I doing something wrong or does MySQL Control center not support 5? Assuming it does not work with MySQL v5, is there a similar substitute that does work with V5 that will enable me to look at my data and try out SQL statements interactively? I was using the GUI program that comes with MySQL V5 (I think it is the MySQL Administrator) and that looks very nice too. However, I could not figure out how to make it view the contents of my tables. It looked like it was supposed to be able to do that from the screen shots. Thanks, Siegfried -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL Control Center works with v4.0.23 -- how about V5?
That is unfortunate that it MSQLCC is deprecated. Is there a gui tool, maybe query-browser, that will let me update, insert and delete without writing SQL statements? That was a very nice feature. Thanks, Siegfried _ From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 30, 2005 1:12 PM To: Siegfried Heintze Cc: mysql@lists.mysql.com Subject: Re: MySQL Control Center works with v4.0.23 -- how about V5? Siegfried Heintze [EMAIL PROTECTED] wrote on 08/30/2005 03:11:26 PM: [Siegfried Heintze] I love MySQL Control center. I can make it work for MySQL v4.0 but not the latest (v5). It simply does not connect to the V5 Mysql server I just installed. It seems to hang on the connection. Am I doing something wrong or does MySQL Control center not support 5? Assuming it does not work with MySQL v5, is there a similar substitute that does work with V5 that will enable me to look at my data and try out SQL statements interactively? I was using the GUI program that comes with MySQL V5 (I think it is the MySQL Administrator) and that looks very nice too. However, I could not figure out how to make it view the contents of my tables. It looked like it was supposed to be able to do that from the screen shots. Thanks, Siegfried I think what you are looking for is MySQL Query Browser http://www.mysql.com/products/tools/ Shawn Green Database Administrator Unimin Corporation - Spruce Pine
RE: Where did my disk space go?
Thank you David and Sebastion, I am not doing this in a transaction (at least, I did not do anything special to start a transaction) and I have no need for a transaction. How do I check the log files you two suggest? Thanks, Siegfried -Original Message- From: Logan, David (SST - Adelaide) [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 03, 2005 8:10 PM To: Siegfried Heintze; mysql@lists.mysql.com Subject: RE: Where did my disk space go? Hi Siegfried, I would check your transaction logs. Are you doing this as one giant transaction? The system may be filling up the logs just in case you need to rollback. Regards David Logan Database Administrator HP Managed Services 148 Frome Street, Adelaide 5000 Australia +61 8 8408 4273 - Work +61 417 268 665 - Mobile +61 8 8408 4259 - Fax -Original Message- From: Siegfried Heintze [mailto:[EMAIL PROTECTED] Sent: Thursday, 4 August 2005 10:00 AM To: mysql@lists.mysql.com Subject: Where did my disk space go? I've been using Perl 8.4+ (ActiveState) on WinXP. My program runs for many ( 20) hours issuing SQL UPDATE and DELETE commands. The update commands should not be increasing the storage requirements, I'm just updating integer values. I've noticed several times now that I run out of disk space. I started with a gigabyte free. Last time, I aborted the program, compressed my disk, retrieved much lost disk space and started again. Now I tried that again: no luck. I rebooted and recompressed again. I'm still out of disk space. It seems that MySQL just keeps using more and more disk space. How can I retrieve my lost disks pace? Thanks, Siegfried -- 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]
RE: Where did my disk space go?
I did the following from the Cygwin bash prompt on WinXP Pro. cd /c/mysql find . -size -10M | xargs ls -l I only found one file greater than 10 megabytes. I'm looking for several hundred megabytes. Thanks, Siegfried -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to delete log files? Was: RE: Where did my disk space go?
David (and anyone else): Here is the directory of my data directory. I don't see hundreds of mega bytes here, but deleting ib_logfile* and ibdata1 would sure help. Which of these files I can delete without loosing any data from my database? drwxr-xr-x 1 Administrator mkpasswd0 Jul 30 23:07 hotjobs -rw-r--r-- 1 Administrator mkpasswd25,088 Mar 4 18:05 ib_arch_log_00 -rw-r--r-- 1 Administrator mkpasswd 5,242,880 Aug 3 17:52 ib_logfile0 -rw-r--r-- 1 Administrator mkpasswd 5,242,880 Mar 4 18:05 ib_logfile1 -rw-r--r-- 1 Administrator mkpasswd 10,485,760 May 3 15:47 ibdata1 drwxr-xr-x 1 Administrator mkpasswd0 Mar 4 18:02 mysql -rw-r--r-- 1 Administrator mkpasswd53,127 Aug 3 17:52 sales.err drwxr-xr-x 1 Administrator mkpasswd0 Mar 4 18:02 test Now what about this my.cnf file? As I recall, this file lives in the top level directory. I have no such file. I found these files in the mysql directory: my-huge.cnf my-innodb-heavy-4G.cnf my-large.cnf my-medium.cnf my-small.cnf I don't recall editing any of these. Should I be editing them? Thanks, Siegfried -Original Message- From: David Logan [mailto:[EMAIL PROTECTED] Sent: Thursday, August 04, 2005 3:33 PM To: Siegfried Heintze Cc: mysql@lists.mysql.com Subject: RE: Where did my disk space go? Hi Siegfried, In the mysql data directory, -rw-rw1 mysqlmysql 358975 Mar 10 14:28 aaudbasa01.log -rw-rw1 mysqlmysql 25088 Feb 15 08:08 ib_arch_log_00 -rw-rw1 mysqlmysql 5242880 Jun 22 11:20 ib_logfile0 -rw-rw1 mysqlmysql 5242880 Feb 15 08:08 ib_logfile1 The above files maybe in slightly different places, it depends on whether you are using the InnoDB engine or the MyISAM one. The InnoDB logging files are the ib_logfiles. You could also check the my.cnf for any form of logging being switched on, in particular binary logging. You could check for this as well. This will be in your my.cnf file as # Replication Master Server (default) # binary logging is required for replication log_bin If so, you could be filling up your disk quite easily Regards Thank you David and Sebastion, I am not doing this in a transaction (at least, I did not do anything special to start a transaction) and I have no need for a transaction. How do I check the log files you two suggest? Thanks, Siegfried -Original Message- From: Logan, David (SST - Adelaide) [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 03, 2005 8:10 PM To: Siegfried Heintze; mysql@lists.mysql.com Subject: RE: Where did my disk space go? Hi Siegfried, I would check your transaction logs. Are you doing this as one giant transaction? The system may be filling up the logs just in case you need to rollback. Regards David Logan Database Administrator HP Managed Services 148 Frome Street, Adelaide 5000 Australia +61 8 8408 4273 - Work +61 417 268 665 - Mobile +61 8 8408 4259 - Fax -Original Message- From: Siegfried Heintze [mailto:[EMAIL PROTECTED] Sent: Thursday, 4 August 2005 10:00 AM To: mysql@lists.mysql.com Subject: Where did my disk space go? I've been using Perl 8.4+ (ActiveState) on WinXP. My program runs for many ( 20) hours issuing SQL UPDATE and DELETE commands. The update commands should not be increasing the storage requirements, I'm just updating integer values. I've noticed several times now that I run out of disk space. I started with a gigabyte free. Last time, I aborted the program, compressed my disk, retrieved much lost disk space and started again. Now I tried that again: no luck. I rebooted and recompressed again. I'm still out of disk space. It seems that MySQL just keeps using more and more disk space. How can I retrieve my lost disks pace? Thanks, Siegfried -- 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] -- David Logan South Australia when in trouble, or in doubt run in circles, scream and shout -- 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]
Where did my disk space go?
I've been using Perl 8.4+ (ActiveState) on WinXP. My program runs for many ( 20) hours issuing SQL UPDATE and DELETE commands. The update commands should not be increasing the storage requirements, I'm just updating integer values. I've noticed several times now that I run out of disk space. I started with a gigabyte free. Last time, I aborted the program, compressed my disk, retrieved much lost disk space and started again. Now I tried that again: no luck. I rebooted and recompressed again. I'm still out of disk space. It seems that MySQL just keeps using more and more disk space. How can I retrieve my lost disks pace? Thanks, Siegfried -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Alternatives to performing join on normalized joins?
Shawn (and anyone else who will listen): I'm already running out of RAM (actually, virtual memory/page file space) just trying to display all the job titles without even joining them with anything. I have to use a LIKE clause to just get a portion of them. So, I could: (1) Have multiple database connections going concurrently where the first one joins everything except the keywords. As I'm iterating thru the first result set with the fetch function, I could get a list of keyword foreign keys for each row with a second database connect and store this in a second result set. Is this a common approach? Are secondary database connections cheap? (2) I could try to store the first join in a hashmap first and then iterate but I've already demonstrated that the hashmap takes too much memory. (3) I could create a new column of type string for each job title. This would contain a comma separated list of integer foreign keys for the keywords. This is the non-normalized option and you discouraged this approach. (4) I could have a very wide result set. Let assume I have a jobtitle (joined with a job posting and company) with 26 keywords. That means 26 rows in the result set are identical except the keyword foreign key (fk) column. I have to then insert the logic to detect the fact that everything except the keyword fk column is identical. Are you advocating this approach? It seems like it requires a lot of computer space and computer time and (worst of all) my time. I believe this is the classical approach, however. Which would you choose? Thanks, Siegfried _ From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Monday, July 25, 2005 9:31 AM To: Siegfried Heintze Cc: mysql@lists.mysql.com Subject: Re: Alternatives to performing join on normalized joins? Siegfried Heintze [EMAIL PROTECTED] wrote on 07/24/2005 11:35:36 AM: I have a large number of job titles (40K). Each job title has multiple keywords making a one-to-many parent-child relationship. If I join job title with company name, address, company url, company city, job name, job location, job url (etc...) I have a mighty wide result set that will be repeated for each keyword. What I have done in the past (in a different, much smaller, application) is perform a join of everything except the keyword and store everything in a hashmap. Then I iterate thru each wide row in the hashmap and perform a separate SELECT statement foreach row in this hashmap to fetch the multiple keywords. Whew! That would be a lot of RAM (and paging) for this application. Are there any other more efficient approaches? Thanks, Siegfried There are two major classes of efficiency when dealing with any RDBMS: time efficiency (faster results), space efficiency (stored data takes less room on the disk). Which one are you worried about? If it were me, I would start with all of the data normalized: * a Companies table (name, address, url, city, etc) * a Job Titles table (a list of names) * a Keywords table (a list of words used to describe Job Titles) * a JobPosting table ( Relates Companies to Job Titles. Should also be used to track things like dateposted, dateclosed, salary offered, etc.) * a Postings_Keywords table (matches a Posting to multiple Keywords). I would only denormalize if testing showed a dramatic improvement in performance by doing so. I would think that the Job Title to Keyword relationship would be different between Companies. One company posting for a Programmer may want VB while another wants PHP and PERL. By associating the Keywords with a Posting (and not just the Job Title), you can make that list Company-specific. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
How to select first 1000 records like MySQL Control Center 0.9?
Some dialects of SQL have SELECT [FIRST|TOP 1000] clause for their SELECT syntax. I looked at the syntax for mysql and it does not appear to have this feature. Apparently, however, this is possible because the MySQL Control Center does this. How does it do it? Thanks, Siegfried -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Alternatives to performing join on normalized joins?
I have a large number of job titles (40K). Each job title has multiple keywords making a one-to-many parent-child relationship. If I join job title with company name, address, company url, company city, job name, job location, job url (etc...) I have a mighty wide result set that will be repeated for each keyword. What I have done in the past (in a different, much smaller, application) is perform a join of everything except the keyword and store everything in a hashmap. Then I iterate thru each wide row in the hashmap and perform a separate SELECT statement foreach row in this hashmap to fetch the multiple keywords. Whew! That would be a lot of RAM (and paging) for this application. Are there any other more efficient approaches? Thanks, Siegfried -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Documentation on Like clause
Could someone kindly point me to the documentation on the like clause. I found the documentation on the SELECT statement but could not find the discussion on the like clause. I search too -- but there were too many like's used outside of the SQL syntax. Thanks, Siegfried -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to use Like Clause in Perl? Works fine in MySQL control center!
I'm having trouble getting the like clause to work. It seems to work fine in
the MySQL Control Center 9.4.beta. I'm using MySQL 4.0.23-debug.
use DBH;
my $sth = DBH-prepare(SELECT 'David!' LIKE '%D%v%');
$sth-execute();
my $row;
print join(@$row,,).\n while ($row = $sth-fetch);
This does not print a 1 in perl. It just prints a ,.
I've posted a query on this in [EMAIL PROTECTED] with no luck.
Anybody have any suggestions?
Thanks,
Siegfried
Here is DBH.pm. Below that is my original post in [EMAIL PROTECTED]
package DBH;
use DBI;
require Exporter;
our @ISA = qw(Exporter);
our @EXPORT = qw(DBH); # Symbols to be exported by default
our @EXPORT_OK = qw(); # Symbols to exported by request
our $VERSION = 0.1;
our $dbh;
sub DBH{
unless ( $dbh $dbh-ping ) {
$dbh = DBI-connect ( 'dbi:mysql:dbname=hotjobs;host=SALES', 'xyz',
'xyz' ) ;
die DBI-errstr unless $dbh $dbh-ping;
}
return $dbh;
}
1;
The following code works with Activestate perl 8.4/MySQL. If I comment the
second line, however, it does not work. No error messages and no results.
If I use the MySQL Enterprise console and type in my first SELECT statement
that includes the LIKE clause, it works.
I'm stumped. There must be something strange with that %, but I cannot
figure it out.
Anyone got any suggestions?
Siegfried
my $sJobTitle = SELECT sName FROM keywords ORDER BY sName WHERE sName LIKE
'%';
$sJobTitle = q[SELECT sName FROM keywords ORDER BY sName];
my $sth = DBH-prepare($sJobTitle);
$sth-execute();
my $row;
while ($row = $sth-fetch){
push @sResult,li.join( , @$row)./li\n;
}
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Tuning MySQL
Are there any tools for finding hot spots in one's database? My screen scraper is maxing out my CPU. I'm thinking I might need some secondary indexes in some of my tables. I have a lot of two column tables consisting of integer primary key and varchar in the second column. I repeatedly search the second column and, if there is no match, return mysql_insertid. Are there any tools to help me tell which SQL statements are gobbling up my CPU and disk? I suppose I could blindly put secondary indexes everywhere. Siegfried -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Compatibility with ActiveState Perl 5.8.4+?
Approximately a half year ago I started to install bugzilla on windows which uses mysql and perl. After much grief, I discovered that there was a problem with windows perl and the latest version of mysql at the time. I finally solved the problem by rolling back to 4.0.23. It looks like there are some nice features in 4.1 but they won't work for me, unless someone has fixed the perl DBI interface. Has anyone tried the newer versions with perl DBI on windows? How does v5 work, for example? I'm reluctant to try it out myself because I have applications using mysql and I would not want to break them by installing v5. Thanks, Siegfried -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SQL Syntax Errors
671 Did not find any old versions with SELECT cJobTitle FROM jobtitlecount WHERE fkJobPosting = 209689 AND dtSnapShot = '2005-06-26', attempt to insert one: INSERT INTO jobtitlecount (fkJobPosting, dtSnapShot, cJobTitle) VALUES (209689,'2005-06-26',1) 671 Did not find any old versions with SELECT cJobTitle FROM jobtitlecount WHERE fkJobPosting = 209689 AND dtSnapShot = '2005-06-26', attempt to insert one: INSERT INTO jobtitlecount (fkJobPosting, dtSnapShot, cJobTitle) VALUES (209689,'2005-06-26',1) 676 $result = $sth-execute(); 678 Insert must of have worked! DBD::mysql::st execute failed: Duplicate entry '209689-2005-06-26' for key 1 at ./crawl-hot-jobs.pl line 675. 676 $result = $sth-execute(); At the end are my print messages from a perl program using MySQL (v 4.1, how do I tell for sure?) with the DBI interface. The first integer on the left is the line number. I first check to see if the record exists: SELECT cJobTitle FROM jobtitlecount WHERE fkJobPosting = 209689 AND dtSnapShot = '2005-06-26' When I don't find an entry, I try an insert: INSERT INTO jobtitlecount (fkJobPosting, dtSnapShot, cJobTitle) VALUES (209689,'2005-06-26',1) This indicates success. But then it tells me I have a syntax error! DBD::mysql::st execute failed: Duplicate entry '209689-2005-06-26' for key 1 at ./crawl-hot-jobs.pl line 675. The primary key consists of two fields: fkJobPosting (integer) and dtSnapshot (date). Now if my SQL had a syntax error, would it not give me an error every time? So why do I get syntax error? I don't get a syntax error every time. Most of the time, everything works fine. Thanks, Siegfried --- 683 insert failed: INSERT INTO jobtitlecount (fkJobPosting, dtSnapShot, cJobTitle) VALUES (209689,'2005-06-26',1) /try delete: 'DELETE jobtitlecount WHERE dtSnapShot ='2005-06-26' AND fkJobPosting = 209689' select='SELECT cJobTitle FROM jobtitlecount WHERE fkJobPosting = 209689 AND dtSnapShot = '2005-06-26''Duplicate entry '209689-2005-06-26' for key 1 DBD::mysql::st execute failed: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE dtSnapShot ='2005-06-26' AND fkJobPosting = 209689' at li at ./crawl-hot-jobs.pl line 686. Use of uninitialized value in concatenation (.) or string at ./crawl-hot-jobs.pl line 707. 707 $nDBVersion[0] = 1 version=1 nDBVersion=() fkJobId = 209689 No need to update database,it is more recent. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
(oops, corrections to that last email message) RE: SQL Syntax Errors
Sorry, I accidentally pasted some garbage at the beginning of that last email message. Here is what I intended: I first check to see if the record exists: SELECT cJobTitle FROM jobtitlecount WHERE fkJobPosting = 209689 AND dtSnapShot = '2005-06-26' When I don't find an entry, I try an insert: INSERT INTO jobtitlecount (fkJobPosting, dtSnapShot, cJobTitle) VALUES (209689,'2005-06-26',1) The execute function indicated success. But then (sometimes) it tells me I have a duplicate entry: DBD::mysql::st execute failed: Duplicate entry '209689-2005-06-26' for key 1 at ./crawl-hot-jobs.pl line 675. How could this be? Now here is another example where I detect a duplicate and delete the statement before trying to insert: DELETE jobtitlecount WHERE dtSnapShot ='2005-06-26' AND fkJobPosting = 211151 DBD::mysql::st execute failed: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE dtSnapShot ='2005-06-26' AND fkJobPosting = 211151' at li at ./crawl-hot-jobs.pl line 686. I don't see any syntax error in that DELETE statement: do you? The primary key consists of two fields: fkJobPosting (integer) and dtSnapshot (date). Now if my SQL had a syntax error, would it not give me an error every time? So why do I get syntax error? I don't get a syntax error every time. These errors are very eratic and I cannot discern what is different when these errors occur. Thanks, Siegfried --- 683 insert failed: INSERT INTO jobtitlecount (fkJobPosting, dtSnapShot, cJobTitle) VALUES (209689,'2005-06-26',1) /try delete: 'DELETE jobtitlecount WHERE dtSnapShot ='2005-06-26' AND fkJobPosting = 209689' select='SELECT cJobTitle FROM jobtitlecount WHERE fkJobPosting = 209689 AND dtSnapShot = '2005-06-26''Duplicate entry '209689-2005-06-26' for key 1 DBD::mysql::st execute failed: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE dtSnapShot ='2005-06-26' AND fkJobPosting = 209689' at li at ./crawl-hot-jobs.pl line 686. Use of uninitialized value in concatenation (.) or string at ./crawl-hot-jobs.pl line 707. 707 $nDBVersion[0] = 1 version=1 nDBVersion=() fkJobId = 209689 No need to update database,it is more recent. -- 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]
Wanted: Help with 'ON DUPLICATE KEY' syntax
Thanks for deciphering that terrible message, Shawn. I accidentally must have hit the paste key too many times. Anyway, here is my new insert statement: INSERT INTO jobtitlecount (fkJobPosting, dtSnapShot, cJobTitle) VALUES (211584,'2005-06-26',2) ON DUPLICATE KEY UPDATE cJobTitle=2 DBD::mysql::st execute failed: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'ON DUPLICATE KEY UPDATE cJobTitle=2' at line 1 at ./crawl-hot-jobs.pl line 675. I'm looking at the documentation on http://dev.mysql.com/doc/mysql/en/insert.html and I don't see what I am doing wrong. Thanks, Siegfried -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to call mysql_insert_id
A long time ago, I posted this query: If I use the auto-increment feature for a couple of normallized relations, how do I insert into them? Specifically, when I insert into a relation with the autoincrement feature on the primary key, how do I get the value of the index on the newly created row so I can use that the value of a foreign key in another relation? That's database specific, and you haven't specified a database. * In MySQL - mysql_insert_id() How do I call this function? I was hoping I could use SQL such as SELECT mysql_insert_id() FROM XYZ but I discovered that does not work. I'm using a mixture of java and perl. I see in my old documentation that PHP programmers can call such a function. Thanks, Siegfried
RE: How to call mysql_insert_id
Thanks, JP. And will this work for multi-threaded, multi-user applications? Siegfried -Original Message- From: Jan Pieter Kunst [mailto:[EMAIL PROTECTED] Sent: Monday, April 11, 2005 3:05 PM To: mysql@lists.mysql.com Subject: Re: How to call mysql_insert_id On Apr 11, 2005 10:50 PM, Siegfried Heintze [EMAIL PROTECTED] wrote: * In MySQL - mysql_insert_id() How do I call this function? I was hoping I could use SQL such as SELECT mysql_insert_id() FROM XYZ but I discovered that does not work. I'm using a mixture of java and perl. I see in my old documentation that PHP programmers can call such a function. SELECT LAST_INSERT_ID(); HTH, JP -- 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]
Access deined for user 'root@sales'
I'm running v 4.0.22 on Win XP 2003 Server with a client running XP Pro.
I have mysql running on 192.168.0.8 and I want to administer it from
192.168.0.202 (aka SALES).
While on 192.168.0.8 running mysql, I issued the following commands:
mysql GRANT ALL ON *.* TO 'root'@'192.168.0.22';
Query OK, 0 rows affected (0.02 sec)
mysql GRANT ALL ON *.* TO 'root'@'192.168.0.202';
Query OK, 0 rows affected (0.01 sec)
mysql GRANT ALL ON *.* TO 'root'@'SALES';
Query OK, 0 rows affected (0.00 sec)
mysql FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.49 sec)
mysql SET PASSWORD FOR 'root'@'SALES' = PASSWORD('xyzabc');
Query OK, 0 rows affected (0.12 sec)
mysql FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)
mysql GRANT ALL ON *.* TO 'root'@'SALES';
Query OK, 0 rows affected (0.00 sec)
mysql FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.09 sec)
mysql exit
Bye
However, I still get Error 1045: Access denied for user: '[EMAIL PROTECTED]'
(using password:YES) when using the command
Mysql -u root -p -h 192.168.0.8
What am I doing wrong? Incidentally, this is an internal network so there is
no firewall.
Thanks!
Siegfried
