View Issue Details

IDProjectCategoryView StatusLast Update
0006464VALENTINA SERVERSQLpublic2013-06-27 19:36
ReporterHelge TjeltaAssigned ToRuslan Zasukhin 
PriorityhighSeveritymajorReproducibilityalways
Status resolvedResolutionfixed 
PlatformINTELOSMac OSOS Version10.8
Product Version5.0 
Target VersionFixed in Version 
Summary0006464: Can not do LIKE with both upper and lowercase, cant ignore case
DescriptionWhen doing ; SELECT * FROM db WHERE fld LIKE '%txt%' I get only the actual case on letters not both: Bitmap and bitmap…

Doing the same with an old database/server (from Gyro my old workplace) it will work… Now I don't get it at all….

Testing done inside Valentina Studio….

Can there be any problems with my new one at Bitmap that is was created in 4.9 and just copied over til a 5.0 server… not a upgrade or any other thing, just copied to a new server that was 5.0 ??

TagsNo tags attached.

Activities

Helge Tjelta

Helge Tjelta

2013-06-20 12:45

reporter  

Bitmap_Server.png (57,040 bytes)
Helge Tjelta

Helge Tjelta

2013-06-20 12:45

reporter  

Bitmap_database.png (77,090 bytes)
Helge Tjelta

Helge Tjelta

2013-06-20 12:46

reporter  

Bitmap_Table.png (64,926 bytes)
Helge Tjelta

Helge Tjelta

2013-06-20 12:46

reporter  

Gyro_Table.png (63,268 bytes)
Helge Tjelta

Helge Tjelta

2013-06-20 12:46

reporter  

Gyro_database.png (75,796 bytes)
Helge Tjelta

Helge Tjelta

2013-06-20 12:46

reporter  

Gyro_server.png (55,557 bytes)
Ivan Smahin

Ivan Smahin

2013-06-20 13:01

manager   ~0007089

>Can there be any problems with my new one at Bitmap that is was created in 4.9 and just copied over til a 5.0 server… not a upgrade or any other thing, just copied to a new server that was 5.0 ??

No - it can't be any problem. vServer just silently convert "old" database to the most current format itself.
Ivan Smahin

Ivan Smahin

2013-06-20 13:07

manager   ~0007090

CREATE TABLE "t1" (
    "f1" STRING (1022) );

SET PROPERTY
    [CollationStrength]
 of FIELD "t1"."f1" TO 'kPrimary';

INSERT INTO "t1" ("f1") VALUES ('BitMap');
INSERT INTO "t1" ("f1") VALUES ('bitMap');
INSERT INTO "t1" ("f1") VALUES ('Bitmap');
INSERT INTO "t1" ("f1") VALUES ('bitmap');

-- returns all the records because of CollationStrength == kPrimary
SELECT * FROM t1 WHERE f1 LIKE '%bitmap%'
Helge Tjelta

Helge Tjelta

2013-06-20 19:17

reporter   ~0007091

Hi Ivan, here is the problem, I did what you said, both in my bitmap database and a new database on that server. I made the table and field in Valentina Studio, but I only get one record when doing that select

Why does this happen on this server…. ???
Helge Tjelta

Helge Tjelta

2013-06-20 19:18

reporter   ~0007092

You can try on the server your self, and maybe find out what is wrong…
Please send me an email and I drop you ther serverinfo.

/Helge
Helge Tjelta

Helge Tjelta

2013-06-20 19:20

reporter   ~0007093

Last edited: 2013-06-20 19:21

View 2 revisions

Now I just saw that the newly created database had strength set to tertiary… when changed to primary and open again, it worked…

But doing it in my old database on the same server, does not work...

Ivan Smahin

Ivan Smahin

2013-06-21 06:55

manager   ~0007094

Could you send me that db for testing? Ivan Smahin <ivan_smahin at valentina-db.com>

What about diagnose and clone that db?
Does it work locally?
Helge Tjelta

