How many pireps are allowed in table pireps

[SKD]

Hi,

 

We have more than 170.000 Pireps in the legacy table.
After we imported to the new v7 table we become this error message.
Is there a limit of 65k pireps ?

[Strider]

Did you try using the command line way of migrating the data? It is also possible it timed out due to the amount of pireps you have. Make sure the settings in your host allow for that.

[DisposableHero]

On 10/10/2023 at 12:45 AM, SKD said:

Is there a limit of 65k pireps ?

Depends on what you are asking, 65k limit is applicable for queries not database entries... So if the above query contains more than 65k entries in that IN (...) statement then yes it may fail, but this can be handled with batch inserts or optimized queries etc.

 

Anyway, database can handle way more than 170k entries, so your pirep count will not be a problem but importing (handling : reading - processing - saving them with php and queries) can be a real pain.

 

I would suggest opening a bug report via GitHub or tag @Nabeel, he was improving the import code, maybe he can check this issue too.

[SKD]

All pireps were imported. The error was shown in the dispobable Basic module (theme).

[DisposableHero]

3 hours ago, SKD said:

All pireps were imported. The error was shown in the dispobable Basic module (theme).

 

Which part ?

 

Send the log please if it is something Disposable related , either theme or module, I need full error details with stacktrace (your laravel log) to find it out (and solve if possible)

[SKD]

Ok i fill my database again and start the migration. I will write back until the migration is done.

 

[DisposableHero]

48 minutes ago, SKD said:

Ok i fill my database again and start the migration. I will write back until the migration is done.

 

 

Probably you will be hitting MySQL/MariaDB's placeholder limits with my module's stat service, it tries to read all accepted pireps and pirep fares to get the pax/cargo counts.

 

If this is the case we can look further to improve that logic with only laravel relationships. Looping thought 170k pireps to get the total count may take some server time though, but we will see how it goes.

 

Another solution would be limiting that feature with 65k pireps, so pax/cgo counts will not work on your setup but will be present on others until they hit 65k pireps

[DisposableHero]

Another solution can be reversing the logic for the stats, instead of using whereIn(...) , I can use whereNotIn(...) with rejected pirep id's array  That will be much less and below 65k I think for everyone.

[SKD]

 

Here is the screenshot from the error.

[DisposableHero]

Yeah, exactly what I though  Below that part, at line 406 you have the passenger and cargo counts of accepted pireps, which uses laravel's whereIn() function and fails because your placeholders passing the maria/mysql 65k limit.

 

If you check the latest update/version of my Disposable Basic module, you will see that I limited it and now it should pass that line, do not cause an exception and work without pax and cgo counts.

 

BTW, my idea reversing the logic causes inaccurate results due to cancelled, deleted, rejected pireps.

 

Hope it helps. (at least until we find a better solution)

[SKD]

Thanks it works.

Pireps an statics in month (see the screens.) are always empty.

 

[DisposableHero]

What is the `submitted_at` date of your pireps ? I am not asking the v5 pireps, we should check v7 tables from now on. As I am pretty sure those stats do work fine, we need to find the difference between an "imported old pirep" and a fresh v7 one to provide a solution (if it is possible of course)

 

Edited October 25, 2023 by DisposableHero

[SKD]

Ah ok. I will check the rows in the tables. 
Perhaps there is a bug in the importer of the old v5 schema.

 

[DisposableHero]

4 hours ago, SKD said:

Ah ok. I will check the rows in the tables. 
Perhaps there is a bug in the importer of the old v5 schema.

 

 

Unfortunately yes, your imported pireps do not have `submitted_at` data  And this is causing the issue because my modules rely on the submit date, not creation date for operational reasons. A kind friend provided some v5 and imported v7 data yesterday night and I saw what I need (Summary; Legacy importer needs to be updated for at least two missing fields)

 

By the mean time, you can copy the created_at field as your submitted_at field in your v7 database with a query (cause the future update of the imported will do the exact same thing)... This will solve your statistic problems, something like below may work;

 

UPDATE pireps SET submitted_at = created_at WHERE submitted_at IS NULL;

 

Good luck

[SKD]

Yes this was the key !!
I try the update and it will work. 
Also i must update the field "status". The default value of this field is SCH => scheduled.
But your controller shows only (this is absolutly currect) the status auf ARR.
 

UPDATE pireps SET status = 'ARR';

WIth the above update the pireps and the statistik work fine !

Many thanks to your support (It makes a smile on my face)

SKD

Edited October 26, 2023 by SKD

[DisposableHero]

Great news then @SKD 

 

I will check the status field from v5 data and provide a solution for v7 too (not for you anymore but for future Legacy Imports).

 

Enjoy v7

 

Side Note: Status should be ARR (ARRIVED) for v7 pireps, it is designed like that. When a pirep is finalized/closed by the pilot, it gets this status. So we should set this for imported pireps, even if they are rejected, they should be arrived. Thanks for sharing your findings on this.

[SKD]

I had to clear the cache at the server side.

(php artisan cache:clear) 

 

The statistics still had the old values and now it works

 

Thanks

Edited October 27, 2023 by SKD