How to block pg_stat_reset() statement in DB Team,

Recently we have found one of our application is triggering pg_stat_reset() executing everyday which is causing performance issue . to disable this statement thru application level will take more time.

Is there any method we can disable in DB even though it trigger from application we should block it.
Please help me to resolve this.

Mutilating PostgreSQL would not be a good idea, so go and fix your application.

One thing that stands out here is that pg_stat_reset can only be executed by a superuser, so either you have a SECURITY DEFINER function that allows your application to call it (in which case you could simply change that function) or you are running your application as superuser, in which case you have bigger problems than performance issues.

Never, ever, run an application as superuser. It is never necessary, and it is an immense security problem.


MySQL: Does Amazon's RDS Multi-AZ feature use binary logs? We are currently running our database in RDS - just one master (no read slaves), with a Multi-AZ deployment for fail-over durability.

I am contemplating some performance tweaks, one of which is disabling binary logs. I know they're used for master->slave replication, but I am not sure whether Amazon uses the same mechanism to support its Multi-AZ set-up or not.

I don't want to accidentally break the Multi-AZ feature by disabling binary logs.

The Multi-AZ feature does not use MySQL's native replication features. Quoting their documentation, "Multi-AZ deployments for Oracle, PostgreSQL, and MySQL DB instances use Amazon technology, while SQL Server DB instances use SQL Server Mirroring." It seems likely that they're doing replication at the storage level and not depending on binary logs. However, disabling binary logs (if RDS will even let you do that) would probably break point-in-time recovery.

Multi-AZ should not be confused with Read Replicas. As @GeekRide's answer shows, Read Replicas do use MySQL's native replication via binlogs.


Generate lower case tag names with Oracle DBMS_XMLGEN? I am generating XML in Oracle PL/SQL with the DBMS_XMLGEN package:

OPEN base_cursor FOR SELECT * FROM foo;
base_context := dbms_xmlgen.newcontext(base_cursor);
base_xml := dbms_xmlgen.getxmltype(base_context, dbms_xmlgen.none);

My problem is that whatever I do I always get the tag names in upper case. To maintain compatibility with things further downstream, I need them to be in lower case. Is there any way I can fix this?

I know there is an option for this in DBMS_XMLQUERY, but the docs says it is not recommended to use that package.

According to ORACLE-BASE,

  Where possible DBMS_XMLGEN should be used as it is more efficient than using the Java based DBMS_XMLQUERY

And:

  The example below is a copy of the previous code with all references to DBMS_XMLQUERY replaced by references to DBMS_XMLGEN. Note that two of the lines have been commented out as this functionality is currently not present in DBMS_XMLGEN

I added my emphasis, because this refers specifically to the settagcase you are looking for.

--DBMS_XMLGEN.settagcase(v_ctx, DBMS_XMLGen.LOWER_CASE);

So it looks like you are stuck with the slower DBMS_XMLQUERY.


Oracle: create index on json, Json item lenght is too long long? I trying to create a index a clob json field. Is anyone know what could be the limit. I have up to 200,000 char. length json record.

Thanks for any tips.

CREATE INDEX emul_full_idx ON CDP_emultimedia (JsonData)
INDEXTYPE IS CTXSYS.CONTEXT
PARAMETERS ('section group CTXSYS.JSON_SECTION_GROUP SYNC (ON COMMIT)')
SQL> /
CREATE INDEX emul_full_idx ON CDP_emultimedia (JsonData)

ERROR at line 1:
ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-20000: Oracle Text error:
DRG-50850: ORA-06515: PL/SQL: unhandled exception JSON item length is too long
ORA-06512: at "CTXSYS.DRUE", line 160
ORA-06512: at "CTXSYS.TEXTINDEXMETHODS", line 366

I could not find any official reference to this, but by experimenting, I found that a JSON record value that is longer than 32K triggers this error.

Maybe this will be fixed in a future release, like this similar bug, that is said to be fixed in 12.2 (this bug is about having JSON identifies longer than 64 bytes, not this error):

Creating a JSON Index Fails with DRG-50850 (Doc ID 2110588.1)

Until then, I would try to use shorter records, or open a Service Request.

And the experiment:

CREATE TABLE tj  (js CLOB  CONSTRAINT c_js CHECK (js IS JSON));

I can easily create an index on "large" (over 200K character) JSON documents, so first a JSON with a lot (50000) of short (2 characters) records:

declare
  c clob;
begin
  c := '{';

  for i in 1..50000
  loop
    c := c || to_clob('"Attr') || to_clob(i) || to_clob('" : "') || lpad(to_clob('X'), 2, 'X') || to_clob('",');
  end loop;

  c := c || '"End" : 0 }';

  insert into tj values (c);
end;
/

The length of this single JSON is:

select dbms_lob.getlength(js) from tj;

DBMS_LOB.GETLENGTH(JS)
----------------------
                938906

Creating the index:

SQL> CREATE INDEX tj_i1 ON tj (js)
INDEXTYPE IS CTXSYS.CONTEXT
PARAMETERS ('section group CTXSYS.JSON_SECTION_GROUP SYNC (ON COMMIT)');

Index created.

Now try a JSON with 1 long record:

SQL> truncate table tj;

Table truncated.

SQL> drop index tj_i1;

Index dropped.

declare
  c clob;
begin
  c := '{';

  for i in 1..1
  loop
    c := c || to_clob('"Attr') || to_clob(i) || to_clob('" : "') || lpad(to_clob('X'), 32800, 'X') || to_clob('",');
  end loop;

  c := c || '"End" : 0 }';

  insert into tj values (c);
end;
/

So our JSON has 1 long record, the overall length is:

select dbms_lob.getlength(js) from tj;

DBMS_LOB.GETLENGTH(JS)
----------------------
                 32825

Now creating the index:

SQL> CREATE INDEX tj_i1 ON tj (js)
INDEXTYPE IS CTXSYS.CONTEXT
PARAMETERS ('section group CTXSYS.JSON_SECTION_GROUP SYNC (ON COMMIT)');

CREATE INDEX tj_i1 ON tj (js)
*
ERROR at line 1:
ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-20000: Oracle Text error:
DRG-50850: ORA-06515: PL/SQL: unhandled exception JSON item length is too long
ORA-06512: at "CTXSYS.DRUE", line 160
ORA-06512: at "CTXSYS.TEXTINDEXMETHODS", line 366

The same works when below the 32K limit:

SQL> truncate table tj;

Table truncated.

SQL> drop index tj_i1;

Index dropped.

declare
  c clob;
begin
  c := '{';

  for i in 1..1
  loop
    c := c || to_clob('"Attr') || to_clob(i) || to_clob('" : "') || lpad(to_clob('X'), 32700, 'X') || to_clob('",');
  end loop;

  c := c || '"End" : 0 }';

  insert into tj values (c);
end;
/

Length, and index:

select dbms_lob.getlength(js) from tj;

DBMS_LOB.GETLENGTH(JS)
----------------------
                 32725

CREATE INDEX tj_i1 ON tj (js)
INDEXTYPE IS CTXSYS.CONTEXT
  3  PARAMETERS ('section group CTXSYS.JSON_SECTION_GROUP SYNC (ON COMMIT)');

Index created.


Problem with pgpoolAdmin installation I got a problem with permissions during installation of pgpoolAdmin(newest version). The problem is when I get to window with main config that should be all okay to move to next installtion step.

The problem is access to two files: pgpool.conf and pcp.conf. Application says: "Write access denied". I tried "chmod 777" on those config files and "chown apache config_file_name" nothing works. I still see "Write access denied". Whole pgpool application is in /var/www/html/pgpoolAdmin. Location of those two files is /usr/local/etc/. I'm using Centos 6.5.

Sorry for no pictures but I have bo permission to upload

You have SELinux enabled, and your policy doesn't allow Apache to write to those files.

Adjust the policy, relabel the files, or (not recommended) disable SELinux.  The sealert can help provide guidance.


How to make SQL Server Management Studio use latest version of SQL Server? I'm upgrading my dev machine from Windows 7 to 8. While installing earlier versions of Visual Studio it installed SQL Server 2005. I then went to this site and downloaded and installed SQL Server Management Studio (SQLManagementStudio_x64_ENU.exe file) and then SQL Server 2012 Express (SQLEXPR_x64_ENU.exe file). Then did a mandatory reboot.

But when I load up management studio and do:

SELECT @@VERSION

I'm still getting SQL Server 2005. Any idea how to make it use SQL Server 2012?

EDIT: This is a screenshot of what I'm getting from SQL Config Manager.

