MySQL corruption even after full dump and re-import We have a production issue where MySQL has started spitting InnoDB corrupt page errors and falling over.

The error message seem to relate to indexes so I assume the data itself is ok and when I restarted the server with innodb_force_recovery=1 I was able to dump the lot using mysqldump.

However, re-importing hasn't fixed the error, dropping/recreating the database didn't help either and I've even gone as far as moving the innodb files out the way and letting MySQL recreate them before re-importing.

No joy, corruptions keep re-occuring within a few minutes and the server dies again.

The weird thing is the database is a mix of ISAM and InnoDB tables and I had a couple of errors in the ISAM tables as well on some attempts.

Is this likely to be something data-related, something corrupt deeper in MySQL that isn't cleared by the above or signs of a hardware fault underneath it all?

EDIT: This is Mysql 5.0.51 running on Ubuntu 8.04

Paolo, can you please provide the mysql error log?  If one isn't available, you can add one by adding this line to your my.cnf file

[mysqld]

log-error=/var/log/mysql/mysql.err

One possible idea to narrow down your research.

You already know how to remove the ibdata and iblog files to create new ones.   If you just want to test your mysql installation.

Remove the Innodb_force_recovery or comment it out
Backup your databases : mysqldump --all-databases -u root -p | gzip -9 > all_databases.sql.gz
Move your MySQL data directory somewhere else: mv /var/lib/mysql{,.bak}
Create a new MySQL data directory: mkdir /var/lib/mysql
Give rights to the MySQL user: chown -R mysql:mysql /var/lib/mysql
Start MySQL: /etc/init.d/mysql start
Run a tail -f on the mysql log you added to my.cnf: tail -f /var/log/mysql/mysql.err

If MySQL isn't running then something else on the system level isn't right.  As Gopinath's link mentionned.  Is there enough disk space: df -h and inodes: df -i? Is you file system healthy? A system reboot or using fsck on the disk partition where MySQL is installed, would detect and possibly fix file system problems.

If MySQL comes back, then you can restore your databases:

zcat all_databases.sql.gz | mysql -u root -p

Send us the logs after completing the steps I sent, and we'll take it from there.


Relationship with a table without unique/primary key I have a database with 3 tables.

Table Work, with a field named workName.

Table SubWorks, which have a field subWorkName and work with a relationship with Work.workName.

And table Activity with field superworkname which should have a relationship with SubWorks.subWorkName.

I can't create this relationship because SubWorks.subWorkName is not an unique key (or primary key, it just don't appear in phpMyAdmin) and it can't be because it's allowed to have multiple SubWorks with the same name until Work.workName is different between them.

How can i resolve this situation? I'm thinking about using a primary key int, but what if one day i go in overflow? Suppose i got so much works to store that i go out the limit?

I added an ID column to Activity so i can differentiate them

That's what i want to do with relationships:

I have a Work named R1.

3 SubWorks, named: R1-1, R1-2, R1-3 with a relationship to R1.

And 2 Activity (ID: 1, 2), with refer with subWorkName to R1-1.
And another activity with ID 3, which refer with subWorkName to R1-2

I want, if i delete R1: R1-1, R1-2, R1-3 will be deleted too and with them all their activity, so activity 1 and 2 will be deleted to.

Now, suppose i delete R1-1, activity ID 1 and 2 should be deleted too.

If i delete R1-2 activity ID 3 should be deleted.

I already did the first part (if i delete a Work, SubWork will gone with it) but i had problem with the second.

Primary keys (and foreign keys) should be unique, otherwise how does the database know which row the foreign key relationship is pointing to?

  I'm thinking about using a primary key int, but what if one day i go in overflow? Suppose i got so much works to store that i go out the limit?

I wouldn't worry about hitting the limit for ints. The mysql documentation lists the limits of various int types. If you're concerned about having more than 2 billion rows anytime soon, you can always use bigint and have over 9 quintillion rows before overflowing. 18 quintillion if you used unsigned.


SELECT same columns of Foreign table multiple time I have tables as follows:

ORG

    id  Name     media_budget  p_contact  s_contact
------  -------  ------------  ---------  -----------
     1  FIFA          4654623          1            3
     2  FIFA_AP          1234          2            3
     3  ICC            203254          3            2

USERS

    id  fname    lname   contact  email
------  -------  ------  -------  -----------------
     1  JHONE    MICHEL   124552  email@gmail.com
     2  William  Martin   254623  gmail@email.com
     3  Ann      Lee      321546  lee@gmail.com

I want to SELECT user.fname for ORG.p_contact and ORG.s_contact for each row in ORG, following statement works fine with single record in ORG however, when query return multiple rows it throws an error

"Error Code: 1242
Subquery returns more than 1 row"

my statement is:

SELECT o.Name, o.media_budget,
(SELECT u.fname AS primaryName    FROM org o LEFT OUTER JOIN users u ON o.p_contact = u.id) AS PrimaryContact,
(SELECT usr.fname AS SecondaryName  FROM org og LEFT OUTER JOIN users usr ON og.s_contact = usr.id) AS SecondaryContact
 FROM org o;

What should be changed in sub-query to achieve desire result :

    id  Name    media_budget  PrimaryContact      SecondaryContact
------  ------  ------------  --------------      ----------------
     3  ICC           203254          Ann            William
     4  FIFA            2656          JOHN            Ann

as showing in question - no chances (You show result exactly for rows not presented in source tables), but:

SELECT
    o.id,
    o.Name,
    o.media_budget,
    u.fname AS PrimaryContact,
    usr.fname AS SecondaryContact
FROM org o
JOIN users u ON o.p_contact = u.id
JOIN users usr ON o.s_contact = usr.id

must work for You

When You use subquery, You must control - subquery must return 0 or 1 records, this is mandatory, for example like this:

SELECT
    o.id,
    o.Name,
    o.media_budget,
    (SELECT u.fname AS primaryName    FROM users u WHERE u.id = o.p_contact LIMIT 1) AS PrimaryContact,
    (SELECT usr.fname AS SecondaryName  FROM users usr WHERE usr.id = o.s_contact LIMIT 1) AS SecondaryContact
 FROM org o;


Forcing a LIMIT to MySQL Queries using ProxySQL, QueryRewrite, or some other method I have a system that end users will need to query but they are not guaranteed to be well versed in query writing.  We wanted to ensure every SELECT statement ends with a LIMIT 100000.  I have thought of some regex to do this and some tools.  It seems the native Query Rewrite is a good option but we needed to use ProxySQL for other things, thus I wanted to see if anyone knows of a way to force this.

It seems like the patter or regex would be something like:
anything that starts with select needs to have a LIMIT on the final line followed by a space and int equal to or less than 100000 then maybe a semi colon.

Has anyone had any luck doing this?

Forget it.  There are ways to write "simple" queries, even with a small LIMIT, that can  take hours to run.

How, if you switch to MariaDB-5.5.21 (or later), there is a "LIMIT ROWS EXAMINED" that would be relatively effective in stopping runaway queries.

An example of where LIMIT does not help much:

SELECT ...
    FROM big_table
    WHERE lots_of_rows_kept
    GROUP BY one_column
    ORDER BY another_column
    LIMIT 1

It will

fetch lots of rows.
GROUP BY, possibly involving a sort of all the rows.
Sort again, this time for the ORDER BY.
Deliver one row.

That is, lots of time and effort went into steps 1,2,3; the final LIMIT had very little impact on the overall time.

Check out Proxy servers -- some of them might have a feature wherein they kill any process running longer than X seconds.

Query Rewrite  My old thoughts on Query Rewrite:  http://mysql.rjweb.org/doc.php/queryrewrite

What's the worst?

So, someone writes a long-running query.  It will hog some resources and slow down other queries, but most likely won't kill the system in any way.  Hopefully, he will be embarrassed and try harder next time.

Anecdotes

I have dealt with several time-series applications.  The first thing I do is build a web site to provide likely information.  And, behind the scenes, I build Summary tables so that queries against them are better (sometimes 10x) than against the raw (Fact) table.

The web pages present the data in an easier to read manner than the non-programmers can get via clumsy SQL.  And I can test them to see that they won't harm the system.  When I am finished, I have no fear of people pounding on my web pages that in turn hit a billion-row dataset.

Yes, I build in LIMITs in various places -- after all, who wants to scroll through a million-row web page (should it ever finish rendering)?  I even give them the ability to change the limit from the sane default I provide.  But they rarely do.

And I listen to their requests.  I try to quickly build whatever they ask for.  (This keeps them from demanding direct SQL access.)

Summary Tables are the key to success.  They don't want item by item results; they want sums/averages by day (or week or hour or ...)

It is easier for me to write the SQL than to explain the nuances of the table.

And, yes, there is always normalization, so JOINs are required.


oracle database filter with full outer join and coalesce intersect in a Oracle Database if I "shoot"

SELECT
    RAP.IDPL,
    C41.TIP_COD_DESEU AS CD41,C41.STOC_VAL AS CANT41,
    C42.TIP_COD_DESEU AS CD42,C42.STOC_VAL AS CANT42,
    C43.TIP_COD_DESEU AS CD43,C43.STOC_VAL AS CANT43
FROM CHEST_UL CHT
    INNER JOIN UL_PUNCT_LUCRU rap
        ON cht.fk_id_raportor = rap.id
    FULL OUTER JOIN CH_41_TAB1 C41
        ON C41.FK_ID_CHEST = CHT.ID
    FULL OUTER JOIN CH_42_TAB1 C42
        ON C42.FK_ID_CHEST = CHT.ID
    FULL OUTER JOIN CH_43_TAB1 C43
        ON C43.FK_ID_CHEST = CHT.ID
    FULL OUTER JOIN CH_44_TAB1 C44
        ON C44.FK_ID_CHEST = CHT.ID
    FULL OUTER JOIN CH_5_TAB1 C5
        ON C5.FK_ID_CHEST= CHT.ID
WHERE COALESCE (C41.STOC_VAL,C42.STOC_VAL,
                  C43.STOC_VAL,C44.STOC_VAL,
                  C5.CANT_PREL)
        IS NOT NULL

the result is:

IDPL    CD1         CANT41  CD2         CANT42  CD3         CANT43
275522                      10.01.01    28.54
275522                                          20.01.01    155
275522  10.01.01    102.766
272343                      10.01.01    123
272343                                          10.01.01    353
272343  10.01.01    102.766

what can I do to obtain a result like this:

IDPL    COD         CANT41      CANT42      CANT43
275522  10.01.01    102.766     28.54
275522  20.01.01                            155
272343  10.01.01    102.766     123         353

You can simply group by IPDL:

SELECT
    RAP.IDPL,
    MAX(C41.TIP_COD_DESEU) AS CD41, MAX(C41.STOC_VAL) AS CANT41,
    MAX(C42.TIP_COD_DESEU) AS CD42, MAX(C42.STOC_VAL) AS CANT42,
    MAX(C43.TIP_COD_DESEU) AS CD43, MAX(C43.STOC_VAL) AS CANT43
