CODEWITHSUNDEEP
mysqldatabasemysql-connectormysql-connector-pythonmysql-connect
Mash
Mash

Reputation: 39

Row size limit issue in MySQL with python (mysql-connector)

I'm trying to insert a new table as usual except that one doesn't work and I don't know why the console tells me I've hit the row limit size:

  1. There is one "INT NOT NULL AUTO_INCREMENT" column (it's 4 bytes)
  2. There is one TEXT(15000) column
  3. Every other column is stupidly set to "VARCHAR(150)".
  4. There's a total of 198 "VARCHAR(150)" columns and one "INT NOT NULL AUTO_INCREMENT" column.
  5. Each column costs 2 bytes to create
  6. (1 * 4) + (1 * 15000) + (198 * 150) + (202 * 2) = 45 108 || This is very far from the max row size of 65535.
  7. The HOST address is the localhost one
  8. The longest table name in MySQL is 64 characters, here the longest one is 59 characters and it's "contract_operationalManagerTags_margin_management_startDate"
  9. I never had any issue inserting new tables and new values in this database
  10. I made sure that the table doesn't exist already in the database before creating it again when running my script

Thanks for your help.

Here is my code:


import mysql.connector

# - Connect to Server

mydb = mysql.connector.connect(
            host      = HOST,
            user      = USER,
            password  = PASSWORD
        )

# - Cursor creation for commands

mycursor = mydb.cursor()

# - Check if Database exists, creates it if doesn't exist

mycursor.execute("CREATE DATABASE IF NOT EXISTS " + DATABASE_NAME)

# - Use the $DATABASE_NAME database

mycursor.execute("USE " + DATABASE_NAME)

# Create my new table (Don't worry if there's some returns in the middle of the query, it's due to the terminal size i copied it from)

query = "CREATE TABLE IF NOT EXISTS invoices (id INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (id), user VARCHAR(150), customerName VARCHAR(150), contractNumber VARCHAR(150), orderNumber VARCHAR(150), invoiceNumber VARCHAR(150), billingDate VARCHAR(150), description 
TEXT(15000), amountExclusiveOfVat VARCHAR(150), amountInclusiveOfVat VARCHAR(150), article VARCHAR(150), articleCategory VARCHAR(150), articleCode VARCHAR(150), articleExternalCode VARCHAR(150), articleInvoiceCode VARCHAR(150), articleVatAccount VARCHAR(150), articleSalesAccount VARCHAR(150), articleDescription VARCHAR(150), articleName VARCHAR(150), articleVat VARCHAR(150), articleTaxCode VARCHAR(150), articleTaxName VARCHAR(150), articleTaxRate VARCHAR(150), customerGroup VARCHAR(150), customerPaymentMethod VARCHAR(150), externalRef VARCHAR(150), customerUuid VARCHAR(150), projectCode VARCHAR(150), orderName VARCHAR(150), orderRefNum VARCHAR(150), orderUuid VARCHAR(150), comment VARCHAR(150), contract_sales VARCHAR(150), salesLastName VARCHAR(150), salesUsername VARCHAR(150), salesShortUsername VARCHAR(150), salesFirstName VARCHAR(150), salesUuid VARCHAR(150), contractGroupCode VARCHAR(150), contractUuid VARCHAR(150), creationDate VARCHAR(150), currency VARCHAR(150), billingAddressName VARCHAR(150), billingAddressCountry VARCHAR(150), billedLegalEntityExternalCode VARCHAR(150), billedLegalEntityName VARCHAR(150), billedLegalEntityVatNumber VARCHAR(150), billedLegalEntityUuid VARCHAR(150), approvalDate VARCHAR(150), billingDateMonth VARCHAR(150), invoiceAmountExclusiveOfVat VARCHAR(150), invoiceAmountInclusiveOfVat VARCHAR(150), invoiceAmountOfVat VARCHAR(150), invoiceSalesAmountOfVat VARCHAR(150), invoiceSalesAmountExclusiveOfVat VARCHAR(150), invoiceSalesAmountInclusiveOfVat VARCHAR(150), invoiceSalesAmountInclusiveOfVatSysCur VARCHAR(150), period VARCHAR(150), periodStartDate VARCHAR(150), periodStartDateMonth VARCHAR(150), periodEndDate VARCHAR(150), periodEndDateMonth VARCHAR(150), invoiceUuid VARCHAR(150), operationalManager VARCHAR(150), operationalManagerLastName VARCHAR(150), operationalManagerUsername VARCHAR(150), operationalManagerShortUsername VARCHAR(150), operationalManagerFirstName VARCHAR(150), operationalManagerUuid VARCHAR(150), amountOfVat VARCHAR(150), salesAmountOfVat VARCHAR(150), salesAmountExclusiveOfVat VARCHAR(150), salesAmountExclusiveOfVatSysCur VARCHAR(150), salesAmountInclusiveOfVat VARCHAR(150), salesAmountInclusiveOfVatSysCur VARCHAR(150), unitAmount VARCHAR(150), position VARCHAR(150), numberOfUnits VARCHAR(150), timesheetDate VARCHAR(150), timesheetDateMonth VARCHAR(150), billingCompanyName VARCHAR(150), invoiceStatus VARCHAR(150), deleted VARCHAR(150), vat VARCHAR(150), taxRate VARCHAR(150), taxBreakDown1Amount VARCHAR(150), taxBreakDown1SalesAmount VARCHAR(150), taxBreakDown1Code VARCHAR(150), taxBreakDown1Name VARCHAR(150), taxBreakDown1InvoiceLabel VARCHAR(150), taxBreakDown1Rate 
VARCHAR(150), taxBreakDown2Amount VARCHAR(150), taxBreakDown2SalesAmount VARCHAR(150), taxBreakDown2Code VARCHAR(150), taxBreakDown2Name VARCHAR(150), taxBreakDown2InvoiceLabel VARCHAR(150), taxBreakDown2Rate VARCHAR(150), taxBreakDown3Amount VARCHAR(150), taxBreakDown3SalesAmount VARCHAR(150), taxBreakDown3Code VARCHAR(150), taxBreakDown3Name VARCHAR(150), taxBreakDown3InvoiceLabel VARCHAR(150), taxBreakDown3Rate VARCHAR(150), invoiceType VARCHAR(150), uuid VARCHAR(150), lastName VARCHAR(150), username VARCHAR(150), shortUsername VARCHAR(150), firstName VARCHAR(150), invoice_billedLegalEntityTags_company_group VARCHAR(150), invoice_billedLegalEntityTags_industry VARCHAR(150), contractTags_contract_BU VARCHAR(150), contract_salesTags_BU VARCHAR(150), contract_salesTags_BU_endDate VARCHAR(150), contract_salesTags_clause_non_conc VARCHAR(150), contract_salesTags_clause_non_conc_endDate VARCHAR(150), contract_salesTags_hr_contract_type VARCHAR(150), contract_salesTags_hr_contract_type_endDate VARCHAR(150), contract_salesTags_region VARCHAR(150), contract_salesTags_region_startDate VARCHAR(150), contract_salesTags_region_endDate 
VARCHAR(150), contract_salesTags_billable VARCHAR(150), contract_salesTags_billable_endDate VARCHAR(150), contract_salesTags_margin_management VARCHAR(150), contract_salesTags_margin_management_startDate VARCHAR(150), contract_salesTags_margin_management_endDate VARCHAR(150), contract_salesTags_staff_membership VARCHAR(150), contract_salesTags_staff_membership_startDate VARCHAR(150), contract_salesTags_staff_membership_endDate VARCHAR(150), contract_salesTags_position VARCHAR(150), contract_salesTags_position_endDate VARCHAR(150), contract_salesTags_Practice VARCHAR(150), contract_salesTags_Practice_startDate VARCHAR(150), contract_salesTags_Practice_endDate VARCHAR(150), contract_salesTags_company VARCHAR(150), contract_salesTags_company_endDate VARCHAR(150), contract_salesTags_consultantType VARCHAR(150), contract_salesTags_consultantType_startDate VARCHAR(150), contract_salesTags_consultantType_endDate VARCHAR(150), contract_operationalManagerTags_BU VARCHAR(150), contract_operationalManagerTags_BU_endDate VARCHAR(150), contract_operationalManagerTags_clause_non_conc VARCHAR(150), contract_operationalManagerTags_clause_non_conc_endDate VARCHAR(150), contract_operationalManagerTags_hr_contract_type VARCHAR(150), contract_operationalManagerTags_hr_contract_type_endDate VARCHAR(150), contract_operationalManagerTags_region VARCHAR(150), contract_operationalManagerTags_region_startDate VARCHAR(150), contract_operationalManagerTags_region_endDate VARCHAR(150), contract_operationalManagerTags_billable VARCHAR(150), contract_operationalManagerTags_billable_endDate VARCHAR(150), contract_operationalManagerTags_margin_management VARCHAR(150), contract_operationalManagerTags_margin_management_startDate VARCHAR(150), contract_operationalManagerTags_margin_management_endDate VARCHAR(150), contract_operationalManagerTags_staff_membership VARCHAR(150), contract_operationalManagerTags_staff_membership_startDate VARCHAR(150), contract_operationalManagerTags_staff_membership_endDate VARCHAR(150), contract_operationalManagerTags_position VARCHAR(150), contract_operationalManagerTags_position_endDate VARCHAR(150), contract_operationalManagerTags_Practice VARCHAR(150), contract_operationalManagerTags_Practice_startDate VARCHAR(150), contract_operationalManagerTags_Practice_endDate VARCHAR(150), contract_operationalManagerTags_company VARCHAR(150), contract_operationalManagerTags_company_endDate VARCHAR(150), contract_operationalManagerTags_consultantType VARCHAR(150), contract_operationalManagerTags_consultantType_startDate VARCHAR(150), contract_operationalManagerTags_consultantType_endDate VARCHAR(150), timesheetOwnerTags_BU VARCHAR(150), timesheetOwnerTags_BU_endDate VARCHAR(150), timesheetOwnerTags_clause_non_conc VARCHAR(150), timesheetOwnerTags_clause_non_conc_endDate VARCHAR(150), timesheetOwnerTags_hr_contract_type VARCHAR(150), timesheetOwnerTags_hr_contract_type_startDate VARCHAR(150), timesheetOwnerTags_hr_contract_type_endDate VARCHAR(150), timesheetOwnerTags_region VARCHAR(150), timesheetOwnerTags_region_startDate VARCHAR(150), timesheetOwnerTags_region_endDate VARCHAR(150), timesheetOwnerTags_billable VARCHAR(150), timesheetOwnerTags_billable_endDate VARCHAR(150), timesheetOwnerTags_margin_management VARCHAR(150), timesheetOwnerTags_margin_management_endDate VARCHAR(150), timesheetOwnerTags_staff_membership VARCHAR(150), timesheetOwnerTags_staff_membership_endDate VARCHAR(150), timesheetOwnerTags_position VARCHAR(150), timesheetOwnerTags_position_endDate VARCHAR(150), timesheetOwnerTags_Practice VARCHAR(150), timesheetOwnerTags_Practice_startDate VARCHAR(150), timesheetOwnerTags_Practice_endDate VARCHAR(150), timesheetOwnerTags_company VARCHAR(150), timesheetOwnerTags_company_endDate VARCHAR(150), timesheetOwnerTags_consultantType VARCHAR(150), timesheetOwnerTags_consultantType_startDate VARCHAR(150), timesheetOwnerTags_consultantType_endDate VARCHAR(150), customerTags_company_group VARCHAR(150), customerTags_industry VARCHAR(150), contractExtensions_accountingCost VARCHAR(150), contractExtensions_department VARCHAR(150), contractExtensions_contractusername VARCHAR(150), timesheetOwnerExtensions_compte_tiers_sage VARCHAR(150), timesheetOwnerExtensions_motifdedepart VARCHAR(150), timesheetOwnerExtensions_sageId VARCHAR(150))"

