Solved

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

Posted on 2014-11-09
17
287 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 83

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 83

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 83

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 83

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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

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 83

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 83

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 83

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 83

Expert Comment

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

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Please help me with my database modeling 7 37
mysql left join sentence 7 33
CheckListBox usage 3 48
Whether to use true/false, yes/no or 0/1 11 50
Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
Fore-Foreword Today (2016) Maxmind has a new approach to the distribution of its data sets.  This article may be obsolete.  Instead of using the examples here, have a look at the MaxMind API (https://www.maxmind.com/en/geolite2-developer-package). …
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
Delivering innovative fully-managed cloud services for mission-critical applications requires expertise in multiple areas plus vision and commitment. Meet a few of the people behind the quality services of Concerto.

932 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

11 Experts available now in Live!

Get 1:1 Help Now