Error: ER_TOO_LONG_IDENT: Identifier name is too long: table name substituted twice

[benswinburne]

Describe the bug

I am aware that this has been discussed and "fixed" with sqlAlias in the below listed issue and PR but I managed to go over the maximum a couple of times today.

By adding

Error: ER_TOO_LONG_IDENT: Identifier name 'dwell_dwell_averages20201201_lsh1ftzo_xmgdsxnu_1ft2cgq_lsh1ftzo_xmgdsxnu_1ft2cgq' is too long

"dwell_dwell_averages20201201_lsh1ftzo_xmgdsxnu_1ft2cgq_lsh1ftzo_xmgdsxnu_1ft2cgq".length // 80

By using sqlAlias on both the cube and the pre-aggregation I managed to get it to 64

"dw_d20201201_lsh1ftzo_xmgdsxnu_1ft2cgq_lsh1ftzo_xmgdsxnu_1ft2cgq".length // 64

But the name and alias is meaningless now really and 60 of the characters are useless. Is is possible that some sort of hash is used instead to still get a consistent name for the table but use fewer characters?

To Reproduce
Steps to reproduce the behavior:

  preAggregations: {
    averages: {
      type: `rollup`,
      indexes: {
        creativeId: {
          columns: [creativeId],
        },
      },
      measureReferences: [count, totalDwellTimeInSeconds],
      timeDimensionReference: dt,
      dimensionReferences: [creativeId],
      granularity: `day`,
      refreshKey: {
        every: `30 minutes`,
      },
      external: true,
      scheduledRefresh: true,

      // This increases the length of the table name considerably, and when
      // starting up it says Performing query: undefined so i can't help but
      // think that it is broken or misconfigured in some way.
      // partitionGranularity: `month`,
      // refreshRangeStart: {
      //   sql: `SELECT current_timestamp - interval '30' day`,
      // },
      // refreshRangeEnd: {
      //   sql: `SELECT current_timestamp`,
      // },
    },
  },

Expected behavior
A reasonable length table name such that it doesn't break the upper limits of MySQL/postgres so easily.

Version:

  "dependencies": {
    "@cubejs-backend/athena-driver": "^0.24.5",
    "@cubejs-backend/mysql-driver": "^0.24.4",
    "@cubejs-backend/server": "^0.24.4"
  },

Additional context
Add any other context about the problem here.

#1068
#86

[ovr]

Hello @benswinburne,

I understand your pain with limitations from MySQL/PostgreSQL.

Table name for pre-aggregations are done automatically and It contains:

`${table_name}_${content_version}_${versionEntry}_${last_updated_at}`

Where:

• content_version/versionEntry - are strings that encoded by hash function
• last_updated_at - is a timestamp that was encoded as Base32

It's not possible to use hash for ${content_version}_${versionEntry}_${last_updated_at} to shorten it because Cube.js parse table names while it is works with pre-aggregations.

At first look, I don't have any ideas about how to shorten table names for pre-aggregations.

[benswinburne]

Given the requirement for either in-memory or redis (and upcoming dynamodb) cache/queue drivers, the dependency on some sort of secondary store is already present so could be leveraged to store metadata. It could possibly store a short hash on the table name which the meta data could be stored against in redis etc and looked up when necessary. This brings the obvious downside of loss of this meta store in the event of failure/restart etc- This isn't an issue with the dynamodb driver when that's available but to solve this issue redis/memory meta stores could be periodically written to disk (or configurable external store such as s3) as the writes would be tiny in theory it should be fast and cheap. Obviously this store would only be read in the event of Cube starting up for whatever reason, not read from on each check. Another option with the move towards a microservices architecture for cube a sidecar container with a metadata store/service would be a possibility. This could work in the same way as say the EC2 metadata service- machines check it for pertinent information when they start up and configure themselves appropriately before being usable. Obviously it's faster and more convenient to store it on the table name itself as it needs no lookups (but this could be stored in memory after first use given a dynamodb/redis or meta service implementation to keep it snappy). The amount of data you're trying to store in the table name is pushing (and breaking in some cases) the limits of the databases you're recommending to use as caching layers unfortunately though. What're your thoughts?

…

On Thu, 10 Dec 2020 at 09:41, Dmitry Patsura ***@***.***> wrote: Hello @benswinburne <https://github.com/benswinburne>, I understand your pain with limitations from MySQL/PostgreSQL. Table name for pre-aggregations are done automatically and It contains: `${table_name}_${content_version}_${versionEntry}_${last_updated_at}` Where: - content_version/versionEntry - are strings that encoded by hash function - last_updated_at - is a timestamp that was encoded as Base32 It's not possible to use hash for ${content_version}_${versionEntry}_${last_updated_at} to shorten it because Cube.js parse table names while it is works with pre-aggregations. At first look, I don't have any ideas about how to shorten table names for pre-aggregations. — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#1564 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AAJHFENZTAZ7HU4DTJWZNHDSUCJU7ANCNFSM4UUH3WSA> .

[ovr]

Yes, It can be a solution to use "meta storage" and put information about pre-aggregation inside it and use table names without meta information, but for now there are no plans to work on it.

I think Cube Store will resolve this limitation and it will be default/recommended database for pre-aggregations.

[paveltiunov]

@benswinburne @ovr This is a bug. The table name is substituted twice.

[blefevre]

I'm having the same issue, it only seems to occur when I have an index defined in my aggregation.

[paveltiunov]

Can be fixed by migration to the Cube Store.