INSERT put some empty data from db1 to db2 where source data is not empty

@Tray2 I agree with you, but the only problem is that in the original database where I get the data and INSERT in a new DB, the data is filled and are not EMPTY otherwise I will not open a new discussion and asking for a solution

@usertxr how do you know? Do you check table one at the same second the error occurs? Or some time later

@usertxr

in the original database where I get the data and INSERT in a new DB, the data is filled and are not EMPTY

It is filled whenever you check it; but not whenever the $data query is executed. This is the same issue as before; you only ever INSERT once; you never update existing records with changes in the mysql2 database, so the data in the mysql database is stale.

@tykus I have made some changes and now every record when updated by users gets status = 'Y'. Whereas every record that has existed before in db and has not been filled has status = 'N'. So query should not take records that were previously empty but will only take them when they have status = 'Y'.

@Sinnbeck I can not check the table in the same second when the error occurs because there is a lot of data and it can happen anytime during the 9 hours that users add data. But I did a log query and from the logs I read I realized that INSERT simply takes them as empty values even though they are filled in the original table (source table).

But there are cases when for example, a record is filled in the original table but in db when inserted all field except student_id are empty, also cases when all fields are filled except for some specific fields like las_name that I showed you in the log above. I know this is a strange case and I do not know what the problem may be

@usertxr It sounds quite implausible that it has a value but ignores it.

But try adding you own log

