Using Shell Script To Create Tables

Create Database From Shell Command Line
From what I can see from the documentation, this does not appear to be an option.

Hope someone knows otherwise?

---

Can I Create New Tables From Old DB On The Fly ???
I am now building a new database which will use some of the tables from several old DBs. I need to change the table names and then add/drop some fields, all other raw data will be kept. I knew I can dump all create table clues by using things like: 'use olddb1; show create tablesG' , adjust it to new DB's table, and then insert raw data by INSERT the result of a sub-query with old DB..

Is there a better way to do "create table" and "insertion raw data"?

---

Cannot Create Tables
I'm trying to work through examples from a book (fundatmentals of database systems). In one example they create a database from the command prompt by inputting the following query: Code:

---

Create Tables
I recently installed mysql administrator on a mac os X 10.3.5 and am not able to create any table at all in any new created schema. If I select 'create table' from the dropdownlist no popup menu appears . If I select create table selecting the existing db 'mysql' I do get the popup menu to create a new table but get an error while trying to execute query... :s.
To be sure it wouldn't be a permission problem I set the rights to 777 but still no go,

---

Create New Tables
I installed all the tools (MySQL Administrator, MySQL Query Browser and MYSQL Server 4.1 for the windows version) and made the connection, and I even made to create a new database but right now I want to create some tables to that database and all I can do is enter to the table editor in the MySQL Query Browser and enter all the colums that I will need for that table but after I click the button ApplyChanges it throws me an error that says something like this:
"MySQL Error Number 1064
You Have an error in your SQL syntax; check the manual that
corresponds to your MYSQLserver version for the right syntax to use
near `AUTO_INCREMENT,PRIMARY KEY(ìd`))TYPE=InnoDB`at line 2"

---

Create Tables
I need to create tables dynamically based on the results of a query.
for example, i have a table with two columns: "name", "item".
i want to create a table for each individual "name" with their respective "items". so with a master table of

jim, eggs
matt, bread
jim, cheese

in the end, i will have two tables:

table_jim:
jim, eggs
jim, cheese

table_mattL
matt, bread

---

Mysql Can't Create Tables
I am trying to create tables in Mysql version 3.23.58
this is what I typed in the terminal:

mysql> create table MyVChanels(
-> Alerts varchar(50),
-> TypeID int not null primary key,
->
-> index(TypeID),
->
-> )type=innodb;
ERROR 1064: You have an error in your SQL syntax near ')type=innodb' at line 3

No matter what kind tables i am creating I get the same error with or
without primary key. To make it simple i even tried to create a table with one field but no help the same error pops up.
However, created tables in Window XP Mysql and works perfectly.

So I guess if there is some kind of configuration that I need to do?

---

How Can I Create Dynamic Tables?
What I'm tryint to do is something similar to phpmyadmin, about creating tables in a database.

I've done the graphic interface, I have a form in which the user enters the name of the table as well as the number of fiels needed. Depending of this, then is displayed a text box and a list with the data types that exist.

The problem is I don't know how to do the php instruction with the array of the variables

---

Dynamically Create Tables?
Is it possible to write something in PHP to dynamically create tables for MySQL. For example, you want to be able to catalog an item, you want to create different catagories for the item to be cataloged, can you write a program that will allow a user of the site to create these on the fly in MySQL?

---

How To Create An Array Of Tables.
how to create an array of tables in MySql ?

I want to creat an array of tables: 900 sets of (names varchar(500), address varchar(500))
where each set indexes to a different location. (i do not want to build seperate tables, just one array of tables)

Also, if in the future, the allocated space is not enough, how can i extend the limit in the same array of tables without losing data?

thanks

---

Will Create Temprory Tables
while running one application sometime i got these errors

Status : Failure - Deadlock found when trying to get lock;
try restarting transaction
Info : CREATE TEMPORARY TABLE TBL_XXXXX_TEMP AS SELECT
... some stuff


select statement contains INNODB tables . what does it mean ?

---

Create Tables Statements
when i am doing my CREATE TABLE statements, can i put in the not null / with null and the defaults etc. at the same time ?

---

Copy .frm Files To Db1 To Db2 To Create New Tables
i have .frm,.MYD,.MYI files on a local folder.
I have installed mysql 5.0 command line client.i created a database called 'ABCD' and wish to use all these .frm , .MYD and .MYI files to create tables in this database.how do i do that?
I have MySQL Maestro software which shows no way to create tables from this .frm files.
I will have to use MySQL command line client.
Could anyone please help me with this ...or suggest some other way by which i can create the tables.

---

Create Multiple Tables From Table
I have a table called 'product' in a sql database called 'product_comparison'.
There are currently 3 fields in this table. ProductNumber (which is charvar - 255), Description (which is charvar - 255), and Baillio's (which is decimal 20,2 **it's a dollar figure**).

I have just under 10,000 lines (different products) and I'm wanting to create a table for each of the products. The tables should be named whatever the 'Product Number' is. Which is usually a combination of letters and numbers (thus the charvar). I also want to take the other 2 values and create 2 fields in their respective product tables.

I'm thinking this should be an easy statement for people as smart as all of you. But I'm having trouble. So far, (with some help) I've come up with a statement something like this:

CREATE TABLE PAGEONE SELECT * FROM product WHERE `ProductNumber` = 2;

This doesn't work becuase 'ProductNumber' isn't an integer first off, and I'm thinking even if it did work, it would still just create one table called PAGEONE and not a table for each product like I want. Is that right?

---

Software To Create Tables Schemas
I'd like to create automatically mySQL tables schemas from the created ones. Is there any know application to do it?

---

Create A New Table By Joining Two Tables Together
TableA and TableB have one field in common: studynumber.

This works:

SELECT * FROM main3 JOIN studies_001 ON main3.studynumber = studies001.studynumber;

Now I want to create a new table based on that JOIN.

I can't seem to do it - I changed the fieldname in one table to snumber to avoid duplicate fields but this does not work:

CREATE TABLE studies_try1 SELECT (main3.studynumber, main3.studydate, main3.studytitle, main3.studyintro, main3.studytext, main3.booknumber, main3.messagetype, main3.media2_filename, main3.teacher_id, main3.media1_filename,studies_001.id, studies_001.snumber, studies_001.chapter_begin, studies_001.verse_begin, studies_001.chapter_end, studies_001.verse_end) JOIN studies_001 ON main3.studynumber = studies_001.snumber;

---

Join Tables + Create View
I have 2 tables when joined generates about 30k records. I need those results to join another table with about 174k records. I know I need to create a view right?
Not too sure how to do this since I use access to do it.
I will be importing to MySQL.

To make it more complicated, I need to join those results to another table with about 20k records. If I join all these tables, that would generate well over the millions. This is why I need to create a View. Is my logic correct? Please help me here since I donot know too much about MySQL.

---

Cannot Create Tables : Forenign Keys Not Workin?
Could some on tell why the last two create table's don't work. The MYSQL gives error message(ERROR below) and "show innodb status" tells foreign key error(below). Code:

---

Can't Create The System/user MySQL Tables
I have mysql installed via rpm package, but when I check the data
dirs, the only table there is "test". The instructions say there
should be others, like users tables, and other such tables.

I stopped the server and ran mysql_install_db which is the command to
create these, I get the following excerpt:

linux:/usr/bin # mysql_install_db --user=mysql
Installing all prepared tables
050304 6:10:00 [Warning] Asked for 196608 thread stack, but got
126976
Fill help tables
050304 6:10:00 [Warning] Asked for 196608 thread stack, but got
126976
.....

But when I check, it doesn't seem to make these tables, only the test
table.

Is this normal? I thought the user table contains the login info, so
if there isn't one, how can I log in?

---

Create Tables Having Turkish Language Character
I have a problem in table names. I want to give a name to my table. But i cant.
The name is 'KiÅŸiler'. It have some characters in only turkish language.
I change my servers character set to latin5, turkish.

---

Log Into MySQL Via Shell?
i can't figure out how to log into mysql via the shell. I am using Microsoft Windows 2000 SP4, so I would guess that I have to use the CMD... but I can't figure it out. I have the 'root' user setup and a password set on it... and i know how to control my databases and create tables and modify them and everything in PHP... but i want to know how in the shell so I don't have to create a new php page and execute it every time.

---

Shell And Mysql
I am trying to run a mysql batch script through the shell. The one
problem though is that the error messages out of my script dont go to
the redirect i.e., if I run

mysql<test.sql>test.log

the error messages do no print into test.log, but to the command line.
I would like to capture the errors into this log file as well. Any
ideas?

---

Shell And Sql Dump
Does anyone know where I can find information on how to setup my shell in
unix/linux to allow me to restore a database from an sql dump...
I have a database on 83200.mysql.com (example) that I have a file mysql.sql
(also just an example).
The file was produced using an sqldump command from a web interface...
How can I configure a connection if I am not on 83200.mysql.com so that I
can import the data into a database with a name of database_wordpress

---

How To Create A Join/relationship Table That Maps To Many Tables
Let's say I had the following table structure:

CREATE TABLE customer
(
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
username VARCHAR(20) NOT NULL,
password VARCHAR(32) NOT NULL,
PRIMARY KEY (id)
) ENGINE = INNODB;

CREATE TABLE service1
(
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
customer_id INT UNSIGNED NOT NULL,
PRIMARY KEY (id)
) ENGINE = INNODB;

CREATE TABLE service2
(
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
customer_id INT UNSIGNED NOT NULL,
PRIMARY KEY (id)
) ENGINE = INNODB;

CREATE TABLE service3
(
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
customer_id INT UNSIGNED NOT NULL,
PRIMARY KEY (id)
) ENGINE = INNODB;

CREATE TABLE subscription
(
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
customer_id INT UNSIGNED NOT NULL,
start_date DATE NOT NULL,
end_date DATE NOT NULL,
PRIMARY KEY (id)
) ENGINE = INNODB;

CREATE TABLE subscription_2_service
(
subscription_id INT UNSIGNED NOT NULL,
service_type TINYINT UNSIGNED NOT NULL,
service_id INT UNSIGNED NOT NULL,
PRIMARY KEY (subscription_id, service_type, service_id)
) ENGINE = INNODB;

CREATE TABLE service_type
(
id TINYINT UNSIGNED NOT NULL,
service_table_name VARCHAR(20) NOT NULL,
PRIMARY KEY (id)
) ENGINE = INNODB;
INSERT INTO service_type (id, service_table_name) VALUES
(1, 'service1'),
(2, 'service2'),
(3, 'service3');
I need a way to map Customers to Subscriptions to Services.

Please keep in mind that one subscription record can be related to many services: even services of the same type.

The above structure would work, but I wouldn't be able to make it transactional. The problem lies with the subscription_2_service table's service_id column. That column doesn't actually reference any other table. It's conditional, meaning its value changes depending on what the service type column value is.

This makes it impossible to implement transactions because the service_id column references nothing concrete.

I considered another way of doing the above by sticking a subscription_id column in all three service tables. I don't like this approach because not all services will require a subscription. This would result in many NULL values and tight coupling between the subscription and service tables. It also seems sloppy/scattered.

I also considered a third way of creating subscription_2_service tables, such as:



CREATE TABLE subscription_2_service1
(
subscription_id INT UNSIGNED NOT NULL,
service1_id INT UNSIGNED NOT NULL,
PRIMARY KEY (subscription_id, service1_id)
) ENGINE = INNODB;

CREATE TABLE subscription_2_service2
(
subscription_id INT UNSIGNED NOT NULL,
service3_id INT UNSIGNED NOT NULL,
PRIMARY KEY (subscription_id, service3_id)
) ENGINE = INNODB;

CREATE TABLE subscription_2_service3
(
subscription_id INT UNSIGNED NOT NULL,
service3_id INT UNSIGNED NOT NULL,
PRIMARY KEY (subscription_id, service3_id)
) ENGINE = INNODB;
This third method above seems ridiculous.

The third approach does decouple the subscriptions and services from one another. Services aren't aware that they're part of a subscription and subscriptions aren't aware of what services have subscribed to them. This method still seems bloated and wrong though.

---

How To Create Relationship Between Tables Whwn Unvoidable Redundancy
If there is lot of redundancy in data in two columns in the table, and other child tables,and it can't be avoided , how we can establish relationship, between parent and child table i.e is there any possibility for establishing realation using primary,unique and foreign key. is composite key a solution for this?

---

How Can I Open MySQL Shell
I want to see shell in following stage:

Code:

---

Running Mysql From Shell
I need to run this sort of querys from shell:

delete from 3dinformatica where preco = " " ;

CREATE TABLE tudotest AS SELECT DISTINCT * FROM 3dinformatica;

DELETE FROM 3dinformatica;

INSERT INTO 3dinformatica (cr_datetime,produto,vendedor,preco,link) SELECT DISTINCT cr_datetime,produto,vendedor,preco,link FROM tudotest;

DROP TABLE tudotest;

i wanted to put the mysql cmd in a file like mysql.sh and run it from crontab.
i have about 100 querys like that i need to run at the same time.

---

Access Mysql Via Shell
I am trying to run queries against a mysql DB, for which I've been provided a uid & pwd, but for this I first have to login as:

CODEmysql -u xx -p
password: xxxx
use dbName;
query

---

Switching To Shell Prompt
how to switch to shell prompt from mysql prompt.

---

Linux Korn Shell 93
I have several korn shell scripts I use with a MySQL database on the
same server (Solaris). I am moving to a Linux environment where the
MySQL database is on another server. I would like to convert the
database connection code to connect to the database on another Linux
server. I see alot of information on Perl DBI CLI and PHP DBI CLI but
cannot find any examples of Korn Shell connecting to a database on
another server.Is there a way to accomplish a network connection to a MySQL database
from Korn Shell?

---

Get Shell Throgh Mysql Db
i want to know if its pssosible to gets MS DOS access with MySQL Control Center or another remote control tool for mysql databank

---

Linux Shell Commands
in mysql database upon update on tables mysql should intimate to the OS process that a table got updated...
how can we make it?
is any ways to do that....
updation should trigger some C or C++ function?

---

Mysql Shell Startup Script
When starting up the mysql shell, is there a way that I can automatically run a startup-script ?
Specifically, I would like to issue a BEGIN WORK everytime I open up the shell. I tried man mysql, but didn't get any the wiser.

---

Send Request From Shell To Database
Is there a meaning to send data from the Unix's Shell to a mysql database?

---

Execute A Shell Command Inside SQL
I wonder if there is anyway you can run a shell command inside   SQL.
for example "SELECT * FROM table;execute shell command"
I had googled around and didn't find anything.

---

Import Data From Shell Script
I've a comma separated file and would like to use "LOAD DATA LOCAL INFILE..." inside a shell script to import data into a table. Basically, I want the script to connect to database, complete import using "LOAD DATA..." and exit.

I understand, rows can be inserted one by one using database handles but I'd like this to be done using "LOAD DATA..." functionality.

---

How To Call Procedure From Shell Prompt
i have created a procedure to delete the history records. i want to call this procedure from shell prompt in linux.

---

Running Sql File From Shell Script
When i run the command "mysql -h host - u user - p password -D database <file.sql" (the file.sql contains an Update Scrpit) from command prompt

/usr/test> mysql -h host - u user - p password -D database <file.sql

i am able to run the file and i am getting expected results.

When i copy the same string in a shell script (suppose in update.sh) and try running it from the command prompt

/usr/test> . update.sh

i get an error as "No file or directory found"

i tried giving in the full path of file.sql and tried running hte shell script but it gave me the same results "No file or directory found".

---

Sql Database From Linux Fedora's Shell
How may I restore *.sql databases from linux shell?

I've read the use of mysql command I don't have installed.
This is a remote server with preinstalled Apache-php-mysql.

---

Mysql User With Shell Access
Is there any reason the mysql user account in a linux system would need shell access and a password? I thought it was just there to be a very underprivileged user and run the mysqld daemon.

---

Executing Shell Command From Mysql Query Browser
This works fine when executing the shell command inside the MYSQL in command line

mysql> ! ls /home/www/htdocs/test
1000.xml class email test.xml test1.xml test2.xml

But when i execute the same with dynamic SQL and executed in TOAD for MYSQL, it throws error

SET @vSql:= '! ls /home/www/htdocs/test';
PREPARE vStmt FROM @vSql;
EXECUTE vStmt;

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '! ls /home/www/htdocs/middleware/test' at line 1

Can some one clarify me Whether is that possible to execute the Shell commands from the query browser or it can be only used in the command line?

---

Query Db From Remote Server Via Shell Script - Newbie, Be Gentle.
I'm looking to connect to a remote mySQL database from a UNIX server, but don't know how.  I downloaded the 'connector' software from mysql.com, but the /bin directory is empty...

Am I missing something?

---

Extracting Field Data And Putting Each Field Into A Shell Variable?
In a MySQL query on the database (one table with 15 variable length fields,
I want to put each field into a Bash variable so that I can handle each
field as an entity.

The query I have is something like this:
mysql -e "use $database; select field1, field2, field3..., from Table1 where
fieldN like '%something%';"

can I / How do I do something like:
mysql -e "use $database; select field1,..., var1=field1, var2=field2,....;"

do
stuff with var1, var2...,
done

---

Create Single Query From Queries On Two Tables (was "Help With Query...")
I read from other thread that query inside loop is not good idea. May I ask some help how can I create a single query to the following code which I use loop.

$sql = "SELECT * FROM mytable order by points desc limit 10";
$rec = mysql_query($sql) or die(mysql_error());
$datas = mysql_fetch_array($rec);

do{
$sq = "Select * from secondtable where linkid = '$datas[id]'";
$rst = mysql_query($sq) or die(mysql_error());
$rows = mysql_fetch_array($rst);
echo "$rows[somefield]";
}while($datas= mysql_fetch_array($rec));
This works perfectly but I want the second query to be out of the loop if there is a way and how.

---

Create Another Field After Create Table
i have create a table from CLI, i would like to add another field after create table.
How to do that ?

---

How To Create Different Namespaces When Using CREATE VIEW?
I'm doing a triple join between tables which have similarly named fields...I was hoping that when using CREATE VIEW, the result would be a table with fields such as a.field, b.field, c.field...but I get an error "Duplicate column name 'field'"

...How can I get around this besides changing the name of the duplicate field in each table?

---

Could Not Connect To The Database, Please Recheck The Settings You Specified. Also Make Sure That A Database With The Name You Specified Already Exists On The Database Server. PhpAdsNew Will Not Create The Database For You, You Must Create It Manually
The error message I get above is the same everytime when trying to install phpAdsnew. I have tried everything. I am hosted through yahoo! and have actually succesfully installed scripts before even though I know nothing about it. Now that I have a "auto-install", it does not work. Yahoo! also offers php-nuke with the auto-install where you just click a few buttons, and I get a similar error stating that it can not conect to the database.

Please can anyone help me and tell me what I am doing wrong?

---

Maximum Allowed Tables/db, Fields/tables, Records/table
1)tables that can be created in a MySql database.
2)fields that can be created in a MySql table.
3)records allowed in a MySql table and in a MySql database.
4) allowed joins in a MySql table/database.

---

Cannot Alias Locked Tables / Join To Unlocked Tables ?
If you manually issue a table lock then query that table, aliasing the table generates an error. If you try to join the table to another table that is not locked, you will receive an error. What is the reason for this?

---