The user specified as a definer ('usernames'@'') does not exist

Hi Folks

Hope someone can help. I am trying to move a web site off an old non-cPanel CentOS 5.0 server with MySQL 5.1.52 and PHP 5.2.16 to a new cPanel CentOS 6.5 server with MySQL 5.5.37 and PHP 5.4.31.

This query relates to two tables in the main database:

core_news
core_news_view

The core_news table has loads of data in it which is supposed to display on the "News" page on the site. I have no idea that the core_news_view table is for but on both servers it appears to be an empty table.

I have moved the site and most of the database-related items, such as logins, etc., all work fine. However, there is one item - core_news_view - that doesn't. When I do a phpMyAdmin check on the database it comes up with this:

core_news_view The user specified as a definer ('usernames'@'') does not exist
core_news_view The table is corrupt

It appears that on the old server there is also no data in this table and when I check the database on that server I get:

core_news_view View `core_news_view` has no creation context
core_news_view OK

On the old server the content of the main news table (core_news) table is populated and being read perfectly and displays fine. On the new server the core_news table is correctly populated with the scores of news items that the site should be displaying.

On the new server I get a blank area on the page where the data is supposed to display and looking in the error_log file I get this:

[09-Nov-2014 20:32:34 PHP Warning:  Illegal string offset 'id' in /home/user/public_html/news.php on line 38
[09-Nov-2014 20:32:34 PHP Warning:  Illegal string offset 'intro' in /home/user/public_html/news.php on line 46
[09-Nov-2014 20:32:34 PHP Warning:  Illegal string offset 'id' in /home/user/public_html/news.php on line 50

I hope I've given enough information to get you started sleuthing the problem for me and look forward to hearing from you in the hope I can resolve this fairly quickly.

Cheers
Chris
LVL 1
Chris KenwardDirectorAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dave BaldwinFixer of ProblemsCommented:
I suspect that 'core_news_view' may only contain a 'view' for 'core_news' and the PHP errors are because some data is missing.  I would go back to the old server and re-export 'core_news_view' making sure to get everything this time like stored procedures and views and re-import it to the new server.
0
Chris KenwardDirectorAuthor Commented:
Hi Dave

Thanks for your quick response. Looking at the table on the old server, there is no data in there either. I'm using phpMyAdmin for the export of the SQL. Could you tell me if there are any tick boxes which would need to be either active or not active before I press GO?

Cheers
Chris
0
Chris KenwardDirectorAuthor Commented:
Dave, I've just had a look at the old server and I was incorrect - there are indeed records in the core_news_view table. I can see these when browsing the core_news_view table. However, if I click on the main database and then look down the list of tables, the core_news_view table says the following: (see snapshot)

This is on the OLD (original) server
If I export the apparently non-existent data from the table, then go to the new server and select to import it, I get this error message:

#1449 - The user specified as a definer ('username'@'') does not exist

I've replaced the actual name with "username" for privacy here.

Does this shed any light on it for you?

Cheers
Chris
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

Dave BaldwinFixer of ProblemsCommented:
After a bit of research, it seems that phpMyAdmin does not properly support views.  'mysqldump' doesn't either!  A similar program called Adminer claims to do it properly.  I've had Adminer recommended to me so maybe I'll try it myself.  http://www.adminer.org/en/phpmyadmin/
0
Chris KenwardDirectorAuthor Commented:
Hi Dave

Super - could you let me know what you think of it and whether you think it would help resolve my issue?

Cheers
Chris
0
Dave BaldwinFixer of ProblemsCommented:
Unfortunately, Adminer crashed my old XP computer or at least the web server.  I'll do some more research and see if I can get it running and let you know.
0
Chris KenwardDirectorAuthor Commented:
Many thanks. Look forward to hearing from you.
0
Dave BaldwinFixer of ProblemsCommented:
Ok.  Adminer4 works fine on my Ubuntu Linux system under Apache so it will probably work fine on CentOS.  I don't know if it will do what you need but it's worth a try.  It's a single PHP file about 384K.  Just plop it down in a directory and run it.

And for what it's worth, Adminer2 works on this XP/IIS system but Adminer3/4 do not.  They crash my web server.  I'm going to try it on another XP system with Apache.  I wouldn't be surprised if it works there.
0
Chris KenwardDirectorAuthor Commented:
Hi Dave
Thanks will have a go at that on the CentOS box. Just a thought though... is the issue also perhaps not related to the error I get when I click on the database on the new server - the one about the definer? So not only am I having trouble exporting the data from the old server for that specific table but cannot import it to the new server because of the definer issue?

Cheers
Chris
0
Dave BaldwinFixer of ProblemsCommented:
You would have to read thru some of the articles on the problems with exporting views from MySQL.  Even their own program 'mysqldump' does not do it in a usable fashion.  What I read is too complicated to explain here.
0
Chris KenwardDirectorAuthor Commented:
Dave, is there a way to export the SQL from just that one table, which I can do, save it to disk, them import it to the specific table on the new server. Because the "definer" error won't let me manipulate that table on the new server, would it OK to create a new table with the same name, then import the SQL directly from disk into that table?

Apologies if this all sounds really dumb - I'm no SQL expert at all so this is way over my head in reality.

Cheers
Chris
0
Dave BaldwinFixer of ProblemsCommented:
You can try it.  You may have to hand edit the file to get it to import.
0
Chris KenwardDirectorAuthor Commented:
Ooh-errr that's never gonna happen without instructions! ;) Hand edit? <gulp>
0
Chris KenwardDirectorAuthor Commented:
Hi Dave

OK - I resolved the issue myself. Several things had to happen.

I had to create the new view myself on the new database.
I had to manually create a new definer on the new server.
I had to sync the old database with the new one and it updated the view just fine.

I think it's working now. <fingers crossed> Thanks for the help and the trouble you went to.

Not sure I can accept any of the messages above as the solution though, as none of them really look like what I ended up doing.

Cheers
Chris
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Chris KenwardDirectorAuthor Commented:
The "fix" I detailed is still working so happy that this has been resolved.

Many thanks to Dave for the help and perseverance with me on this one.
0
Dave BaldwinFixer of ProblemsCommented:
You're welcome, glad to help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.