Create Algorithm Undefined User Algorithm

Question

i had no problem at all running the following code on a local server, but when i run it from phpmyadmin on a yahoo hosting webserver, i am getting this error:

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ALGORITHM=UNDEFINED DEFINER=root@localhost SQL SECURITY DEFI

here's the code:

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `users` AS select `tblusers`.`id` AS `id`,`tblusers`.`password` AS `password`,`tblusers`.`title` AS `title`,`tblusers`.`first` AS `first`,`tblusers`.`last` AS `last`,`tblusers`.`gender` AS `gender`,`tblusers`.`address` AS `address`,`tblusers`.`address_2` AS `address_2`,`tblusers`.`city` AS `city`,`tblusers`.`state` AS `state`,`tblusers`.`postcode` AS `postcode`,`tblusers`.`country` AS `country`,`tblusers`.`email` AS `email`,`tblusers`.`emailnotes` AS `emailnotes`,`tblusers`.`Home_Phone` AS `Home_Phone`,`tblusers`.`Office_Phone` AS `Office_Phone`,`tblusers`.`Cell_Phone` AS `Cell_Phone`,`tblusers`.`Contact_Preference` AS `Contact_Preference`,`tblusers`.`Birthdate` AS `Birthdate`,`tblusers`.`Age` AS `Age`,`tblusers`.`Marital_Status` AS `Marital_Status`,`tblusers`.`Children` AS `Children`,`tblusers`.`occupation` AS `occupation`,`tblusers`.`HebrewName` AS `HebrewName`,`tblusers`.`notes` AS `notes`,`tblusers`.`Feedback` AS `Feedback`,`tblusers`.`date_submitted` AS `date_submitted`,`tblusers`.`DateCalled` AS `DateCalled`,`tblusers`.`last_contact` AS `last_contact`,`tblusers`.`Calling_Card` AS `Calling_Card`,`tbluserstudent`.`student_user_id` AS `student_user_id`,`tbluserstudent`.`LearningBackground` AS `LearningBackground`,`tbluserstudent`.`Affiliation` AS `Affiliation`,`tbluserstudent`.`education` AS `education`,`tbluserstudent`.`Preferred_Level` AS `Preferred_Level`,`tbluserstudent`.`Community` AS `Community`,`tbluserstudent`.`religious_observance` AS `religious_observance`,`tbluserstudent`.`jewish_identity` AS `jewish_identity`,`tbluserstudent`.`mother_jewish_identity` AS `mother_jewish_identity`,`tbluserstudent`.`father_jewish_identity` AS `father_jewish_identity`,`tbluserstudent`.`NotJewish` AS `NotJewish`,`tbluserstudentteacher`.`student_teacher_user_id` AS `student_teacher_user_id`,`tbluserstudentteacher`.`preferred_language` AS `preferred_language`,`tbluserstudentteacher`.`language_other` AS `language_other`,`tbluserstudentteacher`.`language_fluency` AS `language_fluency`,`tbluserstudentteacher`.`2nd_language` AS `2nd_language`,`tbluserstudentteacher`.`2nd_language_other` AS `2nd_language_other`,`tbluserstudentteacher`.`2nd_language_fluency` AS `2nd_language_fluency`,`tbluserstudentteacher`.`hebrew` AS `hebrew`,`tbluserstudentteacher`.`text_discussion` AS `text_discussion`,`tbluserstudentteacher`.`HeardOf` AS `HeardOf`,`tbluserstudentteacher`.`skype_ID` AS `skype_ID`,`tbluserstudentteacher`.`headset` AS `headset`,`tbluserstudentteacher`.`webcam` AS `webcam`,`tbluserstudentteacher`.`NewSystem` AS `NewSystem`,`tbluserstudentteacher`.`Matchable` AS `Matchable`,`tbluserstudentteacher`.`LA` AS `LA`,`tbluserstudentteacher`.`WeeklyReminder` AS `WeeklyReminder`,`tbluserstudentteacher`.`special` AS `special`,`tbluserstudentteacher`.`NotInterested` AS `NotInterested`,`tbluserstudentteacher`.`subject` AS `subject`,`tbluserstudentteacher`.`studytimes` AS `studytimes`,`tbluserstudentteacher`.`RefFirst` AS `RefFirst`,`tbluserstudentteacher`.`RefLast` AS `RefLast`,`tbluserstudentteacher`.`RefPhone` AS `RefPhone`,`tbluserstudentteacher`.`RefNotes` AS `RefNotes`,`tbluserteacher`.`teacher_user_id` AS `teacher_user_id`,`tbluserteacher`.`origin` AS `origin`,`tbluserteacher`.`teach_education` AS `teach_education`,`tbluserteacher`.`education_other` AS `education_other`,`tbluserteacher`.`teaching_experience` AS `teaching_experience`,`tbluserteacher`.`teaching_experience_other` AS `teaching_experience_other`,`tbluserteacher`.`teacher_Preferred_Level` AS `teacher_Preferred_Level`,`tblusershliach`.`shliach_user_id` AS `shliach_user_id`,`tblusershliach`.`Mosad` AS `Mosad`,`tblusershliach`.`Shlucha` AS `Shlucha`,`tblusershliach`.`ShluchaPhone` AS `ShluchaPhone`,`tblusershliach`.`ShluchaEmail` AS `ShluchaEmail`,`tblusershliach`.`invited` AS `invited`,`tblusershliach`.`Contacted` AS `Contacted`,`tblusershliach`.`Wants` AS `Wants`,`tblusershliach`.`web` AS `web`,`tblusershliach`.`sent` AS `sent`,`tblusershliach`.`feed` AS `feed`,`tblusershliach`.`MailingDate` AS `MailingDate`,`tblusershliach`.`Students` AS `Students` from ((((`tblusers` left join `tbluserstudent` on((`tblusers`.`id` = `tbluserstudent`.`student_user_id`))) left join `tbluserstudentteacher` on((`tblusers`.`id` = `tbluserstudentteacher`.`student_teacher_user_id`))) left join `tbluserteacher` on((`tblusers`.`id` = `tbluserteacher`.`teacher_user_id`))) left join `tblusershliach` on((`tblusers`.`id` = `tblusershliach`.`shliach_user_id`)));

why am i getting this error on a webserver but not locally?

Answer 1

Maybe a different MySQL version or a different storage engine or different configuration?

Anyway, you can simply omit 'ALGORITHM = UNDEFINED'.

Answer 2

MySQL 4.1.14 did not support ALGORITHMs. You won't be able to run this query on your Yahoo! host unless they have an option to use MySQL 5, which introduced this new feature.