Search Results

Search found 36756 results on 1471 pages for 'mysql real query'.

Page 254/1471 | < Previous Page | 250 251 252 253 254 255 256 257 258 259 260 261  | Next Page >

• why does mysql have so many more open and fragmented tables than tables in the DB?

  - by kswift

  I've been working making our database run a little smoother and had good results over the past week. But there are still some things I dont understand. For one thing, the database has 25 tables. But mysql status shows 512 are open: mysqladmin status Uptime: 212854 Threads: 1 Questions: 43041 Slow queries: 7 Opens: 2605 Flush tables: 1 Open tables: 512 Queries per second avg: 0.202 I've read that isam opens extra file descriptors and a few other reasons why the number of open tables might be higher than 25, but I am guessing that 512 is not a good thing. Any suggestions on why this might be or what I should be looking into? I've also been using mysqltuner and its been helpful. But it has consistently listed the number of fragmented tables at 207. In phpmyadmin I've selected all the tables and optimized them several times. It hasn't reduced the number of fragmented tables that mysqltuner reports. I think I am missing some important concept about how this all works. Does anyone have any suggestions to point me in the right direction or narrow down google searches or just generally help me be less clueless? Thanks!

  Read the article

• Can MySQL / SQL's short hand of "Using" be used without saying "Inner Join" ?

  - by Jian Lin

  The following 2 statements are to join using gifts.giftID = sentgifts.giftID: mysql> select * from gifts, sentgifts using (giftID); 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 'using (giftID)' at line 1 and the second one: mysql> select * from gifts INNER JOIN sentgifts using (giftID); +--------+------------+----------------+---------------------+--------+------------+--------+------+---------------------+ | giftID | name | filename | effectiveTime | sentID | whenSent | fromID | toID | trytryWhen | +--------+------------+----------------+---------------------+--------+------------+--------+------+---------------------+ | 2 | teddy bear | bear.jpg | 2010-04-24 04:36:03 | 4 | 2010-04-24 | NULL | 111 | 2010-04-24 03:10:42 | | 6 | beer | beer_glass.png | 2010-04-24 05:18:12 | 5 | 2010-03-03 | 11 | 22 | 2010-03-03 00:00:00 | | 6 | beer | beer_glass.png | 2010-04-24 05:18:12 | 6 | 2010-04-24 | 11 | 222 | 2010-04-24 03:54:49 | | 6 | beer | beer_glass.png | 2010-04-24 05:18:12 | 7 | 2010-04-24 | 1 | 2 | 2010-04-24 03:58:45 | +--------+------------+----------------+---------------------+--------+------------+--------+------+---------------------+ 4 rows in set (0.00 sec) Can the first statement also use the "using" shorthand? It seems that when it is used then the word "Inner Join" must be specified... but the first statement is actually an inner join?

  Read the article

• How do I impliment a hidden field in a post form so users can't accidentally double post? (PHP/Mysql

  - by ggfan

  When users submit a form, they will sometimes click refresh or backspace then resubmit which causes multiple entries in mysql. How can i prevent mysql from allowing more than 1 of the same entry? People suggested a hidden field with a value, but how do I use that? <label for="state" class="styled">State:</label> <input type="text" id="state" name="state" value="<?php if (!empty($state)) echo $state; ?>" size="30" /><br /> //a hidden field of a certain value? ________________________________________ <input type="submit" value="Post Ad!" name="submit" /> //php to insert to mysql $query4 = "INSERT INTO posting (state) VALUES ('$state')"; mysqli_query($dbc, $query4);

  Read the article

