Analyze MySQL what needs to be archived I'm not a DBA, I'm a DevOps guy trying to manage a MySQL database which is ever growing and desperately needs some archiving. My question is not how to archive. I think I have a pretty good idea and have SO for anything I might not know yet.
I'm trying to figure out "what" to archive. I don't have a clue what our apps need, or how often they access the records. I can tell you our DB has 410 tables in it. Our largest tables has 4 million records in it. I don't have the time to go through each and every one of those, ask how much they get used, or accessed, etc. I'm trying to trim down our development environments database size. It's slow to refresh them, and our RDS bill is massive. I know for certain our development environments do not need all this data. We also run a sanitizing script which rips out PII and other real world sensitive information. I don't think it is necessary to sanitize 8+ years or possibly older of records just for a development environment database.
Goal: Have a smaller developer environment database to A) trim down bill B) reduce time for data refresh.
Question: What are common things you look at when you are making decisions on what to archive, and how much to archive? Are there tools that help you identify good candidates? Are there common queries that you run to help you make this decision?

Typically this kind of decisions are taken with product managers. They are the ones who can say things like "customers should be able to read 2 years old information, but not necessarily older than that".
MySQL (or any other DBMS, as far as I know) doesn't provide a way to find out when a row was last read.
But there are ways to find out related information. This won't solve your problem, but hopefully you'll find useful indications.

