Converting database and its tables to UTF-8
Hi, I have a database with around 40 tables that needs to be converted to UTF-8 to support multi languages. What is the best procedure to do this? And is it any way to change the default charset to UFT-8 so tables by default will become UFT-8? And can I have one table with different fields with different charset? Best regards, Peter Lauri
Re: Converting database and its tables to UTF-8
Hi Peter, That will be a lot of work ! *1.* First make a back-up... it's always a good ideea ! *2.* For every table in the database alter String Types into BINARY string types that means: - *(VAR)CHAR(M)* will become *(VAR)**CHAR(M) BINARY* or *(VAR)**BINARY(M)* - *TINYTEXT, TEXT, MEDIUMTEXT, *and* **LONGTEXT* will become respectively *TINYBLOB, BLOB, MEDIUMBLOB, *and* LONGBLOB* *3.* Alter the database isuing *ALTER DATABASE `database_name` DEFAULT CHARACTER SET utf8;* *4.* Alter each table issuing: *ALTER TABLE `table_name` DEFAULT CHARSET=utf8;* *5.* Alter back the column types. This should do it ! And because you issued the ALTER DATABASE from now on all tables in that database will be in utf8 ... and falling down any column will be utf8 if you don't specify explicitly anything else... Yes you can have a charset on the database in that database tables with different chartsets and even in a table you can have columns with other chartsets... Gabriel PREDA Senior Web Developer On 2/14/06, Peter Lauri [EMAIL PROTECTED] wrote: Hi, I have a database with around 40 tables that needs to be converted to UTF-8 to support multi languages. What is the best procedure to do this? And is it any way to change the default charset to UFT-8 so tables by default will become UFT-8? And can I have one table with different fields with different charset? Best regards, Peter Lauri
Re: Installation Issue
Starting mysql with root.I tried withn mysql user account also but still same error. thanks Peter M. Groen [EMAIL PROTECTED] wrote: On Tuesday 14 February 2006 00:28, Ravi Kumar wrote: Permission denied 060213 I assume you are starting MySQL NOT as root.. Has the user write permissions on the directory and the socketfile? -- Peter M. Groen Open Systems Development Klipperwerf 12 2317 DZ Leiden T : +31-(0)71-5216317 M : +31-(0)6-29563390 E : [EMAIL PROTECTED] Skype : peter_m_groen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] - Yahoo! Mail Use Photomail to share photos without annoying attachments.
Re: Installation Issue
On 2/14/06, Ravi Kumar [EMAIL PROTECTED] wrote: Starting mysql with root.I tried withn mysql user account also but still same error. thanks Ravi, Assuming you are starting MySQL with mysqld_safe, then it will invoke the MySQL server as the mysql user. I suspect the cause is that /var/lib/mysql is not owned by mysql If so, as superuser: chown -R mysql:mysql /var/lib/mysql Regards, Imran Chaudhry -- http://www.ImranChaudhry.info MySQL Database Management Design Services -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
FW: Any help with resetting the administrative password using 'my sqld_safe' w/ the --init-file option
-Original Message- From: Skarlatos, Matthew P. To: 'mysql@lists.mysql.com' Sent: 2/13/2006 8:37 AM Subject: Any help with resetting the administrative password using 'mysqld_safe' w/ the --init-file option Has anyone run into a problem with setting the root user password in mysql using mysqld_safe with the '--init-file' option that contains the new password for startup? What I'm seeing after I issue the kill command for the 'host.pid' file and then restarting 'mysqld_safe --init-file', is a short pause, and then an error message that mysqld is unable to start - i.e. 060213 13:28:35 mysqld ended My mysql version is 4.0.20 running on a Solaris 9 system. If there are any additional patches or workarounds that are required, I'd appreciate any advice or tips that you could pass along to me for those that have tried to create the root password in this way. http://dev.mysql.com/doc/refman/4.1/en/resetting-permissions.html Thanks in advance, Matt Skarlatos -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
5.0.16. Bug in union?
Hello mysql, Union on zerofilled fields eats 'zerofilling'. How to repeat: create table a (id integer zerofill); insert into a values(1),(2),(3); select * from a; ++ | id | ++ | 01 | | 02 | | 03 | ++ select * from a union select * from a; +--+ | id | +--+ |1 | |2 | |3 | +--+ Where are my leading zeroes? :( Is this a known bug? PS. On 4.1.* all works as expected. -- Best regards, Juri mailto:[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 5.0.16. Bug in union?
Interesting... maybe this is because the fill is not actualy stored in the database... and being sorted/compared as a number MySQL removes the ZEROFILL ! You can go and do: select BINARY * from a union select BINARY * from a; -- Gabriel PREDA Senior Web Developer On 2/14/06, Juri Shimon [EMAIL PROTECTED] wrote: Hello mysql, Union on zerofilled fields eats 'zerofilling'. How to repeat: create table a (id integer zerofill); insert into a values(1),(2),(3); select * from a; ++ | id | ++ | 01 | | 02 | | 03 | ++ select * from a union select * from a; +--+ | id | +--+ |1 | |2 | |3 | +--+ Where are my leading zeroes? :( Is this a known bug? PS. On 4.1.* all works as expected. -- Best regards, Juri mailto:[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
joining 3 tables
I am trying to join 3 tables together. Table A has 3 fields: sub_id, subject, id Table B has 3 fields: ref_cat_id, sub_id, ref_cat Table C has 7 fields: uid, ref_cat_id, etc. I am trying to join Table A and B over sub_id and join B and C over ref_cat_id while pulling in the value of sub_id and ref_cat_id from a php program I tried doing it this way: select reference.uid from reference, subject_name, ref_cat where subject_name.sub_id = '45' and ref_cat.ref_cat_id = '3' and ref_cat.ref_cat_id = reference.ref_cat_id and subject_name.sub_id = ref_cat.sub_id with the '45' and '3' being the values coming from my variables in PHP. We only have MySql 4.0 so I can't use a subquery. If anyone has any suggestions I would be very thankful. Please also let me know if I need to provide more information about the problem. Thank you for your time and help. Amy Thornton, Electronic Services Specialist Information Services, Cook Library The University of Southern Mississippi 118 College Drive #5053 Hattiesburg, MS 39406-0001 Phone: (601) 266-6668 Fax: (601) 266-6857 E-mail: [EMAIL PROTECTED] http://www.lib.usm.edu/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Migration toolkit
Does anyone have any idea on this one? - Thanks Ed Reed [EMAIL PROTECTED] 2/10/06 3:09 PM I'm trying to use the Migration Toolkit to migrate a 4.1 system to a 5.0 system. It doesn't appear that it can successfully migrate my usernames and privileges. Is it supposed to be able to and is there anything special I need to do to make it happen? - Thanks
Re: joining 3 tables
On Tue, 14 Feb 2006, Amy Thornton wrote: I am trying to join 3 tables together. Table A has 3 fields: sub_id, subject, id Table B has 3 fields: ref_cat_id, sub_id, ref_cat Table C has 7 fields: uid, ref_cat_id, etc. I am trying to join Table A and B over sub_id and join B and C over ref_cat_id while pulling in the value of sub_id and ref_cat_id from a php program I tried doing it this way: select reference.uid from reference, subject_name, ref_cat where subject_name.sub_id = '45' and ref_cat.ref_cat_id = '3' and ref_cat.ref_cat_id = reference.ref_cat_id and subject_name.sub_id = ref_cat.sub_id with the '45' and '3' being the values coming from my variables in PHP. We only have MySql 4.0 so I can't use a subquery. Hi Amy, I suggest you try the following: SELECT reference.uid FROM reference INNER JOIN ref_cat ON reference.ref_cat_id = ref_cat.ref_cat_id INNER JOIN subject_name ON ref_cat.sub_id = subject_name.sub_id WHERE subject_name.sub_id = 45 AND ref_cat.ref_cat_id = 3; HTH Thomas Spahni -- 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: Migration toolkit
I haven't tried the migration toolkit so I don't know what it can or cannot do. I am assuming that you have hand-transferred a few users from your old system to the new one. What I can suggest is that you generate two sets of data. The first is a list of your users, their hostnames, and their password hashes from your old server: CREATE TABLE oldUsers SELECT user, host, password FROM mysql.user; You can use the utility mysqldump to convert oldUsers table to a SQL script. Save this dump into a text file (oldusers.sql) for later. Next you need to run a bunch of SHOW GRANTS for statements. A script (pick your favorite scripting language for this) can crawl through oldUsers (just created) and capture the results of SHOW GRANTS FOR 'user'@'host' for every user in the oldUsers table. Save the results into another text file (oldgrants.sql). These will be the SQL statements you will need to restore permissions to your current users in your new database. Now comes the fun part: I prefer to enter the CLI and navigate to the correct database by hand before executing scripts like these (I have seen many people just do this from the command line but I would rather be sure). On your new server, start your mysql CLI and navigate to the mysql database. Once there, execute the script that generates the oldUsers table. The sequence should look something like this: mysql -u yourlogin -p mysql provide your password mysql source full_path_to_oldusers.sql That should create a table of all of your user accounts in the table oldUsers in the mysql database of your new server. Bulk insert them into your users table like this INSERT IGNORE user (user, host, password) SELECT user, host, password FROM oldUsers; And refresh the permissions cache: FLUSH PRIVELEGES; Now you are ready to re-apply privileges. Assuming that you correctly captured the GRANT statements from your SHOW GRANTS for script, you should be able to say. mysql source full_path_to_oldgrants.sql and do one last FLUSH PRIVILEGES; Your old accounts should now exist on your new server with their old permissions restored. Sorry but you asked for any ideas... ;-) Shawn Green Database Administrator Unimin Corporation - Spruce Pine Ed Reed [EMAIL PROTECTED] wrote on 02/14/2006 12:11:05 PM: Does anyone have any idea on this one? - Thanks Ed Reed [EMAIL PROTECTED] 2/10/06 3:09 PM I'm trying to use the Migration Toolkit to migrate a 4.1 system to a 5.0 system. It doesn't appear that it can successfully migrate my usernames and privileges. Is it supposed to be able to and is there anything special I need to do to make it happen? - Thanks
Re: Unable to duplicate a database at home, possible encoding problem
Hi, The precise instructions are here: http://dev.mysql.com/doc/refman/5.0/en/charset-syntax.html You can start your server specifying the character set (http://dev.mysql.com/doc/refman/5.0/en/charset-server.html), and alter the databases (http://dev.mysql.com/doc/refman/5.0/en/charset-database.html), but if it is your local home installation you could also recompile mysql specifying utf8 when you run configure. That's the option I chose for my personal home installation and I updated the server version at the same time :-) hth, melanie From: Dave M G [EMAIL PROTECTED] To: mysql@lists.mysql.com Subject: Re: Unable to duplicate a database at home, possible encoding problem Date: Wed, 15 Feb 2006 02:54:00 +0900 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] _ Are you using the latest version of MSN Messenger? Download MSN Messenger 7.5 today! http://messenger.msn.co.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: joining 3 tables
Amy, You didn't mention what the problem is with your query... select reference.uid from reference, subject_name, ref_cat where subject_name.sub_id = '45' and ref_cat.ref_cat_id = '3' and ref_cat.ref_cat_id = reference.ref_cat_id and subject_name.sub_id = ref_cat.sub_id IAC it is easier to understand and debug written in proper join syntax ... SELECT reference.uid FROM reference AS r INNER JOIN ref_cat AS rc ON r.ref_cat_id=rc.ref_cat_id INNER JOIN subject_name AS s ON rc.ref_cat.sub_id=s.sub_id WHERE s.sub_id = 45 AND rc.ref_cat_id = 3 and it will be easier to help if you provide the CREATE statements and a bit of sample data. PB - Amy Thornton wrote: I am trying to join 3 tables together. Table A has 3 fields: sub_id, subject, id Table B has 3 fields: ref_cat_id, sub_id, ref_cat Table C has 7 fields: uid, ref_cat_id, etc. I am trying to join Table A and B over sub_id and join B and C over ref_cat_id while pulling in the value of sub_id and ref_cat_id from a php program I tried doing it this way: select reference.uid from reference, subject_name, ref_cat where subject_name.sub_id = '45' and ref_cat.ref_cat_id = '3' and ref_cat.ref_cat_id = reference.ref_cat_id and subject_name.sub_id = ref_cat.sub_id with the '45' and '3' being the values coming from my variables in PHP. We only have MySql 4.0 so I can't use a subquery. If anyone has any suggestions I would be very thankful. Please also let me know if I need to provide more information about the problem. Thank you for your time and help. Amy Thornton, Electronic Services Specialist Information Services, Cook Library The University of Southern Mississippi 118 College Drive #5053 Hattiesburg, MS 39406-0001 Phone: (601) 266-6668 Fax: (601) 266-6857 E-mail: [EMAIL PROTECTED] http://www.lib.usm.edu/ -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 267.15.7/259 - Release Date: 2/13/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Stored procedure issue.
I'm having some issues creating a stored procedure to optimize tables
in the database. I'm pulling the table names from the information
schema. The tablename doesn't seem to be correctly being replaced in
the optimize command..
I've tried used prepared statements which seem to correctly replace
the tableName, but I get an error that prepared statements do not
support that kind of query.
With this current version the error is: table queue.tableName doesn't exist..
Any thoughts from some SP gurus? Thanks.
--
DELIMITER $$;
DROP PROCEDURE IF EXISTS `queue`.`sp_OptimizeDatabase`$$
CREATE PROCEDURE `queue`.`sp_OptimizeDatabase` ()
BEGIN
DECLARE exitValue INT;
DECLARE tableName CHAR(120);
DECLARE cursorList CURSOR FOR
SELECT TABLE_NAME FROM information_schema.tables where
TABLE_SCHEMA = 'queue' AND TABLE_TYPE = 'BASE TABLE';
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET exitValue = 1;
OPEN cursorList;
REPEAT
FETCH cursorList INTO tableName;
OPTIMIZE TABLE tableName;
-- SET @optSQL := concat('OPTIMIZE TABLE ', tableName);
-- PREPARE pOptimize FROM @optSQL;
-- EXECUTE pOptimize;
-- DEALLOCATE PREPARE pOptimize;
UNTIL exitValue = 1
END REPEAT;
CLOSE cursorList;
END$$
DELIMITER ;$$
-
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ERROR 1060 while creating a view
Hi, I am trying to create a view by joining two tables.These tables have a common column which references their parent table. I get the following error when I try to create a view. create view trn2 as select * from dbtmsg,dbtrbl where dbtmsg.accxsk=-1 ERROR 1060 (42S21): Duplicate column name 'accxsk' Thank You, Any help appreciated, Vinay
Re: ERROR 1060 while creating a view
Vinay [EMAIL PROTECTED] wrote on 02/14/2006 02:29:45 PM: Hi, I am trying to create a view by joining two tables.These tables have a common column which references their parent table. I get the following error when I try to create a view. create view trn2 as select * from dbtmsg,dbtrbl where dbtmsg.accxsk=-1 ERROR 1060 (42S21): Duplicate column name 'accxsk' Thank You, Any help appreciated, Vinay Since both tables contain a column called `accxsk`, the SELECT * part of the view's definition will try to list both copies (once from each table). Views cannot contain columns with duplicate names. Hence the error. You have to use the more explicit form of SELECT col1, col2, col3, to individually identify each column you want to see in your view in order to avoid this error. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Byte Swaping (Re Post)
Well, just thought I'd try one more time because I didn't get an answer to my question last time. So what I have is a random data stream that is sent in raw form, and based on some data definition, I can assemble with the correct data types and such. One of my requirements is that I have to store the data in raw form, and when I pull the data out, it displays based on the configuration (with the correct data types and such). So floats and doubles are IEEE standards so I don't have to worry about those, however with integer types, I may need to do some byte swapping (because this data can come from variouse systems that could be either big or little endian). So I am singling out the data I need, but now I need to add the ability to byte swap the data. Keep in mind that it would be best if I can do this in SQL so that it is portable. I realize that it can easily be done in C, but that makes my code less portable (which is also a requirement, to have it portable that is). So does anybody know of a MySQL function that is already implemented to do byte swapping? or know of a way to implement this in SQL? If not, is my only other option to write a UDF? Thanks for any help. Accomplishing the impossible means only that the boss will add it to your regular duties. David Godsey -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Byte Swapping (Re Post)
David Godsey [EMAIL PROTECTED] wrote on 02/14/2006 03:28:41 PM: Well, just thought I'd try one more time because I didn't get an answer to my question last time. So what I have is a random data stream that is sent in raw form, and based on some data definition, I can assemble with the correct data types and such. One of my requirements is that I have to store the data in raw form, and when I pull the data out, it displays based on the configuration (with the correct data types and such). So floats and doubles are IEEE standards so I don't have to worry about those, however with integer types, I may need to do some byte swapping (because this data can come from variouse systems that could be either big or little endian). So I am singling out the data I need, but now I need to add the ability to byte swap the data. Keep in mind that it would be best if I can do this in SQL so that it is portable. I realize that it can easily be done in C, but that makes my code less portable (which is also a requirement, to have it portable that is). So does anybody know of a MySQL function that is already implemented to do byte swapping? or know of a way to implement this in SQL? If not, is my only other option to write a UDF? Thanks for any help. Accomplishing the impossible means only that the boss will add it to your regular duties. David Godsey Native functions? No. Something you can cobble together? Yes. There should be several ways you can deal with your data as a string of binary characters. Just re-sequence those and you should have your bytes swapped. One idea is to use the substring functions directly on your BINARY string. Another is to use the substring functions in combination with HEX()/UNHEX() to work on an escaped version of your BINARY string. Sorry or the lame ideas but usually things like this are not handled at the database layer but rather in the application layer. Depending on which version of MySQL you are using you may be able to define a FUNCTION (a different creature than a UDF) or a STORED PROCEDURE to do the swapping. Both will be pure SQL and should meet your compatibility needs. Neither will be as fast as creating and registering a UDF, though. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Installation Issue
Imran, I noticed couple of permissions were not correct.I changed mysql.mysql. Still Ihave been getting following errors. 060214 15:53:05 mysqld started 060214 15:53:05 InnoDB: Started; log sequence number 0 43655 060214 15:53:06 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.host' doesn't exist 060214 15:53:06 mysqld ended Please advice. thanks Imran Chaudhry [EMAIL PROTECTED] wrote: On 2/14/06, Ravi Kumar wrote: Starting mysql with root.I tried withn mysql user account also but still same error. thanks Ravi, Assuming you are starting MySQL with mysqld_safe, then it will invoke the MySQL server as the mysql user. I suspect the cause is that /var/lib/mysql is not owned by mysql If so, as superuser: chown -R mysql:mysql /var/lib/mysql Regards, Imran Chaudhry -- http://www.ImranChaudhry.info MySQL Database Management Design Services -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] - Yahoo! Mail Use Photomail to share photos without annoying attachments.
Re: Byte Swapping (Re Post)
[EMAIL PROTECTED] wrote: David Godsey [EMAIL PROTECTED] wrote on 02/14/2006 03:28:41 PM: Well, just thought I'd try one more time because I didn't get an answer to my question last time. So what I have is a random data stream that is sent in raw form, and based on some data definition, I can assemble with the correct data types and such. One of my requirements is that I have to store the data in raw form, and when I pull the data out, it displays based on the configuration (with the correct data types and such). So floats and doubles are IEEE standards so I don't have to worry about those, however with integer types, I may need to do some byte swapping (because this data can come from variouse systems that could be either big or little endian). So I am singling out the data I need, but now I need to add the ability to byte swap the data. Keep in mind that it would be best if I can do this in SQL so that it is portable. I realize that it can easily be done in C, but that makes my code less portable (which is also a requirement, to have it portable that is). So does anybody know of a MySQL function that is already implemented to do byte swapping? or know of a way to implement this in SQL? If not, is my only other option to write a UDF? Thanks for any help. Accomplishing the impossible means only that the boss will add it to your regular duties. David Godsey Native functions? No. Something you can cobble together? Yes. There should be several ways you can deal with your data as a string of binary characters. Just re-sequence those and you should have your bytes swapped. One idea is to use the substring functions directly on your BINARY string. Another is to use the substring functions in combination with HEX()/UNHEX() to work on an escaped version of your BINARY string. Would not the first zero value character terminate the substring, rendering it invalid? Sorry or the lame ideas but usually things like this are not handled at the database layer but rather in the application layer. Depending on which version of MySQL you are using you may be able to define a FUNCTION (a different creature than a UDF) or a STORED PROCEDURE to do the swapping. Both will be pure SQL and should meet your compatibility needs. Neither will be as fast as creating and registering a UDF, though. Shawn Green Database Administrator Unimin Corporation - Spruce Pine -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Installation Issue
It looks more like you haven't run the mysql_install_db script. From the manual : http://dev.mysql.com/doc/refman/5.0/en/unix-post-installation.html If necessary, run the mysql_install_db program to set up the initial MySQL grant tables containing the privileges that determine how users are allowed to connect to the server. You'll need to do this if you used a distribution type for which the installation procedure doesn't run the program for you. Typically, mysql_install_db needs to be run only the first time you install MySQL, so you can skip this step if you are upgrading an existing installation, However, mysql_install_db does not overwrite any existing privilege tables, so it should be safe to run in any circumstances. To initialize the grant tables, use one of the following commands, depending on whether mysql_install_db is located in the bin or scripts directory: shell bin/mysql_install_db --user=mysql shell scripts/mysql_install_db --user=mysql snip snip mysql_install_db creates several tables in the mysql database, including user, db, host, tables_priv, columns_priv, and func, as well as others. See Section 5.8, The MySQL Access Privilege System, for a complete listing and description of these tables. snip snip If you have trouble with mysql_install_db at this point, see Section 2.9.2.1, Problems Running mysql_install_db. Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: Ravi Kumar [mailto:[EMAIL PROTECTED] Sent: Wednesday, 15 February 2006 7:27 AM To: Imran Chaudhry Cc: Peter M. Groen; mysql@lists.mysql.com Subject: Re: Installation Issue Imran, I noticed couple of permissions were not correct.I changed mysql.mysql. Still Ihave been getting following errors. 060214 15:53:05 mysqld started 060214 15:53:05 InnoDB: Started; log sequence number 0 43655 060214 15:53:06 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.host' doesn't exist 060214 15:53:06 mysqld ended Please advice. thanks Imran Chaudhry [EMAIL PROTECTED] wrote: On 2/14/06, Ravi Kumar wrote: Starting mysql with root.I tried withn mysql user account also but still same error. thanks Ravi, Assuming you are starting MySQL with mysqld_safe, then it will invoke the MySQL server as the mysql user. I suspect the cause is that /var/lib/mysql is not owned by mysql If so, as superuser: chown -R mysql:mysql /var/lib/mysql Regards, Imran Chaudhry -- http://www.ImranChaudhry.info MySQL Database Management Design Services -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] - Yahoo! Mail Use Photomail to share photos without annoying attachments. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Installation Issue
If you have not installed MySQL before, you must create the MySQL grant tables: shell scripts/mysql_install_db --user=mysql Keith In theory, theory and practice are the same; In practice they are not. On Tue, 14 Feb 2006, Ravi Kumar wrote: To: Imran Chaudhry [EMAIL PROTECTED] From: Ravi Kumar [EMAIL PROTECTED] Subject: Re: Installation Issue Imran, I noticed couple of permissions were not correct.I changed mysql.mysql. Still Ihave been getting following errors. 060214 15:53:05 mysqld started 060214 15:53:05 InnoDB: Started; log sequence number 0 43655 060214 15:53:06 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.host' doesn't exist 060214 15:53:06 mysqld ended Please advice. thanks Imran Chaudhry [EMAIL PROTECTED] wrote: On 2/14/06, Ravi Kumar wrote: Starting mysql with root.I tried withn mysql user account also but still same error. thanks Ravi, Assuming you are starting MySQL with mysqld_safe, then it will invoke the MySQL server as the mysql user. I suspect the cause is that /var/lib/mysql is not owned by mysql If so, as superuser: chown -R mysql:mysql /var/lib/mysql Regards, Imran Chaudhry -- http://www.ImranChaudhry.info MySQL Database Management Design Services -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Byte Swapping (Re Post)
gerald_clark [EMAIL PROTECTED] wrote on 02/14/2006 03:59:21 PM: [EMAIL PROTECTED] wrote: David Godsey [EMAIL PROTECTED] wrote on 02/14/2006 03:28:41 PM: Well, just thought I'd try one more time because I didn't get an answer to my question last time. So what I have is a random data stream that is sent in raw form, and based on some data definition, I can assemble with the correct data types and such. One of my requirements is that I have to store the data in raw form, and when I pull the data out, it displays based on the configuration (with the correct data types and such). So floats and doubles are IEEE standards so I don't have to worry about those, however with integer types, I may need to do some byte swapping (because this data can come from variouse systems that could be either big or little endian). So I am singling out the data I need, but now I need to add the ability to byte swap the data. Keep in mind that it would be best if I can do this in SQL so that it is portable. I realize that it can easily be done in C, but that makes my code less portable (which is also a requirement, to have it portable that is). So does anybody know of a MySQL function that is already implemented to do byte swapping? or know of a way to implement this in SQL? If not, is my only other option to write a UDF? Thanks for any help. Accomplishing the impossible means only that the boss will add it to your regular duties. David Godsey Native functions? No. Something you can cobble together? Yes. There should be several ways you can deal with your data as a string of binary characters. Just re-sequence those and you should have your bytes swapped. One idea is to use the substring functions directly on your BINARY string. Another is to use the substring functions in combination with HEX()/UNHEX() to work on an escaped version of your BINARY string. Would not the first zero value character terminate the substring, rendering it invalid? Sorry or the lame ideas but usually things like this are not handled at the database layer but rather in the application layer. Depending on which version of MySQL you are using you may be able to define a FUNCTION (a different creature than a UDF) or a STORED PROCEDURE to do the swapping. Both will be pure SQL and should meet your compatibility needs. Neither will be as fast as creating and registering a UDF, though. Shawn Green Database Administrator Unimin Corporation - Spruce Pine I don't know if it will or if it won't. The original poster (David Godsey) seems to have no trouble extracting specific subsections of raw data from his blob fields. I just assume that working with chunks of raw data that contained zeroes in them was no problem for him. His need is to somehow binarily invert sections of each number (the INET_... functions could also help) in order to convert big-endian to little-endian and vice versa. I was just trying to help point him to some possible functions that may help him to do that. Hopefully he will post back with what works and what doesn't. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
RE: Byte Swapping (Re Post)
If the order of the bytes is opposite between big-endian and little-endian, then if you can get the bytes in a string REVERSE() should flip the order. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, February 14, 2006 3:25 PM To: gerald_clark Cc: mysql@lists.mysql.com; David Godsey Subject: Re: Byte Swapping (Re Post) gerald_clark [EMAIL PROTECTED] wrote on 02/14/2006 03:59:21 PM: [EMAIL PROTECTED] wrote: David Godsey [EMAIL PROTECTED] wrote on 02/14/2006 03:28:41 PM: Well, just thought I'd try one more time because I didn't get an answer to my question last time. So what I have is a random data stream that is sent in raw form, and based on some data definition, I can assemble with the correct data types and such. One of my requirements is that I have to store the data in raw form, and when I pull the data out, it displays based on the configuration (with the correct data types and such). So floats and doubles are IEEE standards so I don't have to worry about those, however with integer types, I may need to do some byte swapping (because this data can come from variouse systems that could be either big or little endian). So I am singling out the data I need, but now I need to add the ability to byte swap the data. Keep in mind that it would be best if I can do this in SQL so that it is portable. I realize that it can easily be done in C, but that makes my code less portable (which is also a requirement, to have it portable that is). So does anybody know of a MySQL function that is already implemented to do byte swapping? or know of a way to implement this in SQL? If not, is my only other option to write a UDF? Thanks for any help. Accomplishing the impossible means only that the boss will add it to your regular duties. David Godsey Native functions? No. Something you can cobble together? Yes. There should be several ways you can deal with your data as a string of binary characters. Just re-sequence those and you should have your bytes swapped. One idea is to use the substring functions directly on your BINARY string. Another is to use the substring functions in combination with HEX()/UNHEX() to work on an escaped version of your BINARY string. Would not the first zero value character terminate the substring, rendering it invalid? Sorry or the lame ideas but usually things like this are not handled at the database layer but rather in the application layer. Depending on which version of MySQL you are using you may be able to define a FUNCTION (a different creature than a UDF) or a STORED PROCEDURE to do the swapping. Both will be pure SQL and should meet your compatibility needs. Neither will be as fast as creating and registering a UDF, though. Shawn Green Database Administrator Unimin Corporation - Spruce Pine I don't know if it will or if it won't. The original poster (David Godsey) seems to have no trouble extracting specific subsections of raw data from his blob fields. I just assume that working with chunks of raw data that contained zeroes in them was no problem for him. His need is to somehow binarily invert sections of each number (the INET_... functions could also help) in order to convert big-endian to little-endian and vice versa. I was just trying to help point him to some possible functions that may help him to do that. Hopefully he will post back with what works and what doesn't. Shawn Green Database Administrator Unimin Corporation - Spruce Pine -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Byte Swapping (Re Post)
-Original Message- From: Gordon Bruce [mailto:[EMAIL PROTECTED] Sent: Tuesday, February 14, 2006 15:42 To: [EMAIL PROTECTED]; gerald_clark Cc: mysql@lists.mysql.com; David Godsey Subject: RE: Byte Swapping (Re Post) If the order of the bytes is opposite between big-endian and little-endian, then if you can get the bytes in a string REVERSE() should flip the order. REVERSE would alter the order of the bytes. To convert between big-endian and little-endian, I believe that you need to reverse the order of the bits in either a byte or a word. Dirk Bremer - Senior Systems Engineer - ESS/AMS - NISC Lake St. Louis MO - USA Central Time Zone 636-755-2652 fax 636-755-2503 [EMAIL PROTECTED] www.nisc.coop -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Stored procedure issue.
I'm having some issues creating a stored procedure to optimize tables
in the database.
PREPARE accepts only CREATE TABLE, DELETE, DO, INSERT, REPLACE, SELECT,
SET and UPDATE.
PB
-
DreamWerx wrote:
I'm having some issues creating a stored procedure to optimize tables
in the database. I'm pulling the table names from the information
schema. The tablename doesn't seem to be correctly being replaced in
the optimize command..
I've tried used prepared statements which seem to correctly replace
the tableName, but I get an error that prepared statements do not
support that kind of query.
With this current version the error is: table queue.tableName doesn't exist..
Any thoughts from some SP gurus? Thanks.
--
DELIMITER $$;
DROP PROCEDURE IF EXISTS `queue`.`sp_OptimizeDatabase`$$
CREATE PROCEDURE `queue`.`sp_OptimizeDatabase` ()
BEGIN
DECLARE exitValue INT;
DECLARE tableName CHAR(120);
DECLARE cursorList CURSOR FOR
SELECT TABLE_NAME FROM information_schema.tables where
TABLE_SCHEMA = 'queue' AND TABLE_TYPE = 'BASE TABLE';
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET exitValue = 1;
OPEN cursorList;
REPEAT
FETCH cursorList INTO tableName;
OPTIMIZE TABLE tableName;
-- SET @optSQL := concat('OPTIMIZE TABLE ', tableName);
-- PREPARE pOptimize FROM @optSQL;
-- EXECUTE pOptimize;
-- DEALLOCATE PREPARE pOptimize;
UNTIL exitValue = 1
END REPEAT;
CLOSE cursorList;
END$$
DELIMITER ;$$
-
--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 267.15.7/259 - Release Date: 2/13/2006
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: General DB Design Question - How to avoid redundancy in table relationships
Scott Klarenbach wrote: These are the tables in question: RFQ (Request for Quote) Part Inventory Inventory items ALWAYS have a partID. RFQ items ALWAYS have a partID. However, sometimes, RFQ items have an inventoryID as well. Now, we have a redundancy problem. Because, in those instances when the RFQ has an inventoryID, the partID should be derived from the inventoryID. If there is no inventoryID, then the partID needs to be stored directly in the RFQ table. We don't want to have both the inventoryID and the partID in the RFQ table, because it opens up data integrity issues. ie, what if the RFQ item shows inventoryID 2, and partID 1...but inventoryID 2 is associated to partID 2. Now which partID is correct? They can't both be right. Since there have not been a lot of responses I decided to jump in. It sounds to me like we have real-world object behavior mixed up with the data model. Example RFQ items ALWAYS have a partID If there is no inventoryID, then the partID needs to be stored directly in the RFQ table. Why? Who or what is enforcing this? Can we look at overall object behavior, then come up with a model that supports the behavior with no preconceptions of table structure. I assume that an RFQ item is a document (paper or eletronic). What does one look like? From your description it will always have a partID and may have an inventoryID. Who populates these fields? Why is there a redundancy in the first place? Who checks to see that the direct partID matches the derived partID? How about leaving partID and inventoryID out of the RFQ table, and adding an association table that relates a RFQ to either a partID or an inventoryID. An attribute of this table would distinguish partID from an inventoryID. Business logic would ensure that only one entry gets into this table per RFQ, and could also validate that the direct partID matches the derived partID I'm sure this type of problem is run up against all the time, and I'm wondering what the best practice methodology is from experienced DBA's. This was a simple example; however, we are running into the problem system wide. For example, a quote table has an OPTIONAL RFQ ID, and a mandatory contactID. The RFQ table has a mandatory contactID. If the quote table has an RFQID, we want to derive the contactID from the RFQID. If the quote has NO RFQID, then we need to store the contactID directly in the quote table. In those instances where there IS an RFQID in the quote table, we end up storing the contactID twice. Once in the quote table, and once in the association between the RFQ/Contact table. Same problem as above: integrity and poor overall design. Same issue here. Remove the IDs from the quote and RFQ table and create another association table. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql oddity
Hi folks. I've been compiling my own apps for over a decade, but it seems like it's time to get -with- the times, and start using precompiled binaries more. So I got a set of matching set of precomiled apache2/mysql5/php5 off of ibiblio for a Solaris 9/Sparc box. However, even though I have apache2 running with php5, and mysql5 is running (mysql4 is not), phpinfo is telling me that it was built against mysql4, and that it is using: mysql MySQL Support enabled Active Persistent Links 0 Active Links 0 Client API version 4.1.18 MYSQL_MODULE_TYPE external MYSQL_SOCKET /tmp/mysql.sock MYSQL_INCLUDE -I/opt/csw/mysql4/include/mysql MYSQL_LIBS -L/opt/csw/mysql4/lib/mysql -lmysqlclient So it kind of seems like maybe I have php5 using mysql5 via the mysql4 client API. Does this sound about right? And if so, what would I lose by leaving it this way? That is, are there useful things in later versions of the mysql client API? -Is- there a version 5.* of the mysql client API? Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Converting database and its tables to UTF-8
Is this the only way? I was hoping that phpMyAdmin would have a nice function for this. What is the reason for not being able to use String types when using UFT-8? Maybe I have to learn more about the UFT-8 to find the answer about that? Best regards, Peter Lauri _ From: Gabriel PREDA [mailto:[EMAIL PROTECTED] Sent: Tuesday, February 14, 2006 6:30 PM To: mysql@lists.mysql.com Cc: Peter Lauri Subject: Re: Converting database and its tables to UTF-8 Hi Peter, That will be a lot of work ! 1. First make a back-up... it's always a good ideea ! 2. For every table in the database alter String Types into BINARY string types that means: - (VAR)CHAR(M) will become (VAR)CHAR(M) BINARY or (VAR)BINARY(M) - TINYTEXT, TEXT, MEDIUMTEXT, and LONGTEXT will become respectively TINYBLOB, BLOB, MEDIUMBLOB, and LONGBLOB 3. Alter the database isuing ALTER DATABASE `database_name` DEFAULT CHARACTER SET utf8; 4. Alter each table issuing: ALTER TABLE `table_name` DEFAULT CHARSET=utf8; 5. Alter back the column types. This should do it ! And because you issued the ALTER DATABASE from now on all tables in that database will be in utf8 ... and falling down any column will be utf8 if you don't specify explicitly anything else... Yes you can have a charset on the database in that database tables with different chartsets and even in a table you can have columns with other chartsets... Gabriel PREDA Senior Web Developer On 2/14/06, Peter Lauri [EMAIL PROTECTED] wrote: Hi, I have a database with around 40 tables that needs to be converted to UTF-8 to support multi languages. What is the best procedure to do this? And is it any way to change the default charset to UFT-8 so tables by default will become UFT-8? And can I have one table with different fields with different charset? Best regards, Peter Lauri
Re: Unable to duplicate a database at home, possible encoding problem
You can set character set for each column or set default character set for each table when CREATE TABLE. Also you can set default character set for each database when CREATE DATABASE. - Original Message - From: Dave M G [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Wednesday, February 15, 2006 1:54 AM Subject: 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
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]
DWHS inc.
Hello, Is there anything we can do to help the MySQL project, we are firm believers and would like to contribute in some way. We have a several 100 mps connections and server space. Thank you, Charles DWHS Inc. Manager http://www.dwhs.com http://www.dwhs.com/hostingforum/ https://dwhs.net/secure/ The message is meant to be kept confidential and by reading it you agree not to share it with public media or business competitors of DWHS Web Hosting. For more information on our privacy and regulations please call 866 660 HOST during normal business hours. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: DWHS inc.
[EMAIL PROTECTED] wrote: Hello, Is there anything we can do to help the MySQL project, we are firm believers and would like to contribute in some way. We have a several 100 mps connections and server space. Thank you, Charles DWHS Inc. Manager http://www.dwhs.com http://www.dwhs.com/hostingforum/ https://dwhs.net/secure/ The message is meant to be kept confidential and by reading it you agree not to share it with public media or business competitors of DWHS Web Hosting. For more information on our privacy and regulations please call 866 660 HOST during normal business hours. Hi - http://dev.mysql.com/downloads/how-to-mirror.html Thanks for the offer! -dant -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
