Mariadb – Code not working due to collation

collationmariadb

Let me preface my question by saying, that my teacher stared at my code for 2h and could only guess that it might be the collation.

Here's the problem: for an exercise we were asked to create a movie database and then write several procedures. One procedure ist supposed to do the following: – take the id of a movie (as a parameter), the director's last and first name and then test wether the director already is in the table. If the answer is no, the name is added to the table. Either way, the procedure then adds the movieID and the ID of the director to a second table. Here's my code:

delimiter //
create or replace procedure p_director 
( par_movieID integer, 
 par_firstName varchar(50),
 par_lastName varchar(100))
 BEGIN 
 DECLARE var_id  INTEGER; 
DECLARE var_firstName  varchar(100);
DECLARE var_lastName   varchar(100);
DECLARE var_control integer;
DECLARE exit handler for SQLEXCEPTION
Begin
rollback; 
End; 
Start TRANSACTION;

set var_firstName = par_firstName;
set var_lastName =  par_lastName; 

SELECT directorID FROM director 
where lastName = var_lastName AND firstName = var_firstName 
INTO var_control; 

select var_control as debug; 

IF (var_control IS NULL) THEN

select max(directorID)+1 from director into var_id; 

insert into director ( directorid, firstName, lastName) 
values (var_id, par_firstName, par_lastName); 

insert into moviedirector (movieID, directorID) values 
( par_movieID ,var_id) ; 

ELSE 

insert into moviedirector (movieID, directorID) 
values ( par_movieID ,var_control); 

END IF;  
END// 
delimiter ;

As you can see I added the line "select var_control as debug;". I did this to get the value of the variable displayed on the screen. Anyway. This code should run fine. I know this, because I wrote a routine that did the exact same thing for a homework assignment. And for that database it works. However if I run this procedure unter MariaDB, it doe not work. Despite no syntax error, the statements between else and end if are never reached and, even stranger, the "select var_control as debug;" statement genereates no output on the screen, not even null!

As I said, my teacher stared at it for a solid 2 hours, finally he found the only difference between the code in my homework assignement and this exercise: the collation and caracterset of the database: For my homework, I used the collation latin1_swedish_ci, while at school we use utf8_general_ci.

Could this be the reason? Can the collation really have such a profound Impact? Has anyone of you ever run into thsi kind of problem?

Best Answer

Learn about AUTO_INCREMENT instead of using max(directorID)+1.

moviedirector is a many:many mapping table. See http://mysql.rjweb.org/doc.php/index_cookbook_mysql#many_to_many_mapping_table for optimal schema for such. (may get you some brownie points)

"procedure unter MariaDB, it doe not work" elaborate.

(I see 2 clues so far that Deutsch is your native language. But your English is great.)

SELECT directorID FROM director -- What is director? It is not declared. Stored Routines are tricky. There are parameters (you prefixed them with par_ -- good); there are database/table/column names; there are @variables; there are DECLAREd variables; there are aliases (you don't have any); there are loop names (none). Using the same 'name' for two different uses can cause mysterious errors.

Collation differences either lead to explicit errors or slow performance due to inability to use indexes. So I don't think it is a collation problem.

I'm not sure, but I think the entire procedure can be replaced by a single INSERT .. ON DUPLICATE KEY UPDATE .. statement.