texts:
I want to make my MySql slave read only, i've been looking and found the read_only option, but its says that users with super privalges can still write (if i understand the text correctly), here is the grants for my app:

GRANT RELOAD, PROCESS ON *.* TO 'my_app'@'%' IDENTIFIED BY PASSWORD '*'
GRANT ALL PRIVILEGES ON `my_app`.* TO 'my_app'@'%'

will the app be able to write to the slave?


texts: One MySQL Table keeps losing auto-increment after restart

texts:
We are trying to figure out why this one table keeps losing auto-increment value, thus giving an error message: Failed to read auto-increment value from storage engine.

The auto ID column has type BIGINT(20), and table has about 140K records.
There are other tables that also use auto increment IDs and have more records (1M to 5M records).

It started a few month ago, that every time the service restarts, the same table loses the auto-increment, and we had to manually reset.

The MySQL server is of version 5.7, and is running on windows2012.

Where should we look into to fix it?  Or should we rebuild the table?

Thanks

Here is the create table;

CREATE TABLE `notification` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `subject` varchar(400) NOT NULL,
  `category` varchar(200) DEFAULT NULL,
  `created_by` bigint(20) DEFAULT NULL,
  `created_date` datetime NOT NULL,
  `priority` int(11) NOT NULL DEFAULT '2',
  `mime_type` varchar(45) DEFAULT NULL,
  `attachment` varchar(500) DEFAULT NULL,
  PRIMARY KEY (`id`)
  FULLTEXT KEY `ix_notification_subject` (`subject`)
) ENGINE=InnoDB AUTO_INCREMENT=141570 DEFAULT CHARSET=utf8;


texts:
Okay my project in simple words. Multiple shop sites are hosted on different hosting services receiving orders from customers. All the orders are to be directed to the main company website where the employees will receive the orders, complete and update the system. So its a two way traffic of information between shop sites and the company website.

Its basically a network of content writing websites where orders will be received and sent to the main writers lounge being hosted on the company website. Initially the plan was to build 1 centralized database on the company website and remotely connect all the website to it BUT there are many factors causing the recent change in plans such as security, vulnerability, sustainability of each website and speed issues since all the websites will be connecting remotely to the database being hosted on the company website. There are almost 20 tables in the database, give or take. And the client does not want to invest in the dedicated hosting or anything, hence it has to be different websites on different hosting plans.

I would appreciate any of your professional thoughts on this. Diversify your answer by assuming two situations: 1) All the websites are being hosted with different hosting service providers and read:writer ratio is 70:30. 2) All the websites are being hosted with the same hosting service provider but different hosting accounts and not a dedicated VPS thing. This might help in overcoming the delay issues in remote database queries.

Solutions that we could think of : * Host 1 centralized database being hosted on the company website and connect all the sites remotely to it. * Host a personal database of each website on its hosting. With a separate database for the Writers being hosted on the main company website and copy the data daily from multiple databases to the writers database. Although we don't think this method is really worth it because it will anyways delay the process and hinder the smoothness of the system.


texts: How can I find all rows that have at least one element in an array matching a pattern?

texts:
Consider the following example:

CREATE TEMPORARY TABLE testing (
    names TEXT[]
);

INSERT INTO testing VALUES
    ('{toplevel1}'),
    ('{toplevel1.sub1, toplevel2.sub2}'),
    ('{toplevel1.sub1.sub2, toplevel2.sub2}'),
    ('{toplevel1.sub1.sub3, toplevel2.sub2}'),
    ('{toplevel1.sub1.sub3.sub3, toplevel2.sub2}'),
    ('{toplevel2.sub1}')
;

SELECT * FROM testing WHERE 'toplevel1.%' ILIKE ANY(names);

I want to write a select where "any" element of an array matches a pattern. But I can't figure out a way to do it. In the example I've given, using the ILIKE operator can't work because it checks if the RHS matches the LHS. For this case I would need a similar operator but which checks if the LHS matches the RHS.

update: I'm currently fiddling around with something like this without much success:

SELECT * FROM testing WHERE names ILIKE ANY (ARRAY(SELECT unnest(names) || '%'));


texts: Return count from not direct related tables

texts:
To clarify what I'm asking:
My main goal was how I can retrieve all Manufacturers with a count of entries next to them.

Working with 3 tables {Manufacturers (wp_manufacturers), Dealers(wp_users), Entries(wp_posts)} [plus a fourth, wp_usermeta, tied to wp_users].

Manufacturers hold brands like Suzuki Honda etc
Dealers as the users publishing an Entry that showcase a Car of a manufacturer.
Entries is the table that holds an entry with the content and the user who published.

