About save question condition

If you load the same CSV file into a different table and save it in metabase(questions), there are a table where you can save questions and a table where you can not save questions.
Each table has a different data type.
Specifically, data stored in int (7) can be saved, and data stored in int (20) and int (5) can not be saved.

Are there any save conditions for meta-based questions?

Hi @yuuuuuuuji
Which version of Metabase?
I don’t understand your problem. Metabase does not allow you to load CSV files, so can you explain what you’re doing in more details?
Do you see any errors in the Metabase log or browser console?

Hi @flamber
Thank you for reply.

metabase version : v0.32.9

I’m sorry for the lack of explanation.
I load the CSV file into MySQL and visualize it with metabase.

If you load the one CSV file into a different MySQL table and save it as a question in metabase, you may or may not be able to.

image

When I press the save button on the Newquestion screen, it will be displayed as Saving failed.

@yuuuuuuuji

Check the Metabase log (Admin > Troubleshooting > Logs), when it fails to save.

After you’ve created tableA/B and added data, have you synced/scanned the database in Metabase? Admin > Databases > “Sync database schema now” and “Re-scan field values now”

The (7) should not make any difference. It’s just the display width. It’s still just int which 4-byte signed.

@flamber

Thank you for reply.

I check the log.

Failed pattern
ーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーー
Jul 31 10:30:36 INFO metabase.api.card :: Card results metadata passed in to API is VALID. Thanks!
Jul 31 10:30:36 DEBUG metabase.async.util :: Running metabase.api.card$save_new_card_async_BANG_$fn__45948@4b1de8e on separate thread…
Jul 31 10:30:36 ERROR metabase.async.util :: Caught error running metabase.api.card$save_new_card_async_BANG_$fn__45948@4b1de8e
java.sql.SQLDataException: (conn=7872) Data too long for column ‘result_metadata’ at row 1
at org.mariadb.jdbc.internal.util.exceptions.ExceptionMapper.get(ExceptionMapper.java:225)
at org.mariadb.jdbc.internal.util.exceptions.ExceptionMapper.getException(ExceptionMapper.java:165)

(Abbreviation…)

