Jump to content

SQL-importing tables problem


jimfog

Recommended Posts

I installed MySQL separately-till now I had it as a part of XAMPP. So I have kept the old MySQL files where in it(in a data file) the tables exist which I wish to import to the new installation. As far as I know in order to import a table into MySQL(I use PHPMyAdmin) it must be an sql file. Nonetheless, in the old NySQL installation the files that contain the tables that I want to import have extensions like .frm and MYI. I tried to import these but could not. I get this message actually:

#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 'þþ' at line 1

What can I do?

  • Like 1
Link to comment
Share on other sites

Import them as an ascii file. Just make sure the structure has been established.

Link to comment
Share on other sites

Import them as an ascii file. Just make sure the structure has been established.
And how do I do that?
Link to comment
Share on other sites

Goto the import tab on phpmyadmin. Look for the box that says "Format of imported file" and use either the "CSV" option or the " I cant remember which one I used last. As I remember, one worked for me. The other didn't.

Link to comment
Share on other sites

I tried some options and I get this error message:

Notice: Array to string conversion in C:\Apache24\htdocs\phpMyAdmin\libraries\Error.class.php on line 202

Link to comment
Share on other sites

The files that MySQL creates are binary files, those aren't things that you can import using phpMyAdmin. There may be a way to use the MySQL command line to create a database based on existing files, or the normal way would be to export the data from the old system to a SQL format and then import it to the new system. There are some examples here, the full reference for MySQL command line options is in their documentation. http://www.mydigitallife.info/how-to-backup-and-restore-export-and-import-mysql-databases-tutorial/

Link to comment
Share on other sites

Guest So Called

I've avoided commenting because my answer seemed to me to be overly simplistic. But here it is: Both systems should have phpMyAdmin installed. Use PMA to export file from first system in SQL format, use PMA on second system to import SQL. SQL files are intended to be portable. Database raw files are generally not intended to be portable. Those .FRM and .MYI files sound non-portable to me. Use PMA and .use SQL file type. Note: You may have some problem with database name on old file not matching new database name. If you can't pick new host with same database name then you may have to manually edit your .SQL file. I've had that happen since my shared hosting assigns database names and does not let me pick.

Link to comment
Share on other sites

The files that MySQL creates are binary files, those aren't things that you can import using phpMyAdmin.
I'm certain I've imported csv files into sql tables using phpmyadmin, but this says I can't. Am I understanding you correctly? Edited by niche
Link to comment
Share on other sites

I'm certain I've imported csv files into sql tables using phpmyadmin, but this says I can't. Am I understanding you correctly?
No, you're not. I'm saying that MySQL does not store data natively as .CSV files, it stores them as binary files. You can use phpMyAdmin to export to a CSV file, and import that file, but that's not the same thing as trying to import the native files that the storage engines create to store the tables. We're dealing with files that were created by MyISAM or InnoDB or whatever that are used to store the structure, indexes, and data that are in the actual tables, not a text format that you've used a tool to export the data to.
Link to comment
Share on other sites

Guest So Called

CSV files are text files. PMA has CSV listed as one of the import/export options but I've never tried it. BTW, being able to import/export databases (like in SQL format) is extremely important because this is how you back up and restore your database (or at least that's how you do it on a remote server like as in shared hosting). If you cannot export/export your database you cannot backup/restore your database, and if you can't do that then you can't protect your site from loss of data. Everybody using a MySQL database should make certain that they can perform these functions. Excepting of course databases used for tutorials or other situations where the loss of the database is of no importance. I recommend as a first experiment using phpMyAdmin's default settings for both export and import. Then revise if problems are encountered.

Link to comment
Share on other sites

Guest So Called

BTW the best way to experiment with import/export in a remote environment (shared hosting) is to create a new database. Export the data from your old database and import it to the new database. If it appears to have worked then change your script server, database name, database user, database password to the new database to see if the operation succeeded. If everything is fine then you did it. If not, you can just change the database name, user, password etc. back to revert to the old (working) database. The alternative (wrong way) would be to export the database then import back into the same database. The problem is that if that fails you've screwed your database and have no way to fall back.