• Does MySQL have some kind of DoS protection or per-user query limit?

  - by Ghostrider

  I'm a bit at a loss. I'm running a MySQL database that's roughly 1GB data in indices combined on a dedicated Linux server. DB version is '5.0.89-community'. Configuration is controlled via cPanel. PHP actually runs elsewhere on a shared hosting. IP addresses are static and don't change. Access from remote IP address is properly configured. Website gets around 10K hits per day with each hit generating a a database query. Some of these queries are expensive (~1 sec execution time). All is fine and well until at some point DB server starts refusing connections from the client, claiming that specific user can't access the server from that IP. Resetting the server will always fix the problem for a day or two and then the same thing happens. There are some other DBs on that server, some of which are hit pretty hard on occasion but constantnly. One of the apps maintains several persistent connections since it does couple of updates per minute. Though I don't think it's related. What's driving me mad is that I can't figure out why server would start refusing connections. There is nothing in the logs. This server is a hosted dedicated server so hosting company created the OS image and I didn't write or go over every line of configuration. I'd do it but I'm at a loss as to where start looking. Any advice is appreciated.

  Read the article

• How do I create a read only MySQL user for backup purposes with mysqldump?

  - by stickmangumby

  I'm using the automysqlbackup script to dump my mysql databases, but I want to have a read-only user to do this with so that I'm not storing my root database password in a plaintext file. I've created a user like so: grant select, lock tables on *.* to 'username'@'localhost' identified by 'password'; When I run mysqldump (either through automysqlbackup or directly) I get the following warning: mysqldump: Got error: 1044: Access denied for user 'username'@'localhost' to database 'information_schema' when using LOCK TABLES Am I doing it wrong? Do I need additional grants for my readonly user? Or can only root lock the information_schema table? What's going on? Edit: GAH and now it works. I may not have run FLUSH PRIVILEGES previously. As an aside, how often does this occur automatically? Edit: No, it doesn't work. Running mysqldump -u username -p --all-databases > dump.sql manually doesn't generate an error, but doesn't dump information_schema. automysqlbackup does raise an error.

  Read the article

• What are the reasons to use SQL Server instead of MySQL with a complex .Net project?

  - by cdeszaq

  We currently have a 10 year old nasty, spaghetti-code-style SQL Server database that we are soon looking to pretty much re-write from scratch as part of a re-write to a large web application. (The existing application will serve as the functional requirements for the next incarnation of the app) The new version will be developed in .Net, so a large portion of the application stack will be based on Microsoft technologies (Visual Studio will be used IIS will be the application server). One of the developers on the project has raised the possibility of switching to MySQL instead of SQL Server in order to save on cost for both the licence of the DB server, as well as the tools to design and manipulate the DB (such as the wonderfully free MySQL Workbench). What are the various pros and cons of using SQL Server vs. MySQL as the database for a complex .Net project? Price is one factor we have identified, both in terms of the DB server licence as well as tools to manipulate the DB, but what other factors come into play?

  Read the article

• MySQL triggers cannot update rows in same table the trigger is assigned to. Suggested workaround?

  - by Cory House

  MySQL doesn't currently support updating rows in the same table the trigger is assigned to since the call could become recursive. Does anyone have suggestions on a good workaround/alternative? Right now my plan is to call a stored procedure that performs the logic I really wanted in a trigger, but I'd love to hear how others have gotten around this limitation. Edit: A little more background as requested. I have a table that stores product attribute assignments. When a new parent product record is inserted, I'd like the trigger to perform a corresponding insert in the same table for each child record. This denormalization is necessary for performance. MySQL doesn't support this and throws: Can't update table 'mytable' in stored function/trigger because it is already used by statement which invoked this stored function/trigger. A long discussion on the issue on the MySQL forums basically lead to: Use a stored proc, which is what I went with for now. Thanks in advance!

  Read the article

• How to skip all the column names in MySQL when the table has auto increment primary key?

  - by Jian Lin

  A table is: mysql> desc gifts; +---------------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------------+-------------+------+-----+---------+----------------+ | giftID | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(80) | YES | | NULL | | | filename | varchar(80) | YES | | NULL | | | effectiveTime | datetime | YES | | NULL | | +---------------+-------------+------+-----+---------+----------------+ the following is ok: mysql> insert into gifts -> values (10, "heart", "heart_shape.jpg", now()); Query OK, 1 row affected (0.05 sec) but is there a way to not specify the "10"... and just let each one be 11, 12, 13... ? I can do it using mysql> insert into gifts (name, filename, effectiveTime) -> values ("coffee", "coffee123.jpg", now()); Query OK, 1 row affected (0.00 sec) but the column names need to be all specified. Is there a way that they don't have to be specified and the auto increment of primary key still works? thanks.

  Read the article