Helge Tjelta

2013-06-21 11:06

reporter   ~0007095

Last edited: 2013-06-21 19:31

View 3 revisions

I did a copy of the database to my own laptop, running server 5.3. Same result… the test database worked, but the old one from bitmap will not. I only get bitmap when doing:
SELECT * FROM t1 WHERE f1 LIKE '%bitmap%'….

Something is wrong with that database….

I cloned it and copied it back into the databases folder, did register and opened the clone.. still no luck...

Also, I made a DUMP, created a new empty database, Load Dump into that one. Still the same…

Ivan Smahin

Ivan Smahin

2013-06-24 11:01

manager   ~0007097

You should reset "Locale name" for the database.

Currently it is set to "Norwegian (Bokmal)" and if you do the SQL dump you will see something like this one:

CREATE DATABASE IF NOT EXISTS [BitmapTest] WITH
SET PROPERTY
...
    [LocaleName],
...
of DATABASE TO
...
    'Norwegian (Bokmal)',
...

When I try to set it to "US English" and then back to "Norwegian (Bokmal)" (using Valentina studio) I get different locale name in the dump and everything above start working:

CREATE DATABASE IF NOT EXISTS [BitmapTest] WITH
SET PROPERTY
...
    [LocaleName],
...
of DATABASE TO
...
    'nb_NO',
...

I'm not about to load that dump - just to see changes.

It seems the collator (performs actual "string compare" in like algorithm) which is related to the "original" "Norwegian (Bokmal)" locale is case-sensitive even on kSecondary level (for unknown reason).
Helge Tjelta

Helge Tjelta

2013-06-24 19:07

reporter   ~0007103

Thanks Ivan, I'll try this, when I get back from vacation!
Helge Tjelta

Helge Tjelta

2013-06-27 19:10

reporter   ~0007137

Hi Ivan, I tried to just change the locale name, first to english, save and refresh, then back to norwegian Bokmal, save and refresh….

And now everyting works again. NO problem with my program anymore… Super thanks!!!!

Issue History

Date Modified Username Field Change
2013-06-20 12:45 Helge Tjelta New Issue
2013-06-20 12:45 Helge Tjelta File Added: Bitmap_Server.png
2013-06-20 12:45 Helge Tjelta File Added: Bitmap_database.png
2013-06-20 12:46 Helge Tjelta File Added: Bitmap_Table.png
2013-06-20 12:46 Helge Tjelta File Added: Gyro_Table.png
2013-06-20 12:46 Helge Tjelta File Added: Gyro_database.png
2013-06-20 12:46 Helge Tjelta File Added: Gyro_server.png
2013-06-20 13:01 Ivan Smahin Note Added: 0007089
2013-06-20 13:07 Ivan Smahin Note Added: 0007090
2013-06-20 19:17 Helge Tjelta Note Added: 0007091
2013-06-20 19:18 Helge Tjelta Note Added: 0007092
2013-06-20 19:20 Helge Tjelta Note Added: 0007093
2013-06-20 19:21 Helge Tjelta Note Edited: 0007093 View Revisions
2013-06-21 06:55 Ivan Smahin Note Added: 0007094
2013-06-21 11:06 Helge Tjelta Note Added: 0007095
2013-06-21 11:23 Helge Tjelta Note Edited: 0007095 View Revisions
2013-06-21 19:31 Helge Tjelta Note Edited: 0007095 View Revisions
2013-06-24 11:01 Ivan Smahin Note Added: 0007097
2013-06-24 19:07 Helge Tjelta Note Added: 0007103
2013-06-27 19:10 Helge Tjelta Note Added: 0007137
2013-06-27 19:36 Ruslan Zasukhin Status new => resolved
2013-06-27 19:36 Ruslan Zasukhin Resolution open => fixed
2013-06-27 19:36 Ruslan Zasukhin Assigned To => Ruslan Zasukhin