Link to comment
Share on other sites

Thanks for the clarification jsg Per your #9 post. I did say in my #2 post, that the structure would need to be there before importing the ascii (csv).

Edited by niche
Link to comment
Share on other sites

One question, not so relevant. I have installed MySQL-how am i going to understand that is actually running?

Link to comment
Share on other sites

Ok, now to return on the topic. Unfortunately I do not have the old system so as to export from there in a CSV format, just the new PHPMyAdmin. What can I do?

Link to comment
Share on other sites

How did you generate those .frm and .myi files? Whatever utility you used, THAT should probably contain means to also restore from those files.

Link to comment
Share on other sites

Here is how the situation now, I managed to found in my system the old PHPMyAdmin installation. So now we have the new PHPMyAdmin installation under Apache24/htdocs(in which I want to import the database) AND the old PHPMyAdmin folder/files somewhere in my PC, not in htdocs-along of course with MySQL tables i wish to import in the NEW PHPMyAdmin. Given the above what can I do? I have not installed yet Mysql server 5.1. I want to see what can we do with what we have in our hands now.

Link to comment
Share on other sites

Guest So Called

phpMyAdmin files have nothing useful to import. PMA is just a tool. Other than having a configuration file with your MySQL login data it's just a program. You need to export your old MySQL files before you can import anything. It doesn't appear that your old MySQL native files are going to do you any good. First step: export old file into SQL format.

Link to comment
Share on other sites

CORRECTION: I meant the old MySQL files(data etc...) You say to export them, how?I must use a tool, what tool? PHPMyAdmin DOES not connect with these folders-these folders are just somewhere in my system.I remind that these files are myi, frm files. Could I use mysqldump?

Edited by jimfog
Link to comment
Share on other sites

Guest So Called

Use phpMySQL to connect with your old MySQL installation that uses those files. Export. Then connect phpMySQL to new MySQL server and import. The topic has been skirting around something that you probably don't want to hear. If you deleted your old MySQL installation before exporting the files then IMO there is a good chance you will not be able to recover them. There may be a small chance that you can reinstall your old MySQL and have access to the old files. Why didn't you export them when you had the chance? And why didn't you back them up? (Same thing as exporting.)

Edited by So Called
Link to comment
Share on other sites

Use phpMySQL to connect with your old MySQL installation that uses those files. Export. Then connect phpMySQL to new MySQL server and import.
How, PMA is under hdocs, when I start it, it connects with the mysql server which has been installed under program files-how I can make PMA connect with the mysql files that are somewhere else in the system?
The topic has been skirting around something that you probably don't want to hear. If you deleted your old MySQL installation before exporting the files then IMO there is a good chance you will not be able to recover them. There may be a small chance that you can reinstall your old MySQL and have access to the old files..
IF the files are permanently lost I have my alternatives-no need to mention them mow
Why didn't you export them when you had the chance? And why didn't you back them up? (Same thing as exporting.).
As I said above I have my alternatives
Link to comment
Share on other sites

Guest So Called
How, PMA is under hdocs, when I start it, it connects with the mysql server which has been installed under program files-how I can make PMA connect with the mysql files that are somewhere else in the system?
phpMyAdmin does not connect with MySQL files. It connects to a MySQL server. The server is the element that is responsible for connecting with the files. If you want those files you're going to have to find a way to connect a MySQL server to them. I have my doubts that it's possible without some serious expert hacking. phpMyAdmin understands only import files. You can get a list of the file types from the drop-down menu in the PMA import/export section. The way PMA works is that when you import files it connects with your MySQL server, and then reads the SQL import file and generates a set of transactions with the MySQL server to get the server to store your data in the server's database. phpMySQL is not designed to connect with raw data. That's why there's so many import/export file types. It's understood that the old database will be exported by PMA or some similar program under the old file system, which turns the data into a format that PMA understands, one of the most common formats being SQL I think you should start looking at your alternatives.
Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...