Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 376
  • Last Modified:

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
0
kenwardc
Asked:
kenwardc
  • 9
  • 8
2 Solutions
 
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
 
kenwardcAuthor 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
 
kenwardcAuthor 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
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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
 
kenwardcAuthor 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
 
kenwardcAuthor 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
 
kenwardcAuthor 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
 
kenwardcAuthor 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
 
kenwardcAuthor Commented:
Ooh-errr that's never gonna happen without instructions! ;) Hand edit? <gulp>
0
 
kenwardcAuthor 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
 
kenwardcAuthor 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

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 9
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now