Microsoft SQL has several components.  You can install SQL Studio (the GUI you're using), SQL Server itself (the actual database, to which SQL Studio can connect), Analysis Services, Reporting Services, and others.

You currently have a SQL 2005 server running, and you're connecting to it with SQL Studio 2012.  That's OK!  But if you'd rather have a SQL 2012 database on your local machine, you will need to run the SQL 2012 installation process again, and add that component.  You may want to remove the 2005 installation first (backup your database first, of course).

If you've used WAMP or LAMP, what you have is equivalent to installing different versions of MySQL (the database) and PHPMyAdmin (the GUI).  Or Oracle and Toad.


Oracle 10.0 package shows INVALID status in reference on oracle 11g on import with IMPDP I have imported dump of oracle 10 version with tables, procedures and packages to oracle 11g version.

While am not facing any issues with tables and procedures, certain body of packages compiles with error.

On checking the same packages in old oracle version, i ve noticed that the reference status of package body is VALID. But in 11g version, it is INVALID

Also there is a mismatch in dependencies of package from old version and new version.

Cud someone shed light on what am i missing?

I wana compile the procedure without any error in 11g version!

Most of the times some object privileges are missing from the owner[s] of the imported objects.
the error PLS-00302: component 'XXX' must be declared
can be explained by missing synonyms and/or missing privileges on that object. Verify in the source database that the owner - or sometimes public - had privileges on the object 'XXX' and make sure this is granted in the target database too.
impdp does not give grants on objects that it did not import in the same session. (the not created by me syndrome) Most likely sys objects are playing a role here.


strange behavior with Turkish language setting I have database with Collation Arabic_CI_AS

when I have windows with English settings I can do

select Column from Table where Column= 'A'

and can do

select column from table where column= 'A'

I mean the naming is not case sensitive

In Turkish windows

if any of the names not as named in the DB it raise error in my windows application

I mean that in Turkish windows the names must be case sensitive

any  idea to explain that and how to solve it such that I don't face it again in any language settings

You need to write your application and test it on a case sensitive server, there is no workaround. Asking all your customers to deploy on case insensitive server collations is not the answer. Fix your code, don't break your customers.


Separate letters from digits in alphanumeric string I have an alphanumeric string as input and I want to get two results out of it:

a string where all the numerals are removed
and

an integer that is the sum of all the digits in the input string.

For instance, for this input:
GR35hc7vdH35

I want the following output:
| Col1.         |        Col2    |
----------------------------------
| GRhcvdH       |        23      |

How can do this?

SQL Server doesn't support replacing patterns of multiple characters - so to do it via REPLACE would take potentially 10 operations.

With that in mind one way of doing it would be a recursive CTE to process the digits 0-9 sequentially.

It does the replacement and then checks the length of the before and after strings to know how many characters of that number there were and what needs to be added on to the total.

DECLARE @Input VARCHAR(8000) = 'GR35hc7vdH35';

WITH R(Level,Input,Accumulator,StringLength)
     AS (SELECT 0,
                Input,
                0,
                DATALENGTH(Input)
         FROM   (SELECT REPLACE(@Input, '0', '')) D(Input)
         UNION ALL
         SELECT NewLevel,
                NewInput,
                Accumulator + NewLevel * ( StringLength - NewStringLength ),
                NewStringLength
         FROM   R
                CROSS APPLY (SELECT Level + 1) C(NewLevel)
                CROSS APPLY (SELECT REPLACE(Input, NewLevel, '')) C2(NewInput)
                CROSS APPLY (SELECT DATALENGTH(NewInput)) C3(NewStringLength)
         WHERE  NewLevel <= 9)
SELECT Input       AS Col1,
       Accumulator AS Col2
FROM   R
WHERE  Level = 9;

Or you could use CLR and regular expressions (SQL Server 2012 compatible version).

   using System;
using System.Data.SqlTypes;
using System.Collections;
using System.Text.RegularExpressions;

public partial class UserDefinedFunctions
{
    private static readonly Regex digitRegex = new Regex(@"[\d]", RegexOptions.Compiled);

    [Microsoft.SqlServer.Server.SqlFunction(FillRowMethodName = "FillRow",
                                            TableDefinition = @"Stripped NVARCHAR(MAX),
                                                                Total INT")]

    public static IEnumerable ReplaceAndTotalise(SqlString input)
    {
        if (!input.IsNull)
        {
            int total = 0;
            string stripped = digitRegex.Replace((string)input, match =>
            {
                total += int.Parse(match.Value);
                return string.Empty;
            });

            yield return new Tuple<string, int>(stripped, total);
        }
    }

    public static void FillRow(object resultObject, out SqlString stripped, out SqlInt32 total)
    {
        var result = (Tuple<string, int>)resultObject;
        stripped = result.Item1;
        total = result.Item2;

    }
}

Example Usage

SELECT Stripped,
       Total
FROM   [dbo].[ReplaceAndTotalise]('GR35hc7vdH35')


Count Function with Aggregate function inside the query Hi my actual code is below:

Select M.TicketID,M.CreatedMoment, Max(L.StatusChangeMoment)AS StatusChangeTime,
Elapsed_time_in_Hours_Minutes = CONVERT(NUMERIC(18,2),DATEDIFF(minute, M.createdmoment, MAX(L.statuschangemoment))/60+(DATEDIFF(minute, M.createdmoment, MAX(L.statuschangemoment)) % 60/100.0))
From XX_MASTER_TICKETS AS M Left Join XX_DETAIL_TICKET_STATUS_LOG AS L
On M.RowID = L.TicketRowID
Where M.CreatedMoment between '08-23-2014' And '08-26-2014'

Group by M.TicketID,M.CreatedMoment
Order by M.TicketID asc

And the Partial Results is this:

TicketID    CreatedMoment   StatusChangeTime    Elapsed_time_in_Hours_Minutes
201408231   8/23/14 8:05 AM 8/25/14 11:47 AM    51.42
2014082310  8/23/14 8:19 AM 8/23/14 12:43 PM    4.24
20140823100 8/23/14 8:38 AM 8/24/14 11:15 AM    26.37
20140823101 8/23/14 8:38 AM 8/23/14 11:58 AM    3.2
20140823102 8/23/14 8:38 AM 8/24/14 10:33 AM    25.55

Basically the statuschangetime came from aggregate function, and the last column is the difference of the 2nd and 3rd column.

I want to modify the query so the results will look like this:

Date          below24Hrs    above24hours
2014-8-23        2              3

My problem is i'm getting error when running this code:

Select
[below24hrs] = COUNT (Case WHEN (CONVERT(NUMERIC(18,2),DATEDIFF(minute, TM.createdmoment, MAX(LG.statuschangemoment))/60+(DATEDIFF(minute, TM.createdmoment, MAX(LG.statuschangemoment)) % 60/100.0))) < 24 THEN 1 END)
From XX_MASTER_TICKETS AS M Left Join XX_DETAIL_TICKET_STATUS_LOG AS L
On M.RowID = L.TicketRowID
Where M.CreatedMoment between '08-23-2014' And '08-26-2014'

Group by M.TicketID,M.CreatedMoment
Order by M.TicketID asc

It says cannot count with the MAX aggregrate function inside the query.

Put the first select in a subquery or a common table expression (shown below)

WITH cte (TicketID, CreatedMoment, StatusChangeTime)
AS (
    SELECT M.TicketID,
           M.CreatedMoment,
           MAX(L.StatusChangeMoment) AS StatusChangeTime
    FROM XX_MASTER_TICKETS AS M
    LEFT JOIN XX_DETAIL_TICKET_STATUS_LOG AS L ON M.RowID = L.TicketRowID
    WHERE M.CreatedMoment BETWEEN {d '2014-08-23'} AND {d '2014-08-26'}
    GROUP BY M.TicketID,
             M.CreatedMoment
   )

SELECT CAST(CreatedMoment AS date) AS [Date],
       SUM((CASE WHEN DATEDIFF(minute, CreatedMoment, StatusChangeTime)<60*24 THEN 1 ELSE 0 END)) AS below24Hrs,
       SUM((CASE WHEN DATEDIFF(minute, CreatedMoment, StatusChangeTime)>=60*24 THEN 1 ELSE 0 END)) AS above24Hrs
FROM cte
GROUP BY CAST(CreatedMoment AS date)
ORDER BY 1;

The common table expression returns a list of TicketID, CreatedMoment and MAX(StatusChangeMoment). Using this result set, we can apply a second aggregate in the SELECT statement below. What I'm doing there is a conditional SUM(). I'm adding 1 for each row that meets the criteria and 0 for each row that doesn't.

NB: I've simplified your criteria a bit, I hope it's equivalent to what you're looking for.


how much innodb_buffer_pool_size need to keep under my.ini file? I have Windows Werver 2012 R2 with installed physical memory 672GB. Can you please suggest innodb_sort_buffer_size?

innodb_sort_buffer_size defaults to 1MB.  That is probably good enough.  However, since you have a huge amount of RAM, let's say 4MB might be a little bet better.

I assume you set innodb_buffer_pool_size to somewhere around 500G -- Setting that is the most important thing for InnoDB.  (Exactly 500 is not important.).  And innodb_buffer_pool_instances should be 16?

The above recommendations assume that all (or most) of the use of the server will be for MySQL.  And it assumes you are using InnoDB, not MyISAM.


compare results between two select queries MySQL I'm making test application where users will input their select queries in input box and the application have to compare them if they are identical with "correct answer" query.
"Correct answer" query is in database, and the comparable select is inputed by user.
I read about EXCEPT and INTERSECT but they don't exist in MySQL.
For example I have this database structure:

Question from the application:

List the first name, last name and date of birth of all female students whose first name is Yana but their last name is not Taylor.

correct answer-
SELECT FIRSTNAME, LASTNAME, DATEOFBIRTH
FROM PERSON
JOIN STUDENTS ON PERSONID=STUDENTID
WHERE GENDER=’F’ AND FIRSTNAME=’YANA’ AND LASTNAME<>’TAYLOR’

I want to check that the returned result is identical. wrong answer will be MySQLquery for example with aliased columns:
SELECT FIRSTNAME as first_name, LASTNAME as last_name, DATEOFBIRTH as date_of_birth ....

or even like statement (if it returns more results):
WHERE GENDER=’F’ AND FIRSTNAME LIKE '%YANA%' AND LASTNAME<>'TAYLOR'

My question is how to compare results between two queries that:

Their returned row count is the same
Their column names are equal
It's from the same table
And maybe return some indication for true or false that they are identical?

pt-table-checksum and its friends are very good at validating that the data is (or is not) the same.


Access 2003 (SQL Server 2000) migration to SQL Azure As my old Windows 2003 RAID controller started throwing errors, I am seriously thinking about switching current Access 2003 (adp/ADO) clients to use a Windows SQL Azure solution, in place of current SQL Server 2000.

Does anybody knows if this is a feasable/painless operation?

If your back end is currently SQL 2000, then you need to do a dance to get this to work, but it should be fairly straightforward to do something as follows:

Spin up a VM in Windows Azure, using a template for SQL2008R2 (that's the oldest available template, I believe).
Take backup of your SQL2000 instance, restore it to the Azure VM instance you just created.
Using Visual Studio, create a new database project pointing to the new SQL2008 R2 database.
Change the properties of the project to be SQL Database as the target.
Fix all the errors.
Export/Import to your SQL Database instance.
Remove your Azure VM (unless you want to keep it, of course, but why pay for something you don't need?)

I have no idea how long this will take you to accomplish, but that's the general outline that should work.


Optimal SQL solution I have notification table (very large table). I need your help with the below scenario:

select all notifications for user id;
insert these notifications in notification_log table;
delete all these notifications from the notification table.

My thoughts :

create flag column in notification table and create on update trigger on it to do steps 2 and 3 in the above scenario. Draw Backs: trigger complicity o(n) and I am always select bulk of notification so it will be not nice for performance;
create simple SQL procedure to do the above scenario. Draw Backs: what if step of the scenario failed to commit the whole procedure will roll-back.

Can you help me to optimize this?

How about only SQL statements! no triggers, and no SPs; within a transaction for integrity:

START TRANSACTION;
INSERT INTO notification_log SELECT field1, field2, ... FROM notification_table WHERE user_id=123;
DELETE FROM notification_table WHERE user_id=123;
COMMIT;

Make sure you have an index on user_id.

HTH


How to run stored procedure in another database on the same server using a different login? I have 2 databases (A and B) on the same server. Database A has user ua defined, and database B has user ub defined.

I have a stored procedure in A, running as ua, that needs to run a stored procedure in B, running as ub (this is the only user that has access).

Until now, A and B were located on different servers, so I used linked server to switch login.
But now, when running the stored procedure in A, I get the following error: "Transaction context in use by another session".

I understood from googling for this error that this is by design, based on Microsoft's article - it mentions that loopback linked servers cannot be used in a distributed transaction.

Are there any other ways to make the stored procedure in A, running as ua, to run the stored procedure in B as ub, if both databases are located on the same server?

Thanks a lot!
Alex

Assuming ua is associated with the login la and ub is associated with lb.

To make this work you could grant impersonation of lb to la. Then you can create a wrapper of b.dbo.someproc in a like this:

CREATE PROCEDURE dbo.b_someproc
AS
BEGIN
  EXECUTE AS LOGIN='lb';
  EXEC b.dbo.someproc;
  REVERT;
END;

However, impersonation is a very broad permission that you probably do not want to grant. To get around it you can either add user ub to database a or user ua to database b.

If you add ub to a you can write a wrapper like this:

CREATE PROCEDURE dbo.b_someproc
WITH EXECUTE AS 'ub'
AS
BEGIN
  EXEC b.dbo.someproc;
END;

By far the easiest way however is to ad ua to b and just allow it to call the procedure directly.


How can I eliminate this costly index seek operation from my query? I have two tables in Sql Server 2008 that look something like this:

Transaction_Details:
    Dataset,
    Department,
    Charge_ID,
    Transaction_Type,
    dos_month,
    post_month,
    amount

Charge_Summary
    Dataset,
    Department,
    Charge_ID,
    dos_month,
    Ins1_Category

The Output I'm looking for would look something like this:
    Dataset,
    Department,
    Ins1_Category,
    dos_month,
    Post_Month,
    Total_Payments,
    Total_Charges,
    Previous_Balance -- Defined as total amount for all matching transactions with a post_month previous to the current month

The SQL I'm using below gives the expected output, but there is one index seek in the execution plan taking up over 90% of the cost.  This is in a datawarehouse operation, so there is no other activity on the server aside from this query, no other reads or writes to worry about.  The execution time of ~30 minutes is acceptable for what it's being used for, but I'd still like some advice if there's a better way to do this.

Link to execution plan on pastebin

SELECT
    t1.Dataset,
    t1.Department,
    c1.Ins1_Category,
    t1.dos_month,
    t1.post_month,
    SUM(case
            when t1.transaction_type = 'payment' THEN t1.amount
            ELSE 0
        END
        ) AS total_payments,
    SUM(case
            when t1.transaction_type = 'adjustment' THEN t1.amount
            ELSE 0
        END
        ) AS total_adjustments,
    SUM(case
            when t1.transaction_type = 'charge' THEN t1.amount
            ELSE 0
        END
        ) AS total_charges,
    (
        SELECT
            SUM(t2.amount)
        FROM
            Transaction_Details t2
        LEFT JOIN
            Charge_Summary c2
        ON
            t2.Dataset = c2.Dataset AND
            t2.Charge_ID = c2.Charge_ID
        WHERE
            t1.Dataset = t2.Dataset AND
            t1.Department = t2.Department AND
            t1.dos_month = t2.dos_month AND
            t1.post_month > t2.post_month AND
            t2.Charge_ID IS NOT NULL AND
            c2.Ins1_Category = c1.Ins1_Category
    ) as previous_balance
FROM
    Transaction_Details t1
LEFT JOIN
    Charge_Summary c1
ON
    t1.Dataset = c1.Dataset AND
    t1.Charge_ID = c1.Charge_ID
WHERE
    t1.Charge_ID is not null
GROUP BY
    t1.Dataset,
    t1.Department,
    c1.Ins1_Category,
    t1.dos_month,
    t1.post_month

In addition to the T-SQL techniques discussed in Aaron Bertrands article, "Best approaches for grouped running totals", you might want to look at a SQLCLR procedure (if you are running less than SQL Server 2012).

The main advantages are that a SQLCLR procedure requires only a single scan of the source records, and benefits from the increased execution speed of compiled code. The sample below is taken from Aaron's post:

DDL

CREATE TABLE dbo.SpeedingTickets
(
  IncidentID    INT IDENTITY(1,1) PRIMARY KEY,
  LicenseNumber INT          NOT NULL,
  IncidentDate  DATE         NOT NULL,
  TicketAmount  DECIMAL(7,2) NOT NULL
);

CREATE UNIQUE INDEX x
ON dbo.SpeedingTickets(LicenseNumber, IncidentDate)
INCLUDE(TicketAmount);

Sample data

;WITH TicketAmounts(ID,Value) AS
(
  -- 10 arbitrary ticket amounts
  SELECT i,p FROM
  (
    VALUES(1,32.75),(2,75), (3,109),(4,175),(5,295),
          (6,68.50),(7,125),(8,145),(9,199),(10,250)
  ) AS v(i,p)
),
LicenseNumbers(LicenseNumber,[newid]) AS
(
  -- 1000 random license numbers
  SELECT TOP (1000) 7000000 + number, n = NEWID()
    FROM [master].dbo.spt_values
    WHERE number BETWEEN 1 AND 999999
    ORDER BY n
),
JanuaryDates([day]) AS
(
  -- every day in January 2014
  SELECT TOP (31) DATEADD(DAY, number, '20140101')
    FROM [master].dbo.spt_values
    WHERE [type] = N'P'
    ORDER BY number
),
Tickets(LicenseNumber,[day],s) AS
(
  -- match *some* licenses to days they got tickets
  SELECT DISTINCT l.LicenseNumber, d.[day], s = RTRIM(l.LicenseNumber)
    FROM LicenseNumbers AS l CROSS JOIN JanuaryDates AS d
    WHERE CHECKSUM(NEWID()) % 100 = l.LicenseNumber % 100
    AND (RTRIM(l.LicenseNumber) LIKE '%' + RIGHT(CONVERT(CHAR(8), d.[day], 112),1) + '%')
    OR (RTRIM(l.LicenseNumber+1) LIKE '%' + RIGHT(CONVERT(CHAR(8), d.[day], 112),1) + '%')
)
INSERT dbo.SpeedingTickets(LicenseNumber,IncidentDate,TicketAmount)
SELECT t.LicenseNumber, t.[day], ta.Value
  FROM Tickets AS t
  INNER JOIN TicketAmounts AS ta
  ON ta.ID = CONVERT(INT,RIGHT(t.s,1))-CONVERT(INT,LEFT(RIGHT(t.s,2),1))
  ORDER BY t.[day], t.LicenseNumber;

SQL CLR procedure DDL

CREATE ASSEMBLY [Demo] AUTHORIZATION [dbo]
FROM [encoded data, 11266 characters]
WITH PERMISSION_SET = SAFE;
GO
CREATE PROCEDURE [dbo].[TicketRunningTotals]
AS EXTERNAL NAME [Demo].[StoredProcedures].[TicketRunningTotals];

Usage

EXECUTE dbo.TicketRunningTotals;

Output

Execution plan

SQL CLR source code

using System.Data;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;

public partial class StoredProcedures
{
    [SqlProcedure]
    public static void TicketRunningTotals()
    {
        using (var conn = new SqlConnection("context connection = true"))
        {
            conn.Open();

            using (var cmd = new SqlCommand())
            {
                cmd.Connection = conn;
                cmd.CommandText =
                    @"
                    SELECT
                        ST.LicenseNumber,
                        ST.IncidentDate,
                        ST.TicketAmount
                    FROM dbo.SpeedingTickets AS ST
                    ORDER BY
                        ST.LicenseNumber,
                        ST.IncidentDate
                    ";

                var columns = new SqlMetaData[4]
                {
                    new SqlMetaData("LicenseNumber", SqlDbType.Int),
                    new SqlMetaData("IncidentDate", SqlDbType.Date),
                    new SqlMetaData("TicketAmount", SqlDbType.Decimal, 7, 2),
                    new SqlMetaData("RunningTotal", SqlDbType.Decimal, 9, 2)
                };

                int licenseNumber, lastLicenseNumber;
                var ticketAmount = 0m;
                var runningTotal = 0m;

                var pipe = SqlContext.Pipe;
                var reader = cmd.ExecuteReader(CommandBehavior.SingleResult);
                var record = new SqlDataRecord(columns);

                pipe.SendResultsStart(record);

                if (reader.Read())
                {
                    lastLicenseNumber = reader.GetInt32(0);

                    do
                    {
                        licenseNumber = reader.GetInt32(0);
                        ticketAmount = reader.GetDecimal(2);

                        if (licenseNumber != lastLicenseNumber)
                        {
                            runningTotal = 0m;
                            lastLicenseNumber = licenseNumber;
                        }

                        record.SetInt32(0, licenseNumber);
                        record.SetDateTime(1, reader.GetDateTime(1));
                        record.SetDecimal(2, ticketAmount);
                        record.SetDecimal(3, runningTotal += ticketAmount);

                        pipe.SendResultsRow(record);
                    }
                    while (reader.Read());
                }

                pipe.SendResultsEnd();

            }
        }
    }
}


How to sort rows by rpm versions there are already some questions about how to sort version numbers. However, these do not deal with the RPM version scheme but often semver (Major, Minor, Patch). The RPM versioning specification is a bit more open and complex. For example it is also possible to use a date based version. Here is an example for how RPMs can be sorted by the version: link
Now to the real question, how can I sort and filter the records, taking into account the complexity of the version and releases, that I get a list in SQLite and PostgreSQL that contains the latest N RPM packages per RPM? Here are sample questions to solve:

What is the latest RPM pro package?
What are the latest two RPMs for the my-first-rpm package?
What are the new five RPMs?

I have the following table with records (sqlite):
CREATE TABLE packages (
  name                  VARCHAR(64)      NOT NULL,
  epoch                 INTEGER          NOT NULL,
  version               VARCHAR(32)      NOT NULL,
  release               VARCHAR(64)      NOT NULL,
  architecture          VARCHAR(16)      NOT NULL,
  CONSTRAINT pk_packages PRIMARY KEY (name, epoch, version, release, architecture)
);

INSERT INTO packages VALUES
("my-first-rpm", 0, "0.1.0", "0", "x86_64"),
("my-first-rpm", 0, "0.1.7", "0", "x86_64"),
("my-first-rpm", 0, "0.1.10", "0", "x86_64"),
("my-second-rpm", 0, "0.1.0", "0", "x86_64"),
("my-second-rpm", 1, "0.1.0", "0", "x86_64"),
("my-third-rpm", 0, "0.1", "0", "x86_64"),
("my-third-rpm", 0, "0.1.1", "0", "x86_64"),
("my-date-based-rpm", 0, "2021010128001", "0", "x86_64"),
("my-date-based-rpm", 0, "2021010128002", "0", "x86_64");

Maybe you need windowing functions like:
select
RANK () OVER (
    PARTITION BY name
    ORDER BY epoch desc, version desc, release desc
        ) namerank,
name,
epoch,
version,
release,
architecture
from packages
order by name,namerank;

But it does not list the versions in the correct order because it sees 0.1.7 as greater than 0.1.10 which it is as a character string. You might need version split into multiple columns of type number to get 0.1.10 greater than 0.1.7 or convert it to a number that keeps the order you want. I tested this on Oracle since I don't have sqllite or postgres handy. Output:
  NAMERANK NAME                                                                  EPOCH VERSION                          RELEASE                                                          ARCHITECTURE
---------- ---------------------------------------------------------------- ---------- -------------------------------- ---------------------------------------------------------------- ----------------
         1 my-date-based-rpm                                                         0 2021010128002                    0                                                                x86_64
         2 my-date-based-rpm                                                         0 2021010128001                    0                                                                x86_64
         1 my-first-rpm                                                              0 0.1.7                            0                                                                x86_64
         2 my-first-rpm                                                              0 0.1.10                           0                                                                x86_64
         3 my-first-rpm                                                              0 0.1.0                            0                                                                x86_64
         1 my-second-rpm                                                             1 0.1.0                            0                                                                x86_64
         2 my-second-rpm                                                             0 0.1.0                            0                                                                x86_64
         1 my-third-rpm                                                              0 0.1.1                            0                                                                x86_64
         2 my-third-rpm                                                              0 0.1                              0                                                                x86_64

Bobby


Is there a way to pass to execute immediate only once a variable but use it several times? I have a Cursor that gets its data from a script along these lines:

OPEN myCursor FOR
SELECT value1 AS Value1,
  myPackage.function1 (value1, :myId) AS Result1,
  myPackage.function2 (value1, :myId) AS Result2,
  myPackage.function3 (value1, :myId) AS Result3
FROM mySchema.myTable
WHERE tpk = :myPk
USING myId, myId, myID

As you can see I use the myId variable 3 times and I have to pass it 3 times in the using statement for it to work.
I couldn't find it anywhere, but is there a way I could pass the myId variable only once?

I don't know of any built-in way to accomplish this but you could use a derived table or a Common Table Expression (CTE or as Oracle likes to call them: subquery_factoring_clause), if your aim is to pass the parameters only once in the USING:

-- derived table
OPEN myCursor FOR
    SELECT value1 AS Value1,
      myPackage.function1 (value1, my.id) AS Result1,
      myPackage.function2 (value1, my.id) AS Result2,
      myPackage.function3 (value1, my.id) AS Result3
    FROM (SELECT :myId AS id, :myPk AS pk FROM dual) my
      CROSS JOIN
        mySchema.myTable
    WHERE tpk = my.pk
USING myPk, myId

-- CTE
OPEN myCursor FOR
    WITH my AS
        (SELECT :myId AS id, :myPk AS pk FROM dual)
    SELECT value1 AS Value1,
      myPackage.function1 (value1, my.id) AS Result1,
      myPackage.function2 (value1, my.id) AS Result2,
      myPackage.function3 (value1, my.id) AS Result3
    FROM my
      CROSS JOIN
        mySchema.myTable
    WHERE tpk = my.pk
USING myPk, myId


Different where clause based on SSRS parameter I have an SSRS report where a parameter can take 3 values. The select query is the same in all cases but the where clause needs to check different date fields based on the parameter value. Is this possible in the same query?

Query:

SELECT OrdNO, OrdType, customer, SalesRep, FundDate, DisbursementDate, Branch, SalesPrice
FROM Orders

-- Case @ReportType=1, use FundDate
   where (FundDate >= @FromDate and FundDate <= @ToDate)

-- Case @ReportType=2, use DisbursmentDate
   Where (DisbursementDate >= @FromDate and DisbursementDate <= @ToDate)

-- Case @ReportType=3, use both dates
   where (FundDate >= @FromDate and FundDate <= @ToDate) or (DisbursementDate >= @FromDate and DisbursementDate <= @ToDate)

Yes, something like the below.

SELECT *
FROM DataTable
WHERE (@SearchField = 'CreateDate' AND CreateDate BETWEEN @StartDate AND @EndDate)
OR (@SearchField = 'ModifyDate' AND ModifyDate BETWEEN @StartDate AND @EndDate)

Or, to use the example WHERE clauses you specified, how about the below?

SELECT *
FROM DataTable
WHERE (
        @ReportType = 1
        AND FundDatte >= @FromDate
        AND FunDate <= @ToDate
        )
    OR (
        @ReportType = 2
        AND DisbursementDate >= @FromDate
        AND DisbursementDate <= @ToDate
        )
    OR (
        @ReportType = 3
        AND (
            (
                FundDatte >= @FromDate
                AND FunDate <= @ToDate
                )
            AND (
                DisbursementDate >= @FromDate
                AND DisbursementDate <= @ToDate
                )
            )
        )


Encryption of Data, Log and Backup files using Always Encrypted in SQL Server 2016 When we implement the Always Encrypted feature in SQL Server 2016, do the .mdf files, .ldf files and .bak files get encrypted as well?

I would like to know if the data/log/backup files gets encrypted or not.

The whole entire data/log file doesn't get encrypted. Only the specific fields you encrypt are encrypted, and yes, those are encrypted in the data, log, and backup files. SQL Server never sees the unencrypted values.

In the docs for Always Encrypted, Microsoft explains:

  Always Encrypted allows clients to encrypt sensitive data inside client applications and never reveal the encryption keys to the Database Engine ( SQL Database or SQL Server).

That isn't great wording, but what it means is that not only is the data encrypted, the SQL Server engine doesn't even have the key to decrypt it for you.

Thus the name - as far as the SQL Server engine is concerned, the data is Always Encrypted.


MySQL 5.6: explicit_defaults_for_timestamp I have the following replication topology:

  DB1 (MySQL 5.5) -> DB2 (MySQL 5.6,  explicit_defaults_for_timestamp = 1) -> DB3 (MySQL 5.6,  explicit_defaults_for_timestamp = 1)

- "date" field:

`date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,

- DB3 replication error:

[ERROR] Slave SQL: Error 'Column 'date' cannot be null' on query. Default database: 'test'. Query: 'INSERT INTO test_log VALUES (null,'12345',12345,'test','saved')', Error_code: 1048

The reason why DB3 is failing is explained here:

  No TIMESTAMP column is assigned the DEFAULT CURRENT_TIMESTAMP or ON
  UPDATE CURRENT_TIMESTAMP attributes automatically. Those attributes
  must be explicitly specified.

I would like to understand why DB2 is working fine, I guess that's because it's replicating from MySQL 5.5 but what settings are responsible for this?

Update Wed  1 Oct 09:34:03 BST 2014:

Table definition match on all three servers:

mysql> SHOW CREATE TABLE test_log\G
*************************** 1. row ***************************
       Table: feedback_log
Create Table: CREATE TABLE `feedback_log` (
  `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `order_ref` varchar(32) NOT NULL,
  `id` int(11) NOT NULL,
  `version` varchar(12) NOT NULL,
  `event` varchar(60) NOT NULL,
  KEY `order_ref` (`order_ref`),
  KEY `id` (`id`),
  KEY `version` (`version`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

SQL_MODE shouldn't be the case here:

  DB1: None
  DB2, DB3: NO_ENGINE_SUBSTITUTION

Summary:

I can't run this query manually on both slaves (DB1, DB2) but it's replicated successfully on DB2:

mysql [5.6.20-68.0-log]> INSERT INTO test_log VALUES (null,'12345',12345,'test','saved')';
ERROR 1048 (23000): Column 'date' cannot be null

Another quick test showing this behaviour:

DB1

mysql [5.5.39-log]> CREATE TABLE t1 (date TIMESTAMP);
Query OK, 0 rows affected (0.20 sec)

mysql [5.5.39-log]> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

DB2

mysql [5.6.20-68.0-log]> SELECT @@explicit_defaults_for_timestamp;
+-----------------------------------+
| @@explicit_defaults_for_timestamp |
+-----------------------------------+
|                                 1 |
+-----------------------------------+
1 row in set (0.00 sec)

mysql [5.6.20-68.0-log]> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

DB3

mysql [5.6.20-68.0-log]> SELECT @@explicit_defaults_for_timestamp;
+-----------------------------------+
| @@explicit_defaults_for_timestamp |
+-----------------------------------+
|                                 1 |
+-----------------------------------+
1 row in set (0.04 sec)

mysql [5.6.20-68.0-log]> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `date` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

I had the same problem, tried to change the sql_mode but didn't work, then changed the explicit_defaults_for_timestamp to 0 and worked!!!, but I encourage you to read the following to understand the issue:
http://openbedrock.blogspot.mx/2015/04/what-happened-to-mysql-timestamp-columns.html
and
https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html


Recommendation for mySQL table structure (no code) I need some advice on how to layout the tables for a MySQL database.

I will first describe what I am trying to do:

I have a table for users. (This covers basic info for now)
A user will select a certain workout plan (say plan 1-6). Different workout
The user will then check a number of checkboxes. Say there are 15 checkboxes for the different machines, a user can select however many he/she wants. Let's say the user choose 6 different checkboxes, not necessarily in order. Each checkbox would have a certain ID. Those checkboxes could post a value of 1 to the database if checked
On the next page a list of every checkbox the user clicked would be generated. Each checkbox has a picture and name associated with it. EX: For gym machines
Each machine the user chose is displayed along with a certain number of sets that is set by an admin

Each plan has certain workout machines. I was thinking of making a users table and machines table. Each has user_id. So my database would keep track of each machine the user chose.

I already have the layout pretty much done. I just need to know how to layout my tables in the database so I can start populating data dynamically.

Do I just make 1 table for users and ad like 40 different columns? Probably not.

So let's say a user has clicked 6 machines, each of those machines gets a value of 1. Then on the next page each machine with a value of 1 is displayed along with the picture and name associated with that machine. The number of sets will be set by the admin.

And ideas? I can clarify further if needed.

If you want a better idea of what I am doing, take a look at this picture:

This picture shows the page after the user has selected which machines he/she wants. You can see that 12 machines were chosen. So 12 different machines were chosen and they were all listed here.

The sets at the top were created by an admin and also reflect each machine shown at the bottom.

Everything is being populated from a database and I already have the layout done I just need a good recommendation for a table structure.

Try this
userMaster
| USERID |   NAME |
-------------------
|      1 |  Peter |
|      2 | George |

machineMaster
| MACHINEID |   MACHINENAME | NOOFSET | IMAGEURL |
--------------------------------------------------
|         1 |     treadmill |      10 | blahblah |
|         2 | leg extension |       5 |  xyz.jpg |
|         3 |           xyz |       6 |      asd |

planMaster
| PLANID | PLANNAME |
---------------------
|      1 |     xyaz |
|      2 |     bbbb |

planDetail
| PLANID | MACHINEID |
----------------------
|      1 |         1 |
|      1 |         3 |
|      2 |         1 |
|      2 |         2 |
|      2 |         3 |

userPlan
| USERID | PLANID |
-------------------
|      1 |      1 |
|      2 |      1 |
|      2 |      2 |

userPlanDetail
| USERID | MACHINEID | FLAG |
-----------------------------
|      1 |         1 |    1 |
|      2 |         2 |    1 |
|      2 |         3 |    1 |

Only 1 concern is that how you will manage if user selects a new plan. so for this you have 2 approaches

Delete the user existing plan from userPlan and userPlanDetail table

If you want to maintain history of user selected plan you can then you can a have a flag field like which I have used in the above table which denotes that this plan or machine is under current use of the user or not.

If you are following 2nd scenario then while inserting records in the userPlan and userPlanDetail table use INSERT ON DUPLICATE KEY UPDATE.
Other tables are quite simple i don't think so it needs any explanation, post if you have any doubts.
Hope this helps...
SQL FIDDLE


Can I use mysql/mariadb databases on different OS? If I will use mysql/mariadb on Windows and soon I'll decide to switch to Linux or vice versa, can I use databases created in one operating system in another one without any data-loss?

Thanks in advance!

Check out the manual here. However, if you're transferring between 32/64 bit, then you'll have to use mysqldump, see here which gives you a text-based dump of the SQL statements you'll have to run to install your database. The commands for mysqldump vary according to whether you're using MyISAM or InnoDB, so just be aware of that.

On Linux, there are more tools available than for Windows - personally I would recommend Linux - you have mydumper (an enhanced replacement for mysqldump) as well as backup methods which aren't available on Windows such as Xtrabackup from Percona (free Open Source hot backup programme for InnoDB) and LVM snapshots. There are many other useful tools in the MySQL ecosphere that ony work (or are far easier to use) on Linux than on Windows. I know that the primary Oracle/MySQL development environment is Unix based (I did read this somewhere, sorry can't remember), either Linux or Solaris.


Why the WHERE clause is not pushed down in the view's query? With Postgres 9.4, I'm doing the following query quite often:

SELECT DISTINCT ON(recipient) * FROM messages
LEFT JOIN identities ON messages.recipient = identities.name
WHERE timestamp BETWEEN timeA AND timeB
ORDER BY recipient, timestamp DESC;

So I decided to create a view:

CREATE VIEW myView AS SELECT DISTINCT ON(recipient) * FROM messages
LEFT JOIN identities ON messages.recipient = identities.name
ORDER BY recipient, timestamp DESC;

I just realized if I query my view like SELECT * FROM myView WHERE timestamp BETWEEN timeA AND timeB I get a significantly worse performance.

Doing EXPLAIN ANALYZE on both queries, I found out the reason is that the database in the second case brings up all the records, does the left join and then applies the WHERE clause. In other words, the WHERE clause is not pushed down into the view's query. I also tried to remove the ORDER BY from the view, but still the database performs the LEFT JOIN on full data rather on the filtered set.

What is the reason of this behavior? Is there a way I can get a comparable performance when using view?

This query:
SELECT DISTINCT ON(recipient) * FROM messages
LEFT JOIN identities ON messages.recipient = identities.name
WHERE messages.timestamp BETWEEN timeA AND timeB
ORDER BY recipient, timestamp DESC;

says:

For all messages between timeA and timeB, find the recipients and for every recipient, find one message (the latest in between timeA and timeB).

This query (which you would get if you use the view):
SELECT *
FROM
  ( SELECT DISTINCT ON(recipient) * FROM messages
    LEFT JOIN identities ON messages.recipient = identities.name
    ORDER BY recipient, timestamp DESC
  ) AS myView
WHERE timestamp BETWEEN timeA AND timeB;

says:

For all messages, find the recipients and for every recipient, find one message, (the latest in all times) and then, show only those messages that are between timeA and timeB.

As a result, the first query will show messages that are between time A and
B which the second query will not show (because there may be one or more messages for the same recipient, later than time B).
So, the queries are logically different and the condition cannot (and shouldn't) be pushed down for your view.
If you want to have parameters passed to your view, look the two answers (by @a_horse_with_no_name and @Erwin Brandstetter) in this question for how to use a set returning function: Pass In “WHERE” parameters to PostgreSQL View?


How can I force MySQL to IGNORE ALL indexes? I have read articles about FORCE index, but how can I force MySQL to IGNORE ALL indexes?
I tried SELECT * FROM tbl IGNORE INDEX(*), but I was not successful.
As for why I (and others) need to do this: For example, I needed to summarize referers statistics by tld like this:
SELECT
    count(*) as c,
    SUBSTRING
    (
        domain_name,
        LENGTH(domain_name) - LOCATE('.', REVERSE(domain_name)) + 2
    ) as tld
FROM `domains_import`
    IGNORE INDEX(domain_name)
GROUP BY tld
ORDER BY c desc
LIMIT 100

...but I always have to look at what indexes are defined or determine what index will be used via Explain. It would be very handy to simply write IGNORE INDEX ALL and simply not care.
Does anyone know the syntax or a hack? (Tens of lines via MySQL definition tables are really not a shortcut).
Added from chat discussion:
Bechmark:

no index = 148.5 secs

with index = 180 secs and still running with Sending data
The SSD array is so powerful, that you almost no care about data cache...

Definition for benchmark:
CREATE TABLE IF NOT EXISTS `domains_import` (
`domain_id` bigint(20) unsigned NOT NULL,
`domain_name` varchar(253) CHARACTER SET ascii COLLATE ascii_bin NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `domains_import`
ADD PRIMARY KEY (`domain_id`),
ADD UNIQUE KEY `domain_name` (`domain_name`);

ALTER TABLE `domains_import`
MODIFY `domain_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT;

InnoDB, the test with index (no USE INDEX() or similar) is still running, 250 secs, I just killed it.

It's absolutely not clear why you want this but you can use the hint USE INDEX () to tell the optimizer not to use any index. From MySQL docs: index hints

  It is syntactically valid to omit index_list for USE INDEX, which means “use no indexes.” Omitting index_list for FORCE INDEX or IGNORE INDEX is a syntax error.

Your query becomes:

SELECT count(*) AS c,
       substring_index(domain_name, '.', -1) AS tld
FROM domains_import
       USE INDEX ()        -- use no indexes
GROUP BY tld
ORDER BY c DESC
LIMIT 100 ;

Side note: the complex expression:

SUBSTRING(domain_name, LENGTH(domain_name) - LOCATE('.', REVERSE(domain_name)) + 2)

can be simplified from 4 function calls to 1:

SUBSTRING_INDEX(domain_name, '.', -1)


Implementing a one to one (1:1) relationship between Person and User Scenario description
I have this person table as super parent (or supertype),
id
firstname
lastname
email
telephone
...
...

and user table as a child (or subtype)
id
person_id (FK)
password
username
screenname
...
...

They must be in a one-to-one (1:1) relationship, because an user cannot be repeated twice, and so a particular email value in a given person row must not be repeated twice.
Then I have this message table which stores messages from anyone,
id
firstname
lastname
email
telephone
subject
content
...
...

but you can see that firstname, lastname, email, telephone are duplicated in the message table.
So, I am thinking to refer it to person table like this below,
id
person_id
subject
content
...

but then it does not seem right, as a person with the same email, name, etc. can send a message to me as many times as they want, so the details he/she provides can be repeated.
Questions

So, should I make message as a child of person the parent or they should be separate entities?

Or, are there any better suggestions to solve this problem?

I have a person table.

Fine (just changed the name of the PK column):

person
------
person_id    PK
firstname
lastname
email
telephone
...
...

  and a user table as a child. They must be 1:1 relationship, because an user cannot be repeated twice.

If the relationship is 1:1 (assuming that person is the supertpe and user is the subtype, you can handle this with having the same column both as Primary Key and Foreign Key to person:

user
----
person_id  PK  FK to person(person_id)
password
username
screenname
...
...

  and so the email in the person row must not be repeated twice.

Add a UNIQUE constraint in the person.email column.

  Then I have this message table which stores messages from anyone,
  but you can see that firstname,lastname, email,telephone are duplicated in message table.
  so I am thinking to refer it to person table like this below.

Fine (adjusted to previous changes). But you also need to store who sends a message and who is the receiver:

message
-------
message_id   PK
sender_id    FK  to user(person_id)         --- or to person(person_id)
receiver_id  FK  to user(person_id)         --- that depends on your requirements
subject
content
...


Stop MySQL generating repeated mysql-relay-bin-****.00000* files MySQL is generating repeated 1KB files with names like:
mysql-relay-bin-2020@002d12@002d06@002011@003a32@003a44@00200@0020@005bnote@005d@0020c@003a@005cxampp@005cmysql@005cbin@005cmysqld@002eexe@003a@0020ready@0020for@0020connections@002e@000d.000005
I'm using XAMPP on Windows and the files are being generated in C:\XAMPP\mysql\data where the database directories live. From what I've read mysql-relay-bin-* files are logs related to master slave or multi-master instances but I haven't knowingly set this up and I can't find any settings referring to it.
How can I debug this and find the cause?
It would be very helpful if I can do this through phpMyAdmin which I'm more familiar with.
show VARIABLES like '%log%'
Running this in phpMyAdmin on the localhost server produces the following output, is there anything here that indicates the cause?

aria_checkpoint_log_activity    1048576
aria_log_file_size  1073741824
aria_log_purge_type immediate
aria_sync_log_dir   NEWFILE
back_log    80
binlog_annotate_row_events  ON
binlog_cache_size   32768
binlog_checksum CRC32
binlog_commit_wait_count    0
binlog_commit_wait_usec 100000
binlog_direct_non_transactional_updates OFF
binlog_file_cache_size  16384
binlog_format   MIXED
binlog_optimize_thread_scheduling   ON
binlog_row_image    FULL
binlog_stmt_cache_size  32768
encrypt_binlog  OFF
expire_logs_days    0
general_log OFF
general_log_file    username.log
gtid_binlog_pos
gtid_binlog_state
innodb_encrypt_log  OFF
innodb_flush_log_at_timeout 1
innodb_flush_log_at_trx_commit  1
innodb_locks_unsafe_for_binlog  OFF
innodb_log_buffer_size  8388608
innodb_log_checksums    ON
innodb_log_compressed_pages ON
innodb_log_file_size    5242880
innodb_log_files_in_group   2
innodb_log_group_home_dir   C:\XAMPP\mysql\data
innodb_log_optimize_ddl ON
innodb_log_write_ahead_size 8192
innodb_max_undo_log_size    10485760
innodb_online_alter_log_max_size    134217728
innodb_scrub_log    OFF
innodb_scrub_log_speed  256
innodb_undo_log_truncate    OFF
innodb_undo_logs    128
log_bin OFF
log_bin_basename
log_bin_compress    OFF
log_bin_compress_min_len    256
log_bin_index
log_bin_trust_function_creators ON
log_disabled_statements sp
log_error   .\mysql_error.log
log_output  FILE
log_queries_not_using_indexes   OFF
log_slave_updates   OFF
log_slow_admin_statements   ON
log_slow_disabled_statements    sp
log_slow_filter admin,filesort,filesort_on_disk,filesort_priority_...
log_slow_rate_limit 1
log_slow_slave_statements   ON
log_slow_verbosity
log_tc_size 24576
log_warnings    2
max_binlog_cache_size   18446744073709547520
max_binlog_size 1073741824
max_binlog_stmt_cache_size  18446744073709547520
max_relay_log_size  1073741824
read_binlog_speed_limit 0
relay_log
relay_log_basename
relay_log_index
relay_log_info_file relay-log.info
relay_log_purge ON
relay_log_recovery  OFF
relay_log_space_limit   0
slow_query_log  OFF
slow_query_log_file username-slow.log
sql_log_bin ON
sql_log_off OFF
sync_binlog 0
sync_relay_log  10000
sync_relay_log_info 10000

If I run the same query directly on my database it shows the following log.

back_log    80
encrypt_binlog  OFF
general_log OFF
innodb_encrypt_log  OFF
innodb_locks_unsafe_for_binlog  OFF
innodb_scrub_log    OFF
relay_log
slow_query_log  OFF
sync_binlog 0
sync_relay_log  10000

SHOW VARIABLES LIKE '%log%';

It looks like the file name has a - that is really a "NON-BREAKING HYPHEN" instead of a regular minus sign.


How to create a Job for Merge Replication? I want to create a step in a Job for Merge Replication. I basically want to know which command should I write so that a particular Subscription starts synchronizing?

When you create your replication, it will automatically create a job with all of the steps needed for replication if you use the GUI.

Step 1: Merge Agent Startup Message. Type T-SQL ran on the distribution database

Step 2: Run Agent. Type Replication Merge. The command will be something like:
-Publisher [] -PublisherDB [] -Publication [] -Subscriber [] -SubscriberDB []   -Distributor [] -DistributorSecurityMode 1

Step 3: Detect nonlogged agent shutdown. Type T-SQL sp_MSDetect_nonlogged_shutdown @ subsystem='Merge', @agentid=


How to select duplicates into new columns? I am trying to select distinct data in new columns!

My Table

productID | imageURL
1         |    a1.jpg
1         |    a2.jpg
2         |    a3.jpg

Target Table

productID | imageURL1    | imageURL2    | imageURL3
1         |    a1.jpg    | a2.jpg       | null
2         |    a3.jpg    | null         | null

I only want 3 images even if I have 100 in my table.

Subqueries are the easiest solution for the problem, I think.

You can so a simple select or create a new table with CREATE TABLE ... FROM SELECT ...:

select product_id,
    (select image_url from images as images_1 where images_1.product_id = images_o.product_id limit 0, 1) as image_url_1,
    (select image_url from images as images_2 where images_2.product_id = images_o.product_id limit 1, 1) as image_url_2,
    (select image_url from images as images_3 where images_3.product_id = images_o.product_id limit 2, 1) as image_url_3
    from images as images_o
    group by product_id
    limit 10

Try it here: http://sqlfiddle.com/#!9/bc915d/1/0


Mysql update adding subquery field value to field I want to update a field adding it the value of a subquery field.

I'm trying this:

UPDATE table a
SET a.field=(a.field+b.subfield)
INNER JOIN (SELECT subid, subfield FROM subtable WHERE ..... ) AS b
ON a.id = b.subid
WHERE ...

but I'm getting the syntax error.

How is the correct syntax for this operation?

Please move the SET a.field=(a.field+b.subfield) after ON a.id = b.subid and before WHERE

So, the query should look like this:

UPDATE table a
INNER JOIN (SELECT subid, subfield FROM subtable WHERE ..... ) AS b
ON a.id = b.subid
SET a.field=(a.field+b.subfield)
WHERE ...

Give it a Try !!!


Incorrect string value: '\xC5\xABt\xC4\x97' I am facing database error Incorrect string value: '\xC5\xABt\xC4\x97'. I did a lot search and tries a few ways, include move from utf8 to utf8mb4. But still not able to solve it.

Following are the database chartset, collation, and table schema.

+--------------------------+--------------------+
| Variable_name            | Value              |
+--------------------------+--------------------+
| character_set_client     | utf8mb4            |
| character_set_connection | utf8mb4            |
| character_set_database   | utf8mb4            |
| character_set_filesystem | binary             |
| character_set_results    | utf8mb4            |
| character_set_server     | utf8mb4            |
| character_set_system     | utf8               |
| collation_connection     | utf8mb4_unicode_ci |
| collation_database       | utf8mb4_unicode_ci |
| collation_server         | utf8mb4_unicode_ci |
+--------------------------+--------------------+

| DELIVEREDEMAIL | CREATE TABLE `DELIVEREDEMAIL` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `RelatedModule` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
  `UserUUID` varchar(40) COLLATE utf8mb4_unicode_ci NOT NULL,
  `Username` varchar(1000) COLLATE utf8mb4_unicode_ci NOT NULL,
  `Email` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `EmailType` enum('NEW_PRECITION_EMAIL','ACTIVITY_EMAIL','LOG_IN_BASED_EMAIL','REPLAY_TO_PREDICT_EMAIL','NEW_TVSHOW_EMAIL','NEW_REWARD_EMAIL','TUNE_IN_EMAIL','EXPIRED_PREDICTION_EMAIL','NEW_FOLLOWER_EMAIL','UCP_UCT_CONFIRMATION_EMAIL') COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `EmailSendingFrequency` enum('DAILY','WEEKLY','BIWEEKLY','MONTHLY','ASAP','UNSUBSCRIBE','SUBSCRIBE') COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `SendTime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `DataInEmail` varchar(5000) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `Code` int(11) NOT NULL,
  `Message` varchar(500) COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`ID`),
  KEY `notification_senttime` (`SendTime`),
  KEY `notification_emailtype` (`EmailType`),
  KEY `notification_useruuid` (`UserUUID`)
) ENGINE=InnoDB AUTO_INCREMENT=3807922 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4 |

The error I got is when try to store username such as "Тхьабысым", I got "Incorrect string value: '\xC5\xABt\xC4\x97' for column 'p_Username' at row 4".

Anyone have some idea? Thanks in advance!

EDIT:
I tried to change charset to latin_1 and collation to latin1_swedish_ci, and it worked. Does it means "Тхьабысым" are latin characters but 4 byte UTF-8 as utf8mb4 still not able to represent these characters? What should I do with these kind of characters? Since this is username pulled from Facebook, there can be all kinds of characters.

Since nobody answer the question and I finally figured it out. I'll answer my own question and hope it may help someone later.

Data is being inserted by stored procedure, alter dababase, table charset and collations will not override charset and collation setting in stored procedure, nor the changes in my.cnf.

After all the database chartsets, collations are correct, drop and reinsert the stored procedure.
Before drop, the procedure was like this:

| MAILSERVER | sp_getDataForNewReward         | PROCEDURE | root@localhost | 2014-05-19 23:08:38 | 2014-05-19 23:08:38 | DEFINER       |         | utf8                 | utf8_general_ci      | latin1_swedish_ci  |

After drop and reinsert, it become this:

| MAILSERVER | sp_insertDeliveredEmail        | PROCEDURE | root@localhost | 2015-01-15 23:38:24 | 2015-01-15 23:38:24 | DEFINER       |         | utf8mb4              | utf8mb4_unicode_ci   | utf8mb4_unicode_ci |

Then the procedure will work properly. When stored procedure being created, it will get the charset and collation from the database setting, so after database setting, it need a "refresh".


Using CLR User Defined Functions in SQL Server I have used CLR user defined functions for very specific instances, such as complex string handling, but generally didn't like them from a maintenance point of view (yet another place to hide code).  What do people consider the pros and cons of this type of function, and are there any best practice guidelines as to when to use them, both from a performance and maintenance perspectives?

This is the classic argument, for or against stored procedures in general. The fact is, you can't stop people connecting to your database. Sure Joe in the warehouse will use the in-house stock control app that has all your business rules in, he's got a dumb terminal/web browser on the Intranet, he's got no choice. But if Bob the CFO wants to connect with Excel and do some pivot tables, are you going to say no, you can't do that, give me 6 months to write you a custom app in the application server tier? No. Or Jane SQL consultant who likes a command line client, and you're on vacation so you can't tell her no.

But you don't want to risk any integrity issues so put your validation and business rules somewhere there's no way round them, right up close with the data. Maybe T-SQL or C# isn't the prettiest language going - but SPs and triggers are the right tool for the job.

Other than that, managing code deployed in the database is actually no different to managing code deployed in an application server (e.g. J2EE, COM objects, whatever). It's a matter of just not treating it as anything "special", having proper discipline about versioning, building and releasing it, etc. How is it "hidden" at your site?


Unable to shrink files I am kind of new to SQL Server.

I am trying to shrink my MDF files, however I am unable to do it.

There is no error message.
Any idea why this is the case ?

Without more more information it might be hard to diagnose this - however, usually the only reason why a shrink wouldn't work (but completes, no errors and doesn't hang) is because there is no free space to release anywhere.

The only way to reduce the size on disk therefore is to delete data.

Run sp_spaceused to find out how much your database is actually taking up. If it's occupying all (or most) of the space on disk, then your shrink operation won't do anything - there's nothing to shrink.

NB (almost tempting to put this at the top, but I thought I'd answer the question first):

If this is on a dev environment, then you're probably OK to play around and try this out.

However, if you're doing this in production - don't.

It's a long quoted (for good reason) fact that shrinking a db is pretty much the worst thing you can do. Performance suffers for the sake of space that you could very well just use up again at some point - and disks are cheap.

Don't take my word for it Brent Ozar, Paul Randal, Stack Exchange Q, Other SE Q (there's plenty more examples saying the same thing).

Ps. Check out SQLServerCentral and Stack Exchange Q for more options of checking space used.


SQL Server sqlcmd setvar depending of selected database I'm using sqlcmd for a moment now to update database on our project.

Before we have use script like this:

:setvar relativepath D:\Project1\Sql
:r $(relativepath)\Tables\CreateTable1.sql
:r $(relativepath)\SP\Table1.sql

This was a good way to update database during developement and publish.

But now we are using different directory depending if we are in a dev branch or a production branch (Subversion).

We wish to avoid error, in case someone comit a script to a wrong directory by setting the path depending of the selected database name. Some thing like this:

:setvar relativepath "D:\Project1\Prod\Sql"
if (DB_name() = 'Project1' OR DB_name() = 'Project1_Dev')
begin
    :setvar relativepath "D:\Project1\Dev\Sql"
end

I can't find a way to do it. Some one have an idea?

[Update] Solution : (Tanks to wBob)

I have a script named : setRelativePath.sql

:out D:\Project1\SQL\Temp\temp.sql
go

declare @bdName varchar(15) = DB_name()
--Here the print got saved to D:\Project1\SQL\Temp\temp.sql
if @bdName = 'Project1' OR @bdName = 'Project1_DEV'
    print ':setvar relativepath D:\Project1\DEV\Sql'
else if @bdName = 'Project1_PROD'
    print ':setvar relativepath D:\Project1\PROD\Sql'
else
    raiserror( 'Unknown value (%s) for sqlcmd variable relativepath.', 16, 1, @bdName )

go
:out stdout
go

!!REM Read the file to execute the setvar statement
:r D:\Project1\SQL\Temp\temp.sql
go

--Here the print is displayed then we execute the script
print 'Relative path set to $(relativepath)'
go

then i use it in my scripts files:

:r D:\Project1\SQL\setRelativePath.sql
:r $(relativepath)\SP\SomeStoredProcedure.sql

D:\Project1\SQL\ is in the trunk .

the relativepath varriable can be used inside of SomeStoredProcedure.sql to.

This script ensure we do not aply a dev script to prod and remove a step when we merge dev to prod.

You cannot mix sqlcmd mode commands with T-SQL conditional logic.  There is a way to do this, but as to whether it is advisable is another matter.

This technique involves redirecting stdout, writing out to a file and executing that file all under sqlcmd mode to get the effect you require:

:out d:\temp\temp.sql
go

declare @x varchar(5) = 'dev'

if @x = 'dev'
    print ':setvar testVar dev'
else if @x = 'prod'
    print ':setvar testVar prod'
else raiserror( 'Unknown value (%s) for sqlcmd variable testVar.', 16, 1, @x )
go
:out stdout
go

!!REM Read the file to execute the setvar statement
:r d:\temp\temp.sql
go

select '$(testVar)' env

print 'it looks like this is the $(testvar) environment after all'
go

Obviously the process running this will require access to a scratch directory and file, and there are associated permissions (and risks) with this method.  The placement of GOs is important when redirecting output.

As regards usage, I would not particularly advise it.  We experimented with this (hence I know this technique) but moved away from it in favour of either database projects (which handle all this for you) or re-runnable (idempotent) scripts.  Rather than have a load of conditional logic in parent scripts, each object script was built to be re-runnable using 'if not exists' checks, for example:

if not exists ( the table )
  create the table

if not exists ( a column )
  add the column

Then it didn't matter if we were running in dev or prod, or how many times we ran the script.  These can get a bit unwieldy after a time.


Nested with - Incorrect syntax near the keyword 'WITH' With inside with

This is ok

    WITH CS AS (
      SELECT      'ab' as a
    )
    SELECT * from cs

http://data.stackexchange.com/mathematics/revision/138658/170321/comment-partition-by-question

But, as soon as I wrap it with a second with,

with PS as (
        WITH CS AS (
          SELECT      'ab' as a
        )
        SELECT * from cs
        ) select * from PS

I get this error,

Incorrect syntax near the keyword 'WITH'. Incorrect syntax near the keyword 'with'.  If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon. Incorrect syntax near ')'.

With inside select

The following is ok

select ab.a from (
  select a from (SELECT 'ab' as a) b
            ) ab

This is also runs

  with cs as (SELECT 'ab' as a)
  select a from cs

But, wrapping expression with another select, as this one proposes,

  select a from (
    with cs as (SELECT 'ab' as a)
    select a from cs
  ) ab

fails again with the same error.

I do it in data.stackexchange and they report that they use MSSQL

From documentation

  Specifying more than one WITH clause in a CTE is not allowed. For
  example, if a CTE_query_definition contains a subquery, that subquery
  cannot contain a nested WITH clause that defines another CTE.

Thus,

with PS as (
    WITH CS AS (
      SELECT      'ab' as a
    )
    SELECT * from cs
    ) select * from PS

gives you error. You can define multiple CTEs though :

WITH CS AS (SELECT 'ab' as a),
PS as (SELECT * from cs)
select * from PS


How to use GROUP BY on a CLOB Column i'm trying to use this QUERY which contains a Clob Column (flow.IDFONCTIONNEL)
SELECT
flow.flowid,
min(flow.CONTEXTTIMESTAMP) contextTime,
flow.STATUT,
flow.IDFONCTIONNEL,
flow.ETAT
FROM Flux flow
WHERE  flow.FLOWCODE = 'HELLO'
AND flow.CONTEXTTIMESTAMP BETWEEN '06/01/20 11:36:21,566000000' AND '06/07/20 11:36:21,566000000'
GROUP BY flow.flowid, flow.STATUT , flow.ETAT, flow.IDFONCTIONNEL
ORDER BY contextTime desc

When I run this query, I get the error
ORA-00932: inconsistent data types Expected got CLOB
This is because the column flow.IDFONCTIONNEL is a CLOB data type. If I comment this column from select clause it works fine but I need this column in the output.
I have seen a post telling to try to use DBMS_LOB.SUBSTR to try avoid this problem, so i tryed :
SELECT
flow.flowid,
min(flow.CONTEXTTIMESTAMP) contextTime,
flow.STATUT,
DBMS_LOB.SUBSTR(flow.IDFONCTIONNEL,4000,1) as idf1,
DBMS_LOB.SUBSTR(flow.IDFONCTIONNEL,8000,4001) as idf2,
flow.ETAT
FROM Flux flow
WHERE  flow.FLOWCODE = 'HELLO'
AND flow.CONTEXTTIMESTAMP BETWEEN '06/01/20 11:36:21,566000000' AND '06/07/20 11:36:21,566000000'
GROUP BY flow.flowid, flow.STATUT,flow.ETAT, idf1 ,idf2
ORDER BY contextTime desc

But i get ORA-00904: "IDF2" invalid identifier.
Anyone as an idea of why it is not working ?
Thanks a lot

GROUP BY can not use aliases defined in the same query block.
You can use this:
SELECT
flow.flowid,
min(flow.CONTEXTTIMESTAMP) contextTime,
flow.STATUT,
DBMS_LOB.SUBSTR(flow.IDFONCTIONNEL,4000,1) as idf1,
DBMS_LOB.SUBSTR(flow.IDFONCTIONNEL,8000,4001) as idf2,
flow.ETAT
FROM Flux flow
WHERE  flow.FLOWCODE = 'HELLO'
AND flow.CONTEXTTIMESTAMP BETWEEN '06/01/20 11:36:21,566000000' AND '06/07/20 11:36:21,566000000'
GROUP BY flow.flowid, flow.STATUT,flow.ETAT, DBMS_LOB.SUBSTR(flow.IDFONCTIONNEL,4000,1),
DBMS_LOB.SUBSTR(flow.IDFONCTIONNEL,8000,4001)
ORDER BY contextTime desc

Or something like this:
SELECT
flowid,
min(CONTEXTTIMESTAMP) contextTime,
STATUT,
idf1,
idf2,
ETAT
from
(
SELECT
flow.flowid,
flow.CONTEXTTIMESTAMP,
flow.STATUT,
DBMS_LOB.SUBSTR(flow.IDFONCTIONNEL,4000,1) as idf1,
DBMS_LOB.SUBSTR(flow.IDFONCTIONNEL,8000,4001) as idf2,
flow.ETAT
FROM Flux flow
WHERE  flow.FLOWCODE = 'HELLO'
AND flow.CONTEXTTIMESTAMP BETWEEN '06/01/20 11:36:21,566000000' AND '06/07/20 11:36:21,566000000'
)
GROUP BY flowid, STATUT,ETAT, idf1 ,idf2
ORDER BY contextTime desc


Working out validity of table relationship Question Background:
In a quest to improve my skill set with TSQL programming I'm designing a 'simple' database schema for a zoo.
In all I currently have 4 tables but am having an issue working out the relationship) between the zoo itself and its employees (the zoo keepers in this case).
Schema:

Is this a valid schema and relationship? Should there even be a relationship between these two tables Or should they be completely separate?

Though a one-to-many relationship exists between the zoo and the keepers, it's not a terribly useful relationship to model, since every single keeper works at a single zoo. This is probably why you are having an issue. Your Zoo table is not really set up to relate to anything unless you have more than one zoo. You could add a Zoo_ID field to both the Zoo and Keepers table. But, with one zoo, what's the point?

For more than one zoo, a one-to-many relationship must be explicitly laid out using keys in order for you to track which zoo a keeper works for. Each zoo gets a unique Zoo_ID (primary key), and each keeper has a Zoo_ID field that matches up with one of the Zoo_Ids (foreign key).

It gets trickier if your keepers can work at multiple zoos. Now you have a many-to-many relationship. You could have multiple rows in your keeper table, one per zoo, but that's kind of tacky and duplicates information. The proper way to represent the relationship is a junction (join) table with the id of the keeper and the id of the zoo. If you imagine a diagram with each zoo and keeper drawn out, and lines connecting the keepers with the zoos, each row in your junction table is like one of those lines, with the zoo's id on one end, and the keeper's id on the other.

tl;dr: Yes, it's a valid relationship. No, you shouldn't bother modeling it, unless you plan on multiple zoos in the future.


Does switching from INT to BIGINT entails significant downtime? Hypothetically, if your INT column already has 4 billion rows, how long is the process of converting that column to BIGINT? I know this will depend on server resources but I just want to have a general idea, say server has 8GB RAM 4CPU.
If you are certain you will hit INT's limit at some point, although years away, does it make sense to go straight to BIGINT?

It depends on the size of the table and the speed of the disk.  Maybe an hour, maybe days.  Check out pt-online-schema-change; it may be able to avoid most of the downtime.
How often does a schema survive intact for more than a couple of years?  Sure, do the math to figure out how soon the id will overflow.  If there is even a small risk, go with a bigger size.

If you are talking about AUTO_INCREMENT, then be away that there are a number of ways in which code could skip numbers -- INSERT IGNORE, REPLACE, etc.  Also, if you are involved with Master-Master or cluster replication.

If you already have replication set up, there may be a technique (using failover) to avoid more than a little downtime.


Is there a type of database that can directly recall an element given a pointer in O(1) time? I'm currently using levelDb, which is a key->value store database. The only data being inserted are small binary blobs (that represent nodes for a large merkle tree). When I put a value in the db, I don't really care what the key is, just that I have some key to use as a reference (from its parent nodes which are written later).

As you can see the key->value store does the job but since I don't need control over key creation, I believe it is unnecessarily slow. In order for a key->value database to lookup a key, it probably uses a b-tree. That means that finding a single value is doing O(log n) disk reads.

For my use case it should be possible to get closer to O(1) reads/writes where I put new data elements to the database and it returns something like a direct address. I can use this address as my reference.

(This can already be done in the filesystem by calculating offsets, but I need a "real" transactional ACID database)

Does such a thing exist? I have tried searching but don't know what to call it other than "pointer based database".

I do not mean to imply that I don't require a delete feature. However, this should also be possible using the same address pointer reference. I can also adhere to the additional constraint that each data item be of the same size (I do not know the fundamentals of how db deallocation is handled, but it seems like having fixed length items might help so that new items can rewrite over deleted items in a compact way).

Most RDBMS (with transaction support) are general purpose and picks a tradeoff between reads, writes, deletes operations and storage (memory/disk) requirements. Your requirement is very specific to your use-case. I don't think you will find any database optimized for it.

Basically you are looking for some kind of array lookup. Otherwise it will not be O(1). Do you really have only a put and a get operation. No deletes ?


Why do MySQL tables crash? How do I prevent it? I'm the Administrator of a Moodle site which had its Users table corrupted and it became unusable.

Luckily, a simple REPAIR TABLE mdl_user made it work back again. The thing is that I don't know why it actually crashed and made it unusable, and I want to make sure that I'm better prepared next time.

I'm not a thoroughly experienced DBA — I'm just a developer who does a lot of stuff, so please bear with me.

I could just restore a backup, but I guess there are ways to prevent crashes.

Those tables are utf8_general_ci and using MyISAM.

Why does a MySQL table crash? What can I do to prevent that from happening?

It is rather easy for a MyISAM table to crash.

In the header of every MyISAM table is a counter that tracks how many open file handles there are against the table.

If you start up mysql and the number in the header does not match the number of actual file handles against, mysqld treates the table as crashed.

If a simple REPAIR TABLE mdl_user makes it work again every time without loss of data, this may indicate that you have a very highly trafficked site that writes to mdl_user.

If dozens of tables require this REPAIR TABLE, I would convert all the tables to InnoDB. However, if the mdl_user table is the only table with this problem, there is something you can do (for this example, let say the database is moodle);

If you want all tables left as MyISAM

STEP 01 : Create a Repair Table Script

echo "REPAIR TABLE moodle.mdl_user;" > /var/lib/mysql/MoodleStartUp.sql

STEP 02 : Declare the Repair Script as the startup file

Add this to /etc/my.cnf

[mysqld]
init-file=/var/lib/mysql/MoodleStartUp.sql

STEP 03 : Restart mysql

Every restart of mysql will trigger the Repair Table Script

If you want all tables to become InnoDB

Run this code to make a bulk conversion script of MyISAM tables to InnoDB, and view it

MYSQL_USER=root
MYSQL_PASS=password
MYSQL_CONN="-u${MYSQL_USER} -p ${MYSQL_PASS}"
echo "SET SQL_LOG_BIN = 0;" > /root/ConvertMyISAMToInnoDB.sql
mysql ${MYSQL_CONN} -A --skip-column-names -e"SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ENGINE=InnoDB;') InnoDBConversionSQL FROM information_schema.tables WHERE engine='MyISAM' AND table_schema NOT IN ('information_schema','mysql','performance_schema') ORDER BY (data_length+index_length)" > /root/ConvertMyISAMToInnoDB.sql
less /root/ConvertMyISAMToInnoDB.sql

Once you are satisfied with the conversion script's contents, then run it

mysql ${MYSQL_CONN} < /root/ConvertMyISAMToInnoDB.sql

UPDATE 2012-03-15 14:00 EDT

@Kevin, While using MyISAM, what if running out of Disk Space is your isuue?

Here is something to consider: According to MySQL 5.0 Certification Study Guide,

bulletpoint #11 says the following on Pages 408,409 Section 29.2:

  If you run out of disk space while adding rows to a MyISAM table, no
  error occurs. The server suspends the operation until space becomes
  available, and then completes the operation.

When you run out of disk space, do not just shutdown or kill mysql. The count of the open file handles in any currently used MyISAM will not have been cleared. Thus, the MyISAM table is marked crashed. If you can free up disk space in the data volume with mysqld still running, mysqld will soldier on once disk space is made available.


Can I combine the results from multiple columns into a single column without UNION? I have a table with several columns which I want to SELECT:

SELECT his_name , her_name, other_name FROM foo;

Bu, I instead I want to combine the results all into a single column.
As an example, I can do this with UNION ALL as

SELECT her_name AS name FROM foo
UNION ALL
SELECT his_name AS name FROM foo
UNION ALL
SELECT other_name AS name FROM foo

Is there more elegant way to do this operation?

It is unclear to me what is a "more elegant way".

Oracle you can use the following statement to make columns to rows

select all_name
from foo
unpivot (all_name for col_name in (
  his_name,
  her_name,
  other_name));

This is the syntax diagram of the select statement

SELECT
    [ALL | DISTINCT | DISTINCTROW ]
      [HIGH_PRIORITY]
      [STRAIGHT_JOIN]
      [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
      [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
    select_expr [, select_expr ...]
    [FROM table_references
      [PARTITION partition_list]
    [WHERE where_condition]
    [GROUP BY {col_name | expr | position}
      [ASC | DESC], ... [WITH ROLLUP]]
    [HAVING where_condition]
    [ORDER BY {col_name | expr | position}
      [ASC | DESC], ...]
    [LIMIT {[offset,] row_count | row_count OFFSET offset}]
    [PROCEDURE procedure_name(argument_list)]
    [INTO OUTFILE 'file_name'
        [CHARACTER SET charset_name]
        export_options
      | INTO DUMPFILE 'file_name'
      | INTO var_name [, var_name]]
    [FOR UPDATE | LOCK IN SHARE MODE]]

Neither the WHERE,GROUP BY, HAVING, LIMIT, SELECT, INTO, FOR UPDATE nor the LOCK IN SHARE MODE clause can increase the number of rows defined by the FROM clause.
So if table_references equals foo the  query cannot contains more rows than the table foo.
So MySQL does not have such an "elegant" way to unpivot a table.

A way to do such unpivoting without the use of UNION can be done buy using a join. We want to create 3 rows for each row of the foo table, so we create an auxiliary table containing three rows
and (cross) join it to the foo table. Now we have three rows in our query for each row in the base table foo. Each query row can be filled by the appropriate data. Instead the ELT function one can use IF or CASE.

SQL Fiddle

MySQL 5.6 Schema Setup:

create table foo (
  his_name varchar(10),
  her_name varchar(10),
  other_name varchar(10));

insert into foo(his_name,her_name,other_name) values ('one','two','three');
insert into foo(his_name,her_name,other_name) values ('four','five','six');

create  table aux(line int);
insert into aux(line) values(1);
insert into aux(line) values(2);
insert into aux(line) values(3);

Pivot Query:

select elt(aux.line,foo.his_name,foo.her_name,foo.other_name) all_name
from foo  cross join  aux

Results:

| all_name |
|----------|
|      one |
|     four |
|      two |
|     five |
|    three |
|      six |

Of course there are different ways to create a table containing the three rows  with values 1,2,3:

SQL Fiddle

Using an auxiliary table:

create  table aux(line int);
insert into aux(line) values(1);
insert into aux(line) values(2);
insert into aux(line) values(3);

Using an auxiliary table:

select line
from aux

using a constant expression:

select 1 line
union all
select 2
union all
select 3

counting row numbers:
I found it here

SELECT
 @rownum := @rownum + 1 line
 FROM
(SELECT @rownum := 0) r, INFORMATION_SCHEMA.COLUMNS t
where @rownum<3

using one of the dictionary views:

SELECT
 ordinal_position  line
 from INFORMATION_SCHEMA.COLUMNS t
where table_catalog='def'
and table_schema='information_schema'
and table_name='COLUMNS'
and ordinal_position between 1 and 3

Results:

| ORDINAL_POSITION |
|------------------|
|                1 |
|                2 |
|                3 |


Two SQL Server with one TCP port I used SQL Server configuration manager to enable TCP/IP on port 1433. I didn't have any problem but when I tried to enable another SQL service (different one) to listen on the same port I couldn't run it. When I stopped the first service there wasn't any problem. I also can run them together when only one of them has TCP enabled.

My question is why I can't run the both services together? I guess it has something to do with port but I thought that they just listen on this port so there shouldn't be problem.

With TCP, you can only have one process listening on each IP/port pair.

This applies to SQL Server.  The first instance you start binds itself to listen on your_ip:1433, then when you start the second, the OS will not allocate the socket to the the new instance of SQL Server, because it has already allocated that listener socket to your first SQL Server instance.  If you look in your error logs, you will see a "socket already in use" error of some sort when you start up the second instance.

If you have multiple physical or logical NICs you should be able to bind the SQL Server instances to the same port on different IP addresses.


SQL Native Client Upgrade Application server for CRM has SQL native client 2008 version installed. I have installed SQL native client 2012 side by side as need to connect to an sql availability group listener. How do I get the application server to use the new client version? Oledb connection
Thanks

The OLE DB provider is usually specified via a connection string for database applications. Connection strings are usually stored in an external application configuration file so that the driver and other attributes (server, database, etc.) can be changed without code changes. Below is are SQL Server 2008 and 2012 Native Client examples, with SQLNCLI11 being the 2012 version:
Provider=SQLNCLI10;Integrated Security=SSPI;Initial Catalog=YourDatabase;Data Source=YourServer

Provider=SQLNCLI11;Integrated Security=SSPI;Initial Catalog=YourDatabase;Data Source=YourServer

Applications can also configure connection string properties programmatically or even have values hard-coded. Consult your CRM vendor documentation to determine specifics of configuration file locations or other configuration means, and importantly, verify the software supports SQL Server 2012 native client.


Does ORDER BY COUNT(*) DESC suffer a penalty? I understand that older versions of MySQL parse the following but has no effect:

INDEX (col DESC)

For the following example, does performance suffer and is there a way to overcome it?

  SELECT col, COUNT(*)
    FROM tbl
GROUP BY col
ORDER BY COUNT(*) DESC

The optimizer might build a hash table -- key == values of col, value = count.  Or it might extract all the col values.  But in either case, it will have to do some kind of sort at some point.

When everything fits in RAM, the cost of the sort is minimal.  As a rule of thumb, when the GROUP BY and the ORDER BY are different, then there will be at least one sort, sometimes two.

The only optimization you can do for that query is INDEX(col).  Then, at least, it is working with the index BTree, and the values of col are known to be sorted.  Hence it walks through the index counting up the counts.  Still there is a sort at the end.

As for whether DESC costs anything versus ASC, yes and no.  In the B+Trees that InnoDB uses, it is slightly more efficient to walk forward (ASC) than backward (DESC).  But that is not relevant here.  The sort for the ORDER BY does not use a BTree if it is in RAM -- Instead it is a Quicksort (or something).

Or is this what you are asking?

  SELECT col, COUNT(*)
    FROM tbl
GROUP BY col
ORDER BY COUNT(*) DESC

versus

  SELECT col, COUNT(*)
    FROM tbl
GROUP BY col
-- no ORDER BY

There definitely is an extra sort.  Without the ORDER BY (ASC or DESC), you will probably get the output sorted by col.  In fact, until a recent version, you were guaranteed to get it sorted by the GROUP BY.  This was an extension to the standard, that has been removed.


postgres user cannot access location of new data directory I moved my postgresql 9.3 data directory to a different drive, however, my postgres user cannot seem to access that new drive despite my best efforts. I am running on ubuntu 14.04 LTS x64. So I created a new directory under /media/$USER/kauthama/data/psql and set postgres:postgres as the owner. However when I try and do initdb the directory keeps saying permission denied. Even when I do su postgres and then try to navidate to the new directory, the autocompletion stops at /media/$USER/ and cannot find the drive kauthama. I even added postgres to the sudoers group to see if that would work. The permissions on kauthama are basically $USER is the owner and the sudo group owns it. So the postgres user should have all the permissions needed to navigate to the directory. Any thought on why this is not working?

I figured it out. So the permissions at one level of the directory hierarchy did not have any setting for letting the other group read that level. Thus that blocked the postgres user from being able to see a drive mounted by any other user. Once that was changed with

chmod -R 777 /media/$USER

Then this created permissions for the other group to see the directory. This fixed the issue.


How to design a relationship database table to store friendship relationship? I want to design a table to store friendship relationship in my web project
It should satisfy at least the following 4 conditions:

who send the add-friend request   e.g.(if A TO B then this column will be A)
who receive the add-friend request e.g.(if A TO B then this column will be B)
current status e.g.(0 denotes rejected whereas 1 denotes accepted or 2 denotes unprocessed
our friend relationship is bilateral

If any of you are experienced with this , any suggestion is welcomed
my current design (I think bad right now) is like this these are the columns
frienshipId
fromUserId
toUserId
status
requestTime

I'd create a table a lot like the one that you have.  I'm using SQL Server data types and syntax, you may need to tweak depending on your platform.

CREATE TABLE FriendStatus
(FriendStatusId BIGINT PRIMARY KEY IDENTITY(1,1),
FromUserId BIGINT,
ToUserId BIGINT,
StatusId TINYINT,
SentTime DATETIME2,
ResponseTime DATETIME2);

Indexing of the table will be critical as the table grows to the tens and hundreds of millions.


The ALTER TABLE statement conflicted with the FOREIGN KEY constrain I had a specific relational database with different tables. what I wanted to do is actually to copy two tables from different DB located on different server to my database using export feature in Microsoft SQL Server Management Studio 2017. The new tables have been imported to my current database. However, the problem is that the tables that have been exported from the other database into mine don't have primary and foreign keys. So what I did, I deleted the old tables in my current database and now working on adding the primary and foreign keys to the freshly copied one.

The issue is that I was able to create the primary keys without any problems however, when I tried to create the foreign key to the second table to link it to the first table I Got this error:

  The ALTER TABLE statement conflicted with the FOREIGN KEY constrain

I know the reason of the error that I have unmatched number of items in my first and second tables. I ran this query:

select UUT_RESULT from STEP_RESULT
WHERE UUT_RESULT NOT IN
(SELECT ID from UUT_RESULT)

I was able to see that there is 883 entries in my second table doesn't have match in the first table.

How can I solve this problem? I can't clean the tables because they have 15 million entries so is there a way that I can delete these 883 from my second table and then I can match the two tables?

Try this:

BEGIN TRANSACTION

DELETE a
FROM STEP_RESULT a
WHERE NOT EXISTS (
        SELECT *
        FROM UUT_RESULT
        WHERE ID = a.UUT_RESULT
        )
--commit    --verify rowcount before commit


MySQL unique static ID I need to get an unique static ID that is not changeable by the users, and if the user copies the database to another server this static ID is different.
Background
My company wants to sell our app and allow for the end user to control the administration of the database.  At the same time we do not want someone to pay for one license and then install the app on 100 different database (as an extreme example).
I thought server_id was is needed. Then, I found out that it can be configured by the end user. Therefore, this is no good.
thanks for the help!

Perhaps you can try server_uuid

Other have addressed using it

Beware of MySQL 5.6 server UUID when cloning slaves
Resolving Error: master and slave have equal MySQL server UUIDs

Some may resort to copying the auto.cnf to another server, which would then have the same generated server_uuid. You would have to code something that would scan a network on port 3306, sniff out the auto.cnf file, and check the UUID inside the file.

You could probably make the following requirements and coalesce them

Require the presence of the auto.cnf
Use Unique Identifier from Linux and imbed it yourself into auto.cnf as server_uuid
Match the UUID in auto.cnf with the machine as well as mysqld
Somehow broadcast that UUID so that your app can detect the presence of more than one occurrence of that UUID in the network

Doing these things may require taking the UUID your app generated and writing that UUID back into the installation software. That way, subsequent installations could compare the UUID of the machine with the now updated UUID from the first installation. Any match would have to deny installation thereafter.


Connecting to SQL Express Server 2017 with SQL Server Authentication Remotely I have two systems that are on the same network. I am able to ping back and forth between these systems with their IP address and with their system names. I have enabled the TCP port 1433 inbound and outbound on both systems. I have enabled TCP in the SQL Server Configuration Manager.

I have created an SQL Server Authentication and have the server on mixed login.

I am able to connect to the server from the system it is running on by using the following:

Server name: DESKTOP\SQLEXPRESS
Authentication: SQL Server Authentication
Login: admin

However, if I change the Server name to tcp:DESKTOP,1433\SQLEXPRESS I am unable to connect. If I change the Authentication mode back to Windows I am able to connect using the above server name.

This is my first time using SQL and I am very confused why I am having this issue. I would like to be able to connect to this server through TCP so that I may connect to it remotely with another system on the same network. Any information or links would be greatly appreciated.

Typically, a default instance will use port 1433, and a named instance will use an arbitrary port in the 49152 to 65535 range. Because SQL Express uses a named instance (even if it is the first and only instance on a server), it will grab an arbitrary high port. So make sure that TCP port 50207 is allowed from your second machine to Desktop. And also open UDP 1434, in order for the SQL Browser service to do its job.