Datasetview error

Johan · February 22, 2018, 8:28am

Hello All,

I have a problem in the datasetview.

xibo error

This problem commes when creating more then 33 columns in the dataset.
if i delete a column the error is gone and the viewpage works!

CMS = 1.8.5 with docker install.

dan · February 22, 2018, 9:00am

You are most likely hitting the MySQL byte limit for columns (strings are created as text, which will be consuming a lot of bytes).

There isn’t anything we can do about this i’m afraid. How many columns do you need?

You could try setting non-string datatypes where appropriate

Johan · February 22, 2018, 9:43am

That is a problem in my situation. I Need like 50 columns with text value (i use the list content option).
Is there any adjustment i can make to the sql database (change to blob?) or any setting i can tweak?

dan · February 22, 2018, 9:45am

You could manually edit the column datatypes in the database, from text to a smaller varchar as required. We create a “realised table” to manage datasets with the name dataset_<datasetid>.

Assuming that you don’t change your column name or datatype after making your manual tweaks, that column shouldn’t be touched.

Johan · February 22, 2018, 9:49am

do i get problems with xibo update’s if i change this manually?
can i change the byte limit to a higher value?

dan · February 22, 2018, 9:54am

You can’t change the byte limit and yes you might get problems with updates in the future (although they would have to be pretty extreme).

We document all changes in each release in GitHub, so you could be confident that you’d see if there were any changes to DataSet’s before you upgraded.

Johan · February 22, 2018, 3:06pm

how can this problem happen if the dataset is empty (only columns created)?

dan · February 22, 2018, 3:30pm

I’m really not sure to be honest - you can read about it here: http://sforsuresh.in/maximum-number-of-columns-per-table-mysql/

I guess it has to allocate certain byte space for the table definition, or something like that.

Johan · February 22, 2018, 4:50pm

Change of the type didn’t helpt. only deleting columns.

just to make sure; the log file states :

WEB GET INFO /dataset/data/view/9 Request stats: { “default”: { “select”: 9 }, “log”: { “insert”: 4 }, “length”: 0.08197689056396484, “memoryUsage”: 8344104, “peakMemoryUsage”: 8503712 }.

dan · February 22, 2018, 5:34pm

Those are just stats for the entire web request, written out for info and debugging purposes.

You’d probably have to change the column types of nearly all of them from TEXT to VARCHAR(20) or whatever length you want.

I’m not sure what else to suggest - aside from the 65k byte limit I think you should be able to have 4000 columns in MySQL.

Johan · February 22, 2018, 5:54pm

is it possible the error comes from loading another table
like “datasetcolumn”? (which holds the values for the xibodataset column-name)

i tried to change the complete dataset to varchar(10) error is still there.
It is strange that the error is there when there is no data inside the dataset table.

Johan · February 22, 2018, 7:11pm

seems the error is coming from :

Failed to load resource: the server responded with a status of 414 (Request-URI Too Long)

dan · February 22, 2018, 8:04pm

Oh really - well I do apologise!! It isn’t what I thought at all then.

Unfortunately that problem might be even harder to fix - its the Data Table sending its filtering and sorting options back to the server.

I will create an issue for it and have a think about how we might solve it.

Johan · February 22, 2018, 8:10pm

Hello Dan,

not good news. The problem is coming with the name of the column. It is possible to create more than 33 columns (just tried it) but with very short naming. The problem is that I can not use it because more people use the Xibo system and they need to know the name of the column. A. number is not clear enough.

Hope to get this fixed soon. maybe it is possible to manually set the filter option from the dataset view to disable for some of the columns? this will make the url shorter.

dan · February 22, 2018, 8:19pm

I can appreciate that - I don’t think we envisaged anyone using it with quite so many columns!

We will do our best to get it fixed, but there will probably be some trade off, like not being able to sort the columns.

I think what needs to happen is that this code here:

github.com

xibosignage/xibo-cms/blob/develop/views/dataset-dataentry-page.twig#L103


    }
});


var table = $("#datasets").DataTable({ "language": dataTablesLanguage,
    serverSide: true, stateSave: true, stateDuration: 0,
    filter: false,
    searchDelay: 3000,
    "order": [[ 0, "asc"]],
    ajax: {
        "url": "{{ urlFor("dataSet.data.search", {id: dataSet.dataSetId}) }}",
        "data": function(d) {
            $.extend(d, $("#datasets").closest(".XiboGrid").find(".FilterDiv form").serializeObject());
        }
    },
    "columns": cols
});


table.on('draw', dataTableDraw);
table.on('processing.dt', dataTableProcessing);


var dataSetEditFormOpen = $.debounce(500, true, function() {

returns something like:

var data = {};
data.draw = dataToSend.draw;
data.length = dataToSend.length;
data.start = dataToSend.start;
data.order = dataToSend.order;
data.columns = [];
$.extend(data, $("#datasets").closest(".XiboGrid").find(".FilterDiv form").serializeObject());
return data;

Johan · February 22, 2018, 8:25pm

i don’t have any program skills but is it possible to add a variable option when you create new column like. :
add filter to datasetview?

dan · February 22, 2018, 8:29pm

Yeah absolutely that is possible - I was just looking to give you a short term workaround. No problem at all if its not something you are comfortable hacking around with.

We’ve got it logged now and will fix asap - workaround at least in the next release.

Johan · February 22, 2018, 8:32pm

thanks for the feedback! I’m starting to use the dataset with 60 client’s. Layout is ready and
I should go live next week Wednesday. Do you think you manage that?

Johan · February 26, 2018, 10:59pm

Received the update and the datasetview works after ticking of the show filter option but a new error popups :

DataTables warning: table id=datasets - Requested unknown parameter ‘USB 3.0’ for row 0. For more information about this error, please see http://datatables.net/tn/4

tried to copy to new dataset, same error (empty dataset)
tried to remove the column with the name from the error “usb 3.0” but the
error comes again with the name from the next column.

checked with phpmyadmin but there is no row0 in the dataset?

data is viewable en editable in the dataset but the layout does not load the dataset.

Johan · February 26, 2018, 11:17pm

got it. you can’t use a “.” in the column name.
thanks again for the update!