Last few days I have trouble with one of my production app.
The trouble was I could not get my data through the app even though the data was recorded in the database.
To give you a context I will explain my table structure.
My app saves user’s data from several devices, So I need to save the id and device_id to know from which device the data was recorded. It is clear, to get John data I can use this SQL query
SELECT from users WHERE id = 123 and device_id = "JDAHADNW"
Unfortunately, I did not get anything. When I check the device_id values I found that after “JDAHADNW” there was a new line character (enter).
So I need to remove the newline character (enter) to get my previous query to work properly.
A further problem is that the rows that experience something like this are not only 1 but hundreds, so I need an instant solution that can remove all enter characters in the device_id column.
This is what I did to solve my problem
UPDATE sampling SET device_id = TRIM(TRAILING '\n' FROM device_id)
However, if the newline character is at the start of the field you can use this query instead
update mytable SET title = TRIM(LEADING '\n' FROM title)
Finally, my problem solved!
If you have trouble with Laravel Specified key was too long error you can check this out: https://tech.digitindo.com/2021/01/25/laravel-specified-key-was-too-long-error/