RE: error creating table

[LIU YAN]

hi,

 

I run your code , but worked propertly. I suggested to check the table USERS , 
ROLES, is the column userid / roleid same data type (INT(10) UNSIGNED ) with 
your userroles table ?

 

==

mysql> create table users (userid INT(10) UNSIGNED primary key);
Query OK, 0 rows affected (0.06 sec)

mysql> create table roles (roleid INT(10) UNSIGNED primary key);
Query OK, 0 rows affected (0.06 sec)

mysql> CREATE TABLE `userroles` (
-> `roleid` INT(10) UNSIGNED NOT NULL DEFAULT '0',
-> `userid` INT(10) UNSIGNED NOT NULL DEFAULT '0',
-> PRIMARY KEY (`roleid`, `userid`),
-> INDEX `FK1_user` (`userid`),
-> CONSTRAINT `fk_userid` FOREIGN KEY (`userid`) REFERENCES `users`
-> (`userid`) ON UPDATE CASCADE ON DELETE CASCADE,
-> CONSTRAINT `fk_roleid` FOREIGN KEY (`roleid`) REFERENCES `roles`
-> (`roleid`) ON UPDATE CASCADE ON DELETE CASCADE
-> )
-> COLLATE=utf8_general_ci
-> ENGINE=InnoDB
-> ROW_FORMAT=COMPACT
-> AVG_ROW_LENGTH=0;
Query OK, 0 rows affected (0.08 sec)

mysql>

==

 

best regards

