Quick up date (question on count and id's used still stand) but
I modified the query slightly to allow me to load the n_year and id_used values into a result table
I got an error on the GROUP_CONCAT function and so added in the DISTINCT clause,
I set the id_used to TEXT and n_year to MEDIUMINT(9)
INSERT INTO `fao_avg_a`
(
species_code_a,
year_a,
n_years,
id_used,
avg_prod
)
SELECT
x.species_code_a,
x.year_a,
COUNT(*) AS n_years,
GROUP_CONCAT(DISTINCT x.aquaculture_id) AS id_used,
AVG(y.qty_prod) AS `avg_prod`
FROM fao_aquaculture AS x
JOIN fao_aquaculture AS y
ON x.species_code_a = y.species_code_a
AND x.year_a BETWEEN y.year_a AND y.year_a + 5
AND x.year_a > 0
AND x.species_code_a <> NULL
GROUP BY x.species_code_a, x.year_a DESC;
It worked for the Aquaculture data but not the capture table data where I got the group_concat error again:
"ERROR 1260 (HY000): Row 3242 was cut by GROUP_CONCAT()"
The manual says try using
SET [GLOBAL | SESSION] group_concat_max_len = val;
So I added:
SET SESSION group_concat_max_len=15000;
after the GROUP BY clause
system said I had a syntax error but I cannot see it?
SET GLOBAL group_concat_max_len=15000;
Also did not work.
The manual says:
"The protocol limit for max_allowed_packet is 1GB. The value should be a multiple of 1024; non-multiples are rounded down to the nearest multiple"
It also says:
"the default max_allowed_packet value built in to the client library is 1GB, but individual client programs might override this."
On my server settings max_allowed_packet=16M.
So I chose 15000 (a bit below 1024x16)
If its not my syntax or crappy math causing the issue, your earlier advice on leaving default settings well alone is ringing in my ears.
Should I change the max_allowed_packet to 1GB?