MySQL Newbie

Hi all, hope in your help.

This is my mysql table:
+----+--------+--------+ | id | field1 | field2 | +----+--------+--------+ | 1 | A1 | 7 | | 2 | B1 | 9 | | 3 | C1 | 0 | | 4 | D1 | 3 | | 5 | A2 | 5 | | 6 | B2 | 6 | | 7 | C2 | 7 | | 8 | D2 | 8 | +----+--------+--------+ I need this output:
+--------+--------------------+ | field1 | field2 | +--------+--------------------+ | A2 | 19.230769230769200 | +--------+--------------------+ | B2 | 23,076923076923100 | +--------+--------------------+ | C2 | 26,923076923076900 | +--------+--------------------+ | D2 | 30,769230769230800 | +--------+--------------------+ and tried this query:
SELECT field1, field2/Sum(field2)*100 as field2 FROM `tbl_t` WHERE field1 IN ('A2', 'B2', 'C2', 'D2'); +--------+--------------------+ | field1 | field2 | +--------+--------------------+ | A2 | 19.230769230769234 | +--------+--------------------+ 1 row in set But the ouput is not what I want, can you help me?
Thank you
Any help would be greatly appreciated.

Hi,
I am trying to set up a FEDERATED table between two MySQL servers. I have created a separate user for this and enabled remote connection to the host server.

On the client server (meaning on the one where I create the FEDERATED table that connects to a table on the host server) I have enabled the FEDERATED enginge.

From the client server I can connect to the host database if I run mysql -h myhost -u myuser -p. I can select from all tables without issue.

Now the problem is that I can not access the data via the FEDERATED table. I can create it without problem and I get no error message. However, when I try SELECT * from myfederatedtable; then I get:
ERROR 1429 (HY000): Unable to connect to foreign data source: Can't connect to MySQL server on '196.168.2.224' (110)

What can be the reason for this?

Best regards

Jacek

Hello.

Please tell why does the following query not work?

DELETE FROM ytgb1_virtuemart_product_medias pm
WHERE NOT EXISTS (
SELECT virtuemart_media_id
FROM ytgb1_virtuemart_medias m
WHERE m.virtuemart_media_id = pm.virtuemart_media_id
)

We have two WinXP machines, let's call them #1 and #2. They have identical software configurations except #1 is 5+ years old and has an older Intel chip and 3.5GB RAM. #2 is 2+ years old and has a dual core Intel chip and 2GB RAM. Each machine is connected to its own robot that collects data and interacts with a MySQL db v5.1.61 that was installed on #2.

Since neither #1 nor #2 is connected to a backup server, we moved the db to a smoking fast Windows 2008 Enterprise server with the same version of MySQL. That server hosts several IIS apppools for ASP pages and hosts ColdFusion pages, but this MySQL db is so small and simple in comparison, it can't be suffering. The robot manufacturer did the db migration, had to make some changes to their code, and everything works fine. The software config on each machine is the same.

The problem is when #1 interacts with the db, an operation that took <1s is now taking 3-4s while it waits for a response from the db. I did pings to the MySQL server as well as other servers from each machine and there is no difference in network connectivity. I have tried running #1 while #2 is shutdown and there is no improvement in speed. I have also changed the db connection to use the IP address instead of DNS name with no improvement.

I'm no dba and no IT guy--I'm the scientist end user. None of the admins (dba, IT admins, or company men) know what could be wrong so I put it out to ya'll. What do you think is going on?

Thanks,
Paul

I have successfully installed php and MySQL with Apache server and I believe all are working fine together.

I am trying to connect to my MYSQL database via php, I have used this code to do that, but I get a fatal error:

//connect to a single database
$con=mysqli_connect("localhost", "root", "1234", "SIGN-TECH");

