Re: MySQL could not support bit storage?
Computer memory is not bit-addressable, how can you store a bit as an independent data unit ? Other than as part of a record, there are no C data types (which is used to implement MySQL kernel) that can process bit as a data type. If i miss the context, please advise me back. thanks On Mon, May 11, 2009 at 7:42 AM, Moon's Father yueliangdao0...@gmail.com wrote: Hi. MySQL only has one datatype called bit, but its smallest storage is one byte. How to save a bit on disk, but not a byte? Any reply is appreciated. -- David Yeung, MySQL Senior Support Engineer, Sun Gold Partner. My Blog:http://yueliangdao0608.cublog.cn Comanpy: http://www.actionsky.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Unix compress equivalent
Hi Olaf, If you only need to compress the column *while* loading it from the csv file then use load data infile with a user variable to do the compression at load time,. mysql create table t(uncompressed varchar(4000), compressed varbinary(1000)); Query OK, 0 rows affected (0.07 sec) mysql \! echo -e 'abcde\nfghijk\n' /tmp/test.csv mysql load data infile '/tmp/test.csv' into table t (@raw) set uncompressed = @raw, compressed = compress(@raw); Query OK, 3 rows affected (0.00 sec) Records: 3 Deleted: 0 Skipped: 0 Warnings: 0 mysql select uncompressed, @a:=hex(compress(uncompressed)), @b:=hex(compressed), @a = @b from t; +--+--+--+-+ | uncompressed | @a:=hex(compress(uncompressed)) | @b:=hex(compressed) | @a = @b | +--+--+--+-+ | abcde| 0500789C4B4C4A4E49050005C801F0 | 0500789C4B4C4A4E49050005C801F0 | 1 | | fghijk | 0600789C4B4BCFC8CCCA060008870274 | 0600789C4B4BCFC8CCCA060008870274 | 1 | | | | | 1 | +--+--+--+-+ 3 rows in set (0.04 sec) Cheers, -Janek On Fri, 2009-05-08 at 12:29 -0400, Olaf Stein wrote: Hi all What is the equivalent in unix (more specifically python) to the compress() function. I am trying to make csv file for use with load data infile and am wondering how to compress the strings that I would usually compress with compress() in a regular sql statement. The field I am writing this into is longblob and I need the compressed version here to be identical to what compress() would do Thanks olaf - Confidentiality Notice: The following mail message, including any attachments, is for the sole use of the intended recipient(s) and may contain confidential and privileged information. The recipient is responsible to maintain the confidentiality of this information and to use the information only for authorized purposes. If you are not the intended recipient (or authorized to receive information for the intended recipient), you are hereby notified that any review, use, disclosure, distribution, copying, printing, or action taken in reliance on the contents of this e-mail is strictly prohibited. If you have received this communication in error, please notify us immediately by reply e-mail and destroy all copies of the original message. Thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Unix compress equivalent
Thanks This is exactly what I needed Olaf On 5/11/09 7:15 AM, Janek Bogucki janek.bogu...@studylink.com wrote: Hi Olaf, If you only need to compress the column *while* loading it from the csv file then use load data infile with a user variable to do the compression at load time,. mysql create table t(uncompressed varchar(4000), compressed varbinary(1000)); Query OK, 0 rows affected (0.07 sec) mysql \! echo -e 'abcde\nfghijk\n' /tmp/test.csv mysql load data infile '/tmp/test.csv' into table t (@raw) set uncompressed = @raw, compressed = compress(@raw); Query OK, 3 rows affected (0.00 sec) Records: 3 Deleted: 0 Skipped: 0 Warnings: 0 mysql select uncompressed, @a:=hex(compress(uncompressed)), @b:=hex(compressed), @a = @b from t; +--+--+--- ---+-+ | uncompressed | @a:=hex(compress(uncompressed)) | @b:=hex(compressed) | @a = @b | +--+--+--- ---+-+ | abcde| 0500789C4B4C4A4E49050005C801F0 | 0500789C4B4C4A4E49050005C801F0 | 1 | | fghijk | 0600789C4B4BCFC8CCCA060008870274 | 0600789C4B4BCFC8CCCA060008870274 | 1 | | | | | 1 | +--+--+--- ---+-+ 3 rows in set (0.04 sec) Cheers, -Janek On Fri, 2009-05-08 at 12:29 -0400, Olaf Stein wrote: Hi all What is the equivalent in unix (more specifically python) to the compress() function. I am trying to make csv file for use with load data infile and am wondering how to compress the strings that I would usually compress with compress() in a regular sql statement. The field I am writing this into is longblob and I need the compressed version here to be identical to what compress() would do Thanks olaf - Confidentiality Notice: The following mail message, including any attachments, is for the sole use of the intended recipient(s) and may contain confidential and privileged information. The recipient is responsible to maintain the confidentiality of this information and to use the information only for authorized purposes. If you are not the intended recipient (or authorized to receive information for the intended recipient), you are hereby notified that any review, use, disclosure, distribution, copying, printing, or action taken in reliance on the contents of this e-mail is strictly prohibited. If you have received this communication in error, please notify us immediately by reply e-mail and destroy all copies of the original message. Thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
store paths and Filenames in MySql
is there a way i can traverse a directory on my server,in php and then have the paths Be Saved as \\server1\projects\ ... etc and actually store the filenames NOT the files and paths in mysql ? thanks for any help -- 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 could not support bit storage?
On Sun, May 10, 2009 at 10:12 PM, Moon's Father yueliangdao0...@gmail.com wrote: Hi. MySQL only has one datatype called bit, but its smallest storage is one byte. How to save a bit on disk, but not a byte? In some cases, CHAR(0) NULL can actually use one bit on disk. You either store the empty string '', or you leave the column NULL. If it is NULL, there is one bit in a bitmask that gets set. If it stores the empty string, it uses no space, and the NULL bit is unset. This is a stupid hack that is probably not a good idea in the general case. Of course, the bitmap of NULL-ness is larger than one bit, so it makes no sense to do this if there is only one such column in the table. And in that case, you might be better off using an integer and packing many bits together into it. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
CLI custom prompt redraw history issue
I use a custom prompt of: \n\U:mysql-\v[\l]\n\d\_ on 5.1.32 Source distribution in the CLI. The problem I'm noticing is that when I have a multi-line prompt, my history provided with the up arrow can't compensate for the length of the prompt string and can't correctly redraw my previous history statements to the CLI. Has or is anyone else running into this? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Merging Databases
Hi, I have a lot of databases that have the exact same tables and each table has the exact same column structure. I'm looking at merging two of these databases together into a single database (Company A bought Company B and wants the data from A combined into B now). I've been tossing around the idea of looking in database B at each table that would need to be merged and simply adding the last ID number to every ID number in database A's tables. For example, in table1 in B's data, the last ID number is 2000, could we simply add 2000 to every ID number in table1 in A's data? Could we then export (SELECT INTO OUTFILE) from A's data and import (LOAD DATA) into B's data? Has anyone done something like this before? Did you have problems? -- - Johnny Withers 601.209.4985 joh...@pixelated.net
RE: Merging Databases
-Original Message- From: Johnny Withers [mailto:joh...@pixelated.net] Sent: Monday, May 11, 2009 3:30 PM To: MySQL General List Subject: Merging Databases Hi, I have a lot of databases that have the exact same tables and each table has the exact same column structure. I'm looking at merging two of these databases together into a single database (Company A bought Company B and wants the data from A combined into B now). I've been tossing around the idea of looking in database B at each table that would need to be merged and simply adding the last ID number to every ID number in database A's tables. For example, in table1 in B's data, the last ID number is 2000, could we simply add 2000 to every ID number in table1 in A's data? Could we then export (SELECT INTO OUTFILE) from A's data and import (LOAD DATA) into B's data? Has anyone done something like this before? Did you have problems? -- - Johnny Withers 601.209.4985 joh...@pixelated.net --- Why not create a view and just concatenate on an identifier? This way the data can be kept in the same forms. Or, if you do want to have it as one table, you can use a select insert statement to move from one to another. Build the select query first to get the data looking like you want it, then convert it when you think you are ready. Of course, backups are your friend in any case. :) Cheers, Craig This e-mail, including any attachments, may be confidential, privileged or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Merging Databases
We don't want to use a view because then this database will not be consistent with the others. We can't simply use a select from .. insert into because when we renumber table1's ID column, items in table2 and 3 and so on may link to the ID column in that table. So we need to update the ID column in table1, then add the same # to the table1_id columns in any other table. After we do this, we could do the select from.. insert into method I suppose. -jw On Mon, May 11, 2009 at 2:49 PM, Weston, Craig (OFT) craig.wes...@oft.state.ny.us wrote: -Original Message- From: Johnny Withers [mailto:joh...@pixelated.net] Sent: Monday, May 11, 2009 3:30 PM To: MySQL General List Subject: Merging Databases Hi, I have a lot of databases that have the exact same tables and each table has the exact same column structure. I'm looking at merging two of these databases together into a single database (Company A bought Company B and wants the data from A combined into B now). I've been tossing around the idea of looking in database B at each table that would need to be merged and simply adding the last ID number to every ID number in database A's tables. For example, in table1 in B's data, the last ID number is 2000, could we simply add 2000 to every ID number in table1 in A's data? Could we then export (SELECT INTO OUTFILE) from A's data and import (LOAD DATA) into B's data? Has anyone done something like this before? Did you have problems? -- - Johnny Withers 601.209.4985 joh...@pixelated.net --- Why not create a view and just concatenate on an identifier? This way the data can be kept in the same forms. Or, if you do want to have it as one table, you can use a select insert statement to move from one to another. Build the select query first to get the data looking like you want it, then convert it when you think you are ready. Of course, backups are your friend in any case. :) Cheers, Craig This e-mail, including any attachments, may be confidential, privileged or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=joh...@pixelated.net -- - Johnny Withers 601.209.4985 joh...@pixelated.net
Re: Merging Databases
On 11-May-09, at 2:09 PM, Johnny Withers wrote: We don't want to use a view because then this database will not be consistent with the others. We can't simply use a select from .. insert into because when we renumber table1's ID column, items in table2 and 3 and so on may link to the ID column in that table. So we need to update the ID column in table1, then add the same # to the table1_id columns in any other table. After we do this, we could do the select from.. insert into method I suppose. I've done this before to merge separate databases, it will work as long as you plan it out right. Some things I ran into: 1) If the other database you are copying into is still active make sure you leave enough room between the current top record and where you expect to start the new records - plan for a couple of weeks activity even. Schedules can change, and you may do the update and dump but then be delayed on the import. 2) Its a good idea to add a where clause to guard against accidental extra runs, or in the case of foreign keys to avoid rewriting data inserted since you modified the main table, i.e. update t1 set pk=pk+10 where pk 10; update t2 set fk=fk+10 where fk 10; That's saved me from a few unexpected consequences. Cheers, Chris. - Chris Clarke Principal Consultant C4 Consulting High performance IT solutions http://cfourconsulting.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Merging Databases
If you are merging table A and table B and say, table A's auto-increment id is up to 2000, just pick a nice round number like 3000 and add it to the auto-increment ID column of table B with something like this: UPDATE tableB SET id = id + 3000; Then do the same to all the fields in other tables that link to tableB's auto-increment ID column. Once that's done, merge the tables with something like: INSERT INTO tableA SELECT * FROM tableB; And do the same with the other tables (if they have their own autoincrement ids then you should leave that out of the insert into select from (unless those auto-increment ids are referenced by other tables in which case you'll have to do the same thing cascading down levels of referential id columns..) Should do the trick. Andrew -Original Message- From: Johnny Withers [mailto:joh...@pixelated.net] Sent: 11 May 2009 22:10 To: Weston, Craig (OFT) Cc: MySQL General List Subject: Re: Merging Databases We don't want to use a view because then this database will not be consistent with the others. We can't simply use a select from .. insert into because when we renumber table1's ID column, items in table2 and 3 and so on may link to the ID column in that table. So we need to update the ID column in table1, then add the same # to the table1_id columns in any other table. After we do this, we could do the select from.. insert into method I suppose. -jw On Mon, May 11, 2009 at 2:49 PM, Weston, Craig (OFT) craig.wes...@oft.state.ny.us wrote: -Original Message- From: Johnny Withers [mailto:joh...@pixelated.net] Sent: Monday, May 11, 2009 3:30 PM To: MySQL General List Subject: Merging Databases Hi, I have a lot of databases that have the exact same tables and each table has the exact same column structure. I'm looking at merging two of these databases together into a single database (Company A bought Company B and wants the data from A combined into B now). I've been tossing around the idea of looking in database B at each table that would need to be merged and simply adding the last ID number to every ID number in database A's tables. For example, in table1 in B's data, the last ID number is 2000, could we simply add 2000 to every ID number in table1 in A's data? Could we then export (SELECT INTO OUTFILE) from A's data and import (LOAD DATA) into B's data? Has anyone done something like this before? Did you have problems? -- - Johnny Withers 601.209.4985 joh...@pixelated.net --- Why not create a view and just concatenate on an identifier? This way the data can be kept in the same forms. Or, if you do want to have it as one table, you can use a select insert statement to move from one to another. Build the select query first to get the data looking like you want it, then convert it when you think you are ready. Of course, backups are your friend in any case. :) Cheers, Craig This e-mail, including any attachments, may be confidential, privileged or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=joh...@pixelated.net -- - Johnny Withers 601.209.4985 joh...@pixelated.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
mysql-bin maintenance
Hi, In the master database, there are plenty of mysql-bin.X. It occupied almost all the disk space. As I know, the files should be removed by issuing reset master. Below are my questions. -Do I need to stop replication before issuing reset master? If yes, do I need to rebuild the replication after reset master? If no, does the existing replication broken after reset master? ango -- 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-bin maintenance
On Mon, May 11, 2009 6:41 pm, Rilawich Ango wrote: Hi, In the master database, there are plenty of mysql-bin.X. It occupied almost all the disk space. As I know, the files should be removed by issuing reset master. Below are my questions. -Do I need to stop replication before issuing reset master? If yes, do I need to rebuild the replication after reset master? If no, does the existing replication broken after reset master? ango See http://dev.mysql.com/doc/refman/5.0/en/purge-binary-logs.html (or the relevant URL for your language/version). And you can use the expire_logs_days system variable to have MySQL automatically purge old logs. - steve -- 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 could not support bit storage?
Thanks for your sincerely reply. On Mon, May 11, 2009 at 11:04 PM, Baron Schwartz ba...@xaprb.com wrote: On Sun, May 10, 2009 at 10:12 PM, Moon's Father yueliangdao0...@gmail.com wrote: Hi. MySQL only has one datatype called bit, but its smallest storage is one byte. How to save a bit on disk, but not a byte? In some cases, CHAR(0) NULL can actually use one bit on disk. You either store the empty string '', or you leave the column NULL. If it is NULL, there is one bit in a bitmask that gets set. If it stores the empty string, it uses no space, and the NULL bit is unset. This is a stupid hack that is probably not a good idea in the general case. Of course, the bitmap of NULL-ness is larger than one bit, so it makes no sense to do this if there is only one such column in the table. And in that case, you might be better off using an integer and packing many bits together into it. -- David Yeung, MySQL Senior Support Engineer, Sun Gold Partner. My Blog:http://yueliangdao0608.cublog.cn Comanpy: http://www.actionsky.com
