Bug 62645 - VIEWING - internally shipped mysqlcppconn can't handle MYSQL_TYPE_NEWDATE
Summary: VIEWING - internally shipped mysqlcppconn can't handle MYSQL_TYPE_NEWDATE
Status: RESOLVED NOTOURBUG
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
4.1.0.0.alpha0+ Master
Hardware: x86-64 (AMD64) macOS (All)
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks:
 
Reported: 2013-03-22 15:21 UTC by Alex Thurgood
Modified: 2013-03-24 15:02 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:
Regression By:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Alex Thurgood 2013-03-22 15:21:31 UTC
Tested Version 4.1.0.0.alpha0+ (Build ID: edf57013463eff6d3b40d8d5d933577f0e6cf8e)

After having gotten the new libmariadb connector to build on OSX, I am seeing the following error when browsing my table list :

The data content could not be loaded.
allocate_buffer_for_field: invalid rbind data type

The SQL command leading to this error is:

SELECT * FROM "ipdatabase"."natpha_ddl"

natpha_ddl is a server registered view

It is built in the following way :
DROP VIEW IF EXISTS `natpha_ddl`;
CREATE OR REPLACE ALGORITHM=UNDEFINED DEFINER=`alex`@`%` SQL SECURITY DEFINER VIEW `natpha_ddl` AS select `oldfmpro`.`File_Num` AS `file_num`,`oldfmpro`.`Owner1` AS `Owner1`,`oldfmpro`.`Filing_Num` AS `filing_num`,`oldfmpro`.`Filing_Date1` AS `filing_date1`,`oldfmpro`.`Prio_FF_Date1` AS `Prio_FF_Date1`,if(((`oldfmpro`.`Prio_FF_Date1` + interval 30 month) is not null),(`oldfmpro`.`Prio_FF_Date1` + interval 30 month),(`oldfmpro`.`Filing_Date1` + interval 30 month)) AS `NATPHA` from `oldfmpro` where ((`oldfmpro`.`Filing_Num` is not null) and (`oldfmpro`.`Ctry_Short` = 'WO') and (`oldfmpro`.`IngJur` like '%ajt%') and (`oldfmpro`.`Filing_Date1` >= (curdate() + interval -(30) month))) order by if(((`oldfmpro`.`Prio_FF_Date1` + interval 30 month) is not null),(`oldfmpro`.`Prio_FF_Date1` + interval 30 month),(`oldfmpro`.`Filing_Date1` + interval 30 month));

The display of the data in this view used to function flawlessly with the previous mysql connector, so there must be some difference in the libmariadb and mysql connector code which is causing this to happen.

The view functions normally in other software (MySQLWorkench, MySQLQuery Browser).



Alex
Comment 1 Alex Thurgood 2013-03-22 15:24:31 UTC
In comparison, the following view does not cause an error :


DROP VIEW IF EXISTS `prio_ddl`;
CREATE OR REPLACE ALGORITHM=UNDEFINED DEFINER=`alex`@`%` SQL SECURITY DEFINER VIEW `prio_ddl` AS select `oldfmpro`.`fmpro_id` AS `fmpro_id`,`oldfmpro`.`Client` AS `Client`,concat(`oldfmpro`.`Client_Copy`,`oldfmpro`.`File_Code`,`oldfmpro`.`File_Num`,`oldfmpro`.`Ctry_Short`) AS `N_ref`,`oldfmpro`.`Filing_Num` AS `Filing_num`,`oldfmpro`.`Filing_Date1` AS `Filing_date1`,(`oldfmpro`.`Filing_Date1` + interval 1 year) AS `DDL_EXT`,`oldfmpro`.`Comments` AS `Comments`,`oldfmpro`.`Summary` AS `Summary` from `oldfmpro` where ((`oldfmpro`.`Filing_Date1` > (curdate() + interval -(1) year)) and isnull(`oldfmpro`.`Prio_FF_Date1`) and (`oldfmpro`.`File_Code` = 'P')) order by `oldfmpro`.`Filing_Date1`;
Comment 2 Alex Thurgood 2013-03-22 15:29:33 UTC
The table definition of oldfmpro :

