Re: Rollback is not take effect on MySQL 5.0.18
Dear Sir, The status of mysql: mysqlshow status; +---+--+ | Variable_name | Value| +---+--+ | Aborted_clients | 0| | Aborted_connects | 0| | Binlog_cache_disk_use | 0| | Binlog_cache_use | 0| | Bytes_received| 148 | | Bytes_sent| 5695 | | Com_admin_commands| 0| | Com_alter_db | 0| | Com_alter_table | 0| | Com_analyze | 0| | Com_backup_table | 0| | Com_begin | 0| | Com_change_db | 1| | Com_change_master | 0| | Com_check | 0| | Com_checksum | 0| | Com_commit| 0| | Com_create_db | 0| | Com_create_function | 0| | Com_create_index | 0| | Com_create_table | 0| | Com_dealloc_sql | 0| | Com_delete| 0| | Com_delete_multi | 0| | Com_do| 0| | Com_drop_db | 0| | Com_drop_function | 0| | Com_drop_index| 0| | Com_drop_table| 0| | Com_drop_user | 0| | Com_execute_sql | 0| | Com_flush | 0| | Com_grant | 0| | Com_ha_close | 0| | Com_ha_open | 0| | Com_ha_read | 0| | Com_help | 0| | Com_insert| 0| | Com_insert_select | 0| | Com_kill | 0| | Com_load | 0| | Com_load_master_data | 0| | Com_load_master_table | 0| | Com_lock_tables | 0| | Com_optimize | 0| | Com_preload_keys | 0| | Com_prepare_sql | 0| | Com_purge | 0| | Com_purge_before_date | 0| | Com_rename_table | 0| | Com_repair| 0| | Com_replace | 0| | Com_replace_select| 0| | Com_reset | 0| | Com_restore_table | 0| | Com_revoke| 0| | Com_revoke_all| 0| | Com_rollback | 0| | Com_savepoint | 0| | Com_select| 2| | Com_set_option| 0| | Com_show_binlog_events| 0| | Com_show_binlogs | 0| | Com_show_charsets | 0| | Com_show_collations | 0| | Com_show_column_types | 0| | Com_show_create_db| 0| | Com_show_create_table | 0| | Com_show_databases| 0| | Com_show_errors | 0| | Com_show_fields | 0| | Com_show_grants | 0| | Com_show_innodb_status| 0| | Com_show_keys | 0| | Com_show_logs | 0| | Com_show_master_status| 0| | Com_show_ndb_status | 0| | Com_show_new_master | 0| | Com_show_open_tables | 0| | Com_show_privileges | 0| | Com_show_processlist | 0| | Com_show_slave_hosts | 0| | Com_show_slave_status | 0| | Com_show_status | 2| | Com_show_storage_engines | 0| | Com_show_tables | 0| | Com_show_triggers | 0| | Com_show_variables| 0| | Com_show_warnings | 0| | Com_slave_start | 0| | Com_slave_stop| 0| | Com_stmt_close| 0| | Com_stmt_execute | 0| | Com_stmt_fetch| 0| | Com_stmt_prepare | 0| | Com_stmt_reset| 0| | Com_stmt_send_long_data | 0| | Com_truncate | 0
Complex Query
I will explain my problem in spanish and english. Se deben Presentar los consumos por 24 horas de energia para su ingreso. Si el usuario ha digitado 10 registros, se deben presentar estos mas los otros 14 correspondientes desde la hora 11 hasta la 24 con los consumos en cero. Siempre un conjunto de 24 Registros. La primera parte de la union es clara, pero como calculo los restantes 14 registros en la misma consulta ? I need to present consumptions for 24 hours that correspond to energy consumptions. If user digit 10 registers, I need to adition others 14 registers with zero consumptions that correspond from hour 11 to 24. Always 24 registers. the first part of union is rigth. How i calculate for union the other 14 register in the same Query? First Part. SELECT e.Plant,e.Date,e.Hour,e.Consuption From Energy Where e.Plant=Var_Plant AND e.Date=Var_Date GROUP BY e.Plant,e.Date,e.Hour UNION () ?? Thanks, Elkin Medellin,Colombia -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Merge tables.
Hi, One of the databases I use just switched to using merge tables and now my queries are painfully slow. One table, initially had about 2.5 million records and now with the change this information is spread across about 1600 tables. A simple query, say select count(*) has gone from .04 to about 30 seconds, sometimes even longer. The OS is FreeBSD on a 3.1 GHz machine with a 1GB ram. Mysql is 4.1.15. My my.cnf currently has only one option: open_files_limit=24576 If anyone could provide me with some other options I might try I would appreciate it. Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Merge tables.
Paul, One of the databases I use just switched to using merge tables and now my queries are painfully slow. One table, initially had about 2.5 million records and now with the change this information is spread across about 1600 tables. A simple query, say select count(*) has gone from .04 to about 30 seconds, sometimes even longer. Why on earth would you spread this information across 1600 (!!!) tables? That's 1600 files to maintain instead of 1. Why did you try Merge in the first place? Was there a problem? The OS is FreeBSD on a 3.1 GHz machine with a 1GB ram. Mysql is 4.1.15. My my.cnf currently has only one option: open_files_limit=24576 If anyone could provide me with some other options I might try I would appreciate it. Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Merge tables.
I guess u had 2.5 million records splited into 1600 tables if iam not wrong :) Why you split the tables to 1600 is there any specific count ? Why you moved to merge table reason behind ? Since 2.5 million records to 1600 is a huge count on nos of tables :( instead of one ;) --Praj On Tue, 14 Mar 2006 07:14:52 -0400 Paul Halliday [EMAIL PROTECTED] wrote: Hi, One of the databases I use just switched to using merge tables and now my queries are painfully slow. One table, initially had about 2.5 million records and now with the change this information is spread across about 1600 tables. A simple query, say select count(*) has gone from .04 to about 30 seconds, sometimes even longer. The OS is FreeBSD on a 3.1 GHz machine with a 1GB ram. Mysql is 4.1.15. My my.cnf currently has only one option: open_files_limit=24576 If anyone could provide me with some other options I might try I would appreciate it. Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Merge tables.
Hello Paul, I suggest you reply to the mailinglist :-) ... The developer insists that for scalability issues, this was the answer. It is likely, for example in my deployment, that these tables would see upwards of 10 million records or more. Well, if there are problems with scalability, I guess you could split it up in a few (not 1600) tables and have them avaialble on different physical hard drives... But - why try to fix something that ain't broken (yet)? Were you experiencing problems already? If the application is fast WITHOUT merge tables, why bother? Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com One of the databases I use just switched to using merge tables and now my queries are painfully slow. One table, initially had about 2.5 million records and now with the change this information is spread across about 1600 tables. A simple query, say select count(*) has gone from .04 to about 30 seconds, sometimes even longer. Why on earth would you spread this information across 1600 (!!!) tables? That's 1600 files to maintain instead of 1. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: update using 'set' keyword
Hi Iam not sure about the answer correct me if iam wrong :( Dont use single quotes in count_of_logons .. Try : $sql = UPDATE members SET count_of_logons = count_of_logons + 1 WHERE logon_id = '$logonid' AND logon_pw= '$logonpw' AND email_verified = 'Y'; --Praj On Mon, 13 Mar 2006 17:18:58 -0500 fbsd_user [EMAIL PROTECTED] wrote: Trying to bump the count_of_logons by 1 using this update. Phpmyadmin shows the count staying at zero. I think that this SET count_of_logons = 'count_of_logons + 1' is not coded correctly, but I get no errors so can not tell. Anybody have any ideas? The table def has count_of_logons INT, $sql = UPDATE members SET count_of_logons = 'count_of_logons + 1' WHERE logon_id = '$logonid' AND logon_pw= '$logonpw' AND email_verified = 'Y'; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL mysql-max-4.0.20 - Error 2008 - MySQL client out of Memory
Hi Trying to dump a complete db where 1 of the tables contains about 88 million rows - When the dump runs on the command line the following error is thrown mysqldump: Out of memory (Needed 3543176 bytes) mysqldump: Got error: 2008: MySQL client run out of memory when retrieving data from server Is it possible to give more memory to the client when running on the command line? thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL mysql-max-4.0.20 - Error 2008 - MySQL client out of Memory
Hi Tom, Use -q option on mysqldump ;) -q Won't buffer query, It will dump directly to stdout. --Praj On Tue, 14 Mar 2006 11:56:42 + Tom Brown [EMAIL PROTECTED] wrote: Hi Trying to dump a complete db where 1 of the tables contains about 88 million rows - When the dump runs on the command line the following error is thrown mysqldump: Out of memory (Needed 3543176 bytes) mysqldump: Got error: 2008: MySQL client run out of memory when retrieving data from server Is it possible to give more memory to the client when running on the command line? thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL mysql-max-4.0.20 - Error 2008 - MySQL client out of Memory
Hi Tom, Are you using the --quick option? --quick, -q This option is useful for dumping large tables. It forces mysqldump to retrieve rows for a table from the server a row at a time rather than retrieving the entire row set and buffering it in memory before writing it out. From the manual at page http://dev.mysql.com/doc/refman/4.1/en/mysqldump.html 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: Tom Brown [mailto:[EMAIL PROTECTED] Sent: Tuesday, 14 March 2006 10:27 PM To: mysql@lists.mysql.com Subject: MySQL mysql-max-4.0.20 - Error 2008 - MySQL client out of Memory Hi Trying to dump a complete db where 1 of the tables contains about 88 million rows - When the dump runs on the command line the following error is thrown mysqldump: Out of memory (Needed 3543176 bytes) mysqldump: Got error: 2008: MySQL client run out of memory when retrieving data from server Is it possible to give more memory to the client when running on the command line? thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Merge tables.
Martijn Tonies [EMAIL PROTECTED] wrote on 14/03/2006 11:32:10: Hello Paul, I suggest you reply to the mailinglist :-) ... The developer insists that for scalability issues, this was the answer. It is likely, for example in my deployment, that these tables would see upwards of 10 million records or more. Well, if there are problems with scalability, I guess you could split it up in a few (not 1600) tables and have them avaialble on different physical hard drives... In my opinion, splitting things into merge tables has a *strong* anti-scalability component. Searching a single table with indexes is O(log n), whereas searching MERGE tables is O(n). Therefore, by splitting your table into very many pieces, you sharply reduce your scalability in time while increasing it in space. Presumably, you want to scatter your table across several drives, so that you will not have problems when you fill one drive. But you are never likely to have 1600 drives, so 1600 is a ridiculously large number of tables to split it into. You should probably split it into no more than two or three times the largest number of disks you ever expect to have. And even so, I would rather combine disks in RAID arrays rather than uses separate tables. This can give you RAID protection as well as more disk space. Which to you expect to run out of first, space or time? You seem to have some heavyweight i.e. time intensive queries, which suggests that you will run out of time first. If that is so, the requirement for scalablity says that you should combine, not split, tables. To quote Donald Knuth (derived from Hoare) Premature optimisation is the root of all evil. You should be sure that you are optimising in the right place before you dive in: your problem suggests that you are trying to fix that which is not broken, and breaking other things in the process. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Merge tables.
On 3/14/06, Martijn Tonies [EMAIL PROTECTED] wrote: Hello Paul, I suggest you reply to the mailinglist :-) ... The developer insists that for scalability issues, this was the answer. It is likely, for example in my deployment, that these tables would see upwards of 10 million records or more. Well, if there are problems with scalability, I guess you could split it up in a few (not 1600) tables and have them avaialble on different physical hard drives... As an example: There was a table called event. This table is now broken up like this: event _sensor_date. So for every sensor, and every day, there is now a new table. So if I have 20 sensors, every day I will have 20 new tables. With this in mind, does this design make sense? how will this scale? Is there anything I can do through configuration (I doubt the developer will change the design) to speed things up? or a workaround that I could do on my end to compensate? Thanks. But - why try to fix something that ain't broken (yet)? Were you experiencing problems already? If the application is fast WITHOUT merge tables, why bother? Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com One of the databases I use just switched to using merge tables and now my queries are painfully slow. One table, initially had about 2.5 million records and now with the change this information is spread across about 1600 tables. A simple query, say select count(*) has gone from .04 to about 30 seconds, sometimes even longer. Why on earth would you spread this information across 1600 (!!!) tables? That's 1600 files to maintain instead of 1. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Merge tables.
The developer insists that for scalability issues, this was the answer. It is likely, for example in my deployment, that these tables would see upwards of 10 million records or more. Well, if there are problems with scalability, I guess you could split it up in a few (not 1600) tables and have them avaialble on different physical hard drives... As an example: There was a table called event. This table is now broken up like this: event _sensor_date. So for every sensor, and every day, there is now a new table. So if I have 20 sensors, every day I will have 20 new tables. With this in mind, does this design make sense? how will this scale? According to you, it doesn't :-) Is there anything I can do through configuration (I doubt the developer will change the design) to speed things up? or a workaround that I could do on my end to compensate? What you're doing here is fixing something that isn't broken. Give your database a test with 20 million rows to see how your queries are performing, make sure your queries make sense and that you use the proper indices. Remember, database systems are designed to handle lots of rows. Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Merge tables.
Paul Halliday [EMAIL PROTECTED] wrote on 14/03/2006 12:09:10: As an example: There was a table called event. This table is now broken up like this: event _sensor_date. So for every sensor, and every day, there is now a new table. So if I have 20 sensors, every day I will have 20 new tables. With this in mind, does this design make sense? how will this scale? Is there anything I can do through configuration (I doubt the developer will change the design) to speed things up? or a workaround that I could do on my end to compensate? Could you explain how this is meant to improve scalability? Because to my mind it is probably the best way I can imagine to make the system unscaleable. To me, this design very much does *not* make sense. You have bought, in MySQL, a highly tuned specialist engine for seqrching and sorting stuff in the most efficent manner. And then you have said that you will disable all its optimisation and force it into a linear search. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Merge tables.
[EMAIL PROTECTED] wrote: Paul Halliday [EMAIL PROTECTED] wrote on 14/03/2006 12:09:10: As an example: There was a table called event. This table is now broken up like this: event _sensor_date. So for every sensor, and every day, there is now a new table. So if I have 20 sensors, every day I will have 20 new tables. With this in mind, does this design make sense? how will this scale? Is there anything I can do through configuration (I doubt the developer will change the design) to speed things up? or a workaround that I could do on my end to compensate? Could you explain how this is meant to improve scalability? Because to my mind it is probably the best way I can imagine to make the system unscaleable. To me, this design very much does *not* make sense. You have bought, in MySQL, a highly tuned specialist engine for seqrching and sorting stuff in the most efficent manner. And then you have said that you will disable all its optimisation and force it into a linear search. I can think of a reason for doing this but not to extent described. Is your developer trying to create a situation where it's easy to archive of results earlier than a given day? So you store say 1000 days of data and can quickly archive the oldest day at midnight each day. Assuming this is the case: There's no point splitting further than by day so tables per day/sensor don't make any sense unless your worried about sub second locking (i.e. doing it wrong). You should make the unmerged tables as large as possible without the time to delete having an impact on your application. Having an impact depends on your applications tolerence to locking and the amount of data your adding and removing, you'll need to find it by testing. The table type you use will have a big impact on concurrent access locks. MyiSAM and Innodb are the two main candidates MyISAM is quick but is doesn't allow concurrent access to the table. Innodb will allow concurrent access but still locks rows and can lock the 'head point' during certain inserts. The fact your storing sensor data worries me. How tolerent of lag/locking on insert or retreval is your application? If it's sensitive to more than a seconds lag you need a careful review of your design. If it's hard real-time sack the developer then review the design. Hope this helps Nigel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL mysql-max-4.0.20 - Error 2008 - MySQL client out of Memory
Are you using the --quick option? --quick, -q This option is useful for dumping large tables. It forces mysqldump to retrieve rows for a table from the server a row at a time rather than retrieving the entire row set and buffering it in memory before writing it out. thanks both - that did the trick cheers -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Merge tables.
nigel wood [EMAIL PROTECTED] wrote on 14/03/2006 13:09:08: [EMAIL PROTECTED] wrote: Paul Halliday [EMAIL PROTECTED] wrote on 14/03/2006 12:09:10: As an example: There was a table called event. This table is now broken up like this: event _sensor_date. So for every sensor, and every day, there is now a new table. So if I have 20 sensors, every day I will have 20 new tables. With this in mind, does this design make sense? how will this scale? Is there anything I can do through configuration (I doubt the developer will change the design) to speed things up? or a workaround that I could do on my end to compensate? Could you explain how this is meant to improve scalability? Because to my mind it is probably the best way I can imagine to make the system unscaleable. To me, this design very much does *not* make sense. You have bought, in MySQL, a highly tuned specialist engine for seqrching and sorting stuff in the most efficent manner. And then you have said that you will disable all its optimisation and force it into a linear search. I can think of a reason for doing this but not to extent described. Is your developer trying to create a situation where it's easy to archive of results earlier than a given day? So you store say 1000 days of data and can quickly archive the oldest day at midnight each day. Assuming this is the case: There's no point splitting further than by day so tables per day/sensor don't make any sense unless your worried about sub second locking (i.e. doing it wrong). You should make the unmerged tables as large as possible without the time to delete having an impact on your application. Having an impact depends on your applications tolerence to locking and the amount of data your adding and removing, you'll need to find it by testing. The table type you use will have a big impact on concurrent access locks. MyiSAM and Innodb are the two main candidates MyISAM is quick but is doesn't allow concurrent access to the table. Innodb will allow concurrent access but still locks rows and can lock the 'head point' during certain inserts. The fact your storing sensor data worries me. How tolerent of lag/locking on insert or retreval is your application? If it's sensitive to more than a seconds lag you need a careful review of your design. If it's hard real-time sack the developer then review the design. I take your point to a certain extent. Of course, in the end it comes down to the searches being used. I would make it a rule of thumb that any search which requires more than a 10 tables is a Bad Thing. So if the very large majority of searches are for 1-4 sensors over 1-4 days, this architecture might make sense. But if searches are over 10 sensors or 10 days, this architecture will b become astoundingly inefficient. Generally, I would expect MERGE tables to be used on much larger lumps of time. If you have tables per month, any random period of a month can be checked very efficiently by merging two tables - the start month and the end month. This is the sort of thing that people tend to want to do. The OP of course knows his application, but I think it unusual for people to slice queries that small. And if the queries are of the form upon which days/sensors did event X happen, then splitting the table up is a one way path to doom. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql on Windows
Is anyone running mySQL on Windows 2003 server platform. Are there any known problems ? Cheers Neil -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysql on Windows
[snip]
Is anyone running mySQL on Windows 2003 server platform. Are there any
known problems ?
{/snip]
The operating system? Yuck yuck! :)
It ran fine for me on 2k3 before I cam back to a *nix shop
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
missing mysqld.sock
Is there a way to recreate the mysqld.sock file, it has gone missing from :/var/run/mysqld . Now MySQL will not run. Thanks Jon L. Miller, ASE, CNS, CLS, MCNE, CCNA Director/Sr Systems Consultant MMT Networks Pty Ltd http://www.mmtnetworks.com.au Resellers for: Novell Gold Partner, Cisco Partner, Peopletelecom, Westnet, Sophos Anti-Virus, CA Products I don't know the key to success, but the key to failure is trying to please everybody. -Bill Cosby !DOCTYPE HTML PUBLIC -//W3C//DTD HTML 4.0 Transitional//EN HTMLHEAD META http-equiv=Content-Type content=text/html; charset=us-ascii META content=MSHTML 6.00.2900.2802 name=GENERATOR/HEAD BODY style=MARGIN-TOP: 2px; FONT: 10pt Arial; MARGIN-LEFT: 2px DIVIs there a way to recreate the mysqld.sock file, it has gone missing from :/var/run/mysqld .nbsp; Now MySQL will not run./DIV DIVnbsp;/DIV DIVnbsp;/DIV DIVThanks/DIV DIVnbsp;/DIV DIVJon L. Miller,nbsp; ASE, CNS, CLS, MCNE, CCNABRDirector/Sr Systems ConsultantBRMMT Networks Pty LtdBRA href=http://www.mmtnetworks.com.au;http://www.mmtnetworks.com.au/ABRResellers for: Novell Gold Partner, Cisco Partner, Peopletelecom, Westnet, Sophos Anti-Virus, CA Products/DIV DIVnbsp;/DIV DIVI don't know the key to success, but the key to failureBRnbsp;is trying to please everybody. -Bill Cosby/DIV DIVnbsp;/DIV DIVnbsp;/DIV/BODY/HTML -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql on Windows
Hi Neil, Neil Tompkins wrote: Is anyone running mySQL on Windows 2003 server platform. Are there any known problems ? There is a known bug for Windows 2003 - bug #12071: http://bugs.mysql.com/bug.php?id=12071 You should use 4.0.19 or 5.0.19. Best regards Mark -- Mark Leith, Support Engineer MySQL AB, Worcester, England, www.mysql.com Are you MySQL certified? www.mysql.com/certification -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Complex Query
ElkinFernando Ortiz [EMAIL PROTECTED] wrote on 03/10/2006 12:26:57 AM: I will explain my problem in spanish and english. Se deben Presentar los consumos por 24 horas de energia para su ingreso. Si el usuario ha digitado 10 registros, se deben presentar estos mas los otros 14 correspondientes desde la hora 11 hasta la 24 con los consumos en cero. Siempre un conjunto de 24 Registros. La primera parte de la union es clara, pero como calculo los restantes 14 registros en la misma consulta ? I need to present consumptions for 24 hours that correspond to energy consumptions. If user digit 10 registers, I need to adition others 14 registers with zero consumptions that correspond from hour 11 to 24. Always 24 registers. the first part of union is rigth. How i calculate for union the other 14 register in the same Query? First Part. SELECT e.Plant,e.Date,e.Hour,e.Consuption From Energy Where e.Plant=Var_Plant AND e.Date=Var_Date GROUP BY e.Plant,e.Date,e.Hour UNION () ?? Thanks, Elkin Medellin,Colombia Please forgive my Spanish, it's a little rusty and I don't know how to type the accented characters. The server cannot give you data that it does not have. The easiest solution is to create a table with ALL of the hour values you want to see in a report and JOIN your original query into it to fill in the missing information. El servidor no puede darte los datos que no tiene. La solucion mas facil es crear una tabla con todos los valores de la hora que te deseas ver en un informe y JOIN su pregunta original en ella a complete la informacion que falta. CREATE TABLE report_hours ( horas tinyint ); INSERT report_hora VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11), (12), (13), (14), (15), (16), (17), (18), (19), (20), (21), (22), (23); SELECT e.Plant,e.Date,e.Hour,e.Consuption From report_horas h LEFT JOIN Energy e on h.horas = e.hour Where e.Plant=Var_Plant AND e.Date=Var_Date GROUP BY h.Hour,e.Plant,e.Date, Or, you could JOIN the plant information to the report_hora table to fill in missing information as part of a UNION query. O, tu puedes JOIN la información de la planta a la tabla del report_hora para complete la informacion que falta como parte de una pregunta UNION. (SELECT e.Plant,e.Date,e.Hour,e.Consuption From Energy Where e.Plant=Var_Plant AND e.Date=Var_Date GROUP BY e.Plant,e.Date,e.Hour) UNION (SELECT var_plant, var_date, h.horas,0 FROM report_horas h LEFT JOIN Energy e ON h.horas = e.hour AND e.plant = Var_Plant AND e.date = Var_Date WHERE e.plant is null) ORDER BY Hour; Shawn Green Database Administrator Unimin Corporation - Spruce Pine Again, HUGE apologies for my poor Spanish.
Re: missing mysqld.sock
did u try restarting mysqld Kishore Jalleda On 3/14/06, Jon Miller [EMAIL PROTECTED] wrote: Is there a way to recreate the mysqld.sock file, it has gone missing from :/var/run/mysqld . Now MySQL will not run. Thanks Jon L. Miller, ASE, CNS, CLS, MCNE, CCNA Director/Sr Systems Consultant MMT Networks Pty Ltd http://www.mmtnetworks.com.au Resellers for: Novell Gold Partner, Cisco Partner, Peopletelecom, Westnet, Sophos Anti-Virus, CA Products I don't know the key to success, but the key to failure is trying to please everybody. -Bill Cosby -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
migrating 4.0 to 4.1 character set problems
Hi, I am having character set problems while trying to migrate my data from a server running 4.0.25-standard to a server running 4.1.16. I believe that the orginal database was using the latin1 character set (not sure, is there any way to tell? show full column doesn't seem to be supported in 4.0.x) and the default character set for the 4.1 server is latin1 as well. However when I try to import my data from a mysqldump from the older server all accents are turned into question marks. I've searched the forums and mailing lists extensively for clues as to how to resolve this issue. The most helpful post I found was here: http://lists.mysql.com/mysql/186657 However, even following the instructions on this post I ran into problems. Rather than turniing accents into question marks, any data with accent was truncated at the first instance of such a character (e.g. López becomes L). So that is not working either. Incidentally, I would be happy to convert the data to utf8 while upgrading to 4.1 but mostly I just want to get rid of the question mark characters. Any ideas? Thanks, Sean -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Complex Query
ElkinFernando Ortiz wrote: How i calculate for union the other 14 register in the same Query? ... SELECT e.Plant,e.Date,e.Hour,e.Consuption From Energy Where e.Plant=Var_Plant AND e.Date=Var_Date GROUP BY e.Plant,e.Date,e.H By having an hours table (hour tinyint) which you join to your energy table: SELECT h.hour, e.Plant,e.Date,e.Hour,SUM(e.Consuption) FROM hours h LEFT JOIN energy e USING (hour) Where e.Plant=Var_Plant AND e.Date=Var_Date GROUP BY e.Plant,e.Date,e.H PB - I will explain my problem in spanish and english. Se deben Presentar los consumos por 24 horas de energia para su ingreso. Si el usuario ha digitado 10 registros, se deben presentar estos mas los otros 14 correspondientes desde la hora 11 hasta la 24 con los consumos en cero. Siempre un conjunto de 24 Registros. La primera parte de la union es clara, pero como calculo los restantes 14 registros en la misma consulta ? I need to present consumptions for 24 hours that correspond to energy consumptions. If user digit 10 registers, I need to adition others 14 registers with zero consumptions that correspond from hour 11 to 24. Always 24 registers. the first part of union is rigth. How i calculate for union the other 14 register in the same Query? First Part. SELECT e.Plant,e.Date,e.Hour,e.Consuption From Energy Where e.Plant=Var_Plant AND e.Date=Var_Date GROUP BY e.Plant,e.Date,e.Hour UNION () ?? Thanks, Elkin Medellin,Colombia -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Permissions block database creation
I'm a total newbie working through the tutorial in DuBois's _MySQL_. I've got MySQL running on my PowerBook. In Terminal, I can use the mysql client to get responses to things like SELECT NOW(). But the command CREATE DATABASE sampdb; results in the following error. ERROR 1044 (42000): Access denied for user ''@'localhost' to database 'sampdb' mysql I am unable to determine its location, which causes me to suspect that it is in the portion of the system not accessible from the Finder. Can anyone offer a suggestion? Mac OS 10.4.5 Thanks, dp -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Permissions block database creation
You need to set GRANTs to create on the host. http://dev.mysql.com/doc/refman/5.0/en/grant.html -Original Message- From: Doug Pinkerton [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 14, 2006 1:26 PM To: mysql@lists.mysql.com Subject: Permissions block database creation I'm a total newbie working through the tutorial in DuBois's _MySQL_. I've got MySQL running on my PowerBook. In Terminal, I can use the mysql client to get responses to things like SELECT NOW(). But the command CREATE DATABASE sampdb; results in the following error. ERROR 1044 (42000): Access denied for user ''@'localhost' to database 'sampdb' mysql I am unable to determine its location, which causes me to suspect that it is in the portion of the system not accessible from the Finder. Can anyone offer a suggestion? Mac OS 10.4.5 Thanks, dp -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Permissions block database creation
User ''@'localhost' just hasn't enough privileges to create databases. http://dev.mysql.com/doc/refman/5.0/en/privilege-system.html Doug Pinkerton wrote: I'm a total newbie working through the tutorial in DuBois's _MySQL_. I've got MySQL running on my PowerBook. In Terminal, I can use the mysql client to get responses to things like SELECT NOW(). But the command CREATE DATABASE sampdb; results in the following error. ERROR 1044 (42000): Access denied for user ''@'localhost' to database 'sampdb' mysql I am unable to determine its location, which causes me to suspect that it is in the portion of the system not accessible from the Finder. Can anyone offer a suggestion? Mac OS 10.4.5 Thanks, dp -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: E/R Tool
So did I Jim, on SuSE 9.2 pro. So I downloaded the windows versions to my laptop instead. Keith In theory, theory and practice are the same; In practice they are not. On Tue, 14 Mar 2006, Jim Douglas wrote: To: [EMAIL PROTECTED] From: Jim Douglas [EMAIL PROTECTED] Subject: Re: E/R Tool From: [EMAIL PROTECTED] To: mysql@lists.mysql.com Subject: Re: E/R Tool Date: Mon, 13 Mar 2006 22:40:34 + (GMT) OK TY Peter. I have downloaded both DBDesigner and MySQL Workbench. Looking forward to workbench reaching GA status. Regards Keith In theory, theory and practice are the same; In practice they are not. On Mon, 13 Mar 2006, Peter Brawley wrote: To: [EMAIL PROTECTED] From: Peter Brawley [EMAIL PROTECTED] Subject: Re: E/R Tool [EMAIL PROTECTED] wrote: Excellent DB design tool Peter. Thanks for posting the information. http://www.fabforce.net/dbdesigner4/ It has become MySQL Workbench (http://forums.mysql.com/read.php?10,73820,73820#msg-73820), but it isn't production-ready yet. PB -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 268.2.1/279 - Release Date: 3/10/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Great looking tool I tried to install and got a lot of failed dependencies on Fedora Core 4. Will a version for FC 4/5 be available anytime soon? Jim -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: missing mysqld.sock
Hi, Jon. mysqld.sock is a unix socket. mysqld creates it while starting and removes it when you stop the server. Jon Miller wrote: Is there a way to recreate the mysqld.sock file, it has gone missing from :/var/run/mysqld . Now MySQL will not run. Thanks Jon L. Miller, ASE, CNS, CLS, MCNE, CCNA Director/Sr Systems Consultant MMT Networks Pty Ltd http://www.mmtnetworks.com.au Resellers for: Novell Gold Partner, Cisco Partner, Peopletelecom, Westnet, Sophos Anti-Virus, CA Products I don't know the key to success, but the key to failure is trying to please everybody. -Bill Cosby -- Regards, Eugene Kosov -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Accountability with MySQL
I am designing a simple accountability system so all the partners can have direct access to it by intranet. I was designing the data model, and came up with this: CREATE TABLE `moviments` ( `moviment_id` int(20) NOT NULL auto_increment, `moviment_date` date NOT NULL default '-00-00', `moviment_description` char(200) NOT NULL default '', `moviment_assignor` char(80) NOT NULL default '', `moviment_drawee` char(80) NOT NULL default '', `moviment_amount` int(20) NOT NULL default '0', PRIMARY KEY (`moviment_id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 But I am a little bit stuck with this: Should I use a column to mark if the moviment has been executed? About income and outcome, should I use a column called moviment_type or just put a negative value when is an outcome for exampe? Does anyone ever made something like that, any other idea that could improve my little system? Best regards, Bruno B B Magalhães -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: timestamp
Further, your reporting will skew the data -- let's say once a day you want to know the last time that row was read. Well, the first day, you'll get accurate numbers. The second day, though, you'll end up seeing that each row was read at latest the day before, because you read it searching for the date. That's an UPDATE you really want to build into your APPLICATION layer. It's a BAD idea to store business logic/rules in the data layer. -Sheeri On 3/13/06, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: fbsd_user [EMAIL PROTECTED] wrote on 03/13/2006 01:10:17 PM: In my mysql 4.4 table definition the default attributes are (ON UPDATE CURRENT_TIMESTAMP). Reading the manual my understanding is this is saying that the auto timestamp update feature is active. The manual does not say what the trigger is to make the timestamp in the row to be bumped to the current timestamp. I have noticed that the timestamp field is only bumped when I update a field in the row. I would like it to be auto bumped every time the row is selected/read. Is there a way to do this without creating a timestamp from date in my php code and then updating the row instead of reading the row? The final goal is to auto bump the timestamp every time the user logs on. It sounds like there is a logon process that the user must perform. What's the issue you have about updating your timestamp from within that process? That's where it sounds most logical to me to add the code to bump your timestamp value. From a design point of view, it would seriously slow down the entire server if it had to check for something to do on EVERY read from ANY table. In order to do what you want the database to do, that facility would need to exist so that the engine could bump the timestamp automatically. It is generally much better to NOT write SELECT statements to a log or to do anything else that would slow them down. Checking every row that ever got selected to see if any column in it is an auto-update-on-select-timestamp column would do that in a major way. It also breaks all kinds of SQL rules to make a SELECT clause into something that modifies data. That would lead to all sorts of data chaos and I want no part of it. I am sorry, but if you want the timestamp changed you are going to have to initiate that change by an UPDATE statement. 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: Accountability with MySQL
Hello Bruno, I am designing a simple accountability system so all the partners can have direct access to it by intranet. I was designing the data model, and came up with this: CREATE TABLE `moviments` ( `moviment_id` int(20) NOT NULL auto_increment, `moviment_date` date NOT NULL default '-00-00', What a weird default date - it's rather invalid, isn't it? `moviment_description` char(200) NOT NULL default '', `moviment_assignor` char(80) NOT NULL default '', `moviment_drawee` char(80) NOT NULL default '', `moviment_amount` int(20) NOT NULL default '0', PRIMARY KEY (`moviment_id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 btw, given all the default clauses, did you know you can actually put a complete empty moviment into this table? Doesn't say a thing. You might want to change that a bit :-) But I am a little bit stuck with this: Should I use a column to mark if the moviment has been executed? About income and outcome, should I use a column called moviment_type or just put a negative value when is an outcome for exampe? Calculating summaries etc is, I think, much easier if you just do a minus. Does anyone ever made something like that, any other idea that could improve my little system? Well, it depends on what you're trying to do and how you want to query data. You could also decide to create two tables - one for positives and one for negatives. Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Accountability with MySQL
Hi Martijn, well about the date default value being invalid, well it´s working here in my system (MySQL 4.1.16, Mac OS X 10.4.5), and all my systens work with this... About the business logic I was thinking about showing the current month by default, and if the user wants he can select diferent days, months and year, or beetween dates.. And everything will be shown in a simple list, with some graphics like bills to pay in the next week, income to recieve in next week, and simple stats of that kind.. To split in two tables (income and outcome) seams a good idea... Best Regards, Bruno B B Magalhães Hello Bruno, I am designing a simple accountability system so all the partners can have direct access to it by intranet. I was designing the data model, and came up with this: CREATE TABLE `moviments` ( `moviment_id` int(20) NOT NULL auto_increment, `moviment_date` date NOT NULL default '-00-00', What a weird default date - it's rather invalid, isn't it? `moviment_description` char(200) NOT NULL default '', `moviment_assignor` char(80) NOT NULL default '', `moviment_drawee` char(80) NOT NULL default '', `moviment_amount` int(20) NOT NULL default '0', PRIMARY KEY (`moviment_id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 btw, given all the default clauses, did you know you can actually put a complete empty moviment into this table? Doesn't say a thing. You might want to change that a bit :-) But I am a little bit stuck with this: Should I use a column to mark if the moviment has been executed? About income and outcome, should I use a column called moviment_type or just put a negative value when is an outcome for exampe? Calculating summaries etc is, I think, much easier if you just do a minus. Does anyone ever made something like that, any other idea that could improve my little system? Well, it depends on what you're trying to do and how you want to query data. You could also decide to create two tables - one for positives and one for negatives. Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Accountability with MySQL
Bruno B B Magalháes [EMAIL PROTECTED] wrote on 03/14/2006 12:41:35 PM: I am designing a simple accountability system so all the partners can have direct access to it by intranet. I was designing the data model, and came up with this: CREATE TABLE `moviments` ( `moviment_id` int(20) NOT NULL auto_increment, `moviment_date` date NOT NULL default '-00-00', `moviment_description` char(200) NOT NULL default '', `moviment_assignor` char(80) NOT NULL default '', `moviment_drawee` char(80) NOT NULL default '', `moviment_amount` int(20) NOT NULL default '0', PRIMARY KEY (`moviment_id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 But I am a little bit stuck with this: Should I use a column to mark if the moviment has been executed? About income and outcome, should I use a column called moviment_type or just put a negative value when is an outcome for exampe? Does anyone ever made something like that, any other idea that could improve my little system? Best regards, Bruno B B Magalhães It all depends on what accounting style you want to use whether you want single-entry or double-entry bookkeeping. There are pros and cons to both styles. The best answer comes from you: If you were an accountant, how would you write this out on paper? Once you figure out what you paper books should look like you should be able to manage your electronic data almost identically to how you manage your paper data. If you are used to seeing a list of debits and credits where each line represents one account (end) within a single transaction (these lines always appear in pairs) then you want to use one type of table. If you want to see each transaction on a single line that also lists both ends of the transaction, you use a table similar to the one you made. If you have a flag for transaction type (debit,credit, etc) then your amounts should almost always be listed as positive values. It's the position of the account (assignor or drawee) and the transaction type that determines the sign (+/-) to apply to the value as you apply the value to an account. Should you have a flag for the status movement complete ? I would say yes but instead of a simple checkbox, you could store a date value. That gives you two pieces of information a) if the date is null then the movement is not complete. b) if the date is NOT null then the movement is complete and you know when it finished. As I said before, how you set up your accounting tables really depends on the style of accounting you want to do. Check with a bookkeeper or an accountant for more details. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Accountability with MySQL
Hello Bruno, well about the date default value being invalid, well it´s working here in my system (MySQL 4.1.16, Mac OS X 10.4.5), and all my systens work with this... Yes, it's a valid value in MySQL, but it's an invalid date, that's what I'm trying to say. Why have an invalid date as the default? Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Accountability with MySQL
Martijn Tonies [EMAIL PROTECTED] wrote on 03/14/2006 01:16:11 PM: Hello Bruno, well about the date default value being invalid, well it´s working here in my system (MySQL 4.1.16, Mac OS X 10.4.5), and all my systens work with this... Yes, it's a valid value in MySQL, but it's an invalid date, that's what I'm trying to say. Why have an invalid date as the default? Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com Martin, If you used MySQL just a bit more often for day-to-day operations (yes, we all know that you build useful tools that interoperate with several RDBM systems), you would know that you cannot assign anything but constant values as default values for any type of column in MySQL (timestamp being the singular, partial exception). That means you cannot design a column to have more meaningful default date by capturing the current time because functions are not permitted as default values (yet). With that in mind, a design default of -00-00 is as good as any other single, randomly chosen default date, don't you think? That's also the default default date if you define a non-null date column and do not specify your own default in the definition. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Accountability with MySQL
Should you have a flag for the status movement complete ? I would say yes but instead of a simple checkbox, you could store a date value. That gives you two pieces of information a) if the date is null then the movement is not complete. b) if the date is NOT null then the movement is complete and you know when it finished. There, Shawn and I disagree :-) In case of a very simple database, one could use NULL as a flag or signal. But in general, I would avoid storing NULLs. Basics of database design: store what you know. Given that NULLs basically means the absence of a value (eg: unknown), you shouldn't be storing NULLs. Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Accountability with MySQL
- Original Message - From: Bruno B B Magalháes [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, March 14, 2006 12:41 PM Subject: Accountability with MySQL I am designing a simple accountability system so all the partners can have direct access to it by intranet. I was designing the data model, and came up with this: CREATE TABLE `moviments` ( `moviment_id` int(20) NOT NULL auto_increment, `moviment_date` date NOT NULL default '-00-00', `moviment_description` char(200) NOT NULL default '', `moviment_assignor` char(80) NOT NULL default '', `moviment_drawee` char(80) NOT NULL default '', `moviment_amount` int(20) NOT NULL default '0', PRIMARY KEY (`moviment_id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 One small observation: if your column names are meant to be in English - as suggested by your use of amount, drawee, etc. I think you should also use an English word where you are currently using moviment. Moviment is not an English word. There _is_ an English word movement that might be what you want but I suspect that transaction would be the term most English-speakers would use in the table you describe. Your email address indicates that you are from Brazil so I'll assume you are a Portuguese-speaker, not a native English-speaker. This suggestion is only meant to be a friendly suggestion, not an insult to your English which is very very good. :-) But I am a little bit stuck with this: Should I use a column to mark if the moviment has been executed? That depends. You could decide to only add the row to the table if it has been executed; then, you would know implicitly that every entry has been executed. On the other hand, if you want to show transactions that have not yet been executed, a column that indicates whether the transaction has been executed would probably be a good idea. It might be even better to display details about the transaction that refer to its successful execution rather than just showing a yes/no flag. Perhaps you could store the timestamp that shows when the transaction was completed and maybe the identity of the person or program that completed the transaction or even a copy of the document generated by the transaction (or a link to this document). For instance, if a receipt was issued for this transaction, you might want to store an image of the receipt (or a link to the image) in the table. About income and outcome, should I use a column called moviment_type or just put a negative value when is an outcome for exampe? Either approach should work. It would probably be easier to simply store the sign of the transaction with the amount. Does anyone ever made something like that, any other idea that could improve my little system? I think the question you need to ask yourself is What do I want to be able to find out about a transaction after it has taken place?. If you decide you want to know what currency was involved in the transaction, you need to record that in your table. If you decide you want to know the serial numbers of the banknotes used in the transaction, you need to record that. And so on. It might be wise to talk to someone in the bank and ask them what things they need to find out while a transaction is taking place and afterwards. You may want to try asking your users (or their managers) what sorts of things people ask for but can't get from the old system; these are prime candidates for things that you could add to your new system. I'm a little surprised that your amount column is an integer. Does the Brazilian currency not have a fractional component? Here in Canada, monetary amounts are always decimal numbers, like $123.45, meaning one hundred and twenty three dollars and forty five cents. Or is your currency like Japanese yen, which have no fractional part? -- Rhino -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.2.2/280 - Release Date: 13/03/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL JDBC Exception: No operations allowed after connection closed
I use Tomcat 5.0.28 on both Fedora and FreeBSD, MySQL 4.1.14, J/Connector JDBC driver for MySQL 3.1.11 and JOTM 2.0.10. From time to time I get the MySQL JDBC driver exception No operations allowed after connection closed, after which DB calls from my application keep failing. The stack trace of the exception is as follows: java.sql.SQLException: No operations allowed after connection closed. at com.mysql.jdbc.Connection.checkClosed(Connection.java:1831) at com.mysql.jdbc.Connection.setAutoCommit(Connection.java:4866) at org.enhydra.jdbc.core.CoreConnection.setAutoCommit(CoreConnection.java:23 4) at org.enhydra.jdbc.standard.StandardXAConnectionHandle.setAutoCommit(Standa rdXAConnectionHandle.java:123) at org.enhydra.jdbc.standard.StandardXAConnectionHandle.prepareStatement(Sta ndardXAConnectionHandle.java:220) at org.enhydra.jdbc.standard.StandardXAConnectionHandle.prepareStatement(Sta ndardXAConnectionHandle.java:197) at com.kc.ppt.dao.mysql.UserDeviceDAOImpl.updatePhoneNumber(UserDeviceDAOImp l.java:499) at com.kc.ppt.bo.UserDeviceBO.updateUserDeviceNumber(UserDeviceBO.java:187) at com.kc.ppt.bo.UserBO.addUserProfile(UserBO.java:188) at com.kc.ppt.bapi.BAPIImpl.addUserProfile(BAPIImpl.java:97) at com.kc.ppt.servlet.CreateUserProfileServlet.processRequest(CreateUserProf ileServlet.java:262) at com.kc.ppt.servlet.CreateUserProfileServlet.doPost(CreateUserProfileServl et.java:123) at javax.servlet.http.HttpServlet.service(HttpServlet.java:709) at javax.servlet.http.HttpServlet.service(HttpServlet.java:802) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(Applicat ionFilterChain.java:252) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilte rChain.java:173) at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve .java:214) at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveCon text.java:104) at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:52 0) at org.apache.catalina.core.StandardContextValve.invokeInternal(StandardCont extValve.java:198) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve .java:152) at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveCon text.java:104) at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:52 0) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java: 137) at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveCon text.java:104) at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java: 118) at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveCon text.java:102) at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:535) at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveCon text.java:102) at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:52 0) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.j ava:109) at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveCon text.java:104) at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:52 0) at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:929) When defining datasources in the application's context.xml I tried both the new recommended configuration for MySQL J/Connector JDBC driver (shown for Resource name=jdbc/primaryDS) and the autoreconnect=true approach (shown for Resource name=jdbc/primaryDS). Please note that I did not mix those two approaches, I just used different datasources to indicate configurations that I used. Does anybody know if the new MySQL J/Connector configuration works with Tomcat? Here's my context.xml file: Context path=/myapp docBase=myapp debug=5 reloadable=true crossContext=true Logger className=org.apache.catalina.logger.FileLogger prefix=localhost_myapp_log. suffix=.txt timestamp=true/ !-- Primary AV data source -- Resource name=jdbc/primaryDS auth=Container type=javax.sql.DataSource/ ResourceParams name=jdbc/primaryDS parameter namefactory/name valueorg.objectweb.jndi.DataSourceFactory/value /parameter !-- Maximum number of dB connections in pool. Configure mysqld max_connections large enough to handle all DB connections. Set to 0 for no limit. -- parameter namemaxActive/name
Re: Accountability with MySQL
Hello Shawn, well about the date default value being invalid, well it´s working here in my system (MySQL 4.1.16, Mac OS X 10.4.5), and all my systens work with this... Yes, it's a valid value in MySQL, but it's an invalid date, that's what I'm trying to say. Why have an invalid date as the default? If you used MySQL just a bit more often for day-to-day operations (yes, we all know that you build useful tools that interoperate with several RDBM systems), you would know that you cannot assign anything but constant values as default values for any type of column in MySQL (timestamp being the singular, partial exception). I know that. That means you cannot design a column to have more meaningful default date by capturing the current time because functions are not permitted as default values (yet). Same here. With that in mind, a design default of -00-00 is as good as any other single, randomly chosen default date, don't you think? That's also the default default date if you define a non-null date column and do not specify your own default in the definition. I'd rather say that having no default (and a NOT NULL) would be the better thing to do. What's the use of not null and a default? Isn't that like saying: well, you have to fill in each column, but if you don't I'll assign something empty-ish. Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Accountability with MySQL
- Original Message - From: Martijn Tonies [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, March 14, 2006 1:16 PM Subject: Re: Accountability with MySQL Hello Bruno, well about the date default value being invalid, well it´s working here in my system (MySQL 4.1.16, Mac OS X 10.4.5), and all my systens work with this... Yes, it's a valid value in MySQL, but it's an invalid date, that's what I'm trying to say. Why have an invalid date as the default? I think there may be a little bit of language confusion here. Bruno, I think Martijn is suggesting that you use a date that can actually exist as the default transaction date; '000-00-00' is accepted by MySQL as a date that can be stored in a table but it is not a date that ever really existed. There was never a Year 0 in the history of the world. I think Martijn is right; it is probably a better choice to use a real date in your table. The obvious choice would be to store the current date in that column. For example, if you add a row to the table, the new row should normally contain the date that the transaction was executed; that might be yesterday or a week ago or maybe even longer. But if no transaction date is known, it would probably be reasonable to store today's date. But sometimes that _wouldn't_ be reasonable. You should probably talk to the people in the bank about this and ask if it is ever possible that the transaction date would be unknown; if the transaction data _can_ be unknown, you should ask what date they usually store in their existing system in that case. Then your new table should probably do the exact same thing as the old system does, assuming everyone agrees that this is the right thing to do. (The users may say that it is the wrong thing to do and suggest a better value to choose for the transaction date.) But if the transaction date can never be unknown, it doesn't make much difference what you choose as the default date since it will never be used. -- Rhino -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.2.2/280 - Release Date: 13/03/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql on Windows
Apart from this are there any other known issues. When trialing mySQL on my XP machine, I noticed all the tables were created in lower case ? Is this normal ? Cheers Neil From: Mark Leith [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Neil Tompkins [EMAIL PROTECTED] CC: mysql@lists.mysql.com Subject: Re: mysql on Windows Date: Tue, 14 Mar 2006 15:51:48 + Hi Neil, Neil Tompkins wrote: Is anyone running mySQL on Windows 2003 server platform. Are there any known problems ? There is a known bug for Windows 2003 - bug #12071: http://bugs.mysql.com/bug.php?id=12071 You should use 4.0.19 or 5.0.19. Best regards Mark -- Mark Leith, Support Engineer MySQL AB, Worcester, England, www.mysql.com Are you MySQL certified? www.mysql.com/certification -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Accountability with MySQL
- Original Message - From: Martijn Tonies [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, March 14, 2006 1:28 PM Subject: Re: Accountability with MySQL Should you have a flag for the status movement complete ? I would say yes but instead of a simple checkbox, you could store a date value. That gives you two pieces of information a) if the date is null then the movement is not complete. b) if the date is NOT null then the movement is complete and you know when it finished. There, Shawn and I disagree :-) In case of a very simple database, one could use NULL as a flag or signal. But in general, I would avoid storing NULLs. Basics of database design: store what you know. Given that NULLs basically means the absence of a value (eg: unknown), you shouldn't be storing NULLs. Nonsense!! That's simply wrong. A null means unknown or not applicable and is a perfectly valid value to use in many, many situations. Two classic examples: 1. You have a table containing test scores. Some students get every question on a given test wrong. Some students don't write the test at all, perhaps because they were sick. In this scenario, I think the correct thing to store is 0 for the students who got every question wrong and NULL for the students who didn't write the test. 2. You have a table containing employee records. One of the columns is termination date. What value do you store for a new employee's termination date? Well, if they are a contractor on a fixed length contract, you could calculate the date the contract ends; fair enough. If they are a permanent employee and your area has mandatory retirement, you could calculate the date they turn 65 (or whatever) and use that. But what if they are a permanent employee and you don't have mandatory retirement? I would store a NULL to mean I don't know right now. Then, if and when they gave notice that they were leaving, I would change the termination date from NULL to their last day of work. -- Rhino -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.2.2/280 - Release Date: 13/03/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 'Into outfile' doesn't include the column names. How can it be done?
On 3/10/06, Jake Peavy [EMAIL PROTECTED] wrote: On 3/7/06, C.R.Vegelin [EMAIL PROTECTED] wrote: Hi Ariel, Maybe this example helps you to create CSV output from MySQL. The first SELECT generates the headerline; the second the data. ( SELECT 'FieldA','FieldB','FieldC', ... ) UNION ( SELECT `FieldA`, `FieldB`, `FieldC`, ... INTO OUTFILE 'D:/MySQL Datafiles/myFile.csv' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n' FROM ... ... GROUP BY `FieldA`, `FieldB`, `FieldC`, ... ); Don't forget the braces ( and ). HTH, Cor So, to all the SQL wizards out there... How would one perform this same operation, but using the INFORMATION_SCHEMA virtual db to provide the column headings in the first row rather having to hand type them as shown above? Bonus marks for beginning the line with a # My attempt stalled as soon as I got one column of data in the result set with a row for each column of the target table. SELECT COLUMNS FROM INFORMATION_SCHEMA WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 'testtable' Ideas? TIA, -jp Nobody has any ideas at all? (sheds solitary tear) -jp
Re: mysql on Windows
Neil Tompkins [EMAIL PROTECTED] wrote on 03/14/2006 01:42:32 PM: Apart from this are there any other known issues. When trialing mySQL on my XP machine, I noticed all the tables were created in lower case ? Is this normal ? Cheers Neil From: Mark Leith [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Neil Tompkins [EMAIL PROTECTED] CC: mysql@lists.mysql.com Subject: Re: mysql on Windows Date: Tue, 14 Mar 2006 15:51:48 + Hi Neil, Neil Tompkins wrote: Is anyone running mySQL on Windows 2003 server platform. Are there any known problems ? There is a known bug for Windows 2003 - bug #12071: http://bugs.mysql.com/bug.php?id=12071 You should use 4.0.19 or 5.0.19. Best regards Mark -- Mark Leith, Support Engineer MySQL AB, Worcester, England, www.mysql.com Are you MySQL certified? www.mysql.com/certification Yes, all lowercase is normal. http://dev.mysql.com/doc/refman/4.1/en/name-case-sensitivity.html I am running several servers on Win2k and Win2K3 and so far so good. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: 'Into outfile' doesn't include the column names. How can it be done?
Jake Peavy [EMAIL PROTECTED] wrote on 03/14/2006 01:52:28 PM: On 3/10/06, Jake Peavy [EMAIL PROTECTED] wrote: On 3/7/06, C.R.Vegelin [EMAIL PROTECTED] wrote: Hi Ariel, Maybe this example helps you to create CSV output from MySQL. The first SELECT generates the headerline; the second the data. ( SELECT 'FieldA','FieldB','FieldC', ... ) UNION ( SELECT `FieldA`, `FieldB`, `FieldC`, ... INTO OUTFILE 'D:/MySQL Datafiles/myFile.csv' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n' FROM ... ... GROUP BY `FieldA`, `FieldB`, `FieldC`, ... ); Don't forget the braces ( and ). HTH, Cor So, to all the SQL wizards out there... How would one perform this same operation, but using the INFORMATION_SCHEMA virtual db to provide the column headings in the first row rather having to hand type them as shown above? Bonus marks for beginning the line with a # My attempt stalled as soon as I got one column of data in the result set with a row for each column of the target table. SELECT COLUMNS FROM INFORMATION_SCHEMA WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 'testtable' Ideas? TIA, -jp Nobody has any ideas at all? (sheds solitary tear) -jp Nope, no suggestions. You are mixing purposes (not to mention data types) when you add column names to the output of INTO OUTFILE. Have you looked at using the CSV storage engine or mysqldump as alternatives? Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Accountability with MySQL
I am designing a simple accountability system so all the partners can have direct access to it by intranet. I was designing the data model, and came up with this: CREATE TABLE `moviments` ( `moviment_id` int(20) NOT NULL auto_increment, `moviment_date` date NOT NULL default '-00-00', `moviment_description` char(200) NOT NULL default '', `moviment_assignor` char(80) NOT NULL default '', `moviment_drawee` char(80) NOT NULL default '', `moviment_amount` int(20) NOT NULL default '0', PRIMARY KEY (`moviment_id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 One small observation: if your column names are meant to be in English - as suggested by your use of amount, drawee, etc. I think you should also use an English word where you are currently using moviment. Moviment is not an English word. There _is_ an English word movement that might be what you want but I suspect that transaction would be the term most English-speakers would use in the table you describe. Your email address indicates that you are from Brazil so I'll assume you are a Portuguese-speaker, not a native English-speaker. This suggestion is only meant to be a friendly suggestion, not an insult to your English which is very very good. :-) Ups, that´s right, I misspelt movement, you know beeing a partner and also being the financial director, technology director and CEO some times can be exhaustive. Well, at least we are growing.. (the opposite of my weekends, that seams to be getting smaller and smaller). But thanks, I also agree transaction is more suitable. But I am a little bit stuck with this: Should I use a column to mark if the moviment has been executed? That depends. You could decide to only add the row to the table if it has been executed; then, you would know implicitly that every entry has been executed. On the other hand, if you want to show transactions that have not yet been executed, a column that indicates whether the transaction has been executed would probably be a good idea. It might be even better to display details about the transaction that refer to its successful execution rather than just showing a yes/no flag. Perhaps you could store the timestamp that shows when the transaction was completed and maybe the identity of the person or program that completed the transaction or even a copy of the document generated by the transaction (or a link to this document). For instance, if a receipt was issued for this transaction, you might want to store an image of the receipt (or a link to the image) in the table. It´s a good idea, but for now, overkill... I mean here is my new design: CREATE TABLE `earnings` ( `transaction_id` int(20) NOT NULL auto_increment, `transaction_planned_date` date NOT NULL default '-00-00', `transaction_executed_date` date NOT NULL default '-00-00', `transaction_description` char(200) NOT NULL default '', `transaction_assignor` char(80) NOT NULL default '', `transaction_amount` int(20) NOT NULL default '0', PRIMARY KEY (`transaction_id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 CREATE TABLE `earnings` ( `transaction_id` int(20) NOT NULL auto_increment, `transaction_planned_date` date NOT NULL default '-00-00', `transaction_executed_date` date NOT NULL default '-00-00', `transaction_description` char(200) NOT NULL default '', `transaction_assignor` char(80) NOT NULL default '', `transaction_amount` int(20) NOT NULL default '0', PRIMARY KEY (`transaction_id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 I think it is a good model for start... About income and outcome, should I use a column called moviment_type or just put a negative value when is an outcome for example? Either approach should work. It would probably be easier to simply store the sign of the transaction with the amount. Does anyone ever made something like that, any other idea that could improve my little system? I think the question you need to ask yourself is What do I want to be able to find out about a transaction after it has taken place?. If you decide you want to know what currency was involved in the transaction, you need to record that in your table. If you decide you want to know the serial numbers of the banknotes used in the transaction, you need to record that. And so on. It might be wise to talk to someone in the bank and ask them what things they need to find out while a transaction is taking place and afterwards. You may want to try asking your users (or their managers) what sorts of things people ask for but can't get from the old system; these are prime candidates for things that you could add to your new system. What I want? Well that my partners stop asking me to send the spreadsheet every week... :) They want to take a look the company´s profitability, if all payments have been made, if all clients have payed... As I am the one who make every transaction I simple would input it into DB (direct) and they would access a simple
Re: Accountability with MySQL
Should you have a flag for the status movement complete ? I would say yes but instead of a simple checkbox, you could store a date value. That gives you two pieces of information a) if the date is null then the movement is not complete. b) if the date is NOT null then the movement is complete and you know when it finished. There, Shawn and I disagree :-) In case of a very simple database, one could use NULL as a flag or signal. But in general, I would avoid storing NULLs. Basics of database design: store what you know. Given that NULLs basically means the absence of a value (eg: unknown), you shouldn't be storing NULLs. Nonsense!! That's a bold statement ... That's simply wrong. A null means unknown or not applicable and is a Ah, so now things become clear, a NULL actually can mean two things? So much for clarity then... perfectly valid value to use in many, many situations. Two classic examples: Let me first state that there's a difference between storing NULLs and handling NULLs in your result. So, let's debunk these two classic examples... 1. You have a table containing test scores. Some students get every question on a given test wrong. Some students don't write the test at all, perhaps because they were sick. In this scenario, I think the correct thing to store is 0 for the students who got every question wrong and NULL for the students who didn't write the test. IMO, the better thing to do in this particular case is to NOT store a test result for the students that did not make the test. So, an example table with test scores: TEST_SCORES StudentID Int, TestID Int, Score TinyInt Unsigned Now, students who didn't make the test won't have a record in here. Perfectly valid design AND you avoid storing NULLs. 2. You have a table containing employee records. One of the columns is termination date. What value do you store for a new employee's termination date? Well, if they are a contractor on a fixed length contract, you could calculate the date the contract ends; fair enough. If they are a permanent employee and your area has mandatory retirement, you could calculate the date they turn 65 (or whatever) and use that. But what if they are a permanent employee and you don't have mandatory retirement? I would store a NULL to mean I don't know right now. Then, if and when they gave notice that they were leaving, I would change the termination date from NULL to their last day of work. Why store a date column if you don't know? Why not use: EMPLOYEES EmployeeID int, StartingDate Date, ... TERMINATED_EMPLOYEES (albeit a bit agressive ;) ) EmployeeID TerminationDate Once more: perfectly valid design. Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 'Into outfile' doesn't include the column names. How can it be done?
On 3/14/06, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Jake Peavy [EMAIL PROTECTED] wrote on 03/14/2006 01:52:28 PM: On 3/10/06, Jake Peavy [EMAIL PROTECTED] wrote: On 3/7/06, C.R.Vegelin [EMAIL PROTECTED] wrote: Hi Ariel, Maybe this example helps you to create CSV output from MySQL. The first SELECT generates the headerline; the second the data. ( SELECT 'FieldA','FieldB','FieldC', ... ) UNION ( SELECT `FieldA`, `FieldB`, `FieldC`, ... INTO OUTFILE 'D:/MySQL Datafiles/myFile.csv' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n' FROM ... ... GROUP BY `FieldA`, `FieldB`, `FieldC`, ... ); Don't forget the braces ( and ). HTH, Cor So, to all the SQL wizards out there... How would one perform this same operation, but using the INFORMATION_SCHEMA virtual db to provide the column headings in the first row rather having to hand type them as shown above? Bonus marks for beginning the line with a # My attempt stalled as soon as I got one column of data in the result set with a row for each column of the target table. SELECT COLUMNS FROM INFORMATION_SCHEMA WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 'testtable' Ideas? TIA, -jp Nobody has any ideas at all? (sheds solitary tear) -jp Nope, no suggestions. You are mixing purposes (not to mention data types) when you add column names to the output of INTO OUTFILE. Have you looked at using the CSV storage engine or mysqldump as alternatives? Yes, but based on the docs it would appear that it doesn't produce column names either (presumably because the table definition is stored in the .frm): mysql CREATE TABLE test(i INT, c CHAR(10)) ENGINE = CSV; Query OK, 0 rows affected (0.12 sec) mysql INSERT INTO test VALUES(1,'record one'),(2,'record two'); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql SELECT * FROM test; +--++ | i| c | +--++ |1 | record one | |2 | record two | +--++ 2 rows in set (0.00 sec) If you examine the test.CSV file in the database directory after executing the preceding statements, its contents look like this: 1,record one 2,record two I didn't actually try this, however, because my version hasn't been built to include the CSV engine. I could, of course, rebuild, but it didn't look like it would do what I wanted anyway. I think this (INTO OUTFILE w/column names) would be used infrequently, but it would be handy to send a small subset of data to someone so they could pivot it anyway they like in Excel. Appreciate the info though, if anyone else has any ideas How would one transform a single column of rows into a single row of columns? Seems like part of a crosstab query... -jp
Re: migrating 4.0 to 4.1 character set problems
Hi, I have figured out a way to get this to work. I am just posting what I did in case anyone else stumbles across my original message. I am sure there are other ways to get this to work, but this is the way that worked for me: ### # My HOWTO: Covernt mysql 4.0.x DBs to 4.1.x # # This will also update the character sets from # # Latin1 to utf8 # # # ### 1. Backup the old database. It must be backed up as separate tab separate files like the following: mysqldump --tab=/tmp/database database -u user --password=password 2. Tar and transfer the directory if necessary, then import it using the following commands: mysql -e CREATE DATABASE database default character set latin1 cat /tmp/database/*sql | mysql database -u user --password=password mysqlimport --default-character-set=latin1 alien8_orig /tmp/tables/ *txt -u user --password=password 3. Convert the database tables to utf-8 ALTER TABLE database.table type=MyISAM, CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci; 4. For use with php5, after every database connection send the following query: mysql_query(SET NAMES 'utf8'); 5. Also, for PHP5, you will want to make sure that server is using utf-8 as the encoding. I believe this is the default. And pages should be valid xhtml. I use the content-type meta tag just to be safe: meta http-equiv=Content-Type content=text/html; charset=UTF-8 / Thanks, Sean On 14-Mar-06, at 11:09 AM, Sean O'Hara wrote: Hi, I am having character set problems while trying to migrate my data from a server running 4.0.25-standard to a server running 4.1.16. I believe that the orginal database was using the latin1 character set (not sure, is there any way to tell? show full column doesn't seem to be supported in 4.0.x) and the default character set for the 4.1 server is latin1 as well. However when I try to import my data from a mysqldump from the older server all accents are turned into question marks. I've searched the forums and mailing lists extensively for clues as to how to resolve this issue. The most helpful post I found was here: http://lists.mysql.com/mysql/186657 However, even following the instructions on this post I ran into problems. Rather than turniing accents into question marks, any data with accent was truncated at the first instance of such a character (e.g. López becomes L). So that is not working either. Incidentally, I would be happy to convert the data to utf8 while upgrading to 4.1 but mostly I just want to get rid of the question mark characters. Any ideas? Thanks, Sean -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] __ ALIEN8 RECORDINGS P.O. BOX 666, STATION R MONTREAL, QC CANADA, H2S 3L1 http://www.alien8recordings.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Cannot connect to server
Sara Woglom wrote: Thanks Shawn!! You should be thanking Mladen Adamovic for pointing you to: http://dev.mysql.com/doc/mysql/en/grant.html This page holds your answer. From the error message you got Telnet to port 3306: Host 'my-machine-name' is not allowed to connect to this MySQL server you can be assured that you made a connection to the MySQL server. The problem is the MySQL server doesn't think you should be allowed to connect. I'm sure if you read the page linked above you will be able to persuade your MySQL server into allowing you to connect to it. James -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql.sock gone
Guys. I just started having a problem, Im running mysql-standard-4.1.12-pc-linux-gnu-i686 binaries under Fedora Core 3 and Ive been having problem where during some parts of the day, /tmp/mysql.oskc goes away, I can still see mysql running when I do a ps ax but when I try to connect to it, it says it cant connect thru mysql.sock What could be the cause that would make mysql.sock dissapear? Thx for any help you can provide -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysql.sock gone
Do you have any cron jobs that clear the /tmp directory during the day? 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: Anton Krall [mailto:[EMAIL PROTECTED] Sent: Wednesday, 15 March 2006 7:38 AM To: mysql@lists.mysql.com Subject: mysql.sock gone Guys. I just started having a problem, Im running mysql-standard-4.1.12-pc-linux-gnu-i686 binaries under Fedora Core 3 and Ive been having problem where during some parts of the day, /tmp/mysql.oskc goes away, I can still see mysql running when I do a ps ax but when I try to connect to it, it says it cant connect thru mysql.sock What could be the cause that would make mysql.sock dissapear? Thx for any help you can provide -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Very large from
Have to develop form with over 100 input fields plus 40 different drop downs. Seeking advice on technique to use. Thinking about single form where user has to use the power bar to move deeper into the form. This has benefit that every thing is written to the database at one time, but the draw back is the user frustration in entering so much data at one time. Alternate thoughts are breaking it down into 2 or 3 separate pages with each page being written to the data base. First page does a insert to create the row, then following pages do updates to complete populating the row with data. Has anyone done anything like this or have seen this done before. What advice can you offer. Where to look for examples. Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Very large from
Both methods are commonly used. Another method used is to have multiple pages like you've mentioned but save all the data into a session (of just pass it from page to page) until all 3 pages are complete and then write all the data at once The question to ask yourself is, do you want just partial data, an incomplete form, to be in your database/table. If you don't, then method 1 or the method I just mentioned is the way to go. Otherwise you'll have incomplete data in your dataset. Surveys do both of any/all 3 of these methods. Of course, with ajax now, I'm seeing more written to the database while a user is inputing their data and then the web application has to remove, at some point, incomplete data. Good Luck! Have to develop form with over 100 input fields plus 40 different drop downs. Seeking advice on technique to use. Thinking about single form where user has to use the power bar to move deeper into the form. This has benefit that every thing is written to the database at one time, but the draw back is the user frustration in entering so much data at one time. Alternate thoughts are breaking it down into 2 or 3 separate pages with each page being written to the data base. First page does a insert to create the row, then following pages do updates to complete populating the row with data. Has anyone done anything like this or have seen this done before. What advice can you offer. Where to look for examples. Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- /Brett C. Harvey; /Creative-Pages.Net, President; /Facility Management Systems, CTO (www.fmsystems.biz); /Lasso Partner Association Member ID #LPA135259 (www.omnipilot.com/www.lassopartner.com); -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Optimization Question
[EMAIL PROTECTED] wrote: Yes, a ranged query should respond faster than a negation. In some cases you can seriously improve query performance for a negation query if you split it into two range queries unioned together. Here is a pseudo example: This query should be slow due to the table scan it takes to test the condition: SELECT ... FROM ... WHERE indexfield 16 This query will use the index if the number of rows with indexfield != 16 is small enough ( about 30%). This query should be noticeably faster due to using ranged queries (partial index ranged matches): ( SELECT ... FROM ... WHERE indexfield 16 ) UNION ( SELECT ... FROM ... WHERE indexfield 16 ) At the very least, you'll want to make that UNION ALL, rather than just UNION, so mysql doesn't waste time looking for duplicates to remove. But even then, this may be slower. First, these two unioned queries may still not use the index. If the number of rows with indexfield 16 is too large (more than about 30%) the first query will be a table scan. If the number of rows with indexfield 16 is too large (more than about 30%) the second query will be a table scan. In fact, if the number of rows with indexfield = 16 is less than about 40% of the table, then at least one of the two unioned queries is guaranteed to be a table scan. Worse yet, this query stands a good chance of being twice as long as the single, != query, because it may require 2 table scans! Second, even if both unioned queries use the index, the result still may not be faster. If the combined number of matching rows is too high, the full table scan should be faster than the indexed lookups. For example: SELECT cat, COUNT(*) FROM inits GROUP BY cat; +--+--+ | cat | COUNT(*) | +--+--+ |0 | 5743 | |1 | 3792 | |2 |30727 | |3 | 1926 | |4 | 7812 | +--+--+ 19273 rows (38.55%) match cat != 2, with roughly half (~19%) on either side. First, the != case: EXPLAIN SELECT * FROM inits WHERE cat != 2 \G *** 1. row *** id: 1 select_type: SIMPLE table: inits type: ALL possible_keys: cat_idx key: NULL key_len: NULL ref: NULL rows: 5 Extra: Using where As expected, a full table scan. SELECT * FROM inits WHERE cat != 2; ... 19273 rows in set (0.37 sec) Now, the unioned range queries: EXPLAIN SELECT * FROM inits WHERE cat 2 UNION ALL SELECT * FROM inits WHERE cat 2 \G *** 1. row *** id: 1 select_type: PRIMARY table: inits type: range possible_keys: cat_idx key: cat_idx key_len: 5 ref: NULL rows: 5680 Extra: Using where *** 2. row *** id: 2 select_type: UNION table: inits type: range possible_keys: cat_idx key: cat_idx key_len: 5 ref: NULL rows: 6543 Extra: Using where *** 3. row *** id: NULL select_type: UNION RESULT table: union1,2 type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: NULL Extra: As hoped, mysql plans to use the index for each query. SELECT * FROM inits WHERE cat 2 UNION ALL SELECT * FROM inits WHERE cat 2; ... 19273 rows in set (0.78 sec) Despite (because of) using the index, this takes more than twice as long! Of course, if you have to do a table scan ANYWAY (because a value you have in a constraint is not in an index) then this won't help. This optimization is all about getting the engine to use an index whenever it can instead of performing a table scan. Of course, that is not always possible. Even when using an index is possible, it is not always desirable. I'd suggest not trying to outfox the optimizer until you've first determined it is making bad choices, and then test to make sure the solution is actually an improvement. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Very large from
Not wanting to be rude but this question would probably be answered better in a php group. I realise there are many users of php that subscribe to this list, but this is a mysql list not php. If you go to http://www.php.net/support.php you will find a large number of resources (including lists at http://www.php.net/mailing-lists.php), not to mention examples of code, irc channels etc. Regards fbsd_user wrote: Have to develop form with over 100 input fields plus 40 different drop downs. Seeking advice on technique to use. Thinking about single form where user has to use the power bar to move deeper into the form. This has benefit that every thing is written to the database at one time, but the draw back is the user frustration in entering so much data at one time. Alternate thoughts are breaking it down into 2 or 3 separate pages with each page being written to the data base. First page does a insert to create the row, then following pages do updates to complete populating the row with data. Has anyone done anything like this or have seen this done before. What advice can you offer. Where to look for examples. Thanks -- David Logan South Australia when in trouble, or in doubt run in circles, scream and shout -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysql.sock gone
No crons that would delete the tmp directory.. In fact, all the other files stay there... Just mysql.sock goes away... |-Original Message- |From: Logan, David (SST - Adelaide) [mailto:[EMAIL PROTECTED] |Sent: Tuesday, March 14, 2006 3:15 PM |To: Anton Krall; mysql@lists.mysql.com |Subject: RE: mysql.sock gone | |Do you have any cron jobs that clear the /tmp directory during the day? | |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: Anton Krall [mailto:[EMAIL PROTECTED] |Sent: Wednesday, 15 March 2006 7:38 AM |To: mysql@lists.mysql.com |Subject: mysql.sock gone | |Guys. | |I just started having a problem, Im running |mysql-standard-4.1.12-pc-linux-gnu-i686 binaries under Fedora |Core 3 and Ive been having problem where during some parts of |the day, /tmp/mysql.oskc goes away, I can still see mysql |running when I do a ps ax but when I try to connect to it, it |says it cant connect thru mysql.sock | |What could be the cause that would make mysql.sock dissapear? | |Thx for any help you can provide | | |-- |MySQL General Mailing List |For list archives: http://lists.mysql.com/mysql |To unsubscribe: |http://lists.mysql.com/[EMAIL PROTECTED] | | |-- |MySQL General Mailing List |For list archives: http://lists.mysql.com/mysql |To unsubscribe: |http://lists.mysql.com/[EMAIL PROTECTED] | | | -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Very large from
On Tuesday 14 March 2006 22:24, fbsd_user wrote: Have to develop form with over 100 input fields plus 40 different drop downs. Seeking advice on technique to use. Thinking about single form where user has to use the power bar to move deeper into the form. This has benefit that every thing is written to the database at one time, but the draw back is the user frustration in entering so much data at one time. Alternate thoughts are breaking it down into 2 or 3 separate pages with each page being written to the data base. First page does a insert to create the row, then following pages do updates to complete populating the row with data. Has anyone done anything like this or have seen this done before. What advice can you offer. Where to look for examples. Thanks Not knowing what language you use, how about a form consisting several tabs, where each tab contains grouped data? Switching from tab can be done by clicking on the tab itself or a Next button on each page... -- 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]
RE: Very large from
I understand the save the page form data into the session. But I don't know what you mean by just pass it from page to page. Would that mean having all the fields in regular form fields to accept the entered data and also hidden corresponding fields to hide page 1 data in as page 2 is entered and for page 3, page 1 2 would be in hidden fields? This results in all that data being passed back and forth between the web server and the user. This would really be slow for a dial up user. Or am I not understanding what you were talking about. Using the 3 page method with data being written by page, I would have flag on the record which would not be a user input field, but a program control to identify the record as not usable until all 3 pages have been executed. Since all the fields are not mandatory there will always be empty fields and that is ok by business design. -Original Message- From: Brett Harvey [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 14, 2006 4:35 PM To: [EMAIL PROTECTED]; mysql@lists.mysql.com Subject: Re: Very large from Both methods are commonly used. Another method used is to have multiple pages like you've mentioned but save all the data into a session (of just pass it from page to page) until all 3 pages are complete and then write all the data at once The question to ask yourself is, do you want just partial data, an incomplete form, to be in your database/table. If you don't, then method 1 or the method I just mentioned is the way to go. Otherwise you'll have incomplete data in your dataset. Surveys do both of any/all 3 of these methods. Of course, with ajax now, I'm seeing more written to the database while a user is inputing their data and then the web application has to remove, at some point, incomplete data. Good Luck! Have to develop form with over 100 input fields plus 40 different drop downs. Seeking advice on technique to use. Thinking about single form where user has to use the power bar to move deeper into the form. This has benefit that every thing is written to the database at one time, but the draw back is the user frustration in entering so much data at one time. Alternate thoughts are breaking it down into 2 or 3 separate pages with each page being written to the data base. First page does a insert to create the row, then following pages do updates to complete populating the row with data. Has anyone done anything like this or have seen this done before. What advice can you offer. Where to look for examples. Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- /Brett C. Harvey; /Creative-Pages.Net, President; /Facility Management Systems, CTO (www.fmsystems.biz); /Lasso Partner Association Member ID #LPA135259 (www.omnipilot.com/www.lassopartner.com); -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Very large from
Well not wanting to be rude back. But the question is dealing with how best to control the writing of data to mysql. No where do I say anything about using php. I could be using perl for all you know. But I respect your right to state your thoughts, but just don't agree with it. I think this is a better place to ask this question, and acknowledge that people on the php list would also most likely be able to shed light on this subject. -Original Message- From: David Logan [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 14, 2006 4:53 PM To: [EMAIL PROTECTED]; mysql@lists.mysql.com Subject: Re: Very large from Not wanting to be rude but this question would probably be answered better in a php group. I realise there are many users of php that subscribe to this list, but this is a mysql list not php. If you go to http://www.php.net/support.php you will find a large number of resources (including lists at http://www.php.net/mailing-lists.php), not to mention examples of code, irc channels etc. Regards fbsd_user wrote: Have to develop form with over 100 input fields plus 40 different drop downs. Seeking advice on technique to use. Thinking about single form where user has to use the power bar to move deeper into the form. This has benefit that every thing is written to the database at one time, but the draw back is the user frustration in entering so much data at one time. Alternate thoughts are breaking it down into 2 or 3 separate pages with each page being written to the data base. First page does a insert to create the row, then following pages do updates to complete populating the row with data. Has anyone done anything like this or have seen this done before. What advice can you offer. Where to look for examples. Thanks -- David Logan South Australia when in trouble, or in doubt run in circles, scream and shout -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
restoring backup, problem with BLOB?
Hello List, I am restoring a MySQL backup (about 40MB), and its taking well over 2 hours to restore. the area where its really hanging is when its importing the BLOB There are about 1200 records, totaling about 10mb in size for these blobs. I am running MyISAM, my current memory settings read: bulk_insert_buffer_size 805306368 innodb_additional_mem_pool_size 7340032 innodb_buffer_pool_awe_mem_mb 0 innodb_buffer_pool_size 318767104 innodb_log_buffer_size 4194304 join_buffer_size131072 key_buffer_size 164626432 myisam_sort_buffer_size 193986560 net_buffer_length 16384 read_buffer_size61440 read_rnd_buffer_size258048 sort_buffer_size262136 Any ideas whats going on here? its a 3 ghz P4, running 1024 ram.
Re: Very large from
At 4:24 pm -0500 14/3/06, fbsd_user wrote: user has to use the power bar to move deeper into the form Out of curiosity, what's the power bar? From a db perspective one single insert is preferable, IMO. Firstly you don't have to declare as NULL required fields that will be entered in the second, third or Nth form page. Secondly you won't get incomplete entries, as has already been mentioned. If it was a sign-up form, say, and you have a unique index on the user e-mail address, then you would run into problems if a user completed the first page of the signup and created a new row in the table, then for some reason started from scratch (e.g. their 'puter crashed). They wouldn't be able to start again because their e-mail address would already be in the unique field in the table. I would do one of two things: a) use a session management system to store the submitted details until you get to the final form page and can insert the whole lot into the table b) Have the whole form on one page and use Javascript to only show one part of the form at any one time (but in such a way that it degrades gracefully for non-JS browsers - i.e. they get the whole form on one page) James Harvard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Accountability with MySQL
- Original Message - From: Bruno B B Magalháes [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, March 14, 2006 2:07 PM Subject: Re: Accountability with MySQL I am designing a simple accountability system so all the partners can have direct access to it by intranet. I was designing the data model, and came up with this: CREATE TABLE `moviments` ( `moviment_id` int(20) NOT NULL auto_increment, `moviment_date` date NOT NULL default '-00-00', `moviment_description` char(200) NOT NULL default '', `moviment_assignor` char(80) NOT NULL default '', `moviment_drawee` char(80) NOT NULL default '', `moviment_amount` int(20) NOT NULL default '0', PRIMARY KEY (`moviment_id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 One small observation: if your column names are meant to be in English - as suggested by your use of amount, drawee, etc. I think you should also use an English word where you are currently using moviment. Moviment is not an English word. There _is_ an English word movement that might be what you want but I suspect that transaction would be the term most English-speakers would use in the table you describe. Your email address indicates that you are from Brazil so I'll assume you are a Portuguese-speaker, not a native English-speaker. This suggestion is only meant to be a friendly suggestion, not an insult to your English which is very very good. :-) Ups, that´s right, I misspelt movement, you know beeing a partner and also being the financial director, technology director and CEO some times can be exhaustive. Well, at least we are growing.. (the opposite of my weekends, that seams to be getting smaller and smaller). But thanks, I also agree transaction is more suitable. But I am a little bit stuck with this: Should I use a column to mark if the moviment has been executed? That depends. You could decide to only add the row to the table if it has been executed; then, you would know implicitly that every entry has been executed. On the other hand, if you want to show transactions that have not yet been executed, a column that indicates whether the transaction has been executed would probably be a good idea. It might be even better to display details about the transaction that refer to its successful execution rather than just showing a yes/no flag. Perhaps you could store the timestamp that shows when the transaction was completed and maybe the identity of the person or program that completed the transaction or even a copy of the document generated by the transaction (or a link to this document). For instance, if a receipt was issued for this transaction, you might want to store an image of the receipt (or a link to the image) in the table. It´s a good idea, but for now, overkill... I mean here is my new design: CREATE TABLE `earnings` ( `transaction_id` int(20) NOT NULL auto_increment, `transaction_planned_date` date NOT NULL default '-00-00', `transaction_executed_date` date NOT NULL default '-00-00', `transaction_description` char(200) NOT NULL default '', `transaction_assignor` char(80) NOT NULL default '', `transaction_amount` int(20) NOT NULL default '0', PRIMARY KEY (`transaction_id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 CREATE TABLE `earnings` ( `transaction_id` int(20) NOT NULL auto_increment, `transaction_planned_date` date NOT NULL default '-00-00', `transaction_executed_date` date NOT NULL default '-00-00', `transaction_description` char(200) NOT NULL default '', `transaction_assignor` char(80) NOT NULL default '', `transaction_amount` int(20) NOT NULL default '0', PRIMARY KEY (`transaction_id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 I think it is a good model for start... About income and outcome, should I use a column called moviment_type or just put a negative value when is an outcome for example? Either approach should work. It would probably be easier to simply store the sign of the transaction with the amount. Does anyone ever made something like that, any other idea that could improve my little system? I think the question you need to ask yourself is What do I want to be able to find out about a transaction after it has taken place?. If you decide you want to know what currency was involved in the transaction, you need to record that in your table. If you decide you want to know the serial numbers of the banknotes used in the transaction, you need to record that. And so on. It might be wise to talk to someone in the bank and ask them what things they need to find out while a transaction is taking place and afterwards. You may want to try asking your users (or their managers) what sorts of things people ask for but can't get from the old system; these are prime candidates for things that you could add to your new system. What I want? Well that my partners stop asking me to send the spreadsheet every week... :) They want to take a look the company´s profitability, if all
Re: Accountability with MySQL
- Original Message - From: Martijn Tonies [EMAIL PROTECTED] To: Rhino [EMAIL PROTECTED]; mysql@lists.mysql.com Sent: Tuesday, March 14, 2006 2:16 PM Subject: Re: Accountability with MySQL Should you have a flag for the status movement complete ? I would say yes but instead of a simple checkbox, you could store a date value. That gives you two pieces of information a) if the date is null then the movement is not complete. b) if the date is NOT null then the movement is complete and you know when it finished. There, Shawn and I disagree :-) In case of a very simple database, one could use NULL as a flag or signal. But in general, I would avoid storing NULLs. Basics of database design: store what you know. Given that NULLs basically means the absence of a value (eg: unknown), you shouldn't be storing NULLs. Nonsense!! That's a bold statement ... That's simply wrong. A null means unknown or not applicable and is a Ah, so now things become clear, a NULL actually can mean two things? So much for clarity then... Come on; that's not fair. Unknown and not applicable are more like different senses of the same thing, not two opposite things. perfectly valid value to use in many, many situations. Two classic examples: Let me first state that there's a difference between storing NULLs and handling NULLs in your result. Yes, of course there is: so what? You said NULLs should never be _stored_; that's what I'm responding to. Using NULLs is not very _difficult_ anyway, just a bit tedious because it is another case to handle. So, let's debunk these two classic examples... Please note that I did not say that you MUST use NULLs, just that they were perfectly valid to use in a design. 1. You have a table containing test scores. Some students get every question on a given test wrong. Some students don't write the test at all, perhaps because they were sick. In this scenario, I think the correct thing to store is 0 for the students who got every question wrong and NULL for the students who didn't write the test. IMO, the better thing to do in this particular case is to NOT store a test result for the students that did not make the test. Okay, that might be acceptable, if it doesn't cause you to lose track of the student altogether. But if this table was the only one that even recorded the _existence_ of the student, you'd have a problem; if someone tried to verify that the student had attended this school, you wouldn't know that they had. Or in a more probable case, if that was the only test for that course and the student missed it and then had no row in the table, you might not have any way of knowing that they took the course! And if they later wanted to write the exam, having recovered from their illness, your query might have the effect of keeping them from taking the second exam: your query would report that they had never taken the first exam so an adminstrator might refuse to let him/her take the second exam because they (apparently) had never been scheduled to take the first one. Now, you could have a second table to record people who were scheduled to take tests but failed to take them to cover that situation but I think it would be easier to record all students in one table and then simply store a null for any test that they fail to take and a zero for every case where a student got every answer wrong. When you compute the class average, the avg() function would ensure that the students who got every question wrong would pull down the class average but that students who failed to write the test at all would NOT skew the average because the avg() function ignores nulls. So, an example table with test scores: TEST_SCORES StudentID Int, TestID Int, Score TinyInt Unsigned Now, students who didn't make the test won't have a record in here. Perfectly valid design AND you avoid storing NULLs. 2. You have a table containing employee records. One of the columns is termination date. What value do you store for a new employee's termination date? Well, if they are a contractor on a fixed length contract, you could calculate the date the contract ends; fair enough. If they are a permanent employee and your area has mandatory retirement, you could calculate the date they turn 65 (or whatever) and use that. But what if they are a permanent employee and you don't have mandatory retirement? I would store a NULL to mean I don't know right now. Then, if and when they gave notice that they were leaving, I would change the termination date from NULL to their last day of work. Why store a date column if you don't know? Why not use: EMPLOYEES EmployeeID int, StartingDate Date, ... TERMINATED_EMPLOYEES (albeit a bit agressive ;) ) EmployeeID TerminationDate Once more: perfectly valid design. Yes, that is also a valid design but it means you have to have yet another table that you could have avoided simply by permitting a null in
Re: Accountability with MySQL
At 7:48 pm -0500 14/3/06, Rhino wrote: Ah, so now things become clear, a NULL actually can mean two things? So much for clarity then... Come on; that's not fair. Unknown and not applicable are more like different senses of the same thing, not two opposite things. IIRC (but please don't ask for a reference) it has been suggested that there should be two different types of NULL (value unknown not applicable) for just this reason. I suppose what it boils down to is that although the two main reasons one might use NULL are *not* the same thing, the outcome (the absence of a value) *is* the same. So you're both right. Sort of. :-) James Harvard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
insert my_ulonglong data with C API
Hello,everyone! My table has a bigint column which needs store 8bytes integer. I looked up in Mysql Menual. It seems that , if I use C API I can only use preapared statement functions to insert bigint values. I can not use mysql_real_query or mysql_query to insert bigint values because I do not know how to convert a my_ulonglong to a string or character array. Am I right? Any ideas? Thanks. Regards, Gu Lei
Re: insert my_ulonglong data with C API
In the last episode (Mar 15), said: Hello,everyone! My table has a bigint column which needs store 8bytes integer. I looked up in Mysql Menual. It seems that , if I use C API I can only use preapared statement functions to insert bigint values. I can not use mysql_real_query or mysql_query to insert bigint values because I do not know how to convert a my_ulonglong to a string or character array. sprintf(buf, insert into mytable values ( %llu ), myulonglongint); -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Accountability with MySQL
At 7:48 pm -0500 14/3/06, Rhino wrote: Ah, so now things become clear, a NULL actually can mean two things? So much for clarity then... Come on; that's not fair. Unknown and not applicable are more like different senses of the same thing, not two opposite things. IIRC (but please don't ask for a reference) it has been suggested that there should be two different types of NULL (value unknown not applicable) for just this reason. Yep. unknown and n/a are two different things and SQL happens to support them by NULLs. This is confusing at times and more difficult to handle at least... The problems with NULLs are numerous. Don't say they aren't, cause they are. In several SQL functions, NULLs make a difference and they will bite you every now and then. Especially for new people, which is a clear signal they are confusing. I suppose what it boils down to is that although the two main reasons one might use NULL are *not* the same thing, the outcome (the absence of a value) *is* the same. So you're both right. Sort of. :-) ;-) Martijn Tonies Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Accountability with MySQL
Basics of database design: store what you know. Given that NULLs basically means the absence of a value (eg: unknown), you shouldn't be storing NULLs. Nonsense!! That's a bold statement ... That's simply wrong. A null means unknown or not applicable and is a Ah, so now things become clear, a NULL actually can mean two things? So much for clarity then... Come on; that's not fair. Unknown and not applicable are more like different senses of the same thing, not two opposite things. Read the literature on how to design databases. What you do is storing true propositions. That is, each attribute defines a certain true proposition. For example: Employee Martijn has Employee# 14. You should be able to derive these sentences from every row. Putting a NULL or N/A in there fails to meet this requirement. perfectly valid value to use in many, many situations. Two classic examples: Let me first state that there's a difference between storing NULLs and handling NULLs in your result. Yes, of course there is: so what? You said NULLs should never be _stored_; that's what I'm responding to. Using NULLs is not very _difficult_ anyway, just a bit tedious because it is another case to handle. There's no point in storing what you don't know. So, let's debunk these two classic examples... Please note that I did not say that you MUST use NULLs, just that they were perfectly valid to use in a design. Depends on who you're asking ... A few years ago, I was working at a company that had developed an application with an Oracle database with about 400 tables, nothing too large. Plenty of tables had NULLs, not because of missing business data, but rather used internally in the system. The number of problems we had with them, I cannot count on my two hands, nor can I count them on the hands of all other developers (5) for that project. Just last week, I spoke to a guy who used to be my collegue there and we discussed the design a bit (he still does database design and development for a large company, on Sybase with over 4000 deployed systems) and we both agreed that using NULLs in most cases as part of the initial design was causing us more problems then helping us. 1. You have a table containing test scores. Some students get every question on a given test wrong. Some students don't write the test at all, perhaps because they were sick. In this scenario, I think the correct thing to store is 0 for the students who got every question wrong and NULL for the students who didn't write the test. IMO, the better thing to do in this particular case is to NOT store a test result for the students that did not make the test. Okay, that might be acceptable, if it doesn't cause you to lose track of the student altogether. But if this table was the only one that even recorded the _existence_ of the student, you'd have a problem; Indeed, then I would have a problem, cause you cannot derive from the test results table that a student exists. This is a rather silly statement. If the test results table should ALSO store the existence of a student, you're design is wrong :-) if someone tried to verify that the student had attended this school, you wouldn't know that they had. In your own words: nonsense. Each table should store what is was designed to store. If I would have to know if a student was enlisted in a certain course or would be attending this school, I would not be using the test results table. Or in a more probable case, if that was the only test for that course and the student missed it and then had no row in the table, you might not have any way of knowing that they took the course! See above. And if they later wanted to write the exam, having recovered from their illness, your query might have the effect of keeping them from taking the second exam: your query would report that they had never taken the first exam so an adminstrator might refuse to let him/her take the second exam because they (apparently) had never been scheduled to take the first one. Different problem, see above. Now, you could have a second table to record people who were scheduled to take tests but failed to take them to cover that situation but I think it Sounds like a decent design to me. would be easier to record all students in one table and then simply store a null for any test that they fail to take and a zero for every case where a student got every answer wrong. When you compute the class average, the avg() function would ensure that the students who got every question wrong would pull down the class average but that students who failed to write the test at all would NOT skew the average because the avg() function ignores nulls. Ah, an excellent example of why NULLs are tedious: they are ignored by some functions, but not by others. Without having the record there in the first place, there would have to be no rule of AVG ignoring
