mysql

WordPress: Google AdSense Plugin 3

 in WordPress  tagged AdSense / Google Ads / Google AdSense Plugin / mysql / sql by

Recently, we were trying to changing the position of the Google Ads that are shown on a website using the Google AdSense plugin for WordPress.

After clicking on the Manage Ads button at the configuration page (http://example.com/wp-admin/options-general.php?page=GooglePublisherPlugin) we would get an error that another theme had set the active ads and that the plugin cannot proceed to modify them unless they are disabled from the other theme (Specifically we would get the following error: Due to a theme change, ads are not being shown. Please reconfigure your ads or restore the previous theme.).

(a) Disabling the plugin, (b) Uninstalling the plugin (c) Reconnecting with another AdSense account did not help at all.

What we did at the end was to remove the GooglePublisherPlugin entry from the wp_option database of our installation.

DELETE FROM `MyDatabase`.`wp_options` WHERE `option_name` = "GooglePublisherPlugin";

Next time we visited the configuration page, we were disconnected from the previous account and we had to reconnect.

GetStarted-AdSense
After reconnecting, we followed the configuration procedure, we pressed the Manage Ads button, the website was analyzed, we got the confirmation message and we clicked on the Save & Activate button to store the changes.

For a few hours, no ads were shown on the site. We did not take any action and on the next day that ads started appearing as expected.

Copy all databases to another host

 in Bash / GNU/Linux  tagged backup / export / import / mysql / mysqldump by

The following command will use mysqldump to create a dump of all available databases in the OLD_HOST that are available for the user OLD_USER.
The results will be imported to another server via the mysql pipe.

OLD_USER="myUser"; OLD_PASS="myPASS"; OLD_HOST="myHost";
NEW_USER="myUserNEW"; NEW_PASS="myPASSNEW"; NEW_HOST="myHostNEW";
mysqldump -u "$OLD_USER" -p"$OLD_PASS" -h "$OLD_HOST" --all-databases | mysql -h "$NEW_HOST" -u "$NEW_USER" -p"$NEW_PASS";

The user must have the LOCK TABLES privilege for the above command to work or else you will get the following error.

mysqldump: Got error: 1044: "Access denied for user 'OLD_USER'@'OLD_HOST' to database 'DBNAME'" when using LOCK TABLES

In case you cannot give the privilege to the user, then use the parameter --single-transaction to mitigate the problem. The command changes as follows.

OLD_USER="myUser"; OLD_PASS="myPASS"; OLD_HOST="myHost";
NEW_USER="myUserNEW"; NEW_PASS="myPASSNEW"; NEW_HOST="myHostNEW";
mysqldump -u "$OLD_USER" -p"$OLD_PASS" -h "$OLD_HOST" --single-transaction --all-databases | mysql -h "$NEW_HOST" -u "$NEW_USER" -p"$NEW_PASS";

In case you want to copy only specific databases, use the following

OLD_USER="myUser"; OLD_PASS="myPASS"; OLD_HOST="myHost"; OLD_DBS=("DB1" "DB2");
NEW_USER="myUserNEW"; NEW_PASS="myPASSNEW"; NEW_HOST="myHostNEW";
mysqldump -u "$OLD_USER" -p"$OLD_PASS" -h "$OLD_HOST" "${OLD_DBS[@]}" | mysql -h "$NEW_HOST" -u "$NEW_USER" -p"$NEW_PASS";

In case you want to copy only specific tables from a database, use the following

OLD_USER="myUser"; OLD_PASS="myPASS"; OLD_HOST="myHost"; OLD_DB="DB1"; OLD_TABLES=("TBL1" "TBL2");
NEW_USER="myUserNEW"; NEW_PASS="myPASSNEW"; NEW_HOST="myHostNEW"; NEW_DB="NewDB";
mysqldump -u "$OLD_USER" -p"$OLD_PASS" -h "$OLD_HOST" "$OLD_DB" "${OLD_TABLES[@]}" | mysql -h "$NEW_HOST" -u "$NEW_USER" -p"$NEW_PASS" "$NEW_DB";

Delete a paste from a private hosted Stikked installation

 in Stikked  tagged mysql / stikked by

To delete a paste from a Stikked installation that you have database access do the following:

Visit the post you like to delete: e.g http://example.com/stikked/htdocs/view/267c4d64
From this URI we need to get the part after view which is the pid (paste ID) of the paste you want to delete. In this case we get 267c4d64.

Then, you need to login to your MySQL DB. In case you are connecting via a terminal you would be using a similar command to this:

mysql -D MY_DB -h MY_HOST -u MY_USER -p

Replace MY_DB, MY_HOST and MY_USER with your credentials, afterwards you will be prompted for you password (if any).

Once in the MySQL console, issue the following:

DELETE FROM `pastes` WHERE `pid` = "267c4d64";

The above line will find the paste we want to delete and perform the deletion.

If you want to see the structure of the pastes table issue the following: SHOW COLUMNS FROM `pastes`;

Type exit to exit the MySQL console.

mysqldump: Got error: 1044: Access denied for user ‘username’@’ip’ to database ‘db’ when doing LOCK TABLES

 in Programming / SQL  tagged mysql / mysqldump by

When trying to issue the command

mysqldump -h "mysql.example.com" -u "username" -p"password" db table_A table_B

to get the MySQL dump for two tables (table_A and table_B), you will get the error

mysqldump: Got error: 1044: Access denied for user 'username'@'ip' to database 'db' when doing LOCK TABLES

when your account does not have the ‘LOCK TABLES’ right.

The ‘LOCK TABLES’ right gives you access to the command LOCK that can be used to emulate transactions or to get more speed when updating tables by preventing other sessions from modifying the locked tables.

To mitigate the problem, use the --single-transaction option as follows

mysqldump -h "mysql.example.com" -u "username" -p"password" --single-transaction db table_A table_B

 

Documentation:

·   --single-transaction

This option sets the transaction isolation mode to REPEATABLE READ and sends a START TRANSACTION SQL statement to the server before dumping data. It is useful only with transactional tables such as InnoDB, because then it dumps the consistent state of the database at the time when START TRANSACTION was issued without blocking any applications.

When using this option, you should keep in mind that only InnoDB tables are dumped in a consistent state. For example, any MyISAM or MEMORY tables dumped while using this option may still change state.

While a --single-transaction dump is in process, to ensure a valid dump file (correct table contents and binary log coordinates), no other connection should use the following statements: ALTER TABLE, CREATE TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE. A consistent read is not isolated from those statements, so use of them on a table to be dumped can cause the SELECT that is performed by mysqldump to retrieve the table contents to obtain incorrect contents or fail.

The --single-transaction option and the --lock-tables option are mutually exclusive because LOCK TABLES causes any pending transactions to be committed implicitly.

This option is not supported for MySQL Cluster tables; the results cannot be guaranteed to be consistent due to the fact that the NDBCLUSTER storage engine supports only the READ_COMMITTED transaction isolation level. You should always use NDB backup and restore instead.

To dump large tables, combine the --single-transaction option with the --quick option.