FROM CHEST_UL CHT
    INNER JOIN UL_PUNCT_LUCRU rap
        ON cht.fk_id_raportor = rap.id
    FULL OUTER JOIN CH_41_TAB1 C41
        ON C41.FK_ID_CHEST = CHT.ID
    FULL OUTER JOIN CH_42_TAB1 C42
        ON C42.FK_ID_CHEST = CHT.ID
    FULL OUTER JOIN CH_43_TAB1 C43
        ON C43.FK_ID_CHEST = CHT.ID
    FULL OUTER JOIN CH_44_TAB1 C44
        ON C44.FK_ID_CHEST = CHT.ID
    FULL OUTER JOIN CH_5_TAB1 C5
        ON C5.FK_ID_CHEST= CHT.ID
WHERE COALESCE (C41.STOC_VAL,C42.STOC_VAL,
                  C43.STOC_VAL,C44.STOC_VAL,
                  C5.CANT_PREL)
        IS NOT NULL
GROUP BY RAP.IDPL

Are you sure you need the FULL joins? Try changing all to LEFT, might return the same result.

If this can be simplified further depends on the actual PK/FK and data...


Oracle - Should processes limit be lower than sessions parameter SQL> select * from v$resource_limit;

RESOURCE_NAME   CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_ALLOCATION LIMIT_VALUE
-------------   ------------------- --------------- ------------------ -----------
processes       88                  150             150                150
sessions        100                 174             248                248

Assuming that all the sessions are active and doesn't share processes with each other, is it okay to keep the processes limit significantly lower than sessions limit? In all the resources I've read, it's suggested / calculated to keep sessions as processes * 1.5. But what if the active processes reaches its limit and sessions could not able to assign a process to it?
We faced an exception intermittently while establishing a database connection on a successfully running application.
INFO   | jvm 1    | main    | 2020/07/30 13:35:13 |  java.sql.SQLException: Listener refused the connection with the following error:
INFO   | jvm 1    | main    | 2020/07/30 13:35:13 | ORA-12519, TNS:no appropriate service handler found
INFO   | jvm 1    | main    | 2020/07/30 13:35:13 |
INFO   | jvm 1    | main    | 2020/07/30 13:35:13 |     at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:458)
INFO   | jvm 1    | main    | 2020/07/30 13:35:13 |     at oracle.bc.driver.PhysicalConnection.<init>(PhysicalCoConnection.java:546)
INFO   | jvm 1    | main    | 2020/07/30 13:35:13 |     at oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:236)
INFO   | jvm 1    | main    | 2020/07/30 13:35:13 |     at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:32)
INFO   | jvm 1    | main    | 2020/07/30 13:35:13 |     at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:521)
INFO   | jvm 1    | main    | 2020/07/30 13:35:13 |     ...
INFO   | jvm 1    | main    | 2020/07/30 13:35:13 | Caused by: oracle.net.ns.NetException: Listener refused the connection with the following error:
INFO   | jvm 1    | main    | 2020/07/30 13:35:13 | ORA-12519, TNS:no appropriate service handler found
INFO   | jvm 1    | main    | 2020/07/30 13:35:13 |
INFO   | jvm 1    | main    | 2020/07/30 13:35:13 |     at oracle.net.ns.NSProtocol.connect(NSProtocol.java:395)
INFO   | jvm 1    | main    | 2020/07/30 13:35:13 |     at oracle.jdbc.driver.T4CConnection.connect(T4CConnection.java:1102)
INFO   | jvm 1    | main    | 2020/07/30 13:35:13 |     at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:320)
INFO   | jvm 1    | main    | 2020/07/30 13:35:13 |     ... 54 more

Considering that the MAX_UTILIZATION of processes reached its limit of 150 at some point (and sessions reached upto 174), I am suspecting that there isn't enough processes to assign it to a session which led me to think that processes should always be higher than or equal to sessions parameter.
SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0  Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

The value of sessions can not be lower than processes by definition. Even if you try to set it, the database will just ignore it:
SESSIONS

Default value Derived: (1.5 * PROCESSES) + 22
Oracle uses the default value of this parameter as its minimum. Values
between 1 and the default do not trigger errors, but Oracle ignores
them and uses the default instead.

You simply hit the process limit for that instance. Increase the limit accordingly, or investigate what application opened/opens these sessions.
Also the number of sessions for users should be limited by their profile, not the instance level sessions parameter.
CREATE PROFILE - look for SESSIONS_PER_USER.


How to find the SQL statements that caused tempdb growth? The tempdb of a server (SQL Server 2008) increases to 500GB+ several times every month. Is it possible to find out which SQL statements caused this problem? The problem is usually not caused by create table #temp...; insert into #temp... or select ... into #temp... but complex joins.

The Initial Size of some of the tempdb files is also automatically set to much bigger values every time. How to prevent it?

Sometime the cached plans prevent resizing/shrinking the files. How to find which one hold the tempdb?

There are three DMVs you can use to track tempdb usage:

sys.dm_db_task_space_usage
sys.dm_db_session_space_usage
sys.dm_db_file_space_usage

The first two will allow you to track allocations at a query & session level. The third tracks allocations across version store, user and internal objects.

The following example query will give you allocations per session:

SELECT
  sys.dm_exec_sessions.session_id AS [SESSION ID]
  ,DB_NAME(database_id) AS [DATABASE Name]
  ,HOST_NAME AS [System Name]
  ,program_name AS [Program Name]
  ,login_name AS [USER Name]
  ,status
  ,cpu_time AS [CPU TIME (in milisec)]
  ,total_scheduled_time AS [Total Scheduled TIME (in milisec)]
  ,total_elapsed_time AS    [Elapsed TIME (in milisec)]
  ,(memory_usage * 8)      AS [Memory USAGE (in KB)]
  ,(user_objects_alloc_page_count * 8) AS [SPACE Allocated FOR USER Objects (in KB)]
  ,(user_objects_dealloc_page_count * 8) AS [SPACE Deallocated FOR USER Objects (in KB)]
  ,(internal_objects_alloc_page_count * 8) AS [SPACE Allocated FOR Internal Objects (in KB)]
  ,(internal_objects_dealloc_page_count * 8) AS [SPACE Deallocated FOR Internal Objects (in KB)]
  ,CASE is_user_process
             WHEN 1      THEN 'user session'
             WHEN 0      THEN 'system session'
  END         AS [SESSION Type], row_count AS [ROW COUNT]
FROM
  sys.dm_db_session_space_usage
INNER join
  sys.dm_exec_sessions
ON  sys.dm_db_session_space_usage.session_id = sys.dm_exec_sessions.session_id

If you want to track usage over a period of time, consider collecting data with sp_whoisactive, as demonstrated by Kendra Little.


Riak in SasS application I am thinking about using Riak for a SasS application I started working on recently. I was originally thinking about MongoDB but after running into Riak it seems like it may be the better option. The application is for server monitoring and goggle anylatics type data. From my understanding it is pretty easy to do a map reduce job in Riak and its easy to put http caching tools like varnish/nginx to make raik your a close friend. The php drive seems pretty good also. I just woundering if there is anything I am missing in the understand of why you would not want raik for this type of data?

Raik does not support database + tables it simply uses buckets?
Riak is very easy to cluster adding new nodes in is said to be very easy, and very easy to cache?
Supports simple querys, search, and map/reduce, but how is the overall index support to make sure my qouerys are quick and how do search and simple querys perform on when there are a large number of nodes in the cluster?
Any gochas i should be aware of for Riak and its PHP, Ruby, or Python drivers?

Thank you so much :)

If anyone thinks i am crazy and should be looking at another database please feel free to let me know.

