Re: I can't have "group" as a column name in a table? [SOLVED]
Aveek, Simcha, Johan, Thanks for explaining the situation. I knew there were some reserved words, but I hadn't realized there were so many. Anyway, now that I know I can protect my column names with backticks, I'm good to go. -- Dave M G -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
I can't have "group" as a column name in a table?
MySQL users, Simple question: In one table in my database, the column was named "group". I kept getting failed query errors until I renamed the column. I've never before encountered a situation where MySQL mistook a column name for part of the query syntax. Should I never use the word "group" for column names? Seems a little silly. Is there a way to protect column names to that there is no confusion? -- Dave M G -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: MySQL won't run on Ubuntu 10.10 [SOLVED]
Bruijne, Thank you for responding. The my.cnf file was fine, but when you mentioned it, it sparked a long forgotten memory that there was something specific to Ubuntu that affects who has access to files like my.cnf. After a little web searching, I realized it is a security thing called "Apparmour". Long story short, when I upgraded Ubuntu, I kept the my.cnf file that had served me well for years, but had overwritten /etc/apparmor.d/usr.sbin.mysqld with new default settings. Some of the directories in that file did not match my system. Editing the Apparmour file and restarting both Apparmour and MySQL solved the problem. Thank you for setting me in the right direction. -- Dave M G -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
MySQL won't run on Ubuntu 10.10
MySQL List, I'm running Ubuntu 10.10, and I have a LAMP server which is not connected to the internet, but is just used for locally creating and testing web sites. MySQL does not seem to be running, however. Check out the output from these commands: ~$ sudo service mysql start mysql start/running ~$ ps -ef | grep mysql dave 23043 22341 0 23:57 pts/000:00:00 grep mysql If I understand these outputs, it says MySQL is running when I start it up, but, after that, if I look for the process, it doesn't show up as running. When I run any of my local web sites that rely on MySQL, they don't work, so I guess the final verdict is that MySQL isn't on. But, without any error when I try to start it, I'm not sure how to diagnose the problem. I tried purging then re-installing MySQL, but that didn't change anything. Any ideas on what else I could try? Thank you for any advice. -- Dave M G -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Alphabetical search to and from
MySQL, This should be a fairly simple question. I have a table with a bunch of people's names. I want to find people who's name begins within a certain range of characters. All names between F and P, for example. What SELECT statement would I use to do that? Thank you for any advice. -- Dave M G -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: MySQL not running on fresh LAMP install
MySQL List, Thanks for your advice and help. I tried various things suggested, and in the end, this is what seems to have worked (I've abbreviated most of the output): $ sudo pkill -9 mysqld $ sudo dpkg --force-all -r mysql-server-5.0 This removed MySQL, but left the configuration files. I went into /etc/mysql and edited both my.cnf and debian.cnf so that the socket = /tmp/mysql.sock Then I reinstalled MySQL server: $ sudo apt-get install mysql-server mysql-server-5.0 Seems to be running now. Thanks everyone for helping me get things sorted out. -- Dave M G -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL not running on fresh LAMP install
Glyn, Thank you for responding. To start with, check that the .pid file doesn't exist if the server is stopped Part of the problem is that I can't stop the server. $ sudo /etc/init.d/mysql stop * Stopping MySQL database server mysqld [fail] try running it manually with "/etc/init.d/mysql start" or "invoke-rc.d mysql start" $ sudo /etc/init.d/mysql start * Starting MySQL database server mysqld [fail] $ sudo invoke-rc.d mysql start * Starting MySQL database server mysqld [fail] invoke-rc.d: initscript mysql, action "start" failed. Try to take a look in syslog, it'll tell you what's been happening - if anything, give "cat /var/log/syslog | grep mysq" a try ... Whatever is messing with my MySQL server might be stopping it from logging properly, because most of the logs I can find are empty. Every time I try to output stuff, it just comes right back to the prompt: ~$ cat /var/log/syslog | grep mysq ~$ cd /var/log /var/log$ more mysql.err /var/log$ more mysql.log /var/log$ cd mysql /var/log/mysql$ ls /var/log/mysql$ Is there anything else I can do to get a clean install of MySQL running again? -- Dave M G -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL not running on fresh LAMP install
Glyn, Thanks for responding. Does the .pid file exist in either /var/run/mysqld/ or /tmp/ at the moment? The mysqld.pid file is in /var/run/mysqld. The mysql.sock file is in /tmp What symlink did you make? None right now. I removed any that I had made before so that I wouldn't confuse the situation. -- Dave M G -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL not running on fresh LAMP install
TLUG, Thanks for the helpful advice. Unfortunately I've hit an error I don't know what to do about. The symlink solution worked, but Ubuntu clears the /tmp directory on every boot. So I edited the my.cnf file to use /tmp/mysql.sock. But when I tried to restart MySQL, it kept saying "[fail]". So I put the my.cnf file back to the way it was, and it still failed to start MySQL. I rebooted, and MySQL still didn't work. So I thought I would reinstall MySQL using Synaptic, and it gave me errors. It said the package hard errors in it, and it wouldn't re-install. Then I tried removing it, and then it ironically told me that it had errors and I should reinstall it before attempting to remove it. I tried removing it from the command line, and it said this: Preconfiguring packages ... (Reading database ... 111433 files and directories currently installed.) Preparing to replace mysql-server-5.0 5.0.51a-3ubuntu5.1 (using .../mysql-server-5.0_5.0.51a-3ubuntu5.1_i386.deb) ... * Stopping MySQL database server mysqld [fail] invoke-rc.d: initscript mysql, action "stop" failed. dpkg: warning - old pre-removal script returned error exit status 1 dpkg - trying script from the new package instead ... * Stopping MySQL database server mysqld [fail] invoke-rc.d: initscript mysql, action "stop" failed. dpkg: error processing /var/cache/apt/archives/mysql-server-5.0_5.0.51a-3ubuntu5.1_i386.deb (--unpack): subprocess new pre-removal script returned error exit status 1 * Stopping MySQL database server mysqld [fail] invoke-rc.d: initscript mysql, action "stop" failed. * Starting MySQL database server mysqld [fail] invoke-rc.d: initscript mysql, action "start" failed. dpkg: error while cleaning up: subprocess post-installation script returned error exit status 1 Errors were encountered while processing: /var/cache/apt/archives/mysql-server-5.0_5.0.51a-3ubuntu5.1_i386.deb E: Sub-process /usr/bin/dpkg returned an error code (1) So now what am I supposed to do? It won't let me start it, stop it, remove it, or anything. Any advice would be much appreciated. -- Dave M G -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL not running on fresh LAMP install
MySQL List, I'm going through a post-hard-drive-crash process of reinstalling my whole operating system, and MySQL is acting very strange. I installed it as I always have done, by using Ubuntu's Synaptic Package Manager to install all the default packages for a LAMP server. Every time before I've done this it has worked like a dream. It seemed to work when I first installed it yesterday, but today, when trying to access phpMyAdmin, I got this error: #2002 - The server is not responding (or the local MySQL server's socket is not correctly configured) I figured I'd give MySQL a bit of a kick just to see if that would help: [EMAIL PROTECTED]:~$ sudo /etc/init.d/mysqld restart But it gave me this new and unusual response: sudo: /etc/init.d/mysqld: command not found Huh. Weird. But I thought it was running, so I ran: [EMAIL PROTECTED]:~$ ps aux | grep mysql And it said back to me: root 11171 0.0 0.0 1772 532 ?S20:36 0:00 /bin/sh /usr/bin/mysqld_safe mysql11290 0.1 0.7 126708 15424 ?Sl 20:36 0:00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql --pid-file=/var/run/mysqld/mysqld.pid --skip-external-locking --port=3306 --socket=/var/run/mysqld/mysqld.sock root 11291 0.0 0.0 2920 736 ?S20:36 0:00 logger -p daemon.err -t mysqld_safe -i -t mysqld dave 11309 0.0 0.0 3004 764 pts/0R+ 20:37 0:00 grep mysql I'm obviously no expert (closer to babe-in-the-woods in the grand scheme of things), but it looks like MySQL is running in some kind of safe mode or something? I tried rebooting, which is how people who are clueless like me pretend problems can be solved, and it still came up the same, indicating that this safe mode seems to be locked in. The problem seems to go beyond phpMyAdmin. I can't login to Zend Platform (returns the same error), nor can I start a mysql interface from the command line: $ mysql -u root -p ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2) Oh, this might be relevant - I ran a PHP script with phpinfo() and it says my MYSQL_SOCKET is located at: /var/run/mysqld/mysqld.sock Which is where it should be, no? Any ideas why it is doing this, and how to get MySQL happy again? Any advice would be much appreciated. -- Dave M G -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [PHP] Importing and exporting from MySQL, escape slash problem
Richard, Jed, Thank you for replying. Richard said: It's possible that there is an .htaccess file in phpMyAdmin that has Magic Quotes on that is messing you up... The .htaccess file for phpMyAdmin says "php_flag magic_quotes_gpc Off", so I guess that means I'm okay there. Other than that, it's specific to phpMyAdmin, so maybe ask those guys what they did... I joined their list through Sourceforge, but I haven't seen any mail from it, and any mail I send gets bounced back to me. I'm not sure what the issue is. Jed said: If you're having trouble importing a mysql dump using phpMyAdmin, it might be simpler not to use it, and use mysqldump instead. I suppose I'll have to if I can't get phpMyAdmin to behave. It's too bad, though, as phpMyAdmin is so convenient otherwise. -- Dave M G -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Importing and exporting from MySQL, escape slash problem
PHP List, MySQL List In my PHP environment, I have "Magic Quotes" turned off, and I use the mysql_real_escape_string() function clean strings of SQL syntax before inserting them into my database. So the data stored in my database does not have escape characters in it. Particularly, double and single quotes don't have slashes in front of them. This seems to work fine so long as I'm reading data into and out of the database from within my scripts. However, when I backup and import databases - I use the phpMyAdmin interface - they have escape slashes in front of every double and single quote characters. I'm not sure if it's on the export or import where they get added in. I've looked through the phpMyAdmin online documentation, and I can't see any option to control the presence of escape slashes. It seems to me that if it adds them in when exporting, it should take them out when importing. Or vice versa, but in either case be consistent. I just want my database to be exactly as it is before any export or import options. I'm a little muddled as to where I'm making the mistake. Can anyone advice on the best practice for preserving my database as is when backing up and restoring? Thanks for any advice. -- Dave M G -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Synchronizing a remote database with a local database
Janek, Thanks for responding. I was really hoping to not have to go to any third parties and start a whole new learning curve. Is what I'm after really not possible with MySQL? -- Dave M G Ubuntu 6.10 Edgy Eft Kernel 2.6.20-5-generic Pentium D Dual Core Processor PHP 5, MySQL 5, Apache 2 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Synchronizing a remote database with a local database
MySQL Users, I have a local website development environment where I have a "master" MySQL database. I have several web sites which use the exact same database structure. The structure of the master database doesn't change very often, but it does sometimes. When that happens, I want to be able to synchronize all the web sites to match it. Currently, I'm creating a bash sell script so that I can update all my web sites in one go. I've got it so that it will upload all the newest PHP and other web files. For MySQL, so far I've figured out that I can create a .sql file with the latest database structure with this command: mysqldump -u "root" -p"password" articlass_db --no-data --result-file=backup_db.sql But I'm now stuck on how to use that .sql file to upload the new structure to each web site's MySQL server. Can this be done? And can it be done in a non-destructive way. I mean, the web sites may include data that I don't want to lose. So I don't want the new data structure to wipe out any existing structure. I just want to compare the master database structure with the one on the web site, and if there are new tables or columns, then add them. Is this possible without third party commercial software? Thank you for any advice. -- Dave M G Ubuntu 6.10 Edgy Eft Kernel 2.6.20-5-generic Pentium D Dual Core Processor PHP 5, MySQL 5, Apache 2 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Global change of database collation
MySQL Users, I know from the manual that I can change the collation set of a table with the following command: ALTER TABLE `mytable` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci And that I can do this to alter a column of a table to change its collation: ALTER TABLE `mytable` CHANGE `column` `column` CHARACTER SET utf8 COLLATE utf8_unicode_ci But what I can't seem to find is the ability to change more than one at a time. Neither of these work: ALTER TABLE * DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ALTER TABLE * CHANGE * CHARACTER SET utf8 COLLATE utf8_unicode_ci WHERE COLLATE=latin_swedish_ci Is there no way to make every instance of "latin_swedish_ci" in a database, both on the table and the column level, turn to "utf8_unicode_ci"? I have a huge database to work with, and the prospect of doing this by hand is rather unappealing. Any advice or information would be much appreciated. -- Dave M G Ubuntu 6.10 Edgy Eft Kernel 2.6.17.7 Pentium D Dual Core Processor PHP 5, MySQL 5, Apache 2 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Can't connect to mysql.sock
MySQL List, This is an issue that I am also pursuing on the PHP list, but since it involved my MySQL my.cnf file, and also use of MySQL sockets, I'm posting here in hopes of finding additional assistance. To make a long story short, while installing Zend Studio (a PHP IDE), I was getting errors when trying to connect to MySQL. I finally resolved the problem with Zend, but, in my earlier attempts to find a solution, I've munged up my MySQL or PHP settings because I can no longer connect to MySQL from PHP. To resolve this, I tried to retrace my steps and put everything back the way it was. I also reinstalled MySQL, PHP, and phpMyAdmin from the Ubuntu repositories using apt-get. Despite these efforts, whatever it is that I've done to mess up my system remains in place. When I start a PHP script that calls upon MySQL, I get the following error: Warning: mysql_pconnect() [function.mysql-pconnect]: Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2) in /web_sites/web/db_fns.php on line 6. So far as I can remember, the only places I made edits were in /etc/php5/apache2/php.ini, and /etc/mysql/my.cnf. In /etc/php5/apache2/php.ini, here is what the relevant section looks like now: - - - - - - - - - ; Default port number for mysql_connect(). If unset, mysql_connect() will use ; the $MYSQL_TCP_PORT or the mysql-tcp entry in /etc/services or the ; compile-time value defined MYSQL_PORT (in that order). Win32 will only look ; at MYSQL_PORT. mysql.default_port = 3306 ; Default socket name for local MySQL connects. If empty, uses the built-in ; MySQL defaults. mysql.default_socket = - - - - - - - - - My understanding of the above is that if the socket variable is left empty, it should go with the MySQL default. I have tried specifying "mysql.default_socket = /var/run/mysqld/mysql.sock" and "mysql.default_socket = /tmp/mysql.sock", but that hasn't helped. As for /etc/mysql/my.cnf, it says: - - - - - - - - - [mysqld] pid-file= /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysql.sock - - - - - - - - - I am unsure where to look to diagnose this problem further. Any advice would be much appreciated. Thank you. -- Dave M G -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Should I upgrade to 4.1 or 5.0 (Newbie level question)
MySQL Mailing List, Currently I'm running MySQL 4.1.15 on my home computer where I do web development, and MySQL 4.1.13-beta on my hosting service. Actually, I do work on web sites that are hosted on many hosting services, but on only one of them do I have enough access to decide for myself if I want to upgrade the server. It seems that MediaWiki, something I'm currently experimenting with, requires MySQL 4.1.14 or above. So at the very least I need to upgrade MySQL on the hosting service. But I'm a little confused by the presence of MySQL 5.0. On the mysql.com web site, it promotes version 5.0 and barely makes any mention of previous versions. (Of course, in the support documentation there is a lot about previous versions, but I'm just talking about the site's promotional text.) Ordinarily, a new version of any software wouldn't be confusing. I would assume that whatever the latest version that the developers are making available is the one that is supported and stable and preferable to use. But none of my hosting services anything higher than 4.1.15. And my home computer, which runs Ubuntu, defaulted to installing 4.1.15 and uthe application update manager doesn't update it to 5.0. So if 5.0 is the "current" version of MySQL, why does it seem to me that it's not widely adopted? Would it be problematic now or later if I upgraded to 5.0? Would I have to soon upgrade to 5.0 if I upgraded to 4.15 now? Thank you for any advice. -- Dave M G -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
utf8 character collation not working
MySQL list, I have a database on a server that contains English and Japanese text. I have tried to ensure at every turn that all text encoding is in utf8. On the web hosting server where the live site resides, everything is working fine. But on my testing environment at home, the Japanese text displays as question marks. Japanese text that is written directly into the PHP/HTML displays correctly. Only Japanese text retrieved from the database displays incorrectly. All the Japanese text was initially entered on the hosting server. To get the database duplicated onto my testing environment, I exported the database, and then imported it again. I also checked the .sql file that was generated to ensure that the utf8 encoded Japanese was still readable as Japanese before importing it. Further, when I look at the database in phpMyAdmin, it displays the Japanese correctly. I had a similar problem a few months back, and at the time, it was advised that I add the following lines to /etc/mysql/my.cnf: init-connect='SET NAMES utf8' character-set-server=utf8 collation-server=utf8_general_ci I've done this, and so these are my collation variables: Variable Session value Global value character set clientutf8 utf8 character set connection utf8 utf8 character set database utf8 utf8 character set results utf8 utf8 character set server utf8 utf8 character set system utf8 utf8 character sets dir /usr/share/mysql/charsets/ /usr/share/mysql/charsets/ collation connection utf8_general_ci utf8_general_ci collation database utf8_general_ci utf8_general_ci collation server utf8_general_ci utf8_general_ci This solved the problem last time. But this time the same solution does not seem to be working. I hope I have described the problem in enough detail. If not, please let me know. Is there any other character set related setting that I may need to do on my local MySQL server to correctly display Japanese? My home set up: Ubuntu Dapper Drake 6.06 MySQL MySQL 4.1.15 phpMyAdmin 2.7 PHP 4.4.2 Any help would be much appreciated. Thank you. -- Dave M G -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Unable to duplicate a database at home, possible encoding problem
The precise instructions are here: http://dev.mysql.com/doc/refman/5.0/en/charset-syntax.html ...but if it is your local home installation you could also recompile mysql specifying utf8 when you run configure. Thank you for the very helpful advice. I understand what it is you're saying I can accomplish, but I'm fuzzy on the specifics, because, if it weren't already apparent, I'm a bit of a newbie at this stuff. I get that I can use commands with the --with-charset=utf8 perameter to rebuild (?) my server with utf8 as the default encoding. But you see, I installed MySQL from within a program called Synaptic within Ubuntu. On the upside, it was very easy to get my MySQL installation up and running. On the downside, I didn't see or do any configuration settings our source code building, which means I'm clueless as to where this stuff happens. Being the exploratory newbie that I am, I tried the following: [EMAIL PROTECTED]:~$ mysqld --character-set-server=utf8 060215 11:40:58 [Warning] Can't create test file /var/lib/mysql/ubuntu1.lower-test 060215 11:40:58 [ERROR] Can't start server: Bind on TCP/IP port: Address already in use 060215 11:40:58 [ERROR] Do you already have another mysqld server running on port: 3306 ? 060215 11:40:58 [ERROR] Aborting 060215 11:40:58 [Note] mysqld: Shutdown complete [EMAIL PROTECTED]:~$ ./configure --with-charset=utf8 bash: ./configure: No such file or directory I kind of knew it wasn't going to work when I tried it, but I thought I'd dive in anyway. Is it possible I can get a slighly more newbie-friendly set of instructions on how I can either reconfigure my MySQL server, or at least permanently alter the system variables? -- Dave M G -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Unable to duplicate a database at home, possible encoding problem
detailed info here: http://dev.mysql.com/doc/refman/5.0/en/charset-connection.html Thank you for that link. I think this is the heart of the issue. Following the advice on the web page, I ran these commands: SET NAMES utf8; SET CHARACTER_SET utf8; But I can check my MySQL system variables in phpMyAdmin, and here's what it says: VariableSession valueGlobal value character set clientutf8 latin1 character set connection utf8 latin1 character set database latin1 latin1 character set results utf8 latin1 character set server latin1 latin1 character set system utf8 utf8 collation connection utf8_general_ci latin1_swedish_ci collation database latin1_swedish_ci latin1_swedish_ci collation server latin1_swedish_ci latin1_swedish_ci What I want to do is change *all* of these to utf8. That's the only encoding I ever work in, and I want to make my system as consistent as possible. What commands do I run to permanently fix all of these collation and character set variables to utf8? -- Dave M G -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Unable to duplicate a database at home, possible encoding problem
MySQL List, With the off list help of a member of this community, I have solved the issue of accessing my database data from within PHP. It was an issue related to PHP's register_globals setting. However, the encoding issue remains. I've taken the text that pertains only to that, and reproduced it here in hopes that someone can give me some advice which will enable me to transport my utf8 encoded data from my hosting service to my home machine. I can take the .sql file that I have exported from my hosting service, open it in OpenOffice Write as a text encoded file, and verify that it is encoded in utf-8. Most of the Japanese text shows up readable. Some of it, however, shows up as coded numbers (I'm not sure what the term is when utf displays this way): メーン・ When I import the .sql file into MySQL, I can look at it in phpMyAdmin and see that the text that displayed correctly as Japanese in OpenOffice still displays correctly as Japanese. The text that was in number form is also still in number form when viewed through phpMyAdmin. In short, phpMyAdmin sees it after import the same way that OpenOffice did before import. But, then when I view a PHP file in FireFox, and it accesses the database that way, the situation changes. The text that is encoded as numbers displays as correct Japanese. The text that displays as actual Japanese text in OpenOffice and phpMyAdmin now displays as quesiton marks. Again, just to be clear, all Japanese characters and all database data display correctly when viewed from the hosting service. I hope someone can shed some light on this. Thank you. -- Dave M G -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Unable to duplicate a database at home, possible encoding problem
MySQL list, I'm afraid this explanation is a bit long, but hopefully it's clear and concise. A while back I reported having troubles copying a database from my hosting service to my home machine. One of the problems I encountered was that I had some table columns named "group", which is a reserved word in MySQL. I've since corrected that error by renaming every instance of the word "group" with the word "groupid". This also entailed editing my PHP files, which took some time. However, at this point, the database and associated PHP files were succesfully corrected, and are working fine on my hosting service. So I downloaded all PHP files to my home computer, and exported my database (structure and data), to a .sql file. My .sql file imports into my home SQL server without returning errors. And almost all the PHP pages that seek to get any data from the database return this error: Warning: mysql_data_seek(): Offset 0 is invalid for MySQL result index 11 (or the query data is unbuffered) in /home/dave/web_sites/tokyocomedy.com/site_files/show.php on line 51 Because the pages work flawlessly on my hosting service, I have to assume that there is some flaw in how it's all being copied to my home computer. The PHP files are just copied by FTP, so it's unlikely that they have been altered. The MySQL data, however, is under suspicion because I have Japanese text, and on my home computer, not all of it is displaying correctly. Some of it comes up as question marks when viewed in FireFox. When I have the .sql file on my desktop, I can open it in OpenOffice Write, as a text encoded file, and verify that it is encoded in utf-8. Most of the Japanese text shows up readable. Some of it, however, shows up as coded numbers (I'm not sure what the term is when utf displays this way): メーン・ When I import the .sql file into MySQL, I can look at it in phpMyAdmin and see that the text that displayed correctly as Japanese in OpenOffice still displays correctly as Japanese. The text that was in number form is also still in number form when viewed through phpMyAdmin. In short, phpMyAdmin sees it after import the same way that OpenOffice did before import. But, then when I view a PHP file in FireFox, and it accesses the database that way, the situation changes. The text that is encoded as numbers displays as correct Japanese. The text that displays as actual Japanese text in OpenOffice and phpMyAdmin now displays as quesiton marks. Again, just to be clear, all Japanese characters and all database data display correctly when viewed from the hosting service. The fact that the Japanese is not displaying correctly and the database error may not be correlated. But I'm hoping that solving one will either alleviate the other, or that solving one will make more clear what the other problem is. One problem at a time, although I don't care which one is solved first. I'm a bit stumped as to how to diagnose and solve exactly what the problem that causes what should be the same data on both my home machine and the hosting service to behave differently. Any advice on how to solve this would be much appreciated. Thank you for taking the time to read this. -- Dave M G -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Exporting utf-8 data problems
Thank you for the advice.
In order to isolate the problem, I have checked to see if the text file
that I have exported from the MySQL server on my virtual host is, in
fact, in UTF-8.
By opening it in OpenOffice and selecting UTF-8 encoding, it displays
correctly.
Not entirely without problems. Most of the Japanese text shows up
correctly. But about 10% of it shows like this:
プル・リーフで
I believe this is because there is probably some Japanese text that was
entered in sometime before the server upgraded MySQL to 4.1.
But, ignoring the 10% of "legacy" text, it seems that at the very
least, I do have a proper UTF-8 encoded text file with which to import
into my home MySQL server.
Instead of importing the data as an SQL file (which successfully
imported, but with faulty Japanese characters), I copied the text and
pasted them in as a straight SQL query.
But it returns an error. Can anyone enlighten me as to why the file
would import into SQL as an SQL file, but the text won't work as an
import statement?
Here is the error output:
SQL query:
# phpMyAdmin MySQL-Dump
# version 2.3.3pl1
# http://www.phpmyadmin.net/ (download page)
#
# Host: localhost
# Generation Time: Jan 04, 2006 at 10:04 AM
# Server version: 3.23.37
# PHP Version: 4.3.11
# Database : `signup`
#
#
# Table structure for table `event_groups`
#
CREATE TABLE event_groups(
egid int( 11 ) NOT NULL AUTO_INCREMENT ,
GROUP int( 11 ) NOT NULL default '0',
event int( 11 ) NOT NULL default '0',
PRIMARY KEY ( egid )
) TYPE = MYISAM
MySQL said: Documentation
#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 'group int(11) NOT NULL default '0',
event int(11) NOT NULL de
(the error message cuts abruptly, as shown here)
Any advice would be much appreciated.
--
Dave M G
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Exporting utf-8 data problems
MySQL List, I have recently switched over from Windows to Ubuntu Linux, in order to emulate as much as possible the environment I have on my web hosting service. The goal is to be able to develop and test my web sites more completely at home before uploading them. I have successfully installed Apache, PHP, and MySQL (Most of which came by default when installing Ubuntu). I'm comfortable writing PHP and MySQL code in a web page, but I am very much a beginner in MySQL set up and maintenance. I have all the PHP and HTML files downloaded, and now my next step is to copy the databases from my web hosting service to my home machine. A lot of my database data is bilingual, English and Japanese. I try at every turn to store and retrieve all data in UTF-8 format. Using phpMyAdmin on my virtual hosting service, I exported my database information to a text file, which I then opened on my local machine, again through the phpMyAdmin interface. It mostly worked. All the tables and their contents were inserted into the home version of the database. However, when viewing the web pages where content is dynamically called from the database, all the Japanese text appears on my home machine as a series of question marks. So far as I know, I selected to use utf-8 encoding at every available opportunity. I'm wondering if the problems came when saving to a plain text file. Can anyone recommend the best way to preserve text encoding methods when copying a database from one machine to another? Any advice is much appreciated. Thank you. -- Dave M G -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