The table Dealers has a field that connects every user with the Manufacturer (actually, currently set up through the fourth table).

What I trying to do is to have for every Manufacturer a count of entries that exist in the Entries table.

A dealer can be only with one manufacturer. Also, a manufacturer can have multiple dealers.

This is original for a WordPress site that is heavily customized and we would like a direct approach to the database.

Given this data, with 2 Manufacturers and 3 dealers:

  Manufacturers           wp_posts
+-----------------+    +-----------------------------------------------+
|id      |name    |    |ID     |post_author    |post_type |post_status |
+-----------------+    +-----------------------------------------------+
|   1    |suzuki  |    | 455   |    13         |  car     | publish    |
|   2    |honda   |    | 689   |    13         |  car     | publish    |
|        |        |    | 1689  |    2          |  car     | publish    |
|        |        |    | 1889  |    13         |  car     | draft      |
|        |        |    | 2090  |    44         |  car     | publish    |
+---^----+--------+    +------------^----------+----------+------------+
    |                           ____|
    |           wp_usermeta    |
    |          +---------+-----v-------------+------------------+
    |          |umeta_id | user_id |meta_key | meta_value       |
    |          +------------------------------------------------+
    |          | 498     |   13     | _manuf  |     1           |
    |          | 709     |   2      | _manuf  |     2           |
    |          | 1029    |   44     | _manuf  |     2           |
    |          |         |          |         |                 |
    |          +------------------------------------------------+
   ++----------------------------------------------^

The results I'd like to get are:

+-------+----------+--------------+
|  id   |   name   |   count      |
+---------------------------------+
|  1    | suzuki   |     3        |
+---------------------------------+
|  3    | honda    |     2        |
+-------+----------+--------------+

I also want to check the post_status to only count entries that have been published.

Today I read again my question and I found indeed not clear. The reason I didn't like to write from the start the table original names was not to be confused with the WordPress solution as I would like only the MySQL approach.

Example on the wp site where I use the tables of wp this returns the data but not with 0 when not an entry found in the wp_post(post_type=car) and also not sure this is the optimal way to do this

 SELECT DISTINCT id, IFNULL(COUNT(id),0),name FROM (
SELECT wp.post_author,wp.post_status,wp.post_type,wu1.meta_value FROM wp_posts wp
  LEFT JOIN (
  wp_users wu
  LEFT JOIN wp_usermeta wu1 ON wu1.user_id=wu.ID
  ) ON wp.post_author=wu.ID

  WHERE wu1.meta_key='_manuf' AND wp.post_status='publish'  AND wp.post_type='car'
 ) x
 left jOIN manufacturers ma  ON x.meta_value=ma.id

  GROUP BY id
  ;


texts: Is there a difference between running the source command from the mysql command line client and '<' from outside it? if so, when should I use each?

texts:
I can run the following commands:

C:\myfolder>mysql -uuser -ppassword

Then from within the client do:

mysql>source C:\path\to\file.sql

Or I can do:

C:\myfolder>mysql -uuser -ppassword  < C:\path\to\file.sql

Is there a difference between these two commands? if so, what is it and when should I use each?


texts:
After pg_upgrade one check fails.

This SQL gets executed to list tables and their sequences. In comments to this question it is called "above sql".

SELECT S.relname, T.relname, C.attname
FROM pg_class AS S,
     pg_depend AS D,
     pg_class AS T,
     pg_attribute AS C,
     pg_tables AS PGT
WHERE S.relkind = 'S'
    AND S.oid = D.objid
    AND D.refobjid = T.oid
    AND D.refobjid = C.attrelid
    AND D.refobjsubid = C.attnum
    AND T.relname = PGT.tablename
ORDER BY S.relname;

Source: https://wiki.postgresql.org/wiki/Fixing_Sequences

This check gets used to see of there are sequences which next-value is too low.

This check gets executed daily since several years and it did not fail since several years. This was the first time that we used pg_upgrade and not dump+restore.

Now I am a bit nervous, that some internal references are broken.

The output for auth_user_groups_id_seq is broken:

psql -f above.sql| grep auth_user_groups_id_seq
 auth_user_groups_id_seq    | auth_user_groups        | id
 auth_user_groups_id_seq    | modwork_emailsender     | id

The first line is correct, the second seems broken.

The table modwork_emailsender does not have the sequence auth_user_groups_id_seq.

Has someome an idea why above sql outputs strange values after pg_upgrade?

Versions: We upgraded from 9.3 to 10.2.

Update

We found a different SQL to get all sequences:

