Mysql groups table corrupted, other table problems ?



  • Hello

    I found several machine groups empty, with no image associated.
    So i try to check mysql table and I get an error on the table ‘groups’.

    mysql -u root -p -D fog -e "select * from groups"
    Enter password: 
    ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'groups' at line 1
    

    I check all the other tables, with no error.

    mysqlcheck -u root -p fog --verbose --check
    mysqlcheck -u root -p fog --verbose --analyze
    mysqlcheck -u root -p fog --verbose --optimize
    

    give me no error.

    mysqldump -uroot -p --allow-keywords -x -v fog > fogbackup200619.sql
    Enter password: 
    -- Connecting to localhost...
    -- Retrieving table structure for table clientUpdates...
    -- Sending SELECT query...
    -- Retrieving rows...
    -- Retrieving table structure for table dirCleaner...
    -- Sending SELECT query...
    -- Retrieving rows...
    -- Retrieving table structure for table globalSettings...
    -- Sending SELECT query...
    -- Retrieving rows...
    -- Retrieving table structure for table greenFog...
    -- Sending SELECT query...
    -- Retrieving rows...
    -- Retrieving table structure for table groupMembers...
    -- Sending SELECT query...
    -- Retrieving rows...
    -- Retrieving table structure for table groups...
    -- Sending SELECT query...
    -- Retrieving rows...
    -- Retrieving table structure for table history...
    -- Sending SELECT query...
    -- Retrieving rows...
    -- Retrieving table structure for table hookEvents...
    -- Sending SELECT query...
    -- Retrieving rows...
    -- Retrieving table structure for table hostAutoLogOut...
    -- Sending SELECT query...
    -- Retrieving rows...
    -- Retrieving table structure for table hostMAC...
    -- Sending SELECT query...
    -- Retrieving rows...
    -- Retrieving table structure for table hostScreenSettings...
    -- Sending SELECT query...
    -- Retrieving rows...
    -- Retrieving table structure for table hosts...
    -- Sending SELECT query...
    -- Retrieving rows...
    -- Retrieving table structure for table imageGroupAssoc...
    -- Sending SELECT query...
    -- Retrieving rows...
    -- Retrieving table structure for table imagePartitionTypes...
    -- Sending SELECT query...
    -- Retrieving rows...
    -- Retrieving table structure for table imageTypes...
    -- Sending SELECT query...
    -- Retrieving rows...
    -- Retrieving table structure for table images...
    -- Sending SELECT query...
    -- Retrieving rows...
    -- Retrieving table structure for table imagingLog...
    -- Sending SELECT query...
    -- Retrieving rows...
    -- Retrieving table structure for table inventory...
    -- Sending SELECT query...
    -- Retrieving rows...
    -- Retrieving table structure for table ipxeTable...
    -- Sending SELECT query...
    -- Retrieving rows...
    -- Retrieving table structure for table keySequence...
    -- Sending SELECT query...
    -- Retrieving rows...
    -- Retrieving table structure for table moduleStatusByHost...
    -- Sending SELECT query...
    -- Retrieving rows...
    -- Retrieving table structure for table modules...
    -- Sending SELECT query...
    -- Retrieving rows...
    -- Retrieving table structure for table multicastSessions...
    -- Sending SELECT query...
    -- Retrieving rows...
    -- Retrieving table structure for table multicastSessionsAssoc...
    -- Sending SELECT query...
    -- Retrieving rows...
    -- Retrieving table structure for table nfsFailures...
    -- Sending SELECT query...
    -- Retrieving rows...
    -- Retrieving table structure for table nfsGroupMembers...
    -- Sending SELECT query...
    -- Retrieving rows...
    -- Retrieving table structure for table nfsGroups...
    -- Sending SELECT query...
    -- Retrieving rows...
    -- Retrieving table structure for table notifyEvents...
    -- Sending SELECT query...
    -- Retrieving rows...
    -- Retrieving table structure for table os...
    -- Sending SELECT query...
    -- Retrieving rows...
    -- Retrieving table structure for table oui...
    -- Sending SELECT query...
    -- Retrieving rows...
    -- Retrieving table structure for table plugins...
    -- Sending SELECT query...
    -- Retrieving rows...
    -- Retrieving table structure for table powerManagement...
    -- Sending SELECT query...
    -- Retrieving rows...
    -- Retrieving table structure for table printerAssoc...
    -- Sending SELECT query...
    -- Retrieving rows...
    -- Retrieving table structure for table printers...
    -- Sending SELECT query...
    -- Retrieving rows...
    -- Retrieving table structure for table pxeMenu...
    -- Sending SELECT query...
    -- Retrieving rows...
    -- Retrieving table structure for table scheduledTasks...
    -- Sending SELECT query...
    -- Retrieving rows...
    -- Retrieving table structure for table schemaVersion...
    -- Sending SELECT query...
    -- Retrieving rows...
    -- Retrieving table structure for table snapinAssoc...
    -- Sending SELECT query...
    -- Retrieving rows...
    -- Retrieving table structure for table snapinGroupAssoc...
    -- Sending SELECT query...
    -- Retrieving rows...
    -- Retrieving table structure for table snapinJobs...
    -- Sending SELECT query...
    -- Retrieving rows...
    -- Retrieving table structure for table snapinTasks...
    -- Sending SELECT query...
    -- Retrieving rows...
    -- Retrieving table structure for table snapins...
    -- Sending SELECT query...
    -- Retrieving rows...
    -- Retrieving table structure for table supportedOS...
    -- Sending SELECT query...
    -- Retrieving rows...
    -- Retrieving table structure for table taskLog...
    -- Sending SELECT query...
    -- Retrieving rows...
    -- Retrieving table structure for table taskStates...
    -- Sending SELECT query...
    -- Retrieving rows...
    -- Retrieving table structure for table taskTypes...
    -- Sending SELECT query...
    -- Retrieving rows...
    -- Retrieving table structure for table tasks...
    -- Sending SELECT query...
    -- Retrieving rows...
    -- Retrieving table structure for table userCleanup...
    -- Sending SELECT query...
    -- Retrieving rows...
    -- Retrieving table structure for table userTracking...
    -- Sending SELECT query...
    -- Retrieving rows...
    -- Retrieving table structure for table users...
    -- Sending SELECT query...
    -- Retrieving rows...
    -- Retrieving table structure for table virus...
    -- Sending SELECT query...
    -- Retrieving rows...
    -- Disconnecting from localhost...
    
    

    the backup file obtenaied seems ok:

    --
    -- Table structure for table `groups`
    --
    
    DROP TABLE IF EXISTS `groups`;
    /*!40101 SET @saved_cs_client     = @@character_set_client */;
    /*!50503 SET character_set_client = utf8mb4 */;
    CREATE TABLE `groups` (
      `groupID` int(11) NOT NULL AUTO_INCREMENT,
      `groupName` varchar(50) NOT NULL,
      `groupDesc` longtext NOT NULL,
      `groupDateTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
      `groupCreateBy` varchar(50) NOT NULL,
      `groupBuilding` int(11) NOT NULL,
      `groupKernel` varchar(255) NOT NULL,
      `groupKernelArgs` varchar(255) NOT NULL,
      `groupPrimaryDisk` varchar(255) NOT NULL,
      PRIMARY KEY (`groupID`),
      UNIQUE KEY `groupName` (`groupName`),
      UNIQUE KEY `groupName_2` (`groupName`),
      KEY `new_index` (`groupName`)
    ) ENGINE=MyISAM AUTO_INCREMENT=64 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
    /*!40101 SET character_set_client = @saved_cs_client */;
    
    --
    -- Dumping data for table `groups`
    --
    
    LOCK TABLES `groups` WRITE;
    /*!40000 ALTER TABLE `groups` DISABLE KEYS */;
    INSERT INTO `groups` VALUES (1,'0-linux','Tous les postes Linux','2017-03-23 14:59:41','fog',0,'','',''),(2,'atelier','','2009-12-09 15:01:14','fog',0,'','',''),(3,'atelier-lin','Postes Linux','2017-03-20 18:41:01','fog',0,'','',''),(4,'b05-929','Bat 5 salle 
    929','2011-06-07 15:12:16','fog',0,'','',''),(5,'b06-002','','2012-06-05 21:15:27','fog',0,'','',''),(6,'b06-008','','2012-06-05 21:03:55','fog',0,'','',''),(7,'b06-013','','2019-09-03 18:28:15','fog',0,'','',''),(8,'b06-013-1','','2012-06-05 15:51:56','fog',
    0,'','',''),(9,'b06-013-2','','2012-06-05 20:29:32','fog',0,'','',''),(10,'b06-016','','2012-06-05 21:22:58','fog',0,'','',''),(11,'b06-019','','2014-07-08 15:32:00','fog',0,'','',''),(12,'b06-019-1','','2012-06-05 21:03:30','fog',0,'','',''),(13,'b06-019-2',
    '','2011-06-07 15:10:23','fog',0,'','',''),(14,'b06-937','','2011-06-07 15:11:49','fog',0,'','',''),(15,'b06-938','','2014-01-10 14:42:47','fog',0,'','',''),(16,'b06-HP5800','','2014-07-07 20:06:44','fog',0,'','',''),(17,'b06-SansImages','','2012-06-05 21:31:
    21','fog',0,'','',''),(18,'b41','','2018-08-29 12:51:23','fog',0,'','',''),(19,'b41-101','','2011-06-06 16:20:05','fog',0,'','',''),(20,'b41-104','Dell 7440 ESIR','2012-07-12 17:29:08','fog',0,'','',''),(21,'b41-conf','','2018-06-07 21:08:26','fog',0,'','',''
    ),(22,'b41-pr','','2011-10-04 21:36:34','fog',0,'','',''),(23,'batO-windows','','2019-02-06 17:05:14','fog',0,'','',''),(24,'conf','','2017-03-28 21:02:42','fog',0,'','',''),(25,'conf_autres','','2010-05-11 13:04:03','fog',0,'','',''),(26,'conf_i05','','2017-
    08-29 20:19:44','fog',0,'','',''),(27,'d020','','2010-04-26 13:31:43','fog',0,'','',''),(28,'d022','','2010-04-26 13:32:13','fog',0,'','',''),(29,'d024','','2010-04-26 13:32:37','fog',0,'','',''),(30,'d026','','2010-04-26 13:33:01','fog',0,'','',''),(31,'d028
    ','','2010-04-26 13:33:23','fog',0,'','',''),(32,'d122','','2010-04-26 13:33:47','fog',0,'','',''),(33,'deployb06','','2017-05-04 14:10:44','fog',0,'','',''),(34,'deployb06-bis','','2017-05-04 19:24:23','fog',0,'','',''),(35,'e003','','2010-04-22 18:38:46','f
    og',0,'','',''),(36,'e005','','2010-02-22 14:55:42','fog',0,'','',''),(37,'e007','','2010-04-22 19:38:59','fog',0,'','',''),(38,'e008','','2010-04-22 19:39:22','fog',0,'','',''),(39,'e010','','2010-04-21 18:06:43','fog',0,'','',''),(40,'e103','','2010-04-22 1
    9:42:24','fog',0,'','',''),(41,'e105','','2010-04-22 19:42:43','fog',0,'','',''),(42,'e107','','2010-04-22 19:43:04','fog',0,'','',''),(43,'e206','','2010-04-22 19:43:25','fog',0,'','',''),(44,'e212','','2010-04-22 19:43:54','fog',0,'','',''),(45,'e214','','2
    010-04-22 19:44:12','fog',0,'','',''),(46,'esir','salles TP b41-101 b41-102 b41-103 b41-104','2017-08-21 15:19:15','fog',0,'','',''),(47,'i054','','2010-04-01 14:02:09','fog',0,'','',''),(48,'i055','','2017-09-01 18:14:10','fog',0,'','',''),(49,'i203','','201
    0-05-04 14:51:49','fog',0,'','',''),(50,'i204','','2018-01-17 14:26:01','fog',0,'','',''),(51,'i206','','2014-07-07 21:14:52','fog',0,'','',''),(52,'i207','','2011-06-16 19:11:42','fog',0,'','',''),(53,'i207clients','','2011-07-06 20:31:58','fog',0,'','',''),
    (54,'i207serveurs','','2011-07-06 20:31:20','fog',0,'','',''),(55,'i214','','2010-05-20 19:56:52','fog',0,'','',''),(56,'miage','','2015-04-28 19:39:20','fog',0,'','',''),(57,'parc_peda_windows','ensemble des machines peda sous windows','2017-03-30 15:33:00',
    'fog',0,'','',''),(58,'sec-esir','','2019-04-12 15:32:34','fog',0,'','',''),(59,'secretaires','','2014-04-22 16:20:31','fog',0,'','',''),(61,'temporaire','','2017-04-28 18:25:08','fog',0,'','',''),(62,'b06','','2019-11-26 13:03:18','fog',0,'','',''),(63,'Exti
    nction-Week-end','E107, E212, E214, I54, I55 B41-104, B41-101 pour extinction','2020-01-17 14:07:59','fog',0,'','','');
    /*!40000 ALTER TABLE `groups` ENABLE KEYS */;
    UNLOCK TABLES;
    

    Could you advise on what to do ?



  • @Tom-Elliott
    Ok, thank you for you answer.


  • Senior Developer

    So, change your query from:

    mysql -u root -p -D fog -e "select * from groups"
    

    To:

    mysql -u root -p -D fog -e "select * from `groups`"
    

    mysql has made groups a keyword now, so you will need to escape the word when referencing it in the future.



  • I drop the database and restore it from dump file, I get the same error.


Log in to reply
 

218
Online

7.2k
Users

14.4k
Topics

135.6k
Posts