if (!$aStudent->first_name) {
   Log::error('NOOOO... ' . $aStudent->id . ' with ' . $aStudent->student_id . ' is missing firstname!' ;
}

if ( !$aStudent->last_name) {
   Log::error('NOOOO... ' . $aStudent->id . ' with ' . $aStudent->student_id . ' is missing lastname! ';
}

@usertxr This tells you that there is no value for last name

["421", //Student id
"name", //First name
"", // Last name if empty

"38","2022-01-19 14:13:35"]

So you need to validate all the inserts made by the users into the db and make sure all fields are filled in. The fields should by the way not be nullable.

@Tray2 This tells you that there is no value for last name

I know this. But when i check same record on source db i see last_name filled. This is problem.

And about validating and nullabme fileds Source db came configured not by me but by the system itself that users use for work. I just made a copy of it for db2

@Sinnbeck It sounds quite implausible that it has a value but ignores it. I know this. Therefore I have 3 days that I can not fix this problem. I tryed this but now i need to wait until such a case occurs again

@usertxr are the first_name and last_name nullable in the original table?

You can try

$data = DB::connection('mysql2') ->table('students') ->where('first_name', '<>', '')->where('last_name', '<>', '')->where('status', '=', 'Y') ->get();
//or
$data = DB::connection('mysql2') ->table('students') ->whereNotNull('first_name)->whereNotNull('last_name')->where('status', '=', 'Y') ->get();

@Sinnbeck Yes they are nullable. I have tryed this before. And if i do this i get no empty record on db2.

But again the same problem remains. Because it does not get the data in db2, but if I check in db1 they are filled

@usertxr Ok so once a record has been added to mysql2 it can never be changed? There is no way of changing your name?

@usertxr What happens if you run this query on the sorce database?

SELECT * FROM students WHERE name IS NULL OR last_name IS NULL;

@Sinnbeck Only if i change manually. Because I'm building a management system and I can edit the data in mysql2 from the datatable I use

@Tray2 receive only data that has not been updated by users

@usertxr Can you show how you are inserting data into the first database table?

@usertxr Then you have the answer

@usertxr so, the two database tables will be out of sync by design?

@usertxr But you just said that the students cannot change the data?

data that has not been updated by users

Only if i change manually.

@Sinnbeck No i cant do this couse it is not on my heand. I am developing this system for menaging data. And the data in db1 is inserted by another system

@Sinnbeck data that has not been updated by users NO NO. db1 by defauld first table have student_id only and other fileds update when user add data on this db by a web form.

Only if i change manually. And this is for db2. I can update manually each row of table

@tykus No i dont think so. Until now on db2 are inserted about 3,100 record sucesfouly without any problem, and about 40 record with this problem witch i am trying to fix

@usertxr Why is there data that has not been updated? There should never be a record that only contains a student_id and have the rest of the information blank. The student_id should be created when the record is about to be inserted.

@Tray2 To explain this I have to explain how db of the other system works. So the table is filled only with student_id for because such is the structure of db of the other system, ie the original db. An admin puts there a list of student_id and this system distributes these student_id to 10 users currently equally, and each of these 10 users has student_id based on which they fill in some data forms that are <option> value </ option> in web forms. after they update each student_id it goes back to the same db.table but this time it goes full of data

@usertxr Whoever designed that system left it wide open for problems like yours.

The problem is most likely that when you run your job you get a list with unfinished students, then you insert these into your new database. So you need to make sure that all you either only retrieve finished students or that you don't insert them in the new database. I would not retrieve them and run the job every ten minutes or so to see if they have been updated properly.

@Tray2 i get only student with status = Y. And record will get status= Y only if will updated with data . So this mean i get only student with data filled

@Tray2 I would also rule out the possibility that cronjob is performed before db1 is filled. because if it were so it would not take any cell but in fact when inserted some cells are filled while some are empty. always change never are the same empty

@usertxr Not if you write the the query correctly.

@Tray2 What do tou mean with if i write the query correctly

@usertxr This get's all the records that has a Y in the column status.

 DB::connection('mysql2') ->table('students') ->where('status', '=', 'Y') ->get();

Add additional wheres to it so that the value must be filled in. Might need to change if the value is null and not an empty string.

 DB::connection('mysql2')
	 ->table('students') 
	->where('status', '=', 'Y')
	->where('name', '!=', '')
	->where('last_name', '!=' , '')
	 ->get();

@Tray2 I have tryed this before. Only it does not insert the records it now inserts with blank fields

@usertxr Of course not and that is the point isn't it?

@Tray2 the point is to get all data correct couse on db1 after update should not have any empty record with status =y

@usertxr

Yes, and the only way to do that is to select the ones that are complete and then iterate until every record is moved.

@tykus They are the same. This is also explained by the fact that most of the data goes to db2, only a few records get stuck and go through the empty field. I'm sure the data is the same because users update the fields from a dropdwn and the values are the same for all records are (optional limited)

@usertxr 🤷‍♂️ stumped

@tykus I know it's weird that most of the data goes smoothly while a small part brings me this problem

@tykus Okay I'm trying to explain it in the easiest way possible. Then I am building a system for managing the data of another system database . I take this data and insert it in a db of mine that is db2. And the problem is that in db1 all the data is complete when users update them. And when they go to db two a small part of this data brings me this problem that I showed you above.

I tried to explain it in the easiest way possible because there is a lot to say since I have been developing this for almost 7 months, there are many complications. that some of them you have helped me with money

@Sinnbeck Thhanks for sugesstion I appreciate. But I have a few days that I am dealing with this and reading the log. But I can understand the problem because in every log I have seen that everything and function run correct

@usertxr you have already shown an example of it not working correct?

insert into student (student_id, first_name, last_name, age, created_at) values (?, ?, ?, ?, ?) ["421","name","","38","2022-01-19 14:13:35"]

Your assumption is that students table on mysql2 never has empty cells. I am giving you a way to prove that isn't the case

@Sinnbeck the first database managed by another system in this case has last_name completed and I can check this. But when the last name is inserted in db2 last_name comes as empty

@usertxr What do you mean by

No came up with blank data.

Can you show what came in that was bad?

@Sinnbeck I mean. That a user just did put the data in the record with student_id = 501 while when this record was inserted in second db was inserted with empty data

And query shows no log::error

@usertxr so your own database inserted a row where all cells were empty? And if you look in the laravel.log there are no new entries?

@Sinnbeck yes my own db inserted a row where some cells are empty this time. And when i check on laravel.log i see success mesaage i have writed if record will inserted on db. But i dont see any error from this log query

if (!$aStudent->first_name) {
   Log::error('NOOOO... ' . $aStudent->id . ' with ' . $aStudent->student_id . ' is missing firstname!' ;
}

if ( !$aStudent->last_name) {
   Log::error('NOOOO... ' . $aStudent->id . ' with ' . $aStudent->student_id . ' is missing lastname! ';
}

@usertxr what cells?

@Sinnbeck With cells i mean column of table

@usertxr I know... What columns? first_name and last_name? or age?

@Sinnbeck Ah sorry. This time i get first_name and age empty but last_name is filled

@usertxr Did you test if you are able to log anything at all? If you cant, then the test is false.

Log::error('Just testing');

@Sinnbeck Yes i have tryed before and it works

@usertxr Can you show your current code for the function? I dont see any way that a variable can have a value in one line and then no value in the next line of code (unless you change it)

@Sinnbeck I know it's weird. But so it happens sometimes it happens so sometimes all the fields in a row come empty sometimes

The actual function is quite complex and will only confuse you if it displays them all

@usertxr maybe it's time you gave upsert a shot. I was able to find a package that can add upsert to your laravel version. Install version 1.2

https://github.com/staudenmeir/laravel-upsert/tree/1.2

@Sinnbeck Can i add upsert for laravel 6.X and php 7.2?

@usertxr yeah version 1.2 seems to work with exactly that https://github.com/staudenmeir/laravel-upsert/blob/1.2/composer.json#L13

@Sinnbeck but it require me php 7.3

Problem 1
    - mockery/mockery is locked to version 1.4.4 and an update of this package was not requested.
    - mockery/mockery 1.4.4 requires php ^7.3 || ^8.0 -> your php version (7.2.5) does not satisfy that requirement.
  Problem 2
    - league/flysystem 1.1.5 requires ext-fileinfo * -> it is missing from your system. Install or enable PHP's fileinfo extension.
    - laravel/framework v6.20.34 requires league/flysystem ^1.1 -> satisfiable by league/flysystem[1.1.5].
    - laravel/framework is locked to version v6.20.34 and an update of this package was not requested.

i run this

composer require staudenmeir/laravel-upsert:"^1.0"

@usertxr install the file info php extension and then try

composer require staudenmeir/laravel-upsert:"1.2"

@Sinnbeck t require a httpd restart so i should do after 1 hour this.

But Just to ask.. Are similar updateOrInsert and upsert?

@usertxr upsert does it all in one query. You make the student_id column unique. If it already exists the query will just update it with the newest data from mysql2

@Sinnbeck But updateOrInsert i think can do the same but with query builder, as i have . Or i am wrong?

@usertxr it does but in multiple queries

@Sinnbeck Couse now i use updateOrInsert but still problem is same

@usertxr so if you manually run that on a row with missing name, it does not update? If so then we can finally start debugging

@Sinnbeck What do you mean with so if you manually run that on a row with missing name i have replace

DB::connection('mysql')->table('student')->Insert([
//to this
DB::connection('mysql')->table('student')->updateOrInsert([

@usertxr find a row in mysql that is missing something. Then make a simple command to try and copy it again

$student = DB::connection('mysql2')->table('student')->where('student_id', $failedId)->first();
DB::connection('mysql')->table('student')->updateOrInsert(... The data 

@Sinnbeck I tryed but is not updating anything

$student = DB::connection('mysql2')->table('student')->where('student_id', $failed->Id)->first();
if ( !$student ) {
		Log::error ('Noo' . $student)
}

In really i get undifined variable $failed

@usertxr good. Then we can finally recreate the error in a contained space. Can you show the code?

@Sinnbeck i get Undefined variable: failed this errorr

@usertxr $failedId.. Just a variable with the id of the student that failed.

@Sinnbeck same

	Undefined variable: failedid {"exception":"[object] (ErrorException(code: 0): Undefined variable: failedid at /path....

@usertxr and you have set the variable above?

$failedId = 59;

Assuming that student_id 59 failed

@Sinnbeck

this is what i have done

$existing_data_in = DB::table('student')->where("student_id", $aStudent->student_id)->first();
    if ( ! $existing_data_in) {

$student = DB::connection('mysql')->table('student')->where('student_id', $failedId)->first();
if ( !$student ) {
		Log::error ('Noo' . $student)
}

        DB::connection('mysql')->table('student')->updateOrInsert([ // i use update or insert now
            "first_name"        =>$aStudent->first_name,
            "last_name"         =>$aStudent->last_name,
            "age"               =>$aStudent->age,
            "student_id"        =>$aStudent->student_id,
            "created_at"        =>$aStudent->created_at
        ]);

@usertxr you need 2 arrays.. Please read the docs https://laravel.com/docs/8.x/queries#update-or-insert

@Sinnbeck i think this fixed my problem

$existing_data_in = DB::table('student')->where("student_id", $aStudent->student_id)->first();
    if ( ! $existing_data_in) {

        DB::connection('mysql')->table('student')->updateOrInsert([ // i use update or insert now
            "first_name"        =>$aStudent->first_name,
            "last_name"         =>$aStudent->last_name,
            "age"               =>$aStudent->age,
            "student_id"        =>$aStudent->student_id,
            "created_at"        =>$aStudent->created_at
        ],
		[
			'age' => age
		]
);

But is any way i can check by using any log to see if is correct or no?

@usertxr the first array are the conditions used to locate the existing record; I would think that student_id should be the only attribute you would use to locate the existing record. Otherwise you will have potentially many records with the same student_id in the second database because other attribute(s) were different?

DB::connection('mysql')->table('student')->updateOrInsert([
    "student_id" =>$aStudent->student_id,
], [
    "first_name" =>$aStudent->first_name,
    "last_name" =>$aStudent->last_name,
    "age" =>$aStudent->age,
    "created_at" =>$aStudent->created_at
]);

The updateOrInsert method will attempt to locate a matching database record using the first argument's column and value pairs. If the record exists, it will be updated with the values in the second argument. If the record can not be found, a new record will be inserted with the merged attributes of both arguments:

https://laravel.com/docs/8.x/queries#update-or-insert

@tykus It is more complicated than that. Otherwise you will have potentially many records with the same student_id

i check before for dublicated record and i not allow dublicated record on my own db.

But i change my code as you suggest and its okay it work good until now

@usertxr you effectively get the duplicate check for free whenever you use the code I gave you above.

@tykus You mean i dont need this

$existing_data_in = DB::table('student')->where("student_id", $aStudent->student_id)->first();
    if ( ! $existing_data_in) {

@usertxr yes; if you're making that query anyway, then why not use the updateOrInsert instead - otherwise there can be no update because you never run updateOrInsert if there is already a record matching the student_id

@Sinnbeck @tykus And this is not fixing this problem. Still hapend same problem

@usertxr did you get rid of the existing_data_in Query/check?

@tykus Yes I had removed it but it had a weird behavior as it once took the blank record in db2 and then updated it and i had not seen this. But now it's okay. Thanks 😊

@tykus Hello, i know i make best answer but when i remove existing_data_in i get another problem. When i manually update a record on my inserted data (i mean ) on table of db2 when i insert data from source db. So when i update a record here it add 2 records with same student_id? one is updated record what i done manually and one is original record. How should i fix this? I dont want multiple record

@usertxr code?

@tykus

DB::connection('mysql')->table('student')->updateOrInsert([
    "student_id" =>$aStudent->student_id,
	"notes" => 'Default' // i just add this colum as i need like this when record will be inserted
], [
    "first_name" =>$aStudent->first_name,
    "last_name" =>$aStudent->last_name,
    "age" =>$aStudent->age,
    "created_at" =>$aStudent->created_at
]);

If i add notes on array2 it will be always 'Default'. I need to change sometime manually

@usertxr again, anything you place in the first argument array will determine if there is an existing record, i.e. the equivalent of

SELECT * FROM student WHERE student_id = :student_id AND notes = 'Default';

Obviously if there is no existing record where the notes values is 'Default', it will create a new record!

If i add notes on array2 it will be always 'Default'.

Again, the values in the second array are the updates you will make to the found record.

I need to change sometime manually

I thought this was an automated process; what could you manually change?

@tykus I thought this was an automated process; This is automated process just for inserting data on db2. But after this i need to update manually some record. For example i need to let some notes for some students. So i need a column by default have notes: 'Default' (or any word else) and in base on my interes i need to change notes for some students. But i dont need dublicated record when i update notes. How can i do this?

I have an idea to make migration with default column. I mean to set notes->default on migration, and to make php artisan migrate:fresh but this will delete some data and user i have created and i dont want to start over with creating user and to start over from beginin with updating record manually.

So i though a solution it was to add this

->updateOrInsert([
    "student_id" =>$aStudent->student_id,
	"notes" => 'Default' // i just add this colum as i need like this when record will be inserted
], 

But now i see it isnt couse this give me dublicated record

@usertxr you can create a migration to modify the existing column without needing to refresh the database.

Schema::table('student', function (Blueprint $table) {
	$table->string('notes')->default('Default')->change();
});

How you deal with the notes value will need to be teased out further - is there nothing in notes on the source database (mysql2), i.e. you only keep and modify notes on the mysql table?

Make a backup of your database table before altering the table in the event you don something to compromise the existing data

@tykus I have notes only in my own db not on source db. I am not clear if i do this ->default('Default')->change(); do i need to run any artisan comand ?

@usertxr you make a new migration (php artisan make:migration make_default_notes), and making sure you are on the correct connection, alter the existing table.

@tykus I understand this but i mean after adding this

Schema::table('student', function (Blueprint $table) {
	$table->string('notes')->default('Default')->change();
});

on new migration make_default_notes i think i should run a artisan comand and if i do will afect and other migration

@usertxr what??

Of course, you need to run the migration php artisan migrate - if your migrations are up to date it should be the only migration that will execute. You can use the --pretend option to check this.

@sr57 Sorry about that but some i have change some things from this questions and i have tryen another way

@usertxr

Of course it's a good thing to do more tests but

I suggest you to

• don't let answers of people who want to help you without feedback, ask for reformulation if you don't understand

• don't let thread opened if they are no more useful for you

• it can be useful to open a new thread, even if the question is slightly different, but inform the readers of your choice (it's easy to create a link like I do)

@sr57 Yeah you are right, sorry for leting previous thread open. I was just focusing to fix this problem. And thanx for your help and sugesstions