Home
Join
check
  • cnicholsontech wrote:

    Scott Alan Miller wrote:

    cnicholsontech wrote:

    Does the length of the comment count against the row size?

    No, that's independent.
    I'm not sure that's true. Or there is some other limit on total-comment-length somewhere else. I just tried cutting back on the sentence in one of the ones that failed, and if I make it short enough, it inserts. Lengthen it another character, and it fails. See below where I first cut the comment back to the first 7 words, then cut off one character and it inserts.
    11:57:48 ALTER TABLE `sometable` CHANGE COLUMN `somecolumn` ` somecolumn ` char(11) NOT NULL DEFAULT '' COMMENT 'This is the calculated amount of a' Error Code: 1117. Too many columns 0.032 sec

    11:57:56 ALTER TABLE `sometable` CHANGE COLUMN ` somecolumn ` ` somecolumn ` char(11) NOT NULL DEFAULT '' COMMENT 'This is the calculated amount of ' 0 row(s) affected Records: 0 Duplicates: 0 Warnings: 0 0.047 sec
    Knowing this, I might be able to go back over the previous rows and trim out enough of the comments that it still makes sense, but it all fits. All the other tables are much less "wide" than this one.
    If that's true, the error is incorrect.  If the issue is total table size, it should report that.
    Was this post helpful? thumb_up thumb_down
  • View Best Answer in replies below

    30 Replies

    • cnicholsontech wrote:

      Avg row length is 2889, so I'm well below the 65,535 character limit for the entire row as well.



      Average isn't useful, max is what we need to know.  And that is a byte limit, not a char limit.  64K bytes gives you 3:1 for UTF8 encoding so just 21,845 chars in that case.  If you are using Unicode or something, that's going to be even less that you get.  If your average is 2889, and your encoding is big, you could easily be over the limit and triggering that error from that alone.
      Spice (2) flagReport
      Was this post helpful? thumb_up thumb_down
    • Are you declaring NULLs anywhere?  That can reduce available column count.

      Spice (1) flagReport
      Was this post helpful? thumb_up thumb_down
    • http://dev.mysql.com/doc/refman/5.5/en/column-count-limit.html

      Spice (1) flagReport
      Was this post helpful? thumb_up thumb_down
    • Firstly, make sure you check not the maximum column limit of MySQL (4096), but of your table engine itself, which can be different depending on it, as well as the version of MySQL you're using with it.

      You may also want to consider partitioning your table, adding a new column is a perfect opportunity to do this, and since they're equal in rows/association, a simple join will bring them together. 

      Spice (2) flagReport
      Was this post helpful? thumb_up thumb_down
    • Scott Alan Miller wrote:

      cnicholsontech wrote:

      Avg row length is 2889, so I'm well below the 65,535 character limit for the entire row as well.



      Average isn't useful, max is what we need to know.  And that is a byte limit, not a char limit.  64K bytes gives you 3:1 for UTF8 encoding so just 21,845 chars in that case.  If you are using Unicode or something, that's going to be even less that you get.  If your average is 2889, and your encoding is big, you could easily be over the limit and triggering that error from that alone.
      No, latin1 character set. No nullable columns on this table (it feeds from an old (but still actively developed) unix-ey COBOL type application, so they mostly use blanks or zeroes). There are no VARCHARs in this table; it's all DECIMAL, DATE and CHAR, so avg should be pretty accurate. 
      Was this post helpful? thumb_up thumb_down
    • cnicholsontech wrote:

      Scott Alan Miller wrote:

      cnicholsontech wrote:

      Avg row length is 2889, so I'm well below the 65,535 character limit for the entire row as well.



      Average isn't useful, max is what we need to know.  And that is a byte limit, not a char limit.  64K bytes gives you 3:1 for UTF8 encoding so just 21,845 chars in that case.  If you are using Unicode or something, that's going to be even less that you get.  If your average is 2889, and your encoding is big, you could easily be over the limit and triggering that error from that alone.
      No, latin1 character set. No nullable columns on this table (it feeds from an old (but still actively developed) unix-ey COBOL type application, so they mostly use blanks or zeroes). There are no VARCHARs in this table; it's all DECIMAL, DATE and CHAR, so avg should be pretty accurate. 
      What engine is it?
      Was this post helpful? thumb_up thumb_down
    • Antal Daavid wrote:

      cnicholsontech wrote:

      Scott Alan Miller wrote:

      cnicholsontech wrote:

      Avg row length is 2889, so I'm well below the 65,535 character limit for the entire row as well.



      Average isn't useful, max is what we need to know.  And that is a byte limit, not a char limit.  64K bytes gives you 3:1 for UTF8 encoding so just 21,845 chars in that case.  If you are using Unicode or something, that's going to be even less that you get.  If your average is 2889, and your encoding is big, you could easily be over the limit and triggering that error from that alone.
      No, latin1 character set. No nullable columns on this table (it feeds from an old (but still actively developed) unix-ey COBOL type application, so they mostly use blanks or zeroes). There are no VARCHARs in this table; it's all DECIMAL, DATE and CHAR, so avg should be pretty accurate. 
      What engine is it?

      MyISAM on MySQL 5.5. Well below the 4096 column limit.

      Spice (1) flagReport
      Was this post helpful? thumb_up thumb_down
    • Does the length of the comment count against the row size?

      Was this post helpful? thumb_up thumb_down
    • Can you share the table schema and your alter statement? I'll create the table and try to run it and see if I get a different result or perhaps maybe can see the problem.

      Was this post helpful? thumb_up thumb_down
    • Antal Daavid wrote:

      Can you share the table schema and your alter statement? I'll create the table and try to run it and see if I get a different result or perhaps maybe can see the problem.

      Sorry, I can't. I'm under weird NDA with the vendor as far as sharing the table structures.
      Was this post helpful? thumb_up thumb_down
    • cnicholsontech wrote:

      Does the length of the comment count against the row size?

      No, that's independent.
      Was this post helpful? thumb_up thumb_down
    • cnicholsontech wrote:

      Antal Daavid wrote:

      Can you share the table schema and your alter statement? I'll create the table and try to run it and see if I get a different result or perhaps maybe can see the problem.

      Sorry, I can't. I'm under weird NDA with the vendor as far as sharing the table structures.
      then I'd use that to force the vendor to support the product :)
      Spice (2) flagReport
      Was this post helpful? thumb_up thumb_down
    • Scott Alan Miller wrote:

      cnicholsontech wrote:

      Antal Daavid wrote:

      Can you share the table schema and your alter statement? I'll create the table and try to run it and see if I get a different result or perhaps maybe can see the problem.

      Sorry, I can't. I'm under weird NDA with the vendor as far as sharing the table structures.
      then I'd use that to force the vendor to support the product :)
      The vendor product in this instance is a DB that used to run under Unix and was converted to linux (but is not Maria or Postgres or MySQL or anything that comes out of apt-get/yum), and they dump files that we pick up nightly and import to another server on MySQL for reporting purposes. Their responsibility ends at the file dump, but they do not want us sharing their layout for "competitive reasons". This is a MySQL limitation, so nothing to do with the vendor. We keep the table structure because it makes it easier to compare back to the originating application, and there's a ton of existing infrastructure that relies on the tables as they exist today; a rewrite to get comments functionality isn't feasible. I'd sooner do without comments in the table. 
      Was this post helpful? thumb_up thumb_down
    • Wowie, well best of luck to you. Without the schema there's not much we can really see, it may be something not considered. Do you have a lot of indices? Seems like a long shot, but what the hell.

      Spice (1) flagReport
      Was this post helpful? thumb_up thumb_down
    • Scott Alan Miller wrote:

      cnicholsontech wrote:

      Does the length of the comment count against the row size?

      No, that's independent.
      I'm not sure that's true. Or there is some other limit on total-comment-length somewhere else. I just tried cutting back on the sentence in one of the ones that failed, and if I make it short enough, it inserts. Lengthen it another character, and it fails. See below where I first cut the comment back to the first 7 words, then cut off one character and it inserts.
      11:57:48 ALTER TABLE `sometable` CHANGE COLUMN `somecolumn` ` somecolumn ` char(11) NOT NULL DEFAULT '' COMMENT 'This is the calculated amount of a' Error Code: 1117. Too many columns 0.032 sec

      11:57:56 ALTER TABLE `sometable` CHANGE COLUMN ` somecolumn ` ` somecolumn ` char(11) NOT NULL DEFAULT '' COMMENT 'This is the calculated amount of ' 0 row(s) affected Records: 0 Duplicates: 0 Warnings: 0 0.047 sec
      Knowing this, I might be able to go back over the previous rows and trim out enough of the comments that it still makes sense, but it all fits. All the other tables are much less "wide" than this one.
      Was this post helpful? thumb_up thumb_down
    • Antal Daavid wrote:

      Wowie, well best of luck to you. Without the schema there's not much we can really see, it may be something not considered. Do you have a lot of indices? Seems like a long shot, but what the hell.

      Nope. 5 indices, covering 5 fields. Thanks anyway.
      Was this post helpful? thumb_up thumb_down
    • cnicholsontech wrote:

      Scott Alan Miller wrote:

      cnicholsontech wrote:

      Antal Daavid wrote:

      Can you share the table schema and your alter statement? I'll create the table and try to run it and see if I get a different result or perhaps maybe can see the problem.

      Sorry, I can't. I'm under weird NDA with the vendor as far as sharing the table structures.
      then I'd use that to force the vendor to support the product :)
      The vendor product in this instance is a DB that used to run under Unix and was converted to linux (but is not Maria or Postgres or MySQL or anything that comes out of apt-get/yum), and they dump files that we pick up nightly and import to another server on MySQL for reporting purposes. Their responsibility ends at the file dump, but they do not want us sharing their layout for "competitive reasons". This is a MySQL limitation, so nothing to do with the vendor. We keep the table structure because it makes it easier to compare back to the originating application, and there's a ton of existing infrastructure that relies on the tables as they exist today; a rewrite to get comments functionality isn't feasible. I'd sooner do without comments in the table. 
      Sounds like the issue is actually a lack of schema and the "competitive reasons" is another phrasing of "embarrassment."  They don't want prospective customers finding out how badly designed and supported their products are.
      Spice (2) flagReport
      Was this post helpful? thumb_up thumb_down
    • cnicholsontech wrote:

      Scott Alan Miller wrote:

      cnicholsontech wrote:

      Does the length of the comment count against the row size?

      No, that's independent.
      I'm not sure that's true. Or there is some other limit on total-comment-length somewhere else. I just tried cutting back on the sentence in one of the ones that failed, and if I make it short enough, it inserts. Lengthen it another character, and it fails. See below where I first cut the comment back to the first 7 words, then cut off one character and it inserts.
      11:57:48 ALTER TABLE `sometable` CHANGE COLUMN `somecolumn` ` somecolumn ` char(11) NOT NULL DEFAULT '' COMMENT 'This is the calculated amount of a' Error Code: 1117. Too many columns 0.032 sec

      11:57:56 ALTER TABLE `sometable` CHANGE COLUMN ` somecolumn ` ` somecolumn ` char(11) NOT NULL DEFAULT '' COMMENT 'This is the calculated amount of ' 0 row(s) affected Records: 0 Duplicates: 0 Warnings: 0 0.047 sec
      Knowing this, I might be able to go back over the previous rows and trim out enough of the comments that it still makes sense, but it all fits. All the other tables are much less "wide" than this one.
      If that's true, the error is incorrect.  If the issue is total table size, it should report that.
      Was this post helpful? thumb_up thumb_down
    • If there isn't much schema, consider moving away from MySQL.  Their DB dump might not be very compatible with it.  Or consider changing your own schema to address this.  Using MongoDB or another database like that might do the trick for you.

      Spice (2) flagReport
      Was this post helpful? thumb_up thumb_down
    • Or Cassandra, I've had better outcomes with building reporting from Cassandra than MongoDB, and we've used both extensively and actually recently converted completely to Cassandra.

      Spice (2) flagReport
      Was this post helpful? thumb_up thumb_down
    • Cassandra is pretty nice.  I've run both.  SPent more time with MongoDB, though.  I know that a lot of people don't like it, but boy has it treated us well.

      Spice (2) flagReport
      Was this post helpful? thumb_up thumb_down
    • Scott Alan Miller wrote:

      Cassandra is pretty nice.  I've run both.  SPent more time with MongoDB, though.  I know that a lot of people don't like it, but boy has it treated us well.

      No complaints about it, other than scalability, then again our databases are huge (billions of rows across 500 databases) so depending on how much data OP has to deal with, it likely could be a negligible thing.
      Spice (1) flagReport
      Was this post helpful? thumb_up thumb_down
    • Scott Alan Miller wrote:

      cnicholsontech wrote:

      Scott Alan Miller wrote:

      cnicholsontech wrote:

      Antal Daavid wrote:

      Can you share the table schema and your alter statement? I'll create the table and try to run it and see if I get a different result or perhaps maybe can see the problem.

      Sorry, I can't. I'm under weird NDA with the vendor as far as sharing the table structures.
      then I'd use that to force the vendor to support the product :)
      The vendor product in this instance is a DB that used to run under Unix and was converted to linux (but is not Maria or Postgres or MySQL or anything that comes out of apt-get/yum), and they dump files that we pick up nightly and import to another server on MySQL for reporting purposes. Their responsibility ends at the file dump, but they do not want us sharing their layout for "competitive reasons". This is a MySQL limitation, so nothing to do with the vendor. We keep the table structure because it makes it easier to compare back to the originating application, and there's a ton of existing infrastructure that relies on the tables as they exist today; a rewrite to get comments functionality isn't feasible. I'd sooner do without comments in the table. 
      Sounds like the issue is actually a lack of schema and the "competitive reasons" is another phrasing of "embarrassment."  They don't want prospective customers finding out how badly designed and supported their products are.
      That was my take as well. Like I said, it's old. Probably designed in the 70's or 80's, and been extended ever since. It's a fairly inelastic environment.
      Spice (1) flagReport
      Was this post helpful? thumb_up thumb_down
    • Antal Daavid wrote:

      Scott Alan Miller wrote:

      Cassandra is pretty nice.  I've run both.  SPent more time with MongoDB, though.  I know that a lot of people don't like it, but boy has it treated us well.

      No complaints about it, other than scalability, then again our databases are huge (billions of rows across 500 databases) so depending on how much data OP has to deal with, it likely could be a negligible thing.
      Yeah, in general, MongoDB goes pretty big.  Bigger than most companies have to deal with. Way bigger than MySQL tends to go.
      Was this post helpful? thumb_up thumb_down
    • Scott Alan Miller wrote:

      cnicholsontech wrote:

      Scott Alan Miller wrote:

      cnicholsontech wrote:

      Does the length of the comment count against the row size?

      No, that's independent.
      I'm not sure that's true. Or there is some other limit on total-comment-length somewhere else. I just tried cutting back on the sentence in one of the ones that failed, and if I make it short enough, it inserts. Lengthen it another character, and it fails. See below where I first cut the comment back to the first 7 words, then cut off one character and it inserts.
      11:57:48 ALTER TABLE `sometable` CHANGE COLUMN `somecolumn` ` somecolumn ` char(11) NOT NULL DEFAULT '' COMMENT 'This is the calculated amount of a' Error Code: 1117. Too many columns 0.032 sec

      11:57:56 ALTER TABLE `sometable` CHANGE COLUMN ` somecolumn ` ` somecolumn ` char(11) NOT NULL DEFAULT '' COMMENT 'This is the calculated amount of ' 0 row(s) affected Records: 0 Duplicates: 0 Warnings: 0 0.047 sec
      Knowing this, I might be able to go back over the previous rows and trim out enough of the comments that it still makes sense, but it all fits. All the other tables are much less "wide" than this one.
      If that's true, the error is incorrect.  If the issue is total table size, it should report that.
      That's probably the case. My gut feeling is that this aspect of MySQL development is mostly ignored. Comments could only be up to 60 characters on MySQL 5.1, only breaking that limit with 5.5. So I think most people just probably don't run up against this and the error reporting is vague. The comments I'm importing are a few sentences, a paragraph at most, but across 500+ fields, that's going to be a decent amount of data. 

      I tried dumping just the comments into a text file and it was about 48k, so maybe that plus the data row have to fit in 64k. The MySQL docs are unclear on it.

      Was this post helpful? thumb_up thumb_down
    • Antal Daavid wrote:

      Scott Alan Miller wrote:

      Cassandra is pretty nice.  I've run both.  SPent more time with MongoDB, though.  I know that a lot of people don't like it, but boy has it treated us well.

      No complaints about it, other than scalability, then again our databases are huge (billions of rows across 500 databases) so depending on how much data OP has to deal with, it likely could be a negligible thing.
      Our deepest table size is in the 230 million row size area. Another table around 50 million. Most of the rest under 10 million. So probably overkill for our needs.

      I've toyed with the idea of switching to Microsoft. I'm not a fan, but it would be easier to get additional talent for the reporting side, at least with my anecdotal experience on hiring report developers in my area. 

      Spice (1) flagReport
      Was this post helpful? thumb_up thumb_down
    • cnicholsontech wrote:

      Scott Alan Miller wrote:

      cnicholsontech wrote:

      Scott Alan Miller wrote:

      cnicholsontech wrote:

      Does the length of the comment count against the row size?

      No, that's independent.
      I'm not sure that's true. Or there is some other limit on total-comment-length somewhere else. I just tried cutting back on the sentence in one of the ones that failed, and if I make it short enough, it inserts. Lengthen it another character, and it fails. See below where I first cut the comment back to the first 7 words, then cut off one character and it inserts.
      11:57:48 ALTER TABLE `sometable` CHANGE COLUMN `somecolumn` ` somecolumn ` char(11) NOT NULL DEFAULT '' COMMENT 'This is the calculated amount of a' Error Code: 1117. Too many columns 0.032 sec

      11:57:56 ALTER TABLE `sometable` CHANGE COLUMN ` somecolumn ` ` somecolumn ` char(11) NOT NULL DEFAULT '' COMMENT 'This is the calculated amount of ' 0 row(s) affected Records: 0 Duplicates: 0 Warnings: 0 0.047 sec
      Knowing this, I might be able to go back over the previous rows and trim out enough of the comments that it still makes sense, but it all fits. All the other tables are much less "wide" than this one.
      If that's true, the error is incorrect.  If the issue is total table size, it should report that.
      That's probably the case. My gut feeling is that this aspect of MySQL development is mostly ignored. Comments could only be up to 60 characters on MySQL 5.1, only breaking that limit with 5.5. So I think most people just probably don't run up against this and the error reporting is vague. The comments I'm importing are a few sentences, a paragraph at most, but across 500+ fields, that's going to be a decent amount of data. 

      I tried dumping just the comments into a text file and it was about 48k, so maybe that plus the data row have to fit in 64k. The MySQL docs are unclear on it.

      If you are hitting 48K in a text file, the chances that you are near 64K under the hood are very, very high.
      Spice (2) flagReport
      Was this post helpful? thumb_up thumb_down
    • cnicholsontech wrote:

      Antal Daavid wrote:

      Scott Alan Miller wrote:

      Cassandra is pretty nice.  I've run both.  SPent more time with MongoDB, though.  I know that a lot of people don't like it, but boy has it treated us well.

      No complaints about it, other than scalability, then again our databases are huge (billions of rows across 500 databases) so depending on how much data OP has to deal with, it likely could be a negligible thing.
      Our deepest table size is in the 230 million row size area. Another table around 50 million. Most of the rest under 10 million. So probably overkill for our needs.

      I've toyed with the idea of switching to Microsoft. I'm not a fan, but it would be easier to get additional talent for the reporting side, at least with my anecdotal experience on hiring report developers in my area. 

      I'd say the opposite.  SQL Server just adds complications and while there are "more" people who work with it, there are fewer than know what they are doing IMHO.  It makes it harder to sort through the people to hire.  And anyone that can do reporting on SQL Server but not MySQL isn't even remotely competent.  Those are shared skills to even an entry level programmer.  Anytime you are choosing your relational database because of the lack of skills of your developer ,you need to get rid of that developer.
      Spice (2) flagReport
      Was this post helpful? thumb_up thumb_down
    • Scott Alan Miller wrote:

      cnicholsontech wrote:

      Antal Daavid wrote:

      Scott Alan Miller wrote:

      Cassandra is pretty nice.  I've run both.  SPent more time with MongoDB, though.  I know that a lot of people don't like it, but boy has it treated us well.

      No complaints about it, other than scalability, then again our databases are huge (billions of rows across 500 databases) so depending on how much data OP has to deal with, it likely could be a negligible thing.
      Our deepest table size is in the 230 million row size area. Another table around 50 million. Most of the rest under 10 million. So probably overkill for our needs.

      I've toyed with the idea of switching to Microsoft. I'm not a fan, but it would be easier to get additional talent for the reporting side, at least with my anecdotal experience on hiring report developers in my area. 

      I'd say the opposite.  SQL Server just adds complications and while there are "more" people who work with it, there are fewer than know what they are doing IMHO.  It makes it harder to sort through the people to hire.  And anyone that can do reporting on SQL Server but not MySQL isn't even remotely competent.  Those are shared skills to even an entry level programmer.  Anytime you are choosing your relational database because of the lack of skills of your developer ,you need to get rid of that developer.
      I'll be sharing that with my boss. Absolutely true, and we've been there before. Guy ended up leaving on his own before we got around to firing him. Then he wanted to come back :)
      Was this post helpful? thumb_up thumb_down
    • Scott Alan Miller wrote:

      cnicholsontech wrote:

      Antal Daavid wrote:

      Scott Alan Miller wrote:

      Cassandra is pretty nice.  I've run both.  SPent more time with MongoDB, though.  I know that a lot of people don't like it, but boy has it treated us well.

      No complaints about it, other than scalability, then again our databases are huge (billions of rows across 500 databases) so depending on how much data OP has to deal with, it likely could be a negligible thing.
      Our deepest table size is in the 230 million row size area. Another table around 50 million. Most of the rest under 10 million. So probably overkill for our needs.

      I've toyed with the idea of switching to Microsoft. I'm not a fan, but it would be easier to get additional talent for the reporting side, at least with my anecdotal experience on hiring report developers in my area. 

      I'd say the opposite.  SQL Server just adds complications and while there are "more" people who work with it, there are fewer than know what they are doing IMHO.  It makes it harder to sort through the people to hire.  And anyone that can do reporting on SQL Server but not MySQL isn't even remotely competent.  Those are shared skills to even an entry level programmer.  Anytime you are choosing your relational database because of the lack of skills of your developer ,you need to get rid of that developer.
      You need to temper this with SQL reporting.  If you're using SSRS you may not know the tools for another system simply because you haven't used it but that doesn't mean you can't learn them.  The tool sets are vastly different, even though the underlying SQL is very similar.
      Was this post helpful? thumb_up thumb_down

    Read these next...

    • Snap! Reporting phishing in Teams, State of IT, Arc A770 graphics card, Optimus

      Snap! Reporting phishing in Teams, State of IT, Arc A770 graphics card, Optimus

      Spiceworks Originals

      Your daily dose of tech news, in brief. How is it already Monday? Actually, how is it already October 2022? It felt like SpiceWorld was just starting, and already it's over (for this year). But don't worry, the fun continues as this month is Cybers...

    • Can you run a print server on windows 10

      Can you run a print server on windows 10

      Windows

      I have many clients with no servers.  Looking for the best way to manage printers.If I share the printer then that machine that shares the printer needs to be online to print correct?   Having to always download the print driver and lookup the printers IP...

    • Lost of VDI rights on Windows 10 Enterprise E3

      Lost of VDI rights on Windows 10 Enterprise E3

      Software

      I am migration from standalone Windows 10 Enterprise E3 online subscription to Microsoft 365 E3 online subscription. However, I am seeing this in the terms.https://www.microsoft.com/licensing/terms/productoffering/Microsoft365/MOSADoes this mean I am losi...

    • Spark! Pro Series - 3rd October 2022

      Spark! Pro Series - 3rd October 2022

      Spiceworks Originals

      Welcome to Monday. I am currently sat at home in isolation, with my wife and I having succumbed to the dreaded Covid-19 after managing to avoid it for the last two and a half years! Still, it gives me the chance to indulge my fancy in yet another Spark! t...

    • What security best practices would you suggest?

      What security best practices would you suggest?

      Spiceworks

      It's Cybersecurity Awareness month, which if people took seriously, would likely be a great boon to all of ITkind. :)  The National Cybersecurity Alliance suggests some specific things that people can do to increase cybersecurity: Enabling multi-facto...