Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Major
-
Resolution: Not a Bug
-
Affects Version/s: 10.0.17
-
Fix Version/s: 10.0.17
-
Component/s: Storage Engine - Connect
-
Environment:Windows 8.1, x64
Description
Varchar size in the following case is limited to 203. I don't know how to extend it.
DDL:
drop table if exists `lite_test`;
create table lite_test (
`id` INT(10) UNSIGNED NOT NULL,
`name` varchar(203) NOT NULL
)
ENGINE=CONNECT READONLY=1 TABLE_TYPE=ODBC tabname='test'
CONNECTION='DSN=SQLite3 Datasource;Database=C:\\Download
db.sqlite'
CHARSET=utf8 DATA_CHARSET=utf8;
And now:
SELECT * FROM `lite_test` ORDER BY `name` ASC /* OK */
But:
ALTER TABLE `lite_test` CHANGE `name` `name` VARCHAR(205) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL;
SELECT * FROM `lite_test` ORDER BY `name` ASC
Throws an error:
" ERROR #1032 - Can't find record in 'lite_test'. "
But without ORDER BY clause works as expected.
ENV:
connect conv size 8,192
connect exact info OFF
connect indx map OFF
connect json grp size 10
connect timeout 10
connect type conv NO
connect use tempfile AUTO
connect work size 67,108,864
connect xtrace 0
SQLITE DDL:
DROP TABLE IF EXISTS "test";
CREATE TABLE "test" ("id" INTEGER, "name" TEXT);
INSERT INTO "test" VALUES(1,'test');
INSERT INTO "test" VALUES(2,'test2');
INSERT INTO "test" VALUES(3,'test3');
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
This not a bug but a limitation due to the fact that ODBC tables are not indexable. Look at the corresponding chapter of the documentation:
https://mariadb.com/kb/en/mariadb/connect-table-types-odbc-table-type-accessing-tables-from-other-dbms/#random-access-of-odbc-tables
In your case of a read only small table, the simpler way to make this work is to execute:
Why does it happen only when the varchar size is more than 203 is probably due to MariaDB that decides over this limit to use random access instead of sorting locally values obtained sequentially.