[RESOLVED] A string being truncated unexpectedly

davidmorley

I am storing a string of characters typically 90 to 110 characters long. I created the dbt using the following code

<?php
require_once 'login.php';
$conn = new mysqli($hn, $un, $pw, $db);
if ($conn->connect_error) die($conn->connect_error);

$query = "CREATE TABLE IF NOT EXISTS Runner (
id SMALLINT NOT NULL AUTO_INCREMENT,
datastring VARCHAR(150) NOT NULL,
PRIMARY KEY (id)
)";

$result = $conn->query($query);
if (!$result) die ("Database access failed: " . $conn->error);
?>

(So far as I understand it I could have up to 255 characters where I have written 150 )

When I write into the dbt, the string is being cut short; I not quite sure of the truncated length, but I think it might be 80 characters long. (The system shamelessly tells me the length of my original string ! )

Can anyone tell me what I did wrong, please ?

NogDog

Just to make sure: since the above create statement is only executed if the table does not already exist, is it possible it already exists with a smaller max length?

dalecosp

davidmorley;11059529 wrote:
(So far as I understand it I could have up to 255 characters where I have written 150 )

Just as an aside: for MySQL versions <= 5.0.3, yes. (Are people still that far behind?) After 5.0.3, VARCHAR(65535) is the limit.

davidmorley

The table definitely did not exist; I previously created the dbt with a length of 32, not having arrived at the long string I now use. When I realised the problem, I deleted the dbt table completely using tools at the server. Then I created a new table using the code shown.

NogDog

So, other things to consider:

The application code is truncating it for some reason
The application code is doing something like htmlentitities() to the inputs and adding characters to the string (characters that aren't directly seen in the output, unless you do a view source in the browser), which is why I would not escape text data when going into the database, but instead waiting until you are actually outputting it to the browser.
Possibly a unicode issue where the DB character limit is effectively a byte limit, but you have multi-byte characters in the data?

davidmorley

OK This has been resolved now. The Plesk panel does not show the whole string for some reason, but if I download the striong and write it out, it is all there.

Sorry for wasting your time.

NogDog

Ahh...didn't even think about how you were looking at it. phpPgAdmin does something similar in its web interface, which makes general viewing cleaner, but means you have to take extra steps to see the full content sometimes. 🙁

Derokorian

Most of these viewers do something like that, I know workbench and phpmyadmin do - but they, those 2 at least, usually have some sort of visual indicator that the content is being truncated in the current view.