Tutes Club

Category: MySQL

  • How to change WordPress user roles in MySQL database using PhpMyAdmin?

    We will be editing the database rows using PhpMyAdmin tool given with cPanel by many hosting companies You can change the WordPress user roles by following the given steps: Step 1: Open phpMyAdmin on cPanel. Step 2: Open the wp_usermeta or xxx_usermeta table in the database as highlighted. (wp_ is just prefix, your table prefix…

  • MySQL error 1449: The user specified as a definer does not exist

    Why this error happens? Most of the times, reason is that your database dump through command line or PhpMyAdmin or even other libraries can have SQL’s Definer statements. Now, as per MySQL’s Official Documentation: The DEFINER clause specifies the MySQL account to be used when checking access privileges at routine execution time for routines that have the SQL…

  • Magento 2 – How to manage multi domain website store setup URLs

  • How to tweak Magento 2 for two letter search in fulltext mode

    The problem: Magento 2 wont return any search results if the search token is 2 letters in length. Solution: Alter MySQL conf file. Edit my.cnf file on your server In CentOS, use command and add the following lines: Save using CTRL+x and then restart mysqld service. Now we need to regenerate the index in order…

  • Magento 2 : How to fix tablespace for table exists | base table or view already exists

    Fixing General error: 1813 Tablespace for table xx Please DISCARD the tablespace before IMPORT and SQLSTATE[42S01]: Base table or view already exists Recently I encountered this error while upgrading an extension on Magento 2.2.2 The error was When I tried to fix the above error, I received: So here is how to fix it: Important…

  • How to find rows which exist in new table but not in old table – MySQL

    SQL Query: SELECT t1.stone FROM newatt t1 LEFT JOIN att t2 ON t2.stone = t1.stone WHERE t2.stone IS NULL newatt = new table (contains updated extra rows) att = old table stone = column name on both tables

  • Insert Indian States in Magento Database

    Connect to your server’s database using mysql cli or phpmyadmin and use the following query INSERT INTO `directory_country_region` VALUES (NULL,”IN”,”AP”,”Andhra Pradesh”), (NULL,”IN”,”AR”,”Arunachal Pradesh”), (NULL,”IN”,”AS”,”Assam”), (NULL,”IN”,”BR”,”Bihar”), (NULL,”IN”,”CG”,”Chhattisgarh”), (NULL,”IN”,”GA”,”Goa”), (NULL,”IN”,”GJ”,”Gujarat”), (NULL,”IN”,”HR”,”Haryana”), (NULL,”IN”,”HP”,”Himachal Pradesh”), (NULL,”IN”,”JK”,”Jammu and Kashmir”), (NULL,”IN”,”JH”,”Jharkhand”), (NULL,”IN”,”KA”,”Karnataka”), (NULL,”IN”,”KL”,”Kerala”), (NULL,”IN”,”MP”,”Madhya Pradesh”), (NULL,”IN”,”MH”,”Maharashtra”), (NULL,”IN”,”MN”,”Manipur”), (NULL,”IN”,”ML”,”Meghalaya”), (NULL,”IN”,”MZ”,”Mizoram”), (NULL,”IN”,”NL”,”Nagaland”), (NULL,”IN”,”OD”,”Odisha”), (NULL,”IN”,”PB”,”Punjab”), (NULL,”IN”,”RJ”,”Rajasthan”), (NULL,”IN”,”SK”,”Sikkim”), (NULL,”IN”,”TN”,”Tamil Nadu”), (NULL,”IN”,”TL”,”Telangana”), (NULL,”IN”,”TR”,”Tripura”), (NULL,”IN”,”UK”,”Uttarakhand”), (NULL,”IN”,”UP”,”Uttar Pradesh”),…

  • Inserting into SQL without specifying primary column

    If Primary key is set to Auto-increment, there are chances that you would not want to provide its value in insert query.   Here is how to do it in MySQL: INSERT INTO table_name VALUES (NULL, ‘column b’, ‘col c’); Assumption^: The first column is the primary key set to AI Similarly you can skip…