Re: Problem running multi master replication
Leonardus Setiabudi [EMAIL PROTECTED] wrote on 04/07/2006 05:33:50 AM: Hi All, I have a problem with my multi master replication plan. I have set my server (linux) to run 4 instance of mysqld, each with a different port, socket, tmpdir, log file, relay log file, master info and relay info. the illustration as follow : Server 1 (non slave) db1 db2 db3 db4 Server 2 (slave from other machine) db2 Server 3 (slave from other machine) db3 Server 4 (slave from other machine) db4 each slave run well and replicate successfully but the problem is, Server 1 unable to see the update from the other slave server, although i can see the update from each originating slave server. Is this a problem with mysql (locking issue?), or file system issue? I am using mysql 4.1.11 on mandrake linux, with ext3 fs and all the tables were MYISAM Server 1 was supposed to be somekind of data warehouse, to provide data from many distributed servers phisically separated. Would someone please help me, i have googled this and red the manual, but couldnt find similiar situation. TIA - Leo Just so that we are on the same page, let's review a few definitions as they relate to database replication: master - the server from which changes will propogate during replication. slave - the server to which replicated changes will be applied during replication. According to the rules of MySQL replication, as I understand them, each master can be a host to many slaves but each slave can only receive changes from a single master. You said: each slave run well and replicate successfully I assume you mean servers 2-4? but the problem is, Server 1 unable to see the update from the other slave server, although i can see the update from each originating slave other slave server? I don't quite understand. If Server 1 is supposed to be receiving updates then it is the slave and the process it is getting the changes from is called the master. A single server process can actually have both roles as in this situation: Server A - replicates to - Server B - replicates to - Server C In this case A is the master of B. B is a slave of A. B is also a master to C which is the slave of B. Multi-master replication would be something like this: Server A - Server C Server B - Server C In this plan, Server C would be recieving changes from both A and B. However the current design of MySQL limits you to having only *ONE* master per slave for many excellent reasons (search the recent archives of this list for a few). Can you possibly re-explain your situation using the terms master and slave as I just used them? Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Reserevd Error -7776 -- Urgent
C K [EMAIL PROTECTED] wrote on 04/07/2006 01:35:22 PM: Dear Friends, I have linked tables from MySQL through ODBC DSN in Access 2003. when I go for updating any record in any linked table then it gives me error as follows: Reserved Error (-7776); there is no message for this error. What will be the problem? Please help. Is this error from MS ACCESS or for MySQL. Thanks Regards, CPK Have you read through this: http://dev.mysql.com/doc/refman/4.1/en/msaccess.html or this: http://dev.mysql.com/doc/refman/4.1/en/myodbc-with-vb.html or checked here: http://msdn.microsoft.com/archive/default.asp?url=/archive/en-us/dnaraccessdev/html/ODC_JetDatabaseEngineVersion30ODBCConnectivity.asp (quoted) -7776 SQLGetData(SQL_C_TIMESTAMP) Illegal date/time value returned. My quick suggestion: Make sure that all of your timestamp and datetime fields are within their expected ranges. Sorry I can't be more helpful but it's a busy day here Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Reserevd Error -7776 -- Urgent
C K [EMAIL PROTECTED] wrote on 04/07/2006 01:59:39 PM: On 4/7/06, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: C K [EMAIL PROTECTED] wrote on 04/07/2006 01:35:22 PM: Dear Friends, I have linked tables from MySQL through ODBC DSN in Access 2003. when I go for updating any record in any linked table then it gives me error as follows: Reserved Error (-7776); there is no message for this error. What will be the problem? Please help. Is this error from MS ACCESS or for MySQL. Thanks Regards, CPK Have you read through this: http://dev.mysql.com/doc/refman/4.1/en/msaccess.html or this: http://dev.mysql.com/doc/refman/4.1/en/myodbc-with-vb.html or checked here: http://msdn.microsoft.com/archive/default.asp?url=/archive/en- us/dnaraccessdev/html/ODC_JetDatabaseEngineVersion30ODBCConnectivity.asp (quoted) -7776 SQLGetData(SQL_C_TIMESTAMP) Illegal date/time value returned. My quick suggestion: Make sure that all of your timestamp and datetime fields are within their expected ranges. I am using MySQL server 5.0.17 and ODBC 3.51.12 for ODBC connection. When I update an y data from MySQL Administrator or SQLYog it gets updated. I see your point? That must be why I *didn't* give you links that document working with MySQL with MySQL Administor OR SQLYog. I gave you links to using MS Access and ODBC with MySQL. Those pages (and the ones they link to) contain valuable information to help you learn how limited those products are compared with MySQL and how you need to adjust your data so that it fits within the boundaries that MS sets for *their* data manipulation tools. The short explanation is that MySQL has more range and capacity than ODBC and Access can handle and you have to work within the MS limits if you want to use with those tools. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Many Attributes Required Design Query
Mark Sargent [EMAIL PROTECTED] wrote on 04/06/2006 10:45:43 PM: [EMAIL PROTECTED] wrote: I agree with the basic design: one table for all of your basic objects (shirts, pants, coats, shoes, etc), one table for all of your attributes (see Barry's response), a sku table equating objects (differentiated by their attributes) and their inventory quantities (on hand, backordered, etc), and one more to relate SKU to all applicable attributes. Hi All, Shawn, what is a SKU? I can't explain it any better than this: http://en.wikipedia.org/wiki/Stock_Keeping_Unit To borrow an analogy from biology SKU relates to model as species relates to genus. Each SKU uniquely identifies a variation of a basic object. Those variations can be due to size changes, colorations, decorations, or style. One model number may have dozens of SKUs associated with it. Each SKU number is used to track how many of each size/style/etc is in stock or on order. In the grocery store, SKU numbers are the barcodes on the labels. Del Monte makes several sizes of canned whole tomatoes (a product). They pack those in different sizes of cans. Each can gets its own barcode (SKU) so that the store can assign the correct price during checkout. These barcodes are also used for inventory control (Imagine the thought process of the manager We have 6 cases of #10 cans but we are down to only 2 case of 12oz cans. We probably need to order more 12oz cans.) You probably need to be able to provide the same level of detail in your inventory control system. Each SKU represents one combination of a base object with a particular set of attributes. IT's the SKU number that important for inventory control and that will uniquely identify a size 8 pair of jeans from a size 9 pair or a pair of black size 8s from a pair of red size 8s all in the same style (cut) from the same manufacturer Makers: maker_id maker_desc Products: prod_id prod_code maker_id prod_desc attri_id object_id Attributes: attri_id attri_desc Objects: object_id object_desc That is where I got to, as I've never done this kinda design before. Thrown into the deep end, I guess. May I ask for more assistance with this? Where does the quantity go? Any tutorials on this kind of design? Cheers. Mark Sargent. I dont know about tutorials but I Googled SKU and got appx 88 million hits. I also Googled for inventory control schema and got over 900 thousand hits. Some of them may give you some ideas of what your database needs to track or how to organize your tables. Most user mangement front-ends hide a lot of the complexity that goes into a database design like this. Make sure you can store and retrieve the information you will need in order to answer the questions your users want to be able to ask your system. I know that sounds circuituous but if you know what your customers want to know, you can create what they need to be able to know it. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Many Attributes Required Design Query
Barry [EMAIL PROTECTED] wrote on 04/06/2006 03:52:53 AM: Mark Sargent wrote: Hi All, I have the job of creating a DB for an online clothing company. I have some experience with relational design and sql and php. I'm stumped as they have products that contain numerous attributes, and are wishing to get away, if possible, with entering separate records for each and every product that has different ones. I will list below some example products and their attributes. Shirts: neck body_length shoulder_length chest waist sleeve cuff color Pants: waist under_crotch_length upper_crotch_length upper_crotch_back_length upper_leg_length lower_leg_length Jacket: body_length shoulder_length chest waist sleeve Belts: body_length body_width waist_length buckle_length buckle_length holes sleeve_length below is a link to what they have on Ebay at the moment, http://cgi.ebay.com/DOLCE-GABBANA-PEACE- Jeans-48_W0QQitemZ7757125046QQcategoryZ11483QQtcZphotoQQcmdZViewItem keep in mind, I have only listed attributes above, not the actual table designs. They deal with a lot of variety within brands etc. Anyone got any solutions for this? I haven't started on a basic design yet, but am looking to do that soon. I f i can minimize their record input, that'd be great Cheers. Mark. Uhm. My solution would be 3 Databases where one has ID,Attrib_object_id, Attrib_name_id, Attrib_value And the other two would be an attrib database and an object databse. Yep, something like that. I think you meant to type tables not databases - :-0 But we knew what you meant... ;-) -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) I agree with the basic design: one table for all of your basic objects (shirts, pants, coats, shoes, etc), one table for all of your attributes (see Barry's response), a sku table equating objects (differentiated by their attributes) and their inventory quantities (on hand, backordered, etc), and one more to relate SKU to all applicable attributes. Each SKU represents one combination of a base object with a particular set of attributes. IT's the SKU number that important for inventory control and that will uniquely identify a size 8 pair of jeans from a size 9 pair or a pair of black size 8s from a pair of red size 8s all in the same style (cut) from the same manufacturer It's a time-tested inventory control model used by all but the smallest of retailers. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Key and Primary Key
news [EMAIL PROTECTED] wrote on 04/06/2006 09:39:33 AM: IF I have a table like this: id int not null, field2 int not null, .., primary key (id), key (field2) ) ENGINE=MyISAM; The primary key is id only or (id, field2)? If this is the case which constraint are aplied on field2? -- Three words describe our society:homo homini lupus You declared that the primary key is only on the field (ID). There are no other fields involved. You also declared that the engine should create and maintain a separate index on the field (field2). There are no contstraints built into a regular index. Does that help? Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Password expire?
NiCK Song [EMAIL PROTECTED] wrote on 04/04/2006 11:05:57 PM: Hi, experts How can I make mysql database users password with expire date? Does mysql can do it? -- NiCK Sorry!! MySQL does not auto-expire any user accounts. You will need to script something to do that manually on a schedule you want to set. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Select Sum with union, tricky question perhaps not for you
H L [EMAIL PROTECTED] wrote on 04/03/2006 01:53:37 PM: The solution is to redesign your tables. You need to split into separate columns the values you want to maintain. You do not want to keep the flat file design you are currently trying to use. CREATE TABLE calendar ( objectid, year, dayofyear, ... other fields... ) CREATE TABLE price ( objectid, year, dayofyear, price ) Having a separate column for each day of the year may make sense to a person but as you have discovered, it is extremely difficult to use for any kind of ad-hoc querying. A more normalized data structure will be almost as efficient in space usage but 1000s of times more efficient for querying. There is no simple way to write a query that spans years with the table structures you currently have. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Hi thanks for the quick response! Well i think you are right about this. I will probably have to redesign the database. What do you think of using DATE instead of year,dayofyear or is there problems i could get into then. It would be great to be able to use select from price where date=somedate AND date = tosomedate /Henrik You could use a single date column for (year, Julian date) but if you are constantly querying on the Julian date (based on your application or other needs) then having that column would be a good thing. Without a column for Julian date, you could not index that value either by itself or as part of another index. Which way works better for you really depends on *your* application's needs and I cannot guess all of them. However, you really should normalize that data first. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: better way of doing 1800 sequential updates?
Ariel Sánchez Mora [EMAIL PROTECTED] wrote on 04/03/2006 09:07:34 PM: This table holds latest data from an app: mysql select * from ultimas_respuestas_snmp limit 10; +++--- +-+---++--+ | id | info_oficina | columna_donde_guardar | info_interfaz | valorSNMP | nombre_dns | hora | +++--- +-+---++--+ | 0 | Sucursal Canas | USO_CPU_1min | | error | canas | 18:49:53 | | 1 | Sucursal Canas | RAM_LIBRE | | error | canas | 18:49:54 | | 2 | Sucursal Canas | ESTADO_ADMIN_1| TDM 195-2883 ICE | 1 | canas | 18:49:55 | | 3 | Sucursal Canas | ESTADO_ADMIN_2| RDSI 669-9010 ICE | error | canas | 18:49:56 | | 4 | Sucursal Canas | ESTADO_ADMIN_3| RDSI_doble 669-9010 ICE | error | canas | 18:49:57 | | 5 | Sucursal Canas | ESTADO_PROTOCOLO_1| TDM 195-2883 ICE | error | canas | 18:49:58 | | 6 | Sucursal Canas | ESTADO_PROTOCOLO_2| RDSI 669-9010 ICE | error | canas | 18:49:59 | | 7 | Sucursal Canas | ESTADO_PROTOCOLO_3| RDSI_doble 669-9010 ICE | 5 | canas | 18:50:00 | | 8 | Sucursal Canas | BW_ENTRADA_1 | TDM 195-2883 ICE | error | canas | 18:50:01 | | 9 | Sucursal Canas | BW_ENTRADA_2 | RDSI 669-9010 ICE | error | canas | 18:50:02 | +++--- +-+---++--+ 10 rows in set (0.00 sec) without the limit 10 1780 rows in set (0.03 sec) the create table: ultimas_respuestas_snmp CREATE TABLE `ultimas_respuestas_snmp` ( `id` int(4) NOT NULL default '0', `info_oficina` varchar(35) default NULL, `columna_donde_guardar` varchar(30) default NULL, `info_interfaz` varchar(30) default NULL, `valorSNMP` varchar(12) default NULL, `nombre_dns` varchar(20) default NULL, `hora` varchar(10) default NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 I cannot use indexes because my dbexpress driver doesn't support it (long story short, I'll change it in the next version). Now, I have to make a lot of sequential updates, like UPDATE ultimas_respuestas_SNMP SET valorSNMP =1, hora =18:47:21 WHERE id =0; UPDATE ultimas_respuestas_SNMP SET valorSNMP =10, hora =18:47:22 WHERE id =1; UPDATE ultimas_respuestas_SNMP SET valorSNMP =1, hora =18:47:22 WHERE id =2; UPDATE ultimas_respuestas_SNMP SET valorSNMP =1, hora =18:47:22 WHERE id =3; UPDATE ultimas_respuestas_SNMP SET valorSNMP =1, hora =18:47:23 WHERE id =4; UPDATE ultimas_respuestas_SNMP SET valorSNMP =1, hora =18:47:23 WHERE id =5; UPDATE ultimas_respuestas_SNMP SET valorSNMP =5, hora =18:47:24 WHERE id =6; UPDATE ultimas_respuestas_SNMP SET valorSNMP =5, hora =18:47:24 WHERE id =7; UPDATE ultimas_respuestas_SNMP SET valorSNMP =7000, hora =18:47: 24 WHERE id =8; . . . UPDATE ultimas_respuestas_SNMP SET valorSNMP =0, hora =18:48:38 WHERE id =1778; UPDATE ultimas_respuestas_SNMP SET valorSNMP =, hora =18:48:38 WHERE id =1779; This makes my server CPU load top 100% for about 1:20 s. First question: is update the best command for this? I've seen replace that might work too; has anyone played around with something like this before? Second: is there a better way of formulating the update command, for this sequence?? Perhaps one that takes advantage of the sequential inserts? TIA, all comments welcome. I am a newbie by the way, trying to optimize my first MySQL related program. Ariel Yes, there is a way to make this go MUCH faster. Assuming you followed the advice of the previous responses and added an INDEX to your ID column on ultimas_respuestas_SNMP. Indexes are used internally to MySQL, the fact that you are using dbexpress has no bearing on good database design. If you want, or in this case *NEED* an index, add it. MySQL deals with those, not your connection library. start script CREATE TEMPORARY TABLE bulkUpdate ( id int not null, newHora varchar(10), newSNMP varchar(12), PRIMARY KEY (id) ) INSERT bulkUpdate (id, newHora, newSNMP) VALUES (0,'18:47:21','1'),(1,'18:47:22','10'), ... the rest of the 1800 rows of changes you want to make ...; UPDATE ultimas_respuestas_SNMP ur INNER JOIN bulkUpdate bu ON bu.id = ur.id SET ur.hora = bu.newHora, ur.valorSNMP = bu.newSNMP; DROP TEMPORARY TABLE bulkUpdate; end script This works faster for several reasons: a) There is an index on the column you are using most often for your lookups (see previous posts) b) You are asking the parser to
Re: Need for distinct sum
Yasir Assam [EMAIL PROTECTED] wrote on 04/03/2006 11:09:01 PM:
Hello,
I need to be able to sum over distinct values but I can't seem to do it
unless I use sub-selects (which I want to avoid doing).
To see what I mean, I've constructed a toy DB:
DROP TABLE IF EXISTS spell;
CREATE TABLE spell (
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
power FLOAT,
time_casted DATETIME
);
DROP TABLE IF EXISTS wizard;
CREATE TABLE wizard (
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
age INT UNSIGNED,
name VARCHAR(255)
);
DROP TABLE IF EXISTS spellcast;
CREATE TABLE spellcast (
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
type ENUM ('fire', 'air', 'water', 'earth'),
spell_id INT UNSIGNED,
wizard_id INT UNSIGNED
);
INSERT INTO wizard (name, age) VALUES
('alan', 20),
('bill', 23),
('chris', 21);
INSERT INTO spell (power, time_casted) VALUES
(400, '2006-02-02 12:00'),
(432, '2006-02-04 12:00'),
(123, '2006-02-03 12:00'),
(543, '2006-02-08 12:00'),
(320, '2006-02-01 12:00'),
(102, '2006-02-12 12:00'),
(732, '2006-02-14 12:00'),
(948, '2006-02-18 12:00'),
(932, '2006-02-21 12:00'),
(842, '2006-02-26 12:00');
INSERT INTO spellcast (type, spell_id, wizard_id) VALUES
('fire', 1, 1),
('air', 1, 1),
('water', 1, 1),
('earth', 2, 1),
('water', 2, 1),
('fire', 3, 1),
('water', 3, 1),
('water', 4, 1),
('fire', 4, 1),
('air', 5, 1),
('fire', 6, 1),
('water', 7, 1),
('water', 1, 2),
('fire', 1, 2),
('air', 2, 2),
('earth', 3, 2),
('water', 3, 2),
('earth', 4, 2),
('fire', 4, 2),
('air', 4, 2),
('water', 1, 3),
('earth', 1, 3),
('air', 1, 3),
('water', 5, 3),
('fire', 5, 3),
('earth', 5, 3),
('water', 6, 3),
('air', 7, 3);
A spell is an individual spell that's been cast. A spellcast is the
action of casting the spell by a particular wizard (or a group of
wizards). When casting a spell, a wizard can contribute various essenses
(fire, earth, air, water). So for example, Alan cast a spell (id=1) and
contributed three essences (fire, air water) - this means that there
are 3 spellcast rows for this contribution to this spell.
Let's say I want to find the total power of all the spells cast by each
wizard that involve fire air. At first I thought the following might
work:
SELECT wizard.name,
SUM(spell.power)
FROM spell,
spellcast,
wizard
WHERE wizard.id = spellcast.wizard_id AND
spellcast.spell_id = spell.id AND
spellcast.type IN ('fire', 'air')
GROUP BY wizard.id;
But this is wrong. The above query will count some spells more than
once, so the resulting sum is greater than it should be.
The only way I can think of doing this correctly is to use sub-queries:
SELECT DISTINCT wizard.name,
sub.s
FROM
(SELECT SUM(inner_sub.power) AS s,
inner_sub.wiz_id
FROM
(SELECT DISTINCT
spell.id,
spell.power,
wizard.id AS wiz_id
FROM spell,
spellcast,
wizard
WHERE wizard.id = spellcast.wizard_id AND
spellcast.spell_id = spell.id AND
spellcast.type IN ('fire', 'air')
) AS inner_sub
GROUP BY inner_sub.wiz_id
) AS sub,
wizard,
spellcast
WHERE wizard.id = sub.wiz_id AND
spellcast.wizard_id = wizard.id AND
spellcast.type IN ('fire', 'air');
This works but I was wondering whether there was a simpler way to do it.
All my queries are generated dynamically, and I want to avoid generating
complex subqueries.
Anyone know of a way to do the above a lot more simply? I can change the
DB schema if needs be.
Thanks,
Yasir
Based on database theory, your schema is correct (so long as each spell
only consumes up to 1 unit of essence). As you have discovered, you are
trying to take a second-level summary within a single statement. While the
COUNT() aggregate function has a DISTINCT modifier none of the others do.
That is why you posted.
One technique you could try is to create an intermediate pivot table. That
way you can know how much of each essence has been used in each spell.
CREATE TEMPORARY TABLE tmpSpellSummary (PRIMARY KEY (wizard_id,spell_id))
SELECT
wizard_id,
spell_id,
SUM(if(type='AIR',1,0)) air,
SUM(if(type='EARTH',1,0)) earth,
SUM(if(type='FIRE',1,0)) fire,
SUM(if(type='WATER',1,0)) water
FROM spellcast
GROUP BY wizard_id, spell_id
Now you can join this pivot table to your other query and you won't have
the duplication. You can also modify this by adding (after the GROUP BY
clause):
HAVING air0 and water0
to pick out just those spell_id's that used both air and water.
Another technique you could try (still assuming that using an essence only
consumes 1 unit of it) is to add an essence bitmap value to your spell
table
Re: link MS Access to MySQL?
Bing Du [EMAIL PROTECTED] wrote on 04/04/2006 12:20:23 PM: Hello everyone, Please correct me if my understanding is wrong. Does the implementation of linked table mentioned on 18.1.11.3. How to Import or Link MySQL Database Tables to Access? (http://dev.mysql.com/doc/refman/4.1/en/import-of-data.html) means that data is actually stored in MySQL and tables in Access are links pointing to tables in MySQL? What I want is do linked table the other way, which is store data in Access and make tables in MySQL links that point to tables in Access? Is it possible and how? Thanks in advance, Bing The only way to link tables in MySQL (to use the term from Access) is to use the Federated database engine. A Federated table physically exists on one server but virtually exists on another. This is exactly like creating a linked table. However, AFAIK, the Federated database engine only supports direct MySQL to MySQL sharing. It is not ready to do MySQL to any other server yet. http://dev.mysql.com/doc/refman/5.0/en/federated-description.html http://dev.mysql.com/doc/refman/5.0/en/federated-limitations.html Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Days in Month
Mike Blezien [EMAIL PROTECTED] wrote on 04/04/2006 02:47:50 PM: Hello, is there a MySQL function to determine the total days of any given month, IE if current month is April, is there a function or query to find the total days of the month? TIA, Mike(mickalo)Blezien === Thunder Rain Internet Publishing Providing Internet Solution that Work === Well, the first place I would have looked is in the manual... http://dev.mysql.com/doc/refman/4.1/en/index.html Having skimmed through it at least once (something I recommend that everyone does), I know there is a section describing all of the functions that work with date and time values... http://dev.mysql.com/doc/refman/4.1/en/date-and-time-functions.html where I found a function to return exactly what you were looking for... http://dev.mysql.com/doc/refman/4.1/en/date-and-time-functions.html#id2691432 Respectfully, Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: very long query for such a simple result
Ed Reed [EMAIL PROTECTED] wrote on 04/04/2006 04:34:29 PM:
Can someone help me simplify this query please? It's meant to return
a single string result that looks something like this,
You have 12 open Problem Reports: Priorities(High=5, Med=6, Low=1)
The relavent columns from the two tables are
Table: ProblemReports
Fields: PRNo, Status, Priority, Responsible
Table: Employees
Fields: Firstname, Lastname, DateTerminated, UserName
Here's the query
Select IF((@a:=(SELECT Count(PRNo) From ProblemReports, Employees
WHERE (ProblemReports.Status='Open') AND
(ProblemReports.Responsible=ConCat(Employees.FirstName, ' ',
Employees.LastName)) AND (Employees.DateTerminated Is Null) AND
(Employees.UserName='User1')))0,
If (@a=1, ConCat('You have one open Problem Report: Prioritiy = ',
(Select Priority From ProblemReports, Employees
WHERE (ProblemReports.Status='Open') AND (ProblemReports.
Responsible=ConCat(Employees.FirstName, ' ',Employees.LastName)) AND
(Employees.DateTerminated Is Null) AND (Employees.UserName='User1'))),
Concat('You have ', @a, ' open Problem Reports: Priorities(',
(Select ConCat('High=',Count(Priority)) From ProblemReports, Employees
WHERE (ProblemReports.Status='Open') AND (ProblemReports.
Responsible=ConCat(Employees.FirstName, ' ',Employees.LastName)) AND
(Employees.DateTerminated Is Null) AND (Employees.UserName='User1')
And (Priority='High')),', ',
(Select ConCat('Med=',Count(Priority)) From ProblemReports, Employees
WHERE (ProblemReports.Status='Open') AND (ProblemReports.
Responsible=ConCat(Employees.FirstName, ' ',Employees.LastName)) AND
(Employees.DateTerminated Is Null) AND (Employees.UserName='User1')
And (Priority='Med')),', ',
(Select ConCat('Low=',Count(Priority)) From ProblemReports, Employees
WHERE (ProblemReports.Status='Open') AND (ProblemReports.
Responsible=ConCat(Employees.FirstName, ' ',Employees.LastName)) AND
(Employees.DateTerminated Is Null) AND (Employees.UserName='User1')
And (Priority='Low')),')')),'');
You are new at this, aren't you? First thing you need to do is to let us
see exactly what we need to deal with. Please respond with the results of
the following two commands
SHOW CREATE TABLE Employees\G
SHOW CREATE TABLE ProblemReports\G
We (the list members) will be able to help straighten you out from there
(there are lots of things we need to talk about but I think that we should
get you working first, ok?)
Always CC the list (or hit the REPLY TO ALL button or whatever you have in
your email client). That way everyone on the list stays informed of the
progress of this issue.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Re: Select Sum with union, tricky question perhaps not for you
H L [EMAIL PROTECTED] wrote on 04/02/2006 11:51:48 AM: Hi, i have a problem to select sum from same table using UNION. The key question is there a way of combining two questions in one so the resulting objectid in query1 is only used in query 2 in a smart way. Eg. if only one object is avaliable in a search first year then only check that objectid for next year and append the sum in the question. I have 4 tables companyobjects that contains the key (companyid,objectid,name etc) and i have calendar the table looks similar to this (objectid,year,day1,day2.day365) price that looks similar to this (objectid,year,day1,day2.day365) reservation that looks similar to this (objectid,year,day1,day2.day365) I have tried as follows below, as you can see i want to calculate price discount and amount to pay from same table but from 2 years. I have tried removed all my where clause below and the result is not correct it is not from both querys. Maybe you know an easier way, totaly diffrent way that i have not thought of perhaps. Thanks in advance /Henrik SELECT `companyobjects`.`objectid`, Sum(`objectprice`.`d362` +`objectprice`.`d363` +`objectprice`.`d364`+`objectprice`.`d365`), Sum((`objectprice`.`d362` +`objectprice`.`d363` +`objectprice`.`d364`+`objectprice`.`d365`)*0.1), Sum((`objectprice`.`d362` +`objectprice`.`d363` +`objectprice`.`d364`+`objectprice`.`d365`)*0.9) FROM `companyobjects` Inner Join `objectprice` ON `companyobjects`.`objectid` = `objectprice`.`objectid` Inner Join `objectreservation` ON `companyobjects`.`objectid` = `objectreservation`.`objectid` Inner Join `objectcalendar` ON `companyobjects`.`objectid` = `objectcalendar`.`objectid` WHERE `companyobjects`.`companyid` = 'C050319112022656' AND `companyobjects`.`maxnrofguests` = '1' AND `objectprice`.`year` = '2006' AND `objectreservation`.`year` = '2006' AND `objectcalendar`.`year` = '2006' GROUP BY `companyobjects`.`objectid` UNION ALL SELECT `companyobjects`.`objectid`, Sum(`objectprice`.`d1` +`objectprice`.`d2`), Sum((`objectprice`.`d1` +`objectprice`.`d2`)*0.1), Sum((`objectprice`.`d1` +`objectprice`.`d2`)*0.9) FROM `companyobjects` Inner Join `objectprice` ON `companyobjects`.`objectid` = `objectprice`.`objectid` Inner Join `objectreservation` ON `companyobjects`.`objectid` = `objectreservation`.`objectid` Inner Join `objectcalendar` ON `companyobjects`.`objectid` = `objectcalendar`.`objectid` WHERE `companyobjects`.`companyid` = 'C050319112022656' AND `companyobjects`.`maxnrofguests` = '1' AND `objectprice`.`year` = '2007' AND `objectreservation`.`year` = '2007' AND `objectcalendar`.`year` = '2007' GROUP BY `companyobjects`.`objectid` I _ Hitta rätt på nätet med MSN Search http://search.msn.se/ The solution is to redesign your tables. You need to split into separate columns the values you want to maintain. You do not want to keep the flat file design you are currently trying to use. CREATE TABLE calendar ( objectid, year, dayofyear, ... other fields... ) CREATE TABLE price ( objectid, year, dayofyear, price ) Having a separate column for each day of the year may make sense to a person but as you have discovered, it is extremely difficult to use for any kind of ad-hoc querying. A more normalized data structure will be almost as efficient in space usage but 1000s of times more efficient for querying. There is no simple way to write a query that spans years with the table structures you currently have. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: stunningly slow query
[EMAIL PROTECTED] wrote on 04/02/2006 05:35:59 PM: snip Can you post your show create table tbl_name statement for these tables that involve slow queries? | old_crumb |CREATE TABLE `old_crumb` ( `link_ID` bigint(20) default NULL, `dir_Travel` char(1) default NULL, `customer_ID` int(11) NOT NULL default '0', `source_ID` int(11) NOT NULL default '0', `vehicle_ID` int(11) NOT NULL default '0', `actual_Time` datetime NOT NULL default '-00-00 00:00:00', snip `last_Modified` datetime default NULL, PRIMARY KEY (`customer_ID`,`source_ID`,`vehicle_ID`,`actual_Time`), KEY `old_crumb_ix_reported_Time` (`reported_Time`), KEY `old_crumb_ix_link_ID` (`link_ID`,`dir_Travel`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 MAX_ROWS=10 COMMENT='List of breadcrumbs already rolled up.' INDEX DIRECTORY='/var/mysql_idx/trimble/' | I'm no DB expert Chris but do you really need to create a primary key index over 4 columns? What about something simple and possibly faster like adding a seperate ID primary key column to the table like: | old_crumb |CREATE TABLE `old_crumb` ( `ID` int unsigned not null auto_increment `link_ID` bigint(20) default NULL, `dir_Travel` char(1) default NULL, `customer_ID` int(11) NOT NULL default '0', `source_ID` int(11) NOT NULL default '0', `vehicle_ID` int(11) NOT NULL default '0', snip PRIMARY KEY (`ID`), snip An unsigned int will take an extra 4 bytes of storage space per row, and will give you an index range of 0 - 4294967295. If that is not enough range, an unsigned bigint will take an extra 8 bytes of storage space, and will give you an index range of 0 - 18446744073709551615. Although this will increase the amount of storage space required in the .MYD file, it may also decrease the amount of space required in the .MYI index file, as you would not be needing to store multi-column indexes. Keith, Your method won't guarantee that there are no rows where the combination of the values in those four columns fails to repeat in any other row. To do that would require an EXTRA four-column unique index of type UNIQUE. Your proposal would actually make the situation worse as now there would be two indexes to maintain to achieve the same effect as the previous single PK. This is the other link_area | CREATE TABLE `link_area` ( `link_ID` bigint(20) NOT NULL default '0', `dir_Travel` char(1) NOT NULL default '', `area_ID` int(11) NOT NULL default '0', PRIMARY KEY (`link_ID`,`dir_Travel`), KEY `link_area_ix_area_ID` (`area_ID`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='Link area table' INDEX DIRECTORY='/var/mysql_idx/landsonar/' | Inserts into the link_area were going very very slowly while data was being moved into old_crumb. old_crumb is large - my suspicion at this point is that the process of looking for key conflicts was slowing things down and starving other query traffic. The same could be applied to the link_area table: Do you need dir_travel as part of the primary key? If dir_travel is part of what makes each row different than every other row, then YES he needs that column as part of his primary key. link_area | CREATE TABLE `link_area` ( `link_ID` bigint(20) NOT NULL default '0', `dir_Travel` char(1) NOT NULL default '', `area_ID` int(11) NOT NULL default '0', PRIMARY KEY (`link_ID`), KEY `link_area_ix_area_ID` (`area_ID`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='Link area table' INDEX DI$ Regards Keith Your suggestions were well intended. However, this seems to me that his key caches are just not large enough to keep the whole key in memory. It may be possible for him to maintain a smaller current or daily table that is then batch merged into the larger historic copy of his old-crum table. I believe he is correct in guessing that his insert traffic to old_crum is interfering with the inserts into link_area and that the most likely cause is the need to both validate the new rows against the PK and add the new rows into the PK. In the spirit of Keith's suggestion, is there any reason why you cannot make a hash or lookup table of all of your (`customer_ID`,`source_ID`,`vehicle_ID`) triplets and replace those columns in old_crum (and it's PK) with the single value? That way you don't lose your row uniqueness but gain space in your PK. You could also reduce your actual_time column to an integer value (instead of a date value) so that you are comparing against a numeric value when you compare against the PK? When you are dealing with 10s of millions of rows like you are, these little changes can make some big differences. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Delete Duplicates
Rich [EMAIL PROTECTED] wrote on 03/30/2006 09:11:56 PM: Hi there. Any quick way of killing duplicate records? Cheers Yes. Some ways involve subqueries, others temporary tables. What version are you on? What are your table definition(s) (use SHOW CREATE TABLE to dump the defs)? How do your define duplicate for the table(s) you are having problems with? More details will get you better answers ;-) Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Compound Insert Statement
Sheeri is correct. Rich's statement should have worked. What Rich is
looking for is the syntax for doing what the manual calls extended
inserts.
quoting TFM (http://dev.mysql.com/doc/refman/5.0/en/insert.html)
INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name [(col_name,...)]
VALUES ({expr | DEFAULT},...),(...),...
[ ON DUPLICATE KEY UPDATE col_name=expr, ... ]
That means that his 4 statements could be rewritten as :
INSERT INTO mytable (myfield) VALUES ('alpha'), ('bravo'), ('charlie'),
('delta');
Which is what he posted as his desired syntax. What puzzles me is if he
got it right the first time, why did he post the question?
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
sheeri kritzer [EMAIL PROTECTED] wrote on 03/30/2006 12:41:01 AM:
That is, in fact, the exact correct syntax. What error are you
getting when you try to run that on the commandline? What version of
MySQL are you using?
-Sheeri
On 3/29/06, Rich [EMAIL PROTECTED] wrote:
Hi folks. I come to the list with another compound question.
My middleware allows me to build any syntax for the actual sql
statement, so I'm trying to minimize the work done to insert several
records at one try. I currently have multiple insert statements, but
can't find any reference to multiple records added using one insert
statement. I now have:
-SQL = INSERT INTO mytable (myfield) VALUES ('alpha');INSERT INTO
mytable (myfield) VALUES ('bravo');INSERT INTO mytable (myfield)
VALUES
('charlie');INSERT INTO mytable (myfield) VALUES ('delta');
What I would like to find is this:
-SQL = INSERT INTO mytable (myfield) VALUES ('alpha'), ('bravo'),
('charlie'), ('delta');
I am either looking in the wrong place in Dubois (Third) or it can't
be
done.
Any recommendations?
Appreciate it.
Cheers
RE: Expiration date on users utilizing freeradius and mysql
We got the question. However what you ask isn't purely a database issue. How does your authentication program (freeradius?) handle expiration dates? If it doesn't then adding those to the database won't help a bit. If it does, then there should already be a date column. (or two) in the appropriate table(s). Either way, the change(s) you want to make are more in the realm of programming than database administration. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Atkins, Dwane P [EMAIL PROTECTED] wrote on 03/30/2006 09:17:26 AM: I submitted this yesterday and was not sure if maybe it did not get out to folks. How would I put an expiration date on a mysql field so that it would match a radius entry? Also, is there a way that I can call up a web based screen and have all the information at my fingertips for inputting user data? Thanks Dwane -Original Message- From: Atkins, Dwane P [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 29, 2006 10:59 AM To: mysql@lists.mysql.com Subject: Expiration date on users utilizing freeradius and mysql I am using freeradius with MySql and what I would like to do is create in my radius table an user with attributes stating a start and stop date. I would like to be able to do a bulk entry (more than 1 at a time) or would love for this to be web based. Is this process out there? Thanks Dwane -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Weird M$ Pasting issue
Vinny [EMAIL PROTECTED] wrote on 03/29/2006 03:52:33 PM: Hello All, I am running across a very weird problem. Sometimes when a person paste text from a Worddoc into the text field of our webapp, the insert fails. Unfortunately I am not seeing the failure in the logs. There are a lot of factors to consider. The path to mysql looks like this. Firefox (OSX) - JDBC - Mysql (on linux) the field we are pasting to is a TEXT field. when I paste into an emacs editor. I see what looks like formatting code. Not sure why that is getting pasted into the text field and also not sure why the jdbc prepared statements are not making the text safe for insert. Anyone have a clue as to what might be happening? -- Ghetto Java: http://www.ghettojava.com -- Didn't you leave out an important component or two in your transfer chain? Doesn't the data actually take a route more like: Firefox (OSX) - Web Server - Server-side scripting language or CGI - JDBC - Mysql (on linux)? The fact that there is some processing stage at the server means that we can isolate the problem to either before or after it arrives at your server. What is the actual data that Firefox is sending to your server-side code? Verify that your server is receiving what you think you are pasting. If what you paste is not what you receive, then Firefox may be to blame. Check how your server-side code mangles the incoming information. You may be unintentionally changing the incoming data somehow. How are you setting up your JDBC connection to MySQL? I haven't used JDBC so I can't say if what you are doing is correct but someone on the list will surely pitch in and help. Are you trying to work with characters that fall outside the range of US-ASCII? When working with Unicode, UTF-8, and a whole slew of other charactersets, you have to ensure that all of the components of your data processing chain are using the same characterset and collation. These are just the first places I would look. Others will probably suggest more. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: SQL Question: alternative to crazy left joins?
eth1 [EMAIL PROTECTED] wrote on 03/28/2006 03:04:13 PM:
Hi All,
I'm migrating to a contact relationship management system (CRM) for
one of
my clients from a proprietary Access database. The CRM system can
import
our donor's contact history, but only in a non-normalized format with up
to
10 donations per contact record in a single row of an Excel spreadsheet.
Needless to say Access SQL doesn't seem up to the task of converting our
Donations table (individual donation indexed by ID and ContactID) to
this
funky format, so I've fired up MySQL in hopes of getting this done. But
I'm
not sure how to proceed. I tried using a nutty left join on ContactID
but
making sure the DonatioIDs for each joined row were different, but that
didn't work (the SQL was:
select a.ContactID,a.DonationID as aID,b.DonationID as bID,c.DonationID
as
cID from (fcdcTomcatsDonations as a LEFT JOIN fcdcTomcatsDonations as b
ON
a.ContactID=b.ContactID) LEFT JOIN fcdcTomcatsDonations as c ON
a.ContactID=c.ContactID WHERE a.DonationID != b.DonationID AND
b.DonationID
!= c.DonationID and a.DonationID != c.DonationID;
It has numerous faults:
* It's a pain to write (and I need to allow for up to 10 donations per
user!)
* It doesn't accound for different variations of the same selection
(e.g.
notated as {a.DonationID,b.DonationID,c.DonationID}, {1,2,3}, {2,1,3}
and
{3,2,1} are all included in the result)
I'm sure I'm betraying some fundamental SQL ignorance here, but maybe
someone out their can point me in the right direction. What I want is
something like this:
| ContactID| Donation1.* | Donation2.* | Donation3.* | etc...
Thanks in advance...off to Ruby to see if I can code some hack quick to
get
this task done with...;)
-Ethan
--
View this message in context: http://www.nabble.com/SQL-Question%3A-
alternative-to-crazy-left-joins--t1357877.html#a3636912
Sent from the MySQL - General forum at Nabble.com.
The correct way to model your information is to use the method you
describe as being used in the Access database. That data is normalized.
You even admit that you are denormalizing the data. The new CRM system is
imposing an artificial limit of 10 donations to any single contact. What
real-world rule says that after 10 donations, the contact is done? Or,
what real-world rule says to ignore the 11th or older contribution? These
artificial limit of only 10 donations in the donation history would be a
deal breakers for me.
Basically, the new design breaks several of the fundamental rules of
efficient database design. I would seriously doubt the capabilities of the
new system if this is how the backend is organized. I worry for your
client.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Re: JOINs-- need some expertise on this one
Mark [EMAIL PROTECTED] wrote on 03/27/2006 09:45:57 AM: Hi i am emiling you hoping you can help me urgently, I have a football tipping script which works fine now except i want to update a field from one table to another. At the moment there is a table (leaderboard) which is updated weekly via the updateleaderboard.php It shows USERNAME ,SCORE ,and COMPETITON NAME (eg: George | 12 points | harrys comp.) What i want to do is ad an avatar next to each username. I have already worked out the form for users to choose avatar and place the name of the image in an AVATAR field in the USERNAME table and echo it. My problem is that each week the LEADERBOARD usernames change and so do thier scores, comp name and of course their avatar.. i have made an AVATAR field in the LEADERBOARD table as well, now i just need some script which i will use as an i-nclude -on the updateleaderboard script to take the avatar field data and carry it over to the AVATAR field in the LEADERBOARD table as well. here is my script that doesnt work (also there is no session involved, makes it harder) below is my schema titleupdate_avatars/title?php include(header.php); include(connect.php); //insert avatar into leaderboard $sql = mysql_query SELECT users.avatar, leaderboard.username FROM users, leaderboard WHERE users.comp_id=leaderboard.comp_id; $result = @mysql_query($sql); $avatars = avatar; $username = username; $query = mysql_query(UPDATE leaderboard SET avatar = '$avatars' WHERE username = '$username'); $query = mysql_query($sql); ? ?php include(footer.html); ? * # # Table structure for table `comps` # CREATE TABLE `comps` ( `id` int(11) NOT NULL auto_increment, `name` varchar(255) NOT NULL default '', `username` varchar(255) NOT NULL default '', `password` varchar(255) NOT NULL default '', `emailtipsuser` char(1) NOT NULL default '0', `emailtipsall` char(1) NOT NULL default '0', `latetips` text NOT NULL, `winpoints` int(11) NOT NULL default '0', `drawpoints` int(11) NOT NULL default '0', `joinfee` float NOT NULL default '0', `perfect8point` char(1) NOT NULL default '0', `perfect8amt` float NOT NULL default '0', `ranking` text NOT NULL, `email` varchar(255) NOT NULL default '', `signup_date` datetime NOT NULL default '-00-00 00:00:00', `rules` text NOT NULL, `status` text NOT NULL, `pool` int(11) NOT NULL default '0', PRIMARY KEY (`id`) ) TYPE=MyISAM ; # Table structure for table `leaderboard` # CREATE TABLE `leaderboard` ( `id` int(11) NOT NULL auto_increment, `user_id` int(11) NOT NULL default '0', `comp_id` int(11) NOT NULL default '0', `username` text NOT NULL, `points` int(11) NOT NULL default '0', `amt` float NOT NULL default '0', `margin` int(11) NOT NULL default '0', `acc_margin` int(11) NOT NULL default '0', `avatar` varchar(50) NOT NULL default '', PRIMARY KEY (`id`) ) TYPE=MyISAM ; CREATE TABLE `tips` ( `id` int(11) NOT NULL auto_increment, `user_id` int(11) NOT NULL default '0', `points` int(11) NOT NULL default '0', `round` varchar(2) NOT NULL default '0', `game` int(11) NOT NULL default '0', `winner` text NOT NULL, `comp_id` int(11) NOT NULL default '0', `margin` int(11) NOT NULL default '0', PRIMARY KEY (`id`) ) TYPE=MyISAM ; # # Table structure for table `users` # CREATE TABLE `users` ( `id` int(11) NOT NULL auto_increment, `comp_id` int(11) NOT NULL default '0', `username` varchar(255) NOT NULL default '', `password` varchar(255) NOT NULL default '', `phone` varchar(255) NOT NULL default '', `email` varchar(255) NOT NULL default '', `signup_date` datetime NOT NULL default '-00-00 00:00:00', `last_login` datetime NOT NULL default '-00-00 00:00:00', `activated` char(1) NOT NULL default '', `first_name` varchar(50) NOT NULL default '', `last_name` varchar(50) NOT NULL default '', `avatar` varchar(50) NOT NULL default '', PRIMARY KEY (`id`) ) TYPE=MyISAM ; } ? ** Regards MArk Multi-table updates work just like multi-table selects. All you need to do is to swap things around a little. UPDATE leaderboard l INNER JOIN users u on u.comp_id = l.comp_id SET l.avatar = u.avatar; Because we are using an INNER JOIN, you will only be updating those records in leaderboard (because that what we said to do in our SET clause) with a valuefrom users (also from the SET clause) based on whether leaderboard.comp_id = users.comp_id (please look at the ON clause). Please RTFineM for more details: http://dev.mysql.com/doc/refman/4.1/en/update.html Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Update multiple tables
Mike Blezien [EMAIL PROTECTED] wrote on 03/27/2006 03:39:15 PM: Hello, I'm alittle unclear on how too update multiple tables. We have two tables with the same column name: account.state account_service.state when we update the account table, we also need to update the account_service table with the same value for the 'state' column. Can this be done with MySQL 4.1.12. and what is the correct syntax? Thx's Mickalo Are there any other fields that link those two tables? If not, this may work It would have been much easier to answer if you had provided more information about your tables and how they relate to each other. UPDATE account act INNER JOIN account_service svc on act.state = svc.state SET act.state = newvalue, svc.state = newvalue WHERE svc.state = oldvalue; Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Is there anyway to return an array?
David Godsey [EMAIL PROTECTED] wrote on 03/24/2006 12:55:29 PM: This is not a typically thing that anyone would expect Mysql to do, however with the application I am writing it would be helpful. Here goes: The application stores raw data from a satellite, along with a configuration of how to read the data. So of the data types are Int Arrays and Float arrays. Each can have conversion parameters to apply to the data. So I have to loop through this raw data, converting to the correct type, and applying some convertion parameter (say multiply by .26 for a float). So I will pass a function (a UDF function) a binary string, perform some conversion, then I would like to return it as an array. I know, I know, sounds like something that should be done in the presentation layer, howerver if possible, I would like to provide common data presentation to multiple presentation layers (written in different languages). So is there anyway to return an array in mysql? Accomplishing the impossible means only that the boss will add it to your regular duties. David Godsey Depending on your version of MySQL, you could return the results of a query created within a stored procedure. Or you could set up a view or Anyway, since array data types are not one of the data types supported by MySQL I think the closest you can get is either to return the data as a table or as the results of a query or as the content presented by a view. If you think about it, a recordset is very similar to a two-dimensional array. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: 4.0.18 restore dump file 'max_allowed_packet' error
Luke Vanderfluit [EMAIL PROTECTED] wrote on 03/22/2006 08:29:02 PM: Hi. I've got mysql 4.0.18 installed on a sun X4100 running solaris. This is just a 32 bit version of mysql. I've reverted back to this version after trying mysql 5, 4.0.26 and 4.0.18 64bit. Those versions were all unstable on 64bit, that is, the server would just go away for no apparent reason. This would ofter rear it's head when importing a dump file, the dump file I need to import is around 10 GIG, but also at other unpredictable times. I have this same database running on another machine running solaris, with no problems, except speed/performance. This other machine is the one that produces the dump file I'm trying to import. It does that with the following command: /usr/local/bin/mysqldump --opt --complete-insert --max_allowed_packet=32M rt3 | bzip2 -9 rt3.out-`date +\%Y\%m\%d-\%H`.bz2 I'm trying to restore the file on the new machine and I'm getting a 'max_allowed_packet' error: ERROR 1153 at line 162: Got a packet bigger than 'max_allowed_packet I've tried different settings for this in my.cnf, from 32 up to 1024M, and I still get the error. Is there anything I can do to remedy this? Kind regards. -- Luke Not exactly sure if this has changed in a recent version but I believe that the MySQL protocol only allows for packets up to 16M. That has to do with the size of the part of the packet that handles that value. Yep, here it is: http://dev.mysql.com/doc/internals/en/the-packet-header.html 7.4. The Packet Header Bytes Name - 3 Packet Length 1 Packet Number Packet Length: The length, in bytes, of the packet that follows the Packet Header. There may be some special values in the most significant byte. Since 2**24 = 16MB, the maximum packet length is 16MB. You are going to need to re-dump your file or you will need to split your larger packets (probably INSERT statements) into smaller chunks. More troubleshooting and information: http://dev.mysql.com/doc/refman/5.0/en/packet-too-large.html However, here it says the limit is 1GB: http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html I don't know which document to trust. Try changing it to use 16M chunks and see if that helps you work around the issue. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Converting password to old format.
Eugene Kosov [EMAIL PROTECTED] wrote on 03/23/2006 07:03:15 AM: Hi, everyone! I have transfer user's database and grants from one mysql server (4.1.15) to an older one (4.0.26). I don't know user's password and have only it's hash. How can I convert hashed password stored in mysql.user.password field to the 4.0 format? Is there something similar to OLD_PASSWORD(), but with 4.1's hashed password as a parameter? Any links, man references, etc. are welcome! Thanks in advance! -- Regards, Eugene Kosov. If such a function actually exists, it invalidates the premise that password hashes are only one-way. The algorithms (both pre-4.1 and post-4.1) for generating password hashes are intended to be one-way hashes. Unless you have the original plain-text password, you should not be able to transfer your existing 4.1 users onto the 4.0.26 system. You will need to create new accounts on your 4.0.26 system for the old user names and let them reset their passwords. Sorry! but it's designed to be this way. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Freeradius and MySql
Atkins, Dwane P [EMAIL PROTECTED] wrote on 03/23/2006 10:38:57 AM: Good morning. I am trying to install Free Radius with MySql, but I either have a login issue or a permissions issue. I have added Radius and [EMAIL PROTECTED] to database, Fedora local users and just about everywhere I can think possible. I have added permissions for radius radius local to databases mysql and database radius. However, when I try to log in to mysql using the command mysql -u radius -p radius, I get and error stating, Error 1045 (28000): Access Denied for user 'radius'@'localhost' (using:Password: YES). I am really frustrated since I have been working on this login and permission issue for some time. Thanks mailto:[EMAIL PROTECTED] Try this: a) Verify that you have actually GRANTED permission for the account you are trying to authenticate with SELECT user, host from mysql.user where user ='radius'; Then for each user-host combination listed above do one of these and compare the privileges listed to those you expected the accounts to have. SHOW GRANTS FOR 'user'@'host'; b) if the records exist but aren't being respected, issue a FLUSH PRIVILEGES command. I have noticed that (in contradiction to the documentation) that you sometimes need to manually refresh the privilege cache even after using a GRANT or REVOKE command. If those don't get you started, come back with whatever new information you learn and we can try something else. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Question about autoincrement ID
[EMAIL PROTECTED] (saf) wrote on 03/23/2006 10:50:10 AM:
Hi,
I have a question about autoincremend id:
If I have an autoincrement id set on my first column field of my
table and I have the
following entries:
1
3
And then I make a INSERT INTO foobar VALUES(''); , the next field
would be automatically 4:
1
3
4
Is there a possibility to take a free ID to not use too high IDs for
nothing?
I would like to take the ID 2 and not 4, because ID 2 is free.
My problem is that my system which uses the ID numbers in
applications which uses them
as signed int or unsigned int,
so I will soon have a problem, because I insert (and delete some
times) many entries in my SQL database,
but not more than the highest value of an signed integer.
--
Best regards,
saf
http://TrashMail.net/
The short answer is no. The Record #2 already existed. It's current
status is deleted. If you had other tables that linked their data to
record #2 and you created a new #2 to replace the one you already deleted
then you could possibly be making a bad match between the old data and
the new data.
For the sake of data consistency and for all of the other good reasons to
have a relational database, once an auto_increment value has been issued
it's considered used and no other record should ever have that number.
Only if you completely reset your table (see the command TRUNCATE TABLE)
could it be possibly safe to begin re-issuing the smaller numbers. Again,
it's only possible if all of the child records that used to point to the
old data were also deleted.
Do not rely on the auto_increment value for record sequencing. If you need
your records serialized in some sequential way, you will need to code the
support for those sequential numbers in your application.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Re: Question about autoincrement ID
[EMAIL PROTECTED] (saf) wrote on 03/23/2006 11:10:04 AM: On Thu, Mar 23, 2006 at 11:04:55AM -0500, [EMAIL PROTECTED] wrote: [EMAIL PROTECTED] (saf) wrote on 03/23/2006 10:50:10 AM: The short answer is no. The Record #2 already existed. It's current status is deleted. If you had other tables that linked their data to record #2 and you created a new #2 to replace the one you already deleted then you could possibly be making a bad match between the old data and the new data. For the sake of data consistency and for all of the other good reasons to have a relational database, once an auto_increment value has been issued it's considered used and no other record should ever have that number. Only if you completely reset your table (see the command TRUNCATE TABLE) could it be possibly safe to begin re-issuing the smaller numbers. Again, it's only possible if all of the child records that used to point to the old data were also deleted. Do not rely on the auto_increment value for record sequencing. If you need your records serialized in some sequential way, you will need to code the support for those sequential numbers in your application. So I must do a big SELECT and then check my self every time (for each INSERT), which IDs are free? Hmm if the table has more than 100 000 entries, this will slow down my system. Specialitty because the check function would be written in PHP. -- Best regards, saf http://www.trashmail.net/ No, you should quit trying to tell the database how it should implement auto_increment. If you don't want a monotonically increasing integer value to be automatically generated for each new record (or attempted new record) then simply don't use auto_increment. At that point you can make your ID values anything you want because you are going to be completely in charge of creating them. There are dozens of great reasons why the database has an auto_increment function built into it. There are probably as many reasons why doing what you propose to do is normally considered very bad practice. What's the real reason you don't want to let auto_increment do its automatic numbering? Many of us on the list manage databases with millions or billions of rows in our tables and we DO NOT even attempt to fill in the gaps as you propose to do. There is just no good reason to do it, and several good reasons to NOT do it. One important thing to remember: You should not let UI design requirements dictate your DB design. Most developers who design the database just to support the front end up regretting the decision. Those designs are either impossible to extend or impossible to manage or both. You should always design for an efficient database and adjust your retrieval methods to present the data in the manner requested, not the other way around. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
RE: Freeradius and MySql
You could try suggestion B) ;-) Shawn Atkins, Dwane P [EMAIL PROTECTED] wrote on 03/23/2006 11:25:24 AM: * a) Verify that you have actually GRANTED permission for the account you are trying to authenticate with SELECT user, host from mysql.user where user ='radius'; mysql SELECT user, host from mysql.user where user ='radius'; ++---+ | user | host | ++---+ | radius | % | | radius | localhost | ++---+ 2 rows in set (0.00 sec) SHOW GRANTS FOR 'user'@'host'; mysql SHOW GRANTS FOR 'radius'@'localhost'; ++ | Grants for [EMAIL PROTECTED]| ++ | GRANT USAGE ON *.* TO 'radius'@'localhost' | | GRANT ALL PRIVILEGES ON `radius`.* TO 'radius'@'localhost' | ++ 2 rows in set (0.00 sec) As you can see, it looks like I have granted permissions to the user, [EMAIL PROTECTED] for db radius. I am not sure what to do next. Thanks Dwane From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Thursday, March 23, 2006 9:51 AM To: Atkins, Dwane P Cc: mysql@lists.mysql.com Subject: Re: Freeradius and MySql Atkins, Dwane P [EMAIL PROTECTED] wrote on 03/23/2006 10:38:57 AM: Good morning. I am trying to install Free Radius with MySql, but I either have a login issue or a permissions issue. I have added Radius and [EMAIL PROTECTED] to database, Fedora local users and just about everywhere I can think possible. I have added permissions for radius radius local to databases mysql and database radius. However, when I try to log in to mysql using the command mysql -u radius -p radius, I get and error stating, Error 1045 (28000): Access Denied for user 'radius'@'localhost' (using:Password: YES). I am really frustrated since I have been working on this login and permission issue for some time. Thanks mailto:[EMAIL PROTECTED] Try this: a) Verify that you have actually GRANTED permission for the account you are trying to authenticate with SELECT user, host from mysql.user where user ='radius'; Then for each user-host combination listed above do one of these and compare the privileges listed to those you expected the accounts to have. SHOW GRANTS FOR 'user'@'host'; b) if the records exist but aren't being respected, issue a FLUSH PRIVILEGES command. I have noticed that (in contradiction to the documentation) that you sometimes need to manually refresh the privilege cache even after using a GRANT or REVOKE command. If those don't get you started, come back with whatever new information you learn and we can try something else. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
RE: Freeradius and MySql
OK, make sure you are using the correct password, too. Did you remember
to encrypt the password with PASSWORD() or OLD_PASSWORD() when you create
the account?
SELECT user, host, password
FROM mysql.user
WHERE user='radius';
make sure your password is hashed, if not we can help you fix that pretty
easily
UPDATE mysql.user
SET `password`=PASSWORD('plain-text-of-password')
WHERE user='radius';
FLUSH PRIVILEGES;
Then try again.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Atkins, Dwane P [EMAIL PROTECTED] wrote on 03/23/2006 11:30:41 AM:
I did do a FLUSH PRIVILEGES and this still resulted in the same error.
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Thursday, March 23, 2006 10:31 AM
To: Atkins, Dwane P
Cc: mysql@lists.mysql.com
Subject: RE: Freeradius and MySql
You could try suggestion B) ;-)
Shawn
Atkins, Dwane P [EMAIL PROTECTED] wrote on 03/23/2006 11:25:24 AM:
* a) Verify that you have actually GRANTED permission for the
account you are trying to authenticate with
SELECT user, host from mysql.user where user ='radius';
mysql SELECT user, host from mysql.user where user ='radius';
++---+
| user | host |
++---+
| radius | % |
| radius | localhost |
++---+
2 rows in set (0.00 sec)
SHOW GRANTS FOR 'user'@'host';
mysql SHOW GRANTS FOR 'radius'@'localhost';
++
| Grants for [EMAIL PROTECTED]|
++
| GRANT USAGE ON *.* TO 'radius'@'localhost' |
| GRANT ALL PRIVILEGES ON `radius`.* TO 'radius'@'localhost' |
++
2 rows in set (0.00 sec)
As you can see, it looks like I have granted permissions to the user,
[EMAIL PROTECTED] for db radius. I am not sure what to do next.
Thanks
Dwane
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Thursday, March 23, 2006 9:51 AM
To: Atkins, Dwane P
Cc: mysql@lists.mysql.com
Subject: Re: Freeradius and MySql
Atkins, Dwane P [EMAIL PROTECTED] wrote on 03/23/2006 10:38:57
AM:
Good morning. I am trying to install Free Radius with MySql, but I
either have a login issue or a permissions issue.
I have added Radius and [EMAIL PROTECTED] to database, Fedora local
users
and just about everywhere I can think possible. I have added
permissions for radius radius local to databases mysql and
database
radius. However, when I try to log in to mysql using the command
mysql
-u radius -p radius, I get and error stating, Error 1045 (28000):
Access Denied for user 'radius'@'localhost' (using:Password: YES).
I
am
really frustrated since I have been working on this login and
permission
issue for some time.
Thanks
mailto:[EMAIL PROTECTED]
Try this:
a) Verify that you have actually GRANTED permission for the account
you
are trying to authenticate with
SELECT user, host from mysql.user where user ='radius';
Then for each user-host combination listed above do one of these and
compare the privileges listed to those you expected the accounts to
have.
SHOW GRANTS FOR 'user'@'host';
b) if the records exist but aren't being respected, issue a FLUSH
PRIVILEGES command. I have noticed that (in contradiction to the
documentation) that you sometimes need to manually refresh the
privilege
cache even after using a GRANT or REVOKE command.
If those don't get you started, come back with whatever new
information
you learn and we can try something else.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Re: Unknown command '\'' during load
[EMAIL PROTECTED] wrote on 03/23/2006 02:20:00 PM: On 3/23/06, sheeri kritzer [EMAIL PROTECTED] wrote: What does line 1189 look like? Good question. Hard to tell, since it's the insert statement for a rather large table (25 million rows) and I have --extended-insert set, so it's all on one *really* long line. Seems like there should be a return or two in there, but apparently it doesn't work that way. On 3/17/06, Jack Baty [EMAIL PROTECTED] wrote: I'm trying to restore a database from a dump as part of my make-sure-this-will-restore-just-in-case process and I get the following error... ERROR at line 1189: Unknown command '\''. The only thing I've been able to find is this bug report... http://bugs.mysql.com/bug.php?id=9756 ...which claims that this (or something similar) was fixed in 5.0.6. I'm running 5.0.18 on OS X here and the dump is from a Debian box running 4.0.15. (I've also tried loading the dump on a box running 4.0.x with the same result) The table in question has just over 25 million rows, so it would be nice to be able to restore it if necessary :) I'm just trying to figure out if it's a data problem, version problem, or something else. -- Jack Baty Fusionary Media - http://www.fusionary.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Jack Baty Fusionary Media - http://www.fusionary.com When you generated this dump file, did you remember to use the --max_allowed_packet parameter to make sure that mysqldump didn't create any extended insert statements larger than your server wants to handle? It could be crapping out because at line 1189 you exceeded max_allowed_packet. To be safe always use a value of 16M or less when setting max_allowed_packet. Check your server's variables for the value it's currently using SHOW VARIABLES LIKE 'max%'; I don't know for sure that this is going to be the problem but it's always something to look at when you start dealing in larger dumpfiles. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: INSERT...SELECT Query Help Request.
Yesmin Patwary [EMAIL PROTECTED] wrote on 03/22/2006 09:56:20 AM: Dear All, First of all, I would like to thank Shawn Green, Peter Brawley and Josh for their kind help in my previous issue. I have a table named master_list with two field customer_id and list_code. I need to insert only non existent records in master_list from a child_list. Both master_list and child_list table structure are identical but data in child_list may contain records from master_list and new records. I have constructed query using INSERT…SELECT but I am unable to check and filter out records that already exist in master_list. INSERT INTO master_list (customer_id,list_code) SELECT DISTINCT customer_id,list_code FROM child_list Is there anyway to check and insert records in master_list without creating dups? Thanks in advance for any help. If you have a unique key or primary key set up on (master_list.customer_id, master_list.list_code) it's more simple that you think. Just add the word IGNORE to your INSERT statement like this :-) INSERT IGNORE INTO master_list (customer_id,list_code) SELECT DISTINCT customer_id,list_code FROM child_list usage details are here: http://dev.mysql.com/doc/refman/5.0/en/insert.html The IGNORE will tell the engine to disregard all duplicate key errors and continue processing rows. If you don't have such a key, I suggest you add one or let us know why you can't create it. Which workaround we can use for the lack of the key will depend on the version you are using. You are most welcome! Shawn Green Database Administrator Unimin Corporation - Spruce Pine
RE: OFAC SDN lists
Ing. Edwin Cruz [EMAIL PROTECTED] wrote on 03/22/2006 11:38:53 AM:
Ok, and sorry for my poor explanation and my spanglish
The ofac list is a database with thousands of names of persons who are
forbidden to do transactions like change of dollars to mexican pesos,
they
are trying to avoid money laundering, so if somebody try to do a
transaction he have to be searched into ofac list, but the problem is
that
the name can be formatted in diferents ways like 'Edwin Cruz' 'Cruz
Edwin'
'Edwin E. Cruz' and so on
I've tryed this:
Select * from OFACSDN where match(name) against ('edwin cruz');
And it returns more than 20 names that contain one or both words and i
want
to improve my search...
An example:
SELECT NameSDN FROM tofacsdn t where nombre like '%cruz%';
+--+
| NameSDN |
+--+
| CRUZ REYES, Antonio Pedro|
| CRUZ, Juan M. de la |
| PEREZ CRUZ, Osvaldo |
| SANTACRUZ LONDONO, Jose |
| CAVIEDES CRUZ, Leonardo |
| SANTACRUZ CASTRO, Ana Milena |
| CASTRO DE SANTACRUZ, Amparo |
| CASTRILLON CRUZ, Maria Leonor|
| RUELAS MARTINEZ, Jose de la Cruz |
| SANTA CRUZ IMPERIAL AIRLINES |
+--+
10 rows in set (0.02 sec)
How do I have to perform a search in that list with my name 'Edwin
Cruz', if
I try with full text I get this:
SELECT NameSDN from tofacsdn WHERE MATCH(nombre) AGAINST ('Edwin cruz')
+--+
| NameSDN |
+--+
| MUGUTI, Edwin|
| PARRA VELASCO, Edwin Hiulder |
| MUTASA, Didymus Noel Edwin |
| CRUZ, Juan M. de la |
| CAVIEDES CRUZ, Leonardo |
| PEREZ CRUZ, Osvaldo |
| CASTRILLON CRUZ, Maria Leonor|
| RUELAS MARTINEZ, Jose de la Cruz |
| SANTA CRUZ IMPERIAL AIRLINES |
| CRUZ REYES, Antonio Pedro|
+--+
10 rows in set (0.01 sec)
The closest result that I want is with this query:
SELECT NameSDN from tofacsdn WHERE NameSDN like '%Edwin%' and NameSDN
like
'%Cruz%'
But it isn't healthy because what abaut if I provide my name like 'Edwin
C.', I dont know what I am going to do with this... In the worst case
I'll
program a script to build a query like above.
I'm using MySQL 5.0.18, php 5.0.5
8 snip 8
Fulltext searches are what you need but you will need to change both the
stop word list and the minimum length parameter. That way you don't filter
out the shorter name parts like de and la. In this list almost nothing
you have is noise so you really need to turn off that part of the full
text indexing engine.
Another thing to try is the BOOLEAN operators that you can use when you do
a search IN BOOLEAN MODE...
I think you are on the right track. However, If for some reason the built
in fulltext indexing doesn't work for you or can't be configured to work
for you, you may need to create your own FT index. It's not as hard as it
may sound with data like what you have. Here are the basic components:
a) a table of all of the words appearing in any indexed column -- in your
situation, all you have are names. This table would hold (as single words)
each part of every name (without any punctuation). Most languages have a
function to split a string into an array based on some delimiter (like a
space) so this should be easy to do.
b) a table linking each of the words in the table from a) with a record in
your source table along with the position of the word in the source table.
It's a simple two-column table and should be very fast to search. If a
word appears more than once in the source record, it gets more than one
record in this table. You typically build this as you build the first
table.
c) an optional stemming table - this is where you can create a table to
expand or contract a name or a name part into other recognizable forms
(like misspelled names into their proper spelling)
d) a searching routine that queries the table from a) for matches to your
base terms and your stemmed terms then uses that list to make a list of
all of the records in the source table (by matching the first list to the
table in b) ). This will give you a list of how many matches occurred for
each source record. Run a count() query on this list to see how many
matches were returned per source row. Order the results by # of matches in
descending order. You could even throw out all single hits as noise
matches.
Anyway, that means that you are now in charge of your own FT index but
this one will be tuned to your particular searching needs. The one built
into MySQL is tuned better for matching words in lots of longer text
fields (like newspaper articles) than it is for searching lists of names.
The little bit of effort you put into building the
Re: UDF help, convert BLOB to BIGINT
David Godsey [EMAIL PROTECTED] wrote on 03/22/2006 01:21:07 PM:
I'm in the process of writing my first UDF and would appreciate some
help.
I am pulling data from a table like:
SELECT payload_time,
SUBSTR(BINARY(frame_data),
FLOOR(foffset/8)+1,
CEIL((flength + (foffset %8 ))/8))
FROM RawMajorFrames
WHERE raw_major_frame_id=rfid
INTO ptime,fdata;
frame_data is type BLOB. It is raw data collected. The substr will get
the specific bytes I'm interested in. What I need to do, is if the data
is = 8bytes, convert it to a BIGINT, so I can do some masking on the
data.
So I am writing a UDF to do the job, but I am apparently unfamiliar with
the Mysql data types and how I can convert them.
In a procedure.
DECLARE fdata_bigint BIGINT UNSIGNED;
SELECT BlobToInt(binary(fdata)) INTO fdata_bigint;
my_bool BlobToInt_init( UDF_INIT* initid, UDF_ARGS* args, char* message
)
{
if (args-arg_count != 1)
{
strcpy(message,Wrong arguments to BlobToInt; should be
BlobToInt(blob));
return 1;
}
return 0;
}
longlong BlobToInt( UDF_INIT* initid, UDF_ARGS* args, char* is_null,
char
*error )
{
longlong tmplong = *((longlong*)args-args[0]);
return tmplong;
}
I guess I was just assuming I could just cast the data as the type I
want,
but that doesn't seem to work. The function returns a 0.
Any help would be appreciated.
Accomplishing the impossible means only that the boss will add it to
your
regular duties.
David Godsey
C is not my strongest language but aren't you getting a null-terminated
string as args[0] ? What if you allocated a longlong and byte-copied the
bytes from args[0] into your longlong? Maybe something like...
longlong BlobToInt( UDF_INIT* initid, UDF_ARGS* args, char* is_null, char
*error )
{
char idx, *cArg
longlong tmplong, *plonglong ;
plonglong = tmplong;
cArg = (args-args[0]);
for(idx=0;idx8;idx++) {
plonglong[idx]=cArg[idx];
}
return tmplong;
}
Again, I strongly stress that C/C++ is not my best language (I don't use
it nearly enough) but I think you can see what I was trying to do. Other
options: memcpy(), strcpy(), strncpy() etc
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Re: multiple DB copies with periodic synchronization
Chris Cowen [EMAIL PROTECTED] wrote on 03/20/2006 10:34:48 AM: Hi We have a mySQL database which is being used by a restaurant ordering system, in which many of the tables are being used to store menu item information, pricing etc. The restaurant started off as a single outlet, but is now about to open some more new premises. We would like to have ordering systems in the new premises which can use the information from the database on the original machines. For operational reasons, we want to synchronise the tables that hold all the menu information once a day, and then use the local copies throughout the day. Synchronisation will be over a VPN. We would prefer to do it this way, so that it the VPN goes down (e.g. WAN or phone line is out), the restaurant can still operate using the last synchronised copy of the menu. (as opposed to simply sending the SQL commands over the VPN). There will be one master machine will be where the restaurant managers make changes to their menus, which will get picked up in the morning by the remote machines. The master will also be used to store transactions from all the other branches (where it can be backed up). We'd like to synchronise the menu information in the morning, before the restaurant opens. Then after they close, the transaction tables for the days sales to be synchronised back to the master machine. What is the usual approach in this sort of case? Do we: 1) write our own perl or php script to run the sql commands we need to synchronise? There's not a lot of tables. I don't know much about MySQL commands for synchronisation, or even if there are any. 2) use a third party synchronisation tool ? I looked at SQLyog, but we don't need a GUI. 3) is there another way? - for example a built-in mechanism in mysql to allow duplication with regular synchronisation. Sorry if this is a dumb question - but I'm sure this type of scenario must be fairly common, for example when implementing redundant or distributed databases, so I would be very interested in hearing about people experiences and opinions. Thanks Chris For your master-to-copy synchronization, MySQL already has the facilities for this. Check the section of the manual for replication http://dev.mysql.com/doc/refman/4.1/en/replication.html For the situation where you store transactions in each satellite restaraunt and at the end of the night you want to re-synch with the master database back at HQ, that's something you will need to script. The reason is, MySQL replication is all one-way. Each replication source (the master) can auto-synch with one or more destinations (slaves) but each slave can only listen to one master at a time. That means that you cannot setup a database at HQ to listen to your multiple satellite sites using the built in facilities. However, you can cascade several servers and you can set up replication to move in a circular pattern. Each of these designs have positives and negatives and you should really understand replication a little better before making a decision. More details are in the reading. Several varieties of questions similar to yours have also been discussed on this list. You should check the archives, too, for more information: http://lists.mysql.com/ Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: update statements problem
cybermalandro cybermalandro [EMAIL PROTECTED] wrote on 03/20/2006 11:00:51 AM: I am trying to update a table with a file that has more than one update statements like this: UPDATE products set products_price=22.00 WHERE products_model=5217-01 OR products_model=521701 AND products_um=CS; UPDATE products set products_price=3 WHERE products_model=5217-01 OR products_model=521701 AND products_um=PK; UPDATE products set products_price=0.25 WHERE products_model=5217-01 OR products_model=521701 AND products_um=EA; In the products table the only record that exist with product_model=5217-01 has a products_um=CS not EA but when my which contains the update statements is executed the last statement is the one that actually makes the change therefore resulting in the record to be products_price=0.25 instead of 22. Any ideas why this is happening? Shouldn't this statements just match the record and make the update? is there another way to do this? Thanks! It has to do with the expression you are using to pick which row to update WHERE products_model=5217-01 OR products_model=521701 AND products_um=CS This is parsed as WHERE products_model=5217-01 OR (products_model=521701 AND products_um=CS) But what I think you wanted to say was WHERE (products_model=5217-01 OR products_model=521701) AND products_um=CS Add the parentheses around your OR terms and you should only be changing what you wanted to change. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
RE: Error 1064: update .....select nested.
AAAUUUGGGHHH!!! You used the dreaded comma separated list !!! ;-) A more explicit way to write the same thing posted by Sr. Cruz... update TABLE1 a INNER JOIN TABLE2 b on a.id = b.other_id set a.FIELD1=b.FIELD2 WHERE a.column1='literal'; Actually posting a real query (instead of a cleansed column1...column2 query) would have resulted in you getting better answers. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Ing. Edwin Cruz [EMAIL PROTECTED] wrote on 03/16/2006 10:24:05 AM: update TABLE1 a, TABLE2 b set a.FIELD1=b.FIELD2 where column2='[EMAIL PROTECTED]' And column1 = '[EMAIL PROTECTED]' Or: update TABLE1 a, TABLE2 b set a.FIELD1=b.FIELD2 where b.column2=a.column1 And column1 = '[EMAIL PROTECTED]' Regards! -Mensaje original- De: Truong Tan Son [mailto:[EMAIL PROTECTED] Enviado el: Miércoles, 15 de Marzo de 2006 07:22 p.m. Para: mysql@lists.mysql.com Asunto: Error 1064: update .select nested. Dear Sir, MySQL-4.1.12 on RedHat Linux EL4: mysql update TABLE1 set FIELD1=(select FIELD2 from TABLE2 where mysql COLUMN2= '[EMAIL PROTECTED]') where COLUMN1='[EMAIL PROTECTED]'; ERROR 1064: You have an error in your SQL syntax. Check the manual that corresp onds to your MySQL server version for the right syntax to use near 'select FIELD2 from TABLE2 where COLUMN2='[EMAIL PROTECTED] mysql For MySQL -5.0.18 is Ok Syntax error for version 4.1.12 ? Thanks you and best regards, -- -- 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: 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: 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
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: 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: Query Optimization Question
Robert DiFalco [EMAIL PROTECTED] wrote on 03/11/2006 12:43:43 PM: In a previous database engine I was using an IN was more optimal than a . So, for example: SELECT * FROM table WHERE table.type IN (1,2,3); Where the possible values of type are 0-3, was appreciably faster than: SELECT * FROM table WHERE table.type 0; I've been playing with the Query Browser and checking out the optimization documents and haven't been able to make a clear call on whether or not this is also the case with MySQL/InnoDB. TIA, R. YES, YES, YES! This is definitely an optimization. When you say IN or =, you are asking for matching values. Matches can come from indexes. When you say or NOT IN, you are asking for everything BUT matches. In order to evaluate a negative, the database engine (and this is usually true regardless of database server) almost always performs a full table scan to test every row to make sure it is either or NOT IN. At the very best, they have to perform a full index scan which is still less efficient than ranged or values-based lookups. It's when you get into the situation where you are matching against dozens of IN-clause items that you may run into slowdowns again. Until you reach 2 or 3 dozen terms (depending on your hardware) you should be faster with an IN comparison than a or a NOT IN comparison. An optimization to search for BUT a term or two is to create a temporary table of all of your terms and delete the exact ones you want to exclude. Put an index on yoru temp table then JOIN that back into your query again (replacing the huge IN clause). The database will match index to index and things will get fast again. This technique can scale up to some really big queries. Always try to code for the affirmative tests. Your users will thank you. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
RE: Query Optimization Question
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 should be noticeably faster due to using ranged queries (partial index ranged matches): ( SELECT ... FROM ... WHERE indexfield 16 ) UNION ( SELECT ... FROM ... WHERE indexfield 16 ) 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. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Robert DiFalco [EMAIL PROTECTED] wrote on 03/13/2006 10:48:29 AM: Shawn, Any performance gains for specifying type 0 than type 0 ? R. From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Monday, March 13, 2006 6:37 AM To: Robert DiFalco Cc: mysql@lists.mysql.com Subject: Re: Query Optimization Question Robert DiFalco [EMAIL PROTECTED] wrote on 03/11/2006 12:43:43 PM: In a previous database engine I was using an IN was more optimal than a . So, for example: SELECT * FROM table WHERE table.type IN (1,2,3); Where the possible values of type are 0-3, was appreciably faster than: SELECT * FROM table WHERE table.type 0; I've been playing with the Query Browser and checking out the optimization documents and haven't been able to make a clear call on whether or not this is also the case with MySQL/InnoDB. TIA, R. YES, YES, YES! This is definitely an optimization. When you say IN or =, you are asking for matching values. Matches can come from indexes. When you say or NOT IN, you are asking for everything BUT matches. In order to evaluate a negative, the database engine (and this is usually true regardless of database server) almost always performs a full table scan to test every row to make sure it is either or NOT IN. At the very best, they have to perform a full index scan which is still less efficient than ranged or values-based lookups. It's when you get into the situation where you are matching against dozens of IN-clause items that you may run into slowdowns again. Until you reach 2 or 3 dozen terms (depending on your hardware) you should be faster with an IN comparison than a or a NOT IN comparison. An optimization to search for BUT a term or two is to create a temporary table of all of your terms and delete the exact ones you want to exclude. Put an index on yoru temp table then JOIN that back into your query again (replacing the huge IN clause). The database will match index to index and things will get fast again. This technique can scale up to some really big queries. Always try to code for the affirmative tests. Your users will thank you. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: socket error
Jim Douglas [EMAIL PROTECTED] wrote on 03/13/2006 12:24:56 PM: I can connect to mysql with mysql -u dbname -p When I start MySQL Administrator v 1.1.6 it says Could not connect to host 'localhost'. MySQL Error Nr. 2002 Can't connect to local MySQL server through socket '/tmp/mysql.sock' (111) ...then I Click on Details and enter, '/var/lib/mysql/mysql.sock' And can connect, no problem. Doesn't the Administrator look to /etc/my.conf for the socket location info? This is my my.conf file [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock log-error=/var/lib/mysql/mysqlerrorlog old_passwords=1 [mysql.server] user=mysql basedir=/var/lib [mysqld_safe] err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid Thanks Jim It may look there but I don't see where you are declaring any settings it wants to pay attention to. You need a new [mysql] section with a socket= setting in it in order for your clients (like MySQL Administrator) to know where your socket is hiding. [mysql] socket=/var/lib/mysql/mysql.sock [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock log-error=/var/lib/mysql/mysqlerrorlog old_passwords=1 [mysql.server] user=mysql basedir=/var/lib [mysqld_safe] err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid More details are here: http://dev.mysql.com/doc/refman/4.1/en/option-files.html Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Problems with timestamp field after upgrading MySQL Server.
Alternatively, you might be able to re-render times and dates in their condensed format by auto-converting them to a numeric value. Try adding zero to your date columns in your select clauses. Once condensed, your substring code should begin working as before. SELECT datecol +0 as datecol FROM ... Shawn Green Database Administrator Unimin Corporation - Spruce Pine Josh [EMAIL PROTECTED] wrote on 03/13/2006 12:42:32 PM: Sure is... SELECT DATE_FORMAT(dateField,'%Y%m%d') as dateField Take a look at: http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html -Josh --- Yesmin Patwary [EMAIL PROTECTED] wrote: Dear All, First of all, I would like to thank to Josh and Peter Brawley for their kind help on previous issue. Here is another Issue: There are many tables that have timestamp field with 8 char (mmdd). I use this format all over our website and to create various reports. Below is static php code that I used numerous places: ? $year=substr($date, 0, 4); $month=substr($date, 4, 2); $day=substr($date, 6, 2); ? All timestamp dependent reports and web pages had problems, once I upgrade to MySQL – 4.1.12. At the end I had to downgrade to 3.23 to bring web site operation to normal. Is there anyway to preserve old timestamp format after upgrading? MySQL - 3.23 +++-- | Field | Type | Default (mmdd) +++-- |log_date|timestamp(8)| MySQL - 4.1.12 ++---+ | Field | Type| Default ++---+ |log_date| timestamp |-00-00 00:00:00 Josh [EMAIL PROTECTED] wrote: You could rotate the output... basically get 1 row with 11 columns (CA01_count,CA02_count,...,CA12_count) (leaving out CA10) joining all 12 tables together... Or... perhaps we can help with the timestamp issues you are having and get you upgraded to later version of mysql that supports nested SELECT statements. --- Yesmin Patwary wrote: Dear All, I had some issues in past with timestamp fields as a result I am unable to upgrade to mysql 4.1 version. I am sure below the query recommended by Josh works with 4.1 or above. Would it be possible to rewrite this query for 3.23 version? Again, thank you Josh and all others for your kind help and comments. Josh wrote: Here's one method: SELECT cl1.list_name, count(*) as count FROM customerList cl1 WHERE cl1.id IN (SELECT cl2.id FROM customerList cl2 WHERE cl2.list_name='CA10') and cl1.list_name != 'CA10' GROUP BY cl1.list_name --- Yesmin Patwary wrote: Good morning all, We have 12 customer lists: CA01, CA02, ….,CA12. Table: customerList +---+--+ | list_name | id | +---+--+ | CA10 | 20BE | | CA07 | 20BE | | CA11 | 20BE | | CA03 | 20BE | | CA10 | NQCR | | CA04 | NQCR | | CA02 | MVYK | | CA10 | 0BEC | | …AND SO ON. | +---+--+ Each list has 25 to 350 customers. Same customer_id may exist in multiple lists. We need to compare CA10 list customer_id’s with other 11 lists to find matching id count by list_name. The query output should be something similar below: +--+---+ | list_name |count | +--+---+ | CA05 | 60 | | CA07 | 42 | | CA01 | 35 | | CA03 | 28 | | CA09 | 15 | | …AND SO ON… | +---+--+ Can this be done with a SELECT statement without using perl or php? Thanks in advance for any help. - Yahoo! Mail Bring photos to life! New PhotoMail makes sharing a breeze. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] - Yahoo! Mail Use Photomail to share photos without annoying attachments. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: timestamp
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
Re: mysql workbench and download?
[EMAIL PROTECTED] wrote on 03/10/2006 04:02:11 AM: It's not on the mysql site yet Greg. From [EMAIL PROTECTED] Sat Mar 4 23:00:04 2006 To: [EMAIL PROTECTED] From: Alfredo Kengi Kojima [EMAIL PROTECTED] Subject: ANNC: MySQL Workbench 1.0.5 beta released MySQL Workbench 1.0.5 beta has been released. MySQL Workbench is a database design tool for MySQL. MySQL objects such as tables, routines and views can be created and edited and their visual representation on the canvas enables one to quickly understand and work efficiently with complex and simple database schemas. Feature highlights: - Reverse engineering of existing MySQL databases - Import DBDesigner4 models - Synchronize edited model with MySQL database - Generate SQL create script file - Printing (Windows) - Powrefull scripting and plugin interface. Plugins can be written in several languages, such as Lua, PHP, Java and Python. - Fast, OpenGL based graphical canvas - Quickly accessible Overview mode, which zooms out the whole canvas to reveal a more general view. An OpenGL capable video card with at least 32MB of memory is needed to use MySQL Workbench. Windows binaries are available at: ftp://ftp.mysql.com/pub/mysql/download/mysql-workbench-1.0.5-beta-win32.msi ftp://ftp.mysql.com/pub/mysql/download/mysql-workbench-1.0.5-beta- win32.msi.md5 ftp://ftp.mysql.com/pub/mysql/download/mysql-workbench-1.0.5-beta- win32-noinstall.zip ftp://ftp.mysql.com/pub/mysql/download/mysql-workbench-1.0.5-beta- win32-noinstall.zip.md5 Linux/Gtk+ binaries are available at: mysql-workbench-1.0.5beta-1.suse93.i586.rpm mysql-workbench-1.0.5beta-1.suse93.i586.rpm.md5 mysql-workbench-1.0.5beta-1.suse93.src.rpm mysql-workbench-1.0.5beta-1.suse93.src.rpm.md5 mysql-workbench-1.0.5beta-linux-i386.tar.gz mysql-workbench-1.0.5beta-linux-i386.tar.gz.md5 The SuSE RPM or the tar.gz should work on most recent distributions. Binaries for more distributions and architectures will follow. Mac OS X binaries will be available soon briefly. Sources: mysql-workbench-1.0.5beta.tar.gz mysql-workbench-1.0.5beta.tar.gz.md5 Changes since the last alpha version: All platforms - Significant speed improvements in the Generic Canvas - Auto arrange elements - Markers - Switchable grid display - Complete database - synchronization - Reverse engineering is complete - New feature: plug-ins, which are scripts to be used for certain tasks in WB- DBDesigner4 import Windows - Significantly faster model load - Export model as image (png) - Printing with preview - Copy Region as Image - Full GRT integration (including tabbed shell), supported for scripting, are now: Lua, Python - Model properties page - Table options editing - BUG when deleting objects is still present Linux - Added several commands for layouting (send back/front etc) - Object properties tab Enjoy! -- Alfredo Kojima, GUI Developer MySQL AB, www.mysql.com Buenos Aires, Argentina Are you MySQL certified? www.mysql.com/certification On Fri, 10 Mar 2006, Gregory Machin wrote: To: mysql@lists.mysql.com mysql@lists.mysql.com From: Gregory Machin [EMAIL PROTECTED] Subject: mysql workbench and download? Hi where can I download mysql workbench ? The links on the mysql forum dont work ... and i cant seem to find another download ... probably not see the wood for the treats .. Thanks It's good that it's not posted on the site yet. I just tried the binary: mysql-workbench-1.0.5-beta-win32-noinstall.zip 1) installed as usual and started well. 2) reverse engineering an exsiting database went smoothly 3) PROB: The entity relationshps were drawn in backwards. My parent tables had the crow's feet pointed to them instead of the child tables. 4) I added a new 1:n relationshp using the 1:n tool. The connector between the two tables appeared. 5) I went to synchronize the change with the backend database. In watching the scripts as it was generated, the tool wanted to add two new columns and was linking to the wrong field on the parent table a) I tried to edit the link to point it to the correct parent field and child field for the FK but could not figure out how to change the properties of the link. b) I tried to delete the link from the map. Couldn't do that. c) I referred to the bundled help file. It has only two paged in it (understandable with a tool this new) d) I used the UI option to find the online documentation. I was redirected to the manual for MySQL 5.1 and couldn't get a response to a search on the term workbench. It's looking good so far but I am not even sure I would call this beta quality yet. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: ~~Info needed~~
Косов Евгений [EMAIL PROTECTED] wrote on 03/10/2006 07:53:37 AM:
Hm... It seems to me In MySQL there's no such thing as database owner.
Or am I missing something?
Mohammed Abdul Azeem пишет:
Hi,
How to check for the database owner for a particular database ? The
way
show procedure status command lists the definer column, Is there a
command that shows the database owner. Similarly, what is the command
to
see the owner of a table in a database ?
Thanks in advance,
Abdul.
This email has been Scanned for Viruses!
www.newbreak.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Eugene is right. Objects within a MySQL database are not owned by any
one account. They are essentially all global objects organized into
specific databases.
Here's the reason behind the column your found. In the CREATE STORED
PROCEDURE statement there is a clause:
SQL SECURITY { DEFINER | INVOKER }
That clause tells the database under whose credentials should the
statements of the SPROC be executed. It determines if the SPROC will
execute under the credentials of the user who created it or the user who
is invoking it. DEFINER is the default value if none is specified.
More details here:
http://dev.mysql.com/doc/refman/5.0/en/create-procedure.html
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Re: Import 5.0 changes to file?
Scott Purcell [EMAIL PROTECTED] wrote on 03/10/2006 12:28:21 PM: Hello, I have been given a file to import into the mysql 5.0 database. This is how it is formatted: I created a database called app which is brand new. Right off, the bat, I noticed there is a field called numeric. So I changed that to int. But when I try and import it, it is complaining about the double quotes. So then I changed the double quotes to single, and it still complained. What can I do to make this import work? I am trying to find the guidelines. Ps the file is pretty long, so here is only a sampling: Thanks, Scott sample script snipped (thank you for posting the snippet. It made diagnosing your problem much easier) Scott, You are going to need to edit this file to be less T-SQL and more ANSI SQL. What you have is written to work on an MS SQL server or on MS Access. There are several things you need to change in order to get MySQL to accept it. a) You should enquote datbase names, table names, and column names with backticks ` ` not single quotes ' '. Double quotes are accepted, too, but only if you tell the server they are OK. It's just safer to use backticks and it's the standard method for MySQL. DROP TABLE `APP`.`ATTACHMENT`; b) If the table `ATTACHMENT` doesn't exist yet in the database `APP`, the statement in a) will throw an error. To make this a conditional DROP COMMAND, use the IF EXISTS modifier and you won't need to worry about it (you get a warning instead of an error). DROP TABLE IF EXISTS `APP`.`ATTACHMENT`; c) In order to use FOREIGN KEYs as part of your design, you have to use a storage engine that supports them. Right now in MySQL, only the InnoDB engine supports FOREIGN KEYs so all of the tables you want to create a FK _from_ or a FK _to_ (both ends) will need to have ENGINE=InnoDB; at the end of the statement CREATE TABLE ... ( ... ) ENGINE=InnoDB; d) In order to create a FK between two tables, you also need to have to have both ends of the FK using the same data type (including signed or unsigned) and they must both be indexed. CREATE TABLE APP.CATEGORY ( `CATAGORY_ID` int NOT NULL, ... `CREATED_BY` int, ... `PARENT_CATAGORY` int, CONSTRAINT CATEGORY_pkey PRIMARY KEY (CATAGORY_ID), INDEX ix_created_by (`created_by`), - add these to this table definition INDEX ix_parent_catagory (`parent_catagory`), - CONSTRAINT CATEGORY_PARENT_CATAGORY_fkey FOREIGN KEY PARENT_CATAGORY) REFERENCES APP.CATEGORY (CATAGORY_ID) ON UPDATE NO ACTION ON DELETE NO ACTION, CONSTRAINT authorfk FOREIGN KEY (CREATED_BY) REFERENCES APP.USER (USER_ID) ON UPDATE NO ACTION ON DELETE NO ACTION, ... ) ENGINE=InnoDB; e) You have to define a storage size for all of your varchar columns. CREATE TABLE `APP`.`ATTACHMENT` ( ... `ATTACHMEMT_PATH` varchar(256), ... ); I am so sorry it's not going to be as painless as you originally thought. However by needing to go through this you can also fix some spelling errors in your column names (category is not spelled catagory like you have in some of your columns). However, if you already have a lot of code that uses the misspelling, it may not be worth the effort to fix it. Please refer to the manual for more details about all of the changes I suggest... CREATE TABLE syntax: http://dev.mysql.com/doc/refman/5.0/en/create-table.html CHAR and VARCHAR columns: http://dev.mysql.com/doc/refman/5.0/en/char.html The InnoDB storage engine: http://dev.mysql.com/doc/refman/5.0/en/innodb.html Foreign keys: http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html If this database already exists on a MS SQL machine and you can access it from your location, it may be easier to use one of the migration tools to automagically transmute and copy the structure from the old system directly into MySQL. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Reusing connections.
fbsd_user [EMAIL PROTECTED] wrote on 03/10/2006 02:18:49 PM: In my reading of mysql tutorials I see many examples of mysql access with out first doing a connect. Comments say something about reusing open connections for faster processing and less resources usage overhead. What are they talking about what code do I need to make this happen? You first need to tell us which of several dozen languages you are programming with. We can't read your mind. ;-) Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: problem accessing mysql from PHP
Nestor [EMAIL PROTECTED] wrote on 03/10/2006 02:33:50 PM: I am getting this error and I do not know why? Could not connect: Client does not support authentication protocol requested by server; consider upgrading MySQL client I can access mysql from the command line and I can access it from mysql Administrator. When I run a php program I get an error. I also I am ahving problems accessing the DB from phpmyadmin I am running php 4.4.2 Client API version 3.23.49 mysql 5.0.18-nt apache 2.0.55 Help? Nestor :-) This is not just a FAQ but it is a VFAQ: http://dev.mysql.com/doc/refman/5.0/en/old-client.html Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: FOREIGN KEYS
Nanu Kalmanovitz [EMAIL PROTECTED] wrote on 03/10/2006 02:35:08 PM: Hi! Server system SBS (Novell Small Business suite) 6.5 sp 1 with MySQL ver. 4.0.15a, PHP 4.2.3, all of them on same machine. I just finished create a new DB called TIULIM (InnoDB) with 3 tables (Sites, Tracks Pathes). Now, using MySQL Query Browser ver. 1.1.15, I'm trying to build FOREIGN KEYS. The above tool is generating the following query: ALTER TABLE `tiulim`.`pathes` ADD CONSTRAINT `FK_pathes_1` FOREIGN KEY `FK_pathes_1` (`Site_ID`) REFERENCES `sites` (`Site_ID`) ON DELETE RESTRICT ON UPDATE RESTRICT, ADD CONSTRAINT `FK_pathes_2` FOREIGN KEY `FK_pathes_2` (`Track_ID`) REFERENCES `tracks` (`Track_ID`) ON DELETE RESTRICT ON UPDATE RESTRICT; After executing the query, it display the error message: MySQL Error Number 1005 Can't create table './tiulim/#sql-84_169.frm' (errno: 150) What is the problem? How to solve it? TIA Nanu Another FAQ A) Whole databases are not handled by InnoDB. The individual tables are. Check the results of SHOW CREATE TABLE table_name_here for each table you are linking from and linking to. You should see ENGINE = InnoDB after the last ) but before the final ; in each definition. If not, you need to change your tables to use that engine. B) Each column participating in either end of a FOREIGN KEY must be of the same data type and size and signedness (everything about the columns must match except the names and comments) C) Each column participating in either end of a FOREIGN KEY must be the leftmost column of an index. Simply declaring a FOREIGN KEY constraint will not automatically create an index on the child table if one is missing. D) When all else fails, read the detailed error message returned by InnoDB by using the SHOW INNODB STATUS; command More details here: http://dev.mysql.com/doc/refman/4.1/en/innodb-foreign-key-constraints.html Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Help creating index's for this query...
Cory at SkyVantage [EMAIL PROTECTED] wrote on 03/10/2006 03:52:56 PM: I need help, I'm somewhat new to indexing, I am joining two tables and need to optimize the speed of the query. I'm running the NDB storage engine so the foreign key stuff is disabled. With that in mind, here's the query and additional comments below: SELECT COUNT(*) Count FROM pnr_passengers pax INNER JOIN pnr_status status ON pax.ID = status.ID_passengers INNER JOIN pnr_seg ments ps ON status.ID_segments = ps.ID WHERE status.res_status='0' AND ps.origin='SJU' AND ps.destination='SIG' AND ps.flight_ date='2006-03-10' AND ps.flight_number='218'; Field types pax.ID, bigint (keyfield, indexed) status.ID, bigint (keyfield, indexed) ps.ID, bigint (keyfield, indexed) status.res_status, int ps.origin, char(3) ps.destination, char(3) ps.flight_date, date ps.flight_number, int As you can see I'm joining three tables here each having a one-to-many relationship with each other pnr_passengers -one to many with- pnr_status pnr_status -one to many with- pnr_segments Here's an EXPLAIN with \G: *** 1. row *** id: 1 select_type: SIMPLE table: status type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 100 Extra: Using where *** 2. row *** id: 1 select_type: SIMPLE table: pax type: ref possible_keys: PRIMARY key: PRIMARY key_len: 8 ref: W4_AirlineData.status.ID_passengers rows: 1 Extra: *** 3. row *** id: 1 select_type: SIMPLE table: ps type: ref possible_keys: PRIMARY key: PRIMARY key_len: 8 ref: W4_AirlineData.status.ID_segments rows: 1 Extra: Using where Any help you anyone can provide as to the best way to create these index's that'd be great! This is an OFTEN used query in our software and the tables with have many rows. Cory Can you post the results of SHOW CREATE TABLE pnr_segments, please? Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Help creating index's for this query...
Cory at SkyVantage [EMAIL PROTECTED] wrote on 03/10/2006 04:25:00 PM: [EMAIL PROTECTED] wrote: Can you post the results of SHOW CREATE TABLE pnr_segments, please pnr_segments | CREATE TABLE `pnr_segments` ( `ID` bigint(20) NOT NULL auto_increment, `ID_pnr` bigint(20) NOT NULL, `ID_pnr_fares` bigint(20) NOT NULL, `flight_number` int(10) unsigned NOT NULL, `flight_date` date NOT NULL, `origin` char(3) NOT NULL, `destination` char(3) NOT NULL, `weightedpercent` decimal(9,6) NOT NULL, `intended_origin` char(3) NOT NULL, `intended_destination` char(3) NOT NULL, PRIMARY KEY (`ID`,`ID_pnr`), ) ENGINE=ndbcluster DEFAULT CHARSET=latin1 Try this: ALTER TABLE pnr_segments ADD KEY(`origin`,`destination`,`flight_date`,`flight_number`,`id`); and this modified query: SELECT COUNT(1) Count FROM pnr_passengers pax INNER JOIN pnr_status status ON pax.ID = status.ID_passengers INNER JOIN pnr_segments ps ON status.ID_segments = ps.ID AND ps.origin='SJU' AND ps.destination='SIG' AND ps.flight_date='2006-03-10' AND ps.flight_number='218' WHERE status.res_status='0'; This is a rather extreme optimization (adding such a large index) but you did say this was a very frequently run query so the performance boost may offset any additional data load. Please tell me you aren't making the database do all of this work just to run the exact same query in the very next statement with a different SELECT clause in order to pull data from these same tables That would be silly :-( If you have a real query (one that doesn't just count how many rows you find) based on these same tables and conditions and you running this query as a test to say if count==0 then skip the real query then you are seriously wasting a trip to the database and a lot of CPU cycles while you are there. Ask for what you need the first time and check to see if you get any results. Then move on if you don't have any. Now, you may have simplified the SELECT clause to obfuscate the column names you actually have in the tables, that I completely understand. However, if you really do this as a query, you should stop. A further refinement to this index would be to relist the columns in the order in which they are found in WHERE clauses most frequently (still leaving the ID column at the end of the list). This is an example of a covering query and when done correctly, they can seriously improve performance for entire categories of queries. In fact, you may consider adding more columns to the list if you use other things than just the ID value in your SELECT clause... Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: duplicate entry (same time every day)
Ronan McGlue [EMAIL PROTECTED] wrote on 03/09/2006 05:05:15 AM:
i have a little quirck with my current Mysql setup. I log all messages
from my mtas into Mysql and then use a simple query to generate stats
from them... as below:
mysql select exim.hour,sum(exim.count),stats.spam,stats.ham from
servers,exim,stats where exim.date=stats.day and exim.date =curdate()
and exim.hour=stats.hour and exim.machine=servers.id and
servers.type='MX' group by exim.hour;
+--+-+--+--+
| hour | sum(exim.count) | spam | ham |
+--+-+--+--+
|0 |1636 | 826 | 775 |
|1 |1243 | 708 | 515 |
|2 |1565 | 1018 | 536 |
|3 |2274 | 637 | 492 |
|4 |1325 | 760 | 547 |
|5 |1177 | 768 | 398 |
|6 |1266 | 718 | 530 |
|7 |1382 | 883 | 477 |
|8 |2026 | 899 | 1020 |
|9 |5856 | 838 | 1879 |
| 10 | 80 | 29 | 50 |
+--+-+--+--+
11 rows in set (0.01 sec)
however i have noticed that at hour 3 every day the DB duplicates the
entries for the 3 MTAs... as below
mysql select servers.id,exim.hour,exim.count from servers,exim,stats
where exim.hour=3 and exim.date=stats.day and exim.date =curdate() and
exim.hour=stats.hour and exim.machine=servers.id and servers.type='MX';
++--+---+
| id | hour | count |
++--+---+
| 0 |3 | 819 |
| 0 |3 | 819 |
| 1 |3 | 189 |
| 1 |3 | 189 |
| 2 |3 | 129 |
| 2 |3 | 129 |
++--+---+
6 rows in set (0.00 sec)
hours 0-2 and 4-23 are fine and report everything as expected...
Now the data must be getting in somehow I just dont know how or why?
The inserts are done by an exim acl entry as follows..
warn condition = ${lookup \
mysql {insert into exim SET \
machine='MACHINE', \
date='${substr_0_10:$tod_log}',\
hour='${substr_11_2:$tod_log}'\
ON DUPLICATE KEY UPDATE count = count + 1\
}{0}{0}}
anythoughts on the matter?
thanks
Ronan
--
Ronan McGlue
Analyst / Programmer
CMC Systems Group
Queens University Belfast
Do you have two entries in `stats` that correspond to hour 3?
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
RE: Charset questions
Ryan Stille [EMAIL PROTECTED] wrote on 03/09/2006 09:59:32 AM: Are my emails not coming through? Or is this question way too 'newbie' and no one wants to touch it? -Ryan Ryan Stille wrote: I'm still hoping someone can else can share their input on this. What do other people usually do as far as the collation setting? Thanks, -Ryan Ryan Stille wrote: When we migrated to MySQL from MS SQL, I left everything set to the default as far as collations - latin1_swedish_ci. This was based on digging through the manual and google. But now I am migrating the application to a newer version of ColdFusion and am running into some issues with charsets on some ColdFusion functions. I am wondering if my database charset has anything to do with it. What do other people usually do as far as the collation setting? We are in the US, but do have a few sites that make use of German and Spanish characters. Thanks, -Ryan I don't think that your question was too newbie. I believe that within the world of MySQL that changing charsets and collations is still something of a dark art. Not many users need something other than the default settings and those that do experiment with the various charsets and collations until they find one that works for them. There have been frequent posts on this list looking for help in setting up a character set or trying to resolve why certain characters no longer appear as they were when they went into the database. The basic thing to remember is that each and every communications channel can have it's OWN charset setting. If you are pushing data in using utf-8 and you are pulling data out through a connection using UCS-2, you may run into a few translation errors (that was just an example, I don't know if they are incompatible or not) I think that the default charset and collation covers most or all of the european alphabets so you may not need to use anything but the defaults for English, Spanish, Italian, Portuguese, French, German, and the Nordic languages (I can't name them all, sorry to those who live there). I have to admit that I haven't had to delve deeply into this subject so I cannot speak from deep experience but that's basically what I have gathered by lurking on the other posts dealing with this topic. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: InnoDB Indices
Robert DiFalco [EMAIL PROTECTED] wrote on 03/09/2006 12:32:44 PM: I have some questions regarding InnoDB indices. Say I have a table with millions of records. On of the fields is a type field that has a possible value of 1,2,3, or 4. I sometimes query by the type field and may at other times order on it. Do queries benefit from an index with this low of a selectivity? If through the query browser I find that they do, should I increase the selectivity of the index by making it a compound index and adding my Primary Key to the TYPE index? If I make it a compound index, does it have any positive impact on INSERT, UPDATE, or maybe just DELETE? R. Hi Robert, I assume you have already read through the entire optimization section in the manual: http://dev.mysql.com/doc/refman/4.1/en/optimization.html -or- http://dev.mysql.com/doc/refman/5.0/en/optimization.html (as appropriate) as this entire chapter deals with the finer details of the questions you are asking. So I will try to just answer you in the general sense so that perhaps you can make better sense of what you already read. One of the most frequent bottlenecks to query performance is related to physically retrieving data from the hard disks. Indexes, though very useful, actually slow down performance if you need to pull more than about 1/3 of any table's rows off of the disks. Columns of data not already included in the indexes themselves must be retrieved from the disk before their values can be used as part of a result or a part of a comparison or as part of a formula. What happens to the performance is that it begins to take longer (mostly due to the random access disk seek operations) to pick lots of individual records (based on an index hit) that it would have taken if you had just found the beginning of the table data and streamed the whole table through memory in one big burst of data. Now, because the indexes to a table are loaded into memory before query evaluation the optimizer can estimate how many rows of a table it would find if it used one index over another (and starting with 5.0 how many it would find based on certain combinations of indexes). If all of the data you need from a table is actually part of an index then the entire read data from disk portion of the query can also be skipped (under the right conditions). So having what is known as a covering index (a multi-column index where some of the right-most columns are listed mostly to avoid actual table reads) can seriously improve the performance of certain queries while providing a normal boost to the performance of a bunch of others. How you execute your queries should not matter. By the time the database server sees it, one query looks just like every other. So I don't see how running a query in the Query Browser would be any faster than if you had issued the same query from the CLI or via any of the other connection APIs. Declaring compound indexes make sense if your query patterns frequently use those columns or if you are trying to create a covering index. Improving the cardinality of any index can only help its performance. The up-side to indexes (keys) is that they improve the chances of quickly finding the data you are looking for thereby improving your overall query performance. The down-side is that the addition of every new record has to create the appropriate additions to every index on the table. The same goes for UPDATES and DELETES. You have to benchmark your performance in your environment to figure out when enough indexes becomes too many for your comfort. Each new index also takes up space in memory and room on the disk so too many indexes can starve your system for resources, too. Like I said above, these are just some general guidelines. The nitty gritty can be found in the manual. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Innobase bought out by Oracle
Dan Rossi [EMAIL PROTECTED] wrote on 03/07/2006 05:47:41 AM: yet just another multi-national gupling up its competitors, i stumbled across this blog http://golgote.freeflux.net/blog/archive/2005/10/08/oracle-buys- innodb.html and had no idea, its just as bad the the latest Eola patent case against microsoft and others its getting out of hand. What are going to be the effects on the development of Mysql if oracle is going to own the company that supply Innodb ? As of the most recent official comments from those in the know (the actual principals involved in negotiations), there is no anticipated impact on either InnoDB or MySQL. The worst case scenario is that the development and support of InnoDB forks into two versions. One stays within Oracle's realm the other belongs to the community. InnoDB is still open source and I have heard of no intent to change that status. There have also been rumors of other open-source developers trying to develop more database engines. Perhaps one of them will surpass InnoDB and take over as the preferred transactional engine. Basically, it's still business as usual; only it's with a different businessman. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Merge tables: how to get the insert_method?
Martijn Tonies [EMAIL PROTECTED] wrote on 03/07/2006 09:57:17 AM: Hi, Anyone got a clue where to get a hold of insert_method after creating the table? Is the SHOW CREATE TABLE output the only way to get it? Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, Oracle MS SQL Server Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com insert_method ?? I thought I knew a little about databases and table design but that's a new term for me. What does it mean and how would I apply it in order to modify a table's contents? Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Merge tables: how to get the insert_method?
Martijn Tonies [EMAIL PROTECTED] wrote on 03/07/2006 10:46:58 AM: Hello Shawn, Thanks for replying. INSERT_METHOD is an option valid for merge tables. See http://dev.mysql.com/doc/refman/5.0/en/create-table.html From what I can see, the only way to get it, is parse the SHOW CREATE TABLE output. Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, Oracle MS SQL Server Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com Martijn Tonies [EMAIL PROTECTED] wrote on 03/07/2006 09:57:17 AM: Hi, Anyone got a clue where to get a hold of insert_method after creating the table? Is the SHOW CREATE TABLE output the only way to get it? insert_method ?? I thought I knew a little about databases and table design but that's a new term for me. What does it mean and how would I apply it in order to modify a table's contents? Shawn Green Database Administrator Unimin Corporation - Spruce Pine Thank you very kindly. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Is MySQL is expected to support these in the future?
J A [EMAIL PROTECTED] wrote on 03/07/2006 12:31:57 PM: 1. Support for N-types for the future. UNICODE support for NCHAR, NCLOB and NVARCHAR datatypes 2. Support for UCS-2 or UTF-16 for future. 3. Support for N-types in Stored Procedures 4. Automatic translation of N-type to SP’s Unicode 5. Embedded database server within a client application. _ On the road to retirement? Check out MSN Life Events for advice on how to get there! http://lifeevents.msn.com/category.aspx?cid=Retirement I don't know where you are getting your information. Most of this is already done!!! 1. Please see: http://dev.mysql.com/doc/refman/5.0/en/charset-national.html 2. Please see (UCS-2... don't know about UTF-16): http://dev.mysql.com/doc/refman/5.0/en/charset-unicode-sets.html?ff=nopfpls 3. AFAIK - All character sets and collations are supported within stored procedures. 4. Please see: http://dev.mysql.com/doc/refman/5.0/en/cast-functions.html 5. Please see: http://dev.mysql.com/doc/refman/5.0/en/libmysqld.html Please, next time check the online documentation first Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: viewing number of current connections
jonathan [EMAIL PROTECTED] wrote on 03/05/2006 03:19:41 PM: is there a way to view the current number of connections in mysql? I'm developing a PHP / MySQL app and would like to be able to debug a few performance problems. Ideally, I'd like to be able to query the number of current connections via the mysqli interface and make decisions based upon that. -jonathan I believe what you are after is: SHOW FULL PROCESSLIST; Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Logging (wrong) passwords by mysqld
I believe Daniel is correct. The passwords are hashed before leaving the client. You may be able to capture invalid hashes but they are already encrypted before they get to the server. I do not know of any event or callback function you can use to tie into the server to trigger a logging event. Sorry! Shawn Green Database Administrator Unimin Corporation - Spruce Pine Daniel da Veiga [EMAIL PROTECTED] wrote on 03/06/2006 07:40:50 AM: On 3/5/06, Manuel Schmitt (manitu) [EMAIL PROTECTED] wrote: is there a reason that the unknown passwords can't simply be reset? yes, because not all clients are currently known and resetting them would possibly break the application(s) I am not pretty sure, but MySQL use to send encrypted passwords to the server, so, you'll never ever be able to really see the passwords. MySQL uses one-way ecryption, that means, if you've lost it, its gone. There's no way to find the characteres that generated that encrypted code. -- Manuel Schmitt - Geschäftsführer - manitu [EMAIL PROTECTED] Welvertstraße 2 http://www.manitu.de/ 66606 St. Wendel Telefon: +49-(0)6851-99808-20 Telefax: +49-(0)6851-99808-99 PGP-Key-ID: 0x3E486E93 Unser Impressum finden Sie unter http://www.manitu.de/impressum/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- Daniel da Veiga Computer Operator - RS - Brazil -BEGIN GEEK CODE BLOCK- Version: 3.1 GCM/IT/P/O d-? s:- a? C++$ UBLA++ P+ L++ E--- W+++$ N o+ K- w O M- V- PS PE Y PGP- t+ 5 X+++ R+* tv b+ DI+++ D+ G+ e h+ r+ y++ --END GEEK CODE BLOCK-- -- 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?
Ariel Sánchez Mora [EMAIL PROTECTED] wrote on 03/06/2006 03:12:20 PM: When using select into outfile, I can only get the table data, but I can't find how to include the column names. I haven't been able to include the column names into the actual return of the query, and they don't get stored in the .csv Anyone know how to include the column names, table name, other info, as an actual answer from MySQL? I realize that when I use the command line I can see the column names, but this is not the case when using, for example, Delphi, and my real problem, when the outfile is made. I use SQLyog for exporting to csv and they do include the columns, my bet is its some SQL option. The manual doesn't specify, at least in the select syntax part. Ariel As far as I am aware, INTO OUTFILE does not have a setting where you can tag the first line with the column names. You may need to use something else like the CSV storage engine or mysqldump to get the results you want. Or, as a last resort, you may have to create your own table export routine. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Getting every other value in a select
[EMAIL PROTECTED] wrote on 03/02/2006 06:55:14 AM: This might be a bit odd, but here we go.. I have some data in a table that has the following structure: CREATE TABLE `gaugereportinglist` ( `Updated` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, `StartTimeAndDate` datetime NOT NULL, `Time_Offset` double default NULL, `OutgoingPcntGgeDev` float default NULL, `IncomingPcntGgeDev` float default NULL, `MillSpeed` float default NULL, `PassNumber` int(2) default NULL, KEY `STADIndex` (`StartTimeAndDate`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 The data is logged once a second. The StartTimeAndDate will be the same for the particular workpiece that I am interested in. I pull out the data with a select statement such as select `OutgoingPcntGgeDev` from gaugereportinglist where `StartTimeAndDate`=2006-03-02 09:36:09 This can give me, say, 3 to 4 thousand rows. I am using BIRT (www.eclipse.org/birt) to do my reporting. These value are shown on a graph. However 3000 points on a small graph on a web page is a little over the top, and takes a long time to plot. Is there any way to select say, every 10th point without doing anything on the client side? You can do it if you store your current results into a temporary table with an auto_increment column in it. Then you can run a query on your temporary table looking for rows where MOD(auto_inc_column_name,10) =0 The MOD() operator returns the remainder that comes from dividing the first parameter by the second. http://dev.mysql.com/doc/refman/4.1/en/mathematical-functions.html If you wanted to get every 20th term, just change the 10 to a 20. See the pattern? Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: dumping results of a select
2wsxdr5 [EMAIL PROTECTED] wrote on 03/02/2006 12:30:17 PM: Is there a way with mysqldump to instead of dumping the contents of a table dump the contents of a select so if you import that sql back in you will get a new table that looks like the select? -- Chris W KE5GIX Gift Giving Made Easy Get the gifts you want give the gifts they want One stop wish list for any gift, from anywhere, for any occasion! http://thewishzone.com I don't think so but if all you wanted to do was to create a new table out of the results you could say CREATE TABLE newtable SELECT ... I use it all the time to great effect. Documentation is near the bottom of this page: http://dev.mysql.com/doc/refman/4.1/en/create-table.html Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Want mysql to return tablename.fieldname format
Ryan Stille [EMAIL PROTECTED] wrote on 03/02/2006 12:42:01 PM: I am working with an existing compilcated query someone wrote years ago. When I dump the data from the query to try to figure out why I'm getting unexpected data, I have three fields named id. Is there anyway to tell mysql to name the fields with the table name when they are returned, so they show up as ads.id, track.id, etc? -Ryan I am not aware of that being an option unless you provide your own aliases in the SELECT clause of your query. Select c.id as customer.id, c.name as customer.name, ct.name as contact.name FROM customer c INNER JOIN contact ct on ct.customer_id = c.id; Sorry! Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: TimeStamp issue
In fact, no time values in MySQL are fractional (yet). All times are stored to the nearest second regardless of which date-time-like storage type you use. They way Rhino phrased his answer, it sounded as though TIMSTAMP would save fractional seconds. It doesn't. He is spot on about needing a separate column to store any values that represent fractions of seconds. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Rhino [EMAIL PROTECTED] wrote on 03/02/2006 01:25:36 PM: If you need a broader range of dates, you could use DATETIME instead of TIMESTAMP: DATETIME can handle the range '1000-01-01 00:00:00' through '-12-31 23:59:59'. The only big difference is that DATETIME does not store the fractional part of the seconds, e.g. milliseconds/microseconds/nanonseconds. If you have to keep the fractional part of the seconds, you could store them in a second column defined as some kind of integer. -- Rhino - Original Message - From: rtroiana [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Thursday, March 02, 2006 10:10 AM Subject: TimeStamp issue Hi All, I have recently noticed in the MySQL 5.0 documentation in section 11.3.1. The DATETIME, DATE, and TIMESTAMP Types, it's mentioned that TIMESTAMP values cannot be earlier than 1970 or later than 2037. This means that a date such as '1968-01-01', while legal as a DATETIME or DATE value, is not valid as a TIMESTAMP value and is converted to 0. Is that a correct range for TimeStamp? It's not big enough to be used in a real life application. I plan to use DATETIME instead of TIMESTAMP. I used to use CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP as default value for my TimeStamp column. Is there a way to assign default value to a DateTime column, since I couldn't find that in the documentation? Thanks, Reema No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 268.1.1/272 - Release Date: 01/03/2006 -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 268.1.1/272 - Release Date: 01/03/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Does MySQL have the ability to send resultsets from stored procedures?
J A [EMAIL PROTECTED] wrote on 03/01/2006 11:04:50 AM: Does MySQL have the ability to send resultsets from stored procedures? (similar to ref cursors in Oracle). _ FREE pop-up blocking with the new MSN Toolbar – get it now! http://toolbar.msn.click-url.com/go/onm00200415ave/direct/01/ It depend on how you call the stored procedure if your client library can handle a resultset or not. If you treat it like a reqular query, most client libraries have little problem accepting the results of a SELECT statement called from within your SPROC. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: What is the rowsize limitation in MySQL?
J A [EMAIL PROTECTED] wrote on 03/01/2006 11:06:27 AM: What is the rowsize limitation in MySQL? _ Don’t just search. Find. Check out the new MSN Search! http://search.msn.click-url.com/go/onm00200636ave/direct/01/ I checked at: http://dev.mysql.com/doc/refman/5.0/en/table-size.html http://dev.mysql.com/doc/refman/5.0/en/legal-names.html http://dev.mysql.com/doc/refman/5.0/en/create-table.html but didn't see a definitive answer. Anyone else find one? It may be related to max_allowed_packet http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Support for temporary tables inside stored procedures?
J A [EMAIL PROTECTED] wrote on 03/01/2006 11:08:10 AM: Does MySQL have support for temporary tables inside stored procedures? _ Express yourself instantly with MSN Messenger! Download today - it's FREE! http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/ Yes, you can create and destroy temporary tables from within stored procedures. Remember that all temporary tables and user variables are connection-specific. You have to maintain the connection between SPROC executions to have the results of one SPROC be available to the next. Drop a connection and your user variables and temporary tables created by that connection go away (garbage collected by the server). The obverse situation is also true. Using a pooled connection may result in object carry-over from one process to another (because returning the connection to the pool does may not actually drop the connection). You should always destroy any temporary object when you are through using it and always initialize your variables before usage. That way you don't accidentally hand old data off to following threads and you don't inherit previous threads state values (unless you really want them). Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Replication from multiple masters?
MySQL cannot handle more than one incoming binlog at a time. The facilities are just not in the code. You also run into a nightmare if a database exists on BOTH masters (same name on both systems) and the PK values of any tables (also with matching names) overlap. If both masters update the same row at appx the same time, we could run into deadlocking in the slave that didn't happen on either master. It also means that the slave and at least one of the masters will become out of sync (because the other master's changes remain in the database) and replication is considered broken at that point. It's a serious can of worms to handle multi-master replication. Your two instances on one matching replicating to two separate masters is not a multi-master replication (more than one master replicating with a single slave) it's two single-master slave setups running on the same machine. Close but not quite what the original post was looking for (I don't think). Shawn Green Database Administrator Unimin Corporation - Spruce Pine David Griffiths [EMAIL PROTECTED] wrote on 03/01/2006 04:34:26 PM: That's not entirely true. You can have two instances of mysql running on the slave, and dbA connects to one instance, and dbB connects to the other. Jeff, when you say, different databases, do you mean that each master has a single mysql instance, and if you typed on M1, show databases you'd see (for example), dbA and if you did the same on M2, you'd see, dbB? If so, I wonder if there is another way to get around it: - create a virtual IP address that represents both masters. Use that virtual master in the my.cnf on the slave; each master has to have an identical replication account - put dbA and dbB on the slave - restrict replication from each master to their respective databases - dbA and dbB - ie don't replicate changes to the mysql database. The two masters appear as one (which overcomes the single-IP-address in the slave's my.cnf file), and each master has a different database inside the mysql instance, they aren't stepping on each others toes. Just my 2 cents. David. Greg Donald wrote: On 3/1/06, Jeff [EMAIL PROTECTED] wrote: Does anyone know if it's possible to replicate to a single slave from different databases on different masters? For instance: M1:dbAM2:dbB \ / rep rep \ / Slave http://dev.mysql.com/doc/refman/5.1/en/replication-features.html snipMySQL only supports one master and many slaves./snip -- Greg Donald Zend Certified Engineer MySQL Core Certification http://destiney.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: bind-address by name under 5.0.18
Yes, there is a very good, not so technical reason to only allow binding by IP Address. Have you ever heard of putting the cart before the horse ? If binding by hostname were allowed, that would mean that every time your MySQL server started up, it would need to somehow resolve that name into an address. In order to do that, it needs a socket it can use to contact a DNS server in order to change the hostname back into an address. Where does it get the socket? Without a socket, it cannot contact a DNS server so it cannot resolve it's hostname to an address and it will not be able to bind to a socket. Binding is the process of giving a socket (an address and a port) to an application so that it has a point of contact on the internet. Without a socket an application has no way to communicate over TCP/IP or UDP/IP as there would be nowhere for the return messages to land. That is the simple reason why you cannot bind to a hostname. Did I make sense? Shawn Green Database Administrator Unimin Corporation - Spruce Pine James Long [EMAIL PROTECTED] wrote on 02/27/2006 03:59:14 PM: It doesn't allow to bind to a hostname. According to http://dev.mysql.com/doc/refman/5.0/en/server-options.html you have to specify an IP address, not a hostname. -Sheeri Thanks for your reply. Yes, I am aware of that, as stated in my question. Wouldn't it be an improvement if it did, though? Currently, I have to use an ugly grep/awk hack to grep the address out of /etc/hosts. Are there technical reasons that make it difficult to write the code such that one could specify an IP or a hostname, or a list of IPs and/or hostnames? I'm looking for something similar to PostgreSQL's: -h hostname Specifies the IP host name or address on which the postmaster is to listen for TCP/IP connections from client applications. The value can also be a space-separated list of addresses, or * to specify listening on all available interfaces. An empty value specifies not listening on any IP addresses, in which case only Unix-domain sockets can be used to connect to the postmaster. Defaults to listening only on localhost. Specifying this option is equivalent to setting the listen_addresses configuration parameter. On 2/22/06, James Long [EMAIL PROTECTED] wrote: I would like to be able to bind to a host name, rather than an IP number. IP numbers come and go, and are beyond the control of anyone who doesn't have their own direct allocation. But since I own my domain, a host name is more permanent. When the time comes to change IP numbers, I want to just edit /etc/hosts to show my new IP: 10.0.0.10 shortname myrealname.example.com and use --bind-address=shortname in my start-up script to automatically pick up the new IP number. This eliminates the headache of having to find and fix umpteen literal IP numbers hiding in my startup scripts. This is much the same reason one says '#define BLKSIZ 512' and then uses the constant 'BLKSIZ' instead of sprinkling literal '512's throughout one's code). Why doesn't bind-address allow this? Or if it does, how can I accomplish this? Thanks! Jim -- 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: bind-address by name under 5.0.18
Oh, no. I know about etc/hosts (even Windoze boxes has one). It's just normally not available for reference until you bind your socket library to at least one socket. Again, it's a cart and horse thing. You see, the etc/hosts file counts as a local DNS server and the DNS protocol requires a socket ( I am not aware of any IP stack that will allow an etc/hosts lookup until you have at least one socket established (though I could be wrong). Shawn Green Database Administrator Unimin Corporation - Spruce Pine James Long [EMAIL PROTECTED] wrote on 02/27/2006 04:26:37 PM: You seem to be unaware of /etc/hosts. Yes, there is a very good, not so technical reason to only allow binding by IP Address. Have you ever heard of putting the cart before the horse ? If binding by hostname were allowed, that would mean that every time your MySQL server started up, it would need to somehow resolve that name into an address. In order to do that, it needs a socket it can use to contact a DNS server in order to change the hostname back into an address. Where does it get the socket? Without a socket, it cannot contact a DNS server so it cannot resolve it's hostname to an address and it will not be able to bind to a socket. I can't speak for your machine, but on my servers, DNS is up before MySQL starts. Binding is the process of giving a socket (an address and a port) to an application so that it has a point of contact on the internet. Without a socket an application has no way to communicate over TCP/IP or UDP/IP as there would be nowhere for the return messages to land. That is the simple reason why you cannot bind to a hostname. Did I make sense? Yes, but none of the information was new. Shawn Green Database Administrator Unimin Corporation - Spruce Pine James Long [EMAIL PROTECTED] wrote on 02/27/2006 03:59:14 PM: It doesn't allow to bind to a hostname. According to http://dev.mysql.com/doc/refman/5.0/en/server-options.html you have to specify an IP address, not a hostname. -Sheeri Thanks for your reply. Yes, I am aware of that, as stated in my question. Wouldn't it be an improvement if it did, though? Currently, I have to use an ugly grep/awk hack to grep the address out of /etc/hosts. Are there technical reasons that make it difficult to write the code such that one could specify an IP or a hostname, or a list of IPs and/or hostnames? I'm looking for something similar to PostgreSQL's: -h hostname Specifies the IP host name or address on which the postmaster is to listen for TCP/IP connections from client applications. The value can also be a space-separated list of addresses, or * to specify listening on all available interfaces. An empty value specifies not listening on any IP addresses, in which case only Unix-domain sockets can be used to connect to the postmaster. Defaults to listening only on localhost. Specifying this option is equivalent to setting the listen_addresses configuration parameter. On 2/22/06, James Long [EMAIL PROTECTED] wrote: I would like to be able to bind to a host name, rather than an IP number. IP numbers come and go, and are beyond the control of anyone who doesn't have their own direct allocation. But since I own my domain, a host name is more permanent. When the time comes to change IP numbers, I want to just edit /etc/hosts to show my new IP: 10.0.0.10 shortname myrealname.example.com and use --bind-address=shortname in my start-up script to automatically pick up the new IP number. This eliminates the headache of having to find and fix umpteen literal IP numbers hiding in my startup scripts. This is much the same reason one says '#define BLKSIZ 512' and then uses the constant 'BLKSIZ' instead of sprinkling literal '512's throughout one's code). Why doesn't bind-address allow this? Or if it does, how can I accomplish this? Thanks! Jim -- 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] --=_alternative 0075136A85257122_= Content-Type: text/html; charset=US-ASCII brfont size=2 face=sans-serifYes, there is a very good, not so technical reason to only allow binding by IP Address. nbsp;Have you ever heard of quot;putting the cart before the horsequot; ?/font br brfont size=2 face=sans-serifIf binding by hostname were allowed, that would mean that every time your MySQL server started up,
Re: Updating Index Statistics
Robert DiFalco [EMAIL PROTECTED] wrote on 02/27/2006 04:53:06 PM: Is there a command to regenerating the selectivity statistics of indices with MySQL? Or does MySQL not have a cost based optimizer and this would make no difference? R. MySQL uses a cost-based optimizer http://dev.mysql.com/doc/refman/4.1/en/query-speed.html and the command you are looking for is ANALYZE TABLE http://dev.mysql.com/doc/refman/4.1/en/analyze-table.html Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Problem revoking all privileges
Twinkletoes [EMAIL PROTECTED] wrote on 02/24/2006 04:18:18 PM: I'm using MySQL 4.1 (I think... a late 4 anyway) on a Gentoo box. It's only a semi-live situation and no need for security just yet. After messing with privileges a bit, I decided to revoke whatever privileges are already applied and specifically grant only the ones I need (using a shell script). To revoke, the obvious choice was to use (if I can remember it correctly): mysql revoke ALL on *.* from '%' However, I'm getting an error about that privilege not applied, and I've read it's because ALL isn't an alias for all privileges, it's a privilege in itself. I think this is the reason I can't remove it! So, the question is, how can I revoke all the permissions that have been applied to a database (recursing into table/columns etc.) ? Many thanks, Steve :) -- View this message in context: http://www.nabble.com/Problem- revoking-all-privileges-t1183423.html#a3115859 Sent from the MySQL - General forum at Nabble.com. If you had checked the manual, you would have seen that your statement's syntax is missing the name of the user whose privileges you are trying to revoke. REVOKE ALL ON *.* FROM 'username'@'%'; is what you are looking for. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Inner join with left join
James Harvard [EMAIL PROTECTED] wrote on 02/22/2006
08:53:56 PM:
At 5:08 pm -0800 22/2/06, Scott Haneda wrote:
I think we are close, thanks
ERROR 1120: Cross dependency found in OUTER JOIN. Examine your ON
conditions
SELECT p.id, p.prod_name, sum(oi.quantity) as qty
FROM Products p
LEFT JOIN orders as o
ON (p.id = oi.product_id)
Maybe this is where your problem is - you're joining to orders but
referencing order_items in your join condition. Shurely shome mishtake?*
AND o.created BETWEEN 2005-01-01 00:00:00 AND
2006-02-22 23:59:59
AND o.status not IN ('cancelled', 'pending', 'ghost')
LEFT JOIN order_items as oi
ON (o.id = oi.order_id)
GROUP BY p.id
ORDER by qty ASC
* ask a Brit, or consult http://en.wikipedia.
org/wiki/Private_Eye#Examples_of_humour
You're right. It was a dumb cut-and-paste mistake.
LEFT JOIN orders as o
on o.product_id = p.id
If fixing this doesn't give the correct results: What's missing? What's
incorrect? Please help us to help you.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Re: Query returns to many results
Schalk [EMAIL PROTECTED] wrote on 02/23/2006 08:55:01 AM: George Law wrote: Schalk , You need to specify the unifying column between your ablb and abm tables. ie - in your where, and ablb.id=abm.id Once you get this so it returns expected results, you can run the query, prefaced with explain and it will give you an idea on the way mysql is running the query. This has helped me determine some additional indexes that greatly speed up my queries. -- George - Original Message - From: Schalk [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Thursday, February 23, 2006 6:59 AM Subject: Query returns to many results Greetings All, Please have a look at the following query: SELECT abm.mem_number, abm.first_name, abm.last_name, abm.area_represented, abm.age, abm.sex, abm.cup, ablb.*, ablb.jp + ablb.rc + ablb.fsmgp + ablb.gmc + ablb.saly + ablb.nwgp + ablb.ecgp + ablb.sams + ablb.wcc + ablb.kzngp + ablb.emc + ablb.lmgp + ablb.saff + ablb.gmgp + ablb.safy + ablb.mmw + ablb.lc + ablb.mmc + ablb.nwmc + ablb.ncc + ablb.samp + ablb.gsc + ablb.wcmgp + ablb.sapm + ablb.kznc + ablb.npc + ablb.smc + ablb.ecc + ablb.mgp + ablb.samo + ablb.cofc + ablb.cs + ablb.ncmgp + ablb.fsc + ablb.ggp + ablb.tmc + ablb.gc + ablb.yotm AS total_points FROM ab_leader_board ablb, ab_members abm WHERE abm.sex = 'Female' AND abm.cup = 'kids' ORDER BY total_points DESC Now this query is run over two tables and the ab_members table contains around 302 rows. Around 1/3 of these will be where cup=kids. However, when this query is run it returns 20,700 results :0 Any idea why this is? Also, any help or pointers as to how I can optimize this query will be much appreciated. Thank you! Thanks George! It works perfectly. Now to optimize this bugger. -- Kind Regards Schalk Neethling Web Developer.Designer.Programmer.President Volume4.Business.Solution.Developers Schalk, You wouldn't have even run into this as an issue if you had used the explicit JOIN form. Again, I blame the documentation for only demonstrating the lazy form of INNER JOIN declaration almost exclusively. I believe that by only demonstrating the comma-separated join, they have created the impression that it is a preferred method. I strongly discourage the use of that form of declaring table joins for the very reason you posted. If you had used the explicit form: SELECT ...(all of your columns)... FROM ab_leader_board ablb INNER JOIN ab_members abm ON ablb.id=abm.id (or whatever is appropriate) WHERE ... It should have be intuitively obvious that you had left out the ON clause from your original query. As it was, your missing JOIN conditions were just not noticed because of all of the other activity in your whole statement. This is a very frequent problem with the join syntax you used in your original query. Again, I implore all SQL coders to use the explicit JOIN syntax on all platforms that support it (Oracle being a well-known exception). It makes it much easier to catch logical errors just like Schalk ran into in his original post. The explicit form is also the only way to declare outer joins in MySQL so you will have to use it sooner or later. Please, again, I ask the documentation team to modify the SQL examples in the manual (especially in the tutorial section) to use the explicit JOIN forms. Humbly yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: linking rows
Tim Johnson [EMAIL PROTECTED] wrote on 02/23/2006 12:26:35 PM: Let's say I have a query that performs select * from Account one of the columns from Account (city) is actually a key which may be either numeric or character. There is a table called City which contains keys and names of cities. and I want the City.Name value where Account.city matches City.ID. I need the most efficient way to do this: examples, keywords, and URLs to relevant documentation are all welcome. thanks tim -- Tim Johnson [EMAIL PROTECTED] http://www.alaska-internet-solutions.com First off, columns can either be numeric or character-based not both. You can store arrangements of the characters 0 through 9 in a character-based field but those are not numbers, they are strings that look like numbers. Your description makes it sound like you have a table that has data in a column called 'city' that looks like: Atlanta Boston 15 10 24 Paris Rome 215 Tokyo or am I mistaken? Back to your direct question: How you link two tables is called joining. There are tons of examples and tutorials of how to join tables. One of my favorites is http://sqlzoo.net/ It takes you through everything you need in order to get your feet wet and it gives you the ability to immediately try out what you are being taught. If you work through their examples you should be able to make some decent headway. I also recommend you read this article about normalization: http://dev.mysql.com/tech-resources/articles/intro-to-normalization.html There is a query that can do what you want but I strongly suggest you review your design before moving too much farther into this project. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Inner join with left join
Sorry - I am trying to cut back to just 2 pots of coffee per day and I the
lack of caffeine can make me a little fuzzy :-) Thank you for being
patient with me.
You have a working query, we just need to convert your INNER JOINs to
LEFT JOINs and move your join-specific WHERE conditions into the correct
ON clauses
SELECT p.id, p.prod_name, sum(oi.quantity) as qty
FROM products as p
INNER JOIN order_items as oi
ON (p.id = oi.product_id)
INNER JOIN orders as o
ON (o.id = oi.order_id)
AND o.status NOT IN ('cancelled', 'pending', 'ghost')
AND (o.created BETWEEN 2006-02-23 00:00:00 AND 2006-02-23 23:59:59)
GROUP BY p.id, p.prod_name
By placing a restriction in the WHERE clause, you are requiring a value
exist in that column after the JOINs are computed. That is why you have
been throwing out all unsold products before you even got to the GROUP BY
stage. You cannot group on values that aren't going to be there so I
moved the two important columns of your SELECT statement back to the
products table (SELECT p.id, p.prod_name ...) and made sure that those
were the values you were grouping by.
Again, Thanks!
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Scott Haneda [EMAIL PROTECTED] wrote on 02/23/2006 12:45:28 PM:
You're right. It was a dumb cut-and-paste mistake.
LEFT JOIN orders as o
on o.product_id = p.id
If fixing this doesn't give the correct results: What's missing?
What's
incorrect? Please help us to help you.
Orders does not have a product_id column.
Let me see if I can explain this again, more better :-)
We have orders and order items, so for every orders, there are 1 or more
order items, pretty basic. This SQL gets me almost what I want:
SELECT p.id, oi.prod_name, sum(oi.quantity) as qty
FROM products as p
INNER JOIN order_items as oi
ON (p.id = oi.product_id)
INNER JOIN orders as o
ON (o.id = oi.order_id)
WHERE o.status NOT IN ('cancelled', 'pending', 'ghost')
AND
(o.created BETWEEN 2006-02-23 00:00:00 AND 2006-02-23 23:59:59)
GROUP BY oi.product_id
However, there are
mysql select count(*) from products;
+--+
| count(*) |
+--+
| 109 |
+--+
1 row in set (0.00 sec)
So, 109 products in the products database, the first SQL above, will
give me
back a row for every order item that meets those criteria, however, it
does
not list products that were not ordered.
If I changed the first SQL to a date 10 years ago, I would get 0 rows, I
want 109 where the sum() is all 0.
Basically, my client is wanting to see what products are selling, and
which
ones are not, in a certain date range, and I need to add in the status
to
limit it to only certain orders.
Running these three SQL's does what I want, with a temp table, but I
find
the solution kinda strange, and know it can be done in one go:
CREATE TEMPORARY TABLE prod_report
SELECT p.id, oi.prod_name, sum(oi.quantity) as qty
FROM products as p
INNER JOIN order_items as oi
ON (p.id = oi.product_id)
INNER JOIN orders as o
ON (o.id = oi.order_id)
WHERE o.status NOT IN ('cancelled', 'pending', 'ghost')
AND
(o.created BETWEEN 2006-02-23 00:00:00 AND 2006-02-23 23:59:59)
GROUP BY oi.product_id
INSERT INTO prod_report (SELECT id, prod_name, 0 FROM products)
SELECT * FROM prod_report GROUP BY id ORDER BY prod_name
--
-
Scott HanedaTel: 415.898.2602
http://www.newgeo.com Novato, CA U.S.A.
Re: query help?
If you are looking just for duplicate (ID,vendort_no) combinations, this will find them: SELECT ID, vendor_no, count(1) as dupes FROM table_name_here GROUP BY ID, vendor_no HAVING dupes 1; Shawn Green Database Administrator Unimin Corporation - Spruce Pine Richard Reina [EMAIL PROTECTED] wrote on 02/23/2006 12:49:28 PM: I's so sorry. You are very correct. The sample data is bad. ID should be unique. Here it is corrected. |ID| vendor_no| date| |2354 | 578 | 2005-12-23| |2355 | 334 | 2005-12-24| |2356 | 339 | 2005-12-26| |2357 | 339 | 2005-12-26| |2358 | 339 | 2005-12-26| |2359 | 445 | 2005-12-26| |2360 | 522 | 2005-12-27| |2361 | 522 | 2005-12-27| [EMAIL PROTECTED] wrote: I am a novice when it come to queries such as this and was hoping someone could help me write a query that tells me how many records have the same ID and vendor number. |ID| vendor_no| date| |2354 | 578 | 2005-12-23| |2355 | 334 | 2005-12-24| |2356 | 339 | 2005-12-26| |2357 | 339 | 2005-12-26| |2358 | 339 | 2005-12-26| |2359 | 445 | 2005-12-26| |2354 | 522 | 2005-12-27| |2355 | 522 | 2005-12-27| Would I use select count? Any help would be greatly appreciated. I'd need a clearer spec to offer advise. What results would you want from the example data? 2354 is there twice but with different vendor numbers. And 522 is there twice with different IDs. One interpretation of [records with] the same ID and vendor number is 0 because no record has both the same as any other. A people that values its privileges above its principles soon loses both. -Dwight D. Eisenhower.
Re: Inner join with left join
I hate remembering crap like this AFTER I hit send...
Because we want to limit our sum() to only those rows that match the ORDER
conditionals, we have to change our formula to recognized when to count
and when to not count an order_item.
SELECT p.id, p.prod_name, sum(if(o.id is null,0,oi.quantity)) as qty
FROM products as p
INNER JOIN order_items as oi
ON (p.id = oi.product_id)
INNER JOIN orders as o
ON (o.id = oi.order_id)
AND o.status NOT IN ('cancelled', 'pending', 'ghost')
AND (o.created BETWEEN 2006-02-23 00:00:00 AND 2006-02-23 23:59:59)
GROUP BY p.id, p.prod_name
I think I need a nap! --- SORRY!
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
[EMAIL PROTECTED] wrote on 02/23/2006 01:01:17 PM:
Sorry - I am trying to cut back to just 2 pots of coffee per day and I
the
lack of caffeine can make me a little fuzzy :-) Thank you for being
patient with me.
You have a working query, we just need to convert your INNER JOINs to
LEFT JOINs and move your join-specific WHERE conditions into the correct
ON clauses
SELECT p.id, p.prod_name, sum(oi.quantity) as qty
FROM products as p
INNER JOIN order_items as oi
ON (p.id = oi.product_id)
INNER JOIN orders as o
ON (o.id = oi.order_id)
AND o.status NOT IN ('cancelled', 'pending', 'ghost')
AND (o.created BETWEEN 2006-02-23 00:00:00 AND 2006-02-23 23:59:59)
GROUP BY p.id, p.prod_name
By placing a restriction in the WHERE clause, you are requiring a value
exist in that column after the JOINs are computed. That is why you have
been throwing out all unsold products before you even got to the GROUP
BY
stage. You cannot group on values that aren't going to be there so I
moved the two important columns of your SELECT statement back to the
products table (SELECT p.id, p.prod_name ...) and made sure that those
were the values you were grouping by.
Again, Thanks!
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Scott Haneda [EMAIL PROTECTED] wrote on 02/23/2006 12:45:28 PM:
You're right. It was a dumb cut-and-paste mistake.
LEFT JOIN orders as o
on o.product_id = p.id
If fixing this doesn't give the correct results: What's missing?
What's
incorrect? Please help us to help you.
Orders does not have a product_id column.
Let me see if I can explain this again, more better :-)
We have orders and order items, so for every orders, there are 1 or
more
order items, pretty basic. This SQL gets me almost what I want:
SELECT p.id, oi.prod_name, sum(oi.quantity) as qty
FROM products as p
INNER JOIN order_items as oi
ON (p.id = oi.product_id)
INNER JOIN orders as o
ON (o.id = oi.order_id)
WHERE o.status NOT IN ('cancelled', 'pending', 'ghost')
AND
(o.created BETWEEN 2006-02-23 00:00:00 AND 2006-02-23 23:59:59)
GROUP BY oi.product_id
However, there are
mysql select count(*) from products;
+--+
| count(*) |
+--+
| 109 |
+--+
1 row in set (0.00 sec)
So, 109 products in the products database, the first SQL above, will
give me
back a row for every order item that meets those criteria, however, it
does
not list products that were not ordered.
If I changed the first SQL to a date 10 years ago, I would get 0 rows,
I
want 109 where the sum() is all 0.
Basically, my client is wanting to see what products are selling, and
which
ones are not, in a certain date range, and I need to add in the status
to
limit it to only certain orders.
Running these three SQL's does what I want, with a temp table, but I
find
the solution kinda strange, and know it can be done in one go:
CREATE TEMPORARY TABLE prod_report
SELECT p.id, oi.prod_name, sum(oi.quantity) as qty
FROM products as p
INNER JOIN order_items as oi
ON (p.id = oi.product_id)
INNER JOIN orders as o
ON (o.id = oi.order_id)
WHERE o.status NOT IN ('cancelled', 'pending', 'ghost')
AND
(o.created BETWEEN 2006-02-23 00:00:00 AND 2006-02-23 23:59:59)
GROUP BY oi.product_id
INSERT INTO prod_report (SELECT id, prod_name, 0 FROM products)
SELECT * FROM prod_report GROUP BY id ORDER BY prod_name
--
-
Scott HanedaTel: 415.898.2602
http://www.newgeo.com Novato, CA U.S.A.
Re: parameterized view ?
[EMAIL PROTECTED] wrote on 02/21/2006 11:20:36 AM: Is there such a thing in mySQL ? I could not find anything anywhere. thanks, laszlo Short answer: No Long answer: MySQL has VIEWS but they are non-parameterized and non-persistent. MySQL also has FUNCTIONS and STORED PROCEDURES, both of them take parameters but are not exactly views. There have been suggestions to make parameterized views and persistent views but I am not sure where those projects are on the TODO lists (I have had to cut back on my list time and haven't been able to lurk the commits list like I had been so I am a few weeks out of date as far as current project statuses.) Sorry but you will probably need to make a SPROC work for what you need. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Inner join with left join
Scott Haneda [EMAIL PROTECTED] wrote on 02/22/2006 01:47:38 AM:
Got myself a little stumped here, 4.0.18-standard
Three tables in this mess, orders, order_items and products.
orders.prod_id = order_items.prod_id = products.prod_id is how I relate
them
all to each other.
order_items has a quantity field.
I need a report that shows me all the products with a sum() for each,
but
only if the status of the order is NOT IN ('cancelled', 'pending',
'ghost')
This gets me pretty close, but does not mask out the orders that have
the
wrong status, as I do not know how to add in the join on orders
SELECT p.prod_name, count(oi.product_id) as mycount
FROM products as p
LEFT JOIN order_items as oi
on (p.id = oi.product_id)
group by oi.product_id order by mycount;
--
-
Scott HanedaTel: 415.898.2602
http://www.newgeo.com Novato, CA U.S.A.
Shouldn't there be a relationship between orders and order_items like
`orders`.`id` = `order_items`.`order_id`
or something similar? That would make better sense to me Making each
`order` product-specific doesn't sound like a good design especially when
each `order` looks like it can contain multiple `order_item`s, each
potentially for a different `product`.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Re: MySQL Queries within Oscommerce
AM COMS [EMAIL PROTECTED] wrote on 02/22/2006 09:16:24 AM: Has anyone here had any experience with Oscommerce? I am having problems with the style of queries they have used or am I just seeing things the wrong way! andrew Sorry! I have never used it. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Inner join with left join
Scott Haneda [EMAIL PROTECTED] wrote on 02/22/2006 03:58:10 PM:
Is this what you mean?
SELECT
p.prod_name,
count(oi.product_id) AS mycount
FROM ORDERS AS o
INNER JOIN products ON o.id=p.id
LEFT JOIN order_items AS oi ON (p.id = oi.product_id)
WHERE o.status NOT IN ('cancelled', 'pending', 'ghost')
GROUP BY oi.product_id
ORDER BY mycount;
Well, sort of, here is what I managed to coble together, which gets me
pretty close, it is just what I want, other than it is missing products
with
a zero count. This tells me those products have not been ordered ever,
but
I would like to know what they are.
SELECT o.id, oi.prod_name, sum(oi.quantity) as qty
FROM orders as o
INNER JOIN order_items as oi
ON (o.id = oi.order_id)
LEFT JOIN products as p
ON (p.id = oi.product_id)
WHERE o.status NOT IN ('cancelled', 'pending', 'ghost')
AND
(o.created BETWEEN 2005-01-01 00:00:00 AND 2006-02-22 23:59:59)
GROUP BY oi.product_id
ORDER by qty ASC
You just need to invert a couple of things...
SELECT p.id, p.prod_name, sum(oi.quantity) as qty
FROM Products p
LEFT JOIN orders as o
ON (p.id = oi.product_id)
AND o.created BETWEEN 2005-01-01 00:00:00 AND 2006-02-22
23:59:59
AND o.status not IN ('cancelled', 'pending', 'ghost')
LEFT JOIN order_items as oi
ON (o.id = oi.order_id)
GROUP BY p.id
ORDER by qty ASC
That should give you a list of all products and a count of how many have
been ordered between 2005-01-01 and 2006-02-22 23:59:59 where the status
of the order is neither 'cancelled', 'pending', or 'ghost'.
The think to remember is that an ON clause can be as complex as a WHERE
clause. The ON clause also determines which rows of which table
participate in a JOIN. In this case the only table to be affected will be
the one on the right side of a LEFT join (in an INNER join both tables are
filtered). So you keep all of your products visible (as declared in the
FROM clause) and optionally associate with each product an order and
optionally past that to an order_item.
HTH!
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Re: Change on LEFT JOIN ON syntax in 5.x?
Jan Pieter Kunst [EMAIL PROTECTED] wrote on 02/21/2006 04:54:46 AM: On 2/20/06, Eric Persson [EMAIL PROTECTED] wrote: Hi, I have a query which works fine for me in my 4.1 environment, but when moved to the 5.0.18 environment, it fails with the result below: mysql SELECT r.uid, u.username, u.image_type, count(id) AS antal, s.timestamp FROM recruits_uid r, users u, users u2 LEFT JOIN sessions s ON s.uid=u.uid WHERE r.uid=u.uid AND r.rec_uid=u2.uid AND u2.deleted=0 AND datetime-00-00 00:00:00 GROUP BY r.uid ORDER BY antal DESC LIMIT 100; ERROR 1054 (42S22): Unknown column 'u.uid' in 'on clause' mysql It seems like the table alias u is not recognized for some reason. Does anyone have any hints about this? Yes, try this: LEFT JOIN sessions s ON (s.uid=u.uid) the ON-clause enclosed in parentheses. JP JP, That's not what Gabriel meant when he said to use parentheses The original table ref portion of the original query contained several tables that were implicitly cross joined by commas. Eric will need to use parentheses in order to change the order of operations (the join sequence) to include one of his comma-listed tables first or he will need to rewrite his query to use the explicit JOIN ON syntax for all of the table def components of his table ref clause. The demotion of the comma operator as of 5.0.12 makes MySQL more compliant with the applicable SQL standards. Unfortunately there were many users who blindly followed the examples in the documentation and only learned to use commas when they wanted to declare INNER JOINs. Or, they come from Oracle-like systems who use only commas to declare INNER JOINs (and the outer ones, too). Full details on the change and it's impact on query design can be found here: http://dev.mysql.com/doc/refman/5.0/en/join.html I have requested that the documentation be rewritten so that the explicit forms of JOIN declarations are used more often than the implicit form. I do not know the status of making those changes. Perhaps if other could make their recommendations, fewer of our new users will run into this same issue. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: How to turn off all constraints in a table?
Easier than that (though Sheeri's way would work): ALTER TABLE yourtablename DISABLE KEYS; SET FOREIGN_KEY_CHECKS=0; ... do your processing ... SET FOREIGN_KEY_CHECKS=1; ALTER TABLE yourtablename ENABLE KEYS; http://dev.mysql.com/doc/refman/5.0/en/alter-table.html http://dev.mysql.com/doc/refman/5.0/en/set-option.html Shawn Green Database Administrator Unimin Corporation - Spruce Pine sheeri kritzer [EMAIL PROTECTED] wrote on 02/20/2006 12:28:54 PM: Drop the keys and references, change it, and then re-create the keys and references. http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html (a SHOW CREATE TABLE will show you the constraints including foreign keys -- copy those, so you can create them later. Then you can use ALTER TABLE to drop the keys, change your columns, and then you can use ALTER TABLE to add the keys back in, although it sounds like you won't have a primary key in the new table). -Sheeri On 2/17/06, Tomáš Vichta [EMAIL PROTECTED] wrote: Hello, I need to turn off all constraints in a table (especially primary and foreign keys) - because for example I need to exchange primary key values of two rows in a table. And because of primary key constraints I can do it directly. I would to turn off the constraint, rename value of PK1 to PK2 - now I have the same 2 values PK2 and it's the problem, if the primary key constraint is enabled. Thanx very much for answer, TV. -- 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: (mysqldump) Serial output. . .?
Michael, I have been following this thread from the beginning and I just don't see the practical difference between what you propose and the replication methods (SBR and RBR) already in place. How does what you propose differ from the SBR (statement -based replication) that MySQL already supports? Sorry if I am being dense but don't your source and destination schemas need to stay in synch in order for the changes in one table to be able to apply to the other? Isn't that why you are worried about capturing your schemas as ALTER TABLE statements? With SBR, each time a table is altered on the replication master, that ALTER TABLE statement is inserted in the binlog so that the change propagates to the slaves. The DML (data modification language) statements that follow the ALTER TABLE statement in the binlog won't fail because they will be applied to the correct schema on the slave. Again, my sincere apologies for missing the difference in the purpose of what you are trying to do. Respectfully, Shawn Green Database Administrator Unimin Corporation - Spruce Pine mwilliams [EMAIL PROTECTED] wrote on 02/20/2006 01:36:18 PM: Sheeri, Thanks very much for the reply. However, that is not what I'm looking for. I don't want the tables that *have been* altered. I want CREATE TABLE statements output in the ALTER TABLE format for re-creation on another system (clean or otherwise). Regards, Michael -- Original Message -- From: sheeri kritzer [EMAIL PROTECTED] Date: Mon, 20 Feb 2006 12:22:55 -0500 mysqldump takes a table or database and dumps it -- current schema, current data. You won't get alter tables. What you want is something that will show all the alter statements. You can run something like this on unix: tail -f binlog* | grep ALTER alter.sql and then the alter.sql text file will always have the alter statements. The binary log captures the alter statements. Or, you could create an 'alteration' table with a text field and timestamp, and have a trigger copy the alter statement to the alteration table. But mysqldump is the wrong solution, because it only dumps now. hope this helps! -Sheeri On 2/16/06, mwilliams [EMAIL PROTECTED] wrote: All, I'm looking to output every piece of data from the database line by line. Is there any methody by which 'mysqldump' can output the following?: use MY_DATABASE; CREATE TABLE IF NOT EXISTS MY_TABLE; ALTER IGNORE TABLE MY_TABLE ADD MY_COLUMN [properties] ALTER IGNORE TABLE MY_TABLE ADD MY_COLUMN [properties] ALTER IGNORE TABLE MY_TABLE ADD MY_COLUMN [properties] ALTER IGNORE TABLE MY_TABLE ADD MY_COLUMN [properties] ALTER IGNORE TABLE MY_TABLE ADD MY_COLUMN [properties] ALTER IGNORE TABLE MY_TABLE ADD MY_COLUMN [properties] ALTER IGNORE TABLE MY_TABLE ADD MY_COLUMN [properties] INSERT IGNORE . . . . INSERT IGNORE . . . . INSERT IGNORE . . . . INSERT IGNORE . . . . INSERT IGNORE . . . . INSERT IGNORE . . . . INSERT IGNORE . . . . The most important of the features above are the ability to CREATE a table only if it doesn't exist (I never want to drop because the same script will be used for syncing) and the ability to have 'mysqldump' be smart and output ALTER IGNORE statements. Any asistance would be greatly appreciated. Regards, Michael -- 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: How to turn off all constraints in a table?
Once CHECK CONSTRAINTS are enabled, I am sure there will be a way to turn them off temporarily. Right now, you can create tables that have them but they are not enforced. To quote http://dev.mysql.com/doc/refman/5.0/en/create-table.html InnoDB tables support checking of foreign key constraints. See Section 14.2, “The InnoDB Storage Engine”. Note that the FOREIGN KEY syntax in InnoDB is more restrictive than the syntax presented for the CREATE TABLE statement at the beginning of this section: The columns of the referenced table must always be explicitly named. InnoDB supports both ON DELETE and ON UPDATE actions on foreign keys. For the precise syntax, see Section 14.2.6.4, “FOREIGN KEY Constraints”. For other storage engines, MySQL Server parses and ignores the FOREIGN KEY and REFERENCES syntax in CREATE TABLE statements. The CHECK clause is parsed but ignored by all storage engines. See Section 1.9.5.5, “Foreign Keys”. So, by disabling the keys (which turns off all UNIQUE and PRIMARY keys) and by disabling all FK checks, you have everything covered. :-) Shawn Green Database Administrator Unimin Corporation - Spruce Pine Tomáš Vichta [EMAIL PROTECTED] wrote on 02/21/2006 11:10:42 AM: Great, that's what I need, thanx very much. And is any similar possibility how to disable all constraints? For example CHECK CONSTRAINT type. [EMAIL PROTECTED] wrote: Easier than that (though Sheeri's way would work): ALTER TABLE yourtablename DISABLE KEYS; SET FOREIGN_KEY_CHECKS=0; ... do your processing ... SET FOREIGN_KEY_CHECKS=1; ALTER TABLE yourtablename ENABLE KEYS; http://dev.mysql.com/doc/refman/5.0/en/alter-table.html http://dev.mysql.com/doc/refman/5.0/en/set-option.html Shawn Green Database Administrator Unimin Corporation - Spruce Pine sheeri kritzer [EMAIL PROTECTED] wrote on 02/20/2006 12:28:54 PM: Drop the keys and references, change it, and then re-create the keys and references. http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html (a SHOW CREATE TABLE will show you the constraints including foreign keys -- copy those, so you can create them later. Then you can use ALTER TABLE to drop the keys, change your columns, and then you can use ALTER TABLE to add the keys back in, although it sounds like you won't have a primary key in the new table). -Sheeri On 2/17/06, TomĂĄĹĄ Vichta [EMAIL PROTECTED] wrote: Hello, I need to turn off all constraints in a table (especially primary and foreign keys) - because for example I need to exchange primary key values of two rows in a table. And because of primary key constraints I can do it directly. I would to turn off the constraint, rename value of PK1 to PK2 - now I have the same 2 values PK2 and it's the problem, if the primary key constraint is enabled. Thanx very much for answer, TV. -- 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: (mysqldump) Serial output. . .?
One problem with dual-master or multi-master replication is that you have to be able to set and check a lock across all masters before performing a schema change. How would you deal with this scenario using your ALTER TABLE database dumps without such a lock? Server A and B share a table X that has the following definition CREATE TABLE X ( id int auto_increment ,name varchar(20) not null ,status tinyint ) Simultaneously, separate changes are applied to table X on servers A and B with the following statements: SERVER A: ALTER TABLE X CHANGE status status tinyint unsigned; SERVER B: ALTER TABLE X CHANGE status status int; Without some way to serialize those changes you could possibly get stuck in an endless loop. 1) Server A's sync process detects B's change and applies it. B's sync process detects A's changes and applies them to itself. 2) Now both tables (A.X and B.X) are different again. Synchronization attempts to match schemas again. Repeat step 1) until someone wins. Question: What should be the definition of X on both servers at that point? Which change should have precedence? /end scenario NDB (clustering) is the only MySQL database that supports distributed locking and distributed transactions (making sure that at any one time all replicas of the data are kept in sync across the cluster). SBR replication takes care of the circular reference problem by tagging each DML statement with the originating server. If a server detects that it is attempting to process a statement that it already applied to itself, it quits and moves on to the next statement. Clustering can use both SBR and RBR replication (RBR = row-based replication or row-by-row) No, I do not know of any good system for two-way synching (other than NDB) built on top of MySQL. Again, you haven't explained why your synching plan is that much different than setting up circular replication. In circular replication server A is the master to B and B is the master of A. This is a useful design if you can ensure that you can somehow ensure that each server only issues private id values so that your records remain unique throughout your enterprise. Schema changes must occur with great care. I have worked with several different replicating database servers (MySQL, MS SQL server, Lotus Notes) and each have a different way of handling what they call replication conflicts. Those arise from scenarios very similar to what I described above (changes occur to the same record on separate servers between synchronization cycles). How do you plan to handle those? Shawn Green Database Administrator Unimin Corporation - Spruce Pine mwilliams [EMAIL PROTECTED] wrote on 02/21/2006 12:12:42 PM: Shawn, Thanks for the reply, but I think the thread has become much more dramatic than nececessary. Basically, I'm not looking for what has been ALTERed. I simply need the table creation data output in ALTER IGNORE (or whatever is appropriate) format so as to ensure table structure is the same before performing any INSERTS. I don't wish to DROP tables because that would then require reimporting all data. I simply want to write a tool to perform updates between multiple databases that keep them in two-way sync with their respective DBs (and even then, only specific tables) on a main server, both structure-wise and data-wise. The current systems don't use bin logs (*eyes rolling in back of head*) and we don't need replication, but true two-way syncing. Are there any truly quality two-way replication master techniques that you can recommend? I've been working with SJA and I like it pretty well. I'd like to write my own, similar program, but I think it might just work for now. Regards, Michael -- Original Message -- From: [EMAIL PROTECTED] Date: Tue, 21 Feb 2006 11:18:44 -0500 Michael, I have been following this thread from the beginning and I just don't see the practical difference between what you propose and the replication methods (SBR and RBR) already in place. How does what you propose differ from the SBR (statement -based replication) that MySQL already supports? Sorry if I am being dense but don't your source and destination schemas need to stay in synch in order for the changes in one table to be able to apply to the other? Isn't that why you are worried about capturing your schemas as ALTER TABLE statements? With SBR, each time a table is altered on the replication master, that ALTER TABLE statement is inserted in the binlog so that the change propagates to the slaves. The DML (data modification language) statements that follow the ALTER TABLE statement in the binlog won't fail because they will be applied to the correct schema on the slave. Again, my sincere apologies for missing the difference in the purpose of what you are trying to do. Respectfully, Shawn
Re: (mysqldump) Serial output. . .?
Ok, I get that. I have several tables just like that (I use mine as shadow tables for change audits. Every change to the normal table ends up creating new record in the shadow table thus documenting each state of the normal table through time). However, shouldn't schema changes be very rare under such a design as yours? Such infrequent modifications deserve any special attention it would take to convert the output of a SHOW CREATE TABLE xxx into a sequence of ALTER TABLE statements within whichever programming language you are using to control the synchronization. I mean it's not hard to take the output of a SHOW CREATE TABLE xxx statement and transform it into a sequence of ALTER TABLE statements. Alternatively, you could use the output from a SHOW COLUMNS FROM xxx statement as the important parts are already parsed into separate columns (column name, data type, null or not null, etc.). If the application on A is changed to use a new table design (X2) and that schema change is sent to Server B, how does the application on Server B not break ? In particular, if you created X2 by deleting a column from X, that would cause major problems with the application running on B until B is notified of the change from X to X2, wouldn't it? I am not trying to discourage your design or your plan of attack. I am just trying to help by playing devil's advocate. If you plan works well, it may be something many of us in the community may be interested in trying for some of our data issues, if you can share. We understand if you can't. Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine mwilliams [EMAIL PROTECTED] wrote on 02/21/2006 01:08:52 PM: Ah, well, in this particular DB, *every single transaction* it's it's own entry. . .basically the DB itself is a binary log. . . kinda. . .sorta. So the current value of a particular item isn't necessarily an issue since, once entered, it will always be the same. A change to that value will in itself be a transaction with its own UUID. As for your question regarding how it's any different, I'm not really sure, besides the need to have the server go down or be locked for a period of time. Regards, Michael snipped
Re: confused...
Patrick Duda [EMAIL PROTECTED] wrote on 02/21/2006 02:39:47 PM:
Why, when I create a table as follows:
mysql create table requestid ( request_id int not null default
1, constraint requestid_innodb_pk_cons primary key(request_id) )
ENGINE=InnoDB;
Query OK, 0 rows affected (0.02 sec)
Do I get the following?
mysql select request_id from requestid;
Empty set (0.01 sec)
When I do a show create table I see:
mysql show create table requestid;
+---
+--
+
| Table | Create
Table
|
+---
+--
+
| requestid | CREATE TABLE `requestid` (
`request_id` int(11) NOT NULL default '1',
PRIMARY KEY (`request_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+---
+--
+
1 row in set (0.00 sec)
Shouldn't I be getting back a '1' when I do my select??? Why am I
getting
an empty set? What am I not understanding? How do I create a table
with a
starting value of '1' or '0' for an int???
Thanks
You haven't created any rows yet. That's why you get nothing back from
your SELECT query. With a single-column table like this, it will be
impossible to add a row to the table without providing a value for ID
(because it's the only column). You will never see the default value
because you must always supply one.
The term starting value in your original post implies that you intended
some sort of sequence. Did you want the server to automatically increment
the request_id value for you each time you add a record to this table? If
so, you have to do two things:
1) add more columns to this table
2) change the definition of your ID column to be an auto_increment column.
Here is an example of what your `request` table may look like
CREATE TABLE `request` (
id int not null auto_increment,
details varchar(50) not null,
tsModified timestamp,
PRIMARY KEY(id)
);
and you could add reqests to it like this:
INSERT `request`(`details`) VALUES ('details of your first
request'),('details of a second request'), ('details of a third request');
Is it making any better sense?
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Re: JOINs with result of aggregate function fails with error #1054
Guillaume Boissiere [EMAIL PROTECTED] wrote on 02/21/2006 02:23:29 PM: This must have been asked before but I could not find the answer searching the list archives. I have a simple table: CREATE TABLE `license` ( `id` int(11) NOT NULL auto_increment, `firstname` varchar(100) NOT NULL default '', `lastname` varchar(100) NOT NULL default '', `host_address` varchar(100) NOT NULL default '', `is_visible` tinyint(4) NOT NULL default '1', PRIMARY KEY (`id`) ) TYPE=MyISAM; This first query works (MySQL 4.0.25): SELECT l.id, MAX(l.id) AS maxid, l.host_address FROM `license` l WHERE l.is_visible='1' GROUP BY l.host_address ORDER BY maxid DESC but this second query fails with the error: #1054 - Unknown column 'maxid' in 'on clause' SELECT l.id, MAX(l.id) AS maxid, l.host_address, l2.firstname, l2.lastname FROM `license` l INNER JOIN license l2 ON l2.id=maxid WHERE l.is_visible='1' GROUP BY l.host_address ORDER BY maxid DESC Is there a way to do a join with the result of an aggregate function (in this case MAX(id)) in one query, or do I have to use multiple queries for this? Thanks in advance! Guillaume You you will have to use two queries. You might have been able to use a subquery but your version doesn't support them. The reason for the error is that the MAX() function isn't evaluated until you get to the GROUP BY portion of the query. This is AFTER all tables are JOIN-ed and all of the WHERE restrictions have been applied. The column name `maxid` won't exist until the MAX() value has been calculated so ... you have nothing to join to. Thus the error. sorry! Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: mysqlimport, \r\n and \n
A little less dodgy is to write a VBA routine within Access to do the filtering. Alternatively, if you don't want to trust access to do it, you can write a VBScript or JScript routine and run it through the shell (yes, Windoze has shell scripts, too) There are all kinds of things you can do. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Daniel Kasak [EMAIL PROTECTED] wrote on 02/21/2006 04:13:28 PM: George Law wrote: Daniel, What about just running your import file through dos2unix before piping it to mysql? That way, it would leave \n alone (since that is unix format) and replace any occurrances of \r\n with plan old \n. I could think of a lot of things I could do if this were all running on Linux. Unfortunately the import routine is being triggered from MS Access ... and come to think of it, I'm using 'load data infile' and not 'mysqlimport', but anyway, you get the idea. AFAIK there is no way to trigger anything useful via ODBC. I could write a Perl script, chuck it in my cgi-bin folder, and opening Firefox from Access, passing the script some pointers to the file. But that's dodgy. I suppose while I'm working with Access I'd better get used to dodgy solutions, eh? ;-) -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: APACHE 2.0 can't connect to MYSQL 5 when using PHP 5.1.2
persant mpote [EMAIL PROTECTED] wrote on 02/16/2006 10:29:37 AM: hi, since 3 days, i'm trying to connect to MYSQL 5.0.18 from php scripts using Apache et Macromedia 2004 Dreamweaver. Could someone help me doing this? Best regard. - Nouveau : téléphonez moins cher avec Yahoo! Messenger ! Découvez les tarifs exceptionnels pour appeler la France et l'international. Téléchargez la version beta. What exactly have been the commands you have tried and what exactly were the error messages you received? We can't help you until you provide us with more details. Have you tried the advice in the manual? http://dev.mysql.com/doc/refman/5.0/fr/can-not-connect-to-server.html http://dev.mysql.com/doc/refman/5.0/fr/starting-server.html What successes or failures have you had? Shawn Green Database Administrator Unimin Corporation - Spruce Pine
RE: (mysqldump) Serial output. . .?
My suggestion: Modify the source of mysqldump yourself. After all, it is open source. Make sure you adhere to any and all licensing requirements and copyright notices and you will keep yourself out of any legal trouble. For the vast majority of users, replication is a better solution than what you propose so the changes you propose haven't been discussed at all. If others would like to have your changes, perhaps you would consider synching your mods with the main development tree and releasing them to the community? Shawn Green Database Administrator Unimin Corporation - Spruce Pine mwilliams [EMAIL PROTECTED] wrote on 02/16/2006 03:22:51 PM: Thanks Dathan, But I'm more concerned with ALTER than anything. As I said, I basically want to be able to output field, type, etc. by row so that *if* the receiving DB needs to add a field it can do so without having to nuke the table with all data in it just to add all fields again with a CREATE statement. The final purpose is for the sake of data syncronization, not replication. Regards, Michael -- Original Message -- From: Dathan V. Pattishall [EMAIL PROTECTED] Date: Thu, 16 Feb 2006 12:06:40 -0800 Mysqldump can do all the following except dump the ALTER commands, it will log the create statement of the table as the table exists. Mysql doesn't keep a record of what, when, how a table was altered only the final result. Look at the mysqldump options for the stuff you want to do by typing mysqldump --help :~ -Original Message- :~ From: mwilliams [mailto:[EMAIL PROTECTED] :~ Sent: Thursday, February 16, 2006 11:50 AM :~ To: mysql@lists.mysql.com :~ Subject: (mysqldump) Serial output. . .? :~ :~ All, :~ :~ I'm looking to output every piece of data from the database line by :~ line. Is there any :~ methody by which 'mysqldump' can output the following?: :~ :~ :~ use MY_DATABASE; :~ :~ CREATE TABLE IF NOT EXISTS MY_TABLE; :~ :~ ALTER IGNORE TABLE MY_TABLE ADD MY_COLUMN [properties] :~ ALTER IGNORE TABLE MY_TABLE ADD MY_COLUMN [properties] :~ ALTER IGNORE TABLE MY_TABLE ADD MY_COLUMN [properties] :~ ALTER IGNORE TABLE MY_TABLE ADD MY_COLUMN [properties] :~ ALTER IGNORE TABLE MY_TABLE ADD MY_COLUMN [properties] :~ ALTER IGNORE TABLE MY_TABLE ADD MY_COLUMN [properties] :~ ALTER IGNORE TABLE MY_TABLE ADD MY_COLUMN [properties] :~ :~ INSERT IGNORE . . . . :~ INSERT IGNORE . . . . :~ INSERT IGNORE . . . . :~ INSERT IGNORE . . . . :~ INSERT IGNORE . . . . :~ INSERT IGNORE . . . . :~ INSERT IGNORE . . . . :~ :~ :~ :~ The most important of the features above are the ability to CREATE a :~ table only if it doesn't :~ exist (I never want to drop because the same script will be used for :~ syncing) and the ability to :~ have 'mysqldump' be smart and output ALTER IGNORE statements. Any :~ asistance would be :~ greatly appreciated. :~ :~ Regards, :~ Michael :~ :~ -- :~ MySQL General Mailing List :~ For list archives: http://lists.mysql.com/mysql :~ To unsubscribe:http://lists.mysql.com/mysql?unsub=dathan- :~ [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Migration toolkit
I haven't tried the migration toolkit so I don't know what it can or cannot do. I am assuming that you have hand-transferred a few users from your old system to the new one. What I can suggest is that you generate two sets of data. The first is a list of your users, their hostnames, and their password hashes from your old server: CREATE TABLE oldUsers SELECT user, host, password FROM mysql.user; You can use the utility mysqldump to convert oldUsers table to a SQL script. Save this dump into a text file (oldusers.sql) for later. Next you need to run a bunch of SHOW GRANTS for statements. A script (pick your favorite scripting language for this) can crawl through oldUsers (just created) and capture the results of SHOW GRANTS FOR 'user'@'host' for every user in the oldUsers table. Save the results into another text file (oldgrants.sql). These will be the SQL statements you will need to restore permissions to your current users in your new database. Now comes the fun part: I prefer to enter the CLI and navigate to the correct database by hand before executing scripts like these (I have seen many people just do this from the command line but I would rather be sure). On your new server, start your mysql CLI and navigate to the mysql database. Once there, execute the script that generates the oldUsers table. The sequence should look something like this: mysql -u yourlogin -p mysql provide your password mysql source full_path_to_oldusers.sql That should create a table of all of your user accounts in the table oldUsers in the mysql database of your new server. Bulk insert them into your users table like this INSERT IGNORE user (user, host, password) SELECT user, host, password FROM oldUsers; And refresh the permissions cache: FLUSH PRIVELEGES; Now you are ready to re-apply privileges. Assuming that you correctly captured the GRANT statements from your SHOW GRANTS for script, you should be able to say. mysql source full_path_to_oldgrants.sql and do one last FLUSH PRIVILEGES; Your old accounts should now exist on your new server with their old permissions restored. Sorry but you asked for any ideas... ;-) Shawn Green Database Administrator Unimin Corporation - Spruce Pine Ed Reed [EMAIL PROTECTED] wrote on 02/14/2006 12:11:05 PM: Does anyone have any idea on this one? - Thanks Ed Reed [EMAIL PROTECTED] 2/10/06 3:09 PM I'm trying to use the Migration Toolkit to migrate a 4.1 system to a 5.0 system. It doesn't appear that it can successfully migrate my usernames and privileges. Is it supposed to be able to and is there anything special I need to do to make it happen? - Thanks
Re: ERROR 1060 while creating a view
Vinay [EMAIL PROTECTED] wrote on 02/14/2006 02:29:45 PM: Hi, I am trying to create a view by joining two tables.These tables have a common column which references their parent table. I get the following error when I try to create a view. create view trn2 as select * from dbtmsg,dbtrbl where dbtmsg.accxsk=-1 ERROR 1060 (42S21): Duplicate column name 'accxsk' Thank You, Any help appreciated, Vinay Since both tables contain a column called `accxsk`, the SELECT * part of the view's definition will try to list both copies (once from each table). Views cannot contain columns with duplicate names. Hence the error. You have to use the more explicit form of SELECT col1, col2, col3, to individually identify each column you want to see in your view in order to avoid this error. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