CREATE TABLE `oldfmpro` (
  `Agent` varchar(254) DEFAULT NULL,
  `Ann_Fees1` date DEFAULT NULL,
  `Attention` varchar(254) DEFAULT NULL,
  `IngJur` char(3) DEFAULT NULL,
  `Client` varchar(254) DEFAULT NULL,
  `Client_Copy` varchar(254) DEFAULT NULL,
  `Comments` varchar(254) DEFAULT NULL,
  `Ctry_Short` varchar(254) DEFAULT NULL,
  `Prio_Ctry1` char(2) DEFAULT '',
  `Prio_Ctry2` char(2) DEFAULT '',
  `Prio_Ctry3` char(2) DEFAULT '',
  `Prio_Ctry4` char(2) DEFAULT '',
  `Prio_Ctry5` char(2) DEFAULT '',
  `Prio_Ctry6` char(2) DEFAULT '',
  `Exp_Date1` date DEFAULT NULL,
  `File_Code` varchar(254) DEFAULT NULL,
  `File_Num` varchar(254) DEFAULT NULL,
  `Filing_Num` varchar(254) DEFAULT NULL,
  `Filing_Date1` date DEFAULT NULL,
  `Grant_Num` varchar(254) DEFAULT NULL,
  `Grant_Date1` date DEFAULT NULL,
  `Inventor1` varchar(50) DEFAULT '',
  `Inventor2` varchar(50) DEFAULT '',
  `Inventor3` varchar(50) DEFAULT '',
  `Inventor4` varchar(50) DEFAULT NULL,
  `Inventor5` varchar(50) DEFAULT '',
  `Inventor6` varchar(50) DEFAULT '',
  `Inventor7` varchar(50) DEFAULT '',
  `Inventor8` varchar(50) DEFAULT '',
  `Inventor9` varchar(50) DEFAULT '',
  `Inventor10` varchar(50) DEFAULT '',
  `Keywords` varchar(254) DEFAULT NULL,
  `Our_Ref` varchar(254) DEFAULT NULL,
  `Owner1` varchar(254) NOT NULL DEFAULT '',
  `Owner2` varchar(254) DEFAULT NULL,
  `Owner3` varchar(100) DEFAULT '',
  `Owner4` varchar(100) DEFAULT '',
  `Owner5` varchar(100) DEFAULT '',
  `Owner6` varchar(100) DEFAULT '',
  `Owner7` varchar(100) DEFAULT '',
  `Owner8` varchar(100) DEFAULT '',
  `Owner9` varchar(100) DEFAULT '',
  `Owner10` varchar(100) DEFAULT '',
  `Prio_SN` varchar(254) DEFAULT NULL,
  `Prio_SN2` varchar(30) DEFAULT NULL,
  `Prio_SN3` varchar(30) DEFAULT NULL,
  `Prio_SN4` varchar(30) DEFAULT NULL,
  `Prio_SN5` varchar(30) DEFAULT NULL,
  `Prio_SN6` varchar(30) DEFAULT NULL,
  `Prio_SN7` varchar(30) DEFAULT NULL,
  `Prio_SN8` varchar(30) DEFAULT NULL,
  `Prio_FF_Date1` date DEFAULT NULL,
  `Prio_FF_Date2` date DEFAULT NULL,
  `Prio_FF_Date3` date DEFAULT NULL,
  `Prio_FF_Date4` date DEFAULT NULL,
  `Prio_FF_Date5` date DEFAULT NULL,
  `Prio_FF_Date6` date DEFAULT NULL,
  `Prio_FF_Date7` date DEFAULT NULL,
  `Prio_FF_Date8` date DEFAULT NULL,
  `Pub_Num` varchar(254) DEFAULT NULL,
  `Pub_Date1` date DEFAULT NULL,
  `Related_Files` varchar(254) DEFAULT NULL,
  `Summary` varchar(254) DEFAULT NULL,
  `Y_ref` varchar(254) DEFAULT NULL,
  `State_dep` varchar(254) DEFAULT NULL,
  `State_dep_num` varchar(254) DEFAULT NULL,
  `chg_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `fmpro_id` int(10) unsigned zerofill NOT NULL AUTO_INCREMENT,
  `pay_ann_fee` char(1) NOT NULL DEFAULT 'Y',
  `assistant` varchar(7) DEFAULT NULL,
  `Prio_Ctry7` char(2) DEFAULT NULL,
  `Prio_Ctry8` char(2) DEFAULT NULL,
  `abandon` char(1) DEFAULT NULL,
  `chg_by` varchar(50) NOT NULL,
  PRIMARY KEY (`fmpro_id`)
) ENGINE=MyISAM AUTO_INCREMENT=4474 DEFAULT CHARSET=latin1
Comment 3 Lionel Elie Mamane 2013-03-22 15:38:34 UTC
If this is a mariadb-client-c library bug, it will have to end up in their bug tracker anyway... I would report it there.
Comment 4 Alex Thurgood 2013-03-22 15:45:56 UTC
(In reply to comment #3)
> If this is a mariadb-client-c library bug, it will have to end up in their
> bug tracker anyway... I would report it there.

Am poking around a bit more to find out where this message is coming from, but it is a showstopper on me migrating to the libmariadb connector.


Alex
Comment 5 Julien Nabet 2013-03-22 15:50:34 UTC
Sorry Alex, I don't even know how to build with MariaDB. On pc Debian x86-64 testing, there's no package which name include "MariaDB" or just "Maria".
Now, if you know what options in autogen.lastrun + Debian package I would have missed, I can give it a try.
Comment 6 Lionel Elie Mamane 2013-03-22 16:04:10 UTC
(In reply to comment #5)
> Sorry Alex, I don't even know how to build with MariaDB.

(Very) recent LibreOffice master.
Comment 7 Julien Nabet 2013-03-22 16:13:34 UTC
I use master sources and git update it almost every day.
I saw options concerning Mariadb in configure.ac but when I used them, something (I don't remember what) was lacking. I'll be able to give console logs during week-end.
Comment 8 Alex Thurgood 2013-03-22 16:14:02 UTC
allocate_buffer_for_field

is to be found in mysqlresult_bind.cpp in 

/core/mysqlcppconn/unxmacxi.pro/misc/build/mysql-connector-c++-1.1.0/driver


#if A0
		// There two are not sent over the wire
		case MYSQL_TYPE_ENUM:
		case MYSQL_TYPE_SET:
#endif
		case MYSQL_TYPE_BIT:
			return st_buffer_size_type(new char[8], 8, MYSQL_TYPE_BIT);
		case MYSQL_TYPE_GEOMETRY:
		default:
			// TODO: Andrey, there can be crashes when we go through this. Please fix.
			throw sql::InvalidArgumentException("allocate_buffer_for_field: invalid rbind data type");




so it would seem to be linked to the builtin version we ship internally ?
Comment 9 Alex Thurgood 2013-03-22 16:17:40 UTC
(In reply to comment #7)

Hi Julien

> I use master sources and git update it almost every day.
> I saw options concerning Mariadb in configure.ac but when I used them,
> something (I don't remember what) was lacking. I'll be able to give console
> logs during week-end.



From master using the :

--enable-ext-mariadb-connector autogen switch


The default behaviour is for the build process to download (a patched) libmariadb tgz, unpack it and then builds it internally from there. Can't think what else it might need.


Alex
Comment 10 Alex Thurgood 2013-03-22 16:26:30 UTC
Okay, so if I understand correctly, this stuff :

/* {{{ allocate_buffer_for_field() -I- */
typedef std::pair<char *, size_t> BufferSizePair;
static struct st_buffer_size_type
allocate_buffer_for_field(const MYSQL_FIELD * const field)
{
	switch (field->type) {
		case MYSQL_TYPE_NULL:
			return st_buffer_size_type(NULL, 0, field->type);
		case MYSQL_TYPE_TINY:
			return st_buffer_size_type(new char[1], 1, field->type);
		case MYSQL_TYPE_SHORT:
			return st_buffer_size_type(new char[2], 2, field->type);
		case MYSQL_TYPE_INT24:
		case MYSQL_TYPE_LONG:
		case MYSQL_TYPE_FLOAT:
			return st_buffer_size_type(new char[4], 4, field->type);
		case MYSQL_TYPE_DOUBLE:
		case MYSQL_TYPE_LONGLONG:
			return st_buffer_size_type(new char[8], 8, field->type);
		case MYSQL_TYPE_YEAR:
			return st_buffer_size_type(new char[2], 2, MYSQL_TYPE_SHORT);
		case MYSQL_TYPE_TIMESTAMP:
		case MYSQL_TYPE_DATE:
		case MYSQL_TYPE_TIME:
		case MYSQL_TYPE_DATETIME:
			return st_buffer_size_type(new char[sizeof(MYSQL_TIME)], sizeof(MYSQL_TIME), field->type);


		case MYSQL_TYPE_TINY_BLOB:
		case MYSQL_TYPE_MEDIUM_BLOB:
		case MYSQL_TYPE_LONG_BLOB:
		case MYSQL_TYPE_BLOB:
		case MYSQL_TYPE_STRING:
		case MYSQL_TYPE_VAR_STRING:
			return st_buffer_size_type(new char[field->max_length + 1], field->max_length + 1, field->type);

		case MYSQL_TYPE_DECIMAL:
		case MYSQL_TYPE_NEWDECIMAL:
			return st_buffer_size_type(new char[64], 64, field->type);
#if A1
		case MYSQL_TYPE_TIMESTAMP:
		case MYSQL_TYPE_YEAR:
			return st_buffer_size_type(new char[10], 10, field->type);
#endif
#if A0
		// There two are not sent over the wire
		case MYSQL_TYPE_ENUM:
		case MYSQL_TYPE_SET:
#endif
		case MYSQL_TYPE_BIT:
			return st_buffer_size_type(new char[8], 8, MYSQL_TYPE_BIT);
		case MYSQL_TYPE_GEOMETRY:
		default:
			// TODO: Andrey, there can be crashes when we go through this. Please fix.
			throw sql::InvalidArgumentException("allocate_buffer_for_field: invalid rbind data type");
	}
}


tells the connector which TYPEs to accept and how much buffer allocation to allow for each one.

Now, it looks like I have to go on a hunt to find the offending unsupported type in my view.


Note that the OSX mysql connector never relied on mysqlcppconn in the past (at least, not on any of my builds), it was built against the MySQL C connector source library only.


Alex
Comment 11 Alex Thurgood 2013-03-22 16:29:16 UTC
Any takers on it being linked to DATE/TIME handling ?
Comment 12 Alex Thurgood 2013-03-22 17:21:10 UTC
If I run the SQL statement as a query from within the Query section of LO (Query in SQL View), I get the same error.


If I remove :

if(((`oldfmpro`.`Prio_FF_Date1` + interval 30 month) is not null),(`oldfmpro`.`Prio_FF_Date1` + interval 30 month),(`oldfmpro`.`Filing_Date1` + interval 30 month)) AS `NATPHA`

from the query, then the following query does execute :

select `oldfmpro`.`File_Num` AS `file_num`,`oldfmpro`.`Owner1` AS `Owner1`,`oldfmpro`.`Filing_Num` AS `filing_num`,`oldfmpro`.`Filing_Date1` AS `filing_date1`,`oldfmpro`.`Prio_FF_Date1` AS `Prio_FF_Date1` from `oldfmpro` where ((`oldfmpro`.`Filing_Num` is not null) and (`oldfmpro`.`Ctry_Short` = 'WO') and (`oldfmpro`.`IngJur` like '%ajt%') and (`oldfmpro`.`Filing_Date1` >= (curdate() + interval -(30) month))) order by if(((`oldfmpro`.`Prio_FF_Date1` + interval 30 month) is not null),(`oldfmpro`.`Prio_FF_Date1` + interval 30 month),(`oldfmpro`.`Filing_Date1` + interval 30 month))


so, LO will handle the IF condition in my WHERE clause, but not when I put it in my SELECT preamble as well ?? 

The VIEW used to work prior to LO 4.0.

If I look at a blank Query Designer window, after adding the table "oldfmpro", in the list of available functions, I can see only following choices :

None
count()


Any link between this and the inoperate "SELECT...IF" ?


Alex
Comment 13 Lionel Elie Mamane 2013-03-23 06:40:10 UTC
Reproduced.

Oh, the error is in mysqlcppconn code, not in libmariadb! I assumed wrong. The problematic value is MYSQL_TYPE_NEWDATE, which seems to be supported by newer mysqlcppconn. Updating that and we'll see.
Comment 14 Lionel Elie Mamane 2013-03-23 07:35:31 UTC
Nope, newest MySQL Connector/C++ still does not support MYSQL_NEWDATE fully. I opened a bug report with the MariaDB Client library for C to see if they would consider staying compatible with MySQL Connector/C++.

If not, I think we should revert to libmysqlclient until we no longer use MySQL Connector/C++.
Comment 15 Fridrich Strba 2013-03-23 16:55:53 UTC
(In reply to comment #14)
> Nope, newest MySQL Connector/C++ still does not support MYSQL_NEWDATE fully.
> I opened a bug report with the MariaDB Client library for C to see if they
> would consider staying compatible with MySQL Connector/C++.

That would be great. Just wondering how big the patch would be. Maybe they could even fix it and we backport it to our tree.

> If not, I think we should revert to libmysqlclient until we no longer use
> MySQL Connector/C++.

I don't really think that reverting anything is a good idea, just use the extension built from the 4.0 tree that I did not touch at all. It would be better to fix this in the part of code where it needs to be fixed, IMHO.
Comment 16 Lionel Elie Mamane 2013-03-23 20:36:42 UTC
Actually, I get the same error with LibreOffice 3.5.4.2 (Debian package), with system libmysqlclient and libmysqlcppconn.

Alex, are you *sure* this view works with previous versions of LibreOffice, connecting to the *same* MySQL server (MYSQL_TYPE_NEWDATE is used only by versions 5.0 and later)?
Comment 17 Alex Thurgood 2013-03-24 08:19:57 UTC
Damn, I could've sworn that this used to work, BUT... I just tested this against MySQL 5.5, LO 334 and Oracle 101 Mysql connector on OSX and I see exactly the same problem. 


Reducing severity, and apologizing profusely for the noise. Just another run of the mill Base bug - sorry peeps.



Alex
Comment 18 Alex Thurgood 2013-03-24 08:28:50 UTC
Changed title to reflect (I hope) the real situation as I understand it. If it is wrong, please correct.


Alex
Comment 19 Alex Thurgood 2013-03-24 08:30:55 UTC
(In reply to comment #16)

 
> Alex, are you *sure* this view works with previous versions of LibreOffice,
> connecting to the *same* MySQL server (MYSQL_TYPE_NEWDATE is used only by
> versions 5.0 and later)?

Well I was sure, but as I have just reported against LO334, I must have done something to either the query or the view or the server version in the past instead... :-/ Sorry once again (kicks self).


Alex
Comment 20 Lionel Elie Mamane 2013-03-24 08:41:29 UTC
This is a bug in MySQL Connector/C++.
Comment 21 Fridrich Strba 2013-03-24 15:02:18 UTC
(In reply to comment #20)
> This is a bug in MySQL Connector/C++.

Seems another reason to use simple libmariadb without any other additional layer. Like that we can fix bugs and provide better user experience ;)