Some tables have a column with the timestamp of the last update. Usually the default value is CURRENT_TIMESTAMP, but check: if there is no default value, I wouldn't consider the column reliable. You can't be sure that the application always updates the column.
You can temporarily add triggers AFTER UPDATE and AFTER DELETE. Triggers can write the value of the primary key into a separate table, using INSERT IGNORE. Keep them going for some time, and then check the oldest row that was updated/deleted. Writing such triggers manually is impossible, but this can easily be automated.
If you use Percona Server or MariaDB, you can enable the user_statistics plugin. It tells you how many times each table and index is read/written. If you're lucky, you'll find out that some tables are never read and can be archived. Even if not, the number of read rows per hour could possibly give you a hint (or not... this is highly dependent on your database logic, so I can't give more advice on this).
If you suspect that a portion of rows is never read, try a trick: read them with a SELECT three times. If the first query was sensibly slower than the second and third, those rows were not cached. Repeat the experiment at different days. It's a very empirical and unreliable test, use it as a hint and nothing more.
If the slow log is enabled, it logs the queries that take more than long_query_time seconds. You can set long_query_time=0 to log all the queries (this is a good practice for many reasons). You can then grep all queries that mention a certain table. You can make a script to run them all, and remember the oldest read row. Careful, you'll need to run SELECTs but not INSERT, UPDATE, etc.


Oracle Data Pump imported table, some columns not selectable Imported a table into Oracle 12g using data pump. Some of the columns, though present in the table as shown by describe, cannot be selected but they show up in select *.

Cannot select them individually  or apply where clause on only these columns.

Let me guess: when you describe the table, you see lowercase letters in column names, meaning the column name is case-sensitive, but you tried to select it without double quotes, in a case-insensitive manner.

Try quotes as in the below example:

SQL> create table t1(column1 number, "column2" number);

Table created.

SQL> insert into t1 values (1, 1);

1 row created.

SQL> commit;

Commit complete.

SQL> desc t1
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 COLUMN1                                            NUMBER
 column2                                            NUMBER

SQL> select COLUMN1, column2 from t1;
select COLUMN1, column2 from t1
                *
ERROR at line 1:
ORA-00904: "COLUMN2": invalid identifier

SQL> select * from t1;

   COLUMN1    column2
---------- ----------
         1          1

SQL> select COLUMN1, "column2" from t1;

   COLUMN1    column2
---------- ----------
         1          1


Update column value divisible by 100 Hello experts I need to update next column value with divisible by 100

You can use the Modulo operator, something along these lines:

UPDATE YourTable
SET NewBalance = Balance - (Balance % 100);

HTH


Selecting the right data type for text In my database, I have columns that should contain text pieces.

What is the best data type used for texts in SQL?
How can I show that text as a numbered or bullet list?
Can I search for a certain word or sentence in that text?
Can I make some words in the text piece as a hyperlinks to refers to another columns content?

Example about the Augmentin medicine

Augmentin is a penicillin antibiotic.
Augmentin contains a combination of amoxicillin and clavulanate  potassium.
Amoxicillin is an antibiotic in a group of drugs called penicillins.
Clavulanate potassium is a form of clavulanic acid, which is similar  to penicillin.
Augmentin is used to treat many different infections caused by bacteria, such as sinusitis, pneumonia, ear infections, bronchitis, urinary tract infections, and infections of the skin.

Data type - VARCHAR for ANSI which uses 1 byte per character or NVARCHAR for UNICODE which uses 2 bytes per character. In your case, I would suggest NVARCHAR, so you can store multiple languages within the same columns. Avoid TEXT and NTEXT as they are deprecated.

Searching - It is possible to use LIKE predicate, for simple searching with Wild Cards, but I suggest you investigate the use of Full Text indexing which will increase query performance and also allow you to use the FULLTEXT predicate for very flexible fuzzy searching.

Bullets and hyperlinks - I think you are thinking of SQL in the wrong way here. I think you should be thinking of those tasks within your client application. To facilitate this from the back end, hyperlinks I would split into a separate table with a key that you can then report on. For bullets, again strongly suggest controlling this through the client side, but if you really wanted to, you could insert a U+2022 at the beginning of each row. If there are multiple bullets per row of data, you could store this char at the beginning of each bullet and then use a string splitting function when reading from the table. Many ways of accomplishing this but suggest using XML sub query.


How to set current sequence while converting primary key serial(integer) to bigserial(bigint) The table already has few thousands of rows and I want to convert the primary key from Integer to BigInteger. How to change the sequence for biginteger?

--Create new temp sequence (sequence-name1) for bigint
CREATE SEQUENCE public.case_audit_case_audit_uid_seq1
    INCREMENT 1
    START 5542
    MINVALUE 1
    MAXVALUE 9223372036854775807
    CACHE 1;
ALTER SEQUENCE public.case_audit_case_audit_uid_seq1  OWNER TO postgres;
--Alter column to bigint
ALTER TABLE public.case_audit ALTER COLUMN case_audit_uid TYPE bigint;
--Alter pk column with temp Sequence
ALTER TABLE public.case_audit ALTER COLUMN case_audit_uid SET DEFAULT     nextval('indsolv.case_audit_case_audit_uid_seq1'::regclass);
--Drop original sequence
DROP SEQUENCE public.case_audit_case_audit_uid_seq;
--Create new sequence for bigint with original sequence name
CREATE SEQUENCE public.case_audit_case_audit_uid_seq
    INCREMENT 1
    START 5542
    MINVALUE 1
    MAXVALUE 9223372036854775807
    CACHE 1;
ALTER SEQUENCE public.case_audit_case_audit_uid_seq OWNER TO postgres;
---Set original sequence
ALTER TABLE public.case_audit ALTER COLUMN case_audit_uid SET DEFAULT nextval('public.case_audit_case_audit_uid_seq'::regclass);
---Drop temp sequence
DROP SEQUENCE public.case_audit_case_audit_uid_seq1;

Is there any simple solution compare to this?

PgAdmin4 has no option to modify graphically but as per PostgreSQL Document, we can alter the sequence data type to smallint, integer, and bigint

Alter table column & sequence data type and set max value for the sequence

ALTER SEQUENCE public.case_audit_case_audit_uid_seq AS bigint;
ALTER SEQUENCE public.case_audit_case_audit_uid_seq MAXVALUE 9223372036854775807;
ALTER TABLE public.case_audit ALTER COLUMN case_audit_uid TYPE bigint;


One CPU in a 32-core system is getting to 100% usage and hurting performance I am experiencing a problem with one server. It has 32 cores and seems to be well-configured in all aspects (memory, hard disk drives...). Performance Dashboard is not showing blocks or deadlocks and no waiting tasks. All queries seem to be performing well.

From time to time (every 5-6 minutes) one of the cores gets up to 100% utilization and the system's performance slows down. It lasts for some seconds.

What should I check to know what is causing this misbehaviour?

It is a SQL Server 2005 standard edition with Service Packs installed.

Thank you so much.

What process is using that CPU? You need to monitor the Process object and figure out which instance (ie.process) is driving CPU up. Also is important to distinguish between privileged (kernel) vs. user time. Also important to see if is not Interrupt Time by any chance.

The purpose of the exercise it o establish if is a hardware resource interrupt driving the CPU (I've seen this with some many network cards for instance), a user process (and which process). If you established that is SQL Server process then you can switch investigation to SQL Server specific means, at which moment it turns into SQL performance investigation well suited to Waits and Queues methodology. But if the problem is outside SQL then perhaps the best methodology to deploy is USE. And Kernrate is your friend.


Is it possible to do a ALL SERVER DML Trigger? My department works on converting a client's database when a client switches from another company to us. We have a RDP session they can connect to and see our software with their data. Once they approve the conversion, we copy the data from our server and set it up on site for them.

During the conversion approval phase it would make things a lot simpler for us if we could have some AFTER and INSTEAD OF triggers running on all databases attached to the server (our department is a different department than the one that develops the software and they will not make a version for the testing site that does the stuff for us built in for us).

I see two options:

Create a DML trigger that either performs the action on all databases attached to the server, but when the database is moved to the live server the action no longer occurs because it is run on the server not the database.
Have a DML trigger that deletes itself if it detects that it is no longer attached to the test server and is in a live environment.

From what I read I don't think the first option is possible, but I wanted to check and see. I think I know enough to do the 2nd option, but any examples of how to implement it would be very helpful.

You are not going to be able to create a server-wide trigger, because it has to exist in the same database that the DML changes are occurring in.

Having self-deleting code sounds a bit problematic as well.

I think you are going to have to have a process in place that creates the triggers when a new database is attached, and removes them when you are finished with them.  This is likely going to be a manual process.


You want nested CASE statements.  You haven't full described the problem, we can't give you a full answer.  One branch would list 'foot' as the first priority, and other as the last priority (or maybe not consider it at all, or consider it an error) Something like:

CASE WHEN speed > 2 then
  CASE WHEN bike then 'bike'
  CASE WHEN car then 'car'
  ...
  CASE when foot then 'foot and/or error'
  ELSE 'none'
  END
ELSE
  CASE when foot then 'foot'
  CASE WHEN bike then 'bike'
  CASE WHEN car then 'car'
  ...
  ELSE 'none'
  END
END

You can't ignore a row from the select-list, so you probably need to write logic in two places.  In the WHERE clause, to ignore rows with a true in more than one non-foot column, plus a CASE expression like the above in the select list, (or in the SET list if doing an UPDATE).


How do you monitor slow query in AWS RDS Postgres My RDS Postgres has 5 read replica and it's the maximum number of read replica and cannot scale out by increasing read replica. In this situation, some of the read replicas occur too much high CPU utilization.

I think one of the solution against it is query tunining. But we cannot login RDS postgres with ssh connect, so to read Postgres Error log we need to download and analyze it. This way is too inefficient and very depend on who do this analysis work.

So I want to build up the system of slow query analysis in RDS PostgreSQL environment. And I want to hear what sort of approach or what middleware is recommend.

My plan is
- Using RDS REST API, download db log file every hour to local storage.
( https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/RESTReference.html )
- Using fluend, build up log analysis system

I would reconfigure parameter group settings to create only one logfile per day which is much easier to download and analyze than many smaller files. And configure slow query logging based on your requirements.

Since You're AWS RDS user you can enable performance insights which might be helpful in Your case.


What is a "Loose Index Scan"? I was reading that MySQL can not use an index on 2 range conditions due to not having a "loose index scan".

What exactly does "Loose Index Scan" mean?

From High Performance MySQL Third Edition,

  Loose Index Scans
  MySQL has historically been unable to do loose index scans, which scan noncontiguous ranges of an index. MySQL’s index scans generally require a defined start point and a defined end point in the index, even if only a few noncontiguous rows in the middle are really desired for the query. MySQL will scan the entire range of rows within these
  end points.

A loose index scan is simply a scan that skips over part of the index. How does it do that? Assuming an index on (a,b) a loose index scan allows you to query for specific values of b without scanning all values of b. It does this by starting at the beginning of the range of b searching, and then skipping backward to find a new a. Compare this with a (tight) index scan where the whole index is scanned and there is no backtracking.

Backtracking in this context usually means moving up on a tree (back) to the previous position.

  I was reading that MySQL can not use an index on 2 range conditions due to not having a "loose index scan"

I don't think that was ever true. It (most RDBMS) can use an index, but without an implementation of a loose index scan they would have had to scan the entire index, as in an Index-only scan. This saves having to visit the table. This may or may not be the desirable action (in the cases where it would have to visit the table anyway), when the selectivity is low, or when the index is close enough to the size of the table that it simply doesn't matter.

See also

MySQL 8: Group by Optimization - Loose Index Scan
PostgreSQL Method (with a Recursive CTE)

Footnotes

I'm not sure whether the book's statement about MySQL is still accurate as the book was printed in 2012 and covers MySQL 5.5.


SQL Server Service Account login or password is not valid error I am trying to install SQL Server 2012 Express on a Windows 2012 Server for test purposes. This machine is part of our company domain.

During installation I am trying to use one of domain account for SQL Server Database Engine service but it is failing with following error,

  "The SQL Server service account login or password is not valid. User
  SQL Server Configuration Manager to update the service account."

I have verified both domain user and password. And this account has "Log on as a Service" permission enabled. I have also added this user into local admin group but still no go.

Any suggestion is appreciated.

The issue isn't with the account you are attempting to use, but your account. If you look in the setup log file, which is created during the installation of the SQL Server application, you will see the details of the issue, with technical jargon that will indicate the issue is probably a permission issue with your account having permission to perform a check on the service account credentials:

Slp: Sco: Attempting to check if container ‘WinNT://DOMAIN’ of user account exists
Slp: UserSecurity.ValidateCredentials — Exception caught and ignored, exception is Access is denied.
Slp: UserSecurity.ValidateCredentials — user validation failed

Rebooting usually fixes this issue, as does changing the service account value after installation is complete. A minor issue that sometimes appears.


Does the ANSI SQL Standard allow filters or parameters in JOIN conditions? Does the ANSI Standard find the following SQL sinppet syntactically correct?  I am interested specifically in the last line:

SELECT name, dept_name
FROM employee JOIN department
     ON employee.dept_id = department.dept_id
     JOIN payroll ON payroll_type = 1;

Yes, it is syntactically correct.

The ON clause can be any boolean expression - i.e. as long as it results in a boolean value (TRUE, FALSE, UNKNOWN).

Note that you have 3 tables that are visible in that ON clause: employee, department and payroll. You'll get an error if payroll_type is a column in more than one of them.


SQL Server Web Service in a web environment architecture I am new at SQL Server web Service and not too knowledgeable in setting up a secure web environment...
From what I've read until now regarding SQL Web Service, I would need to:
1. create a stored procedure,
2. create an endpoint
3. create a web service
4. call the stored procedure from the web...

Sounds easy enough although for Steps 3 and 4, I don't have a clue how to do for now... but my question is in the architecture before I start testing.   From my understanding, the SQL web service resides on the SQL Server?
If this is the case, it means that the DMZ would have direct access to our internal network? that's a nono as far as I know...

In an architecture where I would want an SQL Server within a closed off network, and an application/business tier within a DMZ and web servers in another DMZ... How would I be able to get some information that is needed from the SQL Server in the closed off network while keeping it secure?  hence the reason why I started investigating the possibility of using the SQL Server Web Service...

In my mind, each web server in DMZ1 will have their DB in the DMZ which will contain minimal non sensitive info to run the website, but retrieving general accumulated information (such as points, sales or historic info, etc.) that can be generated from different web sites and different sources cannot be transferred to the Website DBs due to their size and need the data to be in real-time.
I was thinking of having a DMZ2  which contains the SQL Server Web Service (as an SQL Web Service Layer) which would retrieve data from the SQL server within our network.
Is it possible?
Is it a good idea?
If not, How would you go about it?

Thank you for your help.

Typically, you'd have a web service hosted in IIS or some other http daemon in the internal network, with a firewall hole opened up for the specified port and machine where the http web service is hosted.

The web service, being inside, would have access to the SQL Server as required.

This maintains protection for the SQL Server since it is not directly accessible from the dmz/website.

FYI, SOAP/xml endpoints in SQL Server were deprecated as of SQL Server 2012.


Is Cassandra's manual node addition/removal tasks tedious and practical in a large cluster? I am evaluating using Cassandra as a large key-value store over about 500 commodity machines.  AFAIK, Cassandra requires manual intervention during permanent machine failures (removing the deadnode, adding new machines).  I would think that the machine failure rate is high enough to overload the administrator.  So is it practically feasible to run Cassandra in such a large cluster?

Thanks,

There are mentions of clusters with size 300-400 & more machines.  But plenty of tools are provided with Cassandra that could help with automation of deployment process.  Plus you can find a plenty of recipes for Ansible (1, 2, ...), or some other automation frameworks.

More work could be required for other admin tasks, like, making sure that repairs are running, etc.  But people are doing it, and there are "3rd-party" tools for them as well.


Oracle - query by two column combination I have two tables:

    product_table:
    ------------------------------------
     product | market | category | other
    ------------------------------------
             |        |          |
    ------------------------------------

    availability_table:
    -------------------------------------
     product | num_available | some_other
    -------------------------------------
             |               |
    -------------------------------------

I have a list of 7 markets and a list of 41 categories. For each market/category combination, I need to get several products (3 is a good number) that are currently available. I came up with a query that delivers products for one market/category combination:

SELECT product_table.product, product_table.category
FROM product_table, availability_table
WHERE   product_table.market = 1 // market is changing from query to query
    AND product_table.product = availability_table.product
    AND availability_table.num_available > 0
    AND product_table.category = 'category_1' // category is changing from query to query
    AND ROWNUM <= 3

This works well for one market/category combination, but I have to manually submit 287 queries to get all data I need. Is there any way to build a query that will do it for me with one submission?

Since you don't care which products are returned

SELECT product, category, market
  FROM (SELECT product,
               category,
               market,
               row_number() over (partition by market, category
                                      order by rownum) rnk
          FROM product_table
               JOIN availability_table USING (product)
         WHERE num_available > 0)
 WHERE rnk <= 3;


Why are graph databases bad at CRUD operations? I heard that graph DBs are inherently worse at CRUD operations than relational DBs. Is this true? If yes, why is that?

Some thoughts:

To do a crud operation in a realtional DB you basically have to work with one row (best case). This should be quite efficient as long as it is not a columnoriented format (OLAP).

To the best of my knowledge there are 2 popular ways to store a graph:

Either actually storing "objects which point at each other". This would be a natural representation of a graph.

Or storing a table, which lists relations. This would be the non-natural representation of a graph.

In the second case I understand why crud operations are slow: You might have to touch a lot of tables to represent what would just be an update of a single row in a relational schema.

Why is it slow in the first case? To an update or delete you just might have to create/read/update/delete a single object.

I think the software is too complicated to give simple yes/ no answers to this question. It also depends on how you define CRUD.

Let's take the "place order" function as an example. When seen from a business point-of-view this is a single atomic action. In a normalized relational database it will likely touch many tables, however. There will be an insert for the order; perhaps updates to customer or shopping cart tables; reads of many related tables to ensure referential correctness. All these statements will (should?) be wrapped in a database transaction. So a single business action becomes many database statements wrapped in a single database transaction.

Were a document database used instead of a relational one likely there would have been a single write. A graph DBMS would sit between these two, being more or less normalized depending on the particular application's architecture.

Looking just at on-disk structures the discussion is more nuanced, too. Inserting a single row to a table seems simple. If there are indexes then likely there will be write amplification. If there is an index split then more so. If the row holds BLOBs these may be stored in separate data structures, or different files. There could be cascading actions through triggers or materialized views. Compression and encryption may incur large CPU consumption.

There are many on-disk structures to support graph-like processing. In one nodes are the basic object and edges become properties of nodes. Edges are stored as pointer-list in the nodes at either end of the edge. So, to create a node would be one write. To create an edge will be two. To insert a row with a foreign key constraint (the nearest equivalent to an edge) will be a read and a write.

On the read side there are swings-and-roundabouts, too. Counting child rows by parent will be a two-table join in the relational world. If edge pointers are stored as a list within the node it's a single node-list scan. Doing is-that-connected-to-this queries (friends of people who know someone that lives in the same city that person can get to by rail within one hour) is easy in graph DBs; it's monstrous in the relational world. For the "R" bit of CRUD, for transitive queries, graph wins hands-down every time.

In conclusion, I question the premise of the title. If milliseconds are precious in your context then test, at scale, on production-equivalent hardware.


Shrink transaction log while transaction in progress I have a several-GB import into my SQL Server 2005 (Standard Edition) database.

The import is separated into 4 parts, each part in its own transaction.

As you can guess, the transaction log files are quite big. The space on my disk is also limited, but should fit the data. While doing my import the free space on my disk is about 2 gb.

Does it work to shrink the files, while my transactions are in progress? Would it also have an effect?

You do not have to shrink the log (ie. make the file smaller) but instead you have to truncate the log (ie. allow the file to be reused internally, so it doesn't grow to start with). How to achieve this depends on your database recovery model. Under SIMPLE recovery model the truncation occurs automatically. Under other models (FULL or BULK-LOGGED) the truncation occurs basically when you successfully run a BACKUP LOG statement (I'm omitting some details). So, first, figure out what is the recovery model of your target database, see View or Change the Recovery Model of a Database.

For an in depth look, see How to shrink the SQL Server log. The article explains the circular nature of the log and why often simply issuing DBCC SHRINKFILE results in no reduction of the file (read the article to understand why).


Is it good or bad practice to have a fact table relate to the same dimension table multiple times? I'm new to DW/BI, and this seems like a pretty basic question, but I can't seem to find a good answer.

I have a fact table that would have more than one date associated with it, so I was thinking that I would have multiple date keys back to the same dimension table, e.g., SubmittedDateKey, ClosedDateKey. Obviously SQL Server can do this - my reservation in this is that when I designed a proof-of-concept schema this way, the client tools (i.e., Tableau, Excel), seemed to get confused about how to map the relationships. (I also found  this forum post saying that Oracle's Analytic Workspace Manager explicitly forbids this, which of course implies nothing about my Microsoft world, but suggests that it's a general no-no.)

Is there a best practice in DW/BI about this? Instead of having one dimension table hooked up to each of these keys, should I have multiple identical dimension tables, e.g., SubmittedDateDimension, ClosedDateDimension?

Thanks!

I'd use the same dimension table. Especially for a date dimension
This is a Role Playing Dimension also here

I would not let a client tool (or an Oracle forum post!) determine my SQL Server DW design.


DB2 Commands -- Different ways of stopping database I'm hoping to get a more in-depth explanation of the following DB2 commands, which all relate to stopping the database in some way, and how they differ from each other.

How do DBAs think about these commands and decide which situations to apply them?

deactivate database
terminate
quiesce
db2stop

The official documentation doesn't provide more than a couple sentences and usually defines the commands in terms of themselves:

  DEACTIVATE DATABASE: This command deactivates the target database on all members in the
  instance or, a specified member.

Or,

  TERMINATE: Explicitly terminates the command line processor's back-end process.

After trying to research the difference between the commands, most online explanations consist of people simply parroting the documentation. But what is the real difference? Does anyone have an "in the trenches" view of which circumstances require which of these commands?

Ok, this is really simplified, but hopefully helpful.

DEACTIVATE is the opposite of ACTIVATE. ACTIVATE loads some of the database processes into memory to save time and performance on connections. Otherwise the first connection is where the database does this work. DEACTIVATE takes the processes out of memory. It only "completes" once everyone is logged out. You only have to DEACTIVATE if you ACTIVATE.

TERMINATE shuts down any current connections and any processes run by the command line processor (CLP). It does not necessarily "unload" processes from memory like DEACTIVATE does.

QUIESCE puts the database (or instance) into a "restricted mode" so only IDs with QUIESCE_CONNECT privilege can connect. This is useful for running certain upgrades, etc, when you need the database up, but do not want users on the system. UNQUIESCE takes the database (or instance) out of this mode.

db2stop actually stops the instance. This would be the equivalent of hitting the "off" button, so to speak. db2stop force is a stronger "off" that forces users off the system.

EDIT: As a side note, when you run db2start, if you run it as db2start admin mode, it starts the instance in a state of QUIESCE.


Deadlock in INSERT ... ON DUPLICATE KEY UPDATE We have a table defined like this:

CREATE TABLE ipsum (
    id char(40) NOT NULL,
    source char(40) NOT NULL,
    ip_address varbinary(16) NOT NULL,
    port smallint(5) unsigned NOT NULL,
    percentage decimal(5,2) NOT NULL DEFAULT '0.00',
    first_date datetime NOT NULL,
    last_date datetime NOT NULL,
    p0 tinyint(1) NOT NULL DEFAULT '0',
    p1 tinyint(1) NOT NULL DEFAULT '0',
    blocked tinyint(1) NOT NULL,
    created datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
    country varchar(100) DEFAULT NULL,
    continent varchar(100) DEFAULT NULL,
    note varchar(100) DEFAULT NULL,
    PRIMARY KEY (id),
    KEY idx_first_seen_peer (first_date),
    KEY idx_last_seen_peer (last_date),
    KEY ipsum (source,ip_address,port)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

We are doing bulk inserts into this table in batches of up to 15,000 records at a time. Inserts are structured like this:

INSERT INTO ipsum (id, source, ip_address, port, percentage, first_date, last_date, p0, p1, country, continent, note, blocked)
VALUES {{ 15000 legitimate values here sorted alphabetically by the id field }}
ON DUPLICATE KEY UPDATE
p0 = p0 OR VALUES(p0),
p1 = p1 OR VALUES(p1),
last_date = GREATEST(last_date, VALUES(last_date)),
first_date = LEAST(first_date, VALUES(first_date)),
percentage = GREATEST(percentage, VALUES(percentage));

The records in the VALUES set have been ordered by id alphabetically.

When we reach a very high rate of input with multiple instances of the query running we start to see deadlocks:

  Error: ER_LOCK_DEADLOCK: Deadlock found when trying to get lock; try restarting transaction]

No other statements are being executed against the table -- only the inserts as described above.

How can I modify the insert query to prevent the deadlocks?

Following the MySQL recommendations, some options I'd consider:

Check the SHOW ENGINE INNODB STATUS to see what the deadlock is on.
Convert the INSERT ON DUPLICATE KEY UPDATE into a SELECT then INSERT or UPDATE.
Reduce the size of the batches so they are quicker.
Reduce the concurrency.

What is the relation between the ids in the concurrent batches?  Do the ranges of ids overlap?


Encoding issue mysql I'm using the MD5 hash function of MySQL to hash some data I import into the database from a CSV file.
The issue is that some lines have an incorrect hash (but not all of them).
When I use a query:

UPDATE mytable SET email_md5 = (SELECT MD5(email))

MySQL inserts some wrong hashes in the table but when I check with using

SELECT MD5('email@email.com')

I find the correct answer.

Any idea how to check the encoding of the data I import in the database?

UPDATE #1
For this email onachba@i-pcom.com (not existing anymore)
Mysql insert this hash when I using MySQL query (the first in this post) with data in database from the CSV

  c62804a6d7122356cf5f261ebf860684

But when I insert the same email with my hands in database and replay the same query I find this hash (and is the correct hash)

  c31383bbaee83e9704fd7b0ad52581ca

UPDATE #2
We using mysql 8 and adminer
I feel it is a problem of encoding.
When I select the line for edit it from adminer and save it without any changes the browser recognize it like an email (she becomes clickable link with mailto:) + after this when I replay the query he add the good hash ! !
The row don't recognize like an email in adminer give me wrong hash
but
the lines from adminer recognize like an email have the good hash...
This is f*** creazy !

UPDATE #3
For datatype, it's varchar(200) NULL
With another email where I find a MD5 issue
From MySQL with

SELECT MD5(email) FROM mytable WHERE id = 'theID';

  f6cd93fc748b7c856a74e6037f6e14f5

SELECT HEX(email) FROM mytable WHERE id = 'theID';

  627269636540696775616E6573747564696F2E636F6D0D


Full text search with json field in SQLite Is it possible to have full text search in SQLite along using json?

In SQLite json is stored as TEXT (JSON1).
Full text search requires creation of virtual table (full text) but I don't know how to connect those two extensions so that full text search wouldn't search in json field names.

WRITING (ID, FK_PERSON_ID, BOOK_NAME, TEXT_JSON)
PEOPLE (PERSON_ID, NAME)

additionally TEXT_JSON for certain person contains following json objects:

-- WRITING.ID = 1, WRITING.FK_PERSON_ID = 1, BOOK_NAME = "NAME_1"
{
  "chapter1": "Title1",
  "text1": "This is sample sentence with word text",
  "text2": "This is sample sentence with word text"
}
-- WRITING.ID = 2, WRITING.FK_PERSON_ID = 1, BOOK_NAME = "NAME_101"
{
  "chapter1": "Title2",
  "text1": "This is sample sentence without"
}

(the structure of json object can differ)

How should I setup virtual table for full text search to search single person writings BOOK_NAME and values of all TEXT_JSON attributes? Searching word text in writings of PERSON_ID = 1 would return only WRITING.ID = 1.

A full-text search table cannot be used for most 'normal' operation; you should think of it as something like an index.

The FTS documentation shows how you can use triggers to keep the actual table and the FTS table in sync.


Changing MaximumErrorCount when executing maintenance plan I have defined a Maintenance Plan in Microsoft SQL Server 2012. It doesn't execute and it says

   DTS_W_MAXIMUMERRORCOUNTREACHED.
 The Execution method succeeded, but the number of errors raised (2) reached the maximum allowed (1); resulting in failure.
 This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors////

Where should change it?

Each task in a Maintenance Plan has a variety of options that can be changed by right-clicking the desired task, then selecting the "Properties" option from the context-menu.  As in:

To allow a task to have multiple errors prior to failing, simply modify the MaximumErrorCount property I've highlighted above to some number greater than 1.

Having said that, Maintenance Plans are difficult to manage.

Options I recommend using instead:

Ola Hallengren has a great replacement solution that is extensible and makes management a breeze.
MinionWare Backup - built by DBAs for DBAs.


How to check for existence of an INDEX on a column independent of used SQL database Is there are unified way of how to check for the existance of an INDEX for a given column irregardless of the actual SQL database system used?

For MySQL one could for instance check for the existance using SHOW CREATE TABLE mytable. In the result there would be something like this if column mycolumn has an index: KEY 'Index_1' ('mycolumn').

Is this indicator KEY unified among all SQL database systems?

Are there better ways to check for an index?

I'm afraid not.

The ANSI SQL standard defines some data dictionary views in a schema  ("INFORMATION_SCHEMA") that can be used to look for specific types of objects (tables, views, table columns), but it doesn't include anything regarding indexes.

Most RDBMSes have their own internal data dictionary views that expose this information (sysindexes in SQL Server & Sybase, DBA_INDEXES in Oracle, for example).


Help me understand and resolve MySQL deadlock - "lock mode AUTO-INC waiting" I am experiencing an issue with MySQL deadlocks and here is what I see in the MySQL error log:

2017-07-24 09:09:29 7fd5235db700InnoDB: transactions deadlock detected, dumping detailed information.
2017-07-24 09:09:29 7fd5235db700
*** (1) TRANSACTION:
TRANSACTION 177707078, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 360, 1 row lock(s), undo log entries 1
MySQL thread id 20887150, OS thread handle 0x7fd522d7a700, query id 918997514 10.80.11.16 staging update
INSERT INTO `n0_table` (`post_id`, `meta_key`, `meta_value`) VALUES (411902, 'from_header', 'barberklingen <****************>')
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 480 page no 44003 n bits 768 index `post_id` of table `staging`.`n0_table` trx id 177707078 lock_mode X insert intention waiting
*** (2) TRANSACTION:
TRANSACTION 177706917, ACTIVE 1 sec setting auto-inc lock
mysql tables in use 1, locked 1
121 lock struct(s), heap size 13864, 15196 row lock(s), undo log entries 93
MySQL thread id 20887160, OS thread handle 0x7fd5235db700, query id 918997545 10.80.11.15 staging update
INSERT INTO `n0_table` (`post_id`, `meta_key`, `meta_value`) VALUES (411901, '_wc_customer_order_csv_export_customer_is_exported', '0')
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 480 page no 44003 n bits 760 index `post_id` of table `staging`.`n0_table` trx id 177706917 lock_mode X
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
TABLE LOCK table `staging`.`n0_table` trx id 177706917 lock mode AUTO-INC waiting
*** WE ROLL BACK TRANSACTION (1)

Currently the MySQL InnoDB lock mode is:

+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_autoinc_lock_mode | 0     |
+--------------------------+-------+

The hosting setup for this website which generates those deadlocks is the following:

Requests go to two load balancers and from there are forwarded to 4 application servers.
The application servers then use one and the same MySQL master database.
There is a slave which is configured in case the master fails.

I am trying to figure out why those deadlocks are occurring and also how to prevent them. In my understanding the auto increment should be working without deadlocks in this case but I am probably missing something. Thank you and let me know if case other information is required.

UPDATE:

Here is the show create table:

mysql> show create table n0_table\G;
*************************** 1. row ***************************
       Table: n0_table
Create Table: CREATE TABLE `n0_table` (
  `meta_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `post_id` bigint(20) unsigned NOT NULL DEFAULT '0',
  `meta_key` varchar(255) DEFAULT NULL,
  `meta_value` longtext,
  PRIMARY KEY (`meta_id`),
  KEY `post_id` (`post_id`),
  KEY `meta_key` (`meta_key`(191))
) ENGINE=InnoDB AUTO_INCREMENT=8014133 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

The schema for n0_table looks like it is straight from Wordpress?  It has multiple inefficiencies.  Revamp it to make queries faster and either eliminate or decrease the frequency of the original problem.

Details:  http://mysql.rjweb.org/doc.php/index_cookbook_mysql#speeding_up_wp_postmeta


Import csv data from localhost emacs SQLi error MySQL I'm trying to import

LOAD DATA LOCAL INFILE "/home/frederic/cowtest.csv"
    INTO TABLE cow_db1 FIELDS TERMINATED BY ","
    ENCLOSED BY '"' LINES TERMINATED BY '\n'
    IGNORE 1 LINES (state_abb, c_code, state_name);

I get: ERROR 1148 (42000): The used command is not allowed with this MySQL version

I connected mysql with M-x sql-mysql and

  User = root
  Password = .....
  Database =
  Server = localhost

But running directly from the shell after

mysql -h localhost -u root -p --local-infile

does not return the error but

Query OK, 243 rows affected (0.05 sec)
Records: 243  Deleted: 0  Skipped: 0  Warnings: 0

I tried with LOAD DATA LOCAL INFILE instead (has recommended in other solution) and then it returns ERROR 29 (HY000): File '/home/frederic/cowtest.csv' not found (Errcode: 13)

So it is likely that things goes unspecified about SQLi extension, and the  --local-infile specification.

Many thanks
frederic

It seems variable  "local_infile" is turned off before loading and if you specify "--local-infile" turns on this variable.

You can use following steps to load file

  login mysql using mysql -uroot -p
  set global local_infile to value 1
  Then try load data local infile

Hope it helps


Change Data Capture missing changes for a specific record I have enabled CDC in SQL Server and it generally works well but sometimes the changes are not recorded for some reason. And only for a couple of records. But then if I issue an UPDATE from SSMS, the change is recorded.

I'm wondering if it might have something to do with the SQL Server Agent jobs; maybe those jobs fail and because of that the change is not saved?

I have checked the job history in SQL Agent (for cdc.myDatabase_capture and cdc.myDatabase_cleanup) but couldn't find any failures.

What should I investigate to find the cause of the issue?

SQL Server Details:

 Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64)
    Jun 17 2011 00:54:03
    Copyright (c) Microsoft Corporation
    Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
 (Hypervisor)

The problem was that the retention period was set to the default of 3 days.

I have extended it to one year using this script:

EXECUTE sys.sp_cdc_change_job @job_type = N'cleanup', @retention = 525600; --set data retention for 1 year


CREATE TABLE TEXTIMAGE_ON [PRIMARY] is redundant it there's only one filegroup? I've recently generated scripts for a legacy database, and discovered that most of the tables were created with TEXTIMAGE_ON [PRIMARY], but there's only one filegroup.

TEXTIMAGE_ON [PRIMARY] is redundant when there is only a single filegroup, correct?

Yes, this is redundant. By default, SSMS will script these

The option is here:


Inventory System Data Design Issue So I have been trying to design a inventory management system for a Car Showroom. The problem with this design is that I can get a purchase but on selling the product I can't link it to my purchase. The idea I've came with up is add a Batch_ID to my Inventory_Details table but I can only link that Batch_ID in my details table not in my inventory table. Any suggestions will be helpful.

In a traditional Inventory Warehouse System or Warehouse Management System (WMS) whenever a new Item is put on hand (i.e. created or purchased, and therefore added to Stock) a unique identifier must be created, usually a serial number, before you can enter it into Inventory and adjust your quantity on hand. In the context of a Car Showroom, I would imagine this would the be the VIN instead. (I'm not an expert on cars but I believe that's a unique identifier.)
Even when an item is received in batch quantities, that many unique identifiers need to be added to your Inventory before you can add the quantity to your quantity on hand. Therefore if your Purchase table has a one-to-many relationship with how many cars a Purchase can buy, because of batches, then you can't directly relate it to your Inventory table (yet). Rather you should have a separate table such as PurchaseLines which is many-to-one with your Purchase table, and has a row with the unique VIN for each car that was purchased. (This is even a simplification, as a real WMS actually normalizes this further into at least 2 more tables for ledger purposes, but I think that's outside the scope here, and this should be sufficient to what you need.) Your InventoryDetails table would then contain the VIN as well and directly relate back to the PurchaseLine it came from.
Additionally you should have just an Items table that defines the criteria of your cars, so you can generically refer to your quantity on hand by Item. As I'm sure you don't want to track the individual quantity of a VIN which will always be 1 (since my changes above will result in that). Instead if you have an Items table with fields like Model, Description, Color, MFYear, etc then your InventoryDetails table becomes more normalized and only stores an ItemId field instead (one-to-one with the Items table). Then your Inventory table can now track quantity for an ItemId instead which would be the relationship between InventoryDetails.
Finally, the concept of an ItemId becomes universal throughout the Warehouse Management System. It is like your core unit of measure. It should be part of your Purchases (in PurchaseLines) and your Sales (in an equivalent SalesLines table as well), and is a way to establish what item was transacted on.

Additional Resources:

Example ER Diagram of an Inventory Management System


Building dependence between regional data (Street -> City -> PostalCode -> Country) I'm in the process of designing a database to contain regional data to be used inside a CRM system.

So, a user suspects that things like the city or country are automatically filled (or maybe just suggested) when he types in certain pieces of information.

Now my question: Is this feasible when using international schemes? Is it possible to get a mapping between PostalCode -> City -> Country in Germany, like I can do in the US or Japan?

How to design such a database with normalization? I already found offers like GeoNames is doing, but they're not normalized let alone referenced via FK constraints.

Unfortunately, you can't even derive the country from the postal code. For example, the postal code 50170 can be found in 10 different countries.

And some countries don't have postal codes.

And some countries contain other countries, not provinces (UK contains England).

For autofill, I would suggest using HTML5 geolocation or IP address geolocation to help the user fill in forms (for themselves).

Otherwise, ask them to select the country, then enter the postal code, and you can offer the available next options, depending on the country.

Geonames is not bad (though I don't like how they don't use ISO codes for primary divisions), and it's designed that way because administrative divisions are not hierarchical.


Performance issue with xpath in SQL Server 2008 I have a table with lots of large xml-documents.

When I run xpath expressions to select data from those documents I run into a peculiar performance issue.

My query is

SELECT
p.n.value('.', 'int') AS PurchaseOrderID
,x.ProductID
FROM XmlLoadData x
CROSS APPLY x.PayLoad.nodes('declare namespace NS="http://schemas.datacontract.org/2004/07/XmlDbPerfTest";
/NS:ProductAndRelated[1]/NS:Product[1]/NS:PurchaseOrderDetails[1]/NS:PurchaseOrderDetail/NS:PurchaseOrderID[1]') p(n)

The query takes 2 minutes and 8 seconds.

When I remove the [1] parts of the single occurance nodes like this:

SELECT
p.n.value('.', 'int') AS PurchaseOrderID
,x.ProductID
FROM XmlLoadData x
CROSS APPLY x.PayLoad.nodes('declare namespace NS="http://schemas.datacontract.org/2004/07/XmlDbPerfTest";
/NS:ProductAndRelated/NS:Product/NS:PurchaseOrderDetails/NS:PurchaseOrderDetail/NS:PurchaseOrderID') p(n)

The execution time drops to just 18 seconds.
Since the [1]-nodes occurs just once in each parent node in the documents the results are the same except for ordering.

Actual execution plan for the first (slow) query is

and the second (faster) query is

Query 1 full screen Query 2 full screen.

As far as I can see the query with [1] does the same execution as the query without, but with the addition of some extra calculation steps to find the first item.

My question is why the second query is faster.
I would have expected the execution of the query with [1] to break early when a match was found and thus reduce the execution time instead of the opposite.
Are there any reasons why the execution does not break early with [1] and thus reduce the execution time.

This is my table

CREATE TABLE [dbo].[XmlLoadData](
    [ProductID] [int] NOT NULL,
    [PayLoad] [xml] NOT NULL,
    [Size]  AS (len(CONVERT([nvarchar](max),[PayLoad],0))),
 CONSTRAINT [PK_XmlLoadData] PRIMARY KEY CLUSTERED
(
    [ProductID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF,
       IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON,
       ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Edit:
Performance numbers from SQL Profiler:

Query 1:

CPU     Reads   Writes  Duration
126251  1224892 0       129797

Query 2:

CPU     Reads   Writes  Duration
50124   612499  0       16307

The second query uses parallelism. That is, it was expensive enough for the optimizer to shut its eyes to the additional overhead.

I'd guess the second query tells optimizer to "dump everything", which is performed with a paralleled scan. SQL Server likes to "dump everything" in this way when asked.
Whereas the first query asks for "analyze and then give some." The optimizer has no way of knowing there's only one node anyway, so the execution plan it ends up picking is very different.

I'd say it's similar to situation when one table scan is cheaper than many index seeks.


Is there a way to back up agent jobs? I can't find documentation about whether agent jobs in SQL Server can be backed up. If not, how can I back them up in order to restore during the future recovery in case of an instance failure?

SQL agent jobs are stored in the system msdb database, so backing up that database will do what you need.

In a disaster, you can restore the entire msdb database, or, if necessary, recover individual jobs from a restored copy of msdb:

Serverfault: Recovering a specific job definition from an MSDB backup?

If you want them in a more immediately usable format, you can right-click and script out individual jobs manually, or if you'd like to script out all jobs on the server at once:

Stack Overflow: Automatically create scripts for all SQL Server Jobs


Python SQLite3 horizontal partitioning by string column I have a 30 GB SQLite 3 database with a table containing 25 million rows. I want to group the table by a string column and create a new table for every group of records, named after the string and containing all columns.

Is this possible with Python and SQLite 3? I could process chunks in Python, but is there a SQL command?

Start table

all_entries

a   b   c   d
hi  4   234 lala
bye 7   342 lulu
bye 5   433 abc
bye 4   234 abd
hi  56  435 abc
hi  3   455 a

Result Tables

bye

a   b   c   d
bye 7   342 lulu
bye 5   433 abc
bye 4   234 abd

hi

a   b   c   d
hi  4   234 lala
hi  56  435 abc
hi  3   455 a

This can be achieved as follows according to the official SQLite documentation.

For the bye table you could use the following:

CREATE TABLE bye AS
SELECT a,
       b,
       c,
       d,
FROM all_entries
WHERE a = 'bye'

...and for the hi table:

CREATE TABLE bye AS
SELECT a,
       b,
       c,
       d,
FROM all_entries
WHERE a = 'hi'

Reference: CREATE TABLE (SQL As Understood By SQLite)

If this is just a temporary solution for something you wish to achieve then add the TEMPORARY keyword:

CREATE TEMPORARY TABLE bye AS...

There are some limitations with this approach which are described in the referenced article.

  A table created using CREATE TABLE AS has no PRIMARY KEY and no constraints of any kind. The default value of each column is NULL. The default collation sequence for each column of the new table is BINARY.

  Tables created using CREATE TABLE AS are initially populated with the rows of data returned by the SELECT statement. Rows are assigned contiguously ascending rowid values, starting with 1, in the order that they are returned by the SELECT statement.

And you would possibly have to post a question on Stack Overflow for a Python solution.


Why would SELECT * be magnitudes faster than SELECT foo? Consider a table of values and hashes, like so:

+------------+----------+------+-----+---------+----------------+
| Field      | Type     | Null | Key | Default | Extra          |
+------------+----------+------+-----+---------+----------------+
| id         | int(11)  | NO   | PRI | NULL    | auto_increment |
| val        | char(9)  | NO   |     | NULL    |                |
| val_hashed | char(50) | YES  |     | NULL    |                |
+------------+----------+------+-----+---------+----------------+

The following query finishes in 0.00 seconds:

SELECT * FROM hashes ORDER BY 1 DESC LIMIT 1;

However, this query takes 3 min 17 seconds:

SELECT val FROM hashes ORDER BY 1 DESC LIMIT 1;

I see that while the query is running the process list shows it as status Sorting result. The situation is completely reproducible. Note that there is another process performing INSERT operations on the table continuously.

Why would the more specific query take longer to run than the * query? I've always believed that * queries should be avoided specifically for performance reasons.

The phrase ORDER BY 1 refers to different columns; in the first it will be id, in the second val.  Since id is the key it will be indexed and the order by will be a trivial amount of work.  To order by val, however, the system will have to retrieve every row, sort the complete table by val, then choose just one of those rows.

Change both queries to order by id and I think your execution times will be almost identical.


Linked Server and MSDTC on cluster I'm trying to set up a linked server between two instances we have on our network. I've done this many times before without an issue, but this time I repeatedly get the following error:

OLE DB provider "SQLNCLI10" for linked server "MYLINKEDSERVERNAME" returned message
    "The transaction manager has disabled its support for remote/network transactions.".
Msg 7391, Level 16, State 2, Procedure tableName_InsertUpdate, Line 57
The operation could not be performed because OLE DB provider "SQLNCLI10" for linked
    server "MYLINKEDSERVERNAME" was unable to begin a distributed transaction.

I've got server 'A', a SQL2008 R2 box and server 'B' a SQL2012 box as part of a cluster.

I've setup the linked server on server A and I can run a simple select * from [linkedserver].dbname.dbo.table which works correctly.

What isn't working for me, is a a trigger on a table on DB 'A' which is activated from an SP on server A attempting to insert into a table on DB 'B'.

Has anyone got any ideas as to why a query would work, but a trigger fired from an SP wouldn't?

Have you configured MSDTC on both the source and target machine using dcomcnfg?

The distributed transaction coordinator, by default, does not allow distributed transactions.

You need to configure it by running dcomcnfg as an administrator, and configure the options applicable to your situation:


Why does my table hold duplicate primary keys? To my big (and worrying) surprise I just realized that my table with millions of rows, has a few handful of rows, where the primary key (ID) is a duplicate! I don't understand how this can ever happen, and how I might prevent it in the future?

The column holding the primary key is, and has always been, subject to a CONSTRAINT fruits_pkey PRIMARY KEY(id);

I'm running postgreql 9.3.4 on ubuntu.

UPDATE

@Mat: Datatype is integer

@ypercube: Yes, select count(*) from (select count(*) from fruits group by id having count(*) > 1) as t1 returns 41.

@Craig: Yes, I have done a failover before, and my slave is actually 9.3.3

It was all caused by the bug described and fixed in this release: http://www.postgresql.org/about/news/1506/

It caused us a lot of trouble!


Get triggers/table names in PostgreSQL How can I get a listing of all triggers for a database along w/ the table that it belongs to?

I assume that I need pg_trigger and pg_tables but I'm not quite sure how to join them together.

This works for me:

select trg.tgname,
        CASE trg.tgtype::integer & 66
            WHEN 2 THEN 'BEFORE'
            WHEN 64 THEN 'INSTEAD OF'
            ELSE 'AFTER'
        end as trigger_type,
       case trg.tgtype::integer & cast(28 as int2)
         when 16 then 'UPDATE'
         when 8 then 'DELETE'
         when 4 then 'INSERT'
         when 20 then 'INSERT, UPDATE'
         when 28 then 'INSERT, UPDATE, DELETE'
         when 24 then 'UPDATE, DELETE'
         when 12 then 'INSERT, DELETE'
       end as trigger_event,
       ns.nspname||'.'||tbl.relname as trigger_table,
       obj_description(trg.oid) as remarks,
         case
          when trg.tgenabled='O' then 'ENABLED'
            else 'DISABLED'
        end as status,
        case trg.tgtype::integer & 1
          when 1 then 'ROW'::text
          else 'STATEMENT'::text
        end as trigger_level
FROM pg_trigger trg
 JOIN pg_class tbl on trg.tgrelid = tbl.oid
 JOIN pg_namespace ns ON ns.oid = tbl.relnamespace
WHERE trg.tgname not like 'RI_ConstraintTrigger%'
  AND trg.tgname not like 'pg_sync_pg%'

It doesn't include DDL triggers though.


Should data warehouse provide data for data composition for real time business transactions As title, I always had a feeling that data warehouse should only support for data analysis or the like, but shouldn't be used in real time transactions. (eg. being a data source to provide a product info for a cart purchase about to happen).

Could anyone share any views?

You are correct.
The reason for this is the current situation, in which the transactional database is usually separate from the analytical database, and therefore isn't up to date.
While up to date technologies like Data Vault could mean that you would use the same database for transaction and analytics, a situation in which you need to aggregate data from more than one data source could still means that you need ETL process, and therefore your analytical data isn't "up to date ".
So: analytical database gives you changes accross time, but if you want to know what goes on right now, use transactional database.


How to constrain data I have 3 tables which are related and a constraint on one of them which I can enforce in software but I don't know how to enforce in the database. I have a series of providers who send a set of data. A provider may send multiple schemes. So the pair of provider and scheme is distinct. A scheme consists of many records - let's say Credit Ratings. A user is associated with many credit ratings but only 1 from each provider/scheme pair. I'm stuggling to relate this data. What I currently have looks like:

Provider
========
id, providername, typename
1, Provider 1, type1
2, Provider 2, type1
3, Provider 2, type2

CreditRating
=======
id, providerid, rating, rank
1, 1, A, 1
2, 1, B, 2
3, 1, C, 3
4, 2, AA, 1
5, 2, B, 2
6, 3, A+, 1
7, 3, AA, 2

UserRating
==========
id, ratingid, userid
1, 1, 1
2, 1, 2
3, 5, 1

Now what I want to enforce is that the UserRating table, for each user there's only one credit rating from a given provider/type pair. With my current design I can't do that.
One option would be to add the providerid to the userrating table but then I can't ensure that this is consitent with the providerid on the credit rating itself.

Any ideas?

Include the providerid column (and appropriate values) in the UserRating table.
Make (userid, providerid) a unique key for the UserRating table.
Make (id, providerid) a unique key of CreditRating.
Make (ratingid, providerid) a foreign key of UserRating, referencing (id, providerid) in the CreditRating table.

Also, make (providername, typename) a unique key of Provider, if you haven't already. Or better yet, normalize them into separate provider and type tables, with a many-to-many join as appropriate.


MySQL 5.7 Community Server End of Life/Support Is there any official document that outlines the date till which MySQL Community Server 5.7 will receive security updates/patches? I see that page 20 of this document from Oracle outlines Premier and Extended Support end date for MySQL 5.7, but I am not sure if it is talking about Community Server Edition or MySQL Enterprise Edition. Is there a relation between extended support end date listed in the document and the date till which MySQL 5.7 will receive security patches?

Also, are security fixes released to Community Server first and then hardened and released to Enterprise edition of same MySQL version? Or are they released to Enterprise edition first and then to Community Server edition?

Short version of my question: Till what date will MySQL 5.7 Community Server get security patches? :)

Thanks

As per MySQL documentation here the below you can find the life cycle for each version of application MySQL

Current releases

Release        Release date              End of life

MySQL 8.0      April 19, 2018
MySQL 5.7      October 21, 2015          October 21, 2023
MySQL 5.6      February 5, 2013          February 5, 2021

All releases

Release        Release Date            End of life

MySQL 8.0      April 19, 2018
MySQL 5.7      October 21, 2015       October 21, 2023
MySQL 5.6      February 5, 2013       February 5, 2021
MySQL 5.5      December 3, 2010       December 3, 2018
MySQL 5.1      November 14, 2008      December 31, 2013
MySQL 5.0                             January 9, 2012

For further your ref here and here


MariaDB 10.2 Encrypted at rest log files not being written We're running MariaDB 10.2 with InnoDB tables that are encrypted at rest. I've also specified that the log files be encrypted at rest but they remain empty files. The tables seem to be encrypted just fine.

I'm hoping to get this database set up for replication with encrypted at rest log files.

Permissions all seem fine - can anyone think of a reason as to why the log files wont write?

Thanks in advance!

Here is my my.cnf

user=mysql

bind-address = 210.5.55.92
local-infile=0
max_connections=2000
max_allowed_packet=100M
skip-name-resolve

query_cache_type=0
query_cache_size = 128M
query_cache_limit = 1M
query_cache_min_res_unit=512

key_buffer_size = 1G
sort_buffer_size=16M
read_buffer_size=16M
read_rnd_buffer_size=16M
thread_concurrency=0

default-storage-engine = InnoDB
innodb_file_per_table
innodb_flush_log_at_trx_commit=2
innodb_buffer_pool_size = 64G
innodb_buffer_pool_instances = 2

innodb_flush_method=O_DIRECT
innodb_log_file_size=1G
innodb_data_file_path=ibdata1:10M:autoextend

thread_cache_size = 4

myisam_use_mmap=1
tmp_table_size = 512M
max_heap_table_size = 512M

symbolic-links=0

key_buffer_size=128M
sort_buffer_size=8M

expire-logs-days = 5
max-binlog-size  = 100M

open_files_limit=50000

table_open_cache=4096
table_definition_cache=4096

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

general-log
general-log-file=/var/log/mysql/mysqld_queries.log
log-output=file

slow_query_log = 1
slow_query_log_file = /var/log/mysql/log-slow-queries.log
long_query_time = 5
log_queries_not_using_indexes = 1

plugin-load-add=file_key_management.so
file-key-management
file-key-management-filename = /var/lib/mysql/keys
innodb-encrypt-tables
innodb-encrypt-log
innodb-encryption-threads=4

Ok after a bit of trial and error I've got all logs writing! I think the problem must have had to do with permissions as even though the log files were chown to mysql in the separate folder I planned to use - removing file designation from the config allowed logs to get written in the default folders. Sigh - thanks for your help!


How to increase index row maximum size in Postgresql? I want to perform KNN on 512-dimensional vector using the solution suggested here.

I couldn't create table with 512-d cube array. Solved by changing the CUBE_MAX_DIM in the source code.
Couldn't create index on table with dimensions > 200. Set block_size=32. Now works with dimensionality<=510.
Got new error after changing block_size and trying to create index on table with 512-d vectors ERROR:  index row requires 8208 bytes, maximum size is 8191

Is it possible to increase this limit?

That would require significant changes, and I doubt it can be done easily.

See these excerpts from src/include/access/itup.h:

/*
 * Index tuple header structure
 *
 * All index tuples start with IndexTupleData.  If the HasNulls bit is set,
 * this is followed by an IndexAttributeBitMapData.  The index attribute
 * values follow, beginning at a MAXALIGN boundary.
 *
 * Note that the space allocated for the bitmap does not vary with the number
 * of attributes; that is because we don't have room to store the number of
 * attributes in the header.  Given the MAXALIGN constraint there's no space
 * savings to be had anyway, for usual values of INDEX_MAX_KEYS.
 */

typedef struct IndexTupleData
{
    ItemPointerData t_tid;      /* reference TID to heap tuple */

    /* ---------------
     * t_info is laid out in the following fashion:
     *
     * 15th (high) bit: has nulls
     * 14th bit: has var-width attributes
     * 13th bit: AM-defined meaning
     * 12-0 bit: size of tuple
     * ---------------
     */

    unsigned short t_info;      /* various info about tuple */

} IndexTupleData;               /* MORE DATA FOLLOWS AT END OF STRUCT */
[...]

/*
 * t_info manipulation macros
 */
#define INDEX_SIZE_MASK 0x1FFF
#define INDEX_AM_RESERVED_BIT 0x2000    /* reserved for index-AM specific
                                         * usage */
#define INDEX_VAR_MASK  0x4000
#define INDEX_NULL_MASK 0x8000

The limit you are hitting is INDEX_SIZE_MASK, and to increase it, you'd have to change the tuple header so that t_info has more than two bytes.

Perhaps it is as simple as that, but it might have repercussions in other parts of the code.


Database with replication and mirroring stuck in LOG_BACKUP after log file shrink Need an advice from SQL Server Experts. I'm just a developer, not a DBA, so pardon my ignorance.

My environment consists of 2 equal instances (Node1 and Node2) of SQL Server 2008 R2 x64 (10.50.4339.0). I have 2 databases: DB1 and DB2 with Full recovery model. Both databases participate in replication between each other within single SQL instance and also both of them get mirrored from Node1 to Node2. These are not production databases and I did take full DB backups before doing any changes.

However, I have ended up in trouble: the log file is not growing for both DB1 and DB2 at Principal and the execution of:

select log_reuse_wait_desc, name, * from sys.databases where name='DB1' or name='DB2'

shows that both DB1 and DB2 have `log_reuse_wait_desc='LOG_BACKUP' even though I did run log backup manually.

Here is the sequence of events which caused the current state:

I needed to update DB1 and DB2 with year-worth set of changes which involved running many structure and data change scripts. Some of the data scripts were quite heavy, truncating tables and containing 70K+ INSERT calls. So, I've started applying changes at Principal (Node1) one by one in little chunks.
Node1 already had limited disk space available at partition with DB1 and DB2 data and log files.
When applying changes several times I've received log file full... message. So I've stopped and shrunk the log file of both DB1 and DB2 to 0Mb to free up some disk space to be able to finish the data/structure update task.
The log file of both DBs did get shrunk to minimal allowed however after I have continued with data changes I've got the following message:

  Msg 9002, Level 17, State 2, Line 1 The transaction log for database
  'DB2' is full. To find out why space in the log cannot be reused, see
  the log_reuse_wait_desc column in sys.databases

Sometime during running of data/structure change scripts the free disk space at relevant partition dropped to a few KBs and SQL Server sent out warning emails.

I did try to research how to get log_reuse_wait_desc column to show NOTHING. However, the suggested solutions prompted putting DB1 and DB2 into simple recovery mode or detach/re-attach them, something I cannot do because of the replication and mirroring.

I have also followed the advice from Paul S. Randal and have run log backup manually:

 BACKUP LOG DB2 TO DISK = 'z:\DB2.TRN'

which completed successfully, however the log_reuse_wait_desc still shows LOG_BACKUP and the log file is not growing.

DBCC LOGINFO Output:

DB1: 421 records like:

DB2: 381 records like:

Initial loginfo records for DB2: https://i.stack.imgur.com/TTsVu.png

Note, the Field column for DB1 contains values 2 and 4, while for DB2 Field column contains only value 2. The file size and offset fields looks scary: 54GB+? I may not have so much free space. Currently there is 26GB of free space on the drive and it's not decreasing. So seems SQL Server has hung.

Is there any way to fix the current DB1 and DB2 without disabling replication/mirroring followed by a restore from backup?

Any help is greatly appreciated.

I wonder if your operations generated some gaps in the transaction log which may be preventing the virtual log file from looping back around to the front of the physical log file. Conceptually this is what should be happening within your database when the end of the physical file is reached:

If DBCC LOGINFO returns records indicating the VLFs at the beginning of the physical log file have a status of 2, these VLFs need to be cleared, which in Full Recovery mode, means a TLog backup must be taken.  I believe this situation can also cause the log_reuse_wait_desc column to show LOG BACKUP as well. To fix this, you must force the logical header back toward the beginning of the physical file to clear out these status 2 VLFs (and hopefully get your log_reuse_wait_desc column back to showing NOTHING).  To do this, perform the following steps (which may need to done on both DB1 and DB2 databases):

Perform a Log Backup (e.g. BACKUP LOG [DBNAME]...) against the database
Perform a DBCC SHRINKFILE operation on the transaction log file specifying 1 MB as the target size
Perform another Log Backup against the database
Perform another DBCC SHRINKFILE operation on the transaction log file specifying 1 MB as the target size
Manually increase the size of the log file, incrementally per Kimberly Tripp's article, to grow the TLog back to an appropriate size.

So what's happening here?

The first Log Backup simply generates a backup of the completed transactions that have been flushed to disk and deletes inactive virtual log files from the logical transaction log, freeing space for reuse up to the logical log header.
The first DBCC Shrinkfile operation shrinks the physical TLOG down to where the current logical log header is located, freeing as much space as possible.
The second Log Backup operation is now able to loop back around within the logical log and backup/clear any residual inactive VLFs located before the logical log header.  These were probably never flushed in the past for whatever reason, but should be cleared at this point.  This step is critical to fixing your issue and sometimes this backup can take a bit of time depending on how much data needs to be written down to disk.
The second DBCC SHRINKFILE operation moves the logical log header to the front of the physical log file, eliminates excessive VLFs that are likely there now, and reduces the TLOG file back down to a very small size, in this case 1MB.  It is imperative to manually grow the file in step 5 so you don't run into VLF issues again down the road
Manually increasing the size of the tlog will allow you to control the size and number of VLFs within your physical log file.  This is an opportune time to configure this properly, so take advantage.

One final note on the process.  If your tlog is highly active, you may need to perform this a few times or during a window of reduced activity.  High activity may result in another errant auto-growth event occurring before you execute the second log backup.  This basically turns what you thought was step 3 back into step 1.  Generally Steps 1 and 2 go by quickly, and step 3 takes the longest to complete.  Make sure to follow-through with steps 4 and 5.


How do I determine the appropriate size for my tablespace when importing a .dmp file? I have a 14g DMP file. How large should I size my tablespace to import it into an Oracle 11g database?

At least 14 GB.

Maybe more if the dump file is compressed.

Maybe less if the tables are compressed.

A bit more or lot more if there are indexes.

Even Data Pump will miscalculate the required space in case of compressed tables.

Index data is not stored in the dump file, only index definitions. You can have a 14 GB dump file with 14 GB table data and 0 index definitions or hundreds of index definitions, and building those indexes may consume a lot more storage than the base tables, or just a fraction of it.

So the correct answer in my opinion is, test and measure it.


MYSQL - Data Model design for reminders I need some help on reminder data model design with repeat.
Admin can set reminder for any client. Like, a reminder for calling as  client on so and so date, or mailing a client on so and so date. One reminder can be assigned to multiple user so that anybody can react to it.
Tables:- reminder_repeat
repeat_id
name  --> possible values ( Daily, Weekly, Monthly, Yearly )

Tables:- reminder
reminder_id
name
reminder_date
client_id
repeat_id
reminder_status --> 'O' for Open and 'C' for Closed

Table:- reminder_assigned_user
reminder_id
user_id

Here, Logged in user will be able to see the reminders assigned and react to that and mark the status as Completed. I need to make sure that non more than one user is reacting for the same reminder.
Not sure, How to handle the repeat as we need to maintain the status which will mark the reminder as completed. One approach I can think of is to run a batch Job every night which will add entry for that day if there is any repeat.

Reminder systems seem simple at first, but can be wonderfully complex as you think through all the things modern reminder apps are capable of.
One way to approach the problem would be with the use of a “transaction” table that can contain a number of types. By doing this, a person could “take” a reminder, which would let their colleagues know that something is in progress but not yet complete. This would also make it possible to determine whether a task was completed on a given day, or whether something was started, but not marked as complete.
For the Reminder table, something like this might work:
id
subject
note
client_id
due_at
created_at
is_deleted

The note and due_at values would be optional.
For the ReminderRepeat table, you can probably go a bit overboard if you really want to …
id
reminder_id
start_at
until_at
type
remind_at
on_sunday
on_monday
on_tuesday
on_wednesday
on_thursday
on_friday
on_saturday

start_at would default to today and until_at would be NULL to allow repeats until “the end of time”. The type could be any of onetime,daily,weekly,monthly,yearly and so on. The on_ values would let you say “Weekly on Monday, Thursday, and Friday”. By having the reminder_id here you can allow edits to the repeat schedule to be tracked over time.
Then there’s the transaction table, which would be a simple:
id
reminder_id
user_id
status
event_at

With this you can query the transaction table to determine if a reminder is done. If the reminder is to run once (or for a limited period of time), then you can update the is_deleted value in Reminder to keep lookups clean.
Mind you, I do tend to overthink these things before the first line of code is even written …


Troubleshooting a slow performing encrypted stored procedure I have a stored procedure which is part of a vendor supplied system. The stored procedure is encrypted so we can't see the code.

The stored procedure has all of a sudden started to run slow.

The first thing I would look to do is look at the execution plan to try to work out where the problem is, however, there doesn't seem to be a way to show the plan (sp_Whoisactive is NULL in the query_plan field)

I might also try to update statistics but I have no way of seeing what tables the stored procedure uses.

The wait stats for the query do not show anything obvious

Is it realistically possible to do any sort of troubleshooting on this query without having an un-encrypted version?

Notwithstanding that your question is about how to troubleshoot without decrypting the stored procedure, I would suggest that you do decyrpt the stored procedure so you can do troubleshooting as required, without guessing what's going on.

"Encrypted" stored procedures in SQL Server are actually not encrypted AT ALL.  They are simply obfuscated using a pretty simple method.  This means you can very easily de-obfuscate them and see the source code.  Having said that you might violate the terms of the End-User-License-Agreement included with the vendor's software.

Use the details found in this question about how to de-obfuscate the "encrypted" stored procedure.

If the EULA forbids decrypting the stored proc, the best course of action would be to contact the vendor to have them do the performance troubleshooting.   You do have a maintenance contract, right?


Can Extended Events be used instead of Auditing? In BOL it is mentioned:

Does this mean Extended Events can capture all the events that are traced by auditing? Can we get all the functionalities of auditing just by using XEvents?

Extended Events can capture all the events that are traced by auditing?

Yes and no. The built in auditing framework is built upon the extended events framework. This means any of the built in auditing will be serviced via the XE framework, assuming it is setup.

  Can we get all the functionalities of auditing just by using XEvents?

You can get most, but it's not 100% coverage. Since the auditing is built upon the framework there would be the possibility, however not all auditing events have a 1 to 1 or even a correlated mapping to non-auditing based extended events.


Select * on inner join with common columns I have 3 tables (mysql db) on which I want to do an inner join. The problem is that these tables have common columns. I know I could use aliases to differentiate them (select A.x as Ax, B.x as Bx, C.c as Cx ...) but this requires to list the columns. However it is not really an option. Is there a way I could give an alias to all the columns of a table at once?

Something like select a.* as a*?

You need to specify every column by your self.

Example : Table Country and City both have a name column

SELECT
    a.Name AS CountryNane, b.Name AS CityName, c.*
FROM
    Country a
        JOIN
    City b ON a.Code = b.CountryCode
        JOIN
    CountryLanguage c ON a.Code = c.CountryCode;


Use variable on Sql Select I have this query:

SELECT *
FROM (
    SELECT @z := ticketing_ticket.id_ticket_category as id, ticketing_ticket.email,
    CONCAT(
          TIMESTAMPDIFF(day,date_create,date_close) , ' jours '
    ) AS 'temps de traitement '
    FROM ticketing_ticket
    WHERE DATE(date_close) = CURDATE()
) AS X
CROSS JOIN (
    SELECT GROUP_CONCAT(T2.label SEPARATOR ';') AS 'Domaines', @z as id
    FROM (
        SELECT @r AS _id,
             (SELECT @r := id_parent
              FROM ticketing_category
              WHERE id_category = _id) AS parent_id,
              @l := @l + 1 AS lvl
        FROM ( SELECT @r :=@z , @l := 0) vars
        CROSS JOIN ticketing_category m
        WHERE @r <> 0
    ) T1
    JOIN ticketing_category T2
        ON T1._id = T2.id_category
) AS Y;

Output:

+----+------------------+----------+-----------------------------+----+
| ID |       EMAIL      | DURATION |           DOMAINS           | ID |
+----+------------------+----------+-----------------------------+----+
| 35 |  p.p@yopmail.com | 65 jours | Elec_Domain;Help_Sub_Domain | 38 |
+----+------------------+----------+-----------------------------+----+
| 36 | test@yopmail.com | 63 jours | Elec_Domain;Help_Sub_Domain | 38 |
+----+------------------+----------+-----------------------------+----+
| 28 |  admin@admin.com | 29 jours | Elec_Domain;Help_Sub_Domain | 38 |
+----+------------------+----------+-----------------------------+----+
| 38 |   test@test.com  | 21 jours | Elec_Domain;Help_Sub_Domain | 38 |
+----+------------------+----------+-----------------------------+----+
| 38 |   test@test.com  | 21 jours | Elec_Domain;Help_Sub_Domain | 38 |
+----+------------------+----------+-----------------------------+----+

My problem is i want to use the id on my second sub-select but on the output is the same id.
What's wrong on my request ?

The problem is that values of the UDVs is restricted to the statement an do not passed across different SELECTs and JOINs. If you want to refer to the values calculated in the one SELECT from another SELECT you have to use values stored in the resulting table from the previous statement. Try the next form of the second operand of the CROSS JOIN:

SELECT GROUP_CONCAT(T2.label SEPARATOR ';') AS 'Domaines', X.id as id
    FROM (
        SELECT @r AS _id,
             (SELECT @r := id_parent
              FROM ticketing_category
              WHERE id_category = _id) AS parent_id,
              @l := @l + 1 AS lvl
        FROM ( SELECT @r := X.id , @l := 0) vars
        CROSS JOIN ticketing_category m
        WHERE @r <> 0
    ) T1
    JOIN ticketing_category T2
        ON T1._id = T2.id_category
) AS Y;

Here I've replaced all references to the @z by X.id that contains the values of @z calculated in the first SELECT statement.

Just to make it clear: only the last calculated value of the @z UDV is passed across the statements and then it stay unchanged. Therefore you have the same value in the last column of the resulting table. And even now I'm not sure the result would be correct because of complexity of the whole query.


Sql Server consuming lot of memory Creating Bottleneck for my web application Hosted in the same server HI I have a server with 64 GB Ram and I have assigned around 52GB to the SQl Server itself. However Even this has effected the  performance of the server and the web application hosted in the same server is performing poorly because of this, Can anyone suggest me a way to handle this situation
I can give you some of the major activities that can effect server
A)I Constantly import(dump) 5million plus records 3 hour , Compare it to the current 7 Million records and process the new ones.  I use lot of temporary tables for this process ... are these reasons for my bottle neck if so what can i do?
b) My web applications and database server are on the same server is this a bad practice?

