Re: DBD::mysql path forward
> On Sep 19, 2017, at 10:10 AM, Darren Duncan wrote:
>
> What Night Light's post says to me is that there is high risk of causing data
> corruption if any changes are made under the DBD::mysql name where DBD::mysql
> has not been exhaustively tested to guarantee that its behavior is backwards
> compatible.
>
> This makes a stronger case to me that the DBD::mysql Git master (that which
> includes the 4.042 changes and any other default breaking changes) should
> rename the Perl driver package name, I suggest DBD::mysql2 version 5.0, and
> that any changes not guaranteed backwards compatible for whatever reason go
> there.
>
> If the Git legacy maintenance branch 4.041/3 can have careful security
> patches applied that don't require any changes to user code to prevent
> breakage, it gets them, and otherwise only DBD::mysql2 gets any changes.
>
> By doing what I said, we can be guaranteed that users with no control over
> how DBD::mysql gets upgraded for them will introduce corruption simply for
> upgrading.
I don't think we want to guarantee that they will introduce corruption simply
for upgrading. :-)
>
> -- Darren Duncan
>
> On 2017-09-19 5:46 AM, Night Light wrote:
>> Dear Perl gurus,
>>
>> This is my first post. I'm using Perl with great joy, and I'd like to
>> express my
>> gratitude for all you are doing to keep Perl stable and fun to use.
>>
>> I'd like to ask to object to re-releasing this version and discuss on how to
>> make 4.043 backwards compatible instead.
>> This change will with 100% certainty corrupt all BLOB data written to the
>> database when the developer did not read the release notes before applying
>> the
>> latest version of DBD::mysql (and changed its code consequently).
>> Knowing that sysadmins have the habit of not always reading the release
>> notes of
>> each updated package the likelihood that this will happen will therefore
>> high.
>> I myself wasn't even shown the release notes as it was a dependency of an
>> updated package that I applied.
>> The exposure of this change is big as DBD::mysql affects multiple
>> applications
>> and many user bases.
>> I believe deliberately introducing industry wide database corruption is
>> something that will significantly harm peoples confidence in using Perl.
>> I believe that not providing backwards compatibility is not in line with the
>> Perl policy that has been carefully put together by the community to maintain
>> the quality of Perl as it is today.
>> http://perldoc.perl.org/perlpolicy.html#BACKWARD-COMPATIBILITY-AND-DEPRECATION
>>
>> I therefore believe the only solution is an upgrade that is by default
>> backwards
>> compatible, and where it is the user who decides when to start UTF8 encode
>> the
>> input values of a SQL request instead.
>> If it is too time consuming or too difficult it should be considered to park
>> the
>> UTF8-encoding "fix" and release a version with the security fix first.
>>
>> I have the following objections against this release:
>>
>> 1. the upgrade will corrupt more records than it fixes (it does more harm
>> than good)
>> 2. the reason given for not providing backward compatibility ("because it was
>> hard to implement") is not plausible given the level of unwanted side
>> effects.
>> This especially knowing that there is already a mechanism in place to
>> signal
>> if its wants UTF8 encoding or not (mysql_enable_utf8/mysql_enable_utf8mb4).
>> 3. it costs more resources to coordinate/discuss a "way forward" or options
>> than
>> to implement a solution that addresses backwards compatibility
>> 4. it is unreasonable to ask for changing existing source knowing that
>> depending
>> modules may not be actively maintained or proprietary
>> It can be argued that such module should always be maintained but it does
>> not
>> change the fact that a good running Perl program becomes unusable
>> 5. it does not inform the user that after upgrading existing code will start
>> write corrupt BLOB records
>> 6. it does not inform the user about the fact that a code review of all
>> existing
>> code is necessary, and how it needs to be changed and tested
>> 7. it does not give the user the option to decide how the BLOB's should be
>> stored/encoded (opt in)
>> 8. it does not provide backwards compatibility
>> By doing so it does not respect the Perl policy that has been carefully put
>> together by the community to maintain the quality of Perl as it is today.
>>
>> http://perldoc.perl.org/perlpolicy.html#BACKWARD-COMPATIBILITY-AND-DEPRECATION
>> 9. it blocks users from using DBD::mysql upgrades as long as they have not
>> rewritten their existing code
>> 10. not all users from DBD::mysql can be warned beforehand about the side
>> effects as it is not known which private parties have code that use
>> DBD::mysql
>> 12. I believe development will go faster when support for backwards
>> compatibility is addressed
>> 13. having to write 1 extra line for each SQL query v
Re: DBD::mysql path forward
> On Sep 14, 2017, at 1:44 AM, p...@cpan.org wrote: > > MySQL server and its databases has some limitations, so reflect it: > > * it does not provide information if placeholder is TEXT, VARCHAR, VARBINARY > or BLOB > * placeholder's bind value does not have to point to column, it can be also > SQL function > --> for caller/user all placeholders are equivalent and caller itself > needs to know how to treat bind variable and needs to specify if > it is TEXT or BLOB > > * VARBINARY is right padded with 0x00 > --> there is no difference between binary "\x01\x02\x00" and "\x01\x02" BINARY is padded (for storage), VARBINARY is not. https://dev.mysql.com/doc/refman/5.7/en/binary-varbinary.html
Re: suppress quoting in prepared sql
> On Apr 5, 2016, at 12:29 PM, Vaughan, Mark wrote:
>
> This works if the number of elements remains static. You'd have to run the
> prepare again if the number of elements changes.
Sure. But that's true no matter how you construct your statement to be prepared.
>
> Mark Vaughan
> Neustar, Inc. / Lead Consulting Services Consultant, Professional Services
> 8532 Concord Center Drive, Englewood, CO 80112, USA
> Office: +1.303.802.1308 Fax: +1.303.802.1350 / mark.vaug...@neustar.biz
>
>
> -Original Message-
> From: Paul DuBois [mailto:p...@snake.net]
> Sent: Tuesday, April 05, 2016 11:25 AM
> To: Bruce Ferrell
> Cc: dbi-users@perl.org
> Subject: Re: suppress quoting in prepared sql
>
>
>> On Apr 5, 2016, at 11:55 AM, Bruce Ferrell wrote:
>>
>> Ick!
>>
>> ok, I have to dynamically build the IN clause of the prepare as a
>> static sql statement
>
> Yep. This is how I do it for a given array of values:
>
> # Create a string of placeholder characters, with one ? character # per
> element in an array of values.
>
> my @values = (1, 2, 3, 4, 5);
>
> my $str = join (",", ("?") x @values);
>
> Then interpolate $str into your query string.
>
>>
>> On 4/5/16 9:32 AM, Vaughan, Mark wrote:
>>>> From the DBI documentation
>>>> (https://urldefense.proofpoint.com/v2/url?u=https-3A__metacpan.org_p
>>>> od_DBI-23Placeholders-2Dand-2DBind-2DValues-29-3A&d=CwIF-g&c=MOptNlV
>>>> tIETeDALC_lULrw&r=rwT9R07bCzfhX6apOj8NoPX-TbEkSSLuFkjri49xQ-0&m=QpMl
>>>> 4dk0ZSYHx2vhZSJDCeS1tdTQ9Z8GWCyZqgIjc28&s=2uZZNLLOkgh5xJfTn_SVli361r
>>>> ZOaGOrDxGPv_yVwd8&e=
>>>
>>> Also, placeholders can only represent single scalar values. For example,
>>> the following statement won't work as expected for more than one value:
>>>
>>> "SELECT name, age FROM people WHERE name IN (?)"# wrong
>>> "SELECT name, age FROM people WHERE name IN (?,?)" # two names
>>>
>>> You may have to prepare the query each time unless you have a fixed number
>>> of elements in the IN clause.
>>>
>>> HTH,
>>> Mark Vaughan
>>> Neustar, Inc. / Lead Consulting Services Consultant, Professional
>>> Services
>>> 8532 Concord Center Drive, Englewood, CO 80112, USA
>>> Office: +1.303.802.1308 Fax: +1.303.802.1350 /
>>> mark.vaug...@neustar.biz
>>>
>>>
>>> -Original Message-
>>> From: Bruce Ferrell [mailto:bferr...@baywinds.org]
>>> Sent: Tuesday, April 05, 2016 10:24 AM
>>> To: dbi-users@perl.org
>>> Subject: suppress quoting in prepared sql
>>>
>>> I'm generating a sql statement like this:
>>>
>>> sth = $mysql_dbh->prepare(
>>> "select sum(column) as columnSum from table where value in ( ? ) and
>>> row_date between cast( ? as date) and cast( ? as date) ");
>>>
>>> sth->execute( $ValueIDs ,$week_start_date,$week_end_date);
>>>
>>> $ValueIDs is a series of unquoted values:
>>>
>>> 01161,01162,01262,01147,01034,01125,01125,01017,01125,01278,01204,011
>>> 64
>>>
>>> When observed at the mysql server, the sql appears as follows:
>>>
>>> select sum(column) as columnSum where value in (
>>> '01161,01162,01262,01147,01034,01125,01125,01017,01125,01278,01204,01
>>> 164' ) and row_date between cast( '2016-03-29' as date) and cast(
>>> '2016-04-05' as date)
>>>
>>> resulting in no data being returned.
>>>
>>> When the sql is manually entered as follows:
>>>
>>> select sum(column) as columnSum where value in (
>>> 01161,01162,01262,01147,01034,01125,01125,01017,01125,01278,01204,011
>>> 64 ) and row_date between cast( '2016-03-29' as date) and cast(
>>> '2016-04-05' as date)
>>>
>>> The correct values are returned.
>>>
>>> How can I suppress the quoting for the IN clause?
>>>
>>>
>>
>
Re: suppress quoting in prepared sql
> On Apr 5, 2016, at 11:55 AM, Bruce Ferrell wrote:
>
> Ick!
>
> ok, I have to dynamically build the IN clause of the prepare as a static sql
> statement
Yep. This is how I do it for a given array of values:
# Create a string of placeholder characters, with one ? character
# per element in an array of values.
my @values = (1, 2, 3, 4, 5);
my $str = join (",", ("?") x @values);
Then interpolate $str into your query string.
>
> On 4/5/16 9:32 AM, Vaughan, Mark wrote:
>> >From the DBI documentation
>> >(https://metacpan.org/pod/DBI#Placeholders-and-Bind-Values):
>>
>> Also, placeholders can only represent single scalar values. For example, the
>> following statement won't work as expected for more than one value:
>>
>> "SELECT name, age FROM people WHERE name IN (?)"# wrong
>> "SELECT name, age FROM people WHERE name IN (?,?)" # two names
>>
>> You may have to prepare the query each time unless you have a fixed number
>> of elements in the IN clause.
>>
>> HTH,
>> Mark Vaughan
>> Neustar, Inc. / Lead Consulting Services Consultant, Professional Services
>> 8532 Concord Center Drive, Englewood, CO 80112, USA
>> Office: +1.303.802.1308 Fax: +1.303.802.1350 / mark.vaug...@neustar.biz
>>
>>
>> -Original Message-
>> From: Bruce Ferrell [mailto:bferr...@baywinds.org]
>> Sent: Tuesday, April 05, 2016 10:24 AM
>> To: dbi-users@perl.org
>> Subject: suppress quoting in prepared sql
>>
>> I'm generating a sql statement like this:
>>
>> sth = $mysql_dbh->prepare(
>> "select sum(column) as columnSum from table where value in ( ? ) and
>> row_date between cast( ? as date) and cast( ? as date) ");
>>
>> sth->execute( $ValueIDs ,$week_start_date,$week_end_date);
>>
>> $ValueIDs is a series of unquoted values:
>>
>> 01161,01162,01262,01147,01034,01125,01125,01017,01125,01278,01204,01164
>>
>> When observed at the mysql server, the sql appears as follows:
>>
>> select sum(column) as columnSum where value in (
>> '01161,01162,01262,01147,01034,01125,01125,01017,01125,01278,01204,01164' )
>> and row_date between cast( '2016-03-29' as date) and cast( '2016-04-05' as
>> date)
>>
>> resulting in no data being returned.
>>
>> When the sql is manually entered as follows:
>>
>> select sum(column) as columnSum where value in (
>> 01161,01162,01262,01147,01034,01125,01125,01017,01125,01278,01204,01164 )
>> and row_date between cast( '2016-03-29' as date) and cast( '2016-04-05' as
>> date)
>>
>> The correct values are returned.
>>
>> How can I suppress the quoting for the IN clause?
>>
>>
>
Re: DBD::mysql 4.028 released
On Aug 2, 2014, at 10:15 PM, Patrick Galbraith wrote: > Dear Pert and MySQL community, > > I”m pleased to announce the release of DBD::mysql 4.028. This release > includes several fixes, per change log: > > * Fixed bug in mysql.xs where dbh was being used as error code Thanks. I was seeing a mysterious error that a failure of connect() was returning the object rather than undef. It's now gone after upgrading. > * RT #97570: fix wrong salloc free in mysql_st_internal_execute - (Reini > Urban, cPanel) > * Fix RT #97625 use-after-free in mysql_dr_error, and #86153 - (Reini Urban, > cPanel) > * find mysql.h for MariaDB on Win32 (Graham Ollis) > * Update mysql.pm to work with ipv6 and ipv4 addresses (katyavoid) > > I want to thank Reini Urban, Graham Ollis, and Katyavoid for their pull > requests and contributions to DBD::mysql! > > Please feel free to visit: > > http://search.cpan.org/~capttofu/DBD-mysql-4.028/ > > And as always: > > https://github.com/perl5-dbi/DBD-mysql.git > > Regards, > > Patrick Galbraith
Re: why DBD::mysql need database name for connecttion ?
On Oct 14, 2011, at 7:31 PM, Bill Ward wrote: > On Fri, Oct 14, 2011 at 5:26 PM, ZhangJun wrote: > >> >> in the pod: >> >> >> Class Methods >> connect >> use DBI; >> >> $dsn = "DBI:mysql:$database"; >> $dsn = "DBI:mysql:database=$database;host=$hostname"; >> $dsn = >> "DBI:mysql:database=$database;host=$hostname;port=$port"; >> >> $dbh = DBI->connect($dsn, $user, $password); >> >> A "database" must always be specified. >> >> ### >> >> why database is needed ? >> the mysqlclient can connect to server without this, and then select >> database. >> >> > You need to connect to a database before issuing any database commands. Not necessarily. SHOW DATABASES, for example, doesn't need any database selected. In the connect call, the database following the second colon actually is optional. The second colon, I believe, is not. So "DBI:mysql:" is legal. Or "DBI:mysql::hostname" if you want to specify a hostname. > The > mysql client just makes that easier to do interactively. In Perl you could > just create a different object for each database if you need to connect to > more than one. Why is this a problem? > > -- > Check out my LEGO blog at http://www.brickpile.com/ > View my photos at http://flickr.com/photos/billward/ > Follow me at http://twitter.com/williamward
Re: Are MySql passwords sent in the clear?
On Jul 6, 2010, at 1:35 PM, Andrew Yancy wrote: > Thanks for all the replies. From those links, especially > > http://dev.mysql.com/doc/refman/5.1/en/password-hashing.html > > I'm getting the pretty clear picture that passwords are never sent in > the clear in recent versions of MySql. Still though, I don't know how > safe it is to assume that that's exactly what's going on with DBI- >> connect. I imagine DBI->connect must be using the underlying MySql > program in the same way as just typing > > mysql -u andrew732 -p -h remote.host.ip > > from the command line, but I would love to find out for sure. DBD::mysql uses the MySQL C client library, which is where password transmission occurs, so it's the same as for the mysql client program, or any other client that uses the C library. > > > On Jul 6, 12:44 pm, mcd...@stanford.edu (David McMath) wrote: >> I think the quoted section is more about how passwords are stored in the >> database itself than about how they're communicated during login. I >> readhttp://dev.mysql.com/doc/refman/5.5/en/secure-connections.htmlto >> suggest that there isn't much encryption going on at all, particularly >> >>> The standard configuration of MySQL is intended to be as fast as possible, >>> so encrypted connections are not used by default. >> >> I think they can get away with that attitude because (1) SSL is >> available if you really want it and (2) "localhost" is a special case >> for MySQL. Fromhttp://dev.mysql.com/doc/refman/5.5/en/connecting.html: >> >>> On Unix, MySQL programs treat the host name localhost specially, in a way >>> that is likely different from what you expect compared to other >>> network-based programs. For connections to localhost, MySQL programs >>> attempt to connect to the local server by using a Unix socket file. >> >> So for the special case of localhost, there's no "over the network" to >> worry about. But if you're connecting to a remote machine, I think you >> _should_ be at least a little concerned about passwords. >> >> I'd be quite happy to be wrong, though. I'm pretty sure DBD::MySQL >> isn't encrypting the password for transmission, but the underlying calls >> to the MySQL client software might be. >> >> dave >> >> Paul DuBois wrote: >>> http://dev.mysql.com/doc/refman/5.1/en/user-names.html: >> >>> " >>> MySQL encrypts passwords using its own algorithm. This encryption is the >>> same as that implemented by thePASSWORD() SQL function but differs from >>> that used during the Unix login process. Unix password encryption is the >>> same as that implemented by the ENCRYPT() SQL function. See the >>> descriptions of the PASSWORD() andENCRYPT() functions in Section 11.13, >>> “Encryption and Compression Functions”. >> >>> From version 4.1 on, MySQL employs a stronger authentication method that >>> has better password protection during the connection process than in >>> earlier versions. It is secure even if TCP/IP packets are sniffed or the >>> mysqldatabase is captured. (In earlier versions, even though passwords are >>> stored in encrypted form in the user table, knowledge of the encrypted >>> password value could be used to connect to the MySQL server.) Section >>> 5.3.2.3, “Password Hashing in MySQL”, discusses password encryption further. >>> " >> >>> On Jul 6, 2010, at 5:42 AM, John Scoles wrote: >> >>>> andrew...@yahoo.com wrote: >>>> Not a 100% sure for MySql but I would think it is. >> >>>> What happens first is the connection to the server is made in this case >>>> '$database:localhost:3306' and then internally the username and password >>>> are sent. >> >>>> If someone can 'sniff' the connection between the perl program and the >>>> Server and if it is not encoded then yes it is in the clear. >>>> I know with DBD::Oracle this connection is encrypted (at least the Pw and >>>> UID) that same should be true of MySql as I think that is part of the SQL >>>> standard is it not?? >> >>>> cheers >>>> John >>>>> When connecting to a MySql server with DBI->connect: >> >>>>> $dsn = "dbi:mysql:$database:localhost:3306"; >>>>> $dbh = DBI->connect($dsn, $username, $password) >> >>>>> is the password sent in the clear? If so,
Re: Are MySql passwords sent in the clear?
http://dev.mysql.com/doc/refman/5.1/en/user-names.html: " MySQL encrypts passwords using its own algorithm. This encryption is the same as that implemented by thePASSWORD() SQL function but differs from that used during the Unix login process. Unix password encryption is the same as that implemented by the ENCRYPT() SQL function. See the descriptions of the PASSWORD() andENCRYPT() functions in Section 11.13, “Encryption and Compression Functions”. From version 4.1 on, MySQL employs a stronger authentication method that has better password protection during the connection process than in earlier versions. It is secure even if TCP/IP packets are sniffed or the mysqldatabase is captured. (In earlier versions, even though passwords are stored in encrypted form in the user table, knowledge of the encrypted password value could be used to connect to the MySQL server.) Section 5.3.2.3, “Password Hashing in MySQL”, discusses password encryption further. " On Jul 6, 2010, at 5:42 AM, John Scoles wrote: > andrew...@yahoo.com wrote: > Not a 100% sure for MySql but I would think it is. > > What happens first is the connection to the server is made in this case > '$database:localhost:3306' and then internally the username and password are > sent. > > If someone can 'sniff' the connection between the perl program and the Server > and if it is not encoded then yes it is in the clear. > I know with DBD::Oracle this connection is encrypted (at least the Pw and > UID) that same should be true of MySql as I think that is part of the SQL > standard is it not?? > > cheers > John >> When connecting to a MySql server with DBI->connect: >> >> $dsn = "dbi:mysql:$database:localhost:3306"; >> $dbh = DBI->connect($dsn, $username, $password) >> >> is the password sent in the clear? If so, how can this be dealt with? >> >> I actually don't care about hiding the plaintext password in the perl >> source file or encrypting the connection with the database, I just >> don't want the world to see my password when it goes out over the >> network. Is that so much to ask for? I would think this would be an >> obvious issue but as far as I can tell, nobody has ever asked this >> question before in the history of the internet. Apparently a direct >> command line connection to a MySql server will not send the password >> in the clear: >> >> mysql -u andrew732 -p -h 123.456.789.876 >> >> but even that took me several hours of googling to figure out. I'm >> not new to Perl but I'm new to databases; is there a good reason that >> nobody seems to care about password security when it comes to >> databases? I would love to be enlightened! Thanks~ >> >> >
Re: MySQL Stored Procedure works differently with DBI
On Feb 16, 2010, at 4:34 PM, Erik wrote: > Solved. This seemes to have been an issue of "matched rows" vs. "changed > rows". The use of stored procedures was not part of the scope of the problem. > The different behaviors were the result of how different clients connect to > the server. > > This thread helped me understand the problem: > http://lists.mysql.com/perl/72. > > It seems that calling ROW_COUNT() when connected via DBD::mysql returns > "matched rows" and not "changed rows" > > I was not able to resolve this by adding "mysql_found_rows=0" to the DSN > passed to DBD::mysql. It seemed to not have any effect. It's mysql_client_found_rows, not mysql_found_rows.
Re: What is the meaning of the "rows" value after a select?
On Dec 1, 2008, at 9:54 AM, Larry W. Virden wrote:
I inherited some perl code that mostly works, but which I've a couple
questions about what it is doing.
Skipping miscellaneous comments, etc. the code sets some variables
from a file, sets its oracle environment, and then does the following:
$oraProdDBH = DBI->connect("dbi:Oracle:", $user_name, $password)
or die "Failed to connect to $DBI:errstr\n";
$oraProdDBH->{RaiseError} = 1;
$oraProdDBH->{AutoCommit} = 0;
$oraProdDBH->{LongReadLen}=4000;
$getMatchRec = $oraProdDBH->prepare
(q{
SELECT sec_person_id, unix_uid FROM csi_core
WHERE sec_person_id != 0 GROUP BY unix_uid, sec_person_id
HAVING COUNT(sec_person_id) > 1
});
$getMatchRec->execute()
or die "Couldn't fetch records from CSI_CORE";
if ($oraProdDBH->rows != 0)
{
and proceeds to do some stuff.
What does that rows member of the oraProdDBH handle represent? When I
print it out, it doesn't appear to be the number of rows selected. In
fact, right now, it has a value of -1.
Is there a meaning for it? From reading the docs, it seems as if
getting past that die statement should mean that the statement
actually executed (regardless of whether it returned any rows).
The docs are not unclear: rows is not reliable for selects. So
don't use it for them.
"
Returns the number of rows affected by the last row
affecting
command, or -1 if the number of rows is not known or not
available.
Generally, you can only rely on a row count after a
non-"SELECT"
"execute" (for some specific operations like "UPDATE" and
"DELETE"), or after fetching all the rows of a "SELECT"
statement.
For "SELECT" statements, it is generally not possible to
know how
many rows will be returned except by fetching them all.
Some
drivers will return the number of rows the application has
fetched
so far, but others may return -1 until all rows have been
fetched.
So use of the "rows" method or $DBI::rows with "SELECT"
statements
is not recommended.
"
Re: How to write a (My)SQL statement with REGEX / RLIKE containing a scalar variable?
On Nov 14, 2008, at 11:50 AM, Brian Manning wrote: On Fri, Nov 14, 2008 at 4:41 AM, Deviloper <[EMAIL PROTECTED]> wrote: That´s again the moment where I ask me, are there any good mysql cookbooks out there? Every sql book I touch seems only to have crappy mysql examples, but tons of oracle example... I take it you've seen O'Reilly Cookbook? http://oreilly.com/catalog/9780596527082/index.html Are my ears burning? :-) The book has its own web site, too (you can get the example code here, for example): http://www.kitebird.com/mysql-cookbook/ Anything wrong with the MySQL docs page? http://dev.mysql.com/doc/ Lots of examples and downloadable docs, and even some annotations from users in the user's manual. Thanks, Brian
Re: can you have a collumn roll over with auto_increment
mmccaws2 wrote: I have an integer field that tracks entries. The question I have is can I set an option so that when auto_increment reaches it's maximum integer value, it will start all over at zero. I'm deleting every 15 minute the earlier entries, so I'm not too worried about the table growing too large. Can mysql be set to roll once it's max value is reached? No.
Re: DBI requires 5.6.0 or 5.6.1?
Robert Hicks wrote: Paul DuBois wrote: Tim, thanks for clarifying. I was wondering because it wasn't clear to me, in writing about DBI, how to characterize what version of Perl a reader should have. I'll write that 5.6.0 is required but 5.6.1 is preferred. What are you writing about DBI? ; ) Robert There is a DBI chapter and appendix in the Doorstop. http://www.kitebird.com/mysql-book/
Re: DBI requires 5.6.0 or 5.6.1?
Tim Bunce wrote:
On Fri, Jul 13, 2007 at 07:38:02PM -0500, Paul DuBois wrote:
From http://search.cpan.org/~timb/DBI-1.58/Changes:
Changes in DBI 1.33, 27th February 2003
NOTE: Future versions of the DBI *will not* support perl 5.6.0 or
earlier.
: Perl 5.6.1 will be the minimum supported version.
Okay, but ...
Changes in DBI 1.38, 21th August 2003
NOTE: The DBI now requires perl version 5.6.0 or later.
(As per notice in DBI 1.33 released 27th February 2003)
Which contradicts the 1.33 section a bit.
Next:
Changes in DBI 1.49 (svn rev 2287), 29th November 2005
Change to require perl 5.6.1 (as advertised in 2003) not 5.6.0.
But DBI.pm says:
require 5.006_00;
BEGIN {
$DBI::VERSION = "1.58"; # ==> ALSO update the version in the pod text below!
}
So I'm afraid I'm confused by this. What's the required version?
The gist is that 5.6.0 is broken in several ways that didn't directly
impact DBI, so I told people that "Perl 5.6.1 will be the minimum
*supported* version" (new emphasis). However, some old systems have 5.6.0
pre-installed so I let 5.6.0 be the minimum *required* version.
The comment you quoted for DBI 1.49 was a puzzle to me so I looked at
the relevant diff. Turns out to be another case of managing expectations:
-if ($] < 5.006001 && $^O ne 'darwin') {
- # we ignore scaring darwin users because they're stuck on 5.6.0 for now
+if ($] < 5.008000) {
warn qq{\a\a\a
**
- Version $] of perl will NOT BE SUPPORTED by future DBI releases.
- You will have to upgrade your perl or stop upgrading DBI.
- Perl version 5.6.1 will be the lowest version supported.
- Using perl $] you may notice some test warnings and two failures.
+ Perl versions below 5.6.1 are no longer supported by the DBI.
+ Perl versions 5.6.x may fail during installation with a complaint
+ about the use of =head3 in the pod documentation.
Press return to continue...
**
};
If someone tells me that the current DBI doesn't work with 5.6.0 then
I'll probably tweak the required version (or fix the problem).
Is this an issue for you or just curiosity?
Tim, thanks for clarifying.
I was wondering because it wasn't clear to me, in writing about DBI, how to
characterize what version of Perl a reader should have. I'll write that
5.6.0 is required but 5.6.1 is preferred.
DBI requires 5.6.0 or 5.6.1?
From http://search.cpan.org/~timb/DBI-1.58/Changes:
Changes in DBI 1.33, 27th February 2003
NOTE: Future versions of the DBI *will not* support perl 5.6.0 or earlier.
: Perl 5.6.1 will be the minimum supported version.
Okay, but ...
Changes in DBI 1.38, 21th August 2003
NOTE: The DBI now requires perl version 5.6.0 or later.
(As per notice in DBI 1.33 released 27th February 2003)
Which contradicts the 1.33 section a bit.
Next:
Changes in DBI 1.49 (svn rev 2287), 29th November 2005
Change to require perl 5.6.1 (as advertised in 2003) not 5.6.0.
But DBI.pm says:
require 5.006_00;
BEGIN {
$DBI::VERSION = "1.58"; # ==> ALSO update the version in the pod text below!
}
So I'm afraid I'm confused by this. What's the required version?
Re: DBI Issue with MySQL query
On 5/11/06 10:06, "Mark Galbreath" <[EMAIL PROTECTED]> wrote:
> Oh SHIT! I had the text files in the wrong directory. It works now. I am so
> DUMB!
Ah, well. Join the club. :-)
>
> Thank you all for all the suggestions and time you spent on my non-problem. I
> wish the error message would have been a little more instructive, however.
>
> mark
>
>>>> Paul DuBois <[EMAIL PROTECTED]> 11-May-06 11:01:14 AM >>>
> Huh. And you say this worked before? I don't see how it could.
>
> $table is an identifier and shouldn't be surrounded by single quotes. That
> particular name doesn't need any quoting, but if it did, the quote character
> should be backtick (`), not apostrophe (').
>
> Also, you might need to say \\n rather than \n, or perhaps even n
> to compensate for both Perl and MySQL stripping escape characters. But that
> wouldn't result in the syntax error you're seeing, it would just make the
> statement not parse data correctly.
>
> Your statement uses $datafile, but you report the value of $filename... typo
> in your message?
>
>
> On 5/11/06 9:30, "Mark Galbreath" <[EMAIL PROTECTED]> wrote:
>
>> There is no error in the log file. The actual terminal print is:
>>
>> %> import_dbf.pl: DBD::mysql::db do failed: You have an error in your SQL
>> syntax; check the manual that corresponds to your MySQL version for the right
>> syntax to use near 'MRJE001' FIELDS TERMINATED BY '|' LINES TERMINATED BY '
>>
>> The actual code is:
>>
>> $rows = $dbh->do("LOAD DATA INFILE '$datafile' IGNORE INTO TABLE '$table'
>> FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n'");
>>
>> $filename = mrje001.txt
>> $table = MRJE001
>>
>> Just is correct. There must be a bug in DBD::mysql(3)
>>
>> ~mark
>>
>>>>> Paul DuBois <[EMAIL PROTECTED]> 11-May-06 09:29:20 AM >>>
>>
>> Check the server's query log to see what statement actually is arriving on
>> the server side.
>>
>> Also, you say you get a malformed SQL syntax error. What's the exact
>> message you get?
>>
>>
>> On 5/11/06 8:02, "Mark Galbreath" <[EMAIL PROTECTED]> wrote:
>>
>>> Tim,
>>>
>>> I've tried every imaginable option: single quotes, double quotes, escaped
>>> quotes, table name only, no quotesnothing works. I think there is a bug
>>> in DBD::mysql(3). I have spent hours and hours googling this problem and no
>>> one else has reported it.
>>>
>>> ~mark
>>>
>>>>>> Tim Bunce <[EMAIL PROTECTED]> 11-May-06 08:40:24 AM >>>
>>> On Wed, May 10, 2006 at 09:26:52AM -0400, Mark Galbreath wrote:
>>>> Of course it's a string. It was a typo.
>>>>
>>>> my $rows = $dbh->do("LOAD DATA INFILE 'data.txt' INTO TABLE 'db.table'
>>>> FIELDS
>>>> TERMINATED BY '|'");
>>>>
>>>> 'db.table' == "database_name.table_name"
>>>
>>> Don't put single quotes around the table name.
>>>
>>> db.table
>>> or`db`.`table`
>>>
>>> should work, but 'db.table' won't.
>>>
>>> Tim.
>>>
>>>>>>> Paul DuBois <[EMAIL PROTECTED]> 10-May-06 09:13:47 AM >>>
>>>>
>>>> On 5/10/06 7:58, "Mark Galbreath" <[EMAIL PROTECTED]> wrote:
>>>>
>>>>> Hi guys,
>>>>>
>>>>> The following query works fine from the MySQL client:
>>>>>
>>>>> LOAD DATA INFILE 'data.txt' INTO TABLE 'db.table' FIELDS TERMINATED BY
>>>>> '|'
>>>>>
>>>>> but fails in perl with a malformed SQL syntax error:
>>>>>
>>>>> my $rows = $dbh->do(LOAD DATA INFILE 'data.txt' INTO TABLE 'db.table'
>>>>> FIELDS
>>>>> TERMINATED BY '|');
>>>>>
>>>>> Anybody have a clue as to why this will not work?
>>>>
>>>> That hasn't the faintest hope of working. You should post the actual code
>>>> that you're using. do() with a non-string argument is surely not your
>>>> actual code.
>>>>
>>>> Also, 'db.table' doesn't look like a valid quoted identifier.
>>>>
>>>>
>>>>
>>>>
>>>
>>
>>
>>
>>
>>
>
>
>
Re: DBI Issue with MySQL query
Huh. And you say this worked before? I don't see how it could.
$table is an identifier and shouldn't be surrounded by single quotes. That
particular name doesn't need any quoting, but if it did, the quote character
should be backtick (`), not apostrophe (').
Also, you might need to say \\n rather than \n, or perhaps even n
to compensate for both Perl and MySQL stripping escape characters. But that
wouldn't result in the syntax error you're seeing, it would just make the
statement not parse data correctly.
Your statement uses $datafile, but you report the value of $filename... typo
in your message?
On 5/11/06 9:30, "Mark Galbreath" <[EMAIL PROTECTED]> wrote:
> There is no error in the log file. The actual terminal print is:
>
> %> import_dbf.pl: DBD::mysql::db do failed: You have an error in your SQL
> syntax; check the manual that corresponds to your MySQL version for the right
> syntax to use near 'MRJE001' FIELDS TERMINATED BY '|' LINES TERMINATED BY '
>
> The actual code is:
>
> $rows = $dbh->do("LOAD DATA INFILE '$datafile' IGNORE INTO TABLE '$table'
> FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n'");
>
> $filename = mrje001.txt
> $table = MRJE001
>
> Just is correct. There must be a bug in DBD::mysql(3)
>
> ~mark
>
>>>> Paul DuBois <[EMAIL PROTECTED]> 11-May-06 09:29:20 AM >>>
>
> Check the server's query log to see what statement actually is arriving on
> the server side.
>
> Also, you say you get a malformed SQL syntax error. What's the exact
> message you get?
>
>
> On 5/11/06 8:02, "Mark Galbreath" <[EMAIL PROTECTED]> wrote:
>
>> Tim,
>>
>> I've tried every imaginable option: single quotes, double quotes, escaped
>> quotes, table name only, no quotesnothing works. I think there is a bug
>> in DBD::mysql(3). I have spent hours and hours googling this problem and no
>> one else has reported it.
>>
>> ~mark
>>
>>>>> Tim Bunce <[EMAIL PROTECTED]> 11-May-06 08:40:24 AM >>>
>> On Wed, May 10, 2006 at 09:26:52AM -0400, Mark Galbreath wrote:
>>> Of course it's a string. It was a typo.
>>>
>>> my $rows = $dbh->do("LOAD DATA INFILE 'data.txt' INTO TABLE 'db.table'
>>> FIELDS
>>> TERMINATED BY '|'");
>>>
>>> 'db.table' == "database_name.table_name"
>>
>> Don't put single quotes around the table name.
>>
>> db.table
>> or`db`.`table`
>>
>> should work, but 'db.table' won't.
>>
>> Tim.
>>
>>>>>> Paul DuBois <[EMAIL PROTECTED]> 10-May-06 09:13:47 AM >>>
>>>
>>> On 5/10/06 7:58, "Mark Galbreath" <[EMAIL PROTECTED]> wrote:
>>>
>>>> Hi guys,
>>>>
>>>> The following query works fine from the MySQL client:
>>>>
>>>> LOAD DATA INFILE 'data.txt' INTO TABLE 'db.table' FIELDS TERMINATED BY
>>>> '|'
>>>>
>>>> but fails in perl with a malformed SQL syntax error:
>>>>
>>>> my $rows = $dbh->do(LOAD DATA INFILE 'data.txt' INTO TABLE 'db.table'
>>>> FIELDS
>>>> TERMINATED BY '|');
>>>>
>>>> Anybody have a clue as to why this will not work?
>>>
>>> That hasn't the faintest hope of working. You should post the actual code
>>> that you're using. do() with a non-string argument is surely not your
>>> actual code.
>>>
>>> Also, 'db.table' doesn't look like a valid quoted identifier.
>>>
>>>
>>>
>>>
>>
>
>
>
>
>
Re: DBI Issue with MySQL query
Ah. I believe this is the first time you've mentioned that it might have
something to do with the version of DBD::mysql. What's the last version you
used for which it worked, and the version that you're using now? That's
pertinent information; omitting it makes it more difficult for people to
help you.
On 5/11/06 9:12, "Mark Galbreath" <[EMAIL PROTECTED]> wrote:
> Yes. And this code works with previous versions of DBD::mysql.
>
>
>>>> Paul DuBois <[EMAIL PROTECTED]> 11-May-06 09:31:06 AM >>>
> None of those quoting options are valid for quoting _identifiers_.
> Did you try using backticks?
>
> http://dev.mysql.com/doc/refman/5.0/en/legal-names.html
>
>
> On 5/11/06 8:02, "Mark Galbreath" <[EMAIL PROTECTED]> wrote:
>
>> Tim,
>>
>> I've tried every imaginable option: single quotes, double quotes, escaped
>> quotes, table name only, no quotesnothing works. I think there is a bug
>> in DBD::mysql(3). I have spent hours and hours googling this problem and no
>> one else has reported it.
>>
>> ~mark
>>
>>>>> Tim Bunce <[EMAIL PROTECTED]> 11-May-06 08:40:24 AM >>>
>>
>> On Wed, May 10, 2006 at 09:26:52AM -0400, Mark Galbreath wrote:
>>> Of course it's a string. It was a typo.
>>>
>>> my $rows = $dbh->do("LOAD DATA INFILE 'data.txt' INTO TABLE 'db.table'
>>> FIELDS
>>> TERMINATED BY '|'");
>>>
>>> 'db.table' == "database_name.table_name"
>>
>> Don't put single quotes around the table name.
>>
>> db.table
>> or`db`.`table`
>>
>> should work, but 'db.table' won't.
>>
>> Tim.
>>
>>>>>> Paul DuBois <[EMAIL PROTECTED]> 10-May-06 09:13:47 AM >>>
>>>
>>> On 5/10/06 7:58, "Mark Galbreath" <[EMAIL PROTECTED]> wrote:
>>>
>>>> Hi guys,
>>>>
>>>> The following query works fine from the MySQL client:
>>>>
>>>> LOAD DATA INFILE 'data.txt' INTO TABLE 'db.table' FIELDS TERMINATED BY
>>>> '|'
>>>>
>>>> but fails in perl with a malformed SQL syntax error:
>>>>
>>>> my $rows = $dbh->do(LOAD DATA INFILE 'data.txt' INTO TABLE 'db.table'
>>>> FIELDS
>>>> TERMINATED BY '|');
>>>>
>>>> Anybody have a clue as to why this will not work?
>>>
>>> That hasn't the faintest hope of working. You should post the actual code
>>> that you're using. do() with a non-string argument is surely not your
>>> actual code.
>>>
>>> Also, 'db.table' doesn't look like a valid quoted identifier.
>>>
>>>
>>>
>>>
>>
>>
>
>
>
Re: DBI Issue with MySQL query
Check the server's query log to see what statement actually is arriving on
the server side.
Also, you say you get a malformed SQL syntax error. What's the exact
message you get?
On 5/11/06 8:02, "Mark Galbreath" <[EMAIL PROTECTED]> wrote:
> Tim,
>
> I've tried every imaginable option: single quotes, double quotes, escaped
> quotes, table name only, no quotesnothing works. I think there is a bug
> in DBD::mysql(3). I have spent hours and hours googling this problem and no
> one else has reported it.
>
> ~mark
>
>>>> Tim Bunce <[EMAIL PROTECTED]> 11-May-06 08:40:24 AM >>>
> On Wed, May 10, 2006 at 09:26:52AM -0400, Mark Galbreath wrote:
>> Of course it's a string. It was a typo.
>>
>> my $rows = $dbh->do("LOAD DATA INFILE 'data.txt' INTO TABLE 'db.table' FIELDS
>> TERMINATED BY '|'");
>>
>> 'db.table' == "database_name.table_name"
>
> Don't put single quotes around the table name.
>
> db.table
> or`db`.`table`
>
> should work, but 'db.table' won't.
>
> Tim.
>
>>>>> Paul DuBois <[EMAIL PROTECTED]> 10-May-06 09:13:47 AM >>>
>>
>> On 5/10/06 7:58, "Mark Galbreath" <[EMAIL PROTECTED]> wrote:
>>
>>> Hi guys,
>>>
>>> The following query works fine from the MySQL client:
>>>
>>> LOAD DATA INFILE 'data.txt' INTO TABLE 'db.table' FIELDS TERMINATED BY
>>> '|'
>>>
>>> but fails in perl with a malformed SQL syntax error:
>>>
>>> my $rows = $dbh->do(LOAD DATA INFILE 'data.txt' INTO TABLE 'db.table' FIELDS
>>> TERMINATED BY '|');
>>>
>>> Anybody have a clue as to why this will not work?
>>
>> That hasn't the faintest hope of working. You should post the actual code
>> that you're using. do() with a non-string argument is surely not your
>> actual code.
>>
>> Also, 'db.table' doesn't look like a valid quoted identifier.
>>
>>
>>
>>
>
Re: DBI Issue with MySQL query
On 5/10/06 7:58, "Mark Galbreath" <[EMAIL PROTECTED]> wrote: > Hi guys, > > The following query works fine from the MySQL client: > > LOAD DATA INFILE 'data.txt' INTO TABLE 'db.table' FIELDS TERMINATED BY '|' > > but fails in perl with a malformed SQL syntax error: > > my $rows = $dbh->do(LOAD DATA INFILE 'data.txt' INTO TABLE 'db.table' FIELDS > TERMINATED BY '|'); > > Anybody have a clue as to why this will not work? That hasn't the faintest hope of working. You should post the actual code that you're using. do() with a non-string argument is surely not your actual code. Also, 'db.table' doesn't look like a valid quoted identifier.
Re: DBD::mysql 3.0003 and 3.0003_1 released
On 5/6/06 11:54, "Patrick Galbraith" <[EMAIL PROTECTED]> wrote: > Dear DBD::mysql users, > > DBD::mysql version 3.0003 (stable, production) and 3.0003_1 (dev) have > been released! > > Version 3.0003 is the production version with server-side prepare > statements turned off by default, and 3.0003_1 is the development > version with server-side prepare statements turned on by default. > > The changes in 3.0003, as in the changelog, are: > > * Fixed bug where if mysql_server_prepare is set and a prepare > fails, only a warning is issued and no error text is > available (Thanks Martin Evans!) > * Added support for ParamValues and associated test (Martin Evans) > * Removed declaration of int num_fields outside a block which > was causing compilation error with some C compilers. > * Fix to typo in Makefile.PL (Martin Evans) > * Added mysql_stmt_reset for when mysql_stmt_execute fails > * Added test for mysql_stmt_execute bug (Martin Evans) > * Fixed syntax for create table ENGINE=InnoDB instead of type=innobase > * Removed tests for old driver emulation > > Note: to turn on server-side prepared statements, simply append > ";mysql_server_prepare" to the connect string or via the driver handle. > Please refer to documentation for further details. Can you append =0 or =1 to indicate explicitly that you want this turned off or on?
Re: Setting the MySQL client character set
On 4/12/06 13:03, "Peter J. Holzer" <[EMAIL PROTECTED]> wrote:
> This just cost me a few hours of debugging, so I am posting it to spare
> others:
>
> The mysql client character set can be set to UTF-8 with:
>
> $dbh->do("set character set utf8");
> $dbh->do("set names 'utf8'");
How does the result differ from simply using set names by itself?
> That's in the manual. What is not in the manual is that the order is
> crucial. If you swap them, it does not work, and you will get very
> strange results.
>
> hp
Re: Not exactly a dbi question
On 4/5/06 14:07, "Jonathan Mangin" <[EMAIL PROTECTED]> wrote: > > - Original Message - > From: "Dr.Ruud" <[EMAIL PROTECTED]> > To: > Sent: Wednesday, April 05, 2006 11:53 AM > Subject: Re: Not exactly a dbi question > > >> Paul DuBois schreef: >> >>> UPDATE tbl_name SET col_name = 0 WHERE col_name = 0; >>> >>> If you've selected the rows-changed count, $sth->rows() will always >>> return 0, because the statement doesn't actually change any col_name >>> value from its current value. >> >> That would be an odd optimization. The update should take place, even if >> the value wouldn't seem to change anything. Hidden fields like "record >> last updated", or triggers, could depend on this. >> >> -- >> Affijn, Ruud >> >> "Gewoon is een tijger." >> > > mySql made this change approx. 3 years ago(?), so it's an old > discussion that I missed. Apparently the whole world agrees > with you. I simply didn't want to populate an application's > change_log with an event that didn't happen. Hence my need > for the old behaviour. > --Jon Well, no. The underlying basis for this behavior is whether you pass the CLIENT_FOUND_ROWS flag to the mysql_real_connect() C API call. This flag has been around for many years (since 3.21); it's nothing new. That's just a point of trivia, though. DBD::mysql lets you select either behavior, so you can have whichever one you want.
Re: Not exactly a dbi question
On 4/5/06 10:04, "Garrett, Philip (MAN-Corporate)" <[EMAIL PROTECTED]> wrote: > > >> -Original Message- >> From: Paul DuBois [mailto:[EMAIL PROTECTED] >> Sent: Wednesday, April 05, 2006 10:56 AM >> To: Garrett, Philip (MAN-Corporate); dbi-users@perl.org >> Subject: Re: Not exactly a dbi question >> >> On 4/5/06 9:46, "Garrett, Philip (MAN-Corporate)" >> <[EMAIL PROTECTED]> wrote: >> >>> >>> >>>> -Original Message- >>>> From: Paul DuBois [mailto:[EMAIL PROTECTED] >>>> Sent: Wednesday, April 05, 2006 10:41 AM >>>> To: Garrett, Philip (MAN-Corporate); dbi-users@perl.org >>>> Subject: Re: Not exactly a dbi question [snip] >>>>>>> >>>>>>> Sorry, I've been answering your question while assuming the mysql > >>>>>>> driver conformed to the documented DBI interface. >>>>>>> >>>>>>> Perhaps this is a mysql thing, not a DBD::mysql thing? >>>>>> >>>>>> Perhaps. What is the non-conformity to the documented DBI > interface >>>>>> to which you refer? >>>>> >>>>> The DBI pod says: >>>>> >>>>> For a non-SELECT statement, execute returns the number of rows >>>>> affected, if known. If no rows were affected, then execute >>> returns >>>>> "0E0", which Perl will treat as 0 but will regard as true. Note >>> that >>>>> it is not an error for no rows to be affected by a statement. > If >>> the >>>>> number of rows affected is not known, then execute returns -1. >>>>> >>>>> The OP said that execute() was returning 1, whether the row was >>>>> affected or not. >>>> >>>> I don't think there is an error here unless the meaning of > "affected" >>>> becomes defined more precisely. For non-SELECT statements, "rows >>>> affected" can mean either "rows matched" >>>> (regardless of whether actually changed) or "rows changed". The >>>> default for MySQL is the rows-changed value, and that was also the >>>> default for DBD::mysql until the 2.9002 change. The default for >>>> DBD::mysql now is the rows-matched value. >>>> >>>> Does the DBI spec require some particular interpretation of >>>> "affected"? (The JDBC spec requires the rows-matched value.) >>> >>> It's making more sense now. >>> >>> I'm a little confused though -- how does a DML operation have a >>> different number for rows-matched and rows-changed? Isn't the point > of >>> DML to change all rows matched? I'll go looking at the JDBC docs for >>> an explanation. >> >> It's most easily seen for a statement such as this: >> >> UPDATE tbl_name SET col_name = 0 WHERE col_name = 0; >> >> If you've selected the rows-changed count, $sth->rows() will always >> return 0, because the statement doesn't actually change any col_name >> value from its current value. >> >> If you've selected the rows-matched count, $sth->rows() will return >> the number of rows for which col_name is 0. > > Thanks for the explanation. I'm glad we had this discussion, because I'm > sure that would have bitten me had I been using JDBC or MySQL. > The MySQL Connector/J driver automatically tells the server to return the rows-matched value due to the JDBC requirement. > I tend to think that the row is still affected in a logical sense. > Whether the DBMS backend decides to physically write a row that hasn't > actually changed really isn't any of my business. > > It gets even more confusing if there's a trigger on the table. In that > case, a trigger could be fired even though the row wasn't physically > updated (with Oracle, anyway). > > Philip >
Re: Not exactly a dbi question
On 4/5/06 9:46, "Garrett, Philip (MAN-Corporate)" <[EMAIL PROTECTED]> wrote: > > >> -Original Message- >> From: Paul DuBois [mailto:[EMAIL PROTECTED] >> Sent: Wednesday, April 05, 2006 10:41 AM >> To: Garrett, Philip (MAN-Corporate); dbi-users@perl.org >> Subject: Re: Not exactly a dbi question >> [snip] >>>>> >>>>> Sorry, I've been answering your question while assuming the mysql >>>>> driver conformed to the documented DBI interface. >>>>> >>>>> Perhaps this is a mysql thing, not a DBD::mysql thing? >>>> >>>> Perhaps. What is the non-conformity to the documented DBI interface >>>> to which you refer? >>> >>> The DBI pod says: >>> >>> For a non-SELECT statement, execute returns the number of rows >>> affected, if known. If no rows were affected, then execute > returns >>> "0E0", which Perl will treat as 0 but will regard as true. Note > that >>> it is not an error for no rows to be affected by a statement. If > the >>> number of rows affected is not known, then execute returns -1. >>> >>> The OP said that execute() was returning 1, whether the row was >>> affected or not. >> >> I don't think there is an error here unless the meaning of >> "affected" becomes defined more precisely. For non-SELECT >> statements, "rows affected" can mean either "rows matched" >> (regardless of whether actually changed) or "rows changed". The >> default for MySQL is the rows-changed value, and that was also the >> default for DBD::mysql until the 2.9002 change. The default for >> DBD::mysql now is the rows-matched value. >> >> Does the DBI spec require some particular interpretation of >> "affected"? (The JDBC spec requires the rows-matched value.) > > It's making more sense now. > > I'm a little confused though -- how does a DML operation have a > different number for rows-matched and rows-changed? Isn't the point of > DML to change all rows matched? I'll go looking at the JDBC docs for an > explanation. It's most easily seen for a statement such as this: UPDATE tbl_name SET col_name = 0 WHERE col_name = 0; If you've selected the rows-changed count, $sth->rows() will always return 0, because the statement doesn't actually change any col_name value from its current value. If you've selected the rows-matched count, $sth->rows() will return the number of rows for which col_name is 0.
Re: Not exactly a dbi question
On 4/5/06 9:26, "Garrett, Philip (MAN-Corporate)" <[EMAIL PROTECTED]> wrote: >> -Original Message- >> From: Paul DuBois [mailto:[EMAIL PROTECTED] >> Sent: Wednesday, April 05, 2006 2:55 AM >> To: Garrett, Philip (MAN-Corporate); dbi-users@perl.org >> Subject: Re: Not exactly a dbi question >> >> On 4/4/06 15:31, "Garrett, Philip (MAN-Corporate)" >> <[EMAIL PROTECTED]> wrote: >> >>>> -Original Message- >>>> From: Paul DuBois [mailto:[EMAIL PROTECTED] >>>> Sent: Tuesday, April 04, 2006 4:20 PM >>>> To: Jonathan Mangin; Garrett, Philip (MAN-Corporate); >>> dbi-users@perl.org >>>> Subject: Re: Not exactly a dbi question >>>> >>>> On 4/4/06 15:06, "Jonathan Mangin" <[EMAIL PROTECTED]> wrote: >>>> >>>>> Autocommit is on. Perhaps you're misunderstanding. An update is not > >>>>> supposed to happen if the column to be updated is equal to the data > >>>>> being stuffed into it. I want $row to reflect that but $row is >>>>> always 1 even when an update did not happen. Is it broke? >>>>> >>>>> --Jon >>>> >>>> No, the default changed: >>>> >>>> 2003-06-22 Rudy Lippan <[EMAIL PROTECTED]> (2.9002) >>>> * moved pod into mysql.pm from mysql.pod >>>> * Changed the default behaviour of mysql_found_rows, so now >>>> 'UPDATE table set field=?' will return the number of rows >>>> matched >>>> and not the number of rows physically changed. You can get the >>>> old >>>> behaviour back by adding "mysql_found_rows=0" to the dsn > passed >>>> to connect. >>> >>> Sorry, I've been answering your question while assuming the mysql >>> driver conformed to the documented DBI interface. >>> >>> Perhaps this is a mysql thing, not a DBD::mysql thing? >> >> Perhaps. What is the non-conformity to the documented DBI interface to >> which you refer? > > The DBI pod says: > > For a non-SELECT statement, execute returns the number of rows > affected, if known. If no rows were affected, then execute returns > "0E0", which Perl will treat as 0 but will regard as true. Note that > it is not an error for no rows to be affected by a statement. If the > number of rows affected is not known, then execute returns -1. > > The OP said that execute() was returning 1, whether the row was affected > or > not. I don't think there is an error here unless the meaning of "affected" becomes defined more precisely. For non-SELECT statements, "rows affected" can mean either "rows matched" (regardless of whether actually changed) or "rows changed". The default for MySQL is the rows-changed value, and that was also the default for DBD::mysql until the 2.9002 change. The default for DBD::mysql now is the rows-matched value. Does the DBI spec require some particular interpretation of "affected"? (The JDBC spec requires the rows-matched value.)
Re: Not exactly a dbi question
On 4/5/06 7:35, "Jonathan Mangin" <[EMAIL PROTECTED]> wrote: > > - Original Message - > From: "Paul DuBois" <[EMAIL PROTECTED]> > To: "Garrett, Philip (MAN-Corporate)" <[EMAIL PROTECTED]>; > > Sent: Wednesday, April 05, 2006 2:55 AM > Subject: Re: Not exactly a dbi question > > >> On 4/4/06 15:31, "Garrett, Philip (MAN-Corporate)" >> <[EMAIL PROTECTED]> wrote: >> >>>> -Original Message- >>>> From: Paul DuBois [mailto:[EMAIL PROTECTED] >>>> Sent: Tuesday, April 04, 2006 4:20 PM >>>> To: Jonathan Mangin; Garrett, Philip (MAN-Corporate); >>> dbi-users@perl.org >>>> Subject: Re: Not exactly a dbi question >>>> >>>> On 4/4/06 15:06, "Jonathan Mangin" <[EMAIL PROTECTED]> wrote: >>>> >>>>> Autocommit is on. Perhaps you're misunderstanding. An update is not >>>>> supposed to happen if the column to be updated is equal to the data >>>>> being stuffed into it. I want $row to reflect that but $row is >>>>> always 1 even when an update did not happen. Is it broke? >>>>> >>>>> --Jon >>>> >>>> No, the default changed: >>>> >>>> 2003-06-22 Rudy Lippan <[EMAIL PROTECTED]> (2.9002) >>>> * moved pod into mysql.pm from mysql.pod >>>> * Changed the default behaviour of mysql_found_rows, so now >>>> 'UPDATE table set field=?' will return the number of rows >>> matched >>>> and not the number of rows physically changed. You can get the >>> old >>>> behaviour back by adding "mysql_found_rows=0" to the dsn passed >>>> to connect. >>> >>> Sorry, I've been answering your question while assuming the mysql driver >>> conformed to the documented DBI interface. >>> >>> Perhaps this is a mysql thing, not a DBD::mysql thing? >>> >>> Oh well. >>> >>> Philip >> >> Perhaps. What is the non-conformity to the documented DBI interface to > which >> you refer? >> >> > Adding mysql_client_found_rows=0 to the dsn behaves as you describe > (though it returns ~ 0E0 [thanks, Mark]). > mysql_found_rows=0 changes nothing. So which is the real name of > this attribute? > --Jon The name is mysql_client_found_rows. Looks like the change note has a typo (twice). Sorry, I didn't even notice this, or I would have pointed it out.
Re: Not exactly a dbi question
On 4/4/06 15:31, "Garrett, Philip (MAN-Corporate)" <[EMAIL PROTECTED]> wrote: >> -Original Message- >> From: Paul DuBois [mailto:[EMAIL PROTECTED] >> Sent: Tuesday, April 04, 2006 4:20 PM >> To: Jonathan Mangin; Garrett, Philip (MAN-Corporate); > dbi-users@perl.org >> Subject: Re: Not exactly a dbi question >> >> On 4/4/06 15:06, "Jonathan Mangin" <[EMAIL PROTECTED]> wrote: >> >>> Autocommit is on. Perhaps you're misunderstanding. An update is not >>> supposed to happen if the column to be updated is equal to the data >>> being stuffed into it. I want $row to reflect that but $row is >>> always 1 even when an update did not happen. Is it broke? >>> >>> --Jon >> >> No, the default changed: >> >> 2003-06-22 Rudy Lippan <[EMAIL PROTECTED]> (2.9002) >> * moved pod into mysql.pm from mysql.pod >> * Changed the default behaviour of mysql_found_rows, so now >> 'UPDATE table set field=?' will return the number of rows > matched >> and not the number of rows physically changed. You can get the > old >> behaviour back by adding "mysql_found_rows=0" to the dsn passed >> to connect. > > Sorry, I've been answering your question while assuming the mysql driver > conformed to the documented DBI interface. > > Perhaps this is a mysql thing, not a DBD::mysql thing? > > Oh well. > > Philip Perhaps. What is the non-conformity to the documented DBI interface to which you refer?
Re: Not exactly a dbi question
On 4/4/06 15:06, "Jonathan Mangin" <[EMAIL PROTECTED]> wrote: > Autocommit is on. Perhaps you're misunderstanding. > An update is not supposed to happen if the column to be > updated is equal to the data being stuffed into it. > I want $row to reflect that but $row is always 1 > even when an update did not happen. Is it broke? > > --Jon No, the default changed: 2003-06-22 Rudy Lippan <[EMAIL PROTECTED]> (2.9002) * moved pod into mysql.pm from mysql.pod * Changed the default behaviour of mysql_found_rows, so now 'UPDATE table set field=?' will return the number of rows matched and not the number of rows physically changed. You can get the old behaviour back by adding "mysql_found_rows=0" to the dsn passed to connect.
Re: Reg:mysqldump
mysqldump is not a mysql command. Invoke it from your command line, just as you invoke mysql from the command line. On 1/30/06 6:45, "Alexander Foken" <[EMAIL PROTECTED]> wrote: > You may want to ask your question on a mysql mailing list, this is a DBI > mailing list. > > Are you sure "mysqldump" is a command of the mysql monitor and not an > independant program? > > Alexander > > > Dilly raja wrote: > >> I have a problem in taking backup of mysql dump in my windows xp machine. I >> have a database called dvd. i want to take the backup of this database so i >> tried the following, but it gives error.So i got doubt, whether >> the following commands should be executed in teh "mysql>" prompt or in some >> other place. Please give me a result. >> >> >> Enter password: >> Welcome to the MySQL monitor. Commands end with ; or \g. >> Your MySQL connection id is 306 to server version: 5.0.18-nt >> >> Type 'help;' or '\h' for help. Type '\c' to clear the buffer. >> >> mysql> use dvd >> Database changed >> mysql> mysqldump dvd>c:\mysqldump\backupfile.sql >> ERROR: >> Unknown command '\m'. >> ERROR: >> Unknown command '\b'. >> -> ; >> ERROR 1064 (42000): 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 >> 'mysql >> dump dvd>c:\mysqldump\backupfile.sql' at line 1 >> mysql> mysqldump dvd>backupfile.sql >> -> ; >> ERROR 1064 (42000): 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 >> 'mysql >> dump dvd>backupfile.sql' at line 1 >> mysql> mysqldump -u root -p autoraja dvd>backup.sql >> -> mysqldump -u admin -p admin accounts>accounts.sql >> -> ; >> ERROR 1064 (42000): 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 >> 'mysql >> dump -u root -p autoraja dvd>backup.sql >> mysqldump -u admin -p admin account' at line 1 >> >> >> >> since i am new to mysql i dont know where to execute this commands. please >> give me a result. >> >> -- >> Friendly, >> Raja.M >> >> >> >
Re: Can't use mysql's curdate() as bind variable?
curdate() is a function, not a data value.
On 1/6/06 10:18, "Jonathan Mangin" <[EMAIL PROTECTED]> wrote:
> curdate() works if $edate is embedded directly in my
> sql statement, but not as a bind variable. $bdate works
> fine.
>
> my $bdate = $q->param('bdate') || '%';
> my $edate = $q->param('edate') || 'curdate()';
>
> my $sql = "create table $temp_tbl
>(date date,
>uid varchar(14))
>engine = memory
>select date,
>? as uid
>from calendar
>where date between ? and $edate";
> my $sth = $dbh->prepare($sql);
> $sth->execute($uid, $bdate) || die $sth->errstr();
>
> It's mostly just irritating.
> Am I doing something wrong?
>
> Thanks,
> Jon
>
Re: undefined behaviour for sub-transactions?
On 11/14/05 21:52, "Tyler MacDonald" <[EMAIL PROTECTED]> wrote: > I'm looking through the DBI documentation and I can't find anything about > sub transactions (eg; if you do a "begin_work" ... "commit/rollback" when > begin_work has already been called). > > This seems to work under DBD::Pg. Under DBD::mysql, I'm not sure if the > begin_work is just being ignored, or if creating sub-transactions is the > cause of my woes. (see http://rt.cpan.org/NoAuth/Bug.html?id=15803). MySQL doesn't have sub-transactions (nested transactions). You can set savepoints within a transaction and roll back to a given savepoint and then continue the transaction from there. > > I think some pod on the "Transactions" heading of the DBI manpage would be > useful, telling us what to expect (even if it is the unexpected) when we use > sub-transactions under DBI. If this is an engine-specific thing, could DBI > be made to be capable of figuring out whether a particular engine supports > sub-transactions? Then any sub-begin-works could just be ignored, and, say, > attempting to "commit" when a "rollback" has already been called could cause > a fatal error. Or is there already some way to take care of this? There's > very little about transactions in the DBI documentation. > > Thanks, > Tyler >
Re: MySQL 4.1+ Password Incompatibility
You'll need to rebuild DBD::mysql linked against a 4.1 MySQL client library. On 8/25/05 8:38, "Shawn Iwinski (siwinski)" <[EMAIL PROTECTED]> wrote: > I'm having trouble getting DBI connect to a MySQL 4.1+ server. I have > to use MySQL's OLD_PASSWORD to set the password on the server in order > for DBI to connect. This means less secure passwords for MySQL -- 41 > byte hashes instead of 45 byte hashes. Could someone assist in helping > me connect to MySQL through DBI using the newer password scheme? > > Thanks in advance, > Shawn Iwinski
Re: Mysql source command
source is a command understood only by the mysql client program. It's not a
SQL statement.
On 8/10/05 9:13, "Ian Roth" <[EMAIL PROTECTED]> wrote:
> I have been trying to execute a Mysql source command to import a database
> backup. Perl DBI outputs the following error when a script containning the
> source command is used:
>
> 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 'source /tmp/file' at line 1 at /etc/scripts/db.pl line 23.
>
> I have tried to perform both a do() and execute() as in the following lines:
>
> $db->do("source /tmp/file");
>
> and
>
> $source = $db->prepare("source /tmp/file");
> $source->execute();
>
> Perl DBI used is the current release and the operating system is Slackware 9.1
> with Perl 5 and Mysql 4.1.12. I am able execute the same "source /tmp/file"
> command on the mysql command line with success, thus this has been quite
> difficult bug to work out.
>
> Ian
> iroth at closednetworks dot com
>
Re: execute failed: Lost connection to MySQL server during query(2013)
It might be that DBD::mysql on your system is built with a MySQL client library older than 4.0.16. Your message made me remember that there is this item in the MySQL 4.0.16 change notes: If you are using this release on Windows, you should upgrade at least your clients (any program that uses libmysql.lib) to 4.0.16 or above. This is because the 4.0.15 release had a bug in the Windows client library that causes Windows clients using the library to die with a Lost connection to MySQL server during query error for queries that take more than 30 seconds. This problem is specific to Windows; clients on other platforms are unaffected. This sounds similar to your report. On 8/2/05 17:36, "Rob Craig" <[EMAIL PROTECTED]> wrote: > Hi, > > I have a perl script that uses DBI but it times out on queries that > take over 30 seconds. The same query works fine if I issue it directly > from the mysql client. I have included the program, the trace output > from the program, the server variables and the table definition. There > are approximately 8.5 million rows in this table. The computer is > Windows 2000 and perl version is 5.8.3. The message printed when it > times out is: execute failed: Lost connection to MySQL server during > query(2013). > > Any help would be much appreciated. > > Thanks. > > Rob > > perl script: > > #!c:/perl/bin/perl.exe > > use DBI; > connect > DBI->trace(4); > $sql = "select count(DISTINCT seq) FROM Peptide;"; > $sth = $dbh->prepare($sql) or warn "prepare failed: > $DBI::errstr($DBI::err)\n"; > $rows = $sth->execute() or warn "execute failed: $DBI::errstr($DBI::err)\n"; > @result=$sth->fetchrow_array(); > $count = @result[0]; > print "Count: $count\n"; > $sth->finish(); > disconnect > > trace output level 4: > > DBI 1.42-ithread default trace level set to Ox4/0 (in pid 2804) > Note: perl is running without the recommended perl -w option > -> prepare for DBD::mysql::db (DBI::db=HASH(0x1bce098)~0x1bf9354 > 'select count(DISTINCT seq) FROM Peptide;') thr#15d4314 > New DBI::st (for DBD::mysql::st, parent=DBI::db=HASH(0x1bf9354), id=) > dbih_setup_handle(DBI::st=HASH(0x1bf94bc)=>DBI::st=HASH(0x15d52e0), > DBD::mysql::st, 1bf94c8, Null!) > dbih_make_com(DBI::db=HASH(0x1bf9354), 1bf9c54, DBD::mysql::st, 208, > 0) thr#15d4314 > Setting mysql_use_result to 0 > <- prepare= DBI::st=HASH(0x1bf94bc) at timeouttest.pl line 11 > -> execute for DBD::mysql::st (DBI::st=HASH(0x1bf94bc)~0x15d52e0) > thr#15d4314 > -> dbd_st_execute for 01bbc538 > Lost connection to MySQL server during query error 2013 recorded: Lost > connection to MySQL server during query > <- dbd_st_execute -2 rows > !! ERROR: 2013 'Lost connection to MySQL server during query' (err#0) > <- execute= undef at timeouttest.pl line 12 > -> $DBI::errstr (&) FETCH from lasth=HASH >>> DBD::mysql::st::errstr > <- $DBI::errstr= 'Lost connection to MySQL server during query' > -> $DBI::err (*) FETCH from lasth=HASH > <- $DBI::err= 2013 > execute failed: Lost connection to MySQL server during query(2013) > !! ERROR: 2013 CLEARED by call to fetchrow_array method > -> fetchrow_array for DBD::mysql::st > (DBI::st=HASH(0x1bf94bc)~0x15d52e0) thr#15d4314 > -> dbd_st_fetch for 01bbc538, chopblanks 0 > fetch() without execute() error 19 recorded: fetch() without execute() > !! ERROR: 19 'fetch() without execute()' (err#0) > <- fetchrow_array= ( ) [0 items] row-1 at timeouttest.pl line 13 > Count: > !! ERROR: 19 CLEARED by call to finish method > -> finish for DBD::mysql::st (DBI::st=HASH(0x1bf94bc)~0x15d52e0) > thr#15d4314 > <- finish= 1 at timeouttest.pl line 17 > -> disconnect for DBD::mysql::db (DBI::db=HASH(0x1bce098)~0x1bf9354) > thr#15d4314 > &imp_dbh->mysql: 1bf9cac > <- disconnect= 1 at timeouttest.pl line 29 via timeouttest.pl line 19 > Disconnected > -- DBI::END > -> disconnect_all for DBD::mysql::dr > (DBI::dr=HASH(0x1b52830)~0x1bce0bc) thr#15d4314 > <- disconnect_all= (not implemented) at DBI.pm line 657 via > timeouttest.pl line 0 > ! -> DESTROY for DBD::mysql::db (DBI::db=HASH(0x1bf9354)~INNER) > thr#15d4314 > ! <- DESTROY= undef during global destruction > dbih_clearcom 0x1bce098 (com 0x1bf9c54, type 2) done. > > ! -> DESTROY for DBD::mysql::dr (DBI::dr=HASH(0x1bce0bc)~INNER) > thr#15d4314 > ! <- DESTROY= (not implemented) during global destruction > dbih_clearcom 0x1b52830 (com 0x1bf8f14, type 1) done. > > ! <> DESTROY for DBI::dr=HASH(0x1b52830) ignored (inner handle gone) > ! -> DESTROY for DBD::mysql::st (DBI::st=HASH(0x15d52e0)~INNER) > thr#15d4314 > ! <- DESTROY= undef during global destruction > dbih_clearcom 0x1bf94bc (com 0x1bfda04, type 3) done. > > ! <> DESTROY for DBI::st=HASH(0x1bf94bc) ignored (inner handle gone) > ! <> DESTROY for DBI::db=HASH(0x1bce098) ignored (inner handle gone) > > table definition: > > +-
Re: Suggested Patch for DBD::mysql
On 4/26/05 9:03, "Rudy Lippan" <[EMAIL PROTECTED]> wrote:
> On Mon, 25 Apr 2005, James D. White wrote:
>
>> I have a couple of suggested patches for DBD::mysql. These patches were
>> based upon
>> DBD-mysql-2.9006.
>>
>> The first patch is to correct a typo in "t/lib.pl".
>> 47c47
>> < $::test_password = $::test_passowrd || $ENV{'DBI_PASS'} || '';
>> ---
>>> $::test_password = $::test_password || $ENV{'DBI_PASS'} || '';
>>
>> The second patch is a change to Makefile.PL so that is creates a modified
>> "t/mysql.mtest".
>> 129c129
>> < "\$::test_user = \$opt->{'testuser'};\n" .
>> ---
>>> "\$::test_user = \$opt->{'testuser'} if \$opt->{'testuser'};\n"
>>> .
>> 131c131
>> < "\$::test_password = \$opt->{'testpassword'};\n" .
>> ---
>>> "\$::test_password = \$opt->{'testpassword'} if
>>> \$opt->{'testpassword'};\n" .
>> 133,135c133,135
>> < "\$::test_dsn = \"DBI:mysql:\$::test_db\";\n" .
>> < "\$::test_dsn .= \":\$::test_host\" if \$::test_host;\n" .
>> < "\$::test_dsn .= \":\$::test_port\" if \$::test_port;\n" .
>> ---
>>> "\$::test_dsn = \"DBI:mysql:\$::test_db\" if \$::test_db;\n" .
>>> "\$::test_dsn .= \":\$::test_host\" if \$::test_host &&
>>> \$::test_db;\n" .
>>> "\$::test_dsn .= \":\$::test_port\" if \$::test_port &&
>>> \$::test_db;\n" .
>>
>
> Looks good, I'll apply this.
>
>> With these two patches, you can use environment variables DBI_USER, DBI_PASS,
>> and
>> DBI_DSN to pass the test username, test password, and test hostname needed
>> during
>> "make test". Without these changes, the "t/mysql.mtest" that is created
>> overwrites the
>> values obtained from the environment variables. If the default values (null
>> user, null
>> password, and localhost) are not correct, this forces the installer to
>> specify these values
>> on the "perl Makefile" command line. The values are then written to
>> "t/mysql.mtest".
>> The test hostname does not worry me, but I do not like to leave the test
>> username and
>> test password lying around in a file. This seems like a security problem
>> waiting to happen.
>
> I can see your point; however, I don't think that a test account that only
> needs to be able to write to the test database would be too much of a concern
> esp. if the test scripts were run on a non-production system hitting a
> non-production database.
I disagree. At least the way it used to work, the test phase created a
dot-file in which to store the username and password. You wouldn't
necessarily even be aware that such a file was lying around on your system.
If, just to get things working, you happened to have given the root account
parameters for the purpose of connecting, you'd have a significant security
hole. But having any account name/password stored in a file of which you're
not aware is a problem.
>
> In any event, it is nice to be able to specify the connection params by
> envionment variables... And the next logical step is to get DBD::mysql to read
> my.conf for the information.
>
>
>
> Thank you,
>
> Rudy.
>
Re: What is wrong with select?
On 4/2/05 12:11, "Robert A. Rawlinson" <[EMAIL PROTECTED]> wrote: > I am trying to get the index for a state and when I do: > my $sql1 = "SELECT id, name_short, name_long FROM states WHERE > name_short='PA"; > $sth1 = $dbh->prepare($sql1); > # > $sth1->execute(); > > It works. But when I do: > > $StateProvince = 'PA'; > my $sql1 = "SELECT id, name_short, name_long FROM states WHERE > name_short=$StateProvince"; > $sth1 = $dbh->prepare($sql1); > # > $sth1->execute(); > > I get a message that: > > DBD::mysql::st execute failed: Unknown column 'PA' in 'where clause' at > TestDBI.pl line 14. > > It seems to me this should work. I am new at this so I must have done > something wrong, but what? > Thanks for any help you can offer. > Bob Rawlinson If you print the value of $sql1 in each instance, you'll see the difference between them.
Re: Got error 134 from Storage engine?
On 3/9/05 18:30, "Data Man" <[EMAIL PROTECTED]> wrote:
>
> Hi,
>
> I'm using perl 5.8.5 with DBI and Mysql 4.1 on FreeBSD 5.3. I am
> receiving an error 'Got error 134 from Storage engine' when executing
> many mysql queries in a loop. At random iterations under identical
> conditions the app fails with the above error.
>
> My loop looks like this
>
> foreach $itm (@Items) {
>
> create query string with $itm
> prepare
> execute
> while ($sth->fetchrow_array)
>
> }
>
> I've googled around and cannot locate this error. Anyone know how to
> locate the problem? Thanks, DM.
>
% perror 134
MySQL error code 134: Record was already deleted (or record file crashed)
Try REPAIR TABLE to see if that fixes the problem.
Re: max_allowed_package problem
On 1/26/05 8:19, "Wojciech Karlowski" <[EMAIL PROTECTED]> wrote: > Hi, > > I am using Linux Debian (Sid) with libdbi-perl version 1.46-5 and mySQL > version 4.1. Currently, I am trying to set up a perl script which will > use the DBI module to connect to database. Everything works fine unless > I try to insert large (around 18MB) text object. When I do this I get a > following error: > > !! ERROR: 2020 'Got packet bigger than 'max_allowed_packet' > > I have already changed to settings for the "max_allowed_package" > variable in the mysql server config (/etc/mysql/my.cnf) and in the local > settings (~/.my.cnf) file. > > When I issue the same statement using the mysql client everything works > w/o problems. > > Does someone know where can I set up the "max_allowed_package" variable > for the DBI-driven connection? Obviously, it doesn't care about the > server or client settings! > > TIA, Wojtek > Try this: Include the mysql_read_default_file or mysql_read_default_group option in your DNS, and then put the max_allowed_packet setting in the appropriate group. These options trigger calls to the mysql_options() C API function and have the effect of telling mysql_real_connect() to read the option file for options even when they're not available from the command line.
Re: :mysql
Henry McGuinness wrote:
Hi again,
Thought this might be of interest: The same version of
DBD_MySQL (2.9004) worked fine with MySQL 4.0.21. So
either DBD-MySQL* needs changes to deal with the new
password scheme or there's a problem with 4.1.5 in
particular (?)
cheers
Henry
*my version is the same as the latest on CPAN
DBD-mysql must be compiled against the 4.1 MySQL client
libraries. The 4.0 libraries don't know how to authenticate
to a MySQL 4.1 server unless the server is running with
--old-passwords.
--- [EMAIL PROTECTED] wrote:
Henry,
Have you tried any of these fixes?
http://dev.mysql.com/doc/mysql/en/Old_client.html
Are you sure you're running MySQL 4.1.5 because the
'upgrade client' error
is typically caused by using a post 4.1.1 password
scheme with a pre 4.1.1
client.
Elliot
Elliot M. Fielstein, Ph.D.
Neuropsychologist and Health Systems Specialist
VA Medical Center, Nashville TN
and
Assistant Professor of Psychiatry and
Research Fellow in Biomedical Informatics
Vanderbilt University Medical Center
-Original Message-
From: Henry McGuinness
[mailto:[EMAIL PROTECTED]
Sent: Monday, October 11, 2004 12:15 PM
To: [EMAIL PROTECTED]
Subject: DBD::mysql
Hi folks,
This seems to be a DBI issue. I've just reinstalled
most things so I'm running:
Activestate Perl 5.8.4
MySQL 4.1.5 ("gamma" but it should be ok surely?)
DBI 1.43
DBD::MySQL 2.9004
running on Windows XP professional.
Tried to do a simple select (works fine from MYSQL
in
command prompt - users have the right privileges).
From a perl script or from Apache I get the
following
message:
"Client does not support authentication protocol
requested by server; consider upgrading MySQL
client"
Relevant code:
use DBI;
my $dbh =
DBI->connect("dbi:mysql:namesDB;host=localhost",".",".")
or die "Can't make 1st database connect:
$DBI::errstr\n";
my $sth1 = $dbh->prepare("SELECT name, address FROM
tbl_people WHERE (Txtname = ?)");
$sth1->execute("Dave");
while (@res=$sth1->fetchrow_array) {
print "@res[0] : @res[3]: @res[4]\n\n" ;
}
$sth1->finish();
$dbh->disconnect();
(Have also tried without the "host=localhost" part
of
the connect statement)
Anyone seen this/know what's going on?
thanks
Henry
Re: Possible DBI logos
At 15:28 -0400 6/4/04, Michael L Kieras wrote: > In honor of Tim Bunce's 40th birthday, here are some samples of possible DBI logos: > http://www.vpservices.com/jeff/programs/dbi-logo/ Other than the top and bottom middle row, the rest have a sort of Buckaroo Banzai logo thing going on. Similar to the Ballantine Books logo as well. That could be a problem, aside from the issue that visually they imply "BB" rather than "DBI". -- Michael Kieras http://people.umass.edu/michael/
Re: One last time... Re: Follow Up: Re: DBI->state()
At 18:27 +0100 3/31/04, Tim Bunce wrote: On Wed, Mar 31, 2004 at 11:31:51AM -0500, Keith C. Ivey wrote: On 31 Mar 2004 at 11:23, [EMAIL PROTECTED] wrote: > I at least feel a bit better that I am not loosing my mind, or at > least not on this point. I was under the impression that it was > preferred that we use the methods instead of directly accessing the > underlying values. Using the methods, as documented in the Cheetah Book, is fine. But those are *handle* methods, and 'DBI' is not a handle. What people are discussing is the use of DBI->state(), which is wrong, not $dbh->state() or $sth->state(), which are okay. Exactly! So, to restate the question again... does anyone use these specific methods in the DBI class (*not* on handles): DBI->err or DBI->errstr in their code? Tim. No. At least, after this discussion, I hope not. :-)
Re: Installing DBD::mysql on MacOSX 10.3.2 (panther)
At 15:02 -0500 1/20/04, Eric Gorr wrote: I am having great difficulty installing DBD::mysql on MacOSX 10.3.2 Server (panther). The full build log is below. The command I used to install was: perl -MCPAN -e 'install "DBD::mysql"' I am using the recent version of mysql installed from FINK. Any ideas? (If there is a better forum for this question, please let me know.) I found the following message helpful: http://nntp.x.perl.org/group/perl.macosx/6253
Re: Using DBD::mysql with ping
At 23:05 + 1/5/04, Adam Gent wrote: Hi All, I am trying to work on a piece of code which opens a connection and keeps that connection open for an long time days if not months. In order to see if the connection is open I am wanting to use the ping command to see if the connection is still open. I am using the code listed below to test to see if this is working. It appears to be working ok, when I start the script running it connects to the mysql server and prints out the correct information showing that the connection is there and that data is been retrieved from the database. When I stop the mysql server it returns the correct data saying that the ping failed. The question I have is that when I started mysql server again the script prints out the information saying that it is connected to the mysql server and that it is retrieving data. I would have thought that once the connection has been broken that would be it and I would have to issue the connect statement again to connect to the DB, but it appears that the connection to the database is established automatically. Is this to be expected? Assuming the DBD::mysql ping method is based on the mysql_ping() C API call, yes. mysql_ping() attempts to reconnect using the original connection parameters if it finds the connection down.
Re: (Fwd)
At 19:47 + 12/17/03, Tim Bunce wrote:
- Forwarded message from Jerry Rocteur <[EMAIL PROTECTED]> -
Delivered-To: [EMAIL PROTECTED]
Date: Wed, 17 Dec 2003 18:27:12 +0100
Subject:
From: Jerry Rocteur <[EMAIL PROTECTED]>
To: [EMAIL PROTECTED]
Hi Tim,
I'm sorry if this is not the correct way to do this but I just wanted
to report something that is worrying me.
Please let me know what I must do if this is not the correct way to
report this problem.
I'm on an ENSIM system and my DB name is called rocteur_com-house
I set $db_name to rocteur_com-house then I run this: (I copied parts of
the script the MySQL book by Paul DuBois, the original)
# retrieve reference to single-column array of table names
my $ary_ref = $dbh->selectcol_arrayref (qq{ SHOW TABLES FROM
$db_name });
I get this error:
http://www.w3.org/1999/xhtml";
lang="en-US">rocteur_com-house Database Browser
DBD::mysql::db selectcol_arrayref failed: You have an error in your SQL
syntax near '-house ' at line 1 at ./db_browse.pl line 86.
rocteur_com-house Database
BrowserSelect a table by clicking on its name:
If your database name contains a - character, then you must quote it
using backticks. Try: qq{ SHOW TABLES FROM `$db_name` }
I don't think backtick-quoting is mentioned in the first edition of
the book, because it hadn't been implemented in MySQL yet. :-)
I've tried to escape the _ and the - with no success..
So it seems that DBI spits with this function .. Apart from that every
thing seems to be working OK..
I've tried on:
perl -MCGI -e 'print "CGI.pm version $CGI::VERSION\n";'
CGI.pm version 2.752
and CGI.pm version 3.00
Again I apologize if this is NOT the correct way to report a problem.
Best Regards,
Jerry
- End forwarded message -
Re: fwd: Intresting case of SQL Injection
The main problem here was that developers where trusting in PHP auto escaping which worked in MySQL (and probably PostgreSQL) but not in MSSQL. Everything after the 9th word in this sentence should be replaced by a period.
Re: Field terminated by
At 19:04 +0100 11/6/03, W. Bauer wrote: > And from http://search.cpan.org/~rudy/DBD-mysql-2.9003/lib/DBD/mysql.pm mysql_local_infile As of MySQL 3.23.49, the LOCAL capability for LOAD DATA may be disabled in the MySQL client library by default. If your DSN contains the option "mysql_local_infile=1", LOAD DATA LOCAL will be enabled. (However, this option is effective if the server has also been configured to disallow LOCAL.) Rudy, This actually should read "However, this option is *ineffective* if the server has also ..." :-) Thanks a lot, Tim, that solved exactly the problem! Best regards, Wolfgang Bauer
Re: mysql server 4.1
At 10:06 -0500 10/17/03, phani wrote: Hi, I have mysql server 4.1 running on redhat 9.I have perl DBD 2.9002-1.my perl is 5.8.0.When I want to connect to mysql server 4.1 I get an error message saying that the mysql client does not support the security protocol requested by server.Try to update the mysql client. Iám able to log onto mysql server thru normal command line mysql client.How do I update the mysql client for the perl dbi. Recompile DBD::mysql with the 4.1 client libraries. thanx phani
Re: column_info, mysql, and SET/ENUM values
At 13:54 -0400 10/13/03, Scott R. Godin wrote: A while back in February, Tim and Ron were discussing DBD::mysql and column_info.. This is the only thing I could find that *may* relate to my question, so I'm asking here for further clarification. It's not clear in the docs (at least not to me) whether I can have column_info return the possible values of a SET or ENUM column, without hard-coding them into my scripts. In other words I'd like my script to be able to tell when the values of the SET column have changed for the purposes of building an input form via CGI.pm to allow multiple selects from the input form, and to correctly display the current selection of SET items. Before I hare off and write a bunch of testing scripts, I'd just like to know if I'm on the right track. Will I be able to obtain this information, and is column_info the right way to do it? I don't know if column_info can do it, but you don't need to write the code. Go to http://www.kitebird.com/mysql-perl/ and get the webdb distribution. The WebDB/TableInfo.pm file in it has a class that uses SHOW COLUMNS to get the column information and yanks it apart. Part of the information is the set of SET or ENUM values. Or, if you want to write the code anyway, this might give you some ideas.
Re: problem installing DBD-mysql-2.9002 on OS X 10.2.8 (perl 5.8.1)
At 16:38 -0400 10/10/03, Anderson, James H [IT] wrote: (I hope this is the right list :) Unlike installing any other perl module I've tried, this isn't obvious or straightforward... 1. I installed DBI-1.38 2. When I ran 'perl Makefile.PL' for DBD-mysql-2.9002 I got 'Warning: prerequisite DBI 1.08 not found.' Any suggestions? Thanks, jim I install on Mac OS X using CPAN: # perl -MCPAN -e shell cpan> install DBI cpan> force install DBD::mysql I use "force" for DBD::mysql, because it wants to run tests by connecting to the MySQL server using an anonymous account, and I always remove those accounts. force causes the install to proceed even though the tests fail.
Re: Beginner's insert problem results in unknown column value
At 18:37 -0700 10/5/03, jmulkerin wrote:
I used the placeholders and it works.
Thanks.
I still don't undertand why it didn't work the fisrt time.
For exactly the reason I indicated. The query you were executing was:
INSERT INTO log (Nbr, Bdate, dtime)
VALUES (123456, 10012003, 0928A)
For the query to be correct, it would have to be:
INSERT INTO log (Nbr, Bdate, dtime)
VALUES (123456, '10012003', '0928A')
You didn't quote the data values that needed to be quoted.
Remember, MySQL allows column names to begin with a digit,
so an unquoted 0928A is taken to be a column name.
John
- Original Message -
From: "Paul DuBois" <[EMAIL PROTECTED]>
To: "jmulkerin" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Sunday, October 05, 2003 5:42 PM
Subject: Re: Beginner's insert problem results in unknown column value
At 16:01 -0700 10/5/03, jmulkerin wrote:
>Oh most literate ones, can you tell me what's wrong?
You didn't quote your data values, so they appear to be
column references.
Use placeholders instead, you'll be happier:
my $stmt = qq {INSERT INTO log (Nbr, Bdate, dtime)
VALUES (?,?,?)};
my $sth = $dbh->prepare( $stmt) || die "prepare: $stmt: $DBI::errstr";
$sth->execute($Nbr, $Bdate, $Dtime) || die "execute: $stmt: $DBI::errstr";
>
>I get this error:
>DBD::mysql::st execute failed: Unknown column '0928A' in 'field
>list' at test3.pl line 20.
> >execute: INSERT INTO log (Nbr, Bdate, dtime)
> VALUES (123456, 10012003, 0928A): Unknown column '0928A in
> >'field list' at test3.pl line 20.
>
>>From this script:
>#use strict;
>#use DBI qw(:sql_types);
>use DBI;
>#my $dbh;
>my ($username, $password, $eamil, $Bdate, $AcctNbr, $Dtime);
>my $username = 'jblowk';
>my $password = 'abcdefg';
>my $email = '[EMAIL PROTECTED]';
>my $Bdate = '10012003';
>my $Nbr = '123456';
>my $Dtime = '0928A';
>DBI->trace(2,"dbi.out"); # trace everything to
> # dbi.out
>my ($username, $password, $email);
>#my ($dbh, $sth, $count);
>$dbh = DBI->connect ("DBI:mysql:database=logbase;host=localhost;
>'userid', 'password'") or die ("Can't connect to logbase");
>my $stmt = qq {INSERT INTO log (Nbr, Bdate, dtime)
> VALUES ($Nbr, $Bdate, $Dtime)};
>my $sth = $dbh->prepare( $stmt) || die "prepare: $stmt: $DBI::errstr";
>$sth->execute || die "execute: $stmt: $DBI::errstr";
>$sth->finish ();
>$dbh->disconnect ();
Re: Beginner's insert problem results in unknown column value
At 16:01 -0700 10/5/03, jmulkerin wrote:
Oh most literate ones, can you tell me what's wrong?
You didn't quote your data values, so they appear to be
column references.
Use placeholders instead, you'll be happier:
my $stmt = qq {INSERT INTO log (Nbr, Bdate, dtime)
VALUES (?,?,?)};
my $sth = $dbh->prepare( $stmt) || die "prepare: $stmt: $DBI::errstr";
$sth->execute($Nbr, $Bdate, $Dtime) || die "execute: $stmt: $DBI::errstr";
I get this error:
DBD::mysql::st execute failed: Unknown column '0928A' in 'field
list' at test3.pl line 20.
execute: INSERT INTO log (Nbr, Bdate, dtime)
VALUES (123456, 10012003, 0928A): Unknown column '0928A in
'field list' at test3.pl line 20.
From this script:
#use strict;
#use DBI qw(:sql_types);
use DBI;
#my $dbh;
my ($username, $password, $eamil, $Bdate, $AcctNbr, $Dtime);
my $username = 'jblowk';
my $password = 'abcdefg';
my $email = '[EMAIL PROTECTED]';
my $Bdate = '10012003';
my $Nbr = '123456';
my $Dtime = '0928A';
DBI->trace(2,"dbi.out"); # trace everything to
# dbi.out
my ($username, $password, $email);
#my ($dbh, $sth, $count);
$dbh = DBI->connect ("DBI:mysql:database=logbase;host=localhost;
'userid', 'password'") or die ("Can't connect to logbase");
my $stmt = qq {INSERT INTO log (Nbr, Bdate, dtime)
VALUES ($Nbr, $Bdate, $Dtime)};
my $sth = $dbh->prepare( $stmt) || die "prepare: $stmt: $DBI::errstr";
$sth->execute || die "execute: $stmt: $DBI::errstr";
$sth->finish ();
$dbh->disconnect ();
Re: mySQL, how to tell if the handle is connected
At 7:20 PM +0100 9/19/03, Adam Gent wrote: Hi All, I am using mySQL and need away to tell if a connection is open. I am aware that there is the Active command, within the DBI but this does not appear to be supported in DBD::mysql Is there any other way to tell if the connection is still open. Invoke its ping() method.
RE: OT - mysql hostname
At 12:30 -0500 8/22/03, [EMAIL PROTECTED] wrote: On Fri, 22 Aug 2003 12:43:47 -0400, Hardy Merrill <[EMAIL PROTECTED]> wrote: I know this is off-topic, but am hoping one of you who knows MySQL well will be able to answer this quickly, as I haven't been able to find the answer in books or on the internet. Is there a mysql command that I can issue at the 'mysql>' client prompt that will tell me the hostname of the machine on which the MySQL database (server) is running? 'status', it provides more than you requested don't know if there is a way to limit it. See the 'Connection' property... Actually, the answer is "no". "Connection" in the status message may indicate a value that, while correct, is not useful as a host name. (Try connecting over a named pipe to Windows server and check the status message, for example.)
Re: DBD::mysql connect - pw in cleartext?
At 11:06 -0400 8/14/03, Hardy Merrill wrote: Rudy Lippan [EMAIL PROTECTED] wrote: On Thu, 14 Aug 2003, Hardy Merrill wrote: > If I'm running DBI/DBD::mysql on a mysql client machine, > and I want to connect to a different mysql server > machine, I must supply the username and password > in the DBI connect. So, that username and password > will be passed from the client machine to the server > machine in cleartext, right? My question is, how can I do > that such that the username and password is NOT > passed over the wire in cleartext - in the connect > statement? In theory you should be able to use SSL. I have yet to try it, but DBD::mysql does support it if you compile with the -ssl option. Thanks Rudy - I did read 'perldoc DBD::mysql' where it talks about mysql_ssl* parameters, but didn't notice that those were in the connect section :( After reading that mysql_ssl A true value turns on the CLIENT_SSL flag when connecting to the MySQL database: mysql_ssl=1 This means that your communication with the server will be encrypted. my thought was that specifying 'mysql_ssl' in the connect would connect 1st, and *then* start encrypting communications between the client and server - it doesn't really clearly state that the connect itself (username, pw) will also be encrypted. Is this clear to everyone else? If not, I'd like to propose a clarification to the DBD::mysql perldocs - to explicitely say that mysql_ssl will cause the connect, and all subsequent dbi statements, to be encrypted between client and server. Everybody seems to be accepting the idea that the password is passed in cleartext. On what is that based? It's false. (It is true that in MySQL 4.1.x some improvements are made to the encryption, but in earlier versions the password is not sent in cleartext.) The username is sent in cleartext, yes. Also, adding mysql_ssl=1 won't in itself give you an SSL connection. You need the other mysql_ssl_xxx options to specify the key and certificate files. Thanks. -- Hardy Merrill Red Hat, Inc.
Re: DBD::mysql connect - pw in cleartext?
At 12:45 -0400 8/14/03, Hardy Merrill wrote:
Paul DuBois [EMAIL PROTECTED] wrote:
At 11:06 -0400 8/14/03, Hardy Merrill wrote:
>Rudy Lippan [EMAIL PROTECTED] wrote:
>> On Thu, 14 Aug 2003, Hardy Merrill wrote:
>> > If I'm running DBI/DBD::mysql on a mysql client machine,
>> > and I want to connect to a different mysql server
>> > machine, I must supply the username and password
>> > in the DBI connect. So, that username and password
>> > will be passed from the client machine to the server
>> > machine in cleartext, right? My question is, how can I do
>> > that such that the username and password is NOT
>> > passed over the wire in cleartext - in the connect
>> > statement?
>>
>> In theory you should be able to use SSL. I have yet to try it, but
>> DBD::mysql does support it if you compile with the -ssl option.
>
>Thanks Rudy - I did read 'perldoc DBD::mysql' where it
>talks about mysql_ssl* parameters, but didn't notice that
>those were in the connect section :( After reading that
>
> mysql_ssl
> A true value turns on the CLIENT_SSL flag when connecting to
> the MySQL database:
>
> mysql_ssl=1
>
> This means that your communication with the server will be
> encrypted.
>
>my thought was that specifying 'mysql_ssl' in the connect
>would connect 1st, and *then* start encrypting communications
>between the client and server - it doesn't really clearly
>state that the connect itself (username, pw) will also be
>encrypted.
>
>Is this clear to everyone else? If not, I'd like to
>propose a clarification to the DBD::mysql perldocs - to
>explicitely say that mysql_ssl will cause the connect,
>and all subsequent dbi statements, to be encrypted between
>client and server.
Everybody seems to be accepting the idea that the password is passed
in cleartext. On what is that based? It's false. (It is true that
in MySQL 4.1.x some improvements are made to the encryption, but in
earlier versions the password is not sent in cleartext.)
Paul, maybe my initial assumption is wrong. Let me step
back.
If I, on a mysql client machine, use DBI/DBD::mysql to
connect to a mysql server machine, I might do that
connect something like this:
my $dbh = DBI->connect("DBI:mysql:host=my.mysql_server.com;database=mydb",
"db_user", "db_password",
{PrintError => 0, RaiseError => 1});
notice "db_user" and "db_password" - whether I get
those from a module, or I type in the literal values
right into the code, either way my assumption was/is
that those values get transferred over the network
from mysql client machine to mysql server machine in
cleartext. Is this assumption correct or incorrect?
It's cleartext in your program, of course. But it's not sent
in cleartext over the network to the server during connection
establishment.
If this assumption is incorrect, and doing this connect
does NOT display the password in cleartext as it
travels over the network, then this is great, and
I probably don't need mysql_ssl* parameters at all.
Then Rudy reminded me about using the mysql_ssl*
parameters that are described in DBD::mysql perldocs.
So if I add to that connect(above) the proper
mysql_ssl* parameters, I'm assuming that I then
get an ssl connection from mysql client to mysql
server, which encrypts all communications, including
the connect parameters, for that connect and subsequent
dbi statements using that $dbh. Is this correct or
incorrect?
SSL encryption is set up before the username and password
are sent to the server.
I don't mean to make a big deal of this - just trying
to understand what happens with user passwords
in db connections in DBI/DBD::mysql, and what it
takes to *NOT* display the database password in
cleartext on the network.
You don't have to do anything, it's not displayed in cleartext
when the connection is established.
NOTE, however, that it is displayed in cleartext when you first
establish the password or change it with GRANT or SET PASSWORD,
because in those cases, you're including the password literally
as part of a query string. Use of an SSL connection can prevent
the password from being visible literally in this case.
Thanks.
Hardy
The username is sent in cleartext, yes.
Also, adding mysql_ssl=1 won't in itself give you an SSL connection.
You need the other mysql_ssl_xxx options to specify the key and
certificate files.
Re: MySQL LIMIT statement
At 16:20 +0100 7/29/03, Dan Rowles wrote: Hi there, I'm using MySQL 4.0.13-standard, DBD-mysql 2.9002, DBI 1.37, perl 5.6.1 AND perl 5.8.0. The attached program causes a crash, with the following error statement in both version sof perl. It used to work fine using MySQL 3.23.x, and older versions of the DBI. bash-2.05a$ ./database.pl 1 3 Offset num: 1 +OK Limit num: 1 +OK 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 ''1', '3'' at line 1 [for statement ``SELECT email FROM users ORDER BY email LIMIT ?, ?'']) at ./database.pl line 25. Can't list users: 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 ''1', '3'' at line 1 at ./database.pl line 28. LIMIT values must be integer constants. The placeholder binding is probably adding quotes, so you'll need to supply a DBI SQL_INTEGER type when you bind. See "Data Types for Placeholders" in the DBI docs, or p124 of the Cheetah book. The error message suggests to me that the numeric values are being passed in as strings to the database. A quick test of MySQL confirms that passing in the limit parameters as strings does indeed cause the same error message (e-mail addresses replaced):- mysql> SELECT email FROM users ORDER BY email LIMIT 0,10; +--+ | email| +--+ | [EMAIL PROTECTED] | | [EMAIL PROTECTED] | | [EMAIL PROTECTED] | +--+ 3 rows in set (0.16 sec) mysql> SELECT email FROM users ORDER BY email LIMIT "0","10"; ERROR 1064: 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 '"0","10"' at line 1 I've been trawling through the mailling lists, and I've found this post, which relates to a problem with the Postgres DBI driver. It sounds to me like this has caused the problem:- http://archive.develooper.com/[EMAIL PROTECTED]/msg02313.html Can anyone comment on this? Has anyone else had a similar problem? Thanks, Dan Attachment converted: ice3:database.pl (TEXT/R*ch) (0001DF53)
Re: reading utf8 from mysql - should be: inserting utf8 into mysql - solved
At 8:42 -0400 6/18/03, Hardy Merrill wrote:
I've been following this thread with interest - really
curiosity. Your workaround seems like a lot of work -
have you tried posting your question on a mysql mailing
list?
Paul Dubois has written some very good books on MySQL
with sections on DBI use - I think Paul is on this list -
I'm hoping Paul will respond with what he knows about
utf8 use in MySQL.
My suggestion is that if you're using 4.1.0, wait for 4.1.1 and
try it again. There are lots of character-set-handling related
changes, and the problem you're seeing may well be fixed. I know
that some of the new work involves transmission of information
between client and server.
If you *are* using 4.1.1 from the development tree (and thus the
problem remains unsolved), then please report your findings to
bugs.mysql.com so that the problem can be addressed. Thanks!
--
Hardy Merrill
Red Hat, Inc.
Brigitte Jellinek [EMAIL PROTECTED] wrote:
Yesterday I wrote about problems with reading utf8,
later I realized that the problems were actually in
the inserting, not the reading. Today I found a workaround.
The problem is caused by mysql, not by perl.
Maybe I'm doing something really stupit, but it seems
that my mysql-server doesn't accept utf8 as utf8. In the
most basic version, using a utf-8 file with sql commands:
mysql --default-character-set=utf8 test < test-insert.sql
my mysql server still interprets the utf8 strings in the file
as latin1 (and mangles all the non-latin1-charachters).
I tried inserting
SET CHARACTER SET utf8;
into the file, but that didn't help.
I had to change all occurences of literal utf8 strings in my SQL
statements, like so:
INSERT INTO sometable VALUES (,CONVERT(_utf8'blabla' USING
utf8), ...);
where 'blabla' is a string that might contain utf8.
the same for SELECT:
SELECT * FROM sometabel
WHERE somecolumn LIKE CONVERT(_utf8'%blabla%' USING utf8);
I now use this workaround through DBI. the data I get back is
not flagged as utf8, but it is correct utf8, so I can decode it
without a problem:
# to search for books with a title containging $char:
my $sql = qq{
SELECT * FROM buch WHERE Originaltitel LIKE
CONVERT(_utf8 '%$char%' USING utf8)
};
my $sth = $dbh->prepare($sql);
$sth->execute();
my (@r, $i);
while( @r = $sth->fetchrow_array() ) {
$_ = decode("utf8", $_) foreach @r;
print "@r";
}
It works. I'm glad. Thanks for the help.
Brigitte
p.s.
you can find the details at
http://perlwelt.horus.at/Beispiele/Magic/PerlUnicodeMysql/
--
Brigitte'I never met a chocolate I didnt like'Jellinek
[EMAIL PROTECTED] http://www.horus.com/~bjelli/
http://perlwelt.horus.at http://www.perlmonks.org/index.pl?node=bjelli
Re: Determining return type in Perl/MySQL
At 20:07 -0600 5/27/03, Gregg Allen wrote: Hi Paul: The column is defined as a numeric value, but sometimes I get back empty strings or strings that print nothing (from the subroutine), but pass the if(defined($string)) test. I believe the default is NULL. That might be the problem. There's some other stuff going on in that subroutine so I hate to rewrite it. I just cut and pasted it from somebody else's program. It sounds like the routine may be misbehaving. For an numeric column that can contain NULL values, you should either get back a value that (is a string that looks like) a number, or else undef if the value was NULL. You shouldn't get an empty string. GRA P.S. Are you coming out with any new books? We own all of yours. (As far as I know.) Along with numerous others on MySQL and Perl. The 2nd edition of "MySQL" is outstanding! The thing I like about it best is I can read it without my glasses. :-) Probably not for a while. MySQL Cookbook and MySQL 2nd Ed. are the most recent. They wore me out. :-) On Tuesday, May 27, 2003, at 19:31 US/Mountain, Paul DuBois wrote: At 18:30 -0600 5/27/03, Gregg Allen wrote: I have a complex, prewritten subroutine that is supposed to return a numeric value from a MySQL database table. it actually works quite well, most of the time, but sometimes it returns an undefined value, a non-numeric value, or basically anything except a valid numeric value. Do you mean that: - the column type in the table is numeric, but the routine returns other kinds of values if the query fails or - the column holds strings of various types and you need to determine which ones look like numbers The approach you need differs depending on which of these fits your situation. This is OK in the sense that it just means they are records to be ignored, but I am having trouble figuring out how to determine if it is just a simple numeric value or a string or null, or whatever, so I can decide how to process it. Any Suggestions? Thanks in Advance, Gregg Allen
Re: Determining return type in Perl/MySQL
At 18:30 -0600 5/27/03, Gregg Allen wrote: I have a complex, prewritten subroutine that is supposed to return a numeric value from a MySQL database table. it actually works quite well, most of the time, but sometimes it returns an undefined value, a non-numeric value, or basically anything except a valid numeric value. Do you mean that: - the column type in the table is numeric, but the routine returns other kinds of values if the query fails or - the column holds strings of various types and you need to determine which ones look like numbers The approach you need differs depending on which of these fits your situation. This is OK in the sense that it just means they are records to be ignored, but I am having trouble figuring out how to determine if it is just a simple numeric value or a string or null, or whatever, so I can decide how to process it. Any Suggestions? Thanks in Advance, Gregg Allen
Re: Perl DBI & MySQL
At 13:51 -0700 4/2/03, Gregg Allen wrote: Can someone tell me why this statement works interactively but not from a perl script? MySQL> SELECT * FROM MESSAGES WHERE pkey = $pkey\G ; ; \g, \G are conventions of the mysql program. They have no meaning for the Perl API. You should leave them off the end of your query and format the output yourself. This prints the output in a very nice, readable format. but: my $query = " SELECT * FROM MESSAGES WHERE pkey = $pkey\\G "; $sth->prepare($query); $sth->execute(); This chokes on the prepare and execute statements. (It says the \G is invalid syntax.) All I want is a simple little script that will put an arbitrary database record into a file, after I feed it the primary key, but will output the record's file in a format that's readable and printable. Thanks in advance, Gregg Allen
Re: Making script an "interactive" client for MySQL
At 10:14 -0800 4/1/03, b t wrote: --- Paul DuBois <[EMAIL PROTECTED]> wrote: At 9:08 -0800 4/1/03, b t wrote: >Does anyone know how to do this for the MySQL DBI >module? You could change the timeout at the SQL level: SET wait_timeout = some_number Thanks for the suggestion, but I cannot do that for two reasons: 1) We're still on MySQL 3.23.x, so it is not possible to change server variables without a restart - which is not a viable option on a production system. 2) We need to keep the wait_timeout low due to several misbehaving crons hitting the machine and not cleaning up after themselves. I have not had the chance to track down the appropriate parties and get those fixed, but will do so in the future. However, right now, being able to specify that a DBI client is interactive would take care of the particular problem in the short term. I think you may be out of luck. A quick look through the source for DBD::MySQL doesn't reveal any way to do this. Thanks, Ben
Re: Making script an "interactive" client for MySQL
At 9:08 -0800 4/1/03, b t wrote: I have a script that needs to connect to MySQL as an interactive client, so that it falls under the interactive_timeout setting instead of the wait_timeout setting. I looked through the docs, and did not see how to specify this when connecting. However looking at the code in some of mysql's C clients indicates that an interactive flag can be passed along at connect time. Does anyone know how to do this for the MySQL DBI module? You could change the timeout at the SQL level: SET wait_timeout = some_number Thanks, Ben
RE: $dbh->unquote()
At 12:59 -0500 3/27/03, Brian Spindler wrote:
Ok, I don't know how its happening either but here is the entire breakdown:
MySQL field: longtext
Step(1) - Text File is created on unix machine, transferred via FTP to a
Windows machine.
Step(2) - Perl script reads in the file and parses it grabbing certain lines
and inserts them into the database like so:
@inv_data = $dbh->quote("@inv_data");
$dbh->do("INSERT INTO ads_ff VALUES (?,?,?,?,?,?,?,?,?,?,?)", undef,
$inv_no,$ord_no,$date.'!',$tp,'1',$cust_po_num,$cust_num,@inv_data,
"$mysql_date",$inv_tot,$file);
Well no wonder!
You use *either* quote() *or* placeholders, not both. You're quoting
your values twice!
By the way, you can't quote an array.
And you can't bind an array to a single placeholder.
Lotsa problems here.
All the values are derived from substr() commands and passed to the method
which executes the latter.
Brian
-Original Message-
From: Paul DuBois [mailto:[EMAIL PROTECTED]
Sent: Thursday, March 27, 2003 12:40 PM
To: Brian Spindler; 'Sterin, Ilya (I.)'; [EMAIL PROTECTED]
Subject: RE: $dbh->unquote()
At 12:24 -0500 3/27/03, Brian Spindler wrote:
Interesting, in my MySQL database the field is a longtext should I change
it
to a one of the binary types?
That won't make any difference.
I don't know how your problem occurred in fact, but here's one way that
it *can* occur. Maybe this will shed some light on your problem.
$x = $dbh->quote ("\n");
$dbh->do ("INSERT INTO t SET col = $x");
That will insert a newline into the database.
$x = $dbh->quote ('\n');
$dbh->do ("INSERT INTO t SET col = $x");
That will insert a literal backslash-n into the database.
See the difference?
Brian
-Original Message-
From: Sterin, Ilya (I.) [mailto:[EMAIL PROTECTED]
Sent: Thursday, March 27, 2003 11:38 AM
To: 'Brian Spindler'; 'Paul DuBois'; [EMAIL PROTECTED]
Subject: RE: $dbh->unquote()
This depends on what field in the db you are writting to. If it's a raw
type or binary field, then you wouldn't have that problem. There might be
some conversion going on between the db field, as \n is not a standard
carriage return, it's the way Perl interpreter interprets carriage returns
on what ever platform it was build on. It might very possibly be converted
between an ascii field and DBI.
Ilya
-Original Message-
From: Brian Spindler [mailto:[EMAIL PROTECTED]
Sent: Thursday, March 27, 2003 11:33 AM
To: 'Paul DuBois'; [EMAIL PROTECTED]
Subject: RE: $dbh->unquote()
If printed in a windows environment "\n" is a carriage return.
Brian
-Original Message-
From: Paul DuBois [mailto:[EMAIL PROTECTED]
Sent: Thursday, March 27, 2003 11:32 AM
To: Brian Spindler; [EMAIL PROTECTED]
Subject: Re: $dbh->unquote()
At 11:19 -0500 3/27/03, Brian Spindler wrote:
Hi guys, gals!
I have a pretty big string that I am inserting into MySQL via the
$dbh->quote($str) function, this works great however now when I pull the
data back out of the database and go to write it to a file the \n
characters
that were inserted by quote are not being written back out as carriage
returns as desired. How can do a reverse of quote() or just get those
"\n"'s to print out as they should! =) ahh.. I'm frustrated, I tried
everything even split('\n',$str) and then reinserting the "\n". Doesn't
work, please help!
Thanks in advance!
Brian
What do you mean by "as they should"? After all, "\n" isn't a carriage
return.
RE: $dbh->unquote()
At 12:24 -0500 3/27/03, Brian Spindler wrote:
Interesting, in my MySQL database the field is a longtext should I change it
to a one of the binary types?
That won't make any difference.
I don't know how your problem occurred in fact, but here's one way that
it *can* occur. Maybe this will shed some light on your problem.
$x = $dbh->quote ("\n");
$dbh->do ("INSERT INTO t SET col = $x");
That will insert a newline into the database.
$x = $dbh->quote ('\n');
$dbh->do ("INSERT INTO t SET col = $x");
That will insert a literal backslash-n into the database.
See the difference?
Brian
-Original Message-
From: Sterin, Ilya (I.) [mailto:[EMAIL PROTECTED]
Sent: Thursday, March 27, 2003 11:38 AM
To: 'Brian Spindler'; 'Paul DuBois'; [EMAIL PROTECTED]
Subject: RE: $dbh->unquote()
This depends on what field in the db you are writting to. If it's a raw
type or binary field, then you wouldn't have that problem. There might be
some conversion going on between the db field, as \n is not a standard
carriage return, it's the way Perl interpreter interprets carriage returns
on what ever platform it was build on. It might very possibly be converted
between an ascii field and DBI.
Ilya
-Original Message-
From: Brian Spindler [mailto:[EMAIL PROTECTED]
Sent: Thursday, March 27, 2003 11:33 AM
To: 'Paul DuBois'; [EMAIL PROTECTED]
Subject: RE: $dbh->unquote()
If printed in a windows environment "\n" is a carriage return.
Brian
-Original Message-
From: Paul DuBois [mailto:[EMAIL PROTECTED]
Sent: Thursday, March 27, 2003 11:32 AM
To: Brian Spindler; [EMAIL PROTECTED]
Subject: Re: $dbh->unquote()
At 11:19 -0500 3/27/03, Brian Spindler wrote:
Hi guys, gals!
I have a pretty big string that I am inserting into MySQL via the
$dbh->quote($str) function, this works great however now when I pull the
data back out of the database and go to write it to a file the \n
characters
that were inserted by quote are not being written back out as carriage
returns as desired. How can do a reverse of quote() or just get those
"\n"'s to print out as they should! =) ahh.. I'm frustrated, I tried
everything even split('\n',$str) and then reinserting the "\n". Doesn't
work, please help!
Thanks in advance!
Brian
What do you mean by "as they should"? After all, "\n" isn't a carriage
return.
RE: $dbh->unquote()
At 11:33 -0500 3/27/03, Brian Spindler wrote:
If printed in a windows environment "\n" is a carriage return.
Brian
You still haven't said what "as they should" means. You say
that \n's don't print as they should, but not what actually happens.
Do they just disappear entirely? Do they get turned into something
else? What?
-----Original Message-
From: Paul DuBois [mailto:[EMAIL PROTECTED]
Sent: Thursday, March 27, 2003 11:32 AM
To: Brian Spindler; [EMAIL PROTECTED]
Subject: Re: $dbh->unquote()
At 11:19 -0500 3/27/03, Brian Spindler wrote:
Hi guys, gals!
I have a pretty big string that I am inserting into MySQL via the
$dbh->quote($str) function, this works great however now when I pull the
data back out of the database and go to write it to a file the \n
characters
that were inserted by quote are not being written back out as carriage
returns as desired. How can do a reverse of quote() or just get those
"\n"'s to print out as they should! =) ahh.. I'm frustrated, I tried
everything even split('\n',$str) and then reinserting the "\n". Doesn't
work, please help!
Thanks in advance!
Brian
What do you mean by "as they should"? After all, "\n" isn't a carriage
return.
Re: $dbh->unquote()
At 11:19 -0500 3/27/03, Brian Spindler wrote:
Hi guys, gals!
I have a pretty big string that I am inserting into MySQL via the
$dbh->quote($str) function, this works great however now when I pull the
data back out of the database and go to write it to a file the \n characters
that were inserted by quote are not being written back out as carriage
returns as desired. How can do a reverse of quote() or just get those
"\n"'s to print out as they should! =) ahh.. I'm frustrated, I tried
everything even split('\n',$str) and then reinserting the "\n". Doesn't
work, please help!
Thanks in advance!
Brian
What do you mean by "as they should"? After all, "\n" isn't a carriage
return.
Re: making DBD::mysql on fetch on fetch()
At 21:17 -0600 3/16/03, Moritz von Schweinitz wrote:
hi, all.
i always thought that the (one of the) whole purpose of the
prepare/execute/fetch trinity was to let the db-server do all the
things a db-server's supposed to do, and then return a row at a time
(some cache-optimizations inbetween, maybe) to the script when i do
a fetch().
Not sure why you thought that, but it's not true for *any* MySQL API
unless you specify explicitly that you want the server to return only
a row at a time. The default behavior is to return the entire result
set to the client, and the client API hands back the records as you ask
for them.
For MySQL, you can change this:
$sth = $dbh->prepare (...);
$sth->{mysql_use_result} = 1;
$sth->execute ();
my problem right now is that i'm translating a kind of largish (250k
rows) and quite ugly database to my format. my approach was to do
the whole conversion inside the canonical
while ($row = $sth->fetchrow_arrayref)
loop (which works fine). but when i execute that $sth (before that
loop, obviously) my script stalls for a minute or two, gobbles
up >80MB of my precious RAM (on the client), and then proceeds -
which is not exactly what i expected.
is this a general (DBD::?)mysql issue, or is this some configuration issue?
thanks for any help,
M.
Re: Anyone have DBI code that depends on $sth->{NAME} containing "tablename.fieldname"?
At 0:18 + 3/4/03, Tim Bunce wrote:
I've discovered that DBD::mysql will return "tablefoo.fieldbar" as
the NAME of the field in a select like:
SELECT tablefoo.fieldbar FROM tablefoo
That's not what I observe. Is this a recent change in the MySQL C client
library? The library has not returned a table name before, because
the table name is in a separate member of the MYSQL_FIELD structure.
It does that simply because that's what the underlying mysql client
API tell it is the name of the field.
I believe this is very rare (I know of no others drivers that do that)
and I'm considering changing the DBI specification to clarify that NAME
should only contain the fieldname.
I appreciate that there's a loss of information here and that
statements like this:
SELECT table1.field, table2.field FROM table1, table2 WHERE ...
will return the same NAME value for both fields.
However, I'm currently of the opinion that removing the table name
to make NAME be consistent with the behaviour of other drivers is
of greater benefit than retaining the table name.
I'd welcome any examples of code that *relies* in the table name
being present in the NAME attribute and can't easily be changed.
Note that in the specific case of DBD::mysql, the table name of
each field is also availble in the $sth->{mysql_table} attribute,
so any application that needs the table name for each field can
still get it.
Tim.
Re: MySQL DBI problem
I am trying to read email off a mail server using perl, parse it, and then store the fields in a MySQL database. I managed to write the first two parts in a couple of days, but now I've spent two weeks trying to get the fields into a the "Messages" database. I have got several fields to go in and then I ran the same script the next day with no changes and it wasn't working any longer. I have run into about every problem imaginable and I can't get a stable script. Once when I thought I had it working, the script started carping because there were embedded quotes in the email body which it interpreted as delimiters. I tried the "qw" function and it was bitching about another syntax problem. I have a few books on Perl that have DBI examples but they are not made for MySQL and the examples are very Mickey Mouse compared to what I'm trying to do. Any suggestions or directions to potential resources will be greatly appreciated. I would guess that you're not inserting the data values into your SQL statements quoted properly. You should use placeholders, which will solve this problem. MySQL-specific DBI examples can be found here: http://www.kitebird.com/mysql-perl/ The webdb distribution here contains lots of sample code. There's also a sample chapter there. I don't know whether or not you'll consider it "Mickey Mouse", but I suspect not. Thanks in advance, Gregg
Re: CGI/DBI/mySQL Placeholder problem..
At 2:31 -0800 2/19/03, Tushar Balapure wrote: > Hi All, I am facing problem while using placeholder.. I am using CGI/DBI/mySQL. When html form is submitted, if the form text box contains ? then, the INSERT query fails.. If I dont use the plcaholder, it stores form data ? as NULL.. i.e every occurance of ? in text is stored as NULL.. Ex: What is your name ? is stored as What is yor name NULL Please help.. > Thanks Tushar > Sounds like you're inserting your data value directly into the query string without quoting, and then executing that. Write the query string using ? where the data value should go, and then bind the data value to the query when you execute it.
Re: CPAN install of DBD::mysql fails during tests
At 9:08 +1000 2/20/03, Peter Kiem wrote: Hi, I am trying to use CPAN to install DBD::mysql which seems to work fine but then fails all the tests as it is trying to connect to the test database using the root user with no password. Is there some way I can tell it what password to use? Surely I don't have to change my MySQL root password to nothing just to install this? use "force install DBD::mysql" If you want to specify passwords, you have to unpack and build it manually. -- Regards, +-+-+ | Peter Kiem.^. | E-Mail: <[EMAIL PROTECTED]> | | Zordah IT /V\ | Mobile: +61 0414 724 766| | IT Consultancy & /( )\ | WWW : www.zordah.net | | Internet Hosting ^^-^^ | ICQ : "Zordah" 81 | +-+-+ My current spamtrap address is [EMAIL PROTECTED]
Re: Q: MySQL table type & DBI
At 19:58 +1100 2/19/03, Ron Savage wrote: Folks Is there a DBI/DBD::mysql call which will retrieve the table type (ie one of MyISAM/ISAM/Heap/...)? SHOW TABLE STATUS will give you this, along with a bunch of other information. -- Cheers Ron Savage, [EMAIL PROTECTED] on 19/02/2003 http://savage.net.au/index.html
Re: multiple MYSQL
At 8:23 +0800 2/10/03, Jun Beldad wrote: Hi, I'm running two versions of mysql (with different ports) on a server. How do I make DBI connect to the instance of mysql with the non-default-port. TIA. Specify the port number in your DSN. Also, if the server is on the local host, specify the host name as "127.0.0.1" or the host's actual name rather than as "localhost". At least, do that if you're on Unix, for which connections to "localhost" will use a socket file rather than TCP/IP and a port number.
Re: [Fwd: Re: Error installing DBD::Mysql]
At 18:15 + 1/28/03, aa wrote: Hi, So how can i change the path setting Depends on your shell. You might find this helpful: http://www.kitebird.com/mysql-cookbook/path.pdf It explains how to set your PATH for the mysql program, but mysql_config is probably in the same directory. Paul Dubois wrote: At 9:09 + 1/28/03, aa wrote: Hello, Can anyone help me on this please The directory in which mysql_config is located must be in your PATH setting. From: [EMAIL PROTECTED] (Aa) Thanks for pointing me to the right list, to be honest I didnt know which one to post to, And mysql_config is in this location, /usr/local/mysql/bin/mysql_config i.e mysql is in /usr/local/mysql/ is this the 'normal' location? Wiggins D'Anconia wrote: What does this have to do with CGI?? [EMAIL PROTECTED] would be a better list for this question. However, do you have MySQL installed in an abnormal location, or where is the 'mysql_config' executable on your system? If it is in a "normal" location and you just installed it you may need to add it to your path. If you installed from binaries, did you install the mysql-devel package? It appears that 'mysql_config' can't be found which is used to set a number of parameters for the build process. http://danconia.org aa wrote: Hi, I am trying to install DBD::Mysql and I am getting error, I am new to perl and modules, here is the screen print, I'll appreciate any help please. CPAN.pm: Going to build J/JW/JWIED/DBD-mysql-2.1024.tar.gz Can't exec "mysql_config": No such file or directory at Makefile.PL line 169. [clipped]
Re: [Fwd: Re: Error installing DBD::Mysql]
At 9:09 + 1/28/03, aa wrote: Hello, Can anyone help me on this please The directory in which mysql_config is located must be in your PATH setting. From: [EMAIL PROTECTED] (Aa) Thanks for pointing me to the right list, to be honest I didnt know which one to post to, And mysql_config is in this location, /usr/local/mysql/bin/mysql_config i.e mysql is in /usr/local/mysql/ is this the 'normal' location? Wiggins D'Anconia wrote: What does this have to do with CGI?? [EMAIL PROTECTED] would be a better list for this question. However, do you have MySQL installed in an abnormal location, or where is the 'mysql_config' executable on your system? If it is in a "normal" location and you just installed it you may need to add it to your path. If you installed from binaries, did you install the mysql-devel package? It appears that 'mysql_config' can't be found which is used to set a number of parameters for the build process. http://danconia.org aa wrote: Hi, I am trying to install DBD::Mysql and I am getting error, I am new to perl and modules, here is the screen print, I'll appreciate any help please. CPAN.pm: Going to build J/JW/JWIED/DBD-mysql-2.1024.tar.gz Can't exec "mysql_config": No such file or directory at Makefile.PL line 169. readline() on closed filehandle PIPE at Makefile.PL line 171. Can't exec "mysql_config": No such file or directory at Makefile.PL line 169. readline() on closed filehandle PIPE at Makefile.PL line 171. Can't exec "mysql_config": No such file or directory at Makefile.PL line 169. readline() on closed filehandle PIPE at Makefile.PL line 171. Can't exec "mysql_config": No such file or directory at Makefile.PL line 169. readline() on closed filehandle PIPE at Makefile.PL line 171. Can't exec "mysql_config": No such file or directory at Makefile.PL line 169. readline() on closed filehandle PIPE at Makefile.PL line 171. Failed to determine directory of mysql.h. Use perl Makefile.PL --cflags=-I to set this directory. For details see the INSTALL.html file, section "C Compiler flags" or type perl Makefile.PL --help Running make test Make had some problems, maybe interrupted? Won't test Running make install Make had some problems, maybe interrupted? Won't install cpan> install DBD::mysql Running install for module DBD::mysql Running make for J/JW/JWIED/DBD-mysql-2.1024.tar.gz Is already unwrapped into directory /root/.cpan/build/DBD-mysql-2.1024 Makefile.PL returned status 512 Running make test Make had some problems, maybe interrupted? Won't test Running make install Make had some problems, maybe interrupted? Won't install Thanks
Re: Help on accessing a mysql DB from a Perl script
At 11:42 -0500 1/22/03, Tay, William wrote: Hi, I am developing a Web application that invokes a Perl script, which in turn access a mysql database. I have copied a sample database named books to the directory /usr/local/mysql/data of a Linux machine, Bzzzt!! That will *not* work unless the tables are of a type that is binary portable between machines. You're using ISAM tables (as indicated by your references to .isd, .ism, and .ISD below), which are machine dependent. And because you use .isd and .ism (lowercase), I'm guessing you copied them from a Windows box, where filenames are not case sensitive. Your MySQL server is looking for .ISD and .ISM files (case sensitive). You could get it to find the files by renaming them to have uppercase extensions, but if you *did* copy them from a Windows box, it still won't work due to the machine-dependent nature of ISAM table storage. Use this command on the Windows box to dump the database: mysqldump --opt books > books.sql Then copy books.sql to the Linux box and run this command: mysql books < books.sql That will create the database correctly. None of this has anything to do with Perl, of course. :-) But you're not having a Perl problem. where I have also installed an Apache Web server, mysql, DBI module and a DBD::mysql driver. A table named Authors is in the books database. Within /usr/local/mysql/data/books, 3 files (Authors.frm, authors.isd, authors.ism) belonging to the Authors table are located. For testing purpose, I tried to execute the perl script on the command line (perl -w test1.pl). The connection to the books database seemed to work fine. However, executing the SELECT statement that follows gives the following errors: DBD::mysql::st execute failed: Can't find file: 'Authors.ISD' (errno: 2) at test1.pl line 30. DBD::mysql::st execute failed: Can't find file: 'Authors.ISD' (errno: 2) at test1.pl line 30. Would appreciate any comment on what could have happened and suggestions for the solution. Thanks. Will
Re: Creating a new db using DBI
At 19:05 -0500 1/19/03, Mark Riehl wrote: All - Using MySQL under Linux. I'd like to create a new database using the DBI. I thought I could connect to the test database, create the new database, assign grants, then reconnect to the new database. I'm trying to connect to the test database without a username or password and I'm getting an error: Access denied for user user@hostname. I'm trying to use the following connect call: $database = "test"; $databaseString = "DBI:mysql:$database:$ip:$port"; $dbh = DBI->connect($databaseString, "", ""); or die "..."; Any suggestions? Seems pretty clear. Your server (sensibly) doesn't allow anonymous connections. Use an explicit username and password. Thanks, Mark
Re: simple(?) quoting problem with DBD::mysql
At 20:19 + 11/12/02, Tim Bunce wrote:
And the DBI has now defined a $dbh->quote_identifier method.
But I'm not sure if DBD::mysql has implemented it yet.
Tim.
I don't believe so (which is why I didn't mention it).
On Tue, Nov 12, 2002 at 12:48:29PM -0600, Paul DuBois wrote:
At 10:27 -0700 11/12/02, Rob Lee wrote:
>[Please respond directly to me as this address is not subscribed to this
>list - thnx]
The problem is that parameter binding applies to data values, not to
identifiers such as table or column names.
>
>When I parameter bind and execute() I get seemingly-subtle errors related
>to quoting.
>
>my $sth = $dbh->prepare("SELECT * FROM ?");
>my $table = "tblSites";
>$sth->execute($table);
>
>DBD::mysql::st execute failed: You have an error in your SQL syntax near
>''tblSites'' at line 1 at ./quoteTest line 31, line 15.
>
>This fails for the same reason why the following fails:
>
>mysql> SELECT * FROM 'tblSites';
>ERROR 1064: You have an error in your SQL syntax near ''tblSamples'' at
>line1
>
>Back-ticks(`tblSites`) are fine, single quotes cause problems.
>
>I can't seem to find any documentation on this issue.
>
>-R
Re: simple(?) quoting problem with DBD::mysql
At 10:27 -0700 11/12/02, Rob Lee wrote:
[Please respond directly to me as this address is not subscribed to this
list - thnx]
The problem is that parameter binding applies to data values, not to
identifiers such as table or column names.
When I parameter bind and execute() I get seemingly-subtle errors related
to quoting.
my $sth = $dbh->prepare("SELECT * FROM ?");
my $table = "tblSites";
$sth->execute($table);
DBD::mysql::st execute failed: You have an error in your SQL syntax near
''tblSites'' at line 1 at ./quoteTest line 31, line 15.
This fails for the same reason why the following fails:
mysql> SELECT * FROM 'tblSites';
ERROR 1064: You have an error in your SQL syntax near ''tblSamples'' at
line1
Back-ticks(`tblSites`) are fine, single quotes cause problems.
I can't seem to find any documentation on this issue.
-R
Re: Session management in cgi/perl
At 10:19 -0400 10/8/02, John Day wrote: >Try the excellent reference "MySQL and Perl for the WEB" by Paul Du >Bois. (Paul is a member of this list). He talks at length about >session management and tracking - well worth having on the shelf. Thanks for the plug! The scripts discussed in the book can be obtained here: http://www.kitebird.com/mysql-perl/ No, you don't have to buy the book to download them. :-) > > >John > >At 10:02 AM 10/8/2002 -0400, Hardy Merrill wrote: >>I haven't done this yet myself, but I've been told you can >>do it with the Apache::Session module - I think(?) that's >>under mod_perl. >> >>Someone who knows - please confirm(or deny) this. >> >>-- >>Hardy Merrill >>Senior Software Engineer >>Red Hat, Inc. >> >>vikas mehta [[EMAIL PROTECTED]] wrote: >>> Can anyone help me how to maintain user session using perl >>> scripts >>> Are there any online references available >>> >>> Regards >>> Vikas
Re: SQL Syntax in DBD::mysql
At 10:41 +0100 9/24/02, Jon Wyatt wrote:
>I am trying to use the following code to insert data into a mysql database:-
>
>
># write the information to the database
>$sth=$dbh->prepare("INSERT INTO
>images(name,desc,category,subcat1,subcat2,image)
>VALUES(?,?,?,?,?,?)");
>
>$sth->bind_param(1,$name);
>$sth->bind_param(2,$desc);
>$sth->bind_param(3,$category);
>$sth->bind_param(4,$subcat1);
>$sth->bind_param(5,$subcat2);
>$sth->bind_param(6,$photo);
>
>$sth->execute;
>
>
>I get syntax error in sql statement.
"DESC" is a keyword.
> The image field is a blob so I'm not sure if a string is the
>correct format but from the manual it appears that DBI only
>understands string or number formats. The photo variable holds the
>binary data.
>
>The format above as far as I can tell conforms to the standard DBI
>format but the DBD::mysql man page is not particularly explicit in
>this area.
>
>Thanks.
>
>Jon.
Re: Msql-Mysql-modules
At 22:25 -0400 9/6/02, Rich DeSimone wrote: >Hi, I am trying to install the Msql-Mysql modules and seem to be >getting an error. I installed the data-dumper and DBI modules and >had no problem. This is what I am getting.. > >[root] (~/Msql-Mysql-modules-1.2219)-> perl Makefile.PL That's old. Perhaps try installing the current version of the DBD::mysql module instead.
Re: How do I detect non-select statements after calling$sth->execute()
At 9:50 -0500 9/4/02, David Dooling wrote:
>For security reasons, wouldn't you want to know what statements are
>non-select _before_ you execute?
>
>If you only care about after, how about something like this:
>
> $sth->execute;
> my @row = $sth->fetchrow_array;
> if (@row) { # results }
> elsif (!$sth->errstr) { # now rows }
> else { warn $sth->errstr }
>
>You can't distinguish between selects that return no data and
>non-selects. But for your example below, it really wouldn't matter.
>It seems you need to parse this stuff before.
In MySQL, you can distinguish select from non-select statements after
$sth->execute by checking $sth->{NUM_OF_FIELDS}. If it's zero, it's
a non-select, if it's non-zero, it's a select. This works even if the
select returns zero rows, because the "width" of the result set is
greater than zero. No parsing of the statement or any other messing
around with it is necessary.
Does this work for other database engines as well?
>
>On Wed, Sep 04, 2002 at 04:16:14PM +0200, Roger Perttu wrote:
>> I store the result for later use in another query.
>
>How do you parse the SQL to know what to do with it?
>
>> If this is the (pseudo) input to my program:
>>
>> select ID from table1
>> select Name from table2 where PersonID = ID
>> insert into table3 values(ID, Name)
>
>Do you scan the SQL for the values() tag and then look back at the
>previous statements? Is the indentation important?
>
>> Then ID from query one and Name from query two will be inserted into
>> table3. Queries might be spread across different databases and nest to
>> any depth (until I run out of connections).
>
>How do you determine which database to query?
>
>> Does anyone know if such a tool already exists?
>
>The XSQL extensions to XML::Generator::DBI begin to address these
>issues. XSQL provides a means to intelligently store SQL queries and
>their results as structured, XML documents. Some people think XML is
>a little heavy-handed, and it can be at times. But you seems to need
>a lot of power and flexibility, and writing your own parser would be a
>big pain. Of course, to use XSQL, you would have to eventually
>rewrite your queries to get all the power it provides. On the other
>hand, it is easy to start using, and you can change queries to XSQL as
>needed.
>
> http://xsql.sourceforge.net/
>
>What would really be nice is a parser that can read SQL and convert it
>to XSQL. This would ease the transition considerably. XSQL is still
>under development (by me), so that and other features are in the
>works. Help is welcome.
>
>There are still difficulties with PL/SQL anbd PgPL/SQL code, but you
>could still label the code with an attribute that defines it as
>having one effect or the other:
>
>
> BEGIN ...
>
>
>dd
>--
>David Dooling
Re: ANNOUNCE: DBI 1.29
At 10:13 +0100 7/16/02, Tim Bunce wrote: >On Mon, Jul 15, 2002 at 08:57:09PM -0500, Paul DuBois wrote: >> At 0:45 +0100 7/16/02, Tim Bunce wrote: >> >On Mon, Jul 15, 2002 at 09:27:52AM -0500, Paul DuBois wrote: >> >> > file: $CPAN/authors/id/T/TI/TIMB/DBI-1.29.tar.gz >> >> > size: 256485 bytes >> >> >md5: 1811579779bf790e7db5879d302bf4f6 >> >> > >> >> >=head2 Changes in DBI 1.29,15th July 2002 >> >> > >> >> > NOTE: This release changes the specified behaviour for the >> >> > : fetchrow_array method when called in a scalar context: >> >> >> >> By implication, this change also affects selectrow_array()? >> >> Just checking. >> > >> >No. I wavered either way but decided not to change it in the end. >> >> Hmm... Okay, then something seems odd in the docs. The section for >> selectrow_array() says: > >: =item C >: >:@row_ary = $dbh->selectrow_array($statement); >:@row_ary = $dbh->selectrow_array($statement, \%attr); >:@row_ary = $dbh->selectrow_array($statement, \%attr, @bind_values); >: >: This utility method combines L, L and >: L into a single call. If called in a list context, it >: returns the first row of data from the statement. The C<$statement> >: parameter can be a previously prepared statement handle, in which case >: the C is skipped. > >> But if selectrow_array() calls fetchrow_array(), shouldn't it too reflect >> the new behavior? (On the other hand, perhaps the doc is incorrect, since >> in the code, it appears that selectrow_array() really is implemented in >> terms of fetchrow_arrayref() and not fetchrow_array()...) > >Since few sane people would call selectrow_array (or fetchrow_array) in I don't know if sanity is a criterion here. What matters is that the docs describe what actually can be done or what can be expected. :-) >a scalar context for a select statement with more than one result column, >I'm happy to make change selectrow_array to be consistent with the new >fetchrow_array definition. Especially as I don't have to change code or >break anything :) Okay. Thanks for the clarification. Just a little niggle below: >Both now say: > > If called in a scalar context for a statement handle that has more > than one column, it is I whether the driver will return > the value of the first column of the last. So don't do that. first column *or* the last... ? > Also, in a scalar context, an C is returned if there are no > more rows or if an error occurred. That C can't be distinguished > from an C returned because the first field value was NULL. > For these reasons you should exercise some caution if you use > [this method] in a scalar context.
Re: ANNOUNCE: DBI 1.29
At 0:45 +0100 7/16/02, Tim Bunce wrote: >On Mon, Jul 15, 2002 at 09:27:52AM -0500, Paul DuBois wrote: >> > file: $CPAN/authors/id/T/TI/TIMB/DBI-1.29.tar.gz >> > size: 256485 bytes >> >md5: 1811579779bf790e7db5879d302bf4f6 >> > >> >=head2 Changes in DBI 1.29,15th July 2002 >> > >> > NOTE: This release changes the specified behaviour for the >> > : fetchrow_array method when called in a scalar context: >> >> By implication, this change also affects selectrow_array()? >> Just checking. > >No. I wavered either way but decided not to change it in the end. > >Tim. Hmm... Okay, then something seems odd in the docs. The section for selectrow_array() says: =item C @row_ary = $dbh->selectrow_array($statement); @row_ary = $dbh->selectrow_array($statement, \%attr); @row_ary = $dbh->selectrow_array($statement, \%attr, @bind_values); This utility method combines L, L and L into a single call. If called in a list context, it returns the first row of data from the statement. The C<$statement> parameter can be a previously prepared statement handle, in which case the C is skipped. But if selectrow_array() calls fetchrow_array(), shouldn't it too reflect the new behavior? (On the other hand, perhaps the doc is incorrect, since in the code, it appears that selectrow_array() really is implemented in terms of fetchrow_arrayref() and not fetchrow_array()...) By the way, the next entry in the docs is for selectrow_arrayref(), which says: =item C $ary_ref = $dbh->selectrow_array($statement); $ary_ref = $dbh->selectrow_array($statement, \%attr); $ary_ref = $dbh->selectrow_array($statement, \%attr, @bind_values); This utility method combines L, L and L into a single call. It returns the first row of data from the statement. The C<$statement> parameter can be a previously prepared statement handle, in which case the C is skipped. Looks like the three example lines should say _arrayref and not _array? > >> > : The DBI spec used to say that it would return the FIRST field. >> > : Which field it returns (i.e., the first or the last) is now undefined. >> > : This does not affect statements that only select one column, which is >> > : usually the case when fetchrow_array is called in a scalar context. >> > : FYI, this change was triggered by discovering that the fetchrow_array >> > : implementation in Driver.xst (used by most compiled drivers) >> > : didn't match the DBI specification. Rather than change the code >> > : to match, and risk breaking existing applications, I've changed the >> > : specification (that part was always of dubious value anyway). >>
Re: ANNOUNCE: DBI 1.29
> file: $CPAN/authors/id/T/TI/TIMB/DBI-1.29.tar.gz > size: 256485 bytes >md5: 1811579779bf790e7db5879d302bf4f6 > >=head2 Changes in DBI 1.29,15th July 2002 > > NOTE: This release changes the specified behaviour for the > : fetchrow_array method when called in a scalar context: By implication, this change also affects selectrow_array()? Just checking. > : The DBI spec used to say that it would return the FIRST field. > : Which field it returns (i.e., the first or the last) is now undefined. > : This does not affect statements that only select one column, which is > : usually the case when fetchrow_array is called in a scalar context. > : FYI, this change was triggered by discovering that the fetchrow_array > : implementation in Driver.xst (used by most compiled drivers) > : didn't match the DBI specification. Rather than change the code > : to match, and risk breaking existing applications, I've changed the > : specification (that part was always of dubious value anyway).
Re: mysql_insertid problem
At 15:40 -0400 7/12/02, Keith Jackson wrote: >I've use the mysql_insertid function on many different boxes but I am >now having a problem. Could be a DBI::mysql version problem. I don't remember the version, but one of the 2. versions had some issue with AUTO_INCREMENT.
Re: sth->cancel
At 23:32 +0100 6/30/02, Tim Bunce wrote: >On Sun, Jun 30, 2002 at 10:43:19AM -0700, Michael A Chase wrote: >> On Sun, 30 Jun 2002 11:48:49 -0500 Paul DuBois <[EMAIL PROTECTED]> wrote: >> >> > At 16:23 +0100 6/30/02, Tim Bunce wrote: >> > >Support for cancel was added in DBD::Oracle 1.09 thanks to a patch >> > >from Fredrik Sjoholm. >> > >> > Will cancel eventually become the general preferred replacement for >> > finish for early termination of result set fetching operations? >> >> No. >> >> finish() is a hint to DBI that the query will not be fetched from again. >> The hint may be passed along to the databse to allow it to release >> resources being held for the statement. >> >> cancel(), when it works, tells the database to abort the current operation. > >Spot on. > >I'll probably rename finish() to something like discard_unfetched_rows(). >(Keeping an alias for old code of course.) > >Tim. I guess I got the idea from this sentence in perldoc DBI: The `finish' method should have been called `cancel_select'.
Re: CGI.pm & DBI
At 15:14 -0700 6/30/02, Will wrote: >Greets All, > >A friend told me that I shouldnt use DBI's connect(), >prepare(), exec()and disconnect() methods when I am >using CGI.pm, but he said rather to use do(). If that's *all* he told you, that's ridiculous. Perhaps there is more to this story? > >Can anyone tell me why? And, also, can anyone give me >some examples of how the do() method would allow me to >connect, run SQL queries and so on? do() doesn't allow you to connect, which is one reason why your friend's advice seems ill-advised. > >Thanks, > >Will
Re: sth->cancel
At 16:23 +0100 6/30/02, Tim Bunce wrote:
>Support for cancel was added in DBD::Oracle 1.09 thanks to a patch
>from Fredrik Sjoholm.
Will cancel eventually become the general preferred replacement for
finish for early termination of result set fetching operations?
>
>He sent this test script with it:
>
>#!/usr/bin/perl
>use DBI;
>use strict;
># test $sth->cancel() implementation
>my $db = DBI->connect ('dbi:Oracle:', "ez", "ez", { RaiseError=>1,
>AutoCommit=>0 });
>my $q = $db->prepare ("begin loop null; end loop; end;");
>$SIG{ALRM} = sub { print "Alarm\n"; $q->cancel; print "Cancelled\n"; };
>alarm 5;
>$q->execute();
>$db->disconnect;
>
>I've not tested it myself (and the test looks limited) but he was
>obviously happy with it.
>
>Tim.
Re: Book Recommendation?
At 0:03 -0700 6/25/02, Will wrote: >Greets Folks, > >I wrote in yesterday as per the author of the DBI >module, but the documentation i have found really >doesnt seem sufficient for learning the module... > >Is there a book I should buy for this? I checked ou >this book: > >http://www.amazon.com/exec/obidos/ASIN/1565926994/ref=ase_dbi/102-6988665-4359353 > >But this is a 350 page book, and I am just starting >out. > >Is there something a little more concise? I've got >Paul DuBois' book MySQL and Perl for the Web, but I >feel like maybe I shoudl focus more on the DBI module >itself before going too much further into DuBois' >book. > >What would you folks recommend? Most of MySQL and Perl for the Web is fairly database-independent, in the sense that its discussion of DBI isn't tied specifically to MySQL. On the other hand, it's an application-writing book, not a reference book. If you're looking for the latter, I suggest Descartes and Bunce, the O'Reilly book that you reference above. That's what I use when I need to look up something that's not in "perldoc DBI". Their book has received some criticism along the lines that it's little more than what you can get from the online docs, but I don't think that criticism is particularly accurate. Yes, the book does include the perldoc material (why wouldn't it?) but goes beyond it. > >Thanks, > >Will
Re: Passing cmd line args to MySQL from Perl
At 6:56 -0700 6/21/02, Tom Atwater wrote: >Hello, > >I am trying to use the LOAD DATA LOCAL INFILE MySQL >command from Perl. >(I have Perl v5.6.1 . DBI.pm is dated Feb 6 2002.) > >As of version MySQL 3.23.49 (which is what I have), >MySQL changed so that this command was only >allowed under certain conditions >(see http://www.mysql.com/doc/L/O/LOAD_DATA_LOCAL.html ). > >If these conditions are not met, when one tries to use >LOAD DATA LOCAL INFILE, one gets the following error: > >The used command is not allowed with this MySQL version > >This is what I am getting. > >>From an old thread on the MySQL mailing list, a solution is >supposed to be to add the option > --local-infile >to the command line when you invoke both the client mysql >and the server mysqld. > >I can and do invoke mysqld this way, but what does it mean >to invoke mysql this way, when the client is Perl DBI? >That is my basic question. > >I tried using these values of db_str as the 1st arg to DBI->connect() : >dbi:mysql:database=SPOTPLAY;local-infile=1 >dbi:mysql:database=SPOTPLAY;local_infile=1 Neither of those will work because you don't specify options like that directly in the connect string. >dbi:mysql:database=SPOTPLAY;mysql_read_default_file=/etc/my.cnf That will work, except that there is an option file processing but in the 3.23.49 C client library (which DBD::mysql relies on). You should update to the current version (3.23.51), and possibly reinstall DBD::mysql to make sure it uses the new client library. > >where my.cnf had entries >[perl] >local-infile=1 >[client] >local-infile=1 >(Dumped core when I added [client]) That's because of the bug. You could also specify your connect string like this: dbi:mysql:database=SPOTPLAY;mysql_read_default_group=perl And then when you connect, all the standard option files will be searched, using options in the [perl] and [client] groups.
Re: _ListTables or ->tables?
At 9:40 -0500 6/18/02, Cary Mathews wrote:
>I'm trying to list the tables within one of my mysql databases.
>According to the perldoc for DBD::mysql, I should use the DBI standard
>interface: $dbh->tables(); over the depreciated $dbh->func('_ListTables').
>On various websites, I've seen both used and recomended.
>
>But after searching through perldoc.com, I saw that the $dbh->tables()
>call was new and experimental and may be changed.
>
>So I've tried both calls, and neither one has worked. I've gone into the
>database, to double check that there exist actual tables to be displayed,
>and there are. Is there a proper way to query a database for the tables
>within it? Also, if this is not the proper list to post this question to,
>my appologies.
>
>I'm using perl 5.005_03, DBI version 1.20 (?) and DBD::mysql version
>2.1004 (?) on i386. (Note: I got the version numbers by grep-ing through
>the respective perl modules. If there is an "official" manner to check
>the version, I'd be intrested in knowing it.)
print "$DBI::VERSION\n";
print "$DBD::mysql::VERSION\n";
>
>Thank you in advance,
>Cary
Re: _ListTables or ->tables?
At 9:40 -0500 6/18/02, Cary Mathews wrote:
>I'm trying to list the tables within one of my mysql databases.
>According to the perldoc for DBD::mysql, I should use the DBI standard
>interface: $dbh->tables(); over the depreciated $dbh->func('_ListTables').
>On various websites, I've seen both used and recomended.
>
>But after searching through perldoc.com, I saw that the $dbh->tables()
>call was new and experimental and may be changed.
my @tables = $dbh->tables ();
print "@tables\n";
works for me. This is with RedHat 7.0, DBI 1.23, DBD::mysql 2.1017 ...
okay, I just tried it under Win2000, DBI 1.201, DBD::mysql 2.0400, and
it worked there, too.
Do you get an error, or just nothing?
>
>So I've tried both calls, and neither one has worked. I've gone into the
>database, to double check that there exist actual tables to be displayed,
>and there are. Is there a proper way to query a database for the tables
>within it? Also, if this is not the proper list to post this question to,
>my appologies.
>
>I'm using perl 5.005_03, DBI version 1.20 (?) and DBD::mysql version
>2.1004 (?) on i386. (Note: I got the version numbers by grep-ing through
>the respective perl modules. If there is an "official" manner to check
>the version, I'd be intrested in knowing it.)
>
>Thank you in advance,
>Cary
Re: How to store images in MySql table
At 15:54 +0530 6/15/02, Saju wrote: >Hi, >I want to store images in MySQL table. > >Is it by creating a table with one of its fields a BLOB type. >Then how will i insert the image into the table. It's no different than any other kind of data: - Use a placeholder and bind the image value to the placeholder or - Quote it with $dbh->quote() and insert the result directly into the query string >Thanks > >Saju
Re: Fw: mysql query statement
At 9:50 -0700 6/10/02, William R. Mussatto wrote: >On Mon, 10 Jun 2002, Paul DuBois wrote: > >> Date: Mon, 10 Jun 2002 10:41:12 -0500 >> From: Paul DuBois <[EMAIL PROTECTED]> >> To: Hytham Shehab <[EMAIL PROTECTED]>, dbi <[EMAIL PROTECTED]> >> Subject: Re: Fw: mysql query statement >> >> At 18:35 +0300 6/10/02, Hytham Shehab wrote: >> >So, how can i make a statement like these without writing it explicity in >> >the prepare statement? >> >> Do you mean "how can I write a statement where I can arbitrarily change >> any part of it at execute() time?" >> >> If so, you can't. >> >> > >> >thx guys. >> > >> >-- >> >Hytham Shehab > >dbh->selectrow_array(...). >and its cousins are more appropriate. Eh? How does that change the answer to the question? selectrow_array() and cousins map to prepare + execute + some row-fetching method. > > >Sincerely, > >William Mussatto, Senior Systems Engineer >CyberStrategies, Inc >ph. 909-920-9154 ext. 27
Re: Fw: mysql query statement
At 18:35 +0300 6/10/02, Hytham Shehab wrote: >So, how can i make a statement like these without writing it explicity in >the prepare statement? Do you mean "how can I write a statement where I can arbitrarily change any part of it at execute() time?" If so, you can't. > >thx guys. > >-- >Hytham Shehab
Re: Fw: mysql query statement
At 18:05 +0300 6/10/02, Hytham Shehab wrote:
> hi all of u,
> i got a simple question, why (1) is valid, however, (2) is not ?!!
> (1) sorting order is explicity typed in the query statement:
> $sth = $dbh->prepare("select student_name from students order by
>first_name
> asc");
> $sth->execute();
> (2) sorting order is passed as a bind parameter:
> $sort = 'asc';
> $sth = $dbh->prepare("select student_name from students order by first_name
> ?");
> $sth->execute($sort);
Because placeholders are valid only for data values, not for keywords
or identifiers.
> thx v. much
> --
> Hytham Shehab
Re: Segmentation fault using mysql_read_default_file
At 18:45 -0500 6/7/02, Danny wrote:
>Danny writes:
>>
>>I just upgraded to redhat 7.3 and am now having this problem when I
>>try to read my /home/dwrice/.my.cnf file.
>>
>>perl -we 'use DBI;DBI->trace(2);my $dsn =
>>"DBI:mysql:dwrice;mysql_read_default_group=client;mysql_read_default_file=$ENV{HOME}/.my.cnf";my
>
>>$dbi = DBI->connect($dsn,undef,undef) || die $DBI::errstr;print
>>"ref=$dbi\n"'
>>
>> DBI 1.21-nothread dispatch trace level set to 2
>> ->
>>DBI->connect(DBI:mysql:dwrice;mysql_read_default_group=client;mysql_read_default_file=/home/dwrice/.my.cnf,
>
>>, )
>> -> DBI->install_driver(mysql) for linux perl=5.006001 pid=3158
>>ruid=500 euid=500
>>install_driver: DBD::mysql version 2.0419 loaded from
>>/usr/lib/perl5/site_perl/5.6.1/i386-linux/DBD/mysql.pm
>> <- install_driver= DBI::dr=HASH(0x81019d0)
>> -> default_user in DBD::_::dr for DBD::mysql::dr
>>(DBI::dr=HASH(0x81019d0)~0x81435a4 undef undef HASH(0x818b560))
>> <- default_user= ( undef undef ) [2 items] at DBI.pm line 468
>> -> connect for DBD::mysql::dr
>>(DBI::dr=HASH(0x81019d0)~0x81435a4
>>'dwrice;mysql_read_default_group=client;mysql_read_default_file=/home/dwrice/.my.cnf'
>
>>undef HASH(0x818b560))
>>imp_dbh->connect: dsn =
>>dwrice;mysql_read_default_group=client;mysql_read_default_file=/home/dwrice/.my.cnf,
>>uid = , pwd =
>>imp_dbh->MyLogin: dbname = dwrice, uid = NULL, pwd = NULL,host =
>>NULL, port = NULL
>>imp_dbh->MyConnect: host = NULL, port = 0, uid = NULL, pwd = NULL
>>imp_dbh->MyConnect: Reading default file /home/dwrice/.my.cnf.
>>imp_dbh->MyConnect: Using default group client.
>>imp_dbh->MyConnect: client_flags = 0
>>Segmentation fault
>>
>>My .my.cnf still works fine with the mysql client.
>>
>>I am using
>>perl-5.6.1-34.99.6
>>perl-DBI-1.21-1
>>perl-DBD-MySQL-1.2219-6
>>
>>-Danny
>
>I was running the default redhat 7.3 mysql-3.23.49-3 when the above
>occurred. I replace 3.23.49-3 with the 4.0 alpha mysql MySQL-4.0.1-2
>and this problem went away.
>
>-Danny
This change notes page probably explains what you were seeing:
http://www.mysql.com/doc/N/e/News-3.23.50.html
That page has this item near the end:
Fixed core dump bug when reading client groups from option files
using mysql_options().