• Most efficient way to update a MySQL Database on a Linux host with that of an ASP.Net Form on Window

  - by NJTechGuy

  My kind webhost (1and1) royally asked me to go elsewhere to do something like this. I have 2 sites. One of them was developed by a .Net programmer. Now I am contracted to implement a PHP site and fetch data from the .Net site. There is an ASP.Net form that a customer fills and when they hit submit, the data gets stored in SQL Server DB. How do I also store the same data in MySQL parallelly? I cannot directly use some database connectors with ASP.Net since MySQL connectivity is not supported on 1and1 Windows hosting (biz account, no less!). What I thought of is to publish an RSS feed of entries in ASP.Net site and routinely scrape that data into MySQL on Linux host. It is an overkill, I know. Not efficient. I thought I would pick the best brains on SOF to get a different, efficient opinion. Thanks in advance guys...

  Read the article

• How to make a remote connection to a MySQL Database Server?

  - by MLB

  Hello: I am trying to connect to a MySQL database server (remote), but I can't. I am using an user with grant privileges (not root user). The error message is the following: Can't obtain database list from the server. Access denied for user 'myuser'@'mypcname' (using password: YES) "myuser" is an user I created with grant access. This user allows me to connect locally to every database. I am using the same software versions in both hosts: MySQL Server 4.1 (server) and EMS SQL Manager 2005 for MySQL, edition 3.7.0.1 (client). The point is that I need to connect to the remote server using a different user, not root user. So, how to make the connection? Thanks.

  Read the article

• Mysqldump create empty sql file? [php & mysql on Windows]

  - by mysqllearner

  Hi all, I tried to dump a database: <?php $dbhost = "localhost"; $dbuser = "XXXX"; $dbpass = "XXXXXXXX"; $dbname = 'testdb'; $list = shell_exec ("C:\wamp\bin\mysql\mysql5.1.33\bin\mysqldump.exe $dbname --user=$dbuser--password=$dbpass > dumpfile.sql"); ?> I tried both specified full path to mysqldump.exe or just use mysqldump, it still give me a 0kb dumpfile.sql Details: Programming Language: PHP Database: MySql 5.XX OS(server): Windows Server 2003. (currently testing on Windows Vista machine) EDIT @ Jeremy Heslop: I tried: shell_exec("C:\wamp\bin\mysql\mysql5.1.33\bin\mysqldump.exe --opt -h $dbhost -u$dbuser -p$dbpass $dbname > test.sql"); safe_mode = off Still no luck man.

  Read the article

• stored procedures and triggers the same in sql server than mysql?

  - by user314877

  Hello, I have two stored procedures and one trigger for a class I am taking that are done in sql server 2005. I want to recreate them in mysql with phpmyadmin. Everytime i put them in I get a 1064 error. Is this becuase phpmyadmin does not handle stored procedures or triggers well, or is it becuase the syntax from sql server 2005 to mysql is very different? If it is this, then where could i go online to learn the correct syntax to write my stored procedures and triggers in mysql/phpmyadmin... or should I just pick up a book at barnes and nobles? This is my senior project.

  Read the article

• If don't own proprietary database engine, what is best way to convert database to mysql?

  - by John Robertson

  I work for a very small company. I was recently faced with the question of whether there is a good way to convert a proprietary database to a MySQL database without owning the proprietary database engine e.g. if one is given a large oracle database file (or choose your favorite proprietary database engine format), but doesn't have a license for the oracle database engine, is there a good, perfectly reliable way to convert it to a MySQL database format that can be read with the MySQL database engine? My question is very vague as to which proprietary format is the source just because there would be multiple sources and it looks like they would be "various and sundry". My suspicion is that there is no perfectly reliable way, especially for a wide variety of proprietary databases. If there are a few proprietary formats for which this is possible, I would still be interested in knowing, though "various and sundry" is probably the real issue. Minimizing cost, effort and correct conversion are key so I think this is probably is the not possible list. -John

  Read the article