I'll answer your second question first, which is typically it's ideal to host your SQL Server instance on its own dedicated server, to maximize the resources it has available to it without causing contention for other applications or battling contention from other applications.
The Memory, CPU, I/O, and Disk Space available on the server are all key resources utilized by a SQL Server instance, and some of them are even eagerly acquired and held onto to maximize efficiency and performance of that SQL Server instance. For example, SQL Server will generally continually acquire more Memory (out of the total allocated to the instance) over time for multiple uses of caching, and generally won't release that Memory back to the server, unless the Windows OS indicates a critical Memory pressure event, or the server or instance is restarted.
To answer your first question, we can't accurately tell you the issue you're currently facing without more details and context, unfortunately. It's too complicated, and broad of a question, without observing actual concrete examples with metrics. But if you're certain
your SQL Server instance is affecting performance of your web application, then you're likely under-provisioned resource-wise. 12 GB of Memory sounds suspiciously low for both the OS (which likely needs a minimum of 4 GB itself) and the web application itself.
Aside from that, I wouldn't doubt you're seeing other resource contention either from the CPU or I/O. Your best bet is to try to isolate a specific example of a performance issue your web application is experiencing so that it's reproducible, and then measuring what's actively running on your SQL Server instance concurrent to those performance issues of the application. I find Adam Machanic's sp_whoisactive very helpful as a starting point to see what is currently running on my SQL Server at any given instant.


