[freeside-devel] freeside-daily v2.1.1 mysql issue

[Ivan Kohler]

On Mon, Oct 25, 2010 at 07:45:47PM -0700, Vernon E Peets wrote:
> [a bunch of HTML]

Next time it would probably be a good idea to use plaintext.  :)

> It seems that there is a small MySQL compatibility problem in the SQL
> code in FS/Cron/bill.pm. The error message I received when running
> freeside-daily was:
> 
> DBD::mysql::db do 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 'DECLARE cron_bill_cursor CURSOR FOR
> SELECT custnum FROM cust_main WHERE cust_m' at line 1 at
> /usr/lib/perl5/site_perl/5.8.8/FS/Cron/bill.pm line 63.
> 
> [...]
> 
> I modified the code and have attached a patch (bill.pm.patch).<br>

It is difficult to follow the relevant parts of your patch as it 
includes a bunch of local cruft like your debugging print statements.  
When submitting a patch, it is a good idea to make sure those are 
removed first and the patch only includes relevant changes.  Please also 
send patches as unified diffs (-u).

In the meantime I've implemented a workaround for MySQL which simply 
forgoes the use of cursors.  Although not as scalable as the cursor 
version, it should be fine for MySQL folks with under 100k customers or 
so.  :)

> Once this was changed another issue arose in notify.pm.&nbsp; The error
> message received:
> 
> 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 'INTEGER ) > 0
> ... at /usr/lib/perl5/site_perl/5.8.8/FS/Record.pm line 454.
> 
> 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 'INTEGER
> ) > 0 and 0 <= ( 1287286216'
> at line 10 at /usr/lib/perl5/site_perl/5.8.8/FS/Cron/notify.pm line 89
> 
> It appears that the CAST statement type was hardwired in a couple of
> places to "INTEGER" which I assume is kosher for Postgres. There is an
> $integer variable used in other places which is set to "SIGNED" for
> MySQL environments.
> 
> I modified the code and have attached a patch (notify.pm.patch).

Thanks!  This was already fixed in CVS on October 19th.  :)

Thanks again for the patches!

-- 
_ivan