Clone data from one DB to other

@Sinnbeck We wont with that logic because will do it all automatically, at 14:00 will do the clone and at 14:15 will make some update queries in destination DB all of that with cronjob , dont know why im getting that error can you help me please?

@Chris1989 i would still do the cloning using mysqldump.

Anyways. What is this?

$table->{'Tables_in_destination'}

That does not make sense to me

@Sinnbeck How can i define the names of tables to check? when i put only $table i get error : Object of class stdClass could not be converted to string

@Chris1989 what does this give you?

$tables = $destination->statement('SHOW TABLES');
dd($tables);

@Sinnbeck True

@Chris1989 and

$tables = $destination->select('SHOW TABLES');
dd($tables);
 

@Sinnbeck

array:33 [▼
  0 => {#4161 ▼
    +"Tables_in_pms_ergani_destination": "active_personnel"
  }
  1 => {#4160 ▼
    +"Tables_in_pms_ergani_destination": "afm_import"
  }
  2 => {#4159 ▶}
  3 => {#4158 ▶}
  4 => {#4157 ▶}
  5 => {#4156 ▶}
  6 => {#4155 ▶}
  7 => {#4154 ▶}
  8 => {#4153 ▶}
  9 => {#4152 ▶}
  10 => {#4151 ▶}
  11 => {#4150 ▶}
  12 => {#4149 ▶}
  13 => {#4148 ▶}
  14 => {#4147 ▶}
  15 => {#4146 ▶}
  16 => {#4145 ▶}
  17 => {#4144 ▶}
  18 => {#4143 ▶}
  19 => {#4142 ▶}
  20 => {#4141 ▶}
  21 => {#4140 ▶}
  22 => {#4139 ▶}
  23 => {#4138 ▶}
  24 => {#4137 ▶}
  25 => {#4136 ▶}
  26 => {#4135 ▶}
  27 => {#4134 ▶}
  28 => {#4133 ▶}
  29 => {#4132 ▶}
  30 => {#4131 ▶}
  31 => {#4130 ▶}
  32 => {#4129 ▶}
]

@Chris1989 hm. What if you do dd() on $table in the loop?

@Sinnbeck In foreach dd($table) gives me only that: { #4200 ▼ +"Tables_in_pms_ergani_destination": "active_personnel" } That is only one table

@Chris1989 so did you try this?

$table->Tables_in_pms_ergani_destination

@Sinnbeck Now seems to proceed, but im geting error with drop tables: SQLSTATE[23000]: Integrity constraint violation: 1217 Cannot delete or update a parent row: a foreign key constraint fails (SQL: DROP TABLE IF EXISTS annexes) is there any way to avoid that errors? or to make more simply is there a way without check to drop all of Tables and then to insert my tables from Source?

@Chris1989 just disable them in the script https://laravel.com/docs/9.x/migrations#toggling-foreign-key-constraints

@Sinnbeck I fixed the drop, Now the last problem is on insert tables and data, i get error : Object of class stdClass could not be converted to string also

i made dd on that part:

	$tables = $source->select('SHOW TABLES');
		
			foreach ($tables as $table) {

				$tableName = $table->Tables_in_pms_prod_source;
			  
				$sourceData = $source->table($tableName)->get();
				dd($sourceData);  
				$destination->table($tableName)->insert((array) $sourceData); 		
			
			}

also the output is :

Illuminate\Support\Collection {#4200 ▼
  #items: array:745 [▼
    0 => {#4160 ▼
      +"id": 1
      +"afm": 150601068
      +"code_number": "19-01-23-0302-30037"
      +"last_name": "ΑΓΓΕΛΟΠΟΥΛΟΥ"
      +"first_name": "ΑΝΑΣΤΑΣΙΑ"
      +"company": "SAFETY"
      +"working_dates": "6"
      +"working_hours": "6.6667"
      +"contract_type": "B"
      +"shift_working_dates": "1"
    }
    1 => {#4159 ▶}
    2 => {#4158 ▶}
    3 => {#4157 ▶}
    4 => {#4156 ▶}
    5 => {#4155 ▶}
    6 => {#4154 ▶}
    7 => {#4153 ▶}
    8 => {#4152 ▶}
    9 => {#4151 ▶}
    10 => {#4150 ▶}
    11 => {#4149 ▶}
    12 => {#4148 ▶}
    13 => {#4147 ▶}
    14 => {#4146 ▶}
    15 => {#4145 ▶}
    16 => {#4144 ▶}

@Chris1989 just do $sourceData->toArray()

@Sinnbeck Unfortunately i made that and im getting the same error:

	$destination->table($tableName)->insert($sourceData->toArray());

@Chris1989 if you dd() that what do you get?

@Sinnbeck Good Morning and thanks for help! If i make dd($destination); i get error: Object of class stdClass could not be converted to string

@Chris1989 i meant $sourceData->toArray()

@Sinnbeck the dd( $sourceData->toArray()); gives me same output with $sourceData = $source->table($tableName)->get(); output :

array:745 [▼
  0 => {#4160 ▼
    +"id": 1
    +"afm": 150601068
    +"code_number": "19-01-23-0302-30037"
    +"last_name": "ΑΓΓΕΛΟΠΟΥΛΟΥ"
    +"first_name": "ΑΝΑΣΤΑΣΙΑ"
    +"company": "SAFETY"
    +"working_dates": "6"
    +"working_hours": "6.6667"
    +"contract_type": "B"
    +"shift_working_dates": "1"
  }
  1 => {#4159 ▶}
  2 => {#4158 ▶}
  3 => {#4157 ▶}
  4 => {#4156 ▶}
  5 => {#4155 ▶}
  6 => {#4154 ▶}
  7 => {#4153 ▶}
  8 => {#4152 ▶}
  9 => {#4151 ▶}
  10 => {#4150 ▶}
  11 => {#4149 ▶}
  12 => {#4148 ▶}
  13 => {#4147 ▶}
  14 => {#4146 ▶}
  15 => {#4145 ▶}

@Chris1989 that looks like an array of arrays to me. Are you sure that what causes the error?

@Sinnbeck The problem starts when the code goes to $destination to insert the data on each table, but the variables $tableName , $sourceData gives data on dd() dont know please look my updated code if i make something wrong :

$source  = DB::connection('mysql');
$destination  = DB::connection('mysql_destination');
$tables = $source->select('SHOW TABLES');
			
				foreach ($tables as $table) {
 
					$tableName = $table->Tables_in_pms_prod_source;
					
					$sourceData = $source->table($tableName)->get();
					
					//dd($sourceData->toArray());

					$destination->table($tableName)->insert($sourceData->toArray());
 
				}

@Chris1989 so it does not give an error if you comment that put?

//$destination->table($tableName)->insert($sourceData->toArray());

@Sinnbeck Exact if i comment that and make dd on $sourceData->toArray()); i get the tables, But i notice that if dont make dd on $sourceData->toArray()); then i get This page isn’t working127.0.0.1 is currently unable to handle this request. HTTP ERROR 500 Maybe has few data and get error when try to insert?

@Chris1989 so new error all of the sudden? And this sort of stuff should be run using an artisan command or a job.

@Sinnbeck Is not exact new error, simply getting error on fetching because too many data, when i DD that working very well. Tried that command $destination->table($tableName)->insert(json_decode(json_encode($sourceData),true)); And seems working the query but doesnt find tables, the error now is :

SQLSTATE[42S02]: Base table or view not found: 1146 Table 'pms_ergani_destination.active_personnel' doesn't exist (SQL: insert into `active_personnel` (`afm`, `code_number`, `company`, `contract_type`, `first_name`, `id`, `last_name`, `shift_working_dates`, `working_dates`, `working_hours`) values (150601068, 19-01-23-0302-30037, SAFETY, B, ΑΝΑΣΤΑΣΙΑ, 1, ΑΓΓΕΛΟΠΟΥΛΟΥ, 1, 6, 6.6667)

So in that function that i wrote above is there any easy way to create if dont exist that tables before insert data? because the table names already fetching in that command: $tableName = $table->Tables_in_pms_prod_source;