How to find Rank of row in MySQL I am new to MySQL. I know that ROW_NUMBER() OVER (PARTITION) will not work in MySQL. I want to convert the code from SQL Server to MySQL:

SELECT  *,
    ROW_NUMBER() OVER (
        Partition By X.TPID,originTLA, destinationTLA
        ORDER BY packageStartDate,totalPackageSavingsPct desc
    ) AS ttl_perct_rnk
FROM TABLE

I want to convert this code to MySQL.

In Most of the cases the requirement to find rank is based on a single column. But here we have to find rank based on three columns. It is easy to find rank based on single column. But I am not sure how can we implement the rank based on three column or multiple columns.

This what you are looking for?:

SELECT  @rank := @rank + 1 AS 'Rank', province, city, population
    FROM  canada
    JOIN  ( SELECT  @rank := 0 ) AS init
    WHERE  population > 0
    ORDER BY  province, city, population DESC;

+------+----------+---------------+------------+
| Rank | province | city          | population |
+------+----------+---------------+------------+
|    1 | Alberta  | Airdrie       |      24673 |
|    2 | Alberta  | Athabasca     |       2539 |
|    3 | Alberta  | Banff         |       7502 |
|    4 | Alberta  | Barrhead      |       4430 |
|    5 | Alberta  | Bassano       |       1388 |
|    6 | Alberta  | Beaverlodge   |       2219 |
|    7 | Alberta  | Black Diamond |       3663 |
|    8 | Alberta  | Blackfalds    |       3198 |
|    9 | Alberta  | Blairmore     |       2095 |
|   10 | Alberta  | Bon Accord    |       1611 |
|   11 | Alberta  | Bonnyville    |       6003 |
|   12 | Alberta  | Bow Island    |       1792 |
|   13 | Alberta  | Brooks        |      12745 |
|   14 | Alberta  | Calgary       |     968475 |
|   15 | Alberta  | Calmar        |       2000 |
|   16 | Alberta  | Camrose       |      15687 |
...
|  708 | Saskatchewan              | Wilkie                    |       1198 |
|  709 | Saskatchewan              | Wynyard                   |       1793 |
|  710 | Saskatchewan              | Yorkton                   |      15172 |
|  711 | Yukon                     | Haines Junction           |       1148 |
|  712 | Yukon                     | Mayo                      |        540 |
|  713 | Yukon                     | Whitehorse                |      19616 |
+------+---------------------------+---------------------------+------------+

