问题:

尝试连接到mysql的数据库时我面临的问题。 我还提供了我使用过的数据库设置。

 Traceback (most recent call last):
 File "manage.py", line 10, in <module>
 execute_from_command_line(sys.argv)
 File "/home/ar/Desktop/test/testprac/local/lib/python2.7/site-packages/django/core/management/__init__.py", line 453, in execute_from_command_line
utility.execute()
File "/home/ar/Desktop/test/testprac/local/lib/python2.7/site-packages/django/core/management/__init__.py", line 392, in execute
self.fetch_command(subcommand).run_from_argv(self.argv)
File "/home/ar/Desktop/test/testprac/local/lib/python2.7/site-packages/django/core/management/__init__.py", line 272, in fetch_command
klass = load_command_class(app_name, subcommand)
File "/home/ar/Desktop/test/testprac/local/lib/python2.7/site-packages/django/core/management/__init__.py", line 77, in load_command_class
module = import_module('%s.management.commands.%s' % (app_name, name))
File "/home/arundhati/Desktop/test/testprac/local/lib/python2.7/site-packages/django/utils/importlib.py", line 35, in import_module
__import__(name)
File "/home/ar/Desktop/test/testprac/local/lib/python2.7/site-packages/django/core/management/commands/syncdb.py", line 8, in <module>
from django.core.management.sql import custom_sql_for_model, emit_post_sync_signal
File "/home/ar/Desktop/test/testprac/local/lib/python2.7/site-packages/django/core/management/sql.py", line 9, in <module>
from django.db import models
File "/home/ar/Desktop/test/testprac/local/lib/python2.7/site-packages/django/db/__init__.py", line 40, in <module>
backend = load_backend(connection.settings_dict['ENGINE'])
File "/home/ar/Desktop/test/testprac/local/lib/python2.7/site-packages/django/db/__init__.py", line 34, in __getattr__
return getattr(connections[DEFAULT_DB_ALIAS], item)
File "/home/ar/Desktop/test/testprac/local/lib/python2.7/site-packages/django/db/utils.py", line 93, in __getitem__
backend = load_backend(db['ENGINE'])
File "/home/ar/Desktop/test/testprac/local/lib/python2.7/site-packages/django/db/utils.py", line 27, in load_backend
return import_module('.base', backend_name)
File "/home/ar/Desktop/test/testprac/local/lib/python2.7/site-packages/django/utils/importlib.py", line 35, in import_module
__import__(name)
File "/home/ar/Desktop/test/testprac/local/lib/python2.7/site-packages/django/db/backends/mysql/base.py", line 17, in <module>
raise ImproperlyConfigured("Error loading MySQLdb module: %s" % e)
django.core.exceptions.ImproperlyConfigured: Error loading MySQLdb module: No module named MySQLdb

数据库设置::

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.mysql', # Add 'postgresql_psycopg2', 'mysql', 'sqlite3' or 'oracle'.
        'NAME': 'ar_test_db',                      # Or path to database file if using 
        # The following settings are not used with sqlite3:
        'USER': '',
        'PASSWORD': '',
        'HOST': '',                      # Empty for localhost through domain sockets or   '127.0.0.1' for localhost through TCP.
        'PORT': '',                      # Set to empty string for default.
    }
}

非常感谢您的帮助 !!

The problem Im facing while trying to connect to database for mysql.I have also given the database settings that i have used.Databse Settings::Thanks a lot for the help !!

问题:

所以我试图将一个主键添加到我的数据库中的一个表。 现在它有一个这样的主键:

PRIMARY KEY (user_id, round_number)

其中user_id是外键。

我想把它改成这个:

PRIMARY KEY (user_id, round_number, created_at)

我在phpmyadmin中通过单击表结构视图中的主键图标来执行此操作。

这是我得到的错误:

#1025 - Error on rename of './database/#sql-2e0f_1254ba7' to './database/table' (errno: 150)

它是一个带有InnoDB表引擎的MySQL数据库。

So I am trying to add a primary key to one of the tables in my database.Right now it has a primary key like this:Where user_id is a foreign key.I am trying to change it to this:I am doing this in phpmyadmin by clicking on the primary key icon in the table structure view.This is the error I get:It is a MySQL database with InnoDB table engine.

问题:

CREATE TABLE `users` (
  `UID` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `username` VARCHAR(45) NOT NULL ,
  `password` VARCHAR(100) NULL ,
  `name` VARCHAR(100) NULL ,
  `gender` BIT NULL ,
  `email` VARCHAR(255) NULL ,
  `phone` VARCHAR(30) NOT NULL ,
  `verified` BIT NOT NULL DEFAULT 0 ,
  `time_zone` INT NULL ,
  `time_register` DATETIME NULL ,
  `time_active` DATETIME NULL ,
  PRIMARY KEY (`UID`) ,
  UNIQUE INDEX `username_UNIQUE` (`username` ASC) ,
  INDEX `verified_INDEX` (`verified` ASC) )
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `companies`
-- -----------------------------------------------------
CREATE TABLE `companies` (
  `CID` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `name` VARCHAR(45) NOT NULL ,
  `address` VARCHAR(100) NULL ,
  `email` VARCHAR(255) NULL ,
  `phone` VARCHAR(30) NULL ,
  `link` TEXT NULL ,
  `image_small` TEXT NULL ,
  `image_large` TEXT NULL ,
  `yahoo` VARCHAR(100) NULL ,
  `linkin` VARCHAR(100) NULL ,
  `twitter` VARCHAR(20) NULL ,
  `description` TEXT NULL ,
  `shoutout` VARCHAR(140) NULL ,
  `verified` BIT NOT NULL DEFAULT 0 ,
  PRIMARY KEY (`CID`) ,
  INDEX `name_INDEX` (`name` ASC) ,
  INDEX `verified_INDEX` (`verified` ASC) )
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;

