Help With Insert Statements

Hello, I am working through ProSpring book, in which they are using Postgres. I need to convert the following inserts into Mysql 5. The table 'Test' , 'Customer' work fine, but 'CustomerAddresses' blows up on the delete cascade portion. As I was trying to figure out the error, another

constraining unique values across columns

I occasionally run into issues where I would like to ensure that the values across two or three columns are not duplicated. At work we use Oracle, and I see that they add constraints when creating the columns. I am running mysql 5.0 and was hoping someone could give me the simple explanation of

MUL when describe table

Hello, When I do a table describe, I see a MUL on a column I have marked as a foreign-key. I tried searching for MUL within the docs, but do not see a concise explanation of what that is? Anyone? Thanks, Scott

Syntax Question Constraint, Index

I ran into some syntax over the weekend, that I am trying to make sense of. Here is the create table statements. Drop table if exists events Drop table if exists locations Create table events ( uid BIGINT NOT NULL AUTO_INCREMENT, name VARCHAR(255), start_date DATE, duration INTEGER,

Table Type For PK/FK

I have been reading the definitive guide to MySQL5, and I am not finding a answer to a question. I am designing a project in which I am going to have primary keys, and foreign keys. In order to keep things proper, I am going to have delete on cascades on my foreign keys, so when the primary

Create a constraint over 3 columns.

I would like to be able to avoid inserting a duplicate record. The duplication consists of 3 columns though. Here is the scenario. I have this existing table item (which is a product item) CREATE TABLE ITEM ( parent_id INT NOT NULL default 0, id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

Import 5.0 changes to file?

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

Help Understanding Document Syntax

Hello, I have created some tables a while back, and of course, and I am learning, I have found problems with duplicate entries and other problems. So upon a fresh read of the 5.1 docs, I am trying to understand the word symbol after the constraint. I would like to be able to somehow

Find username password on tables

Hello, A while back, I created a database, and performed the following: GRANT ALL ON util_db.* to XXX identified by XXX; Problem is, a year later, I need to find the username and password, so I can write to these tables. Can this be accomplished, I am the root user. Thanks, Scott -- MySQL

Backing Up Database and All Tables

Hello, I am running mysql 4.0.15 on a Win2000 operating system. I would like to be able to back up the system with a perl script. I have a mix of InnoDB and MyISAM table types. Upon investigating this, I found this web site, with an article about backing up mysql databases:

Seeking Backup Strategy

Hello, After many months of preparation, I am finally going to go live with a project I have created. It is your basic e-commerce site, where I need to make sure I have a current backup, specifically on the orders placed, etc. I am going to run the mysql server on a PC possibly running XP.

Phone Number Column Advice

Hello, I am waffling on how to handle a column of phone numbers. I am not sure what is common practice on the database side. Talking to some co-workers, some feel it would be fine to use a varchar and others think there should be 3 integer columns in the database for each number? Anyone have

Help Table Types Causing Issues

Hello, I have created a table for items. And I needed some of the columns to be searchable, so I created the table as: a MyISAM for full text search. CREATE TABLE `item` ( `parent_id` int(11) NOT NULL default '0', `id` int(11) NOT NULL auto_increment, `manufacturer_id` varchar(50) default

Insert with prefix

Hello, I have the following dilemma, that I do not know how to handle. I have the need for a table where I create a identifier. The identifier consists of a two character string, eg: UP + the next AUTO_INCREMENT Here is what I have. category_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

null data in table question

Hello, I have created a web-based simple application, and used mysql for data storage. All has worked well. But I do have a simple question I would like to ask the group. I have some web-based forms that match table structure. Most of the important fields have validation, and I ensure good

Help Foreign Key Error

Hello, MYSQL on PC v 4.0.15 And I am trying to add an address table off of it. Using the users(id) as a primary key and deleting on cascade. So when the user is deleted from system, so are the addresses associted with him. // code that errors CREATE TABLE `address` ( `id` int(11),

create unique index

Hello, I am reading the docs, but I am slightly confused. I have a table with a varchar(50) column (not a primary column) where I do not want duplicates. It is a properties column, and I am getting duplicates inserted, which is causing problems in my display. An Oracle DBA that works with me

Alter Column Name?

I cannot find any information on this, so I am thinking the answer is no. But I have a column that I would like to change the name of: current the column name isID, and I would like it to change it to CHILD_ID, as the the software is changing, and we are adding parent_id column. Thanks, Scott

change data prefix query

Hello, I have a table that has a varchar column in which I need to change a prefix for all records. Currently there are about 500 records and I did not want to do this by hand. It looks like this: [data here ] UP05000 UP05001 UP05002 UP05003 The identifier has now changed to EN so each

default current_timestamp problem

Hello, I am trying to create simple reference table for some cookies I am creating. I wanted to put in a current_timestamp each time I do an insert so I can delete this data after 2 or 3 weeks. Here is the insert statement: CREATE TABLE COOKIE_REF (cookie_ref varchar(50), dat timestamp

InnoDB to MyISAM

Hello, I posted last night but did not receive an answer. I am trying to create a fulltext index, but my table was created as an InnoDB type. There is quite a bit of data there (1000+ records) and I need to change to a MyISAM table for the indexing for fulltext search. How can I convert the

Alter InnoDB to MyISAM Part2

I can do this programatically, and will alter the table. But there are three tables that have foreign key references to the table I will be altering. They look like this. CREATE TABLE ITEM_CAT_REL ( id INT, cat_id INT NOT NULL, key(id), FOREIGN KEY (id) references ITEM(id) on

Fulltext Simple Question

Hello, I am running 4.0.15 for Win95/98 and am working through the docs. I created a text type field with a 'fulltext' index. As I am experimenting, I have run into a couple of questions: First off, I was having trouble getting results. So I added the word foobar to one of the descriptions:

RE: Fulltext Simple Question

install, and I am just learning it. How do I create this file, and where does it go? Thanks, Scott -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 25, 2005 2:21 PM To: Brian Mansell Cc: mysql@lists.mysql.com; Scott Purcell Subject: Re

Create fulltext index

Hello, I created table a while back like so: CREATE TABLE CATEGORY ( cat_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, parent_id INT, visible varchar(1) NOT NULL DEFAULT 'Y', sort INT, name varchar(200) ) TYPE=InnoDB; I believe I created it as type InnoDB for a

varchar to text

Hello, I created a table that uses a varchar(254) size field, and found out that the customers data is being truncated. It needs to be larger. Currently there are about 500 records (it is a description field) in the column. Upon reading the docs, it looks like I need to use a text type column.

RE: varchar to text

-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Monday, May 23, 2005 8:58 AM To: Brent Baisley Cc: Scott Purcell; mysql@lists.mysql.com Subject: Re: varchar to text Hi, I we can, one should ask if it is a good solution ? text can't be indexed as a varchar. You will need

Database Table Date Assistance

Hello, I would like to do the following: I am creating a site where people can add items to a cart. In order to keep items for [X] amount of days, I would like to create a database table in mysql to hold a 'itemRef' and a 'Date'. Then in a relationship table I can hold the 'itemRef' and

Seeking Syntax Assistance

Hello, I am accustomed to Oracle query syntax, and I am having trouble with this following query which uses the IN. I am running mysql ( Ver 12.21 Distrib 4.0.15, for Win95/Win98 (i32) ) which does not support the IN. How do I rewrite the below query to function? Basically what I have is three

RE: Work / Home DB synchronization

05, 2005 12:08 PM To: Scott Purcell; mysql@lists.mysql.com Subject: Re: Work / Home DB synchronization You require SQLyog's Data Sync Tool. Download it from http://www.webyog.com You can find an article on it at: http://www.sitepoint.com/article/mysql-data-sqlyog-job-agent Karam --- Scott Purcell

Work / Home DB synchronization

Hello, I am developing a web application in which I have been working between home and an away office. Anyway, up to lately, most of my efforts have been writing the queries, etc for the backend. So I have a text file that dumps the database and repopulates them. And up until now, it has been

Seeking advice on currency type

Hello, I am seeking to create a table to hold prices for products. I am not sure what type would be best. According to the docs, I have many choices, dec, double, float, etc. Can anyone give me an idea, of which is the proper choice for handling US currency like so: 1500.99 Thanks, Scott K

create table with foreign key

Hello, I have this simple table of unique items. CREATE TABLE ITEM ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, manufacturer_id varchar(50), ) TYPE=InnoDB; Foreach item, I can have 1 to many assets. So I am trying to use this: CREATE TABLE ITEM_ASSET_REL ( id INT,

grant question

I am here in the docs. GRANT priv_type [(column_list)] [, priv_type [(column_list)]] ... ON {tbl_name | * | *.* | db_name.*} TO user [IDENTIFIED BY [PASSWORD] 'password'] [, user [IDENTIFIED BY [PASSWORD] 'password']] ... [REQUIRE NONE | [{SSL| X509}]

Find out who owns database and tables

Hello, I have created some databases and tables, a while back, as a new user. I believe when the system was installed it asked me fora root username and password. And I set one. I have some databases, and I would like to find out two things: 1) Who owns what database and its tables: Is there

driver question

Hello I am using org.gjt.mm.mysql.Driver to connect a web-app to a mysql database. I ran into an error, I could use some help with. I have a database that has a timestamp field CREATE TABLE FOO ( create_date TIMESTAMP, approved_date TIMESTAMP, ) TYPE=InnoDB; Now when I insert

database pooling problem

Hello, I am working with a traditional database pooling class basically putting connection objects into a Vector. When I run the code from a main, it appears solid. I have run 10s of thousands of back to back queries and all is good. Problem is when I connect it to a web-app, and reboot the

update and get value

UPDATE menu_sequence SET id=LAST_INSERT_ID(id+1); I am writing some code that I need to actually get the menu_sequence from above, store it in a variable and then do a increment. I do not know why I am having trouble with this, but it looks like I would have to String seq = select id from

mysql open connections question

Hello, I am writing a web-based application and incorporated a roll-your-own database pool into it. So far I am running well, but I have seen a couple of issues I would like to present. After being up for a couple of days, I noticed a Error cannot connect, too many connections error coming

set auto_increment does not work?

Hello, I am having trouble getting the auto_increment function to begin at a set value. When I search the docs, I find information like: Posted by Michael Craig on September 6 2002 9:51pm [ http://dev.mysql.com/doc/mysql/comment.php?id=1058action=delete Delete] [

auto_increment not working?

Hello, I am trying to get auto_increment to begin at a certain integer. CREATE TABLE USERS ( user_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, username varchar(50), firstname varchar(50), middlename varchar(50), lastname varchar(50), email varchar(100),

cascade on delete problem

Hello, I apologize for a possible simple question, but I am having trouble with the below code. I have three simple tables. In short, menu_group has an id, that is referenced in the menu_group_rel. When a user deletes an id from the menu_group, I wanted the entry in menu_group_rel (data_id)

re: Cascade problem now error:

'); -Original Message- From: Artem Koltsov [mailto:[EMAIL PROTECTED] Sent: Thursday, January 27, 2005 10:29 AM To: Scott Purcell; mysql@lists.mysql.com Subject: RE: cascade on delete problem Hello Scott, Make sure your tables are InnoDB type: CREATE TABLE table_name ( table_def ...) ENGINE

RE: Cascade problem now error:

) 120 perror 150 MySQL error: 150 = Foreign key constraint is incorrectly formed Cheers, --V Scott Purcell wrote: Thanks, I updated my script and all looks good. But now I get an error when tryng to issue this command. Any ideas? Thanks, Scott mysql ALTER TABLE MENU_GROUP_REL ADD

RE: Cascade problem now error:

, INDEX par_ind (parent_id), FOREIGN KEY (parent_id) REFERENCES parent(id) ON DELETE CASCADE ) TYPE=INNODB; -Original Message- From: Scott Purcell [mailto:[EMAIL PROTECTED] Sent: Thursday, January 27, 2005 11:59 AM To: V. M. Brasseur

create sequence

Hello, I would like to create a sequence object in mysql that I could use in multiple tables through a application I am developing. Could someone please send my the syntax of how to create a simple sequence that starts at 1 increments by 1. thanks,

RE: create sequence

To: Scott Purcell Cc: mysql@lists.mysql.com Subject: Re: create sequence You want AUTO_INCREMENT, http://dev.mysql.com/doc/mysql/en/example-AUTO_INCREMENT.html. PB - Scott Purcell wrote: Hello, I would like to create a sequence object in mysql that I could use in multiple tables through

outer join

, but do not understand. Could someone please give me a hand with this? Thanks, Scott Purcell -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: SQL Query Question

This works for Oracle, give it a try, use any format you want for the MM/DD/YY area. select to_char(queue_time, 'MM/DD/YY'); Scott Purcell -Original Message- From: Dirk Bremer (NISC) [mailto:[EMAIL PROTECTED] Sent: Friday, April 16, 2004 2:55 PM To: [EMAIL PROTECTED] Subject: SQL

Foreign Key Problems

Hello, I am trying to create some tables that I can use the delete on cascade function for. This would help me code the project and ensure data integrity. I am on the docs @ http://www.mysql.com/doc/en/InnoDB_foreign_key_constraints.html but I am not quite understanding the syntax. I am

Restore And Dump

Hello, I am running two instances of mysql. They both run on different boxes, not networked. Anyway, I have been developing on one machine, and trying to do a dump and keep the other box updated with Database changes. I have ran this from the dev box. mysqldump --add-drop-table -u dbusername

copying blob data to remote box

Hello, I am running a DB on a machine in which I am developing on. Then I have been copying the contents of ~mysql/data/databasename to another box where I am running my code. All the data I modify copies well, and I can run on my production box with no problems. Except for binary blob data.

RE: copying blob data to remote box

, the system begain complaining. I am looking at the mysql --help, but I do not see anything about a password, or about where to put the files. Thanks, -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, February 10, 2004 8:24 AM To: Scott Purcell Cc: [EMAIL

auto_increment pseudo sequence?

Hello, I have an application in which I am using auto_increment as a kind of sequence replacement. The only problem I have is trying to get the auto_increment to start at a larger number than 0. Is auto_increment the replacement for sequences? and if so, is there a way to set the digit to a

What replaces IN?

I am trying to run the below query, and believe that the 'IN' is not supported in mysql. What is the 'IN' replacement? I tried exists and that doesn't work. select * from table1 where item_id IN (select item_id from table2) Thanks, Scott -- MySQL General Mailing List For list

mysql deny access to datasource

I am running a web application under tomcat 4.03, and using a database pooling class (below) and all works well. That is until I update a class file. After I update a class file, then I get the error below stating the deny access to datasource. Does anyone know why this happens. I printed to

command line operation problems

Hello, I am new to mysql and have a couple of questions that I can't seem to get through at this time. I have installed the product on my win2000 box. I followed the docs for PC, and removed the generic user in the users table. Here is my question: According to the docs when I go to the cmd

blobs in mysql

Hello, I have opted to insert some small jpg files into the mysql database using java. Code below. When I do a select from the table using the mysql command line, it generates pages of ---

First Time Install Leads To Slight Confusion

I have just installed MySql 4.0.15 from package [mysql-4.0.15-win.zip] onto my Win2000 box. After doing the binary install, I got a 'cmd' prompt and followed the install directions. http://www.mysql.com/documentation/mysql/bychapter/index.html I followed the 2.1.1.1 Installing the Binaries