Or you can upgrade to MySQL 8.0 or MariaDB 10.2 to get CTEs and Windowing functions.


WHERE clause on a TIMESTAMP column causes serious performance penalty I have a pages and a page_views tables with 100k~ & 4M~ records respectively.

I run a query to get the top pages that have the most page_views:

    select `pages`.`id`, `pages`.`host`, `pages`.`embed_code_id`, `views`.`count`
from `pages`
inner join `embed_codes` on `pages`.`embed_code_id` = `embed_codes`.`id`
inner join (
    select `page_id`, COUNT(*) AS count
    from `page_views`
    where `page_views`.`created_at` between '2018-12-25 13:20:35' and '2018-12-26 13:20:35'
    group by `page_id`
    ) as views on `views`.`page_id` = `pages`.`id`
where `embed_codes`.`id` in (512)
and `embed_codes`.`account_id` = 173
order by `views`.`count` desc
limit 6

This query takes more than 9 seconds to run, but when i remove the WHERE clause on page_views.created_at, it takes only 700ms~, which is acceptable i guess, given the tables sizes.

NOTE - page_views.created_at is indexed.

Any ideas why the WHERE clause is causing this slowness? Any way to avoid it?
Thanks.

It looks like with the WHERE condition the system is searching by the index on 'created_at' and when you eliminate this condition it searches by 'page_id'.
if you add a new composed index on 'page_id' and 'created_at' or in the other order, the optimizer possibly will find the correct execution plan.