• Can you hide tables from a MySQL user in phpMyAdmin?

  - by AK

  I have a MySQL user added to a database that I would like to prevent from viewing certain tables. I can limit their privileges through MySQL by preventing them from running statements like DROP or ALTER. But is it possible to prevent them from viewing certain tables in phpMyAdmin? If there isn't a MySQL privilege that controls this (I wouldn't imagine there would be), is there a configuration in phpMyAdmin that allows this? I understand one workaround here is to move the tables to a new database that they're not added to. This isn't an option for my application.

  Read the article

• What are good systems for managing PHP/MySQL infrastructure?

  - by sbrattla

  I work in a company which is about to migrate most applications from in-house custom built Java/Tomcat applications to Drupal. Due to company policies, applications and websites need to run on in-house servers. This means that we need infrastructure for Drupal (PHP/MySQL) applications. This must have been solved a million times already. I believe this is what web-hosting companies does every day. Even though we work on a much smaller scale than web-hosting companies, i assume it would make sense to look at the task as if we're going to have an internal small-scale web-hosting company. This means that the guys in IT operations could be "responsible" for "offering" web-hosting, while developers could use these "services". We have three environments; dev(elopment), test and prod(uction). It would make sense that developers could log in to a system and create/edit/delete dev and test sites as they'd like. Production sites should be available through the same system, but only available to IT ops. We need to work with clusters of web servers, meaning that an administration system should be capable of creating/editing/deleting sites across multiple servers. I know there's no "this is it" answer to my question; but what would be a good place to start to get going with this? Apart from the actual hardware, what would be a good administration system for this?

  Read the article

• In SQL / MySQL, what is the difference between "On" and "Where" in a join statement?

  - by Jian Lin

  The following statements give the same result (one is using "on", and the other using "where"): mysql> select * from gifts INNER JOIN sentGifts on gifts.giftID = sentGifts.giftID; mysql> select * from gifts INNER JOIN sentGifts where gifts.giftID = sentGifts.giftID; I can only see in a case of a Left Outer Join finding the "unmatched" cases: (to find out the gifts that were never sent by anybody) mysql> select name from gifts LEFT OUTER JOIN sentgifts on gifts.giftID = sentgifts.giftID where sentgifts.giftID IS NULL; In this case, it is first using "on", and then "where". Does the "on" first do the matching, and then "where" does the "secondary" filtering? Or is there a more general rule of using "on" versus "where"? Thanks.

  Read the article

• What is best configuration settings for Wordpress and MySQL on Win2008 + IIS7 stack?

  - by holiveira

  I currently have four blogs that uses Wordpress running on a shared hosting company. This blogs have a considerable amount of visits and I'm constantly receiving warnings from the hosting company saying that I'm consuming too much server CPU. Considering the fact that I have a dedicated server in another company with plenty of idle resources (it has a quad core Xeon 2.5GHz and 8GB of Ram and run on Win2008) I'm planning to move the blogs to this server in order to have some more freedom. I'm currently using this server to host some web applications using ASP.Net and SQL Express. I've installed a blog to test and it worked fine, but some issues appeared and raised some questions in my mind: How to properly set the permissions in the folders used by wordpress plugins, I mean, what permissions should I set for the IIS_User in some folders so that the plugins works correctly? What's the best caching plugin to use considering this is a Window Server? In the previous hosting company I used the WPSuperCache, but it was a Linux Stack. Or should I ignore the caching plugins and use the Dynamic Caching Feature of IIS7? How can I optmize the MySQL server running in this server (specially the settings regarding memory and caching) How can I protect the admin folders against hacker attacks? I know some people will advice me not to run Wordpress in a Windows stack, but that's my only choice. I don't even know were to start managing and LAMP stack, don't have the time to do so nor the money to rent another server.

  Read the article

