Trying to update a mysql table

Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

Replies are listed 'Best First'.
Re: Trying to update a mysql table by Ovid (Cardinal) on May 22, 2003 at 22:49 UTC
Reduce your code down to the smallest possible test case that fails and then post it here. Without seeing the code, we have no way of knowing what is going on. Also, you can try turning on `trace()`. `DBI->trace($trace_level, $trace_filename);` See the docs for more information about that. It can help you solve most DBI problems. Cheers, Ovid New address of my CGI Course. Silence is Evil (feel free to copy and distribute widely - note copyright text)	[reply] [d/l]
Re: Trying to update a mysql table by chromatic (Archbishop) on May 22, 2003 at 22:28 UTC
There's something wrong with it, where it is one of: your code the database the server the data in the database Without any further details, all we can do is guess. Have you tried converting your query to a SELECT and running it? Perhaps there are no records that match the id.	[reply]
Re: Trying to update a mysql table by dws (Chancellor) on May 22, 2003 at 23:16 UTC
I've checked the code over and over again and there's nothing wrong with it. If, as you clarified, this script is working against a local database, and the same script works on a different server, also using a local-to-that-server database, then one of the first things I would suspect are the privileges granted to whatever database user you're using. It's quite possible for a user to have INSERT privs, but not UPDATE privs. Are privileges the same on both boxes?	[reply]
Re: Trying to update a mysql table by The Mad Hatter (Priest) on May 22, 2003 at 23:01 UTC
Are you explicitly checking return values of all DBI calls or do you have `RaiseError` (which will force a die on any problem) set? If you aren't doing either, set `RaiseError` and see what happens when you run it. Set `RaiseError` by doing something like this in the `connect` `my $dbh = DBI->connect("DBI:$dbType:$dbName:$dbHost", $dbUser, $dbPass, { RaiseError => 1 }) or die "DBI Connect Error", $DBI::errstr, "\n";` [download]	[reply] [d/l] [select]
Re: Trying to update a mysql table by LameNerd (Hermit) on May 22, 2003 at 22:28 UTC
Does you script do anything like this: `my $sth = $dbh->prepare($sql) \|\| die print "$0:Error prepare sql [$DBI::errstr]\n";` [download] If not maybe you could add the printing out of $DBI::errstr.	[reply] [d/l]
Re: Trying to update a mysql table by benn (Vicar) on May 22, 2003 at 22:39 UTC
Are your scripts and the database on the same machine? It could be that your db user account is not allowed remote access, only from 'localhost'. Cheers Ben.	[reply]
Re: Trying to update a mysql table by Anonymous Monk on May 22, 2003 at 22:41 UTC
Yup, everything's on the same machine. The UPDATE queries work from other scripts on the same machine... But not from this script.	[reply]
Re: Trying to update a mysql table by runrig (Abbot) on May 22, 2003 at 22:54 UTC
I've checked the code over and over again and there's nothing wrong with it. So why's this happening?? Because something's wrong?`<ducks>`	[reply] [d/l]
Re: Trying to update a mysql table by Anonymous Monk on May 22, 2003 at 23:54 UTC
Problem's fixed :=) Thanks for the help. It was just some fields in the database didn't exist :-p	[reply]
Re: Trying to update a mysql table by Anonymous Monk on May 22, 2003 at 22:30 UTC
The script has some SELECT statements and those work fine. What it can't do is UPDATE... Very weird.	[reply]
Re: Trying to update a mysql table by Anonymous Monk on May 22, 2003 at 23:27 UTC
Well, I'm accessing the database as root user. So that should have all priveledges, shouldn't it?	[reply]
Re: Re: Trying to update a mysql table by Itatsumaki (Friar) on May 22, 2003 at 23:50 UTC
Three points: Try not to use place-holders for updates. In my experience, the biggest performance benefits for placeholders are in updates and inserts, not in selects. Not only that, I can't count the number of times I've screwed up quoting on different DBs. That doesn't sound like your problem here, but nonetheless you'd be well-served if you started getting into the habit of using place-holders everywhere. Just because you are logged into a machine as root doesn't mean you are logged into the database as root! You need to know what your DB login is, and what privileges you have on the db, not just on the OS. It's hard to figure out what your problem is without a little more info. If you can, please give us the OS, MySQL version, DBI version, DBD version, and the script (remove passwords certainly, and feel free to change table-names to something like table1.field1). Finally, we'd really need to see your SQL to say much more. If possible, post the DBI trace log and that will help us a ton. -Tats	[reply]