NoSpy Posted June 19, 2023 Share Posted June 19, 2023 (edited) Hello, conf_global.php $INFO['mysql_tbl_type'] = 'MyISAM'; I have removed this ligne but, how change type MyISAM to InnoDB when are mixed in my SQL base ? Edited June 19, 2023 by NoSpy Link to comment Share on other sites More sharing options...
Marc Stridgen Posted June 19, 2023 Share Posted June 19, 2023 This is something you would contact your hosting company on if you are unsure how to switch over table types G17 Media 1 Link to comment Share on other sites More sharing options...
NoSpy Posted June 19, 2023 Author Share Posted June 19, 2023 (edited) I've already contacted my WebHost and he says it's not his problem, it supports both if there's a mixture in the SQL database it's a programming error by IPS. Edited June 19, 2023 by NoSpy Link to comment Share on other sites More sharing options...
Marc Stridgen Posted June 19, 2023 Share Posted June 19, 2023 Sorry to hear you are being told this. I'm not sure where it is they get that impression, as we create tables and they will be created on the server with whatever the default is for that servers MySQL setup. Just taking a quick search there, this looks like it may help https://stackoverflow.com/questions/3856435/how-to-convert-all-tables-from-myisam-into-innodb NoSpy and G17 Media 2 Link to comment Share on other sites More sharing options...
NoSpy Posted June 20, 2023 Author Share Posted June 20, 2023 (edited) After modifying each table individually, I have few table refuse changing MyISAM to InnoDB with error like this : The list of SQL tables that generate an error https://files.nospy.ch/BUG/IPS/2023-0620/MyISAM-01-InnoDB.jpg How fix that ? Edited June 20, 2023 by NoSpy Link to comment Share on other sites More sharing options...
Marc Stridgen Posted June 20, 2023 Share Posted June 20, 2023 Have you checked to ensure your tables are not using compact row format? Link to comment Share on other sites More sharing options...
teraßyte Posted June 20, 2023 Share Posted June 20, 2023 (edited) This post was recognized by Marc Stridgen! teraßyte was awarded the badge 'Helpful' and 5 points. Try using this kind of query which sets the table to use the DYNAMIC row format by default instead: ALTER TABLE nexus_licensekeys ENGINE=InnoDB ROW_FORMAT=DYNAMIC; That is, as long as your host has it enabled. I've seen plenty of hosting with only the COMPACT option available, unfortunately. Edited June 20, 2023 by teraßyte Marc Stridgen, SeNioR- and TDBF 3 Link to comment Share on other sites More sharing options...
TDBF Posted June 20, 2023 Share Posted June 20, 2023 This post was recognized by Marc Stridgen! TDBF was awarded the badge 'Helpful' and 5 points. On 6/19/2023 at 9:59 AM, NoSpy said: I've already contacted my WebHost and he says it's not his problem, it supports both if there's a mixture in the SQL database it's a programming error by IPS. You have to do this yourself, and it is quite easy to do once you use the right tools. If you don't want to use SHH and MySQL commands, then I would suggest a tool like HeidiSQL. You can easily convert your database tables from MyISAM to InnoDB using the 'Bulk Table Editor' within the Table Tools. If you do not want to download more software, you can use phpMyAdmin (You will most likely find this in your CPANEL account or something similar) and run an alter command like this: 'ALTER TABLE table_name ENGINE=InnoDB;' Hope this helps?! 🙂 NoSpy, Marius and Marc Stridgen 3 Link to comment Share on other sites More sharing options...
nodle Posted June 21, 2023 Share Posted June 21, 2023 This post was recognized by Marc Stridgen! nodle was awarded the badge 'Helpful' and 5 points. I'm not trying to plug my own site, but I made this post awhile back so I could always remember how to do it. This has always worked for me. Converting MYISAM database tables to INNODB - Tech Zone - Fluxoid Marc Stridgen and TDBF 2 Link to comment Share on other sites More sharing options...
NoSpy Posted June 21, 2023 Author Share Posted June 21, 2023 (edited) On 6/20/2023 at 9:01 AM, Marc Stridgen said: Have you checked to ensure your tables are not using compact row format? I have checked and all tables using DYNAMIC for InnoDB or MyISAM. https://files.nospy.ch/BUG/IPS/2023-0620/MyISAM-01-InnoDB.jpg https://files.nospy.ch/BUG/IPS/2023-0620/MyISAM-02-InnoDB.jpg My host by default set "InnoDB COMPACT" and "MyISAM DYNAMIC". It's possible add in global config ? $INFO['mysql_tbl_type'] = 'InnoDB'; $INFO['mysql_tbl_type'] = 'DYNAMIC'; Edited June 21, 2023 by NoSpy Link to comment Share on other sites More sharing options...
Marc Stridgen Posted June 21, 2023 Share Posted June 21, 2023 15 minutes ago, NoSpy said: I have checked and all tables using DYNAMIC for InnoDB or MyISAM. https://files.nospy.ch/BUG/IPS/2023-0620/MyISAM-01-InnoDB.jpg https://files.nospy.ch/BUG/IPS/2023-0620/MyISAM-02-InnoDB.jpg My host by default set "InnoDB COMPACT" and "MyISAM DYNAMIC". It's possible add in global config ? $INFO['mysql_tbl_type'] = 'InnoDB'; $INFO['mysql_tbl_type'] = 'DYNAMIC'; Thats not something we would add in the software no. It's a hosting related function. You would need to contact your hosting company again if you are unable to change table type unfortunately. Link to comment Share on other sites More sharing options...
NoSpy Posted June 21, 2023 Author Share Posted June 21, 2023 (edited) 17 minutes ago, Marc Stridgen said: Thats not something we would add in the software no. It's a hosting related function. You would need to contact your hosting company again if you are unable to change table type unfortunately. The host will not make any changes as demand is too low (2 clients only), we have also seen some loosening. MyISAM DYNAMIC and InnoDB COMPACT = SQL 88MB MyISAM DYNAMIC and InnoDB DYNAMIC = SQL 312MB If new table are created InnoDB COMPACT and I have change manualy to DYNAMIC https://files.nospy.ch/BUG/IPS/2023-0620/MyISAM-03-InnoDB.txt Edited June 21, 2023 by NoSpy Link to comment Share on other sites More sharing options...
Marc Stridgen Posted June 21, 2023 Share Posted June 21, 2023 I'm not sure on what else I can add here, unfortunately. If your hosting company is unable to fulfil the requirements you need for your site, I would advise on looking for new hosting. Link to comment Share on other sites More sharing options...
NoSpy Posted June 21, 2023 Author Share Posted June 21, 2023 I've checked with my other hosts, none of which offer this feature except on the dedicated server, and I don't see why you insist on it. The hosting provider fully supports DYNAMIC InnoDB and MyISAM as you can see : https://files.nospy.ch/BUG/IPS/2023-0620/MyISAM-03-InnoDB.txt The original question is that I'm unable to modify some tables MyISAM to InnoDB here's the list : https://files.nospy.ch/BUG/IPS/2023-0620/MyISAM-01-InnoDB.jpg This table MyISAM is empty or nearly so, and it's impossible to modify to InnoDB it without getting this error. Link to comment Share on other sites More sharing options...
Marc Stridgen Posted June 21, 2023 Share Posted June 21, 2023 You could attempt removing the indexes for that table, and then changing the table type. Note, this is not something supported, but something that will likely work as the indexes would be readded by the support tool if missing. You should take a full backup first and foremost There isn't much I can really offer in terms of advise on your hosting companies capabilities. We insist on it because its what the software requires to function in its proper manner Link to comment Share on other sites More sharing options...
NoSpy Posted June 21, 2023 Author Share Posted June 21, 2023 MyISAM DYNAMIC: ALTER TABLE `downloads_ccontent` InnoDB DYNAMIC: ALTER TABLE `downloads_ccontent` Edit : What do I need to delete from this table - is it empty? Link to comment Share on other sites More sharing options...
Marc Stridgen Posted June 21, 2023 Share Posted June 21, 2023 Its not data, but rather indexes. If you are unsure on how to do that, you will require assistance on your database. That is a workaround for the issue you are having there Link to comment Share on other sites More sharing options...
teraßyte Posted June 21, 2023 Share Posted June 21, 2023 (edited) On 6/20/2023 at 3:53 PM, teraßyte said: Try using this kind of query which sets the table to use the DYNAMIC row format by default instead: ALTER TABLE nexus_licensekeys ENGINE=InnoDB ROW_FORMAT=DYNAMIC; That is, as long as your host has it enabled. I've seen plenty of hosting with only the COMPACT option available, unfortunately. I mentioned the issue in my reply above, you need to manually specify the ROW_FORMAT value in the ALTER TABLE query to avoid using the COMPACT format set by default by your hosting. In your example above you're still skipping that part: ALTER TABLE downloads_ccontent ENGINE=InnoDB; instead of: ALTER TABLE downloads_ccontent ENGINE=InnoDB ROW_FORMAT=DYNAMIC; Edited June 21, 2023 by teraßyte TDBF 1 Link to comment Share on other sites More sharing options...
Recommended Posts