Invision Community 4: SEO, prepare for v5 and dormant account notifications Matt November 11, 2024Nov 11
Posted June 19, 20231 yr 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, 20231 yr by NoSpy
June 19, 20231 yr This is something you would contact your hosting company on if you are unsure how to switch over table types
June 19, 20231 yr Author 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, 20231 yr by NoSpy
June 19, 20231 yr 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
June 20, 20231 yr Author 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, 20231 yr by NoSpy
June 20, 20231 yr This post was recognized by Marc! 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, 20231 yr by teraßyte
June 20, 20231 yr This post was recognized by Marc! 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?! 🙂
June 21, 20231 yr This post was recognized by Marc! 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
June 21, 20231 yr Author 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, 20231 yr by NoSpy
June 21, 20231 yr 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.
June 21, 20231 yr Author 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, 20231 yr by NoSpy
June 21, 20231 yr 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.
June 21, 20231 yr Author 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.
June 21, 20231 yr 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
June 21, 20231 yr Author 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?
June 21, 20231 yr 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
June 21, 20231 yr 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, 20231 yr by teraßyte
