22

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 ...


17

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


15

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 ...


12

(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 ...


12

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

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,...


6

No, you can't. Not out of the box. Possible workarounds: Triggers (an AFTER INSERT trigger). Disadvantages: Plain horror. Comes with all the other disadvantages of triggers, like maintenance and debugging nightmares. Advantages: You can have FOREIGN KEY constraints that reference this column. You can update the first and the second id columns ...


6

The passwords you're talking about are passwords for MySQL users. You don't really set a password for a database or table. You can effectively implement different passwords for different DBs by creating multiple users with different passwords and only granting access to specific databases for each user. When people say that you should set a password for ...


6

If you are doing a range of consecutive dates, there is no need to use the IN operator. If you use this to compute midnight seven days ago DATE(NOW()) - INTERVAL 7 DAY + INTERVAL 0 SECOND here is what you get back: mysql> SELECT DATE(NOW()) - INTERVAL 7 DAY + INTERVAL 0 SECOND; +--------------------------------------------------+ | DATE(NOW()) - ...


5

The automated way of doing so is by defining foreign keys on your tables, with a DELETE CASCADE in your case. This way, a DELETE on the master table will propagate and delete matching rows from all derived tables. You will have to use InnoDB tables for that. I assume your tables do not have foreign keys at the moment, otherwise you wouldn't ask. In which ...


5

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 ...


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

I guess you want to either insert a new row with the counter status_likes = 1 or incresase it by 1 when a row with status_id already exists. If there is a UNIQUE constraint on member_status_like_count (status_id), you can use INSERT ... ON DUPLICATE KEY UPDATE: CREATE TRIGGER member_status_likes__AFTER_INSERT__UPSERT__like_count AFTER INSERT ON ...


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

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 ...


3

The difference is going to be in how much data you are allowed to store there. The memory and disk space will depend on how much is actually stored, not what limits you put in place. Most db's (MySQL included) store varchar fields with a length identifier and then a variable length data string. This means that the size of the field on disk goes up more or ...


3

I have good news and bad news GOOD NEWS You can multiple auto_increment values BAD NEWS Two things: It is only available for the MyISAM Storage Engine Only one numeric auto_increment value is allowed. Each auto_increment must be have associated column to define uniqueness from other auto_increment values within the same table. I have discussed this before ...


3

Since you did this changes to the MySQL side, your only other option is to downgrade PHP. Other links support this: StackOverflow : MySQL PHP incompatibility ServerFault : mysql_connect(): The server requested authentication method unknown to the client [mysql_old_password] in Another Blog : http://datatables.net/forums/discussion/12188/notice-array-to-...


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

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 ...


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