at java.lang.Thread.run(Thread.java:748)
Caused by: java.sql.SQLException: Data too long for column ‘result_metadata’ at row 1
Query is: INSERT INTO report_card (description, collection_position, table_id, result_metadata, database_id, collection_id, query_type, name, creator_id, updated_at, dataset_query, display, visualization_settings, created_at) VALUES (NULL, NULL, 362, ?, 3, NULL, ?, ?, 1, ?, ?, ?, ?, ?), parameters [’[{“name”:“0_01”,“display_name”:“0 01”,“base_type”:“type/Integer”,“special_type”:“type/Category”,“fingerprint”:{“global”:{“distinct-count”:1,“nil%”:0},“type”:{“type/Number”:{“min”:0,“max”:0,“avg”:0,“sd”:0,“q1”:0,“q3”:0}}}},{“name”:“0_02”,“display_name”:“0 02”,“base_type”:“type/Integer”,“special_type”:“type/Category”,“fingerprint”:{“global”:{“distinct-count”:1,“nil%”:0},“type”:{“type/Number”:{“min”:0,“max”:0,“avg”:0,“sd”:0,“q1”:0,“q3”:0}}}},{“name”:“0_03”,“display_name”:“0 03”,“base_type”:“type/Integer”,“special_type”:“type/Category”,“fingerprint”:{“global”:{“distinct-count”:2,“nil%”:0},“type”:{“type/Number”:{“min”:0,“max”:1,“avg”:0.008771929824561403,“sd”:0.0936585811581694,“q1”:0,“q3”:0.3017…
java thread: clojure-agent-send-off-pool-22
at org.mariadb.jdbc.internal.util.LogQueryTool.exceptionWithQuery(LogQueryTool.java:163)
at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.executeQuery(AbstractQueryProtocol.java:280)
at org.mariadb.jdbc.MariaDbPreparedStatementClient.executeInternal(MariaDbPreparedStatementClient.java:223)
… 45 more

ーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーー

Success pattern
ーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーー
Jul 31 10:55:40 INFO metabase.api.card :: Card results metadata passed in to API is VALID. Thanks!
Jul 31 10:55:40 DEBUG metabase.async.util :: Running metabase.api.card$save_new_card_async_BANG_$fn__45948@7a3db575 on separate thread…
Jul 31 10:55:40 DEBUG metabase.middleware.log :: POST /api/card 200 [ASYNC: completed] 252 ms (38 DB calls) Jetty threads: 3/50 (2 idle, 0 queued) (67 total active threads) Queries in flight: 1
Jul 31 10:55:48 DEBUG metabase.middleware.log :: GET /api/user/current 200 2 ms (3 DB calls) Jetty threads: 4/50 (3 idle, 0 queued) (66 total active threads) Queries in flight: 0
Jul 31 10:55:48 DEBUG metabase.middleware.log :: GET /api/session/properties 200 2 ms (1 DB calls) Jetty threads: 4/50 (2 idle, 0 queued) (66 total active threads) Queries in flight: 0
Jul 31 10:55:48 DEBUG metabase.middleware.log :: GET /api/database 200 2 ms (3 DB calls) Jetty threads: 4/50 (2 idle, 0 queued) (66 total active threads) Queries in flight: 0
Jul 31 10:55:48 DEBUG metabase.middleware.log :: GET /api/setting 200 353 µs (0 DB calls) Jetty threads: 4/50 (2 idle, 0 queued) (66 total active threads) Queries in flight: 0
Jul 31 10:55:52 DEBUG metabase.middleware.log :: GET /api/user/current 200 2 ms (3 DB calls) Jetty threads: 5/50 (2 idle, 0 queued) (65 total active threads) Queries in flight: 0
Jul 31 10:55:52 DEBUG metabase.middleware.log :: GET /api/session/properties 200 2 ms (1 DB calls) Jetty threads: 4/50 (2 idle, 0 queued) (65 total active threads) Queries in flight: 0
Jul 31 10:55:52 DEBUG metabase.middleware.log :: GET /api/database 200 2 ms (3 DB calls) Jetty threads: 4/50 (2 idle, 0 queued) (65 total active threads) Queries in flight: 0
Jul 31 10:55:52 DEBUG metabase.middleware.log :: GET /api/setting 200 280 µs (0 DB calls) Jetty threads: 4/50 (2 idle, 0 queued) (65 total active threads) Queries in flight: 0
Jul 31 10:56:13 DEBUG metabase.middleware.log :: GET /api/user/current 200 2 ms (3 DB calls) Jetty threads: 5/50 (2 idle, 0 queued) (65 total active threads) Queries in flight: 0
Jul 31 10:56:13 DEBUG metabase.middleware.log :: GET /api/session/properties 200 2 ms (1 DB calls) Jetty threads: 4/50 (2 idle, 0 queued) (65 total active threads) Queries in flight: 0

ーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーー

After you’ve created tableA/B and added data, have you synced/scanned the database in Metabase? Admin > Databases > “Sync database schema now” and “Re-scan field values now”
→This has been done.

@yuuuuuuuji
Okay, not sure what is generating so much data in result_metadata that it cannot fit in the column.
I’m guessing you’re using MySQL as your metadata backend database.
What’s the column type of report_card.result_metadata ? If it is TEXT (64 KB) then try changing it to MEDIUMTEXT (16 MB).

@flamber

Thank you for reply.

The table does not have a column named result_metadata or report_card.result_metadata
This is result of desc.

after change
ーーーーーーーーーーーーーーーーーーーーーーーーーーーー
±------------±-----------±-----±----±--------±------+
| Field | Type | Null | Key | Default | Extra |
±------------±-----------±-----±----±--------±------+
| indexno | mediumtext | YES | | NULL | |
| clocktime | mediumtext | YES | | NULL | |
| nano | mediumtext | YES | | NULL | |
| SampleIndex | mediumtext | YES | | NULL | |
| 0_01 | int(20) | YES | | NULL | |
| 0_02 | int(20) | YES | | NULL | |
| 0_03 | int(20) | YES | | NULL | |
| 0_04 | int(20) | YES | | NULL | |
| 0_05 | int(20) | YES | | NULL | |
| 1_01 | int(20) | YES | | NULL | |



| 0_22 | int(20) | YES | | NULL | |
| 0_23 | int(20) | YES | | NULL | |
| 0_24 | int(20) | YES | | NULL | |
| 0_25 | int(20) | YES | | NULL | |
| 0_26 | int(20) | YES | | NULL | |
| 0_27 | int(20) | YES | | NULL | |
±------------±-----------±-----±----±--------±------+

ーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーー

before change
ーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーー
±------------±-----------±-----±----±--------±------+
| Field | Type | Null | Key | Default | Extra |
±------------±-----------±-----±----±--------±------+
| indexno | text | YES | | NULL | |
| clocktime | text | YES | | NULL | |
| nano | text | YES | | NULL | |
| SampleIndex | text | YES | | NULL | |
| 0_01 | int(20) | YES | | NULL | |
| 0_02 | int(20) | YES | | NULL | |
| 0_03 | int(20) | YES | | NULL | |
| 0_04 | int(20) | YES | | NULL | |
| 0_05 | int(20) | YES | | NULL | |
| 1_01 | int(20) | YES | | NULL | |



| 0_22 | int(20) | YES | | NULL | |
| 0_23 | int(20) | YES | | NULL | |
| 0_24 | int(20) | YES | | NULL | |
| 0_25 | int(20) | YES | | NULL | |
| 0_26 | int(20) | YES | | NULL | |
| 0_27 | int(20) | YES | | NULL | |
±------------±-----------±-----±----±--------±------+

I changed the data type(text→mediumtext ) and tried it, but I couldn’t save it.

@yuuuuuuuji
You’re misunderstanding. I’m not talking about your data.
I’m talking about Metabase’s application database (also called metadata). Have you migrated from H2 to MySQL?

@flamber

Thank you for reply.

Okey, Understood.
I has not migrate H2 to MySQL.

Is this?
mysql> show tables;
±-----------------------------+
| Tables_in_metabase |
±-----------------------------+
| DATABASECHANGELOG |
| DATABASECHANGELOGLOCK |
| QRTZ_BLOB_TRIGGERS |
| QRTZ_CALENDARS |
| QRTZ_CRON_TRIGGERS |
| QRTZ_FIRED_TRIGGERS |
| QRTZ_JOB_DETAILS |
| QRTZ_LOCKS |
| QRTZ_PAUSED_TRIGGER_GRPS |
| QRTZ_SCHEDULER_STATE |
| QRTZ_SIMPLE_TRIGGERS |
| QRTZ_SIMPROP_TRIGGERS |
| QRTZ_TRIGGERS |
| activity |
| card_label |
| collection |
| collection_revision |
| computation_job |
| computation_job_result |
| core_session |
| core_user |
| dashboard_favorite |
| dashboardcard_series |
| data_migrations |
| dependency |
| dimension |
| label |
| metabase_database |
| metabase_field |
| metabase_fieldvalues |
| metabase_table |
| metric |
| metric_important_field |
| permissions |
| permissions_group |
| permissions_group_membership |
| permissions_revision |
| pulse |
| pulse_card |
| pulse_channel |
| pulse_channel_recipient |
| query |
| query_cache |
| query_execution |
| report_card |
| report_cardfavorite |
| report_dashboard |
| report_dashboardcard |
| revision |
| segment |
| setting |
| task_history |
| view_log |
±-----------------------------+
53 rows in set (0.00 sec)

mysql>

@yuuuuuuuji
I’m getting more confused. You say that you have not migrated from H2 to MySQL, but then you post table output from MySQL, which is clearly a Metabase database.

Are you using H2 or MySQL for the Metabase application database?

If you are using MySQL, then change the column type like I wrote in comment #6

@flamber

Thank you for reply.
Sorry for the low understanding…

I’m sorry, I can not understand the transition from H2 to MySQL.
I thought to change the data type in the application database.

When connecting a database to metabase, it is registered with MySQL.

@yuuuuuuuji

It’s okay, Metabase is simple to use, but takes a bit more technical skills to set it up.
And it doesn’t help that we’re not speaking the same language :wink:

Metabase has to store all it’s data (settings, questions, dashboard, etc.) somewhere. That is the “application database” - also called “metadata”.
It has nothing to do with which databases Metabase is connected to in Admin > Databases.

Let’s go back to start:
You’re using Metabase 0.32.9.
How are you running it - JAR-file, Docker, … ?
If Docker, then post your docker run ... command.
If JAR-file, then post your java -jar ... command. And post your environment variables, if you have defined those.
Remember to remove passwords (or sensitive information) before posting

@flamber

Thank you for your polite response

How are you running it - JAR-file, Docker, … ?
→It’s JAR-file.

I’m running with systemctl.

ーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーー

[XXXX@ip-XX-XX-XX-XX system]$ cat metabase.service
[Unit]
Description=metabase server
After=syslog.target network.target

[Service]
User=root
Type=simple
WorkingDirectory=/metabase
ExecStart=/etc/systemd/system/metabase.sh
StandardOutput=syslog
StandardError=syslog
SyslogIdentifier=metabase
SuccessExitStatus=143
TimeoutStopSec=180
Restart=always

[Install]
WantedBy=multi-user.target

[XXXX@ip-XX-XX-XX-XX system]$
[XXXX@ip-XX-XX-XX-XX system]$ cat metabase.sh
#!/bin/bash

export LANG=‘ja_JP.UTF-8’
export MB_DB_TYPE=mysql
export MB_DB_DBNAME=metabase
export MB_DB_PORT=3306
export MB_DB_USER=XXXX
export MB_DB_PASS=XXXX
export MB_DB_HOST=XXXXX

/usr/bin/java -jar /metabase/metabase.jar
[XXXX@ip-XX-XX-XX-XX system]$

ーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーー

@yuuuuuuuji

There it was - MB_DB_TYPE=mysql
So you are using MySQL for your metadata.

Okay, go to that host (MB_DB_HOST=XXXXX), go to the database metabase, then the table report_card, then change the column result_metadata type from TEXT to MEDIUMTEXT

I still don’t understand why the change from int(7) to int(20) suddenly made the data exceed the column size, but without seeing your real data from the CSV, then it’s hard to guess.

@flamber

Thank you for reply.

You’re the best!
I can save questions now!
.

ーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーー

mysql> alter table report_card modify result_metadata MEDIUMTEXT;
Query OK, 53 rows affected (0.16 sec)
Records: 53 Duplicates: 0 Warnings: 0
mysql> desc report_card;
±-----------------------±-------------±-----±----±--------±---------------+
| Field | Type | Null | Key | Default | Extra |
±-----------------------±-------------±-----±----±--------±---------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| created_at | datetime | NO | | NULL | |
| updated_at | datetime | NO | | NULL | |
| name | varchar(254) | NO | | NULL | |
| description | text | YES | | NULL | |
| display | varchar(254) | NO | | NULL | |
| dataset_query | text | NO | | NULL | |
| visualization_settings | text | NO | | NULL | |
| creator_id | int(11) | NO | MUL | NULL | |
| database_id | int(11) | YES | MUL | NULL | |
| table_id | int(11) | YES | MUL | NULL | |
| query_type | varchar(16) | YES | | NULL | |
| archived | bit(1) | NO | | b’0’ | |
| collection_id | int(11) | YES | MUL | NULL | |
| public_uuid | char(36) | YES | UNI | NULL | |
| made_public_by_id | int(11) | YES | MUL | NULL | |
| enable_embedding | bit(1) | NO | | b’0’ | |
| embedding_params | text | YES | | NULL | |
| cache_ttl | int(11) | YES | | NULL | |
| result_metadata | mediumtext | YES | | NULL | |
| read_permissions | text | YES | | NULL | |
| collection_position | smallint(6) | YES | | NULL | |
±-----------------------±-------------±-----±----±--------±---------------+
22 rows in set (0.00 sec)

1 Like