Preventing r (Carriage Return) From Being Entered In To Fields

[N3WWN]

Hi!

When I add a row to a table in my database, a r (carriage return) is included, which is causing problems with my tables.

The field that I'm seeing this behavior in is a varchar(45).

I pull up some results, see what I need to add, switch from “(Read Only)” to the table name, click the “Insert New Row” icon and enter the data for the row. I've tried hitting enter, tabbing to the next field and using the arrow keys to exit the field, but all seem to insert the r at the end of the varchar field.

No settings became apparent when going through the preferences, connection details, help, FAQs, Google, forums, etc, so I figured I'd post my question here.

Anyone encounter this before and have a solution they can offer?

Just FYI, I'm using SQLyog Community v8.7 RC, which I've been very happy with since the “dropped where clause” bug was resolved (http://www.webyog.com/forums/index.php?showtopic=5233).

Thanks!

-Rich

[Aparna]

Hi Rich,

We are unable to reproduce this issue at our end. Could you please provide us the structure of your table and some sample data to reproduce this at our end. Also could you upgrade to the latest 8.8 Beta1 community and check if the same issue is occurring?

Thanks and Regards,

Aparna.

[N3WWN]

Hi Aparna!

Here is the table description:

Code:

mysql> describe sites;
+———-+—————+——+—–+———+—————-+
| Field | Type | Null | Key | Default | Extra |
+———-+—————+——+—–+———+—————-+
| id | int(11) | NO | PRI | NULL | auto_increment |
| site | varchar(45) | NO | UNI | NULL | |
| mac | varchar(45) | YES | UNI | NULL | |
| marketId | int(11) | YES | | NULL | |
| notes | varchar(1024) | YES | | NULL | |
+———-+—————+——+—–+———+—————-+
6 rows in set (0.00 sec)

Here is some sample data for one of our groups of sites:

Code:

mysql> select id,site,mac,marketId,notes from sites where site like 'KHQ%';
+—–+———+——————-+———-+——-+
| id | site | mac | marketId | notes |
+—–+———+——————-+———-+——-+
| 265 | KHQ0001 | 00:12:CF:FE:B1:7D | 12 | NULL |
| 266 | KHQ0002 | 00:12:CF:FE:AD:70 | 12 | NULL |
| 48 | KHQ0003 | 00:12:CF:FE:AE:E7 | 12 | NULL |
| 23 | KHQ0004 | 00:12:CF:FE:AC:97 | 12 | NULL |
| 316 | KHQ0887 | 00:12:CF:C5:47:45 | 12 | NULL |
+—–+———+——————-+———-+——-+
5 rows in set (0.00 sec)

Now, if I add a new fictitious row via the mysql cli, all works as expected:

Code:

mysql> insert into sites set site=”KHQ0005″, mac=”00:00:00:00:00:00″, marketId=12;
Query OK, 1 row affected (0.00 sec)

mysql> select id,site,mac,marketId,notes from sites where site like 'KHQ%';
+—–+———+——————-+———-+——-+
| id | site | mac | marketId | notes |
+—–+———+——————-+———-+——-+
| 265 | KHQ0001 | 00:12:CF:FE:B1:7D | 12 | NULL |
| 266 | KHQ0002 | 00:12:CF:FE:AD:70 | 12 | NULL |
| 48 | KHQ0003 | 00:12:CF:FE:AE:E7 | 12 | NULL |
| 23 | KHQ0004 | 00:12:CF:FE:AC:97 | 12 | NULL |
| 317 | KHQ0005 | 00:00:00:00:00:00 | 12 | NULL |
| 316 | KHQ0887 | 00:12:CF:C5:47:45 | 12 | NULL |
+—–+———+——————-+———-+——-+
6 rows in set (0.00 sec)

But, if I now add another new fictitious row via SQLyog, there is an extra r inserted:

Code:

mysql> select id,site,mac,marketId,notes from sites where site like 'KHQ%';
+—–+———–+——————-+———-+——-+
| id | site | mac | marketId | notes |
+—–+———–+——————-+———-+——-+
| 265 | KHQ0001 | 00:12:CF:FE:B1:7D | 12 | NULL |
| 266 | KHQ0002 | 00:12:CF:FE:AD:70 | 12 | NULL |
| 48 | KHQ0003 | 00:12:CF:FE:AE:E7 | 12 | NULL |
| 23 | KHQ0004 | 00:12:CF:FE:AC:97 | 12 | NULL |
| 317 | KHQ0005 | 00:00:00:00:00:00 | 12 | NULL |
| 318 | KHQ0006
| 00:00:00:00:00:01 | 12 | NULL |
| 316 | KHQ0887 | 00:12:CF:C5:47:45 | 12 | NULL |
+—–+———–+——————-+———-+——-+
7 rows in set (0.00 sec)

One thing that I didn't mention is that I'm getting the site ID from an Excel spreadsheet, so I'm copying and pasting from a single spreadsheet cell into the SQLyog field. So, Excel may be the source of the r.

I do not see the extra r if I type the site ID in manually.

But, SQLyog doesn't display an extra character during entry or when I run the query again. If SQLyog was able to display the r character and allow me to edit/remove it, it would save a LOT of time going back in and manually editing the fields via the mysql cli.

I'll upgrade to 8.8 beta 1 Community shortly and let you know if I see the same issue.

Thanks!

-Rich

[N3WWN]

Oh, Aparna, where is 8.8 Beta1 community?

I found the announcement here: http://www.webyog.com/blog/2011/01/04/sqlyog-mysql-gui-8-8-beta-1-released/

But the download page, which is linked from the announcement page, only has 8.8 beta 0:

SQLyog Installers

* Stable 8.7.1-0 Trial (30-day Trial. Contains all features of Professional, Enterprise and Ultimate)

* Stable 8.7.1-0 Full (Requires customer login)

* 8.8.0-0 (Beta) Trial (30-day Trial. Contains all features of Professional, Enterprise and Ultimate)

* 8.8.0-0 (Beta) Full (Requires customer login)

Plus, I'm not sure if these are the community editions as I think your website layout (or at least the descriptions) have changed since I last downloaded SQLyog in July.

Thanks and sorry for my ignorance on where to get 8.8 Beta1 community…

-Rich

[Aparna]

Hi Rich,

Quote:

One thing that I didn't mention is that I'm getting the site ID from an Excel spreadsheet, so I'm copying and pasting from a single spreadsheet cell into the SQLyog field. So, Excel may be the source of the r.

You are right. Excel is the source of r. When you copy paste a single cell value, r gets appended to the data. As a work around for this you can copy only the data with in the cell (instead of copying the entire cell) in order to avoid the r getting appended.

Quote:

If SQLyog was able to display the r character and allow me to edit/remove it, it would save a LOT of time going back in and manually editing the fields via the mysql cli.

Unlike a text/blob field which has multi line edit control, Varchar has a single line edit control because of which characters like r,n(non printable characters) are not visible.

The download link for the latest Beta is: http://code.google.com/p/sqlyog/downloads/list

You are definitely welcome to upgrade to the latest version but you will still see the same behavior in the latest version also.

Thanks and Regards,

Aparna.

[N3WWN]

Hi Aparna!

Thank you for the reply and sorry for the delay in my response.

'Aparna' wrote:

You are right. Excel is the source of r. When you copy paste a single cell value, r gets appended to the data. As a work around for this you can copy only the data with in the cell (instead of copying the entire cell) in order to avoid the r getting appended.

Unlike a text/blob field which has multi line edit control, Varchar has a single line edit control because of which characters like r,n(non printable characters) are not visible.

Since the varchar can contain non-printable characters, wouldn't it make sense to display them? They could be shown in hex, as a box the size of the font character, etc.

I've also seen a similar problem with the multi-line editor… the application that we're running which utilizes the MySQL database does not expect characters like r in any of the fields. When I edit a text/blob field with the SQLyog multi-line editor, DOS/Windows-style rn end of lines are included, but not visible.

'Aparna' wrote:

The download link for the latest Beta is: http://code.google.com/p/sqlyog/downloads/list

You are definitely welcome to upgrade to the latest version but you will still see the same behavior in the latest version also.

Thanks and Regards,

Aparna.

Thanks! I'm updating now to make sure the other problems that I'm encountering (no reconnect without disconnect, queries hang on lost connection, etc) aren't resolved before I post questions about them.

-Rich

[Knudsen]

'N3WWN' wrote:

Hi Aparna!

Thank you for the reply and sorry for the delay in my response.

Since the varchar can contain non-printable characters, wouldn't it make sense to display them? They could be shown in hex, as a box the size of the font character, etc.

I've also seen a similar problem with the multi-line editor… the application that we're running which utilizes the MySQL database does not expect characters like r in any of the fields. When I edit a text/blob field with the SQLyog multi-line editor, DOS/Windows-style rn end of lines are included, but not visible.

Thanks! I'm updating now to make sure the other problems that I'm encountering (no reconnect without disconnect, queries hang on lost connection, etc) aren't resolved before I post questions about them.

-Rich

[Author]

Posts