• MySQL on Windows-7 (64-bit) on 0.0.0.0:3306 rather than 127.0.0.1:3306

  - by Mark Baker

  I've just installed the latest production release of MySQL (64-bit) on my Windows 7 box. It was a straight vanilla install, using all defaults; but phpmyadmin can't see it at all. MySQL is configured as a service to start automatically, and I know it's running because the MySQL GUI tools work correctly. Doing a netstat -a, I see TCP 0.0.0.0:3306 Marks-Netbook:0 LISTENING when I'd expect to see TCP 127.0.0.1:3306 Marks-Netbook:0 LISTENING I don't know if this is the reason phpmyadmin can't connect, but suspect that it is probably the case. Can anybody confirm whether this is the likely cause, and/or suggest how I can reso;lve this?

  Read the article

• Why is MySQL unable to open hosts.allow/hosts.deny?

  - by HonoredMule

  I have a storage server running Nexenta (OpenSolaris kernel, Ubuntu userspace) with MySQL on top of a ZFS storage array, using innodb_file_per_table and ulimit -n set to 8K. mysqltuner.pl confirms the file limit and claims there are 169 files. The following command: pfiles `fuser -c / 2>/dev/null indicates one mysqld process having 485 file/device descriptors (and they're almost all for files) so I don't know how reliable the tuning script is, but it is still way less than 8K and this list also finds no other process which is close to it's limit. The global total number of descriptors in use is around 1K. So what can cause mysqld to be constantly streaming the following errors? [date] [host] mysqld[pid]: warning: cannot open /etc/hosts.allow: Too many open files [date] [host] mysqld[pid]: warning: cannot open /etc/hosts.deny: Too many open files Everything appears to actually be operating fine, but the issue is constantly flooding the admin console and starts right away on a fresh boot (not only reproducible, but always from mysqld and always the hosts files, whose permissions are the default -rw-r--r-- 1 root root). I could, of course, suppress it from the admin console but I'd rather get to the bottom of it and still allow mysqld warnings/errors to reach the admin console. EDIT: not only is the actual file descriptor well within sane limits, the issue also persists (with immediate appearance) even with the file limit raised to 65535 and always only on hosts.allow/deny.

  Read the article

• MySQL command-line tool: How to find out number of rows affected by a DELETE?

  - by ambivalence

  I'm trying to run a script that deletes a bunch of rows in a MySQL (innodb) table in batches, by executing the following in a loop: mysql --user=MyUser --password=MyPassword MyDatabase < SQL_FILE where SQL_FILE contains a DELETE FROM ... LIMIT X command. I need to keep running this loop until there's no more matching rows. But unlike running in the mysql shell, the above command does not return the number of rows affected. I've tried -v and -t but neither works. How can I find out how many rows the batch script affected? Thanks!

  Read the article

• What is the precedence of characters when sorting in MySQL, PHP, or just in general?

  - by FireCoding

  Question: Where can I find the precedence of characters when sorting in MySQL, PHP, or just in general on Linux and Windows OS? For example, everybody knows that a comes before b when performing an ascending sort on a string in MySQL. But what about other characters? Does the dollar-sign $ come before asterisk * ? Does a space come before an exclamation-mark? etc... What dictates the sort order? Does it use underlying ascii / UTF-8 values? Is it different for different technologies? Technologies to consider: Databases - MySQL / SQL / SQLite / Oracle / etc Programming languages (for string-sorting functions) - PHP / Javascript / ASP.NET / Visual C# / Python / Ruby / Objective C OS (i.e., sorting files by filename) - Windows / Linux / MacOS / iOS / Android

  Read the article

• How do I convert german dates to MySQL standard dates?

  - by Kebman

  I'm importing a CSV file with dotted german dates into a MySQL database. I want the dates in the CSV to automatically be formatted correctly to the correct data type fields used by MySQL. I'm using Sequel Pro for the import. I gather I'm supposed to use the STR_TO_DATE function, but I just can't wrap my head around how to use add value or expression. German date Here are the dates in the CSV file: DD.MM.YYYY e.g.: 28.01.1978 MySQL date Here is what I want to end up with in the database: YYYY-MM-DD e.g.: 1978-01-28 Any ideas?

  Read the article

• Mysql: Working With 192 Trillion Records... (Yes, 192 Trillion)

  - by Sarah

  Here's the question... Considering 192 trillion records, what should my considerations be? My main concern is speed. Here's the table... CREATE TABLE `ref` ( `id` INTEGER(13) AUTO_INCREMENT DEFAULT NOT NULL, `rel_id` INTEGER(13) NOT NULL, `p1` INTEGER(13) NOT NULL, `p2` INTEGER(13) DEFAULT NULL, `p3` INTEGER(13) DEFAULT NULL, `s` INTEGER(13) NOT NULL, `p4` INTEGER(13) DEFAULT NULL, `p5` INTEGER(13) DEFAULT NULL, `p6` INTEGER(13) DEFAULT NULL, PRIMARY KEY (`id`), KEY (`s`), KEY (`rel_id`), KEY (`p3`), KEY (`p4`) ); Here's the queries... SELECT id, s FROM ref WHERE red_id="$rel_id" AND p3="$p3" AND p4="$p4" SELECT rel_id, p1, p2, p3, p4, p5, p6 FROM ref WHERE id="$id" INSERT INTO rel (rel_id, p1, p2, p3, s, p4, p5, p6) VALUES ("$rel_id", "$p1", "$p2", "$p3", "$s", "$p4", "$p5", "$p6") Here's some notes... The SELECT's will be done much more frequently than the INSERT. However, occasionally I want to add a few hundred records at a time. Load-wise, there will be nothing for hours then maybe a few thousand queries all at once. Don't think I can normalize any more (need the p values in a combination) The database as a whole is very relational. This will be the largest table by far (next largest is about 900k) UPDATE (08/11/2010) Interestingly, I've been given a second option... Instead of 192 trillion I could store 2.6*10^16 (15 zeros, meaning 26 Quadrillion)... But in this second option I would only need to store one bigint(18) as the index in a table. That's it - just the one column. So I would just be checking for the existence of a value. Occasionally adding records, never deleting them. So that makes me think there must be a better solution then mysql for simply storing numbers... Given this second option, should I take it or stick with the first... [edit] Just got news of some testing that's been done - 100 million rows with this setup returns the query in 0.0004 seconds [/edit]

  Read the article

• How to restore a slave from a mysql backup?

  - by robsf

  I'm running MySql 5.1. I have Master and a Slave on 2 machines and I set up replication. I do periodic backup on my slave server. I stop mysql, I copy all the files and I restart mysql. In case I lose the Master, I can set up a new one from the last backup. What If I lose the Slave? Can I restart the slave from the last backup? Am I supposed to keep track of the position of the replication every time I to a backup?

  Read the article

• How can I determine if a specified string is in a specific MySQL column? (and also perhaps a tutoria

  - by Rob

  This is a fairly simple question. Basically, I'm having a program send HardWare ID's to my PHP script as GET data. I need the PHP script check to make sure that HardWare ID is in a specific MySQL column, and if it is, { continue the script, } else { exit(); } Problem is I'm not too good with MySQL and have no idea how to do this. However, I feel that I should know this by now, so if someone could also link me to a good tutorial site for MySQL, that kind of keeps it "humanized" if you know what I mean. One that "dumbs it down." I'm not dumb or anything, I just get sidetracked easily, and if all its doing is showing me code and not explaining it, I won't pick it up. If you don't have any tutorial sites off the top of your head, I'll settle for help with the first question, and try to hunt down a tutorial later.

  Read the article

< Previous Page | 250 251 252 253 254 255 256 257 258 259 260 261  | Next Page >