-- -----------------------------------------------------
-- Table `products`
-- -----------------------------------------------------
CREATE TABLE `products` (
  `PID` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `CID` INT UNSIGNED NOT NULL ,
  `name` VARCHAR(100) NULL ,
  `description` TEXT NULL ,
  `image_small` TEXT NULL ,
  `image_large` TEXT NULL ,
  `tag` VARCHAR(45) NULL ,
  `price` DECIMAL(11,2) NULL ,
  PRIMARY KEY (`PID`) ,
  INDEX `tag_INDEX` (`tag` ASC) ,
  INDEX `company.cid_FK` (`CID` ASC) ,
  CONSTRAINT `company.cid_FK`
    FOREIGN KEY (`CID` )
    REFERENCES `companies` (`CID` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;

-- -----------------------------------------------------
-- Table `users_companies`
-- -----------------------------------------------------
CREATE TABLE `users_companies` (
  `UID` INT UNSIGNED NOT NULL ,
  `CID` INT UNSIGNED NOT NULL ,
  `role` INT UNSIGNED NULL ,
  PRIMARY KEY (`CID`, `UID`) ,
  INDEX `users.uid_FK` (`UID` ASC) ,
  INDEX `company.cid_FK` (`CID` ASC) ,
  CONSTRAINT `users.uid_FK`
    FOREIGN KEY (`UID` )
    REFERENCES `users` (`UID` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `company.cid_FK`
    FOREIGN KEY (`CID` )
    REFERENCES `companies` (`CID` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;

我收到了错误

#1005 - Can't create table 'wew.users_companies' (errno: 121) (<a href="server_engines.php?engine=InnoDB&amp;page=Status&amp;token=3f35afdea97dd11f6b4ec1b669816738">Details...</a>) 

谁能告诉我哪一个是问题?

i got errorcan anyone tell me which one is the problem ?

问题:

我现在正在忙着实现一个类别的过滤器,我需要为每个“标签”生成一个INNER JOIN clausse来过滤。

问题是在一大堆SQL之后,我有一个表包含了我选择所需的所有信息,但是每次生成的INNER JOIN我都需要它

这基本上看起来像:

SELECT
    *
FROM search
INNER JOIN search f1 ON f1.baseID = search.baseID AND f1.condition = condition1
INNER JOIN search f2 ON f2.baseID = search.baseID AND f2.condition = condition2
...
INNER JOIN search fN ON fN.baseID = search.baseID AND fN.condition = conditionN

这工作但我更喜欢“搜索”表是临时的(如果它不是一个普通的表,它可以小几个数量级)但这给我一个非常恼人的错误: Can't reopen table

一些研究引导我查看这个错误报告,但MySQL的人们似乎并不关心这样的基本功能(使用多次表)不适用于临时表。 我遇到了很多关于此问题的可伸缩性问题。

有没有可行的解决方法,不需要我管理潜在的大量临时但非常真实的表或让我维护一个包含所有数据的大表?

亲切的问候,克里斯

[额外]

GROUP_CONCAT答案在我的情况下不起作用,因为我的条件是按特定顺序排列的多个列,它会使OR成为我需要的AND。 但是,它确实帮助我解决了早期的问题,所以现在不再需要表格,不管是不是临时表格。 我们只是想对我们的问题过于笼统。 过滤器的整个应用现在已经从大约一分钟恢复到大约四分之一秒。

I’m currently busy implementing a filter of sorts for which I need to generate an INNER JOIN clausse for every “tag” to filter on.The problem is that after a whole bunch of SQL, I have a table that contains all the information I need to make my selection, but I need it again for every generated INNER JOINThis basically looks like:This works but I would much prefer the “search” table to be temporary (it can be several orders of magnitude smaller if it isn’t a normal table) but that gives me a very annoying error: Can't reopen tableSome research leads me to this bug report but the folks over at MySQL don’t seem to care that such a basic feature (using a table more than once) does not work with temporary tables.I’m running into a lot of scalability problems with this issue.Is there any viable workaround that does not require me to manage potentially lots of temporary but very real tables or make me maintain a huge table with all the data in it?Kind regards, Kris[additional]The GROUP_CONCAT answer does not work in my situation because my conditions are multiple columns in specific order, it would make ORs out of what I need to be ANDs.However, It did help me solve an earlier problem so now the table, temp or not, is no longer required.We were just thinking too generic for our problem.The entire application of filters has now been brought back from around a minute to well under a quarter of a second.

问题:

我很困惑如何导入SQL转储文件。 如果不首先在MySQL中创建数据库,我似乎无法导入数据库。

这是尚未创建database_name时显示的错误:

username =有权访问原始服务器上数据库的用户的username名。
database_name =原始服务器中的数据库名称

$ mysql -u username -p -h localhost database_name < dumpfile.sql   
Enter password:  
ERROR 1049 (42000): Unknown database 'database_name' 

如果我以root身份登录MySQL并创建数据库database_name

mysql -u root  
create database database_name;  
create user username;# same username as the user from the database I got the dump from.  
grant all privileges on database_name.* to [email protected]"localhost" identified by 'password';  
exit mysql

然后再次尝试导入sql转储:

$ mysql -u username -p database_name < dumpfile.sql  
Enter password:  
ERROR 1007 (HY000) at line 21: Can't create database 'database_name'; database exists

我该如何导入SQL转储文件?

I’m confused how to import a SQL dump file.I can’t seem to import the database without creating the database first in MySQL.This is the error displayed when database_name has not yet been created:username = username of someone with access to the database on the original server.database_name = name of database from the original serverIf I log into MySQL as root and create the database, database_namethen attempt to import the sql dump again:How am I supposed to import the SQL dumpfile?