liuyann


 
> Date: Sun, 4 Oct 2009 23:47:54 +0530
> Subject: error creating table
> From: saf...@gmail.com
> To: mysql@lists.mysql.com
> 
> Hi,
> I'm trying to create a table with 2 columns both are primary key
> (combined) and both are foreign key as well. I'm getting error cannot
> create table. Here is the sql
> 
> CREATE TABLE `userroles` (
> `roleid` INT(10) UNSIGNED NOT NULL DEFAULT '0',
> `userid` INT(10) UNSIGNED NOT NULL DEFAULT '0',
> PRIMARY KEY (`roleid`, `userid`),
> INDEX `FK1_user` (`userid`),
> CONSTRAINT `fk_userid` FOREIGN KEY (`userid`) REFERENCES `users`
> (`userid`) ON UPDATE CASCADE ON DELETE CASCADE,
> CONSTRAINT `fk_roleid` FOREIGN KEY (`roleid`) REFERENCES `roles`
> (`roleid`) ON UPDATE CASCADE ON DELETE CASCADE
> )
> COLLATE=utf8_general_ci
> ENGINE=InnoDB
> ROW_FORMAT=COMPACT
> AVG_ROW_LENGTH=0
> 
> -- 
> Sharique uddin Ahmed Farooqui
> (C++/C# Developer, IT Consultant)
> http://safknw.blogspot.com/
> "Peace" is the Ultimate thing we want.
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=liuy...@live.com
> 
  
_
Windows Live: Keep your friends up to date with what you do online.
http://www.microsoft.com/middleeast/windows/windowslive/see-it-in-action/social-network-basics.aspx?ocid=PID23461::T:WLMTAGL:ON:WL:en-xm:SI_SB_1:092010

Re: No tuples, but AttributeError about tuple!

[Michael Dykman]

This looks like much more of a python question than a MySQL question..
 I'll reply offline.

 - michael dykman

On Sun, Oct 4, 2009 at 2:04 PM, Victor Subervi  wrote:
> Hi;
> I have the following python code:
>      sql = 'insert into products values(%s, %s, %s, %s, %s, %s, %s, %s, %s,
> %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s);', (id, name, title,
> description, price, bedrooms, bathrooms, conditions, acreage, construction,
> location, estate, address, furnished, pic1, pic2, pic3, pic4, pic5, pic6)
>      cursor.execute(sql)
>
> which, when printed to screen, gives the following:
>
> insert into products values ('1', 'name1', 'title1', 'descr1', '1.1', '2',
> '1', 'New', '1.5', 'new', 'princesse', 'princesse', '123 princesse', 'Not
> furnished', '', '', '', '', '', '');
>
> which I can enter into the database directly. However, when I try to do it
> through the script, I get the following error:
>
> AttributeError: 'tuple' object has no attribute 'encode'
>
> Why is that? There are no tuples here!! Nothing but strings!!
> TIA,
> Victor
>



-- 
 - michael dykman
 - mdyk...@gmail.com

Don’t worry about people stealing your ideas. If they’re any good,
you’ll have to ram them down their throats!

   Howard Aiken

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

RE: error creating table

[John]

What is the exact error you are getting?



John Daisley
MySQL & Cognos Contractor

Certified MySQL 5 Database Administrator (CMDBA)
Certified MySQL 5 Developer (CMDEV)
IBM Cognos BI Developer

Telephone +44 (0)7812 451238
Email j...@butterflysystems.co.uk

-Original Message-
From: Sharique uddin Ahmed Farooqui [mailto:saf...@gmail.com] 
Sent: 04 October 2009 19:18
To: mysql
Subject: error creating table

Hi,
I'm trying to create a table with 2 columns both are primary key
(combined) and both are foreign key as well. I'm getting error cannot
create table. Here is the sql

CREATE TABLE `userroles` (
`roleid` INT(10) UNSIGNED NOT NULL DEFAULT '0',
`userid` INT(10) UNSIGNED NOT NULL DEFAULT '0',
PRIMARY KEY (`roleid`, `userid`),
INDEX `FK1_user` (`userid`),
CONSTRAINT `fk_userid` FOREIGN KEY (`userid`) REFERENCES `users`
(`userid`) ON UPDATE CASCADE ON DELETE CASCADE,
CONSTRAINT `fk_roleid` FOREIGN KEY (`roleid`) REFERENCES `roles`
(`roleid`) ON UPDATE CASCADE ON DELETE CASCADE
)
COLLATE=utf8_general_ci
ENGINE=InnoDB
ROW_FORMAT=COMPACT
AVG_ROW_LENGTH=0

-- 
Sharique uddin Ahmed Farooqui
(C++/C# Developer, IT Consultant)
http://safknw.blogspot.com/
"Peace" is the Ultimate thing we want.

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/mysql?unsub=john.dais...@butterflysystems.co.uk

No virus found in this incoming message.
Checked by AVG - www.avg.com 
Version: 8.5.409 / Virus Database: 270.14.3/2413 - Release Date: 10/04/09 
06:20:00


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

error creating table

[Sharique uddin Ahmed Farooqui]

Hi,
I'm trying to create a table with 2 columns both are primary key
(combined) and both are foreign key as well. I'm getting error cannot
create table. Here is the sql

CREATE TABLE `userroles` (
`roleid` INT(10) UNSIGNED NOT NULL DEFAULT '0',
`userid` INT(10) UNSIGNED NOT NULL DEFAULT '0',
PRIMARY KEY (`roleid`, `userid`),
INDEX `FK1_user` (`userid`),
CONSTRAINT `fk_userid` FOREIGN KEY (`userid`) REFERENCES `users`
(`userid`) ON UPDATE CASCADE ON DELETE CASCADE,
CONSTRAINT `fk_roleid` FOREIGN KEY (`roleid`) REFERENCES `roles`
(`roleid`) ON UPDATE CASCADE ON DELETE CASCADE
)
COLLATE=utf8_general_ci
ENGINE=InnoDB
ROW_FORMAT=COMPACT
AVG_ROW_LENGTH=0

-- 
Sharique uddin Ahmed Farooqui
(C++/C# Developer, IT Consultant)
http://safknw.blogspot.com/
"Peace" is the Ultimate thing we want.

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

No tuples, but AttributeError about tuple!

[Victor Subervi]

Hi;
I have the following python code:
  sql = 'insert into products values(%s, %s, %s, %s, %s, %s, %s, %s, %s,
%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s);', (id, name, title,
description, price, bedrooms, bathrooms, conditions, acreage, construction,
location, estate, address, furnished, pic1, pic2, pic3, pic4, pic5, pic6)
  cursor.execute(sql)

which, when printed to screen, gives the following:

insert into products values ('1', 'name1', 'title1', 'descr1', '1.1', '2',
'1', 'New', '1.5', 'new', 'princesse', 'princesse', '123 princesse', 'Not
furnished', '', '', '', '', '', '');

which I can enter into the database directly. However, when I try to do it
through the script, I get the following error:

AttributeError: 'tuple' object has no attribute 'encode'

Why is that? There are no tuples here!! Nothing but strings!!
TIA,
Victor

Re: Questions on Database Design

[Mark Phillips]

Thanks to Martin and John for their help!

Mark

On Sat, Oct 3, 2009 at 5:53 PM, Martin Gainty  wrote:

>  enforcing by username/password to the DB is your safest method
> and if you want to really be safe put ssh access onto the MySQL Server
>
> here is how to install SSH and MySQL onto Ubuntu
> http://ubuntuforums.org/showthread.php?t=388073
>
> and to access SSHClient
> http://dev.mysql.com/doc/refman/5.1/en/windows-and-ssh.html
>
> HTH
> Martin Gainty
> __
> Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité
>
> Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene
> Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte
> Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht
> dient lediglich dem Austausch von Informationen und entfaltet keine
> rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von
> E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen.
>
> Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le 
> destinataire prévu, nous te demandons avec bonté que pour satisfaire informez 
> l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci 
> est interdite. Ce message sert à l'information seulement et n'aura pas 
> n'importe quel effet légalement obligatoire. Étant donné que les email 
> peuvent facilement être sujets à la manipulation, nous ne pouvons accepter 
> aucune responsabilité pour le contenu fourni.
>
>
>
>
>
>
> > Date: Sat, 3 Oct 2009 18:11:59 -0600
> > From: john.l.me...@gmail.com
> > To: m...@phillipsmarketing.biz
> > CC: mysql@lists.mysql.com
> > Subject: Re: Questions on Database Design
>
> >
> > Mark Phillips wrote:
> > > On Sat, Oct 3, 2009 at 3:06 PM, Martin Gainty 
> wrote:
> > >
> > >
> > >> depends on the relationship of the Data Tables and the Users that use
> them
> > >>
> > >> for instance if I was to setup a table of outgoing calls from 2
> distinct
> > >> individuals :
> > >> Me> calls to HarvardMedicalSchool, MassGeneral,
> > >> SomervilleHospital and AMA
> > >> VereinDesKrankRufs>calls to Biff,Tony,EdSoprano and Destiny
> > >>
> > >> so as you can see the difference between my calls and Vereins calls
> should
> > >> never be joined
> > >> as Vereins customers are distinctly not mine and mine are not his
> > >> Moreover my contact table would contain Degrees and titles where
> Vereins
> > >> customers
> > >> have no need for that
> > >> So in this case it would make perfect sense for my Database to be
> separate
> > >> and distinct from Vereins database..if for no other reason than the
> schemas
> > >> are completely difference
> > >>
> > >> With an emphasis on security once Verein initiates populating his
> records
> > >> on your DB by populating the same tables and using the same join
> > >> relationships it will be impossible to force him to not use those
> tables
> > >> or even to restrich his access to the slave server while you're
> updating
> > >> the master
> > >> You can restrict access by GRANT SELECT on the tables to Verein but
> that
> > >> would last only a week or 2 until Verein requests update and insert
> access
> > >> to the DB. Once the INSERT and UPDATE grants are made you wont be able
> to
> > >> separate his records from yours
> > >>
> > >> Keep the 2 separate is my suggestion..MySQL is inexpensive and HW is
> cheap
> > >> so this should be a low cost solution for you
> > >>
> > >> Keep us apprised and any feel free to inquire on any operational
> details
> > >> you may require.
> > >>
> > >> Thanks! To make sure I understand. Even if the schemas are the same,
> if the
> > >>
> > > data is not related, nor is meant to be combined in some way (eg rolled
> up
> > > or summed in some way), then creating a separate database for each user
> is a
> > > better way to go; or at least a meaningful way to go. A side benefit is
> > > greater security from the stand point that user a cannot get to user
> b's
> > > data.
> > >
> > > Can't I achieve the same level of security if each row has a userID,
> and all
> > > queries use a "where userID=xxx" clause?
> > >
> > > Mark
> > >
> >
> >
> >
> > no, don't confuse that with database security. There are too many ways
> > to get around that sort of trick through SQL injection attacks. Read
> > http://dev.mysql.com/doc/refman/5.4/en/privilege-system.html for a
> > starter on privileges and security.
> > But as long as you're not needing to regularly combine and aggregate the
> > data then creating separate databases is a reasonable option.
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe: http://lists.mysql.com/mysql?unsub=mgai...@hotmail.com
> >
>
> --
> Hotmail: Trusted email with powerful SPAM protection. Sign up 
> now.
>

MySQL not displaying table info

[MarvinC]

Can anyone help me figure out the following issue surrounding MySQL and
phpMyAdmin:
I load phpMyAdmin ver3.2, log in, and create a new database. I see the
following warning at the bottom:

Your PHP MySQL library version 5.0.51a differs from your MySQL server
version 5.1.39. This may cause unpredictable behavior.

The right column displays the following info:
 MySQL

   - Server: localhost via TCP/IP
   - Server version: 5.1.39-community
   - Protocol version: 10
   - User: r...@localhost
   - MySQL charset: UTF-8 Unicode (utf8)

 Web server

   - Microsoft-IIS/6.0
   - MySQL client version: 5.0.51a
   - PHP extension: mysql

I assume all is well so I copy my Wordpress files to my new web directory,
like I've done in the past, and add the database connection info to its
config.php file. When I view the database in the left column it doesn't show
any table info. In fact it doesn't show anything at all:

   - information_schema
(28)
   - mysql 
(23)
   - testdb1
   - 
testdb2


I get a Webpage not found message when trying to launch the install script.
I can view htm and .php files, including the phpinfo.php file, from both of
my test directories. Can someone advise on what could be causing this?

Any responses appreciated.