People who code: we want your input. Take the Survey
23

phpmyadmin Perhaps you could just select the phpMyAdmin Operations tab: In phpMyAdmin, click on the table you want to reset or change the AUTO_INCREMENT value Click on the Operations Tab In the Table Options box find the auto_increment field. Enter the new auto_increment starting value Click on the Go button for the Table Options box. Since this one of the ...


20

View definitions are stored in the information schema: SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'yourviewname'


14

As a supplement to the other answer(s), I'd prefer a more visual approach: Click the table you want to change. Click Operations: Look under "Table options": Also note that:  –§–  You cannot reset the counter to a value less than or equal to the value that is currently in use. For both InnoDB and MyISAM, if the value is less ...


13

(Regarding PostgreSQL 9.3 and MySQL 5.6, written in 2014; if you're looking at other versions, this may be outdated): Lots more features. CHECK constraints True SERIALIZABLE isolation Arrays (including index support for arrays) Window functions (lead, lag, row_number, etc) Common table expressions (WITH queries) including recursive CTEs and writeable CTEs ...


13

Turns out it was phpMyAdmin - needed to show full texts. Just above the SQL output there is an 'Options' link. Click that and you will see Partial texts/Full texts radio buttons. Note, that when I went back to get the screenshot it was still set to Full Texts so I assume there is a cookie being set which may explain why it was different in one database to ...


8

I had the same error, trying to debug locally some application that connects to remote DBs. When using php < 5.3, everything worked. When using php 5.3 or greater, error shows up. After hours spent reading and tweaking, i realized this only occurred when connecting as a certain user. All databases i use have new password length (41). I changed the ...


8

This is quite an easy one really (one of those "Doh" moments :-) ). You set it in my.cnf as described here: [mysqld] .. .. other_stuff .. event_scheduler=on .. .. more_stuff .. (or my.ini if you're running Windows as pointed out below) From the MySQL documentation here, you have: When the Event Scheduler is ON, the event scheduler thread is listed ...


7

You have several choices. You can either create the tables without the constraints & add them afterwards, or create the tables with the foreign keys & them import the data with foreign key checks disabled - simply run SET FOREIGN_KEY_CHECKS=0; in your mysql session to temporarily disable them. For example: mysql> insert into favourite values( 1,...


5

Any idea why this query doesn't work with table B? Do you have to insert/update every table column when running the query? Yes, UserName is a NOT NULL column and doesn't have a DEFAULT value. Therefore you need to provide a value for every row INSERT. Not for UPDATE though, the column can be updated or not, depending on your requirements. Is it a PHP ...


5

TL;DR - eventually figured out how to do this in a single pass - that answer's at the end. I left the original answer in place in order for people here to maybe learn from my own (creaking ;-) ) thought processes! Update: there's an alternative answer provided below. This proved to be quite tricky. To solve this, I did the following (see fiddle here) I ...


5

To pick MySQL or MariaDB, right click on the wampmanager icon in the system tray and you should see this this menu Please Select Components like I did please check the 2nd Screenshot


4

Yes, if you use phpMyAdmin on your WAMP server, you can use the Export tab to dump your database and all the data in it. On the Export tab, choose the Custom export method. This opens a bunch of options. Under Save Output to a File, choose Compression and you get to choose zipped from a drop-down. Then you can Import that zip to another MySQL instance on ...


4

WAMPServer is primarily designed for use on a single machine i.e. a developer writing and testing code locally on that PC. So to protect the beginner from his/her self the Apache & MySQL configuration by default is setup so no access can be made to the Apache or MySQL server form anywhere but the PC running Apache and MySQL. When changing this ...


4

Since you specifically asked about phpMyAdmin and the previous answers, while correct, are both about doing this on the command line: Navigate to the database you wish to export. Across the top row of tabs click "Export" Next, click the Custom radio button Halfway down the "Format-specific options" area, select "structure" (the default is "structure and ...


4

Data is present in data folder of MySQL which is usually in ProgramData for windows installations. look into "C:\ProgramData\MySQL\MySQL Server 5.6"..as programdata usually have the files on by default


4

You can copy files from one server to the next to move your database. To do this, you must make sure that both instances of your DB are stopped. I also recommend using rsync to copy your database files. In addition to making sure that both database servers are stopped before moving the files, you will most likely need to change the ownership of your files. ...


4

Changing the MySQL settings worked. This is what I changed, exactly: In php.ini Find: post_max_size = 8M upload_max_filesize = 2M max_execution_time = 30 max_input_time = 60 memory_limit = 8M Change to: post_max_size = 750M upload_max_filesize = 750M max_execution_time = 5000 max_input_time = 5000 memory_limit = 1000M In my.ini (MySQL File) Under ...


4

It has to do with the max_allowed_packet on the MySQL server. It was set to 16M and when I upped it to 64M in my.cnf located in /private/etc/ (on Mac El Capitan) which was a copy of the my-huge.cnf. After stopping the MySQL server and Apache and restarting them made my site work locally again and the error messages went away.


4

Without seeing any additional information, here is my best guess why MySQL is trying to shutdown for a long time: I suspect mysqld no longer has a socket file. About 1.5 years ago, I answered mysql restart issue after move database. I learned that mysqld depends on the presence of a socket file. If there is no socket, mysqld just draws lots of dots on the ...


4

Question 1 : What is the difference between these 2 queries, if it loads the same data. None. Question 2 : Why there is these much time difference, if it loads the same data. Within margin of error and your first query may have put them in a cache making it slightly faster. Question 3 : Why phpmyadmin by default loads SELECT * FROM table WHERE 1 ...


4

The Apache logs show what the Apache Web Server has been asked to do. It generally knows (nor cares) not a jot about SQL. Of any flavour. Please, please, please tell me you're not submitting raw SQL statements to your web application through the URL and/or QueryString and executing them! Obligatory XKCD Reference: Little Bobby Tables. This is a huge ...


3

Since the tables you want to check are MyISAM, you can use just one query. For any given table or tables using MyISAM, here is that query: SET @SecondsAgo = 60; SELECT table_schema,table_name, data_length MYDSize,index_length MYISize, table_rows,update_time FROM information_schema.tables WHERE engine='MyISAM' AND table_schema NOT IN ('...


3

I get this effect only when applying a query. A table, view, or other direct structure seems to export into SQL fine. The SQL option seems is not available to a query result set, very sadly.


3

OK, I finally found a solution. To give proper credit, I had to blend answers provided by two people: RiggsFolly's answer to this question on resetting the root password in wamp, which is what I'm using, and jm666's answer for this question on restoring a deleted root user in MySQL using MAMP on OS X. For Wamp Users: Click on the Wamp system tray icon to ...


3

It was a bug with phpmyadmin. When you create a new constraint you usually are allowed to leave its name field blank, and it fills it for you. This time, for some still unknown reason, this didn't work and gave this unrelated error. Filling the name field did the trick, and allowed me to create the constraint.


3

I got the same issue and happend to see this thread. I have a feeling that the newest version of phpmyadmin doesn't have this bug. The name for foreign key must be unique. You can create the same constraint with a different name, but you can't create a different constraint with same name. Not even in a different table. But the error statement remains same: ...


3

All you're missing is an index on postcode. In your query you're doing this: where('postcode', '=', $pcode It almost certainly translates at the database layer to select a, b, c from table where postcode = x. Without the index on postcode you're doing a slow scan of the entire table, with an index you can do a fast seek.


3

You can use the documented feature SHOW CREATE VIEW view_name which is documented on the official dev.MySQL.com website: Reference: 13.7.5.14 SHOW CREATE VIEW Syntax (Version 5.5) 14.7.5.13 SHOW CREATE VIEW Syntax (Version 5.7) mysql> SHOW CREATE VIEW v\G *************************** 1. row *************************** View: v ...


3

This is a somewhat common logical error. ('$checkin' BETWEEN start_date AND end_date) OR ('$checkout' BETWEEN start_date AND end_date)); The problem is not the way the query is written, it's that you're using the wrong criteria. A reservation can conflict while neither of these is true, simply by starting before and ending after the desired values. What ...


3

It seems that the id column is varchar(255) so from your config file enable the innodb_large_prefix on this instance of MySQL to resolve the problem but per the below, this explains why this is occurring from my interpretation. You can run show variables and see if the innodb_large_prefix if ON or OFF. Maximums and Minimums By default, the index key ...


Only top voted, non community-wiki answers of a minimum length are eligible