I accidentally dropped my schema and had to recover all the tables per the steps in this post; Recover schema and the data in it from accidentally deleted schema

However, I notice that all of the tables are now using latin1 encoding. From memory I thought they were utf8. On one table, and possibly more, I haven't finished checking all tables; some of the columns are now double spaced. They contain a non-printable character, the first byte of the string, a non-printable character, the second byte and so on. I don't know if it's related to the character encoding or something I missed or did wrong when I recovered the table.

Example 1: Viewed from the application Example 2: Snippet as viewed from workbench

I could write some code to simply repair the data (probably not the correct approach). Is there another way to fix this issue?

This is the CREATE table statement;

CREATE TABLE `refadmin`.`games` (
  `idGames` int(11) NOT NULL AUTO_INCREMENT,
  `gDow` tinyint(4) NOT NULL,
  `gDate` date NOT NULL comment 'YYYY-MM-DD',
  `gSession` tinyint(4) NOT NULL comment 'Look up for sessions table',
  `gVenue` tinyint(4) NOT NULL comment 'Link to venues table',
  `gGender` varchar(2) NOT NULL comment 'M, F, B, G',
  `gType` varchar(6) NOT NULL comment 'RS: Regular season \nGR: Grading\nSF: Semi Final\nPF: Preliminary
 Final\nGF: Grand final\n',
  `gSuper` int(11) NOT NULL comment 'Who is the supervisor on this session',
  `gOutcomes` varchar(3) DEFAULT NULL comment 'P=Punitive (taken off me for punishment of some kind): S=
Sacrified: I found a repalcement (Eg illness); Tech, Unsportsmanlike, disqualified, reported, cancelled',
  `gInjury` tinyint(4) DEFAULT NULL comment '0=No, 1=Yes',
  `gEval` varchar(6) DEFAULT NULL comment 'IF: Informal (major feedback)\nFE: Formal evaluation recorded
 on Referee Manager\nGR: Regraded (up or down)\nNF: Negative feedback from Supervisor\nPF: Positive feedback from Supervisor',
  `gRosterer` int(11) NOT NULL comment 'Who rostered the games',
  `gMyGrade` varchar(12) DEFAULT NULL comment 'Lookup to grades table',
  `gChgGame` tinyint(4) DEFAULT NULL comment 'Was this a changed game? 0=No, 1=yes',
  `gMyPerf` tinyint(4) NOT NULL comment 'My expected performance: 0=Unmeasured, 1=Trainee Level, 2=C Grade, 3-4=B Grade, 5-6 AR
grade, 7=A grade',
  `gComment` varchar(1536) DEFAULT NULL,
  `Spare` varchar(135) DEFAULT NULL,
  `gDateAdded` date DEFAULT NULL,
  `gComp` varchar(4) DEFAULT NULL comment 'JD, SD, AS, TO, VJ, CH, etc',
  `Trainee` varchar(2) DEFAULT NULL comment 'Trainee Name\\\\n(Since they are not yet on ref manager)\\\\n',
  `court` tinyint(4) DEFAULT NULL,
  `partner1` int(11) DEFAULT NULL,
  `partner2` int(11) DEFAULT NULL,
  `p1perf` tinyint(4) DEFAULT NULL,
  `p2perf` tinyint(4) DEFAULT NULL,
  `grade` varchar(6) DEFAULT NULL,
  `age` tinyint(4) DEFAULT NULL,
  `techs` tinyint(4) DEFAULT NULL comment 'Game Techs',
  `Unsps` tinyint(4) DEFAULT NULL,
  `disq` tinyint(4) DEFAULT NULL,
  `reports` tinyint(4) DEFAULT NULL,
  `walkover` tinyint(4) DEFAULT NULL comment 'Which game(s) were walkovers?\n',
  `Feedback` varchar(765) DEFAULT NULL comment 'Feedback about my performance 1=Very negative, 3=None, 5
-Very Good',
  `origage` tinyint(4) DEFAULT NULL,
  `origcourt` tinyint(4) DEFAULT NULL,
  `origgrade` varchar(6) DEFAULT NULL,
  `difficulty` tinyint(4) DEFAULT NULL,
  `refsup` tinyint(4) NOT NULL,
  `slot` tinyint(4) DEFAULT NULL,
  `gpoints` tinyint(4) DEFAULT NULL comment 'Grade pints H=1 A=15',
  `paid` tinyint(4) DEFAULT NULL comment 'Has I been paid for this game?',
  `attachment` int(11) DEFAULT NULL,
  `sessrec` int(11) DEFAULT NULL comment 'This is the record number on the sessions table that these games belong to. This is re
quired to create a link betwee this table and other so that payments can be processed.',
  `tranid` varchar(90) DEFAULT NULL,
  `gsex` varchar(2) DEFAULT NULL,
  `origpart` int(11) NOT NULL,
  `tname` varchar(90) DEFAULT NULL comment 'Trainee name',
  `negevt` tinyint(4) DEFAULT NULL,
  `netext` varchar(1024) DEFAULT NULL,
  `datepaid` date DEFAULT NULL,
  `gamenum` int(11) DEFAULT NULL comment '230422: This is used to track games as using idgames requires the continual sorting an
d realignment of many tables in the system. Where idgames is used in a script, it must be changed to use gamenum',
UNIQUE KEY `idGames_UNIQUE` (`idGames`)
) ENGINE=InnoDB COMMENT 'Contains details of all games refereed';

The varchar fields are all 2x - 3x their original definition.

  • The length of varchar() columns on all tables were anywhere up to 8 times their original values. Typically 3x. All tables were affected. Only the games table had the corrupted data. I wrote some code to fix it all. The schema appears to have changed from utf8 to latin1, which I have fixed and gone through and changed all tables to utf8. I don't know why the games table had corrupted data or why everything changed to latin1 and I haven't found a way to resolve the problems or why columns were expanded. After repairing the data and checking things everything appears to be fine now. Commented Apr 28, 2022 at 3:45


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.