//check connection
if (mysqli_connect_err($con))
{
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

?

Fatal error: Call to undefined function mysqli_correct_err() in C:\Apache24\htdocs\mysql_connect2.php on line 7

When I look at my php info I have this enabled, I read elsewhere to check to make sure of this and it is enabled:


mcrypt support enabled


mcrypt_filter support enabled

Version 2.5.8
Api No 20021217
Supported ciphers cast-128 gost rijndael-128 twofish cast-256 loki97 rijndael-192 saferplus wake blowfish-compat des rijndael-256 serpent xtea blowfish enigma rc2 tripledes arcfour
Supported modes cbc cfb ctr ecb ncfb nofb ofb stream



Directive Local Value Master Value

mcrypt.algorithms_dir no value no value
mcrypt.modes_dir no value no value

I want to set-up data replication on MySQL in a way so that I receive all updates from master to replicated DB however I should be able to periodically purge data from the replicated server. Objective is to be able to run some reports from replicated server but remove data older than x days so that report query time is faster.

Would appreciate any pointer on this. Is there some other mechanism to achieve this requirement?

Hi,

I want to increase value of max_allowed_packet variable.

I used one command, SET GLOBAL max_allowed_packet=75485760
but when server get restarted, value of variable reset to default value
i.e. max_allowed_packet=10485760.

So there is one more method i.e. doing change in my.ini file. I done that but when I queried in database it shows default value and not changed value.

show variables like 'max_allowed_packet
max_allowed_packet=10485760;

Please help.

I installed MySQL Community Edition 5.6.11 (MySQL Workbench CE 5.2.47) on my Windows 7 system. However whenever I click on Server Administration -> Manage Security I get the following error:

Error Starting Workbench Administrator
Attribute Error: 'NoneType' object has no attribute 'parameterValues'

How can this be fixed so that I can use Manage Security to grant privileges, add user etc. ?

When I give the command
mysqlsla -lt slow -- sort -mf 't_avg > 5' /home/tpbackup/log/db1.totalpaas.local/mysql-slow.log.1 > /home/pujit.koirala/sample/t_avg1.txt

I get the output but when reading the t_avg1.txt file using vim at the top there is as follows
Report for slow logs:sort /home/tpbackup/log/db1.totalpaas.local/mysql-slow.log.1
2.21k queries total,533 unique
sorted by ,t_sum,
Grand totals: Time 32.14ks,Lock 1 s,Ros sent 6.56k, Rows examined 244.40M


My question is after i gave -mf 't_avg > 5',there is sorting on the basis of t_sum.

Can any one explain me this.I want to sort suppose all the queries having execution time greater than 2 sec than what will be the possible command.

I have a table that contain a column like as-

Course_Name Status

Math Complete
Math Complete
Math Complete
Science Incomplete
Science Complete
Science Complete

I need the sql query who give % complete of each course which output like as follows-

Course_Name % status_Complete
Math 100%
Science 33.3%

Assume I have a table and an unknown number of depending (primary) indexes.

When I now simply enter a DROP TABLE command then I got errors because of
still depending objects/constraints.

Is there a way to DROP a TABLE and all (unknown) (primary) INDEXES in one command? I mean something like:

DROP TABLE mytab123 INCLUDEDEPENDINGINDEXES;

Peter

Hello I have 2 quesitons about the query below:
1 - It takes almost 20 seconds. How else can I optimise?
2 - Why not use convering_index_three?
Thank you
============================================================

SELECT `products_table`.`id`,
`code`,
`class`,
`category`,
`status`,
`price`,
`production_date`,
`products_status`.`title` AS STATUS
FROM products_table
JOIN `products_status`
ON `products_status`.`id` = `products_table`.`status`
WHERE `products_table`.`id` > 0
AND `class` = '6'
AND `category` = 'E'
ORDER BY `products_table`.`id`
LIMIT 50

================================

CREATE TABLE `products_table`
(
`id` INT(11) NOT NULL auto_increment,
`code` VARCHAR(4) NOT NULL,
`class` INT(11) NOT NULL,
`category` VARCHAR(5) NOT NULL,
`price` DECIMAL(6, 2) NOT NULL,
`production_date` DATE NOT NULL,
`quick_description` VARCHAR(255) NOT NULL,
`description` TEXT NOT NULL,
`status` INT(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `production_date` (`production_date`),
KEY `index_price` (`price`),
KEY `class_index` (`class`),
KEY `category_index` (`category`),
KEY `status_index` (`status`),
KEY `covering_index_class` (`class`, `category`, `price`, `production_date`
, `status`),
KEY `covering_index` (`id`, `code`, `class`, `category`, `price`,
`production_date`, `status`),
KEY `covering_index_three` (`id`, `class`, `category`, `price`,
`production_date`, `status`),
KEY `covering_category` (`id`, `category`, `price`, `production_date`,
`status`)
)
engine=innodb
auto_increment=7642518
DEFAULT charset=latin1

=================================

Indexes: http://webmoosh.com/aashghalduni/ax/indexes.png

EXPLAIN: http://webmoosh.com/aashghalduni/ax/explain_sql.png

Am I angry? Very. Am I learning? Not much! Am I being patient? Very. I feel like I am running the marathon with metal shoes and a bag full of sand on my shoulders, burning the engine and wasting time and passion.

======================================

Issue number 1:
Quote:
Use "EXPLAIN" to see the execution plan and improve the performance. If it doesn't return a great number of 'rows' then it is a good candidate.
This is WRONG advice! Proof: "rows" in example 1 are only 188 and it takes 3.9150 seconds while "rows" in example 2 are 3012128 and it takes 0.3491

What is going on? How can I test, watch and learn from something that doesn't make sense? There is absolutely no relation between those numbers! Even if the number is approximate because of INNODB it does not make ANY sense at all! Most analysis in books or forums are based on that!

EXAMPLE 1: (Query took 3.9150 sec)
Code:

SELECT `products_table`.`id`,
`code`,
`class`,
`category`,
`status`,
`price`,
`production_date`,
`products_status`.`title` AS STATUS
FROM `products_table`
JOIN `products_status`
ON `products_status`.`id` = `products_table`.`status`
WHERE `products_table`.`id` > 0
AND `class` = '1'
AND `production_date` = '1972-01-19'
ORDER BY `products_table`.`id`
LIMIT 50

Type: Index_merge, Key production_date,PRIMARY, Rows:188

EXAMPLE 2: (Query took 0.3491 sec)
Code:

SELECT `products_table`.`id`,
`code`,
`class`,
`category`,
`status`,
`price`,
`production_date`,
`products_status`.`title` AS STATUS
FROM `products_table` USE INDEX (primary)
JOIN `products_status`
ON `products_status`.`id` = `products_table`.`status`
WHERE `products_table`.`id` > 0
ORDER BY `products_table`.`id`
LIMIT 50

Type: range, Key PRIMARY, Rows:3012128, EXTRAusing where








Issue number 2:
MySQL is DUMB! Why? In example 3 why doesn't it simply use primary key??

EXAMPLE 3: (Query took 1007.1933 sec)
Code:

SELECT `products_table`.`id`,
`code`,
`class`,
`category`,
`status`,
`price`,
`production_date`,
`products_status`.`title` AS STATUS
FROM `products_table`
JOIN `products_status`
ON `products_status`.`id` = `products_table`.`status`
WHERE `products_table`.`id` > 0
ORDER BY `products_table`.`id`
LIMIT 50

Type: Range, Key:covering_index,Rows2207384, EXTRAUsing where; Using index

Primary key works in that case. Proof: Example 4.

EXAMPLE 4: (Query took 0.0026 sec)
Code:

SELECT `products_table`.`id`,
`code`,
`class`,
`category`,
`status`,
`price`,
`production_date`,
`products_status`.`title` AS STATUS
FROM `products_table` USE INDEX ( primary )
JOIN `products_status`
ON `products_status`.`id` = `products_table`.`status`
WHERE `products_table`.`id` > 0
ORDER BY `products_table`.`id`
LIMIT 50

Type: range, Key:PRIMARY, Rows:3016458, EXTRA:Using WHERE









Issue number 3:
Why not use the proper index 'covering_index_three which is on ( id, class, category) columns?
EXAPLE 5: (Query took 154.00 sec)
Code:

SELECT `products_table`.`id`,
`code`,
`class`,
`category`,
`status`,
`price`,
`production_date`,
`products_status`.`title` AS STATUS
FROM `products_table`
JOIN `products_status`
ON `products_status`.`id` = `products_table`.`status`
WHERE `products_table`.`id` > 0
AND `class` = '2'
AND `category` = 'F'
ORDER BY `products_table`.`id`
LIMIT 50

Type: ref, Key:status_index, ref: testing.products_status.id, Rows:3751, EXTRA:Using WHERE


INDEXES
Code:

CREATE TABLE `products_table`
(
`id` INT(11) NOT NULL auto_increment,
`code` VARCHAR(4) NOT NULL,
`class` INT(11) NOT NULL,
`category` VARCHAR(5) NOT NULL,
`price` DECIMAL(6, 2) NOT NULL,
`production_date` DATE NOT NULL,
`quick_description` VARCHAR(255) NOT NULL,
`description` TEXT NOT NULL,
`status` INT(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `production_date` (`production_date`),
KEY `index_price` (`price`),
KEY `class_index` (`class`),
KEY `category_index` (`category`),
KEY `status_index` (`status`),
KEY `covering_index_class` (`class`, `category`, `price`, `production_date`
, `status`),
KEY `covering_index` (`id`, `code`, `class`, `category`, `price`,
`production_date`, `status`),
KEY `covering_index_three` (`id`, `class`, `category`, `price`,
`production_date`, `status`),
KEY `covering_category` (`id`, `category`, `price`, `production_date`,
`status`)
)
engine=innodb
auto_increment=7642518
DEFAULT charset=latin1

I have MySQL installed on a Mac (latest versions of all hard and software) which is acting as a server and I am accessing the database remotely from other computers.

I find I can only access the database if I drop the Mac firewall entirely. Macs allow you to specify which applications are allowed through the firewall via security settings. I've tried adding all apps on the mac with mysql in their names but still no joy.

How can I have the server running accepting remote access with the firewall up?

Hello -

I'm trying to move a mysql database off of a Linux machine to a Windows machine running MySql. I'm attempting to do this using 'mysqldump' but I keep getting a syntax error. Any help would be greatly appreciated.

Here is what I'm typing in;

mysql>mysqldump norfolk > dump.sql;

ERROR 1064 (42000): 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 'mysqldump norfolk > dump.sql' at line 1

I also tried;

mysql>mysqldump --databases norfolk > dump.sql but I get the same error.

Any help would be great appreciated!

Thanks
Ryan

I'm not sure I qualify as a newbie, but I didn't find a specific place for upgrading , so I'll give it a go in here.
I am running a MySql (5.0.51a-3ubuntu5.1-log) database.
I wish to upgrade to 5.5, and at the same time, I'd like to upgrade my OS to a newer Ubuntu-version.

Am I safe if I simply make a full dump, then wipe the entire server, install my new OS, then MySql 5.5 and load the dumpfile into the new database? Or would there be problems? I am trying to avoid the extra work with installing mysql 5.0, then upgrading to 5.1 and then to 5.5 here, ofc.

I use InnoDB only, utf8_general_ci, for all tables in all databases, if that is relevant.
I don't have any stored procedures or views, I only have a few triggers (which I can recreate easily) so that's not very relevant to me.

From what I understand, there can be issues with the users and privileges, but is that the only problem I am likely to run into? I only have 2 users that I can recreate easily, so I am not too worried about that.

So, are my dumps safe, will they go into a MySql 5.5 database as they are? Or do I need to run mysql_upgrade and go through all versions?

Hoping for an answer

- Christian

Hi. My intro.
I have generated some array from PowerShell. It looks like several:
DistinguishedName : CN=PC1,OU=COMPUTERS,OU=CITY,OU=FIRMA,DC=domain,DC=LOCAL
DNSHostName : PC1.DOMAIN.LOCAL
Enabled : True
Name : PC1
ObjectClass : computer
ObjectGUID : f8da0000-f4a9-45cd-a750-5f2e1df3b6db
SamAccountName : PC1$
SID : S-1-5-21-187615067-1267644000-423120465-1286
UserPrincipalName :

DistinguishedName : CN=PC2,OU=COMPUTERS,OU=CITY,OU=FIRMA,DC=domain,DC=LOCAL
DNSHostName : PC2.DOMAIN.LOCAL
Enabled : True
Name : PC2
ObjectClass : computer
ObjectGUID : f8da0000-f4a9-45cd-a750-5f2e1df3b6dc
SamAccountName : PC2$
SID : S-1-5-21-187615067-1267644000-423120465-1287
UserPrincipalName :

My case:
I set data this array to variable $computers:
$computers = MyPowershell.command
And now i want insert separate data from array:
"INSERT INTO computers (computers) VALUES ('$computers[0].Name')"
But in database is entered only first line from array:
CN=PC1,OU=COMPUTERS,OU=CITY,OU=FIRMA,DC=domain,DC=LOCAL
if i try enter command $computers[0].Name in powershell i am getting right data:
PC1
My question:
why mysql query not understand $computers[0].Name ? How i can isert only PC1 into database?

Thank you!

i want to create stored proc and insert data in a table where controlno increment

CREATE TABLE table_agency (agencyCd varchar(12),agencyname varchar(100),controlNo varchar(12);

insert into table_agency(agencyCd,agencyName,controlNo) values
('OTH','fire','201301010001');

the output should like this:


agencyCd | agencyName | controlNo
OTH | FIRE | 201301010001
OTH | FIRE | 201301010002
OTH | FIRE | 201301010003
OTH | FIRE | 201301010004

There are some very pleasant battle clips that are strong and very well implemented. Gina Carrano along with Michelle Rodriguez actually battle one other 2 times within this picture, so when the supporter connected with each women of all ages, it had been pleasant to see. That is Carrano carrying out precisely what your woman can best, along with Rodriguez's organic look provides the two annoyance along with paralyzing desparation which your ex figure, Letty, will go by means of regarding a lot of the picture. Likewise, Rodriguez keeps her own versus Carrano, who has been an actual MMA fighter prior to your ex performing occupation.


Watch Fast & Furious 6 Online

I wanted to sort all the slow queries form log file that took execution time greater than 2 second and for that I put the following command Here "at>2" is used to sort for the queries that took greater than 2 second

mysqlsla -lt slow --sort at > 2 /home/tpbackup/log/db1.totalpaas.local/mysql-slow.log.1
This command gives me the error as follows

Use of uninitialized value in numeric comparison (<=>) at /usr/bin/mysqlsla line 2962.

Can any body help me to solve this problem.