Problem segmenting [EMPTY] values in date custom fields

[Groundarker]

Good evening guys,
I would like to point out one last rather strange problem that I ran into.

Until yesterday everything worked correctly but from today the segmentation for empty values in some custom fields we created (precisely to collect the last opened date, last click date and last sent date) returns 0 subscribers when in reality the condition returned many contacts with the created filter.

Given that we have enabled the automatic field synchronization function (by setting the command to yes) and also launched the cron every hour to synchronize the custom fields with "sync-lists-custom-fields".

I repeat everything worked correctly. Inserting this filter that I paste below showed me many subscribers:




I wanted to learn more personally by going to MySQL and launching a query in the "ms_list_field_value" table indicating in the "field_id" column the identification number of the custom field and here too it returned 0 fields that are empty. The strange thing is that yesterday thanks to the segmentation he was able to recover the fields [EMPTY] I don't know how such a thing could have happened.

Now launching the command to repopulate the custom fields with the value [EMPTY] after the cron is finished, the situation is practically the same, ie that the software has not evaluated [EMPTY] the fields where the value is missing.

If, on the other hand, I try to segment with the following condition: "FNAME" field is Empty, the subscribers appear where there are no names of people.

P.S .: I don't know if it helps but I use the default TAGs [SUBSCRIBER_LAST_OPEN_DATETIME], [SUBSCRIBER_LAST_CLICK_DATETIME], [SUBSCRIBER_LAST_SEND_DATETIME] to populate the database where there have been openings, clicks and sends of campaigns with the date. Since the problem is only with these fields where there is a default value I think it is something related to this.

 

[Groundarker]

Guys for this error I have found the solution myself and I am writing it here in case someone else needs to solve the same problem as mine!

Having passed millions of contacts and many custom fields, the software was no longer able to create a value for the "value_id" column of the "ms_list_field_value" table.
This is because the Data type was set to INT and I had to change it to BIGINT to be able to exceed the range of values that the software creates automatically!

This I was able to find out by running the "sync-lists-custom-fields" in --verbose = 1 and the error was the following:




@twisted1919 and @laurentiu if it can be useful in case someone needs it in the future, I hope to have helped you in my small way!

 

[twisted1919]

@Groundarker - We figured out this issue a while back(i believe more than one year ago) and new installs since then will get that column to be a big integer.

Existing installs, when they hit this issue, they'll have to do exactly the same thing as you did.
The reason why we don't do this automatically is because altering a large table, can take even days...

 

[Groundarker]

Ah here! Thanks @twisted1919 now it is clear forgive me for the post then, I hope it can still be useful to someone who has had the same problem as me!
In the same regard, I wanted to ask you if there are other changes that you have made and that maybe existing installations need to be done to avoid problems? (at the level of MySQL I speak)

For example, we have noticed that in the information modification area of the new registered customer, with the subsequent versions released by you, a field has appeared that allows you to choose the possibility of inactivating the customer. It happens that when I try to change information or change the customer group and save the changes I get this error:

 

[twisted1919]

In the same regard, I wanted to ask you if there are other changes that you have made and that maybe existing installations need to be done to avoid problems?

This is the only one, generally we port all changes between the updates/new versions unless they are super heavy database changes like this one, but as I said, this is the only exception so far. 2.x has more of these, but that's something different, in 2.x we don't have to keep BC that much like we do here.

It happens that when I try to change information or change the customer group and save the changes I get this error:

This should not happen, can you check and make sure your database has this field? the customer table. If you find this column, then the fix is relatively simple, you just need to clear the caches of the application, and you can do that by using the "clear-cache" command, but I can instruct you how to do this when you confirm/infirm the existence of that column.

 

[Groundarker]

Questo è l'unico, generalmente portiamo tutte le modifiche tra gli aggiornamenti / nuove versioni a meno che non siano modifiche al database super pesanti come questa, ma come ho detto, questa è l'unica eccezione finora. 2.x ha più di questi, ma è qualcosa di diverso, in 2.x non dobbiamo mantenere BC così tanto come facciamo qui.


Questo non dovrebbe accadere, puoi controllare e assicurarti che il tuo database abbia questo campo? la tabella dei clienti. Se trovi questa colonna, la correzione è relativamente semplice, devi solo svuotare le cache dell'applicazione e puoi farlo usando il comando "clear-cache", ma posso spiegarti come farlo quando confermare / negare l'esistenza di quella colonna.

@twisted1919 What is the column called?
I don't think there is because I do cache cleanup every day and to sync empty fields or other fields that interest me.
I have had the problem with this field for a long time, I don't know in which version you have integrated it!
Can you tell me how the column or SQL query should be called to create it?

Thank you

 

[twisted1919]

What is the column called?

The column name is called: inactive_at and it has been added in 1.9.0, as shown below:

--
-- Update sql for MailWizz EMA from version 1.8.9 to 1.9.0
--

--
-- Table `customer`
--
ALTER TABLE `customer` ADD `inactive_at` DATETIME NULL DEFAULT NULL AFTER `last_login`;

The above also contains the query you have to run to add the column, if missing. But again, it should not be missing...

 

[nadworks]

So my ongoing issue with [EMPTY] definitely not working in my segmentations (ticket #PVAVJ7T0) could be related to this???
Is there a solution that does not involve the above operation, which - I'm afraid - I'm not too comfortable tackling?

 

[twisted1919]

@laurentiu - This one.

 

[laurentiu]

So my ongoing issue with [EMPTY] definitely not working in my segmentations (ticket #PVAVJ7T0) could be related to this???
Is there a solution that does not involve the above operation, which - I'm afraid - I'm not too comfortable tackling?

Please check response in your ticket