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`;

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

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.

(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

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.

(In reply to comment #5)
> Sorry Alex, I don't even know how to build with MariaDB.

(Very) recent LibreOffice master.

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.

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 ?

(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

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

Any takers on it being linked to DATE/TIME handling ?

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

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.

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++.

(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.

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)?

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

Changed title to reflect (I hope) the real situation as I understand it. If it is wrong, please correct.


Alex

(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

This is a bug in MySQL Connector/C++.

(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 ;)