The key architectural difference between the two, is in CAP configuration (as-in Brewer's CAP Theorem).  Riak is considered to be an "AP" database (High-availability, Partition-tolerant), whereas MongoDB is a "CP" (Strong-consistency, Partition-tolerant).  The underlying question becomes whether the more-important aspect of your system is to read your data or to write your data.  If successful writes are more-important, and you can handle the fact that not all nodes will be consistent (or will be via "eventual consistency") then I would say Riak is the better choice.  If the more-important aspect is that each read returns accurate data a high percentage of the time, and you're prepared to sacrifice a little performance (due to locking) to get it, then MongoDB is the better choice.

To get an understanding of databases/tables/buckets, make sure to read-up on the Riak data model before getting too deep in your application's design.  The only "gotchya" I would caution against, is that I've heard people use many words to describe doing a Map-Reduce, and "easy" was never one of them.  If that functionality is a key component of your application, I'd make sure to investigate that thoroughly before making any decisions.

Here's an article that does a comparison of Riak vs. MongoDB.  It's by Basho (full-disclosure), so it may have some bias.  Additionally, here's a comparison of several NoSQL databases that you may find useful as well.


How can I store latitude and longitude without PostGIS? I know there are similar questions on here that have been answered but unfortunately none of them work for me. I am hacking my way through creating a page which will display multiple locations on a map. I am using HTML 5 mapping plus Google maps. I have converted the addresses I have, into long/lat and they are stored in a CSV file along with other attributes.

I have tried storing as point, character varying and number as well as trying to create my own definition but have had no success. The table already exists and I just need to add these additional columns. I would rather not use PostGIS since that feels like adding in an additional level of complexity which I could well do without.

So what do you suggest?

The simplest solution without PostGIS would be to store lat/long as two number columns.
numeric for exact precision.
double precision or even just real if you don't need the precision.

I see no reason why the data type point shouldn't work as well. Per documentation:

  Points are the fundamental two-dimensional building block for
  geometric types. Values of type point are specified using either of
  the following syntaxes:

( x , y )
  x , y

  where x and y are the respective coordinates, as floating-point
  numbers.

Related:

Which data type for latitude and longitude?


SQL Server: Should the same log records be backed up multiple times? I came to SQL Server database administration from an Oracle DBA background. So maybe naturally, I always try to understand the differences between the two DBMSs as well as the similarities. One question that have struck me recently is that, should the log records be backed up multiple times, and how? In Oracle database, assuming the database is in the "Archive-Log" database mode, the "redo" log files are archived as separate "archived redo log" files, whose role will be critical in case of a point-in-time recovery scenario, and so because of that, it's very important for these files to be backed up regularly. Often times, DBAs take multiple backups of each "archived redo log" file so that if, for example, a particular tape is lost or damaged, there would still be another copy of the same backup available somewhere else on some other backup device. This will obviously provide a high level of protection against a data loss.
In SQL Server, as we know, there are transaction logs containing log records, which play avital role for performing database recovery. Now my question is this: for a database that is on the "FULL" recover model, would it be possible to backup the log records multiple times? And if possible, would it be a good idea? And how that can be done?

The archive redo log is roughly equivalent to a transaction log backup in SQL Server, and there are options within SQL Server to take multiple copies of the transaction log backup simultaneously.
When performing your BACKUP LOG operation, you can specify MIRROR TO to take multiple copies of the same backup at different locations, providing protection against disk/tape failure.
There is also some useful information here.


Problem with permissions for user Hi I'm trying to create a login role (user) with a fairly minimal ability to modify the database. They should be able to select, delete, create triggers, insert and update. I would then like to have multiple accounts which are members of user so they have the same permissions but I can log edits for individual users.

I'm using pgadmin for my database design and my program accesses this with QtSQL.

The error message I get is caught by Qt when trying to select some data as a test. When I run the same code as the database owner and superuser I have no problems.

bool run_query(const QString & q){
    QSqlDatabase db = QSqlDatabase::database("default");
    QSqlQuery query(db);

    query.exec(q);

    if (!query.isActive()){
        QMessageBox::warning(0, QObject::tr("Database Error"),
                             query.lastError().text());
        return false;
    }

    while (query.next()){
        QString title = query.value(0).toString();
        std::cerr << qPrintable(title) << std::endl;
    }
    return true;
}

run_query("Select forename from contacts;");

  ERROR:  permission denied for relation contacts
  QPSQL: Unable to create query

So far I've created the role (user).

-- Role: user

-- DROP ROLE "user";

CREATE ROLE "user" LOGIN
  ENCRYPTED PASSWORD 'md54d45974e13472b5a0be3533de4666414'
  NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;
COMMENT ON ROLE "user" IS 'Low level user';

As this gave the previous problems. I tried adding permissions for public for the database but this didn't work either.

Now I've no idea where to look for a solution. Any help is much appreciated.
Thanks.

GRANTing ALL permissions for public to the database is mostly redundant (as public has connect, temporary by default, so you'd only be adding CREATE which you probably don't want to do). You probably expected a GRANT ALL on the database to result in a recursive GRANT ALL to contained schemas and tables. GRANT is not recursive, so this doesn't happen; a GRANT ALL on a database just grants CONNECT and TEMPORARY rights to the database, with no effect on contained schemas and tables.

The default GRANTs are, from the docs on GRANT:

  PostgreSQL grants default privileges on some types of objects to
  PUBLIC. No privileges are granted to PUBLIC by default on tables,
  columns, schemas or tablespaces. For other types, the default
  privileges granted to PUBLIC are as follows: CONNECT and CREATE TEMP
  TABLE for databases; EXECUTE privilege for functions; and USAGE
  privilege for languages. The object owner can, of course, REVOKE both
  default and expressly granted privileges. (For maximum security, issue
  the REVOKE in the same transaction that creates the object; then there
  is no window in which another user can use the object.) Also, these
  initial default privilege settings can be changed using the ALTER
  DEFAULT PRIVILEGES command.

So you can see you don't need to GRANT anything on the database unless you want the user to be able to create schemas, etc. You need to:

GRANT USAGE ON SCHEMA myschema TO theuser; for any schema other than public. CREATE can be granted if you want the user to be able to make tables, views, etc.
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE sometable TO theuser; for tables. I've omitted the TRUNATE, REFERENCES and TRIGGER rights as you probably don't want to grant them.
GRANT USAGE ON SEQUENCE sometable_somecolumn_seq TO theuser; for any sequences that are used in table defaults, either explicitly or via a SERIAL or BIGSERIAL column.

... etc. See the manual for GRANT linked above for full definitions of what the privileges do, which are available on which objects, etc. Take note of the wildcard ALL TABLES and ALL SEQUENCES options.

If this seems like too much hassle to do for each table, view, schema, sequence, etc, you can in Pg 9.1 and above use ALTER DEFAULT PRIVILEGES to change the default GRANTs on new objects.

The manual for GRANT
The manual for ALTER DEFAULT PRIVILEGES


Does the PCI standard forbid the use of Windows Authentication? We currently use Mixed Mode for our SQL Server Authentication. I've tried to convince our DBA to allow us to use Windows Authentication so we can use Team Foundation Server, however he absolutely refuses to allow us to have it.

Accord to him, we cannot have Windows Authentication since we are planning to become PCI compliant eventually and PCI requires Mixed Mode.  From what I see online, it's the opposite: The PCI standard actually prefers Windows Authentication over Mixed Mode.

Can someone give me some more information about this (preferably a URL  that states the correct information) so I can direct it to our department head?

No.
As you suggested, the PCI Data Security Standard actually prefers Windows Authentication over any other means of authenticating to SQL Server.
The section of the standard that covers this is Requirement 8: Assign a unique ID to each person with computer access.  SQL Server authentication (which Mixed Mode authentication allows) fails, or makes it extremely difficult to satisfy, the following PCI requirements:

8.5.5 Remove/disable inactive user accounts at least every 90 days.
8.5.8 Do not use group, shared, or  generic accounts and passwords, or  other authentication methods.
8.5.12 Do not allow an individual to submit a new password that is the same as any of the last four passwords he or she has used

As an administrator, the main problem I have with allowing SQL Server authentication is that an application connecting to your database with it will likely pull usernames and passwords in plain text from a configuration file.  Anybody with read access to that configuration file now also has access to your database.
If you're in a Windows shop with a strong Active Directory setup, using only Windows Authentication to connect to your production databases confers many advantages:

Security and identity are enforced at the domain level, making it easy to confer and revoke rights across the domain.

Every person and service gets a separate domain account; service accounts can't remote into machines, and non-DBA person accounts can't connect to the databases.
Your DBA team can no longer share a DBA SQL Server login with God rights on every instance in your environment.

While SQL Server does allow you to enforce password complexity rules (which you have to enforce per PCI DSS requirements 8.5.9-11), I bet AD does this better.  Also, do you really want to enforce these rules in two different places?

Applications connecting using Windows Authentication can no longer expose their credentials in plain text.  It is much harder for someone to pop open a config file or application server and get access to your database.


SQL Server Linked Table in Access DB I'm wondering if/how an SQL Server would be impacted by an Access DB that has ODBC Read-Only linked tables from that SQL Server on it.

If a form event triggers a query on one of those linked tables,  is that query hitting a local version of that linked table, or is it going to impact the overall performance of the SQL Server? I have about 30 users simultaneously using an Access Front End, linked to an Access Back End, and have added functionality that draws on several of our SQL Server's tables; I just can't allow that many users to be hitting the server with query on top of query. It would bog down the SQL Server immensely.

If you have any ideas on how to best proceed, let me know!

Thanks,

Yes, Access on the client machine will connect directly to the linked tables located on SQL Server (or whatever ODBC back-end you're using). If you're dealing with forms and reports bound directly to these linked tables, it shouldn't cause any major performance problems. If you've written more complex queries within Access, then performance can depend on how well those have been optimized.

We've got a number of small Access "apps" running against SQL Server. They're great for when you just need to bang out a simple CRUD tool for a few people in an hour or two. Haven't had any real performance problems with them, though occasionally we run into a lock being held longer than it should.

My advice would be to take all the tables stored in that Access back-end and migrate them to SQL Server as well. Shared Access data files tend to be awful for performance and reliability.


Join query returning 0 rows - impossible where My query is performed from a php web request using a PDO prepared statement.

My desired output is to list the top 10 brands which have the same transaction id as the 'selected' brand :brand by order of frequency.

I initially started with the first query below on a single table with small sample data consisting of 2 columns (txn_id, brand).

My goal/intention now is to make this happen with the actual data, which is split into 2 tables, 1_txns and 2_products,, which have a common field (sku).

Query #1: Queries 1 table, returns desired results. (top 10 brands which share a transaction id with :brand

SELECT brand
FROM transactions
WHERE
id IN (SELECT id FROM transactions WHERE brand = :brand)
AND brand <> :brand
GROUP BY brand
ORDER BY COUNT(*) DESC
LIMIT 10;

I tried to modify my existing query to use the actual data, and the result returned 0 rows. I used EXPLAIN, and an Impossible WHERE was noted. Obviously I'm by no means an expert, and this is actually my first go at handling joins, and subqueries for filters, with MySQL. If I can get a grip on things like this, then I will look to move some of my excel->access solutions to web based. Many Thanks in Advance

SELECT `2_products`.`brand`
FROM `2_products`
RIGHT JOIN `1_txns`
ON `2_products`.`sku`=`1_txns`.`sku`
WHERE `1_txns`.`txn_id` IN (SELECT `1_txns`.`txn_id`
                      FROM `1_txns`
                      WHERE `2_products`.`brand` = :brand)
AND `2_products`.`brand` <> :brand
GROUP BY `2_products`.`brand`
ORDER BY COUNT(*) DESC
LIMIT 10;

EDIT: for Derek Downey

Plain english.  this query is supposed to return a list of the top 10 brands that are purchased with the selected brand.
The data is as follows:
1_txns

(txn_id, sku, salesamount, various other columns like qty etc)
1, 12345, 5
1, 12346, 10
2, 12345, 5
2, 12347, 10
3, 12345, 15
3, 12346, 30
4, 12346, 20
4, 12347, 10

2_products

(sku, brand, various other columns)
12345, Nike
12346, Adidas
12347, Reebok

The desired output is:

If Nike is the selected brand
Adidas
Reebok

If Adidas is the selected brand
Nike
Reebok

If Reebok is the selected brand
Something that will deal with a tie and still list both in alphabetical order

this query is supposed to return a list of the top 10 brands (other than the selected brand) that are purchased with the selected brand.

Try this:

SELECT `2_products`.`brand`, COUNT(*) as numOrders
FROM `2_products`
INNER JOIN `1_txns` ON `1_txns`.sku=`2_products`.sku
INNER JOIN (SELECT `1_txns`.`txn_id`
    FROM `1_txns`
    INNER JOIN `2_products` USING (sku)
    WHERE brand = :brand) as tmp_txns ON tmp_txns.txn_id=`1_txns`.txn_id
WHERE brand <> :brand
GROUP BY brand
ORDER BY numOrders desc, brand ASC
LIMIT 10;

Example sqlfiddle


SQL Server performance: PREEMPTIVE_OS_DELETESECURITYCONTEXT dominant wait type I received a call yesterday from a customer who was complaining about high CPU usage on their SQL Server. We're using SQL Server 2012 64 bit SE. Server is running Windows Server 2008 R2 Standard, 2.20 GHz Intel Xeon (4 Cores), 16 GB RAM.

After making sure that the culprit was in fact SQL Server, I took a look at the top waits for the instance using the DMV query here. The top two waits were: (1) PREEMPTIVE_OS_DELETESECURITYCONTEXT and (2) SOS_SCHEDULER_YIELD.

EDIT: Here are the result of the "top waits query" (although someone restarted the server this morning against my wishes):

We do a lot of intense calculations/conversion, so I can understand SOS_SCHEDULER_YIELD. However, I am very curious about the PREEMPTIVE_OS_DELETESECURITYCONTEXT wait type and why it might be the highest.

The best description/discussion that I can find on this wait type can be found here. It mentions:

  The PREEMPTIVE_OS_ wait types are calls that left the database engine, typically to a Win32 API, and are performing code outside of SQL Server for various task.  In this case, it is deleting a security context previously used for remote resource access.  The related API is actually named DeleteSecurityContext()

To my knowledge, we don't have any external resources like linked servers or filetables. And we don't do any impersonation, etc. Could a backup have caused this to spike or maybe a faulty domain controller?

What the heck could cause this to be the dominant wait type? How can I track this wait type further?

Edit 2: I checked the contents of the Windows Security Log. I see a few entries that may be of interest, but I'm not sure if these are normal:

Special privileges assigned to new logon.

Subject:
    Security ID:        NT SERVICE\MSSQLServerOLAPService
    Account Name:       MSSQLServerOLAPService
    Account Domain:     NT Service
    Logon ID:       0x3143c

Privileges:     SeImpersonatePrivilege

Special privileges assigned to new logon.

Subject:
    Security ID:        NT SERVICE\MSSQLSERVER
    Account Name:       MSSQLSERVER
    Account Domain:     NT Service
    Logon ID:       0x2f872

Privileges:     SeAssignPrimaryTokenPrivilege
            SeImpersonatePrivilege

Edit 3: @Jon Seigel, as you requested, here are the results of your query. A little different than Paul's:

Edit 4: I admit, I'm a first time Extended Events user. I added this wait type to the wait_info_external event and saw hundreds of entries. There is no sql text or plan handle, only a call stack. How can I further track down the source?

I know this question, based on the Title, is mainly concerned with the PREEMPTIVE_OS_DELETESECURITYCONTEXT wait type, but I believe that is a misdirection of the true issue which is " a customer who was complaining about high CPU usage on their SQL Server ".

The reason I believe that focusing on this specific wait type is a wild goose chase is because it goes up for every connection made. I am running the following query on my laptop (meaning I am the only user):

SELECT *
FROM sys.dm_os_wait_stats
WHERE wait_type = N'PREEMPTIVE_OS_DELETESECURITYCONTEXT'

And then I do any of the following and re-run this query:

open a new query tab
close the new query tab
run the following from a DOS prompt: SQLCMD -E -Q "select 1"

Now, we know that CPU is high so we should look at what is running to see what sessions have high CPU:

SELECT req.session_id AS [SPID],
       req.blocking_session_id AS [BlockedBy],
       req.logical_reads AS [LogReads],
       DB_NAME(req.database_id) AS [DatabaseName],
       SUBSTRING(txt.[text],
                 (req.statement_start_offset / 2) + 1,
                 CASE
                     WHEN req.statement_end_offset > 0
                        THEN (req.statement_end_offset - req.statement_start_offset) / 2
                     ELSE LEN(txt.[text])
                 END
                ) AS [CurrentStatement],
       txt.[text] AS [CurrentBatch],
       CONVERT(XML, qplan.query_plan) AS [StatementQueryPlan],
       OBJECT_NAME(qplan.objectid, qplan.[dbid]) AS [ObjectName],
       sess.[program_name],
       sess.[host_name],
       sess.nt_user_name,
       sess.total_scheduled_time,
       sess.memory_usage,
       req.*
FROM sys.dm_exec_requests req
INNER JOIN sys.dm_exec_sessions sess
        ON sess.session_id = req.session_id
CROSS APPLY sys.dm_exec_sql_text(req.[sql_handle]) txt
OUTER APPLY sys.dm_exec_text_query_plan(req.plan_handle,
                                        req.statement_start_offset,
                                        req.statement_end_offset) qplan
WHERE req.session_id <> @@SPID
ORDER BY req.logical_reads DESC, req.cpu_time DESC
--ORDER BY req.cpu_time DESC, req.logical_reads DESC

I usually run the above query as it is, but you could also switch which ORDER BY clause is commented out to see if that gives more interesting / helpful results.

Alternatively you can run the following, based on dm_exec_query_stats, to find highest-cost queries. The first query below will show you individual queries (even if they have multiple plans) and is ordered by Average CPU Time, but you can easily change that to be Average Logical Reads. Once you find a query that looks like it is taking a lot of resources, copy the "sql_handle" and "statement_start_offset" into the WHERE condition of the second query below to see the individual plans (can be more than 1). Scroll to the far right and assuming there was an XML Plan, it will display as a link (in Grid Mode) which will take you to the plan viewer if you click on it.

Query #1: Get Query Info

;WITH cte AS
(
   SELECT qstat.[sql_handle],
          qstat.statement_start_offset,
          qstat.statement_end_offset,
          COUNT(*) AS [NumberOfPlans],
          SUM(qstat.execution_count) AS [TotalExecutions],

          SUM(qstat.total_worker_time) AS [TotalCPU],
          (SUM(qstat.total_worker_time * 1.0) / SUM(qstat.execution_count)) AS [AvgCPUtime],
          MAX(qstat.max_worker_time) AS [MaxCPU],

          SUM(qstat.total_logical_reads) AS [TotalLogicalReads],
   (SUM(qstat.total_logical_reads * 1.0) / SUM(qstat.execution_count)) AS [AvgLogicalReads],
          MAX(qstat.max_logical_reads) AS [MaxLogicalReads],

          SUM(qstat.total_rows) AS [TotalRows],
          (SUM(qstat.total_rows * 1.0) / SUM(qstat.execution_count)) AS [AvgRows],
          MAX(qstat.max_rows) AS [MaxRows]
   FROM sys.dm_exec_query_stats  qstat
   GROUP BY qstat.[sql_handle], qstat.statement_start_offset, qstat.statement_end_offset
)
SELECT  cte.*,
        DB_NAME(txt.[dbid]) AS [DatabaseName],
        SUBSTRING(txt.[text],
                  (cte.statement_start_offset / 2) + 1,
                  CASE
                      WHEN cte.statement_end_offset > 0
                          THEN (cte.statement_end_offset - cte.statement_start_offset) / 2
                      ELSE LEN(txt.[text])
                  END
                 ) AS [CurrentStatement],
        txt.[text] AS [CurrentBatch]
FROM cte
CROSS APPLY sys.dm_exec_sql_text(cte.[sql_handle]) txt
ORDER BY cte.AvgCPUtime DESC

Query #2: Get Plan Info

SELECT  *,
        DB_NAME(qplan.[dbid]) AS [DatabaseName],
        CONVERT(XML, qplan.query_plan) AS [StatementQueryPlan],
        SUBSTRING(txt.[text],
                  (qstat.statement_start_offset / 2) + 1,
                  CASE
                        WHEN qstat.statement_end_offset > 0
                        THEN (qstat.statement_end_offset - qstat.statement_start_offset) / 2
                        ELSE LEN(txt.[text])
                  END
                 ) AS [CurrentStatement],
        txt.[text] AS [CurrentBatch]
FROM sys.dm_exec_query_stats  qstat
CROSS APPLY sys.dm_exec_sql_text(qstat.[sql_handle]) txt
OUTER APPLY sys.dm_exec_text_query_plan(qstat.plan_handle,
                                        qstat.statement_start_offset,
                                        qstat.statement_end_offset) qplan
-- paste info from Query #1 below
WHERE qstat.[sql_handle] = 0x020000001C70C614D261C85875D4EF3C90BD18D02D62453800....
AND qstat.statement_start_offset = 164
-- paste info from Query #1 above
ORDER BY qstat.total_worker_time DESC


Is it OK to use SQL Server availability groups on VMware installations? Can I use SQL Server availability groups on VMware installations or should I use a hardware installation for the Windows Server Cluster?

Sure, you can use Always On Availability Groups inside VMware virtual machines (or Hyper-V, or Xen, or cloud provider VMs, etc.)

They're actually a great fit for high availability and disaster recovery for virtual machines because each VM has its own copy of the database.

Keep in mind that if you're trying to avoid single points of failure, then don't put all replicas on the same shared storage, along with your backups. As the great philosophers once said, you gotta eep 'em separated.


MySQL - Got an error reading communication packets - high open tables, server goes offline I am currently testing a product with a couple of my friends before releasing to the public. There's now 10 of us who use the application daily. Yesterday, we noticed a performance error on the MySQL server, which currently runs on the smallest DigitalOcean droplet, with 1 GB Memory, 1 vCPU, 25 GB SSD Disk and 1 TB Transfer. Our plan is to increase this as soon as we go public, but we were hoping it would be sufficient for testing purposes.

The first thing I looked at was the MySQL error.log file. The output can be found here. This output was captured right after the server crashed for the first time, and I still don't know why.

I also had a look at the config file, which can be found on this link.

A couple more interesting things:

mysql> show global status like '%onn%';
+-----------------------------------------------+---------------------+
| Variable_name                                 | Value               |
+-----------------------------------------------+---------------------+
| Aborted_connects                              | 19                  |
| Connection_errors_accept                      | 0                   |
| Connection_errors_internal                    | 0                   |
| Connection_errors_max_connections             | 0                   |
| Connection_errors_peer_address                | 0                   |
| Connection_errors_select                      | 0                   |
| Connection_errors_tcpwrap                     | 0                   |
| Connections                                   | 121                 |
| Locked_connects                               | 0                   |
| Max_used_connections                          | 10                  |
| Max_used_connections_time                     | 2019-06-27 16:52:29 |
| Performance_schema_session_connect_attrs_lost | 0                   |
| Ssl_client_connects                           | 0                   |
| Ssl_connect_renegotiates                      | 0                   |
| Ssl_finished_connects                         | 0                   |
| Threads_connected                             | 3                   |
+-----------------------------------------------+---------------------+
16 rows in set (0.01 sec)

Note: this was captured right after I restarted the server.

mysql> SHOW VARIABLES LIKE 'table%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| table_definition_cache     | 1400  |
| table_open_cache           | 2000  |
| table_open_cache_instances | 16    |
+----------------------------+-------+
3 rows in set (0.01 sec)

Is there anything I can or should to do increase performance? Is there something I am doing wrong? This is the first time I am handling a MySQL server in combination with NodeJS, whereas my previous experience with MySQL was only with webhosting. If you need anything else, feel free to ask, I will be happy to provide anything I can find.

Per request from Wilson, I have uploaded a couple more files to pastebin which can be found here:

SHOW GLOBAL STATUS;
SHOW GLOBAL VARIABLES;
SHOW FULL PROCESSLIST;
SHOW ENGINE INNODB STATUS;
htop
ulimit -a
iostat -xm 5 3
MySQLTuner
mysqld.cnf (my.cnf)
error.log
mysql.log
mysql-slow.log is empty

My specific concerns go towards, of course memory/cpu usage which caused the server to crash twice with almost no users (like 5 at most) and how I can prevent this and/or allocate the right resources to the right processes etc., but also about the timeout error in the error.log file. Of course if there are other suggestions to optimize my system, I would be happy to. I have this server run on a DigitalOcean droplet with 1 GB memory, 1 vCPU, 25 GB SSD Disk and 1 TB transfer - dedicated for MySQL so nothing else runs on there. Will upgrade to more memory/cpu/disk/transfer when we scale.

At this point I am a little afraid because I suspect a lot of changes would need to be made to make this small server be performant enough. Looking forward to your suggestions.

First of all, you need to tweak your mysql database so that it doesn't use all your server's memory and dies. A good tool for that is using MySQLTuner, it can provide you with good recommendations.

What you should also do is to perform some load testing to see how your application will handle in a real world scenario. You can use several tools for testing, one of them is Jmeter from the Apache Foundation which is free. You can test for example what happens if 100 users concurrently visit and use your application, see what breaks and fix it.


Table size grows indefinitely after deleting and then inserting the same data I create a table and populate it with data:

create table tab
as
select 'value' || level val
from dual
connect by level <= 10000;

If I then delete all rows and insert the same data:

delete from tab;

insert into tab
select 'value' || level val
from dual
connect by level <= 10000;

commit;

The table size increases every time delete-then-insert is performed. Executing these operation multiple times causes the table to grow to many times it's original size.

I would expect the size to remain (approximately) the same. Why is this happening?

A delete marks space as free, it doesn't actually shrink the segment. A subsequent insert should first use available empty blocks below HWM.

I can recover the space using alter table tab shrink space.

I tested in Oracle 12 and Oracle 18c.

If you have access to My Oracle Support:

Space Used By Tables Are Not Being Re-used With Repeat Delete/insert Operation (Doc ID 1601805.1)

So, without just copy-pasting the contents from there, the above note states:

the space management algorithm prefers allocating more space over reusing already allocated but free space
this is not a bug, but a feature, this is how it was designed
you can use shrink to compact the segment, as you have already discovered


Multiple applications accessing SQL Server This is my first post and am very new to MS SQL Server.  We have MS SQL Server running on an existing server. We have a software application that is utilizing the SQL Server as its back-end.

We are purchasing another software application that requires MS SQL Server.  Can we utilize the same SQL Server for this new application or do we need to purchase new SQL server software and install on new server?

Both applications will use the same version of SQL Server.

It depends on a few things.

SQL version. Will the new application run on the same version of SQL as the existing application?
Security. Are you comfortable having the same people possess server-wide permissions to both applications?
Performance. Are you comfortable with a runaway query in one application possibly affecting the performance of the other application?
Maintenance. Are you comfortable with the idea of any maintenance that requires a server reboot or a SQL service restart taking both applications out of commission at the same time?
Vendor support. Do the vendors of each application support having them co-located on a server with other applications? (Just because the vendor doesn't like it, doesn't mean you can't make it work. But if you don't follow the vendor's requirements, you run the risk of them refusing to help you when something goes wrong.)


Relay log read fail, relay or master log currupt ,How to repair? I am running mysql InnoDB cluster 8.0.17(group replication)
During the repication the slave disk gets full and no space left at all the thread waits for the disk space to be freed at the same time this was happening the server restarted once server came back mysql error log shows relay log is corrupt and slave cannot rejoin.
my question is How can I repair the slave?

error log
 Disk is full writing ‘./ic-1-relay-bin-group_replication_applier.000039’ (OS errno 28 – No space left on device). Waiting for someone to free space… Retry in 60 secs. Message reprinted in 600 secs.
2019-08-21T19:02:37.095609Z 33 [ERROR] [MY-010584] [Repl] Slave SQL for channel ‘group_replication_applier’: Could not execute Write_rows event on table sbtest.sbtest3; Error writing file ‘/tmp/MLfd=28’ (OS errno 22019-08-22T06:55:36.766622Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.17) starting as process 910
2019-08-22T06:55:43.510632Z 1 [Warning] [MY-012637] [InnoDB] 81920 bytes should have been written. Only 77824 bytes written. Retrying for the remaining bytes.
2019-08-22T06:55:43.510669Z 1 [Warning] [MY-012638] [InnoDB] Retry attempts for writing partial data failed.
2019-08-22T06:55:43.510683Z 1 [ERROR] [MY-012639] [InnoDB] Write to file ./#innodb_temp/temp_6.ibt failed at offset 0, 81920 bytes should have been written, only 77824 were written. Operating system error number 28. Check that your OS and file system support files of this size. Check also that the disk is not full or a disk quota exceeded.
….
Error number 28 means ‘No space left on device’
2019-08-22T07:02:37.278732Z 1 [ERROR] [MY-012267] [InnoDB] Could not set the file size of ‘./ibtmp1’. Probably out of disk space
2019-08-22T07:02:37.278746Z 1 [ERROR] [MY-012926] [InnoDB] Unable to create the shared innodb_temporary.
2019-08-22T07:02:37.278764Z 1 [ERROR] [MY-012930] [InnoDB] Plugin initialization aborted with error Generic error.
2019-08-22T07:02:37.776328Z 1 [ERROR] [MY-010334] [Server] Failed to initialize DD Storage Engine
2019-08-22T07:02:37.776504Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
2019-08-22T07:02:37.779913Z 0 [ERROR] [MY-010119] [Server] Aborting
…
[MY-010818] [Server] Error reading GTIDs from relaylog: -1
Slave SQL for channel ‘group_replication_applier’: Relay log read failure: Could not parse relay log event entry. The possible reasons are: the master’s binary log is corrupted (you can check this by running ‘mysqlbinlog’ on the binary log), the slave’s relay log is corrupted (you can check this by running ‘mysqlbinlog’ on the relay log), a network problem, the server was unable to fetch a keyring key required to open an encrypted relay log file, or a bug in the master’s or slave’s MySQL code. If you want to check the master’s binary log or slave’s relay log, you will be able to know their names by issuing ‘SHOW SLAVE STATUS’ on this slave. Error_code: MY-013121

After searching for sometime I found a reason and solution.

By default the relay_log_purge variable was on and the relay_log_recovery was off in mysql configuration and both values are default.
As per the mysql documentation the --relay-log-recovery option must be enabled on the slave to guarantee resilience in the event of an unexpected server halt.
so in my case due to no disk space server restarted and relay log corrupt error message came now

SOLUTION:

first remove the node from group

do this on the removed slave node

STOP GROUP REPLICATION

RESET SLAVE

rejoin node to the cluster .It will work.

Explanation:
RESET SLAVE makes the slave forget its replication position in the master's binary log
deletes all the relay log files, and starts a new relay log file.

To use RESET SLAVE on a Group Replication group member, the member status must be OFFLINE, meaning that the plugin is loaded but the member does not currently belong to any group. A group member can be taken offline by using a STOP GROUP REPLICATION statement

For a server where GTIDs are in use (gtid_mode is ON), issuing RESET SLAVE has no effect on the GTID execution history. The statement does not change the values of gtid_executed or gtid_purged


'Privileges dependent' error when try to drop a role/user even if there are no database attached and no role attributes I had a user/role that was a database owner;

so I dropped first the database;
I try dropping the user and I get the following error:

  role 'user_name' cannot be dropped because some objects depend ot it
  DETAIL: privileges for schema public

This is very confusing for me;

The schema public is the default schema schema when a database is created.

By dropping the database, shouldn't schema and other objects related to database dropped ?  Why this privileges error ?

I want not just a solution to fix it, but also to understand why.

I checked:

with \l to be sure the database was droped
\du to check role attributes(empty)

PostgreSQL isn't MySQL. You can't drop databases or users without being attached to some database.  Based on the error message, the database you are attached to in order to execute the DROP USER statement is the one whose public schema has privileges granted to the doomed user.

Schema grants are not attributes of a role, so don't show up in \du.  To see them, you would use \dn+.


update all rows after insertion in mysql I am working with mysql and I have a table with the following structure (a summary):

CREATE TABLE `costs` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`utility` DECIMAL(9,2) UNSIGNED NOT NULL,
`tax` DECIMAL(9,2) UNSIGNED NOT NULL,
`active` TINYINT(1) UNSIGNED NOT NULL DEFAULT '1',
`created_at` TIMESTAMP NULL DEFAULT NULL,
`updated_at` TIMESTAMP NULL DEFAULT NULL,
PRIMARY KEY (`id`),
)

where the active field defaults to 1 when inserting, then i would like when saving a new record all other rows update the active field as 0, so i try to create a trigger for this but i am getting a mysql error.

DELIMITER //
CREATE TRIGGER after_costs_insert AFTER INSERT ON costs FOR EACH ROW
BEGIN
  UPDATE costs SET active = 0 WHERE id <> NEW.id;
END;
//
DELIMITER ;

I think it is not possible to do this, so how can I update these rows?

Updating all rows in a table is seriously non-scalable.

Consider tossing active and updated_at.  Change created_at to be DEFAULT CURRENT_TIMESTAMP (or something like that).  And have INDEX(created_at).  Then,

SELECT ...
    ORDER BY created_at DESC  LIMIT 1

Will get the "latest" row (which seems to be your "active").

If there is a risk of inserting two rows in the same second, there are workarounds.


MySql: how to remove special characters from column in query What i get value in php

    $subject = preg_replace('/[^A-Za-z0-9]/', '', 'abc - 123');

// abc123

Is it possible to use preg_replace in my mysql query?

I want to something like this. want to compare like following

SELECT `id`,`user_id`,`subject` FROM `mail` where preg_replace('/[^A-Za-z0-9]/', '',`subject`)=$subject

I know what i write above is not available. Still i want help. I didn't get proper answer.

I tried with function

CREATE DEFINER=`root`@`localhost` FUNCTION `removeExtraChar`(`str` VARCHAR(255)) RETURNS varchar(255) CHARSET latin1
    NO SQL
BEGIN
  DECLARE i, len SMALLINT DEFAULT 1;
  DECLARE ret CHAR(32) DEFAULT '';
  DECLARE c CHAR(1);
  SET len = CHAR_LENGTH( str );
  REPEAT
    BEGIN
      SET c = MID( str, i, 1 );
      IF c REGEXP '[[:alnum:]]' THEN
        SET ret=CONCAT(ret,c);
      END IF;
      SET i = i + 1;
    END;
  UNTIL i > len END REPEAT;
  RETURN ret;
END

and call it like

My table data

id   user_id   subject
----------------------
1      1        abc-123
2      1        xyz-234
3      2        new-post

SELECT `id`,`user_id`,`subject` FROM `mail` where  removeExtraChar(`subject`)=$subject

but this goes in infinite loop. Nothing executes
Note: I don't want to use extra mysql plugin.

For similar purposes, you can also rely on a series and GROUP_CONCAT:

SET @str:='abc - 123';
SELECT GROUP_CONCAT(c SEPARATOR '')
FROM (
    SELECT MID(@str, id, 1) AS c FROM myseries
    WHERE id BETWEEN 1 AND LENGTH(@str)
) a
WHERE c REGEXP '[[:alnum:]]';

For this you could keep a myseries table for general use, it can be quickly created with 10,000 rows like this:

CREATE TABLE myseries (id int NOT NULL AUTO_INCREMENT PRIMARY KEY);
INSERT INTO myseries
SELECT NULL FROM information_schema.columns, information_schema.columns c2 LIMIT 10000;

Or create one on the fly from any table large enough:

SET @str:='abc - 123';
SET @len:=LENGTH(@str);
SELECT GROUP_CONCAT(c SEPARATOR '')
FROM (
    SELECT MID(@str, id, 1) AS c
    FROM (
        SELECT @num:=@num+1 AS id
        FROM (SELECT @num:=0) num, yourtable
        WHERE @num<=@len
    ) myseries
) a
WHERE c REGEXP '[[:alnum:]]';


pg_dump. Some COPY processes stuck on 'idle in transaction' after pg_dump completion We do a dump of huge database (~ 2 TB) via pg_dump command:
pg_dump -h 192.168.0.132 -d vodka_factory -U vodka_user -F d -j 10 -v -f /backups/vodka_factory_2021-03-26-23-56.dump/

pg_dump process successfully (return code 0, no errors), but on database we can see ~8 COPY processes and one "select" in 'idle in transaction' which never finish:

No locks. More detailed information for some processes:
postgres=# select * from pg_stat_activity where pid=358683;
-[ RECORD 1 ]----+---------------------------------------------------------------
datid            | 16404
pid              | 358683
application_name | pg_dump
client_addr      | 10.136.131.164
client_hostname  |
client_port      | 57732
backend_start    | 2021-03-26 23:56:43.311535+03
xact_start       | 2021-03-26 23:56:43.321329+03
query_start      | 2021-03-26 23:57:07.682549+03
state_change     | 2021-03-26 23:57:07.683027+03
wait_event_type  | Client
wait_event       | ClientRead
state            | idle in transaction
backend_xid      |
backend_xmin     | 3802998627
query            | SELECT last_value, is_called FROM public.waterfall_task_id_seq
backend_type     | client backend

postgres=# select * from pg_stat_activity where pid=358919;
-[ RECORD 1
datid            | 16404
pid              | 358919
application_name | pg_dump
client_addr      | 10.136.131.164
client_hostname  |
client_port      | 57744
backend_start    | 2021-03-26 23:57:07.836322+03
xact_start       | 2021-03-26 23:57:07.844714+03
query_start      | 2021-03-27 00:29:19.240355+03
state_change     | 2021-03-27 00:30:10.458445+03
wait_event_type  | Client
wait_event       | ClientRead
state            | idle in transaction
backend_xid      |
backend_xmin     | 3802998627
query            | COPY public.slr_child_care (id, benefit_type_id, child_count, child_index, start_month, region_index, ent_id, created, modified, external_id, begin, "end", candidate_id, hospital_json, calcs_json, years_replace, first_year_replace, second_year_replace, excluded_days, old_end, compensation, alg_code, state, correction_id, ref_number, date_of_issue, regional_factor, share_by) TO stdout;
backend_type     | client backend

Databse version: PostgreSQL 11.6 on x86_64-pc-linux-gnu
pg_dump version: pg_dump (PostgreSQL) 11.8
Could you please advice how we can investigate the issue? The iisue happens every time...

It is slightly surprising that pg_dump returned sucessfully with a return code of 0. The only theory I can come up with is that some of the 10 jobs got done early and started to idle, whereupon a misconfigured firewall cut the connection. The backends should die after around 2 hours due to TCP keepalive.
What you could do is reduce the keepalive idle time and see if that improves things:
pg_dump -d 'host=... port=... dbname=... user=... keepalives_idle=300' -f dumpfile


Test if a string is a palindrome using T-SQL I am a beginner in T-SQL. I want to decide whether an input string is a palindrome, with output = 0 if it is not and output = 1 if it is. I am still figuring out the syntax. I am not even getting an error message. I am looking for different solutions and some feedback, to gain a better understanding and knowledge of how T-SQL works, to become better at it --I am still a student.

The key idea, as I see it, is to compare the left- and right- most characters to each other, to check for equality, then go on to compare the second character from the left with the 2nd-from last one, etc. We do a loop: If the characters are equal to each other, we continue. If we reached the end, we output 1, if not, we output 0.

Would you please critique:

CREATE function Palindrome(
    @String  Char
    , @StringLength  Int
    , @n Int
    , @Palindrome BIN
    , @StringLeftLength  Int
)
RETURNS Binary
AS
BEGIN
SET @ n=1
SET @StringLength= Len(String)

  WHILE @StringLength - @n >1

  IF
  Left(String,@n)=Right(String, @StringLength)

 SET @n =n+1
 SET @StringLength =StringLength -1

 RETURN @Binary =1

 ELSE RETURN @Palindrome =0

END

I think I am on the right track, but I am still a long way off. Any ideas?

If you are using SQL Server you can use the REVERSE() function to check?

SELECT CASE WHEN @string = REVERSE(@String) THEN 1 ELSE 0 END AS Palindrome;

Including Martin Smith's comment, if you are on SQL Server 2012+ you can use the IIF() function:

SELECT IIF(@string = REVERSE(@String),1,0) AS Palindrome;


How to bulk convert ISO8859 text in Text columns to UTF8 in nvarchar(max) columns? I've been struggling for days with a problem on a MS SQL database. Here is the situation: I have an old Open Source PHP application (Limesurvey version 1.92) running on a Windows 2008R2 server, with SQL2008R2 as RDBMS. I need to migrate the surveys in the application to a new instance, running on Linux with MySQL. The new instance is version 3, and database structure between both versions is different. The scenario I tried to use is upgrading the old version on the old server, and then exporting the surveys as export files, which I can import into the new application. It works well. Except that I'm stuck because of a problem of character encoding. The old application (web application) uses UTF-8. But the database uses only VARCHAR and TEXT columns. Therefore UTF-8 characters are stored using several bytes in these columns : é is stored as Ã© for example. When I upgrade the application, the upgrade script changes the structure of the tables: TEXT columns become NVARCHAR(MAX), but the content in the column is not converted to UTF-8. So I'm searching for a way to change the content in the NVARCHAR(MAX) columns from ISO8859 to UTF-8. The database has approx 200 tables and is 600MB.

SQL Server 2008r2 dopes not support UTF8 (such support does not arrive until the upcoming 2019 release) it only supports UCS2 (essentially a subset of UTF16). Because of this there is unlikely to be a practical solution in SQL Server itself.

You may have to code up a tool to pull the data out, convert it, and update the DB with the newly converted values.

On rereading the question, I notice you mention mysql as the target database, though your tags do not mention that. The types you mention (NVARCHAR etc.) are not mysql types. You may need to clarify the question and update the tags.


How to efficiently recompute the data in a rather large X table? I have recently noticed performance issues for the main application I am developing right now (it is a legacy one). A job triggers a stored procedure that computes the security for all the documents and all the application users in the database. The table looks like the following:
Id INT IDENTITY(1, 1) CONSTRAINT PK_DocumentSecurity PRIMARY KEY CLUSTERED,
DocumentId INT CONSTRAINT FK_DocumentSecurity FOREIGN KEY REFERENCES Document,
UserId INT CONSTRAINT FK_DocumentSecurity FOREIGN KEY REFERENCES ApplicationUser,
CanWrite BIT NOT NULL,
+ an index for DocumentId and UserId

This table has about 3.6M records in production, but this keeps growing due to the increasing number of documents and users.
This happens very often (once a minute) and I have noticed using sp_who2 that it sometimes blocks other SPIDs. The profiler also indicates a very large number of reads and CPU, so clearly, this process is very heavy.
Sometimes, on the test environments, the computation seems to take forever, probably due to a very inefficient query plan that happens from time to time (test servers are significantly lower on resources than the production one).
I have checked the code and the current algorithm looks like the following:

compute a hash for all the records in the tables involved in the computation
if the hash is the same as before, do nothing
insert the result of a big and complicated SELECT statement (I will provide details below) to insert into a persistent table having the exact schema as above
when done the view used for joining to apply the security is altered to use the brand new computed table

The actual computation looks like the following (actual details are removed due to brevity):
SELECT DISTINCT ISNULL(ds.UserId, 0), ISNULL(ds.DocumentId, 0)
FROM (
    SELECT ... FROM Document ... CROSS JOIN ApplicationUser ...
    UNION
    SELECT ... FROM Document ... CROSS JOIN ApplicationUserRole ...
    UNION
    SELECT ... FROM Document ...
    UNION
    SELECT DISTINCT ... FROM DocumentDetail ...
    UNION
) ds
JOIN (
    SELECT ... FROM Document ...
    WHERE (... OR ... OR ... ) AND ...
    UNION
    SELECT ...
) dsp ON dsp.DocumentId = ds.DocumentId AND dsp.UserId = ds.UserId

From my perspective, there are two big problems with this way of computation:

maintenance: it is becoming harder and harder to introduce new security rules
performance: ORs and DISTINCTs are killing the performance

I have already created a story to refactor this code, but I would like to validate that my reasoning is correct (I have an OOP bias, as I am mainly a .NET developer) before starting working on it:

refactor the security rules (to be validated by the business, since they certainly look different than what is already there which also might not be 100% correct for all cases) that they all like the following (precedence matter here):

if role is admin, allow access for all documents if role is sales and
user has read role for document, insert with CanWrite = 0 if role is
sales and user has a write role for document, insert with CanWrite = 1

create a temporary table with the target schema

create an index for DocumentId and UserId

apply each security rule:
INSERT INTO #buffer (DocumentId, UserId, CanWrite)
SELECT ...
FROM Document D ...
  JOIN ApplicationUser U ...
WHERE NOT EXISTS (SELECT 1 FROM #buffer B WHERE B.DocumentId = D.DocumentId AND B.UserId = U.UserId)

truncate the target table

INSERT INTO DocumentSecurity SELECT * FROM #buffer

The last two steps might need to be optimized if they take too long (maybe replace with the swap the table under the view mechanism already in place).
This should make the code more maintainable and I assume the performance will be better if I remove all the ORs and the DISTINCTs, but I am interested in a DBA's perspective for such an approach. Is it suboptimal?
Note: I cannot have duplicates in the security table because there are currently many places (Entity Framework used in application layer + stored procedures) that simply JOIN with the security view (as opposed to check for existence).

The amount of data we are talking about here is not nearly enough to be this big of a problem. Odds are, a bit of optimization will do you quite nicely.
So, opening your query plan, as I go along:

Fix your WHERE clauses. Your current query forces table scans just about everywhere because you use functions. IsNull(COALESCE(linkedlot.IsPrivate, c.IsPrivate), 0) = 0 should be ((ll.IsPrivate = 0) Or (ll.IsPrivate Is Null And c.IsPrivate = 0) Or (ll.IsPrivate Is Null And c.IsPrivate Is Null)). Side note: are these fields actually nullable? Why? These queries would be MUCH simpler if they were not.

Change UNION to UNION ALL. UNION does an implicit DISTINCT, but you're doing one in the main query already. Depending on your data, this may help a lot or hurt a lot.

Make sure your temporary tables have indexes/primary keys.

If the query actually takes a while, load it into a copy of the table, then MERGE it into the live copy afterwards.

Wait, never mind... very simply, your CROSS JOINs are killing you.
Look through your query plan (or rather, save it and open it up in SQL Server Management Studio), and hover over some of those thick arrows. You're exploding Contract times ApplicationUserRole five times (8 million rows every time), Contract times ApplicationUser twice (6 million rows every time), then slapping all those together (on several levels) to get distinct values (sorting 8-16 million rows every time).
But "explode all possible combinations for every possible way rights can be affected, whether they exist or not, then combine, then sort, then sort again" is not what you want. You want users versus documents, and look up their rights.
So let's do that.
Your query should look like this:
Select
  UserId = U.UserId,
  ContractId = C.ContractId,
  -- If there are explicit denials, MAX should be MIN
  CanRead = Max(Case When <<highest priority condition>>
                     When <<down the list...>>
                     Else 0 End),
  -- If there are explicit denials, MAX should be MIN
  CanWrite = Max(Case When <<highest priority condition>>
                      When <<down the list...>>
                      Else 0 End)
From
  dbo.User As U
  Cross Join dbo.Contract As C
  Left Outer Join (<<Look up first thing...>>)
  Left Outer Join (<<down the list...>>)
Group By
  U.UserId, C.ContractId

So, grabbing a few from your plan:
Select
  UserId = U.UserId,
  ContractId = C.ContractId,
  CanRead = Max(Case When ContractOwner.UserId Is Not Null Then 1
                     When <<down the list...>>
                     Else 0 End),
  CanWrite = Max(Case When ContractOwner.UserId Is Not Null Then 1
                      When <<down the list...>>
                      Else 0 End)
From
  dbo.User As U
  Cross Join dbo.Contract As C
  Left Outer Join dbo.LotSupplier As LotSupplier
    On LotSupplier.ContractId = C.Id
  Left Outer Join dbo.ContractBuyer As ContractBuyer
    On ContractBuyer.ContractId = C.Id
  Left Outer Join dbo.Lot As LinkedLot
    On LotSupplier.LotId = LinkedLot.Id
  Left Outer Join dbo.ApplicationUserRole As ContractOwner
    On ContractOwner.UserId = U.UserId
    And ContractOwner.BUid = C.SignatoryBUId
    And ((C.IsPrivate Is Null) Or (C.IsPrivate = Cast(0 As Bit)))  -- NO ISNULL!!
    And ContractOwner.RoleId In (16, 19, 20)
  Left Outer Join dbo.ContractBuyer As ContractBuyer
    On ContractBuyer.ContractId = C.Id
  Left Outer Join dbo.ApplicationUserRole As Buyer
    On ((C.IsPrivate = Cast(1 As Bit)
        Or (LinkedLot.IsPrivate = Cast(1 As Bit))
    And ContractBuyer.BuyerId = Buyer.UserId
    And ...on and on...
  Left Outer Join (<<down the list...>>)
Group By
  U.UserId, C.ContractId

Unless you're running this on a brutally underspecced server, the entire query can be made to run in a few seconds, trust me.
Since you're also obviously working in what I call a many-hats model (a person can be a user, buyer, owner, et cetera, I highly recommend you consider normalizing out a Person (or Party, or whatever tickles your fancy) table.

Information would have to be updated in one place only
You would not have to do that awful join on e-mail
That join would not fail on updating info in only one place :-)
You would be much better set up for sub-contracting, multi-owner contracting, contract transfers and all that fun

It looks like you're kind of using ApplicationUser for this purpose, but I think your question alone shows one of the downsides to that.


Accurately simulate SQL Server work load with diskspd We were building a new node in an alwayson availibility group and this new server was on a different storage than the other nodes in the cluster.  Since it was a separate storage we wanted to make sure the performance was comparable to the other nodes.  The storage engineer told that the new server was on flash storage and should be considerably faster than the old one which was using HDD.  We used dskspd and ran various tests and compared to the existing nodes and the results were not drastically different.  Since we did not lose performance we decided to go ahead anyway and added the node to the existing cluster and moved some reports to that server and to our surprise it was much faster.

So I decided to do some simple test.  I took a table around 200GB and ran the below query to do a clustered index scan

select count(*) from Tabletest with (index(1)) option (maxdop 6)

When I checked on the old server the perfmon counters were something like this

On the new server with flash storage it was

We can see the lower latency and higher reads/sec and get an idea of why the new flash storage is faster.
Now my question is what parameters do we pass in to diskspd to validate this kind of behavour.  We ran diskspd with the below parameters.

diskspd -b8K -d60 -o32 -t8 -Sh -r -w0 -L -c5G G:\MP16\DATA\iotest.dat

New node is the one with flash storage.

We tried different file size and different block sizes.  Even if I bump up the thread to 80, I am able to push way higher IO's through diskspd on the older disk than what SQL Server could.  Screenshot below on old storage with below run

diskspd -b64K -d60 -o32 -t80 -Sh  -w0 -r  -L -c10G G:\MP10\DATA\iotest.dat

So how do we accurately simulate using diskspd a sql server workload(eg clustered index scan)?  Is what I am seeing have something to do with the file that diskspd uses for testing and that somehow is compressed by san?  I read about -Z parameter but it seems to be applicable only for write tests.  Does anyone know how to create a file that is less compressible by the sam or use an actual sql server data file for the test with diskspd?

Now my question is what parameters do we pass in to diskspd to validate this kind of behavour.

We really don't know enough about the clustered index to really make those kind of assumptions. For example, you specify MaxDOP 6 but was it run with MaxDOP 6? Does the clustered index have data in it stored in off-row? How much read-ahead was used?

We can approximate sorta kinda the workload given the disk results, but it would, at best, approximate the workload of scanning your clustered index of this specific table with MaxDOP 6. Since this is a secondary replica that's readable and servicing reports, I highly doubt that's indicative of your overall workload.

DiskSpeed is best when trying to test the storage and validate it against a DiskSpeed on another server, making sure you can hit your latency and bandwidth requirements. It's not made to act like SQL Server and you'll rarely be able to get it to behave in such manner due to multiple other influences that do not exist in DiskSpeed (such as waiting on locks, latches, coordinators, spinlocks, etc.).

  Even if I bump up the thread to 80, I am able to push way higher IO's through diskspd on the older disk than what SQL Server could.

See above. SQL Server works cooperatively in scheduling, you won't get more throughput through SQL Server than you will through dedicated threads for testing such as this, which isn't doing nearly as much as SQL Server (not that it's a good or bad thing, it's just different as they have different uses.)

  Is what I am seeing have something to do with the file that diskspd uses for testing and that somehow is compressed by san?

Benchmarking software, of which DiskSpeed is no exception, has been targeted by storage vendors and has built in algorithms to detect workloads and artificially speed them up. I'm not saying that's what you're seeing but there are multiple factors at play, here. Some things to think about:

Size of SAN cache
Size of SAN cache devoted for read
HBA settings such as Queue Depth
Multipathing setup
IO Group port statistics
Any SVCs in front of the SAN
Any specialized drivers that may also be caching, and their cache sizes
File data, such as completely random, all zero's, repeating patterns
File initialization such as setting a high watermark vs writing random data
Thin or thick provisioning of disk resources

Once you know the cache sizes and initialization types of the files along with the settings per server such as HBA settings and multipathing setup, then you can start to investigate the differences in values between the servers/SANs.

Since we don't know any of this data, at best it'll be pure speculation though given that flash is obviously faster could be a majority, or that it's a new array and less systems are on it thus it's less loaded could be a major factor. Really, more investigation on the SAN backends from the SAN admins is required, such as average IOPs while the tests are running on the entire device, max specifications for the devices, IO group/port statistics on load, etc.

  We used dskspd and ran various tests and compared to the existing nodes and the results were not drastically different.

This means that either the workload does not hit any limitations or exceed maximums of the SAN, or it means that there are infrastructural issues that are artificially limiting your latency/throughput.

  We tried different file size and different block sizes.

The filesize will need to be large enough that it can't be entirely in read cache of the device, unless of course that's what you want to test. You'll need to check the sizes, generally 2x to 4x the size is enough to not be able to cache the data.

Block size changes and number of threads will determine either latency or throughput. It's unclear which you're attempting to test from the results. Testing each has different setups, generally when testing latency a very small block size is used such as 512 bytes or 4k with 4k being the most prevalent these days. This will lead to a maximum number of IOPs with very small sizes, thus very small throughput. To test throughput, a large block size is used, 2MB, 4MB, 8MB and latency tends to be much higher but the maximum throughput is now tested.

  Does anyone know how to create a file that is less compressible by the sam or use an actual sql server data file for the test with diskspd?

DiskSpeed doesn't know what SQL Server is, so while you could use a SQL Server data file... DiskSpeed wouldn't use it like SQL Server does. It would just be a file that it reads offsets and data.


What should I do with mongodb security installed in aws ec2? I using aws linux freetier

When I entered my site today, mongodb all collection was drop... OTL

(I checked the global log and confirmed that the Chinese ip dropped...)

When I installed mongodb, my /etc/mongod.conf setting

# network interfaces
net:
  port: change my port num
  #bindIp: 127.0.0.1
security:
    authorization: enabled

next i was aws security group open and write my port num and anywhere setting

Is this a problem? What should I add more?
Please teach me

Here is a few things to read up upon:
https://docs.mongodb.com/manual/administration/security-checklist/

In your mongo.conf its good to disable the REST API and http interface.

Use a non default Mongo port e.g not 27017 and use AWS security groups to only allow that port to a given IP not to the world.


Oracle database link to MySQL database on RedHat Linux I have an Oracle 11g database on a RedHat server and also have MySQL database on another RedHat server. I want to create a link to the MySQL server from the Oracle which allows me to execute queries in MySQL from Oracle.

Is this possible?

Oracle can connect to MySql with an ODBC connector.  This is described in some detail here.  Also see the official Oracle documentation here.

This will work for Oracle 10 and 11g to MySQL 4.1, 5.0, 5.1 or 6.0.  There are limitations both in performance and what you can do.  My understanding is

no distributed transactions
no insert as select .....
treat numbers as numbers and strings as strings, this is not the place for implicit type casts
as you might expect non-Oracle features are not supported
no Oracle DDL is supported
these functions are supported AVG(exp), LIKE(exp), COUNT(*), MAX(exp), MIN(exp), NOT


Create foreign key constraint on indirect data I am trying to enforce relational integrity across some database design containing sensor data. The relevant parts of the database:

CREATE TABLE logger (
    id INTEGER PRIMARY KEY
);

CREATE TABLE sensor (
    logger_id INTEGER REFERENCES Logger(id),
    logger_sensor_id SMALLINT,
    PRIMARY KEY (logger_id, logger_sensor_id)
);

CREATE TABLE measurement (
    id BIGINT PRIMARY KEY,
    logger_id INTEGER REFERENCES logger(id)
)

CREATE TABLE sensor_measurement_data (
    measurement_id BIGINT REFERENCES measurement(id),
    logger_sensor_id REFERENCES sensor(logger_sensor_id) -- problem is here
)

Since logger_sensor_id is not unique in the sensor table, I can't create the foreign key in sensor_measurement_data and the above won't work. I want to create a foreign key on both the logger_sensor_id and the logger_id which is included in the measurement table.

Is it possible to create such an "indirect foreign key" constraint? I guess it's possible to ensure referential integrity through triggers and checks, but I'd like to know if it's possible with foreign keys - it looks it would be a lot less error-prone to me. If it's not possible, is there a technical reason for this?

I'm currently using postgresql, but I'd surely want to know if other systems would be able to implement the above.

-- Logger LOG exists.
--
logger {LOG}
    PK {LOG}

-- Sensor number SNS# of logger LOG exists.
--
sensor {LOG, SNS#}
    PK {LOG, SNS#}

FK1 {LOG} REFERENCES logger {LOG}

-- Measurement batch MEA was
-- received from logger LOG at time TIM.
--
measurement {MEA, LOG, TIM}
         PK {MEA}
         AK {LOG, TIM}
         SK {MEA, LOG}

FK1 {LOG} REFERENCES logger {LOG}

-- Value VAL was received from sensor number SNS#
-- of logger LOG in measurement batch MEA.
--
sensor_meas {MEA, LOG, SNS#, VAL}
         PK {MEA, LOG, SNS#}

FK1 {LOG, SNS#} REFERENCES sensor {LOG, SNS#}

FK2 {MEA, LOG}  REFERENCES measurement {MEA, LOG}

Note:
All attributes (columns) NOT NULL

PK = Primary Key
AK = Alternate Key   (Unique)
SK = Proper Superkey (Unique)
FK = Foreign Key

Using suffix # to save on screen space.
OK for SQL Server and Oracle, for others use _NO.
For example, rename SNS# to SNS_NO.


Any ways to find past week blocking session Any query to find past week blocking session in SQL server or blocking session which happen 2 weeks ago? Thanks

Unless you are collecting the data in some way (monitoring tool, etc), you aren't going to be able to find the blocking session from a week ago or any time in the past.

Start collecting current activity to a table using sp_WhoIsActive. You can use this method to do it (I wrote the post).


Spinning multiple environments from single backup file I am having a unique situation where my production database size 5TB (SQLSrv) which needs to be replicated across 4 servers daily. I am looking for an option where all 4 environments can use a single backup file as the source and continue to perform the write operation in isolation thereafter. It will save a lot of disk space and restoration time (3hours per env)

If you are open to look into 3rd party solutions, you can try SQL Clone by Redgate.
It will created virtual data files and virtual delta files. These files combined will be presented to SQL Server and it will be treated as a separate copies of the database.
You can find documentation here: https://www.red-gate.com/products/dba/sql-clone


sql server 2008 r2 mirroring I am trying to set up mirroring between two servers behind the same firewall on the same subnet.

I backed up mainserver and restored to mirrorserver.

I'm using the wizard on "mainserver" and connect to the "mirrorserver" using the sa login as both servers have the same sa login password.

The wizard creates endpoints on both servers correctly. When I start the mirroring it asks about FQDN but there is no domain so I click "Yes". It works for a while then I get the error alert below.

What is strange is that the error suggests it can't connect to itself.

Since posting this I found elsewhere suggestions that the problem is the database wasn't restored with NO RECOVERY on the mirrorserver. I tried this and now the wizard says

Cannot connect to mirrorserver

Login failed for user 'sa'. (Microsoft SQL Server, Error: 18456)

previously

Alter failed for Database 'mydatabase'.  (Microsoft.SqlServer.Smo)

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

The server network address "TCP://mainserver:5022" can not be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational. (Microsoft SQL Server, Error: 1418)

This is typical Microsoft. The error message is about tcp ports. The actual error is that the mirror database wasn't restored with NO RECOVERY

BTW this is the best step by step guide I've found to setting up mirroring in a workgroup situation

Database Mirroring between a Server in Domain and Server outside domain (workgroup)


SQL Server log file used pecentage does not drop after multiple backups SQL Server 2005

I ran "dbcc SQLPerf(logspace)", then took two log backups, and ran "dbcc SQLPerf(logspace)" again. No change from 48.55% used, even though "dbcc opentran (foo)" says that there are no active open transactions.

Does that mean that there are inactive open transactions that are preventing the log free space from changing after backups?

Thanks.

In addition to the additional space marked "in-use" by SQLPERF you might have space in your transaction log that is not being freed for other reasons.  You should probably take a look at the log_reuse_wait_desc column of sys.databases. If it says NOTHING or LOG_BACKUP then what you are seeing is probably the space taken up by the current VLF.  If however if you are seeing other values, such as DATABASE_MIRRORING or REPLICATION then a portion of your log is waiting to be replicated to another machine.  VLFs are not marked "free" unless they are completely ready to be re-used.  This can mean an open transaction or waiting on a log backup (if in bulk-logged or full recovery model), but it can also be other things too.


Oracle: spool data to different files depending on where condition (loop) I am trying to get my way around something like this

declare
  filename varchar2(100);
begin
  for condition in (select fieldN from mytable) loop
    filename := 'data_for_value'||condition.fieldN||'txt';
    spool filename
    select some_data from some_table where fieldN = condition.fieldN;
    spool off
  end loop;
end;

In order to export different sections of data from a table onto different files but I just cannot get it right, I have read this and tried to do something similar in consequence:

set serveroutput on
set pagesize 0
set numwidth 2
set trimspool on

DECLARE
  filename varchar2(100);
BEGIN
  for field2 in (select field2 from blau2) loop
    filename := 'COST_TRAN_BASE '||field2.field2||'.txt';

    spool "run_query.sql" REPLACE

    select distinct 'spool "' || filename || '.txt"' || chr(10) ||
              'select field1, field3 from blau2 where field2 = ' || field2.field2|| ';' || chr(10) ||
              'spool off' cmd
    from blau2;
    spool off
    @"run_query.sql"
  end loop;
END;

But to no avail. I get the feeling that I am not allowed to use the spool command where I am trying to (I recently started working in the Oracle environment and the line between SQL*Plus and PL/SQL is still a bit blurry (you can kill me now for my ignorance :') ))

Am I just struggling against the impossible? D:

Edit:

with the initial help of Balazs Papp and my follow up investigations I came up with this system:
Using this file:

basic_script.txt
-----------------
SET PAGESIZE 0
SET TRIMSPOOL ON

spool RUN_QUERY.sql REPLACE

select distinct 'set heading off' || chr(10) ||
        'spool "BLAU2 ' || field2 || '.txt"' || chr(10) ||
                'select field1||chr(9)|| field3 from blau2 where field2 = ' ||
field2 || ';' || chr(10) ||
                'spool off'
    from blau2;

spool off
-----------

And this other file:

basic_script_condition.sql:
-----------
set feedback off
set trimspool on
set recsep off
set serveroutput on

col some_column new_value script_name
select decode((select count(*) from (select distinct FIELD2 from BLAU2)),1,'exit.sql','RUN_QUERY.sql') some_column from dual;
@&script_name
-----------

Calling them from command line in a simple .bat program:

basic_launcher.bat:
-------------
echo exit | sqlplus lmartin/lmartin @"BASIC_SCRIPT.txt"
echo exit | sqlplus lmartin/lmartin @"BASIC_SCRIPT_CONDITION.sql"
-------------

A script for exporting the different sections of data in the BLAU2 table is generated and executed only if there is data to export (if there is no data to export, basic_script would generate a .sql file with no rows selected as its content, possibly repeated N times, and the execution would fail, hence the basic_script_condition.sql controlling this behavior).

SQL> create table t1 (c1) as select mod(rownum, 4) from dual connect by level <= 12;

Just to have some sample data.

$ cat run.sql
set heading off feedback off echo off termout off pagesize 0
spool select.sql
select distinct 'spool c1_' || c1 || '.txt ' || chr(10)|| 'select c1 from t1 where c1 = ' || c1 || ';' || chr(10) || 'spool off' from t1 order by 1;
spool off
@select.sql
!rm select.sql
exit

$ sqlplus -s bp/bp @run.sql

And the result:

$ grep "" *txt
c1_0.txt:         0
c1_0.txt:         0
c1_0.txt:         0
c1_1.txt:         1
c1_1.txt:         1
c1_1.txt:         1
c1_2.txt:         2
c1_2.txt:         2
c1_2.txt:         2
c1_3.txt:         3
c1_3.txt:         3
c1_3.txt:         3


How are the DBA, SYSDBA, SYSOPER roles and SYS, SYSTEM users related in Oracle? I don't know if I understand correctly the privileges of these built-in users and roles.
This is the list of information I have found:

I think that DBA and SYSDBA roles enable to do everything - if this is true, then why are there two names for the same thing?
the SYSOPER role enables to do everything except of accessing users' data,
the SYS user has DBA and SYSDBA roles, so he can do anything,
the SYSTEM user has DBA role, so it seems that he could do anything, but I have read that he cannot do backups, recovery, and DB upgrade.

Do I understand it correctly?

I think that DBA and SYSDBA roles enable to do everything - if this is
true, then why are there two names for the same thing?

They aren't the same thing.  SYSDBA is not a role but a special privilege in and of itself.  It gives the grantee ultimate authority, including the ability to issue shutdown and startup commands, override some other limitations, and a few other items that escape me.  Unlike roles, when one is granted SYSDBA, they are added to the password file (external of the database), allowing them to connect over the network to an idle instance.
DBA is just a role. And while it is created by default, and assigned to user SYSTEM by default, it is fundamentally no different than any role that you might create and assign privileges to.  While it is not recommended that privs be added or removed, there is nothing to stop you from doing so.

the SYSOPER role enables to do everything except of accessing users'
data,

Like SYSDBA, SYSOPER is not a role but a special, hard-coded privilege. Off the top of my head, I cannot remember the limitations, but it's primary purpose is to allow a granted user the ability to shutdown and startup, without giving it a lot of other privileges.  It is not granted to anyone by default, and in my entire career (working in small shops with at most 3 DBAs) I've never had cause to grant it.

the SYS user has DBA and SYSDBA roles, so he can do anything,

Yes.

the SYSTEM user has DBA role, so it seems that he could do anything,
but I have read that he cannot do backups, recovery, and DB upgrade.

Without being granted SYSDBA or SYSOPER (and he shouldn't be) he cannot stop or start the instance.  If he cannot stop the instance, he cannot perform upgrades or recovery. He can perform online backups (IF the database is in archivelog mode) because that doesn't require a shutdown. But what would be the point?


What is the significance of a semicolon at the end of SQL*Plus commands? Some statements like create table, insert into etc take a semicolon at the end:

CREATE TABLE employees_demo
    ( employee_id    NUMBER(6)
    , first_name     VARCHAR2(20)
    , last_name      VARCHAR2(25)
         CONSTRAINT emp_last_name_nn_demo NOT NULL
    ) ;

while others like

set echo on
connect system/manager

go through without the semicolon as well.

What is the reasoning behind this? How can I decide myself where to put the semicolon and where not?

Commants to the local instance execute on return. Multi-line commands to the server execute on semicolon

Special commands as detailed in the SQL*Plus manual are the only ones that do not accept semi-colons. Wheras SQL Commands must end with a ; in order to be parsed by the server.


'Id' with the format: YYYYNNNNNN with the NNNNNN part restarting each year I have a a business requirement that each record in the Invoice table has an id which looks like YYYYNNNNNN.

The NNNNNN part needs to restart at the beginning of each year. So the first row entered in 2016 would look like 2016000001 and the second like 2016000002 etc.
Lets say the last record for 2016 was 2016123456, The next row (of 2017) should look like 2017000001

I don't need this id to be the primary key and I store the creation date as well. The idea is that this 'display id' is unique (so I can query by it) and human group-able, by year.

It is unlikely that any records would be deleted; however, I would be inclined to code defensively against something like that.

Is there any way I could create this id without having to query for the max id this year every time a insert a new row?

Ideas:

A CreateNewInvoiceSP, which gets the MAX value for that year (yucky)
Some magical built in feature for doing exactly this (I can dream right)
Being able to specify some UDF or something in the IDENTITY or DEFAULT declaration (??)
A view which uses PARTITION OVER + ROW() (deleted would be problematic)
A trigger on INSERT (would still need to run some MAX query :( )
An annual background job, updated a table with the MAX for each year inserted which I then... Something?!

All of which are a bit non ideal. Any ideas or variations welcome though!

There are are 2 elements to your field

Year
An auto incrementing number

They do not need to be stored as one field

Example:

A year column which has a default of YEAR(GETDATE())
A number column based on a sequence.

Then create a computed column concatenating them (with appropriate formatting).
The sequence can be reset on change of year.

Sample code in SQLfiddle:*(SQLfiddle doesn't always work)

-- Create a sequence
CREATE SEQUENCE CountBy1
    START WITH 1
    INCREMENT BY 1 ;

-- Create a table
CREATE TABLE Orders
    (Yearly int NOT NULL DEFAULT (YEAR(GETDATE())),
    OrderID int NOT NULL DEFAULT (NEXT VALUE FOR CountBy1),
    Name varchar(20) NOT NULL,
    Qty int NOT NULL,
    -- computed column
    BusinessOrderID AS RIGHT('000' + CAST(Yearly AS VARCHAR(4)), 4)
                     + RIGHT('00000' + CAST(OrderID AS VARCHAR(6)), 6),
    PRIMARY KEY (Yearly, OrderID)
    ) ;

-- Insert two records for 2015
INSERT INTO Orders (Yearly, Name, Qty)
    VALUES
     (2015, 'Tire', 7),
     (2015, 'Seat', 8) ;

-- Restart the sequence (Add this also to an annual recurring 'Server Agent' Job)
ALTER SEQUENCE CountBy1
    RESTART WITH 1 ;

-- Insert three records, this year.
INSERT INTO Orders (Name, Qty)
    VALUES
     ('Tire', 2),
     ('Seat', 1),
     ('Brake', 1) ;