Clone data from one DB to other | Laravel | Page 1

vast gorge Feb 17, 2023, 10:03 PM

#

Im trying to insert tables and data from one db to another but im getting error :
Object of class stdClass could not be converted to string

My Function:

            $source  = DB::connection('mysql');
            $destination  = DB::connection('mysql_destination');
         

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


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

little stratus Feb 17, 2023, 10:18 PM

#

Wouldn't this be easier using a straight databse dump? Or are you building a package?

vast gorge Feb 18, 2023, 7:47 AM

#

Want that logic because i use it inside cronjob

deft surge Feb 18, 2023, 7:48 AM

#

have you tried ```php
$sourceData->toArray()

vast gorge Feb 18, 2023, 7:54 AM

#

i change to $destination->table($tableName)->insert($sourceData)->toArray() and i get Argument 1 passed to Illuminate\Database\Query\Builder::insert() must be of the type array, object given, called in C:\laragon\www\projects\laravel\personel-management-system-7.4-cx\app\Http\Controllers\WorkersController.php on line 82

deft surge Feb 18, 2023, 8:03 AM

#

vast gorge i change to `$destination->table($tableName)->insert($sourceData)->toArray()` ...

now we need to drop those indexes, try

array_values($sourceData->toArray())

vast gorge Feb 18, 2023, 8:05 AM

#

i made that but still get the same error, look my updated code :

    $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(array_values($sourceData->toArray()));
 
                }

#

To help you to understant if i //dd($sourceData->toArray());
i get that output, so data comming through 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 ▶}

deft surge Feb 18, 2023, 8:07 AM

#

there's sth else that's missing, wait

#

oh sorry try this

insert(json_decode( json_encode($sourceData),true))

#

I think there was some other helper but I can't remember
this ought to do it

vast gorge Feb 18, 2023, 10:04 AM

#

@deft surge Hmm now im getting sql error like this:

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), (32147460, 16-01-23-0302-30024, SAFETY, B, ΕΛΕΝΗ, 2, ΑΘΑΝΑΣΙΟΥ, 1, 6, 6.6667), (49188410, 00-07-03-0606, SAFETY, A, ΑΡΓΥΡΗ, 3, ΑΙΒΑΛΙΩΤΗ, 2, 5, 8), (122498832, 01-11-03-0703, SAFETY, B, ΗΛΙΑΣ, 4, ΑΛΑΜΑΡΑΣ, 1, 6, 6.6667), (121121860, 00-07-03-0601, SAFETY, A, ΑΓΓΕΛΙΚΗ, 5, ΑΝΑΓΝΩΣΤΟΥ, 2, 5, 8), (54314485, 01-11-03-0660, SAFETY, B, ΣΩΤΗΡΗΣ, 6, ΑΝΔΡΙΟΠΟΥΛΟΣ, 1, 6, 6.6667), (55246629, 00-07-03-0596, SAFETY, A, ΚΩΝΣΤΑΝΤΙΝΟΣ, 7, ΑΡΓΥΡΟΠΟΥΛΟΣ, 2, 5, 8), (66897604, 01-11-03-0821, SAFETY, B, ΜΑΡΚΟΣ, 8, ΑΡΜΕΝΑΚΗΣ, 1, 6, 6.6667), (66698340, 19-01-23-0302-30035, SAFETY, B, ΚΑΡΛΟΤΤΑ, 9, ΒΕΝΑΡΔΟΥ, 1, 6, 6.6667), (72810882, 00-37-03-0594, SAFETY, B, ΝΙΚΟΛΑΟΣ, 10, ΒΛΑΧΑΚΗΣ, 1, 6, 6.6667), (115133889, 01-11-03-0850, SAFETY, C, ΑΝΔΡΕΑΣ, 11, ΒΡΑΤΣΙΝΗΣ, 5, 2, 7), (53653320, 01-11-03-0628, SAFETY, B, ΚΩΝ ΝΟΣ, 12, ΒΡΟΝΤΑΚΗΣ, 1, 6, 6.6667), (50492618, 00-07-03-0605, SAFETY, A, ΜΙΧΑΗΛ, 13, ΓΕΩΡΓΙΟΥ, 2, 5, 8), (50972595, 01-11-03-0746, SAFETY, B, ΙΩΑΝΝΗΣ, 14, ΓΙΑΚΑΛΟΣ, 1, 6, 6.6667), (145015270, 19-01-23-0302-30030, SAFETY, O, ΔΗΜΗΤΡΙΟΣ, 15, ΓΙΑΝΝΑΚΟΠΟΥΛΟΣ, 1, 6, 6), (52248377, 02-35-03-0730, SAFETY, B, ΔΗΜΗΤΡΙΟΣ, 16, ΓΙΑΝΝΟΥΧΟΣ, 1, 6, 6.6667), (110542402, 01-11-03-0663, SAFETY, B, ΧΡΗΣΤΟΣ, 17, ΓΚΑΡΓΚΑΝΙΤΗΣ, 1, 6, 6.6667), (52549660, 00-08-03-0664,

deft surge Feb 18, 2023, 10:06 AM

#

you're inserting in a table that doesn't exist.
create tables too.

deft surge Feb 18, 2023, 10:07 AM

#

vast gorge Im trying to insert tables and data from one db to another but im getting error ...

create tables if doesn't exist before inserting

vast gorge Feb 18, 2023, 10:44 AM

#

@deft surge
I was hopping that my code does that can you make in my code a example please?

#

Because the data of tables comming corectly with that variable : $tableName = $table->Tables_in_pms_prod_source

compact tinsel Feb 18, 2023, 11:40 AM

#

You can make a custom artisan command to do that, I'm using mysqldump in a docker environment to backup databases; you are also able to use pipes, so migrating the database one connection to another sholdn't be an issue, just the tip, you shouldn't use the environment MYSQL_PWD in that case, maybe you can use it like MYSQL_PWD=password1 mysqldump ... | MYSQL_PWD=password2 mysql .

use Symfony\Component\Process\Process;
$process = Process::fromShellCommandline(join(" ", [
                "mysqldump",
                "--force",
                "--skip-comments",
                "--complete-insert",
                "--host={$connection['host']}",
                "--port={$connection['port']}",
                "--user={$connection['username']}",
                "--extended-insert",
                "--single-transaction",
                "--quick",
                "--databases",
                $connection['database'],
                "|",
                "gzip",
                "-6",
                ">",
                $path,
            ]), null, [
                "MYSQL_PWD" => $connection['password'],
            ]);

            //# Long running task.
            $process->setTimeout(3600);

            //# Execute the command in a shell.
            $process->run();

vast gorge Feb 18, 2023, 2:40 PM

#

@compact tinsel I think its very different way i make something like this

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

                    $createTableQuery = $source->select("SHOW CREATE TABLE $tableName");
                    dd($createTableQuery);
                

                    $createTableQuery = $createTableQuery[0]->{'Create Table'};
                    //dd($createTableQuery);
                    
                    $destination->statement($createTableQuery);

                
                    $data = $source->table($tableName)->get();
                    
                    $destination->table($tableName)->insert($data->toArray());
                }

#Clone data from one DB to other