MySQL/MariaDB BigInt Primary Key ID not working in Where

Wappler General Bugs
#1

Wappler Version : 3.6.1
Operating System : W10
Server Model: PHP
Database Type: MariaDB
Hosting Type: Self

Expected behavior

image
image808Ă—290 8.29 KB

Running a query on this table returns value correctly on the SQL Server

select id from tbl_x where id = 4659555762231;

Actual behavior

Running the same query via Wappler returns empty result set.

image

How to reproduce

Create a fresh new table in MariaDB

CREATE TABLE slashash_nh.tbl_test (
	id BIGINT NOT NULL,
	CONSTRAINT tbl_test_PK PRIMARY KEY (id)
)
ENGINE=InnoDB
DEFAULT CHARSET=latin1
COLLATE=latin1_swedish_ci;

Insert a row with id as 4659555762231.
Setup query builder like so:

image
image931Ă—216 9.33 KB

It should return a blank data set. Even though it returns 1 row when same query is run directly on sql server.

Running the query without the condition returns this:
image
ID being in double quotes is a bit weird since its supposed to be a number.

2021: The Year in Review
#2

Tested this on a MySQL server also. Same result.

Interestingly, if I use a custom query, it returns me the row with that ID correctly. So something is wrong with the query builder?

#3
#4

I will investigate it. The double quotes around the number is normal, the problem is that JS and PHP do not support 64bit integers and that is why these numbers are stored as strings.

1 Like
#5

@patrick did you get a chance to investigate this?

#6

In my first tests it just worked fine, tested with MySQL 8 and PHP 7.3. This was with the database in docker and unicode charset, don’t think that a latin1 charset should make the difference but will test that also, just have some other work to finish first.

#7

I don’t think chrarset is the cause here either.
Will check and share my server’s PHP and MariaDB version.

#8

PHP version 7.0
MariaDB: 10.3.27-MariaDB-cll-lve

#9

I also tested this with PHP 7.0.10 and MySql 5.7.14 - the results are returned as expected.

{
"query": 
  [
    {
      "id": 4659555762231
    }
  ]
}
#10

Thanks for the update.
From what I know, MariaDB 10.3+ is equivalent to MySQL 8.0+. Can you please also check with MySQL 8 or even better if you can find MariaDB 10.3+?

#11

Tested with mysql 8 as well, still getting the results as expected.

#12

Tried it again, still not working for me.
I checked this time on client’s production DB server which has MySQL 5.7.32 (a bit higher than 5.7.14 you tested on).

image
Query is from multiple query builder. Custom is custom query.

Can you please share the schema of the table you are testing this on? In case you did not notice the schema in original question, maybe you can try that.
Also, is there any more information on my end that I can provide to debug this?

#13

NodeJS is notorious for being bad at a few things with numbers. Big ones and division. That is why libraries such as https://github.com/MikeMcl/bignumber.js/ have been created to try and fix this.

#14

In this case it is a PHP problem, not NodeJS. Also latest JavaScript and NodeJS support BigInt (https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/BigInt).

In my test case I had a single id field of the type BIGINT, just like in the bug report. Only difference is that I tested on MySQL 8 and with the unicode charset.

1 Like
#15

So the latin charset is the issue?

#16

I tested with latin1 charset, working fine for me.

#17

Checked again with 3.7.2. Still no luck.
Anything else you can suggest to debug this please?

#18

What I do, when a query that works within PHP My Admin for example but not from Wappler, I create a custom query. This usually solves the issue.

#19

That’s right. And custom query is working for me too.
The bug report is to improve Wappler’s query builder. It should be able to run queries which it can build, reliably.

1 Like
#20

Well there are some very little cases that a query from Wappler would not work and I think that the custom query is the answer to this. I mean in every 1000 queries, 1 might not work as expected so just recreate it using a custom one and you are done!