RE: Machine Learning
Hi Garot, This sounds an interesting idea. Are you looking to store all known commands and their options or are you looking for a 'formula' for calling any unix command ? The reason for my question is that, at the end of the day, a unix command is just a program that is run in the operating system. Each program comes with its own options and acceptable inputs. I don't know if there is a rule or convention for structuring these commands. Are you then looking to build a system that 'knows' all commands and 'how to' call them ? Thanks, Justin -Original Message- From: Garot Conklin [mailto:garotconk...@yahoo.com] Sent: 20 August 2012 03:39 To: Martin Gainty; mysql@lists.mysql.com Subject: Re: Machine Learning The initial goal is to provide a working framework from which to call all UNIX shell command combinations as the underlying storage mechanism for a machine learning algorithm. I would like to build a completely self aware instantiation that will maintain itself on all levels... I postulate that the first place to start would be in determining a method for maintaining all possible remediation combinations including the unknown to eventually be learned from and populate new knowledge into the database. Thank you for the reply, Garot -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
ANN: Database Workbench 4.3.1, the multi-DBMS IDE now available!
ANN: Database Workbench 4.3.1, the multi-DBMS IDE now available! Ladies, gentlemen, Upscene Productions is proud to announce the next version of the popular Windows-based multi-DBMS development tool: Database Workbench 4.3.1 Pro This release fixes a few issues with MySQL support. For more information, see http://www.upscene.com/go/?go=newsid=20120820 The FREE Lite Editions will follow later. Changes include: - NEW: Stored Procedure Trigger debugger for MySQL - NEW: incremental data search in SQL, Table View Editor - FIXED: Oracle Debugger fixes - FIXED: NexusDB error when testing Stored Functions - FIXED: MySQL module not properly displaying stored procedures/functions without mysql.procs access and much more... Database Workbench supports: - Borland InterBase ( 6.x - XE ) - Firebird ( 1.x, 2.x ) - MS SQL Server/MSDE ( 7, 2000, 2005, 2008 ) - MySQL 4.x, 5.x - Oracle Database ( 8i, 9i, 10g, 11g ) - Sybase SQL Anywhere ( 9, 10, 11 and 12 ) - NexusDB ( 3.0 and up ) Thank you for your support, Martijn Tonies Database Workbench - the database developer tool for professionals Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: update doesn't
On Sun, August 19, 2012 18:19, william drescher wrote: On 8/17/2012 12:13 PM, Rik Wasmus wrote: I get 1 row affected, but the status does not change when I look at the row. If I set it to 'X' it does change. To make it even more wacky, if I (using phpMyAdmin) change it to 'H' it will change and the row is shown change, but when I go to examine the row (using the pencil icon=Edit) it changes back to 'W'. Either there is something really strange or my mysql is possessed. I am using Server version: 5.1.63-0ubuntu0.10.04. Anyone have any thoughts about this or suggestions on how to debug it? 1) One thing that _could_ do this is a trigger. Does SHOW TRIGGERS; show any that could be doing this? 2) However, in 99.999% of cases, it is just a logic error in the application (be it your application or PHPMyAdmin), not anything in MySQL. Can you connect with the command line client, run the UPDATE statement, en then check what the SELECT shows? If it shows a correct result... the problem ain't in MySQL itself. mysql select status from tasks; ++ | status | ++ | W | ++ 1 row in set (0.00 sec) mysql update tasks set status= 'H'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed 1 Warnings: 0 mysql select status from tasks; ++ | status | ++ | W | ++ 1 row in set (0.00 sec) whoops bill Maybe a SHOW CREATE TABLE `tasks`\g could shed some light. -- Mogens Melander +66 8701 33224 -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: update doesn't
On 8/20/2012 10:09 AM, Mogens Melander wrote:
On Sun, August 19, 2012 18:19, william drescher wrote:
On 8/17/2012 12:13 PM, Rik Wasmus wrote:
I get 1 row affected, but the status does not change when I look
at the row.
If I set it to 'X' it does change.
To make it even more wacky, if I (using phpMyAdmin) change it to
'H' it will change and the row is shown change, but when I go to
examine the row (using the pencil icon=Edit) it changes back to 'W'.
Either there is something really strange or my mysql is possessed.
I am using Server version: 5.1.63-0ubuntu0.10.04.
Anyone have any thoughts about this or suggestions on how to
debug it?
1) One thing that _could_ do this is a trigger. Does SHOW TRIGGERS; show
any
that could be doing this?
2) However, in 99.999% of cases, it is just a logic error in the
application
(be it your application or PHPMyAdmin), not anything in MySQL. Can you
connect
with the command line client, run the UPDATE statement, en then check
what the
SELECT shows? If it shows a correct result... the problem ain't in MySQL
itself.
mysql select status from tasks;
++
| status |
++
| W |
++
1 row in set (0.00 sec)
mysql update tasks set status= 'H';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed 1 Warnings: 0
mysql select status from tasks;
++
| status |
++
| W |
++
1 row in set (0.00 sec)
whoops
bill
Maybe a SHOW CREATE TABLE `tasks`\g could shed some light.
I can't figure out how to get puTTY to log the session so I can't
cut and paste the output, but here is a phpMySQL export of the
table with the extra stuff typed in from the create table
task_id mediumint(9) NOT NULL AUTO_INCREMENT
status char(1) NOT NULL DEFAULT ''
priority char(1) NOT NULL
due_date_time datetimeNOT NULL
hold_date_time datetime NOT NULL
review_date_time datetime Default NULL
requestor varchar(10) NOT NULL
performer varchar(10) NOT NULL
repeat_frequency char(1) NOT NULL
repeat_time char(2) NOT NULL
repeat_from char(1) NOT NULL
task_title varchar(60) NOT NULL
description text Yes NULL
history text Yes NULL
function_to_run varchar(80) DEFAULT NULL
last_access datetime NOT NULL
completed datetime NOT NULL
notify tinyint(1) DEFAULT NULL
PRIMARY kEY('TASK_ID')
UNIQUE KEY 'performer' ('performer', 'status', priority',
due_date_time', 'task_id')
UNIQUE KEY 'requestor' ('requestor', prirority','due_date_time',
'task_id')
ENGINE=InnoDB AUTO_INCREMENT=312 DEFAULT CHARSET=ascii
As a workaround I changed status to task_status and now it
works just fine.
bill
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
Re: Machine Learning
My initial thought was to propagate the db with everything and allow the algorithm to then begin to determin trends/patterns and begin either an indexing methodology, additional table/db creation process or both to further optimize the calls being made and build in some internal levels of redundancy. I am actually approaching this with some degree of biological conception in the multipathing within our own brains however until I have something up and running under some substantial load however I may not get a complete picture. Thanks, Garot
RE: Hi, how did u do de-emphasis of sql statements?
Let me change your example slightly... select * from table where name’A’ versus select * from table where name’Z’ Now, let's assume you have an INDEX starting with `name` and names are distributed in the typical way. The will be perhaps 1% of the names satisfying 'Z', but 95% satisfying 'A'. The index would be very useful for Z, but a waste for A. Hence, it is a feature that MySQL does not cache execution plans. You will also find that MySQL's query analyzer is very fast (compared to the competition). Hence, there is much less need for a cache than 'they' have. -Original Message- From: Johan De Meersman [mailto:vegiv...@tuxera.be] Sent: Monday, August 13, 2012 5:49 AM To: MID.night Cc: 673575760; mysql Subject: Re: Hi, how did u do de-emphasis of sql statements? - Original Message - From: MID.night 693893...@qq.com Like select * from table where name’A’ is just The same as select * from table where name’B’. The execution plan for both statements is indeed likely (but not guaranteed!) to be the same. As far as I'm aware, though, MySQL does not bother about that, though, as there is no execution plan cache. The query result cache does not equate the statements - it works based off the EXACT query text, INCLUDING spaces and capitalization. When analyzing various logs, the Maatkit/Aspersa/Percona toolset does transform SQL statements into their canonical form, though; so if you're looking for ways to do that you can have a look at how it's done there. -- Linux Bier Wanderung 2012, now also available in Belgium! August, 12 to 19, Diksmuide, Belgium - http://lbw2012.tuxera.be -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: Machine Learning
From: garotconk...@yahoo.com Subject: Re: Machine Learning To: webmas...@lisol.co.uk; mgai...@hotmail.com; mysql@lists.mysql.com My initial thought was to propagate the db with everything and allow the algorithm to then begin to determin trends/patterns MGwhich trends or patterns will you be modelling? and begin either an indexing methodology MGwhich indexes are you considering: Unique index, primary index or foreign index? additional table/db creation process or both to further optimize the calls being made MGoptimize based on execution time or diskspace allocated, EliminatingFTS or some other criteria? MGhttps://dev.mysql.com/doc/refman/5.5/en/optimization.html and build in some internal levels of redundancy. MGwhat about replication MGhttp://dev.mysql.com/doc/refman/5.5/en/replication.html I am actually approaching this with some degree of biological conception in the multipathing within our own brains however until I have something up and running under some substantial load however I may not get a complete picture. Thanks, Garot Interesting Martin From: webmas...@lisol.co.uk webmas...@lisol.co.uk; To: 'Garot Conklin' garotconk...@yahoo.com; 'Martin Gainty' mgai...@hotmail.com; mysql@lists.mysql.com; Subject: RE: Machine Learning Sent: Mon, Aug 20, 2012 7:13:25 AM Hi Garot, This sounds an interesting idea. Are you looking to store all known commands and their options or are you looking for a 'formula' for calling any unix command ? The reason for my question is that, at the end of the day, a unix command is just a program that is run in the operating system. Each program comes with its own options and acceptable inputs. I don't know if there is a rule or convention for structuring these commands. Are you then looking to build a system that 'knows' all commands and 'how to' call them ? Thanks, Justin -Original Message- From: Garot Conklin [mailto:garotconk...@yahoo.com] Sent: 20 August 2012 03:39 To: Martin Gainty; mysql@lists.mysql.com Subject: Re: Machine Learning The initial goal is to provide a working framework from which to call all UNIX shell command combinations as the underlying storage mechanism for a machine learning algorithm. I would like to build a completely self aware instantiation that will maintain itself on all levels... I postulate that the first place to start would be in determining a method for maintaining all possible remediation combinations including the unknown to eventually be learned from and populate new knowledge into the database. Thank you for the reply, Garot
Re: Machine Learning
MGwhich trends or patterns will you be modelling? ALL (interesting answer if you allow yourself to consider that there really are no restrictions aside from those we would impose here) MGwhich indexes are you considering: Unique index, primary index or foreign index?Primary for speed however unique may be more useful to keep the unwanted redundancy to a minimum. MGoptimize based on execution time or diskspace allocated, EliminatingFTS or some other criteria? As diskspace is somewhat less concerning these days, I would like to focus on execution time. MGwhat about replication -Yes, however I wanted an internal form of redundancy as well as distributed. garotconk...@yahoo.com From: Martin Gainty mgai...@hotmail.com To: garotconk...@yahoo.com; webmas...@lisol.co.uk; mysql@lists.mysql.com mysql@lists.mysql.com Sent: Monday, August 20, 2012 2:23 PM Subject: RE: Machine Learning From: garotconk...@yahoo.com Subject: Re: Machine Learning To: webmas...@lisol.co.uk; mgai...@hotmail.com; mysql@lists.mysql.com My initial thought was to propagate the db with everything and allow the algorithm to then begin to determin trends/patterns MGwhich trends or patterns will you be modelling? and begin either an indexing methodology MGwhich indexes are you considering: Unique index, primary index or foreign index? additional table/db creation process or both to further optimize the calls being made MGoptimize based on execution time or diskspace allocated, EliminatingFTS or some other criteria? MGhttps://dev.mysql.com/doc/refman/5.5/en/optimization.html and build in some internal levels of redundancy. MGwhat about replication MGhttp://dev.mysql.com/doc/refman/5.5/en/replication.html I am actually approaching this with some degree of biological conception in the multipathing within our own brains however until I have something up and running under some substantial load however I may not get a complete picture. Thanks, Garot Interesting Martin From: webmas...@lisol.co.uk webmas...@lisol.co.uk; To: 'Garot Conklin' garotconk...@yahoo.com; 'Martin Gainty' mgai...@hotmail.com; mysql@lists.mysql.com; Subject: RE: Machine Learning Sent: Mon, Aug 20, 2012 7:13:25 AM Hi Garot, This sounds an interesting idea. Are you looking to store all known commands and their options or are you looking for a 'formula' for calling any unix command ? The reason for my question is that, at the end of the day, a unix command is just a program that is run in the operating system. Each program comes with its own options and acceptable inputs. I don't know if there is a rule or convention for structuring these commands. Are you then looking to build a system that 'knows' all commands and 'how to' call them ? Thanks, Justin -Original Message- From: Garot Conklin [mailto:garotconk...@yahoo.com] Sent: 20 August 2012 03:39 To: Martin Gainty; mysql@lists.mysql.com Subject: Re: Machine Learning The initial goal is to provide a working framework from which to call all UNIX shell command combinations as the underlying storage mechanism for a machine learning algorithm. I would like to build a completely self aware instantiation that will maintain itself on all levels... I postulate that the first place to start would be in determining a method for maintaining all possible remediation combinations including the unknown to eventually be learned from and populate new knowledge into the database. Thank you for the reply, Garot
RE: Machine Learning
Hi Garot, You'll have to elaborate some more ... I understand you may want to protect the idea as well, so if you can narrow it down to some technical specifics then it'll help. What is the objective of this system, for instance ? Thanks, Justin From: Martin Gainty [mailto:mgai...@hotmail.com] Sent: 20 August 2012 19:23 To: garotconk...@yahoo.com; webmas...@lisol.co.uk; mysql@lists.mysql.com Subject: RE: Machine Learning From: garotconk...@yahoo.com Subject: Re: Machine Learning To: webmas...@lisol.co.uk; mgai...@hotmail.com; mysql@lists.mysql.com My initial thought was to propagate the db with everything and allow the algorithm to then begin to determin trends/patterns MGwhich trends or patterns will you be modelling? and begin either an indexing methodology MGwhich indexes are you considering: Unique index, primary index or foreign index? additional table/db creation process or both to further optimize the calls being made MGoptimize based on execution time or diskspace allocated, EliminatingFTS or some other criteria? MGhttps://dev.mysql.com/doc/refman/5.5/en/optimization.html and build in some internal levels of redundancy. MGwhat about replication MGhttp://dev.mysql.com/doc/refman/5.5/en/replication.html I am actually approaching this with some degree of biological conception in the multipathing within our own brains however until I have something up and running under some substantial load however I may not get a complete picture. Thanks, Garot Interesting Martin _ From: webmas...@lisol.co.uk webmas...@lisol.co.uk; To: 'Garot Conklin' garotconk...@yahoo.com; 'Martin Gainty' mgai...@hotmail.com; mysql@lists.mysql.com; Subject: RE: Machine Learning Sent: Mon, Aug 20, 2012 7:13:25 AM Hi Garot, This sounds an interesting idea. Are you looking to store all known commands and their options or are you looking for a 'formula' for calling any unix command ? The reason for my question is that, at the end of the day, a unix command is just a program that is run in the operating system. Each program comes with its own options and acceptable inputs. I don't know if there is a rule or convention for structuring these commands. Are you then looking to build a system that 'knows' all commands and 'how to' call them ? Thanks, Justin -Original Message- From: Garot Conklin [mailto:garotconk...@yahoo.com] Sent: 20 August 2012 03:39 To: Martin Gainty; mysql@lists.mysql.com Subject: Re: Machine Learning The initial goal is to provide a working framework from which to call all UNIX shell command combinations as the underlying storage mechanism for a machine learning algorithm. I would like to build a completely self aware instantiation that will maintain itself on all levels... I postulate that the first place to start would be in determining a method for maintaining all possible remediation combinations including the unknown to eventually be learned from and populate new knowledge into the database. Thank you for the reply, Garot
Re: Machine Learning
Ya the idea is not anything new, but must be apparently quit difficult or not a priority as I have yet to find it already implemented anywhere... Far be it from me to not make some attempt here anyway... I am creating a fully automated framework from which a distributed infrastructure can be maintained. I have been writing automation scripts/code for some time now and the logical progression is to embark on a full concept of systems health auto remediation. I have numerous monitoring solutions under my control however none that properly (in my opinion) implements any real learning algorithms from which to draw even a minimalists view of automation. I like mySQL therefor began thinking about creating the aspects (lobes) of the brain as a relational database(s). So this is only one facet of what I am trying to do, however leveraging a full command set of shell utilities/commands/programs seemed to be a good starting point before I get into the hard stuff ! -Garot garotconk...@yahoo.com From: webmas...@lisol.co.uk webmas...@lisol.co.uk To: 'Martin Gainty' mgai...@hotmail.com; garotconk...@yahoo.com; mysql@lists.mysql.com Sent: Monday, August 20, 2012 3:55 PM Subject: RE: Machine Learning Hi Garot, You'll have to elaborate some more ... I understand you may want to protect the idea as well, so if you can narrow it down to some technical specifics then it'll help. What is the objective of this system, for instance ? Thanks, Justin From: Martin Gainty [mailto:mgai...@hotmail.com] Sent: 20 August 2012 19:23 To: garotconk...@yahoo.com; webmas...@lisol.co.uk; mysql@lists.mysql.com Subject: RE: Machine Learning From: garotconk...@yahoo.com Subject: Re: Machine Learning To: webmas...@lisol.co.uk; mgai...@hotmail.com; mysql@lists.mysql.com My initial thought was to propagate the db with everything and allow the algorithm to then begin to determin trends/patterns MGwhich trends or patterns will you be modelling? and begin either an indexing methodology MGwhich indexes are you considering: Unique index, primary index or foreign index? additional table/db creation process or both to further optimize the calls being made MGoptimize based on execution time or diskspace allocated, EliminatingFTS or some other criteria? MGhttps://dev.mysql.com/doc/refman/5.5/en/optimization.html and build in some internal levels of redundancy. MGwhat about replication MGhttp://dev.mysql.com/doc/refman/5.5/en/replication.html I am actually approaching this with some degree of biological conception in the multipathing within our own brains however until I have something up and running under some substantial load however I may not get a complete picture. Thanks, Garot Interesting Martin _ From: webmas...@lisol.co.uk webmas...@lisol.co.uk; To: 'Garot Conklin' garotconk...@yahoo.com; 'Martin Gainty' mgai...@hotmail.com; mysql@lists.mysql.com; Subject: RE: Machine Learning Sent: Mon, Aug 20, 2012 7:13:25 AM Hi Garot, This sounds an interesting idea. Are you looking to store all known commands and their options or are you looking for a 'formula' for calling any unix command ? The reason for my question is that, at the end of the day, a unix command is just a program that is run in the operating system. Each program comes with its own options and acceptable inputs. I don't know if there is a rule or convention for structuring these commands. Are you then looking to build a system that 'knows' all commands and 'how to' call them ? Thanks, Justin -Original Message- From: Garot Conklin [mailto:garotconk...@yahoo.com] Sent: 20 August 2012 03:39 To: Martin Gainty; mysql@lists.mysql.com Subject: Re: Machine Learning The initial goal is to provide a working framework from which to call all UNIX shell command combinations as the underlying storage mechanism for a machine learning algorithm. I would like to build a completely self aware instantiation that will maintain itself on all levels... I postulate that the first place to start would be in determining a method for maintaining all possible remediation combinations including the unknown to eventually be learned from and populate new knowledge into the database. Thank you for the reply, Garot
RE: Machine Learning
When I hear 'AI' I always imagine theres a HAL 9001 behind the scenes that is running the show constantly admonishing its creator to take another stress pill Sounds like a fun project Keep us apprised, Martin Gainty __ Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen. Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le destinataire prévu, nous te demandons avec bonté que pour satisfaire informez l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est interdite. Ce message sert à l'information seulement et n'aura pas n'importe quel effet légalement obligatoire. Étant donné que les email peuvent facilement être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité pour le contenu fourni. Date: Mon, 20 Aug 2012 13:50:04 -0700 From: garotconk...@yahoo.com Subject: Re: Machine Learning To: webmas...@lisol.co.uk; mgai...@hotmail.com; mysql@lists.mysql.com Ya the idea is not anything new, but must be apparently quit difficult or not a priority as I have yet to find it already implemented anywhere... Far be it from me to not make some attempt here anyway... I am creating a fully automated framework from which a distributed infrastructure can be maintained. I have been writing automation scripts/code for some time now and the logical progression is to embark on a full concept of systems health auto remediation. I have numerous monitoring solutions under my control however none that properly (in my opinion) implements any real learning algorithms from which to draw even a minimalists view of automation. I like mySQL therefor began thinking about creating the aspects (lobes) of the brain as a relational database(s). So this is only one facet of what I am trying to do, however leveraging a full command set of shell utilities/commands/programs seemed to be a good starting point before I get into the hard stuff ! -Garot garotconk...@yahoo.com From: webmas...@lisol.co.uk webmas...@lisol.co.uk To: 'Martin Gainty' mgai...@hotmail.com; garotconk...@yahoo.com; mysql@lists.mysql.com Sent: Monday, August 20, 2012 3:55 PM Subject: RE: Machine Learning Hi Garot, You'll have to elaborate some more ... I understand you may want to protect the idea as well, so if you can narrow it down to some technical specifics then it'll help. What is the objective of this system, for instance ? Thanks, Justin From: Martin Gainty [mailto:mgai...@hotmail.com] Sent: 20 August 2012 19:23 To: garotconk...@yahoo.com; webmas...@lisol.co.uk; mysql@lists.mysql.com Subject: RE: Machine Learning From: garotconk...@yahoo.com Subject: Re: Machine Learning To: webmas...@lisol.co.uk; mgai...@hotmail.com; mysql@lists.mysql.com My initial thought was to propagate the db with everything and allow the algorithm to then begin to determin trends/patterns MGwhich trends or patterns will you be modelling? and begin either an indexing methodology MGwhich indexes are you considering: Unique index, primary index or foreign index? additional table/db creation process or both to further optimize the calls being made MGoptimize based on execution time or diskspace allocated, EliminatingFTS or some other criteria? MGhttps://dev.mysql.com/doc/refman/5.5/en/optimization.html and build in some internal levels of redundancy. MGwhat about replication MGhttp://dev.mysql.com/doc/refman/5.5/en/replication.html I am actually approaching this with some degree of biological conception in the multipathing within our own brains however until I have something up and running under some substantial load however I may not get a complete picture. Thanks, Garot Interesting Martin _ From: webmas...@lisol.co.uk webmas...@lisol.co.uk; To: 'Garot Conklin' garotconk...@yahoo.com; 'Martin Gainty' mgai...@hotmail.com; mysql@lists.mysql.com; Subject: RE: Machine Learning Sent: Mon, Aug 20, 2012 7:13:25 AM Hi Garot, This sounds an interesting idea. Are you looking to store all known commands and their options or are you looking for a 'formula' for calling any unix command ? The reason for my question is that, at the end of the day, a unix command is just a program that is run in the operating system. Each program comes with its own options and acceptable inputs. I don't know if there is a rule or convention for structuring these commands. Are you then looking to build a system that 'knows' all commands and 'how to' call them ? Thanks, Justin
RE: Best design for a table using variant data
Even if you get past those issues, you will get to other nasty problems... Ugly JOINs, terrible performance, huge disk footprint, etc. Split the attributes into two groups: * The ones you _really_ need to search on. * The rest. Build columns for the first group; use the appropriate datatypes. Throw the rest into a JSON string, put that into another column. (I like to compress the JSON and put it into a MEDIUMBLOB.) For searching,... Use SQL (WHERE ...) to filter on any attributes in the first group. Then use your programming language (PHP, etc) to finish the filtering, after fetching (and uncompressing) the JSON. -Original Message- From: Gaston Gloesener [mailto:gaston.gloese...@web.de] Sent: Sunday, August 12, 2012 10:58 PM To: mysql@lists.mysql.com Subject: RE: Best design for a table using variant data You don't specify how many different types (including min/max values) you expect to be using. If you expect to end up with a few hundred, then you should perhaps consider using an ENUM or SET column directly in the data table. I do not quite understand where the use of enums/sets could be help here. Regarding the number of basic types there are only expected to be a few similar to a programming language: At the beginning there will be 4 types (integer, 64-bit integer, floating point (probably double) and strings with their associated constraints. Each single attribute (i.e. table row) would only use one of these types. Possibly my explanations where not clear enough, so let me take a virtual example which would match my needs: A database which represents a second hand shop. This shop would contain a number of things of different types. Let's take cars and clothes as example. Those two would have a number off attributes which depend on the type. I.e. the car would have Brand (String), Model (String), manufacturing year (Inetger, max 2012), horsepower (integer), ... while some of these are meaningless for the clothes which will have size, ... These attributes are not known at development type and are to be defined by the users of the database and may be extended over time. As said above this is a virtual example which does not match the actual much more complex database use. / Gaston -Original Message- From: Carsten Pedersen [mailto:cars...@bitbybit.dk] Sent: Friday, August 10, 2012 15:33 To: Gaston Gloesener Cc: mysql@lists.mysql.com Subject: Re: Best design for a table using variant data You don't specify how many different types (including min/max values) you expect to be using. If you expect to end up with a few hundred, then you should perhaps consider using an ENUM or SET column directly in the data table. / Carsten On 10.08.2012 10:51, Gaston Gloesener wrote: Hello, I am currently facing a design where a table (virtually) needs to store attributes of a topic (related table). The attributes can be user defined, i.e. not known at development type and depend on other factors. Each attributes value can be one of different types (int, int64, double, string) and may have constraints like min, max or length (string). Thus the data type would be modeled as variant in some programming languages, but this is not an option in SQL (beside the MS SQLserver sql_variant extension). So, how to simulate this in SQL. Basically there would be one table describing the attributes type (Type identifier, min/max,.) and one table for the values itself. The design I am currently thinking of would be to make exactly these two tables, with the attributes having a Dataype column and iMin,iMax for integer, i64Min, i64Max, fMin,fMax for double , sMinLen, sMaxLen for strings. The same applies to the value table which will have iValue, i64Value, fValue, string columns to hold the actual data. Now the columns will be filled according to the data type, columns not matching the type will be NULL. This means that each row in the table will have virtual space for any data type which violates database normalization. However it seems to me to be the best deal for performance and data space as NULL takes virtually no room (4/8 bytes in total for a number of fields in some circumstances) and requires no complex queries. One could also imagine to have the constraints moved to a separate table and interpreted according to the data type. Also a table for each type could be imagined but this will make the queries very complicated working against performance. Note: The model has to work for huge databases Anybody has a better alternative ? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL
RE: MySQL LDAP Authentication Plugin - Full example with source code
Does that depend on mysql running as root in order to see /etc/shadow (or whatever)? If so, that is too big a security hole to do. -Original Message- From: Ignacio Ocampo [mailto:naf...@gmail.com] Sent: Sunday, August 12, 2012 3:54 PM To: mysql Subject: MySQL LDAP Authentication Plugin - Full example with source code I have published in my blog, an full example (with source code) of a LDAP Authentication plugin for MySQL. http://nafiux.com/blog/2012/08/11/mysql-ldap-authentication-plugin/ -- Ignacio Ocampo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: MySQL LDAP Authentication Plugin - Full example with source code
Hi Rick, The plugin does not need to access to /etc/passwd or /etc/shadow. It uses an LDAP Server to authenticate the user through ldap_simple_bind_s function. Best regards. On Mon, Aug 20, 2012 at 7:24 PM, Rick James rja...@yahoo-inc.com wrote: Does that depend on mysql running as root in order to see /etc/shadow (or whatever)? If so, that is too big a security hole to do. -Original Message- From: Ignacio Ocampo [mailto:naf...@gmail.com] Sent: Sunday, August 12, 2012 3:54 PM To: mysql Subject: MySQL LDAP Authentication Plugin - Full example with source code I have published in my blog, an full example (with source code) of a LDAP Authentication plugin for MySQL. http://nafiux.com/blog/2012/08/11/mysql-ldap-authentication-plugin/ -- Ignacio Ocampo -- Ignacio Ocampo Millán