select s.relname as seq, n.nspname as sch, t.relname as tab, a.attname as col
from pg_class s
  join pg_depend d on d.objid=s.oid and d.classid='pg_class'::regclass and d.refclassid='pg_class'::regclass
  join pg_class t on t.oid=d.refobjid
  join pg_namespace n on n.oid=t.relnamespace
  join pg_attribute a on a.attrelid=t.oid and a.attnum=d.refobjsubid
where s.relkind='S' and d.deptype='a'

Source: https://stackoverflow.com/questions/6941043/get-table-and-column-owning-a-sequence/6945493#6945493

The first query returns 140 rows (including strange results), the second 120 (without strange results).


texts: Invalid Use of Group Function Using Join

texts:
SELECT
    a.aircraftid 'Aircraft ID',
    COUNT(s.serviceid)
FROM
    aircraft a
JOIN service s ON
    a.aircraftid = s.serviceid
JOIN(
    SELECT
        (
            COUNT(ser.serviceid) / COUNT(DISTINCT ser.aircraftid)
        ) AS Aver
    FROM
        service ser
) AS se
ON
    COUNT(s.serviceid) > se.Aver
GROUP BY
    a.aircraftid

I have been trying to figure out why I am getting the Invalid Use Of Group Function.

What I am trying to do is show the aircraft ID and how many services it has had if the number of services is above the average number of services.

The problem is that I use group by to count the number of services per aircraft. I tried to have the average in the where clause, but I also got an error using the following code:

SELECT
    a.aircraftid,
    COUNT(s.serviceid)
FROM
    aircraft a,
    service s
WHERE
    COUNT(s.serviceid) >(
        COUNT(s.serviceid) / COUNT(DISTINCT s.aircraftid)
    )
GROUP BY
    a.aircraftid


texts:
I have 2 tables that I am dealing with, table 1 has identity information (II), columns ID and Name. Table 2 has data collection (DC) information, InfoId, DCId, data, timestamp. The DC table has time series data for each element in the II Table.

II Table

    id    Name
    0      A
    1      B
    2      C

DC Table

    id  DCId  data  timestamp
     0    0     5      TS3
     1    0     7      TS1
     0    1     8      TS10
     2    1     3      TS5
     1    1     5      TS4
     0    2     6      TS2

Where TS10 is later than TS1, etc

Currently I do a select on the II table to get all of the data, and then use a while loop to get the data in the second table for a matching Id and the max DCid.

Which results in

    Name      Data    TS
      A        8     TS10
      B        5     TS4
      C        3     TS5

I would like to do this with one select statement because I would like the result ordered by timestamp, but I am unsure how to get what I want.

Name      Data    TS
  A        8     TS10
  C        3     TS5
  B        5     TS4

The current code looks similar to this:

Select * from II ;
for ( results )
{
   Select Max(DC.DCId) as MaxDCId from DC where DCId=II.sensorInfoId ;
   Select Data, timestamp from DC where DCId=:MaxDCId AND DC.ID =II.ID
   (Stick results for name, value, and timestamp in HTML table)
}

I am not sure if I need a sub-query join or combinations of both to get what I want (or if it is possible with a single SQL query). I did it this way for expediency...I wanted to get the application running. Now that I want the result table ordered by timestamp, I could cache all the values, sort em and then display the HTML table, but if I could do it with 1 query and let SQL do the work I would rather do that.


texts:
If I delete some huge non-clustered indexes will the backup size of that database also decrease?

I am facing issue with huge backup files and don't have enough disk space to hold those backups.

I have some unused indexes of size 40 GB and above; if I delete this index will the backup file size reduce?


texts: Time series data of several sources in one SQLite table or several? File size impact?

texts:
Here is the situation:

A large amount of time series data from a very limited set of sources is to be kept in a SQLite database file.
Time series sources are identified by a 10 character long string. The number of sources is very low and not at all dynamic.
There is no immediate need for any correlation analysis between the time series sources.

I guess, conventionally, one would dump all time series data in a single table with a text field identifying the source of each data point.

However, I am wondering whether it would be more efficient to create a separate table for each source of time series data? Will the file size of the SQLite database eventually be much smaller by eliminating the repetitive text field? Will queries run much faster?

Or should I completely refrain from creating a table for each source and for what reason?

The database will not be huge, which may warrant the use of SQLite over MySQL. Apart from the integer timestamps, there are four more integer fields. Measurements are made every 3 minutes, but it could easily run for a year or more. Part of the design might end up in an embedded device with a very limited amount of flash memory (64Mbit), hence my initial interest for SQLite. However, I am open to other suggestions.