How to pass, specific day month and year in the below query? I have registration according to the data in a table and I want to show the whole month, I found the easy solution with below query but I do not know how can I pass specific day, month and year in below query?

SELECT AAA.date_field,
    IFNULL(BBB.val,0) val
FROM
(
    SELECT date_field
    FROM
    (
        SELECT MAKEDATE(YEAR('2014-02-06'),1)
             + INTERVAL (MONTH('2014-02-06')-1) MONTH
             + INTERVAL daynum DAY date_field
        FROM
        (
            SELECT t*10+u daynum FROM
            (SELECT 0 t UNION SELECT 1 UNION SELECT 2 UNION SELECT 3) A,
            (SELECT 0 u UNION SELECT 1 UNION SELECT 2 UNION SELECT 3
            UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7
            UNION SELECT 8 UNION SELECT 9) B ORDER BY daynum
        ) AA
    ) AA WHERE MONTH(date_field) = MONTH('2014-02-06')
) AAA LEFT JOIN (SELECT date_field,val FROM school) BBB USING (date_field)

Can you please help me how can I pass the day month and year in the above query?

First create a table for as calendar in your database and fill it with few years data.

CREATE TABLE `calendar` (
`date_field` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Then prepare your query  like :

set @dy='6',@mnth='2',@yr='2016';

select c.date_field,IFNULL(t.val,0) val
from calendar c
  left outer join
   (SELECT date_field,val FROM school) t
    USING (date_field)
 where if(@dy='',1,day(c.date_field)=@dy)
   and if(@mnth='',1,month(c.date_field)=@mnth)
   and if(@yr='',1,year(c.date_field=@yr);


What's a good way to get SQL Server to release memory to the operating system on a dev machine? On my dev box I have my SQL Server configured with a high maximum amount of memory (8GB of 16GB). This is required for many development tasks.

Sometimes I want my memory back, though. I don't want to leave 8GB consumed permanently.

What's a good way to get SQL Server to suddenly release all it's memory to the operating system?

DBCC DROPCLEANBUFFERS does not do that. It merely marks clean buffers as available.

Restarting SQL Server is a problem because this causes a 1 minute database recovery to be run due to this bug/feature: Stopping the SQL Server service causes databases to not shut down cleanly (please vote for that connect item; seems like an egregious bug).

I routinely increase and decrease the max server memory configuration option for the SQL Server 2005-2016 instances running on my laptop (with lock pages in memory enabled). Has never caused me any problems, and memory always seems to be released quickly. I also stop, start, and restart instances as I find I need them (using SQL Server Configuration Manager, which I keep open).

As far as checkpoint is concerned, how you configure that depends on version. Manual checkpoint with a duration target has been available since 2005 (though it is undeniably more effective in later versions).

Running a manual checkpoint <duration> per database before shutdown will help spread the I/O load and reduce recovery time on startup. Managed checkpointing will also reduce I/O needed when a memory shrink is requested because there will be fewer dirty pages to write to persistent storage. There is no particular benefit to dropping clean buffers before reducing the max memory setting that I have noticed.

Not using 'lock pages in memory' may increase the rate at which SQL Server can release memory, but I rely on that to avoid paging SQL Server memory to disk. If you don't need it for that purpose, you might choose to run without it.


Copy snapshot files from Publisher, Paste and Apply on subscriber in SQL Merge Replication I read an excellent article about copying the snapshot files for Transactional replication from Publisher to Subscriber and Apply them on subscriber.

http://blogs.msdn.com/b/repltalk/archive/2010/03/07/tips-to-improve-performance-when-applying-snapshot-in-transactional-replication.aspx

But the focus of this article is directed to Transactional Replication.

I want to know how we can copy the snapshots from publisher, paste them on the subscriber and apply them on subscriber. I am using Merge Replication with push subscriber, Distributor and Publisher are on the same server.

Yes - With Merge we can copy the snapshot files to the Subscriber and apply the snapshot locally using the Merge Agent -AltSnapshotFolder parameter.


Good explanation of cascade (ON DELETE/UPDATE) behavior I don't design schemas everyday, but when I do, I try to setup cascade updates/deletes correctly to make administration easier. I understand how cascades work, but I can never remember which table is which.

For example, if I have two tables - Parent and Child - with a foreign key on Child that references Parent and has ON DELETE CASCADE, which records trigger a cascade and which records get deleted by the cascade? My first guess would be the Child records get deleted when Parent records are deleted, since Child records depend on Parent records, but the ON DELETE is ambiguous; it could mean delete the Parent record when the Child record is deleted, or it could mean delete the Child record when the Parent is deleted. So which is it?

I wish the syntax was ON PARENT DELETE, CASCADE, ON FOREIGN DELETE, CASCADE or something similar to remove the ambiguity. Does anyone have any mnemonics for remembering this?

If you like the Parent and Child terms and you feel they are easy to be remembered, you may like the translation of ON DELETE CASCADE to Leave No Orphans!

Which means that when a Parent row is deleted (killed), no orphan row should stay alive in the Child table. All children of the parent row are killed (deleted), too. If any of these children has grandchildren (in another table through another foreign key) and there is ON DELETE CASCADE defined, these should be killed, too (and all descendants, as long as there is a cascade effect defined.)

The FOREIGN KEY constraint itself could also be described as Allow No Orphans! (in the first place). No Child should ever be allowed (written) in the child table if it hasn't a Parent (a row in the parent table).

For consistency, the ON DELETE RESTRICT can be translated to the (less aggresive) You Can't Kill Parents! Only childless rows can be killed (deleted.)


Delayed durability - can data loss be prevented in case of crash This book suggests that
"Data loss is possible with delayed durability because you might lose committed transactions. If SQL Server crashes after a transaction commits, but before the 60KB buffer is filled, that transaction cannot be recovered because it is lost."

However wont the transactions be available on the transaction log and can be replayed for recovery?

https://books.google.co.in/books?id=5Es3DwAAQBAJ&pg=PT159&lpg=PT159&dq="data+loss+is+possible+with+delayed+durability"&source=bl&ots=Mwmh4cKa6G&sig=tctRb18itYQyBxo7lLhkT1w4-z4&hl=en&sa=X&ved=2ahUKEwjAyau784jeAhWHMo8KHW1XCi4Q6AEwAHoECAEQAQ#v=onepage&q="data%20loss%20is%20possible%20with%20delayed%20durability"&f=false

When you enable delayed durability, transactions aren't written to the transaction log file before they are committed.

That's why transactions aren't available in the log, and they will be permanently lost in that scenario.


How to configure postgresql to use multiple cores? How can I configure postgresql to use multiple cores in a single machine , is it automatically uses it ?

PostgreSQL automatically uses multiple cores to support multiple database connections, if you have multiple cores.  No further configuration is necessary.