# Insert the new table in the database
mycursor.execute(query)

CONSOLE OUTPUT:

File "c:\Users\MASH\OneDrive - Quanteam\Bureau\Boulot\repo_github\SI\mediation\alibeez\tools\db_extract&create.py", line 81, in extract_create
    mycursor.execute(query)
  File "C:\Users\MASH\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.10_qbz5n2kfra8p0\LocalCache\local-packages\Python310\site-packages\mysql\connector\cursor.py", line 572, in execute
    self._handle_result(self._connection.cmd_query(stmt))
  File "C:\Users\MASH\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.10_qbz5n2kfra8p0\LocalCache\local-packages\Python310\site-packages\mysql\connector\connection.py", line 922, in cmd_query
    result = self._handle_result(self._send_cmd(ServerCmd.QUERY, query))
  File "C:\Users\MASH\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.10_qbz5n2kfra8p0\LocalCache\local-packages\Python310\site-packages\mysql\connector\connection.py", line 732, in _handle_result
    raise errors.get_exception(packet)
mysql.connector.errors.ProgrammingError: 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs

HERE YOU CAN SEE MY QUERY IN VERTICAL FORMAT (I passed an online syntax checker for MySQL 8.0 on https://www.eversql.com/sql-syntax-check-validator/ and not mistake has been found)

CREATE TABLE IF NOT EXISTS invoices (
  id INT NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (id),
  user VARCHAR(150),
  customerName VARCHAR(150),
  contractNumber VARCHAR(150),
  orderNumber VARCHAR(150),
  invoiceNumber VARCHAR(150),
  billingDate VARCHAR(150),
  description TEXT(15000),
  amountExclusiveOfVat VARCHAR(150),
  amountInclusiveOfVat VARCHAR(150),
  article VARCHAR(150),
  articleCategory VARCHAR(150),
  articleCode VARCHAR(150),
  articleExternalCode VARCHAR(150),
  articleInvoiceCode VARCHAR(150),
  articleVatAccount VARCHAR(150),
  articleSalesAccount VARCHAR(150),
  articleDescription VARCHAR(150),
  articleName VARCHAR(150),
  articleVat VARCHAR(150),
  articleTaxCode VARCHAR(150),
  articleTaxName VARCHAR(150),
  articleTaxRate VARCHAR(150),
  customerGroup VARCHAR(150),
  customerPaymentMethod VARCHAR(150),
  externalRef VARCHAR(150),
  customerUuid VARCHAR(150),
  projectCode VARCHAR(150),
  orderName VARCHAR(150),
  orderRefNum VARCHAR(150),
  orderUuid VARCHAR(150),
  comment VARCHAR(150),
  contract_sales VARCHAR(150),
  salesLastName VARCHAR(150),
  salesUsername VARCHAR(150),
  salesShortUsername VARCHAR(150),
  salesFirstName VARCHAR(150),
  salesUuid VARCHAR(150),
  contractGroupCode VARCHAR(150),
  contractUuid VARCHAR(150),
  creationDate VARCHAR(150),
  currency VARCHAR(150),
  billingAddressName VARCHAR(150),
  billingAddressCountry VARCHAR(150),
  billedLegalEntityExternalCode VARCHAR(150),
  billedLegalEntityName VARCHAR(150),
  billedLegalEntityVatNumber VARCHAR(150),
  billedLegalEntityUuid VARCHAR(150),
  approvalDate VARCHAR(150),
  billingDateMonth VARCHAR(150),
  invoiceAmountExclusiveOfVat VARCHAR(150),
  invoiceAmountInclusiveOfVat VARCHAR(150),
  invoiceAmountOfVat VARCHAR(150),
  invoiceSalesAmountOfVat VARCHAR(150),
  invoiceSalesAmountExclusiveOfVat VARCHAR(150),
  invoiceSalesAmountInclusiveOfVat VARCHAR(150),
  invoiceSalesAmountInclusiveOfVatSysCur VARCHAR(150),
  period VARCHAR(150),
  periodStartDate VARCHAR(150),
  periodStartDateMonth VARCHAR(150),
  periodEndDate VARCHAR(150),
  periodEndDateMonth VARCHAR(150),
  invoiceUuid VARCHAR(150),
  operationalManager VARCHAR(150),
  operationalManagerLastName VARCHAR(150),
  operationalManagerUsername VARCHAR(150),
  operationalManagerShortUsername VARCHAR(150),
  operationalManagerFirstName VARCHAR(150),
  operationalManagerUuid VARCHAR(150),
  amountOfVat VARCHAR(150),
  salesAmountOfVat VARCHAR(150),
  salesAmountExclusiveOfVat VARCHAR(150),
  salesAmountExclusiveOfVatSysCur VARCHAR(150),
  salesAmountInclusiveOfVat VARCHAR(150),
  salesAmountInclusiveOfVatSysCur VARCHAR(150),
  unitAmount VARCHAR(150),
  position VARCHAR(150),
  numberOfUnits VARCHAR(150),
  timesheetDate VARCHAR(150),
  timesheetDateMonth VARCHAR(150),
  billingCompanyName VARCHAR(150),
  invoiceStatus VARCHAR(150),
  deleted VARCHAR(150),
  vat VARCHAR(150),
  taxRate VARCHAR(150),
  taxBreakDown1Amount VARCHAR(150),
  taxBreakDown1SalesAmount VARCHAR(150),
  taxBreakDown1Code VARCHAR(150),
  taxBreakDown1Name VARCHAR(150),
  taxBreakDown1InvoiceLabel VARCHAR(150),
  taxBreakDown1Rate VARCHAR(150),
  taxBreakDown2Amount VARCHAR(150),
  taxBreakDown2SalesAmount VARCHAR(150),
  taxBreakDown2Code VARCHAR(150),
  taxBreakDown2Name VARCHAR(150),
  taxBreakDown2InvoiceLabel VARCHAR(150),
  taxBreakDown2Rate VARCHAR(150),
  taxBreakDown3Amount VARCHAR(150),
  taxBreakDown3SalesAmount VARCHAR(150),
  taxBreakDown3Code VARCHAR(150),
  taxBreakDown3Name VARCHAR(150),
  taxBreakDown3InvoiceLabel VARCHAR(150),
  taxBreakDown3Rate VARCHAR(150),
  invoiceType VARCHAR(150),
  uuid VARCHAR(150),
  lastName VARCHAR(150),
  username VARCHAR(150),
  shortUsername VARCHAR(150),
  firstName VARCHAR(150),
  invoice_billedLegalEntityTags_company_group VARCHAR(150),
  invoice_billedLegalEntityTags_industry VARCHAR(150),
  contractTags_contract_BU VARCHAR(150),
  contract_salesTags_BU VARCHAR(150),
  contract_salesTags_BU_endDate VARCHAR(150),
  contract_salesTags_clause_non_conc VARCHAR(150),
  contract_salesTags_clause_non_conc_endDate VARCHAR(150),
  contract_salesTags_hr_contract_type VARCHAR(150),
  contract_salesTags_hr_contract_type_endDate VARCHAR(150),
  contract_salesTags_region VARCHAR(150),
  contract_salesTags_region_startDate VARCHAR(150),
  contract_salesTags_region_endDate VARCHAR(150),
  contract_salesTags_billable VARCHAR(150),
  contract_salesTags_billable_endDate VARCHAR(150),
  contract_salesTags_margin_management VARCHAR(150),
  contract_salesTags_margin_management_startDate VARCHAR(150),
  contract_salesTags_margin_management_endDate VARCHAR(150),
  contract_salesTags_staff_membership VARCHAR(150),
  contract_salesTags_staff_membership_startDate VARCHAR(150),
  contract_salesTags_staff_membership_endDate VARCHAR(150),
  contract_salesTags_position VARCHAR(150),
  contract_salesTags_position_endDate VARCHAR(150),
  contract_salesTags_Practice VARCHAR(150),
  contract_salesTags_Practice_startDate VARCHAR(150),
  contract_salesTags_Practice_endDate VARCHAR(150),
  contract_salesTags_company VARCHAR(150),
  contract_salesTags_company_endDate VARCHAR(150),
  contract_salesTags_consultantType VARCHAR(150),
  contract_salesTags_consultantType_startDate VARCHAR(150),
  contract_salesTags_consultantType_endDate VARCHAR(150),
  contract_operationalManagerTags_BU VARCHAR(150),
  contract_operationalManagerTags_BU_endDate VARCHAR(150),
  contract_operationalManagerTags_clause_non_conc VARCHAR(150),
  contract_operationalManagerTags_clause_non_conc_endDate VARCHAR(150),
  contract_operationalManagerTags_hr_contract_type VARCHAR(150),
  contract_operationalManagerTags_hr_contract_type_endDate VARCHAR(150),
  contract_operationalManagerTags_region VARCHAR(150),
  contract_operationalManagerTags_region_startDate VARCHAR(150),
  contract_operationalManagerTags_region_endDate VARCHAR(150),
  contract_operationalManagerTags_billable VARCHAR(150),
  contract_operationalManagerTags_billable_endDate VARCHAR(150),
  contract_operationalManagerTags_margin_management VARCHAR(150),
  contract_operationalManagerTags_margin_management_startDate VARCHAR(150),
  contract_operationalManagerTags_margin_management_endDate VARCHAR(150),
  contract_operationalManagerTags_staff_membership VARCHAR(150),
  contract_operationalManagerTags_staff_membership_startDate VARCHAR(150),
  contract_operationalManagerTags_staff_membership_endDate VARCHAR(150),
  contract_operationalManagerTags_position VARCHAR(150),
  contract_operationalManagerTags_position_endDate VARCHAR(150),
  contract_operationalManagerTags_Practice VARCHAR(150),
  contract_operationalManagerTags_Practice_startDate VARCHAR(150),
  contract_operationalManagerTags_Practice_endDate VARCHAR(150),
  contract_operationalManagerTags_company VARCHAR(150),
  contract_operationalManagerTags_company_endDate VARCHAR(150),
  contract_operationalManagerTags_consultantType VARCHAR(150),
  contract_operationalManagerTags_consultantType_startDate VARCHAR(150),
  contract_operationalManagerTags_consultantType_endDate VARCHAR(150),
  timesheetOwnerTags_BU VARCHAR(150),
  timesheetOwnerTags_BU_endDate VARCHAR(150),
  timesheetOwnerTags_clause_non_conc VARCHAR(150),
  timesheetOwnerTags_clause_non_conc_endDate VARCHAR(150),
  timesheetOwnerTags_hr_contract_type VARCHAR(150),
  timesheetOwnerTags_hr_contract_type_startDate VARCHAR(150),
  timesheetOwnerTags_hr_contract_type_endDate VARCHAR(150),
  timesheetOwnerTags_region VARCHAR(150),
  timesheetOwnerTags_region_startDate VARCHAR(150),
  timesheetOwnerTags_region_endDate VARCHAR(150),
  timesheetOwnerTags_billable VARCHAR(150),
  timesheetOwnerTags_billable_endDate VARCHAR(150),
  timesheetOwnerTags_margin_management VARCHAR(150),
  timesheetOwnerTags_margin_management_endDate VARCHAR(150),
  timesheetOwnerTags_staff_membership VARCHAR(150),
  timesheetOwnerTags_staff_membership_endDate VARCHAR(150),
  timesheetOwnerTags_position VARCHAR(150),
  timesheetOwnerTags_position_endDate VARCHAR(150),
  timesheetOwnerTags_Practice VARCHAR(150),
  timesheetOwnerTags_Practice_startDate VARCHAR(150),
  timesheetOwnerTags_Practice_endDate VARCHAR(150),
  timesheetOwnerTags_company VARCHAR(150),
  timesheetOwnerTags_company_endDate VARCHAR(150),
  timesheetOwnerTags_consultantType VARCHAR(150),
  timesheetOwnerTags_consultantType_startDate VARCHAR(150),
  timesheetOwnerTags_consultantType_endDate VARCHAR(150),
  customerTags_company_group VARCHAR(150),
  customerTags_industry VARCHAR(150),
  contractExtensions_accountingCost VARCHAR(150),
  contractExtensions_department VARCHAR(150),
  contractExtensions_contractusername VARCHAR(150),
  timesheetOwnerExtensions_compte_tiers_sage VARCHAR(150),
  timesheetOwnerExtensions_motifdedepart VARCHAR(150),
  timesheetOwnerExtensions_sageId VARCHAR(150)
) "

Upvotes: 0

Views: 787

Answers (0)

Related Questions