MYSQL :: Numbers In A Mysqldump File Mean

Mysqldump File
I know I knew how to do this, and I know that I found it somewhere in the manual, but it's Monday and I'm heading out so I thought I'd ask you all and hopefully have an answer when I get back on Tuesday so I can stop banging my head against the keyboard.
Recently set up mysql 4.0.20a on a development machine to try and upgrade to 4.1 before I blow up the live database and I make an hourly dump form the live database using mysqldump.I grabed the latest dump and moved it to the development machine but I can't for the life of me recall how to take that file and make it create all the database, tables, and data on the new install.
Any thoughts linkage to the right portion of the MySQL manual definatly acceptable
As an aside, did anyone else find the MySQL page to be extremely slow today (2/7/2005)

Mysqldump Backup To More Than One File
We currently use mysqldump to dump all our db's in quick mode to one
file, backup.sql. This file has come quite large.

mysqldump -Aq -u root > /MySQL/backup/backup.sql

I would now like to split the mysqldump so that the output comprises
of several files of less than 2 gigabytes each. Does anyone know the
mysqldump options that could be used to do this? Or with using the >
out command?

Import Only 1 Database From A Mysqldump File
i like to import only one Database include 37 Tables from a dumpfile. But i must be shure that only this one Database is imported. The dumpfile include more Databases (70) with >1500 Tables

Can i use this --database=onlyforum option for mysql ?

mysql -hlocalhost -uuser -p --database=onlyforum < alldatabases.sql

ore is there a other option to use?

Mysqldump Produces An Empty File
i am having a problem with mysqldump.i am running this script from the
shell

root@linuxserver:/usr/local/mysql#bin/mysqldump u im_backup --opt
inventory /home/all/sqlbu/inv_'date +%F.sql

(user=imbackup
dbase name = inventory)

i don't know what am i missing or what's the problem with this line
since it generates only an empty file...

i created the user im_backup granting all privileges. There was no
error messages too.. what's the problem w/ this?

Sheduled Mysqldump Via Windows Bat File
I want to make a scheduled task in windows to do a mysqldump. Someone gave
me this .bat to run but I'm not able to get it to work.

REM @echo off

for /f "tokens=1" %%i in ('date /t') do set DATE_DOW=%%i

for /f "tokens=2" %%i in ('date /t') do set DATE_DAY=%%i
for /f %%i in ('echo %date_day:/=-%') do set DATE_DAY=%%i
for /f %%i in ('time /t') do set DATE_TIME=%%i
for /f %%i in ('echo %date_time::=-%') do set DATE_TIME=%%i
C:Program FilesMySQLMySQL Server 4.1in
mysqldump -u user -p'pass' dbname >C:DATE_DAY%_%DATE_TIME%_database.sql

When I run this script it doesn't work. All I get is a blank .sql file in
the output directory.

Can I Use Mysqldump To Dump A Query Result Into A CSV File?
Can I use mysqldump to dump a query result into a CSV file?

How Do You Import A Mysqldump File Using Query Browser?
I have a MySQL 4.1.5-gamma-nt-max instance running on a Windows 2000 Server box. I have tried creating a script to restore a mysqldump backup file to a schema on that server. Here is my syntax:

set foreign_key_checks=0;
source b:/folderwithdata/data.043006
set foreign_key_checks=1;

When I execute this locally on the server from the command line, it executes without a hitch. However, the server is going to be located in a server farm where I will not have access so I have tried to execute this from my workstation using Query Browser. I launch Query Browser and execute the script and get the following error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use. ErrorNr: 1064 near 'SOURCE B:/FOLDERWITHDATA/DATA.043006 SET FOREIGN_KEY_CHECKS=1' at
line 1

Like I said, it executes fine on the server via command line so I know it's not bombing out on some syntax within the file itself. So I'm assuming it has something to do with how I'm writing the script in the GUI itself.

Contents Of A Mysql File Created By Mysqldump
I want to view the contents of a .mysql file created by using mysqldump. Short of reading it back into a database using mysql, is there a way just to view what tables are in it?

Mysqldump: Got Errno 27 On Write. File Too Large
I have musql 4.1.7 on Solaris 9, 64 bits and I want to mysqldump a +-4 gigas db.

The dump I want to do is from a myISAM tables databank. I used to do that dump wihtout any problem until recently while the size of the Db double (+-2 to +-4) Code:

Mysqldump: Got Error: 23: Out Of Resources When Opening File '.datapallc052108.MYD' (Errcode: 24) When Using LOCK TABLES
I want to dump my database in a SQL File But I am getting the error

