Solved

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

Posted on 2014-11-09
17
277 Views
Last Modified: 2014-12-07
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
0
Comment
Question by:kenwardc
  • 9
  • 8
17 Comments
 
LVL 82

Expert Comment

by:Dave Baldwin
ID: 40431630
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
 

Author Comment

by:kenwardc
ID: 40431637
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
 

Author Comment

by:kenwardc
ID: 40431671
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
 
LVL 82

Expert Comment

by:Dave Baldwin
ID: 40431691
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
 

Author Comment

by:kenwardc
ID: 40431714
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
 
LVL 82

Expert Comment

by:Dave Baldwin
ID: 40431731
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
 

Author Comment

by:kenwardc
ID: 40431746
Many thanks. Look forward to hearing from you.
0
 
LVL 82

Expert Comment

by:Dave Baldwin
ID: 40431828
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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

Author Comment

by:kenwardc
ID: 40432167
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
 
LVL 82

Expert Comment

by:Dave Baldwin
ID: 40432173
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
 

Author Comment

by:kenwardc
ID: 40432178
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
 
LVL 82

Assisted Solution

by:Dave Baldwin
Dave Baldwin earned 500 total points
ID: 40432201
You can try it.  You may have to hand edit the file to get it to import.
0
 

Author Comment

by:kenwardc
ID: 40432231
Ooh-errr that's never gonna happen without instructions! ;) Hand edit? <gulp>
0
 

Accepted Solution

by:
kenwardc earned 0 total points
ID: 40457016
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
 
LVL 82

Expert Comment

by:Dave Baldwin
ID: 40458179
0
 

Author Closing Comment

by:kenwardc
ID: 40485316
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
 
LVL 82

Expert Comment

by:Dave Baldwin
ID: 40485362
You're welcome, glad to help.
0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

I have been using r1soft Continuous Data Protection (http://www.r1soft.com/linux-cdp/) for many years now with the mySQL Addon and wanted to share a trick I have used several times. For those of us that don't have the luxury of using all transact…
Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now