I am using Laravel 5.4 with PHP 7.2 all within xampp. I am trying to truncate a temporary table and then insert the new data from a MySQL View. The code executes and does exactly what I want it to do, but it says there is an error -
SQLSTATE[HY000]: General error (SQL: Insert Into prod_joins_temp (date,code,sid,time_start,time_end,made,firsts,seconds,p_broken,q_broken,g_broken,prod_id) Select date,code,sid,time_start,time_end,made,firsts,seconds,p_broken,q_broken,g_broken,prod_id From prod_joins)
Initially I had the following error General error: 1615 Prepared statement needs to be re-prepared after inserting two columns to my View: a time_start and time_end column.
I tried deleting the two columns to see if that was the issue, but now all I get in the dreaded general error.
I tried this answer Laravel: General error: 1615 Prepared statement needs to be re-prepared. It didnt work and according to the comments it will cause problem ak validations.
I have found this answer PDO error: “ SQLSTATE[HY000]: General error ” When updating database which is the exaclty the same issue to my problem, but it doesnt answer my question because I am not using fetchAll() nor am I using any duplicated variables of any variables at all.
I have tried two ways of achieving my goal as follows.
Truncate table prod_joins_temp
ProdJoinsTemp::truncate();
Then populate table prod_joins_temp from the view prod_joins
DB::select('Insert Into prod_joins_temp (date,code,sid,time_start,time_end,made,firsts,seconds,p_broken,q_broken,g_broken,prod_id) Select date,code,sid,time_start,time_end,made,firsts,seconds,p_broken,q_broken,g_broken,prod_id From prod_joins');
The other way which gives me exactly the same error is as follows
drop table prod_joins_temp if it exists
\Schema::dropIfExists('prod_joins_temp');
Then create table prod_joins_temp from the view prod_joins
DB::select('CREATE TABLE prod_joins_temp AS SELECT * FROM prod_joins;');
Here is the code that makes the view
SELECT
`scorecard54`.`production`.`date` AS `date`,
`scorecard54`.`products`.`code` AS `code`,
`scorecard54`.`production`.`sid_production` AS `sid`,
`scorecard54`.`production`.`time_start` AS `time_start`,
`scorecard54`.`production`.`time_end` AS `time_end`,
`scorecard54`.`production`.`made` AS `made`,
`scorecard54`.`qcontrol`.`firsts` AS `firsts`,
`scorecard54`.`qcontrol`.`seconds` AS `seconds`,
`scorecard54`.`production`.`broken_production` AS `p_broken`,
`scorecard54`.`qcontrol`.`broken_qcontrol` AS `q_broken`,
`scorecard54`.`grinding`.`discarded` AS `g_broken`,
`scorecard54`.`products`.`prod_id` AS `prod_id`
FROM
(
(
(
`scorecard54`.`production`
LEFT JOIN `scorecard54`.`products` ON
(
(
`scorecard54`.`production`.`prod_code` = `scorecard54`.`products`.`prod_id`
)
)
)
LEFT JOIN `scorecard54`.`grinding` ON
(
(
`scorecard54`.`production`.`sid_production` = `scorecard54`.`grinding`.`sid_grinding`
)
)
)
LEFT JOIN `scorecard54`.`qcontrol` ON
(
(
`scorecard54`.`grinding`.`tray_id_grinding` = `scorecard54`.`qcontrol`.`tray_id_qcontrol`
)
)
)
WHERE
(
`scorecard54`.`grinding`.`have_qcontrol` = 1
)
And here is the function that drops and creates a table from the view
public function generateTable(Request $request) {
// Drop temp table
\Schema::dropIfExists('prod_joins_temp');
// re create table with the new data
DB::select('CREATE TABLE prod_joins_temp AS SELECT * FROM prod_joins;');
$request->session()->flash('alert-success', 'Success: Reports table regenerated.');
return Redirect::to('/statistics/');
}
I also tried truncating then re-populating with this function
public function generateTable(Request $request) {
// Drop temp table
ProdJoinsTemp::truncate();
// re generate table with the new data
DB::select('Insert Into prod_joins_temp (date,code,sid,time_start,time_end,made,firsts,seconds,p_broken,q_broken,g_broken,prod_id) Select date,code,sid,time_start,time_end,made,firsts,seconds,p_broken,q_broken,g_broken,prod_id From prod_joins');
$request->session()->flash('alert-success', 'Success: Reports table regenerated.');
return Redirect::to('/statistics/');
}
What am I doing wrong here? Is there some sort of cache for views?