mysqldump: Got error: 23: Out of resources when opening file '.datapallc052108.MYD' (Errcode: 24) when using LOCK TABLES

I just tried to search on the net but i was not able to find any documentation relating to the error. I need to get this thing done

How To Import "mysqldump" File Into Different Database?
We have successfully used "mysqldump" to create backup files for our databases on our mysql installation. We wish to test the quality/integrity of our dump files by importing a dump file into an empty database having a name different from the original source database from which we created the mysqldump file.

Can someone offer an example of a command we would use to restore a dumpfile into a database different from the original source database?

Mysqldump, Getting Error Error #1016, Can't Open *.ibd File
So we have a MySQL database that suddenly started reporting "can't
open *.ibd file" errors on InnoDB tables in phpMyAdmin. (MyISAM are
fine). To recover the data is all I want at this point -- so one
suggestion I came across was to try and dump using mysqldump like
this:

mysqldump -u root -p mydatabase databasedump.sql
Enter password: *******************
mysqldump: Got error: 1016: Can't open file: 'access.ibd' (errno: 1)
when using
LOCK TABLES

Is there a way to run this dump so that it will export the database
tables and ignore the *.ibd error? I had read that removing *.frm
files might help, but our testing suggests that it isn't effective
either.

How Do I Add 4 Numbers?
I have a table in four databases. I want to count the number of rows in each table. I have a file for each databses, and it looks like this:

<?php

$connect = mysql_connect("localhost", "root", "PASS");
mysql_select_db("DATABASE_GOES_HERE", $connect);

$result = mysql_query("SELECT * FROM cheats");
$num_cheats = mysql_num_rows($result);
?>

So, I have four of those. I want anouther file to add the 4 $num_cheats from those four files, then echo it out into the adding file, then include them into the page that the user will see. Sorry if that doesn't make any sence, but i'm not to good at explaining stuff. So, How would I do that.

Odd And Even Numbers
I have a table that has three fields that I want to sort: txtStreetName, txtHouseNum, txtAptNum(all string values). The kicker is that I want the sort to be by street name with all the odd house numbers sorted first followed by all the even number house numbers: e.g. 101 Apple, 103 Apple, 105 Apple then 100 Apple, 102 Apple, 104 Apple, etc and if 101 Apple was an apartment block then I want the list sorted by aptNum. Can anyone suggest a solution to this problem?

Insert Numbers Like 111.222.333
Like title says, i need to insert numbers like 111.222.333 in table, but i don't know what type is best for this... I have tryed almost all and many of them cut rest of 111.222....

I can't use varchar because of sorting...

How To Order Numbers Like: 20,18,4,3,2 Rather Then 4,3,20,2,18
I'm having trouble sorting a request, what I am getting is this:

SELECT * FROM table WHERE column = value ORDER BY column DESC

5
4
3
20
2
19
18

When I would like this:

20
19
18
5
4
3
2

Is there a way to do this?

Storing Numbers
How do I store a number in this format... 2.00

ORDER BY Numbers?
I am trying to do an ORDER BY clause on a field that holds prices but when I run the select it orders by prices but the output is like:

10.49 then 2.25 then 21.00

I assume this is because the field is being treated as a string vs. as an integer.

How can I correct this so my select statement will output like:

2.25 then 10.49 then 21.00

Getting Funky Numbers From Sum()
SELECT `itemProdId` , `itemProdQty` , `itemProdSize` , sizeUnitPrice, sum( `itemProdPrice` ) as calcTotal
FROM items
LEFT JOIN sizes ON itemProdId = sizeProductId
where `itemCustId` = -1
and `itemOrdId` = 36
group by itemProdId

i entered test data and this query only finds one row calcTotal should = 1.00 but when i run query calcTotal = 4.00 the only time i get the correct sum() is when i change it to a single table query?

SELECT The Numbers 1..10
I would like to be able to generate multiple rows from a SELECT statement that are not related to rows in a table. For example:

SELECT nums IN (1 to 5);
This doesn't work, but I would like the following result:

nums
-----
1
2
3
4
5

How can this really be done?

Negative Numbers
What is the correct definition for a column to allow negative decimal numbers? I've been using float 5,2 but when the numbers come into the table they are shown as 0. If I look at the console, it does show the negative numbers being imported, but content shows no value.

Sum With Negative Numbers
I have a table with two integer fields (field1 and field2).

field1 = 2
field2 = 5

Then, i make the following SQL:

select field1 - field2 from test;

It returns a giant positive number instead of the -3 witch is the right answer.

Why is that ?

