Ascii Symbols Shown In Datetime Column Values Of Mysql

[Renuka]

Some rows in my database are shown in binary format.

DateTime values are shown as below in Sqlyog.

 

DC1203-68-58 51:18:72

Æ420-94-57 38:13:76

722-49-30 63:17:35

ETB801-61-42 37:69:60

 

When i retrieved these from UI, displayed correctly as

2014-08-19 21:44:17.0

2014-08-19 21:44:17.0

 

and also some varchar columns are displayed as (Binary/Image).

when i Export to Excel i found the values like

í1

 

But in UI displayed normally as ‘OID-231-9k48wknuyc9376rj’

 

Can any one please please tell me what i need to do to make these values to display in normal format?

what made these values to display like this?

 

Thanks in Advance.

[Abhishek Kumar Pandey]

Hi,

Please provide following details–

• Your MySQL version , execute Select Version();
• Your SQLyog version.
• Create table statement of above mentioned table. Execute Show create table .
  

And also you are referring to UI, what is UI here? Is this SQLyog or any other GUI client for MySQL?

[peterlaursen]

Also please provide a dump of a table where it is reproducible. If you don’t want to post it here you can mail it to [email protected]. The dump must be UTF8-encoded. Use mysqldump or  SQLyog to create the dump. 

 

I believe the application does some client side operation when reading and writing from the database.  SQLyog displays what is stored in the database. It cannot know if some application has enrypted/obfuscated or serialized data for instance.

[Renuka]

Thank you very much for your replies

 

Mysql Version

---

> 5.5.16-log

SqlYog  

---

>1.0.0.1

UI—>its a UI with jsp’s

used Hibernate3 in application.

 

Application doesn’t contain any specific client side operation for read/write data from db.

This problem is only for the few rows and it doesn’t have any encrypted data.

 

CREATE TABLE `CUSTOMERORDER` (

  `Pk` varchar(32) NOT NULL,

  `PurchCustomerPk` varchar(32) DEFAULT NULL,

  `RegCustomerPk` varchar(32) DEFAULT NULL,

  `SalesRep` varchar(32) NOT NULL DEFAULT ‘Unknown’ COMMENT ‘OSS System Name Of Sales Rep Placing The Order’,

  `UpdateRep` varchar(32) DEFAULT NULL COMMENT ‘Rep That Last Updated The Record’,

  `OfferingPk` bigint(20) unsigned DEFAULT NULL COMMENT ‘Reference To The Purchased Offer’,

  `BundleName` varchar(64) DEFAULT NULL COMMENT ‘Reference To The Bundle’,

  `PaymentMethodPk` varchar(32) NOT NULL,

  `Currency` varchar(3) NOT NULL DEFAULT ‘USD’ COMMENT ‘Order Currency’,

  `CustomerAddressBillPk` varchar(32) NOT NULL COMMENT ‘Reference To Customer Billing Address’,

  `CustomerAddressSvcPk` varchar(32) DEFAULT NULL COMMENT ‘Reference To Customer Service Address’,

  `CustomerAddressShipPk` varchar(32) NOT NULL COMMENT ‘Reference To Customer Shipping Address’,

  `PGID` int(10) unsigned NOT NULL COMMENT ‘Processing GroupID’,

  `OrderID` varchar(32) NOT NULL,

  `Created` datetime NOT NULL,

  `Updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  `ProcessOn` datetime NOT NULL DEFAULT ‘1900-01-01 00:00:00’ COMMENT ‘Date/Time After Which The Order Can Be Processed’,

  `Status` enum(‘NEW’,’REJECT’,’PAY’,’PAID’,’FAILPAY’,’PARTPAID’,’DELAYPROV’,’READYIN’,’READYOUT’,’PROV’,’PROVPART’,’PROVFAIL’,’INSERV’,’OUTAGE’,’SUSPEND’,’TERM’,’DELAYPAY’,’DELAYFUL’) NOT NULL DEFAULT ‘NEW’ COMMENT ‘Order Status Indicator (ENUM)’,

  PRIMARY KEY (`Pk`),

  UNIQUE KEY `OrderID` (`OrderID`),

  KEY `Indx_CORD_Referal` (`PGID`),

  KEY `FK_CO_REF` (`RegCustomerPk`),

  KEY `FK_CO_SVCADDR` (`CustomerAddressSvcPk`),

  KEY `FK_CO_BILLADDR` (`CustomerAddressBillPk`),

  KEY `FK_CO_SHIPADDR` (`CustomerAddressShipPk`),

  KEY `Indx_CO_PGID` (`PGID`),

  KEY `Indx_CO_ICID` (`PurchCustomerPk`),

  KEY `FK_CO_OFFER` (`OfferingPk`),

  KEY `FK_CO_BUND` (`BundleName`) USING BTREE,

  KEY `OStatus` (`Status`),

  CONSTRAINT `FK_CO_BILLADDR` FOREIGN KEY (`CustomerAddressBillPk`) REFERENCES `CUSTOMERADDRESS` (`Pk`),

  CONSTRAINT `FK_CO_BUNDLE` FOREIGN KEY (`BundleName`) REFERENCES `BUNDLE` (`Name`) ON UPDATE CASCADE,

  CONSTRAINT `FK_CO_OFFER` FOREIGN KEY (`OfferingPk`) REFERENCES `OFFERING` (`Pk`),

  CONSTRAINT `FK_CO_SHIPADDR` FOREIGN KEY (`CustomerAddressShipPk`) REFERENCES `CUSTOMERADDRESS` (`Pk`),

  CONSTRAINT `FK_CO_SVCADDR` FOREIGN KEY (`CustomerAddressSvcPk`) REFERENCES `CUSTOMERADDRESS` (`Pk`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT=’Table For Customer Orders Placed For Products’

[Renuka]

Attached screen shot of the yog

[Kartik Grover]

Hi

 

1) We are sorry but we are unable to understand the version of SQLyog that you are using.

2) There seems to be some problem with the upload of the attached screenshot of SQLyog, So please send it again.

 

Please provide the above information along with the dump file.

 

I tried to run the code given by you on version 12.07 and could not reproduce the error. I inserted some random values to check the export but there seems to be no problem with it. So any more information relevant to the issue would be a great help. 

 

 

Regards 

 

[peterlaursen]

To determine the SQLyog version go to menu .. help .. about.

[peterlaursen]

MySQL 5.5.16 is a pretty old server.  Refer http://dev.mysql.com/doc/relnotes/mysql/5.5/en/. There may be a server bug involved. or a JAVA connector bug. Do you know the version of the JAVA connector?

 

Could you try with another client – such as ‘mysql’ commandline client?

[Author]

Posts