Letters And Numbers
I'm working on a PHP form that allows for data entry into a MySQL DB. Some of the fields are radio buttons with values of 5,4,3,2,1, and 'NA' respectively.

In another part of the script I average the values of all the radio button groups. However, my script (or I think perhaps MySQL) is counting the 'NA' value as 0. What I'd really like is if MySQL (or PHP) were to count 'NA' as a null value that didn't register in a SQL averaging algorithm as a value.

Is there any way to make MySQL/PHP recognize that 'NA' is a value that is not be counted?

Numbers And MySQL
I am trying to save a number in the data base, this numbers is: 19566.04 and in the table the properties for this field is: float(10,2)
It works fine saving the number but when I do a select to read the number it reads 19566.00 .

Sorting Numbers
I got a list of movies and would like to select alle the movies starting
with a numer 0-10 and another symbol (., #, !, etc.) but I don't know
how to get them all in the same query. How do I do that?

My query should look like this, but I don't know what to put instead of
the ??
"SELECT * FROM movies WHERE title = ?? ORDER BY created"

DISPLAY NUMBERS
HOW CAN I DISPLAY NUMBERS LIKE 123,456.00 IN MYSQL?

Actually in phpmyadmin.

when i choose decimal or double i can get two zeros ( like anynumber.00)

what i wan to see is ###,###.00

Matching Id Numbers
I have a problem with a user management system.
Simply i have 3 tables.

Table 1 - ID1, Name, Pass

Table 2 - ID2, Name, ItemID

Table 3 - ID3, ItemID, ItemName

Let user login using info from table 1, once logged in query table 2 to get all the ItemID's for the logged in user.Once this is done I need to finally query table 3 looking for matches to the itemid's from table 2 in order to show how many orders for each item but only when the itemid is associated with the user from tables 1 and 2.

Numbers Separated
having this table

id name week_days
1 xpto 2;3
2 optx 1;4;5

and want to see if a string, e.g $days="2;6;7" exists in table

The numbers are the day of week 1- Mon 2- Tue 3-Wed etc
Want to do a select where $days exist in week_days. For the example given, Tue exists in id=1

Select Where Name = Numbers
How to select only names that begins with numbers (0-9).

i.e I just want to display
311
50 Cents
10000 Maniacs
.....

Rows With Only Numbers
Is there a way I can run a query that selects from a colum that only has numbers and not letters?

Letters & Numbers?
Is there a way i can update a field for every record with a random letters OR numbers? Reason is i have 25,000 people in my mailing list but I have created a login for all the members but they have no password.

i want to randomly given them a password which is stored in the mySQL db.

i.e. UPDATE nn_users SET password = 'random';

Total Numbers
I need to count total number of records present in 2 tables. I am having a working query for
SQL Server but it doesn't work in mySQL.

select sum(a.total1) from
(select count(*) total1 from CoreJava_Code
union all
select count(*) total from CoreJava_File)a

Max Numbers Of Characters
Is there any way to have more to store more than 255 characters in a field?

Search Between Numbers
I need to search a field in my database, and check if it's between one number and another.For example... They can input 10 and 20 and it'll find all entries inbetween them numbers. Which is the best way to do this?

Select Certain Numbers
SELECT * FROM table WHERE status != '0' AND status != '2' AND status != '4' AND date <= '$current_time'

So if I don't want to SELECT from status when it equals 0,2,or 4 should I use this statement:

SELECT * FROM table WHERE (status != '0' OR status != '2' ORD status != '4') AND date <= '$current_time'

or what?

the status field only has a number 1 thru 5 in it.

Concat For Numbers
I am interested in performing a concat for numbers with a space
e.g.
select concat (num1, ' ', num2) from tablex as numconc

such that if num1 is 123 and num2 is 456 the result would be '123 456'
I believe concat should work for a string rather than number format, however having created the fields num1 and num2 in string format it still does not work.
Any advice on why this isn't working, or an alternative method to achieve the result would be most welcome.

Remove Numbers
I got a post_title field and some entries have numbers before it.
Example: 3232243 how to eat food. I'd like to find away to remove the numbers from the beginning.

DISTINCTed Count Numbers
data in myTable1
(n) country_id
(1) 3
(2) 1
(41) 1
(5) 2
(6) 3
(7) 4
data in myTable4
(time) param
(10 : 10) c=4
(10 : 12) c=2
(10 : 30) n=41&k=5
(10 : 35) c=1
(10 : 37) n=5
(10 : 50) c=2
(10 : 54) c=2
(10 : 55) n=1&cate=6
(11 : 12) c=2
(11 : 15) n=7
(11 : 20) c=1
I have data in myTables like the above.
The followig code1 produces the following result1.
code1
select distinct time
from myTable4,myTable1
where
(left(param,1)='n'
and
substring(substring_index(param, '&', 1) ,3)=myTable1.n
and
myTable1.country_id=1 ) // 10 : 30
or
(left(param,1)='c'
and
substring(param,3,1)=1 ) // 10 : 35 + 11 : 20
order by time
result1
(10 : 30)
(10 : 35)
(11 : 20)
The following code2 produces the following result2.
code2
select distinct left(time,2) as hour, count(*) as count
from myTable4,myTable1
where
(left(param,1)='n'
and
substring(substring_index(param, '&', 1) ,3)=myTable1.n
and
myTable1.country_id=1 )
or
(left(param,1)='c'
and
substring(param,3,1)=1 )
group by left(time,2)
order by time
result2
(10) 7
(11) 6
I don't like the count value 7 and 6 because they are not DISTINCTed count numbers.
I like to produce the following target result.
target result
(10) 2 // 10 : 30 + 10 : 35
(11) 1 // 11 : 20
How can I get my target result above with the data in myTables in mySQL 4.0?

Defining Decimal Numbers
I am having a problem defining decimal numbers such that MySQL will accept the syntax I give it.
I believe the correct syntax is dec(4,2) however my MySQL rejects this.
The attachment shows the ouptut from mt SQL command line.
It doesn't seem to like the ',' inside the '()'. Unless of course the error is elsewhere.
My knowledge of SQL is still very rudimentary.

How To Get Unique Numbers From Database
I want a 6-8 digit unique number. Is there someway by which the database keeps generating these numbers for me in a random fashion and make sure all the numbers are unique and not-guessable???

Query Munching My Numbers
I have this query that basically looks at a table of votes and their respective scores and returns a list of nodes with a total score for each node:

SELECT DISTINCT(n.nid), n.title, SUM(nv.vote) AS score
FROM node n, nodevote nv
WHERE n.nid = nv.nid
GROUP BY n.nid
ORDER BY score DESC
LIMIT 0, 5

The above works great. But when I add a node_access table to the query in an effort to just see scores of nodes I have access to, the scores come out 4 to 6 times as large as they should be. It almost seems like the score is doubled each time is passes one of the arguements in the where clause. Can you see where I'm missing something?

SELECT DISTINCT (n.nid), n.title, SUM(nv.vote) AS score
FROM node n, nodevote nv
INNER JOIN node_access na ON na.nid = n.nid
WHERE (na.grant_view >=1 AND ((na.gid =0 AND na.realm = 'all')
OR (na.gid =0 AND na.realm = 'og_public')
OR (na.gid =0 AND na.realm = 'og_all')))
AND n.nid = nv.nid
GROUP BY n.nid
ORDER BY score DESC
LIMIT 0, 5

Posting Numbers Beginning With Zero Into DB
i've just upgraded to a better reseller host, who is running mysql 4.1 and php 5.x. When i'm trying to insert a phone number into a INT column, it is stripping the trailing slash. This possibly is not a new problem since upgrading, but the first time i've tried inserting a number starting with a zero.

Despite my best search efforts, the best solution I can come up with is to use a varchar/text field to store the number.

Numbers Showing As Null
I am running a query where I want students not assigned a test location to show up, and ones that are assigned to not show up. The site locations are numbers. When I run a Where statement classlist.testsite is null, it ignores the Nulls and still brings up the records that have numbers in them..?

Sequential Row Numbers In A Query
I need to generate a report with a sequential row number shown in every row.

For example: SELECT * FROM employees gives:

Multipling By Negative Numbers
how do i mulitiple by a negative numbers, if i have

Select This.That*-50 the it multiples it by 50 not minus 50.

The value in This.That will either be a 1 or a zero. So if its a zero multipling it by -50 will give me zero, multiplingit by one with giveme -50, which is what i want.

How do i solve this?

Generating Random Numbers
I want to generate some random numbers and add it to a column called 'epass'. I was wondering if mySQL could do this.

Inserting Decimal Numbers
I am using MySQL Version 4.1.18-nt. I have created a table with a column set to Decimal(4,2). When I insert the number 1.0 or 1.06 the table is populated with 1.

Order By Letters And Numbers
I have sporting league tables which have the divisions as 'P' (Premier), '1', '2' and '3'.

I have this code installed: ORDER BY division ASC, which puts 'P' at the bottom of the list. How can I put 'P' at the top? (then 1,2,3).