How can I handle a huge amount of logging generated from frequent table rebuilds? We have several tables which we "refresh" frequently by rebuilding them in a staging-table, then performing a metadata-switch to the production-table using the ALTER TABLE <source> SWITCH INTO <target> statement.

These operations are causing a huge amount of logging, and we're not sure how to handle it. For now we just moved these tables and operations to a new database using the SIMPLE recovery model, but I would love to hear alternative solutions to this problem.

How do you suggest we handle the massive log generation as a result of frequent table rebuilds?

one option (if feasible for you considering the downside) would be to change the database to bulk logging and bcp into the target table from a view reading the source tables.  the actual logging should be minimized, but the log backup will still be huge as it will include the changed extents.  a downside of bulk logging is that is compromises the ability to point in time recover to a time frame during the bulk logging operation.  make sure you take a log backup immediately after the bulk import


Mongo crash after 2 seconds of starting My mongod service crashes within 2-5 seconds of starting. I don't understand why.

Here are the systemctl logs:

  systemctl status mongod.service

mongod.service - High-performance, schema-free document-oriented database
Loaded: loaded (/usr/lib/systemd/system/mongod.service; enabled; vendor preset: disabled)
Active: failed (Result: exit-code) since Thu 2016-05-05 11:43:57 UTC; 4min 40s ago
Process: 7597 ExecStart=/usr/bin/mongod $OPTIONS run (code=exited, status=0/SUCCESS)
Main PID: 7601 (code=exited, status=14)

May 05 11:43:52 database13.choozon.net systemd[1]: Starting High-performance, schema-free document-oriented database...
May 05 11:43:52 database13.choozon.net mongod[7597]: warning: bind_ip of 0.0.0.0 is unnecessary; listens on all ips by default
May 05 11:43:52 database13.choozon.net mongod[7597]: about to fork child process, waiting until server is ready for connections.
May 05 11:43:52 database13.choozon.net mongod[7597]: forked process: 7601
May 05 11:43:54 database13.choozon.net systemd[1]: Started High-performance, schema-free document-oriented database.
May 05 11:43:57 database13.choozon.net systemd[1]: mongod.service: Main process exited, code=exited, status=14/n/a
May 05 11:43:57 database13.choozon.net systemd[1]: mongod.service: Unit entered failed state.
May 05 11:43:57 database13.choozon.net systemd[1]: mongod.service: Failed with result 'exit-code'.

So far I have:

checked permissions (everything looks good)
checked the service
tried deleting mongo.lock

mongod appears to start without any errors, but then fails after a couple of seconds.

Any ideas ?

Search for mongod.lock and remove it and restart the service.

Find out the Mongo data path cat /etc/mongod.conf | grep dbPath
Look for mongod.lock file in it.
If its there then delete the file and restart the server.


Is there anyway to change the standard SQL SERVER reports? And/OR See the SQL Behind it? SQL SERVER (through Management Studio) offers quite a few standard reports, but I don't see how you could change the reports. I don't even see an option for extracting/obtaining the SQL Code behind it.

Is there any way to achieve this? Specifically, I am more interested in security reports (although it'd be nice to get code for all of them).

Thank you in advance

Yes, run a Profiler trace while you run the report. It's just plain old T-SQL.

(Source: that's how I wrote the Performance Dashboard Reports chapter in the book SQL Server 2008 Internals and Troubleshooting.)


SQL Server Timeout: Drop and Recreate Index Solves the Issue After having sql timeout issues with one of our queries we added a few indices that then made the query run very quickly and got rid of any timeout out issues we were having.  The strange thing is after about a month of use, the query starts to timeout again.  If we drop the same set of indices and recreate them, the query starts running very quickly again.

The indices are nothing fancy:

CREATE NONCLUSTERED INDEX [IX_Transaction_BillingAccountId] ON [dbo].[Transaction]
(
     [BillingAccountId] ASC
)
WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

Any ideas as to why this would happen? We are using SQL Server 2008 Express.

There is possibility of fragmentation issue with the index.

Check possibility of index fragmentation -

Here is a simple query to check fragmentation on your existing indexes:

DECLARE @DBNAME VARCHAR(130);
SET @DBNAME = 'MYDBNAME';

DECLARE @DBID INT;
SET @DBID = DB_ID(@DBNAME);

SELECT
OBJECT_ID AS objectID
, index_id AS indexID
, avg_fragmentation_in_percent AS fragmentation
, page_count
INTO #indexDefragList
FROM
sys.dm_db_index_physical_stats
(@DBID, NULL, NULL , NULL, N'Limited')
WHERE
index_id > 0
OPTION (MaxDop 1);

SELECT
i.[name] as indexname,
d.fragmentation,
d.page_count
FROM
#indexDefragList d
INNER JOIN sys.indexes i
ON d.objectid = i.object_id
ORDER BY
d.fragmentation DESC

DROP TABLE #indexDefragList

This will return a list of all indexes in your current DB with their fragmentation in %.

Rebuilding of indexes will solve the problem.

Rebuilding of indexes is not supported automatically in any version of Microsoft SQL Server - the reason being is that rebuilding indexes can potentially be very expensive operations, and so need careful scheduling and planning.

In many environments special scripts will be written to handle this, for example:

http://weblogs.sqlteam.com/tarad/archive/2008/09/03/Defragmenting-Indexes-in-SQL-Server-2005.aspx

Note that whilst SQL can automatically update statistics for you in many cases there is a performance gain to be had by managing these more carefully as well.

You can easily build a script to automatically rebuild or reorganise them. There is a great article from SQLFool on this including a complete pre-made script.

Reference link


Contents of transaction log file in more detail I have a question regarding transaction log (let’s just call it LDF for short) contents. I am assuming a database with full recovery model.

I have read that LDF file contains (logs) each and every operation to the database (that is in full recovery mode). How is it different from logging during BEGIN TRAN; COMMAND(s); COMMIT? I am asking because apparently you can roll back transactions, but you cannot roll back standard commands (in full recovery mode).

I guess that during transaction the contents being logged into the LDF file are different than in regular full recovery logging. Is that right? How is it different? Is it only the inclusion of “undo” operations for each action?

On a related note, I have heard that there are commercial tools to "rollback/undo" standard queries using full recovery LDF file. How do they do it? Do they analyze the LDF contents and try to come-up with inverse/undo operations?

The difference is that what you call "standard commands" have implicit transactions (as in "not explicit" and not real implicit transactions which mean something different), so every time you issue an INSERT command without an explicit transaction, it will open a transaction, insert the data and automatically commit. This is called an autocommit transaction.

This is also why you can't rollback this INSERT: it's already committed. So the rule is the same as explicit transactions: you can't rollback once they've been committed.

You can see what I mean directly from inside SQL Server.

Microsoft ships SQL Server with a DMF called sys.fn_dblog that can be used to look inside the transaction log of a given database.

For this simple experiment I'm going to use the AdventureWorks database:

USE AdventureWorks2008;
GO

SELECT TOP 10 *
FROM dbo.Person;
GO

INSERT INTO dbo.Person (FirstName, MiddleName, LastName, Gender, Date)
VALUES ('Never', 'Stop', 'Learning', 'M', GETDATE());
COMMIT;

BEGIN TRAN;
INSERT INTO dbo.Person (FirstName, MiddleName, LastName, Gender, Date)
VALUES ('Never', 'Stop', 'Learning', 'M', GETDATE());
COMMIT;
GO

SELECT *
FROM sys.fn_dblog(NULL, NULL);
GO

Here I'm doing two inserts: one with and one without an explicit transaction.

In the log file you can see that there's absolutely no difference between the two:

The red one is the INSERT within an autocommit transaction and the blue one is the INSERT with an explicit transaction.

As for the 3rd party tools you mention, yes they analyse the database log and generate normal T-SQL code to "undo" or "redo" the operations. By normal I mean they don't do anything special other than generate a script that will have the effect of doing exactly the opposite of what is in the log file.


PostgreSQL replication I have two projects working on different databases. The problem is: I need to replicate changes in the databases only for a few tables. I need it to synchronize the data.
Need an advice how can I solve it.
I have PostgreSQL 9.1 DB servers, the replication schema is master-master. What I found is:

Rubyrep
Bucardo
Postgres-XC

If you need to replicate only a few tables master-master, then Bucardo is probably your best bet.


query discrepancies on very large postgres table I have a fairly large table (25m entries) which stores tracking data, I am not a postgres admin by trade so this may be a simple problem.

these two queries although similar are taking massively different amounts of time and I am not sure why.

SELECT MAX(logdatetime) FROM tlog WHERE pointid = 'and.andrew' (5100ms)
SELECT MAX(logdatetime) FROM tlog WHERE pointid = '359710040139645' (41ms)

logdatetime is indexed, and I ran a VACUUM and REINDEX on the table when I noticed the discrepancy, but it hasn't resolved the issue.

Here is the EXPLAIN ANALYZE output for each of the queries:

--- and.andrew
Result  (cost=35.20..35.21 rows=1 width=0) (actual time=1924.811..1924.811 rows=1 loops=1)
  InitPlan 1 (returns $0)
    ->  Limit  (cost=0.00..35.20 rows=1 width=8) (actual time=1924.804..1924.804 rows=1 loops=1)
          ->  Index Scan Backward using logdatetime on tlog  (cost=0.00..2434879.14 rows=69168 width=8) (actual time=1924.800..1924.800 rows=1 loops=1)
                Index Cond: (logdatetime IS NOT NULL)
                Filter: ((pointid)::text = 'and.andrew'::text)
Total runtime: 1924.853 ms

--- 359710040139645
Result  (cost=1.44..1.45 rows=1 width=0) (actual time=32.525..32.525 rows=1 loops=1)
  InitPlan 1 (returns $0)
    ->  Limit  (cost=0.00..1.44 rows=1 width=8) (actual time=32.518..32.518 rows=1 loops=1)
          ->  Index Scan Backward using logdatetime on tlog  (cost=0.00..2434879.14 rows=1688018 width=8) (actual time=32.513..32.513 rows=1 loops=1)
                Index Cond: (logdatetime IS NOT NULL)
                Filter: ((pointid)::text = '359710040139645'::text)
Total runtime: 32.568 ms

The column pointid is defined as character varying (50), in case that matters.

The output from EXPLAIN ANALYZE is very helpful for understanding this. In particular, note these two lines:

->  Index Scan Backward using logdatetime on tlog
    (cost=0.00..2434879.14 rows=69168 width=8)
    (actual time=1924.800..1924.800 rows=1 loops=1)

->  Index Scan Backward using logdatetime on tlog
    (cost=0.00..2434879.14 rows=1688018 width=8)
    (actual time=32.513..32.513 rows=1 loops=1)

I reformatted and added bold emphasis.

Since you only seem to have an index on logdatetime that's what the query planner is going to use. It seems promising that you only want a single row, with MAX(logdatetime), so the query planner thinks it's a good idea to start from the back of the index  (Index Scan Backward) and scan until the first row with matching pointid pops up.

This strategy works out just nicely for pointid = '359710040139645', but not so much for pointid = 'and.andrew'. The maximum logdatetime for this particular pointid is obviously rather old, so a lot of rows (1.688.018) have to be read before the first hit (which is the youngest row).

But still a lot better than a full table scan where you would need to read all 25m rows to identify the matching ones, then find the max among these. (Try deleting the index an rerun the query. Be prepared for a long wait.)

If you can afford adding indexes (some cost to write operations and considerable disk space for 25m rows) try adding an index on pointid instead, or better yet, this multi-column index:

CREATE INDEX tlog_pointid_logdatetime ON tlog (pointid, logdatetime DESC);

This would make your query fly.

If you don't want indexes to slow down your writes, you might create this index just for the purpose of running a couple of queries and delete it again. Index creation is pretty fast in PostgreSQL. This may pay even for just a few queries.


How to link my Azure VM running SQL server to my on-premise domain or Active Directory? I'm kinda new to cloud/azure. I have set up a VM running SQL Server using default templates and settings like dynamic IP and name etc.

Can I connect it to my on premise domain or AD?
What are the exact steps and prerequisites for it?

Yes you can.  You'll need to have a site to site VPN between Azure and your environment.

Once that's one you've got a couple of options.

You can change the DNS settings on the vNet to point to your office domain controllers.
Add domain controllers to Azure (with static IPs), then change the vNet to use the Azure DCs as for authentication.

Once one of those is done, reboot your VM and you should be able to add it to your domain. We do this for clients all the time, and we've done it with our
lab, and it works perfectly.

The product Azure Active Directory isn't needed to do this at all.


Backing up mongo database of about 120 GB size We have a mongo database of about 120 GB size. I have run mongodump using nohup and redirecting the logs to /dev/null about 3 days back, but the dump file is ~40GB in size now, and the dump is still running. Is this expected?

If yes, what is the approximate compression ratio for a mongo database? i.e. for a 120 GB database, how much is the backup file size going to be?

This would help me in estimating the time remaining for the dump to finish. I have no clue why it is taking up so much time, also, wanted to know if there is a faster/better way of backing up the mongo database (remote copy is not something i'm considering)?

We are running this on the live system, but are currently not using this database. So, effectively, mongodump is the only client that mongod is serving.

Answers originally left in comments:

Compressed size will depend on how compressible your data patterns inside your documents and collections are.  Mongo isn't going to backup the indexes so subtract whatever indexes you have from that 120GB and that's what it will backup, along with any profiling data you have saved in the DB itself.  There's a lot to consider when backing up mongo. V3 and 2.8 backup methods are similar. – Ali Razeghi

If you're not using the database, you could just stop the mongo service, and make a copy of the data directory - if you update on the same server. You stop mongo service, read upgrade info on mongo website. And start your new mongo, it will use your existing data directory.

If you do the same on another server, it's like an upgrade. In your script, save the mongo version, and install the same mongo version:

sudo apt-get install -y
    mongodb-org=3.0.8 mongodb-org-server=3.0.8 mongodb-org-shell=3.0.8
    mongodb-org-mongos=3.0.8 mongodb-org-tools=3.0.8

...and run an upgrade afterwards. Always check upgrade info on the mongo website!  – aldwinaldwin


Table design when one data is dependant of another and the second one has repeated values I have a products, and subproducts.

Like:

Product Identifier - Product Name - Subproduct Identifier - SubProduct Name
                 1       ProA              1                   SubProA1
                 1       ProA              2                   SubProA2
                 1       ProA              3                   SubProA3
                 2       ProB              1                   SubProB1
                 2       ProB              2                   SubProB2
                 2       ProB              3                   SubProB3
                 3       ProC              1                   SubProC1
                 3       ProC              2                   SubProC2
                 3       ProC              3                   SubProC3

As you can see, the subproduct identifier is not unique. My input is gonna be the product and the subproduct, and I need to return the names.

I did this:

  Table Products: ID, Name

  Table SubProducts: IDproduct, IDsubProduct, Name

But I know that is wrong, I wanted to make 3 tables, products, subproducts and a relation between the two, but I don't know how to design it.

If I was to save the product and subproduct into another table, I would have to save both values, that table would have to have the productID and subproductID columns, that's why I feel having a relation product subproduct table would be the best.

But I'm having a hard time doing it.

With the clarifications in your comments, that there are only 2 levels of products, e.g. only Products and SubProducts and no subproduct is related to two or more products, your design is fine.

I would only add two unique constraints on table SubProducts, on (IDproduct, IDsubProduct) and on (IDproduct, Name) - or make one of them PRIMARY KEY and the other UNIQUE:

CREATE TABLE Products
( IDproduct INT NOT NULL
, Name VARCHAR(100) NOT NULL

, CONSTRAINT Products_PK
      PRIMARY KEY (IDproduct)
, CONSTRAINT Product_Name_UQ
      UNIQUE (Name)
) ;

CREATE TABLE SubProducts
( IDproduct INT NOT NULL
, IDsubProduct INT NOT NULL
, Name VARCHAR(100) NOT NULL

, CONSTRAINT SubProducts_PK
      PRIMARY KEY (IDproduct, IDsubProduct)
, CONSTRAINT SubProduct_Name_UQ
      UNIQUE (IDproduct, Name)
, CONSTRAINT Products_SubProducts_FK
      FOREIGN KEY (IDproduct)
      REFERENCES Products (IDproduct)
) ;


DynamoDB: searching with primary key or range key, which way is faster? To those familiar with DynamoDB, I'm trying to make a table where items only have 2 entries. For example, . Most of the time I'll be searching the user_id with a facebook_id on hand.

Amazon's DynamoDB only provides the ability to search based on 2 keys, the primary key and the range key.  I have 2 options; either making the user_id or the facebook_id the primary key.  Primary and Range keys differ on the matching algorithm used.

I'm just curious which way is more efficient in this case?  Searching with the primary key, or searching with the range key, or is there really no difference?

Could we add DynamoDB as a tag?  As DynamoDB is fairly new, I'll not be surprised if no-one knows the answer.

Are you trying to determine if to query only using the hash OR only the range key? Unless I'm misunderstanding your question, you are not able to to perform a query with just the range keys. For example, if your range key was the date, you cannot query with just two dates, you MUST also specify the hash key. Remember when using both hash and range keys, the primary key IS both the hash and range, however the range can be optional.

With that said, I suggest specifying the facebook_id as the hash and your user_id as an attribute not the range. Use this if only your logic is using the facebook_id as a lookup id to your application specific user_id.


Mapping 5 columns to 1 (ID, dimension) table without 5 joins? I'm using survey monkey's API to dump survey data into a SQL data warehouse.

So I'm dumping response data into a table 'resonses' thusly:

survey_id, Q1, Q2, Q3, Q4, Q5

survey3944, 848948344, 430984384, 43094839, 238094983, 2380284

Then I have a dimensional table 'answer_key' that has the id/ text combos. Such as

ID, text

848948344, 5 - very satisfied

I'm wondering how I can join the 5 answer columns above into meaningful text (or at least the integer) without doing 5 joins.

I mean say I have the table 'responses' and the table 'answer_key'.

My initial thought would be go through the motion:

Select r.survey_id, a.text, a2.text, a3.text, a4.text, a5.text
from responses r
inner join answer_key a on r.q1 = a.id
inner join answer_key a2 on r.q2 = a2.id
inner join answer_key a3 on r.q3 = a3.id
inner join answer_key a4 on r.q4 = a4.id
inner join answer_key a5 on r.q5 = a5.id

Is that the best way to accomplish this? Just wondering. Especially if the survey is expanded to 10 questions, or what not.

I realize I can have a row for each question response rather than each survey for the table structure --- that's a possibility but I think it's a bit more difficult given the structure of the JSON, but maybe it's more logical.

Normalise the table responses so there is 1 question per row. Then you are also not limited to a number of questions per survey.


Concatenating rows into a single string query running for 5 hrs and counting I have a table with 2.6m records. It looks like this:

email                           prject_name
rafael.nadal@xyz.com              lab1
rafael.nadal@xyz.com              lab2
rafael.nadal@xyz.com              lab3
TEST@TEST.COM                     shift1
TEST@TEST.COM                     shift2

But I want my table to look like this:

email                     project_name
rafael.nadal@xyz.com     lab1, lab2, lab3
TEST@TEST.COM            shift1, shift2, shift3

I have used this query

select distinct email ,
STUFF((Select ','+project_name
from dbo.[UMG sent 2016] as  T1
where T1.email=T2.email
FOR XML PATH('')),1,1,'') from dbo.[UMG sent 2016] as T2;

It has been running for 5 hours already.
How do I speed up the process?

As you don't care about the order of the concatenated items it would be quite easy to knock up a custom CLR aggregate to do this and it will likely out perform the XML method, there is an example of one in this article.

There is a quick and easy change you can make to your existing code though.

Instead of

SELECT DISTINCT email,
                STUFF((SELECT ',' + project_name
                       FROM   dbo.[UMG sent 2016] AS T1
                       WHERE  T1.email = T2.email
                       FOR XML PATH('')), 1, 1, '')
FROM   dbo.[UMG sent 2016] AS T2;

You could use

SELECT email,
       STUFF((SELECT ',' + project_name
              FROM   dbo.[UMG sent 2016] AS T1
              WHERE  T1.email = T2.email
              FOR XML PATH('')), 1, 1, '')
FROM   dbo.[UMG sent 2016] AS T2
GROUP  BY email;

The difference being that the first one calculates concatenated strings for all rows in [UMG sent 2016] and then removes duplicates for email,string. The second one finds distinct email first and then just performs the string concatenation work on those distinct values. So in your example data instead of performing the work 5 times (twice for test and 3 times for Nadal) then throwing away three of them it will just perform the work 2 times, once for each.


When I change the content of VARCHAR(32) from 32 chars to 0, will it save some space? When I change the content of VARCHAR(32) from 32 chars to 0, will it save some space?

The reason I'm asking it is because I store a hash of 32 chars to check something but after a while I'll not use it anymore, so if it takes some of DB space, I might change this field to empty ''.

According to the manual, it takes 1b plus the chars for strings with 255 chars or less, but what if we already have some data and change it to empty, does it save some space?

I'm currently using MySQL with InnoDB engine.

PS.: I'm a beginner yet, please bare with me.

You are leaving the datatype at VARCHAR(32), correct?  But updating the value to be the empty string, correct?

A 32-char hash takes 33 bytes -- 1 byte length plus 32 hex characters (1 byte per character).

The empty string will take 1 byte -- 1 byte length, plus no space for any characters.

However...  You are deleting stuff out of the middle of blocks in the middle of the table.  So don't expect that zapping a million hashes will necessarily shrink the table by 32MB.  There are too many things going on with blocking, free space, reuse of free space, etc, to say anything more than "it will free up some space for future usage".

I don't know where you got the 1KB info in that link -- the length is 1 byte, not 1 kilobyte.


I want to make a new column based on another column I don't want to make a new table; rather, I want my query to produce the desired output.

I have a query that produces a bunch of columns, and then a PROGRAM_ID column which only has 666 or 667. I want to make a select on top of this select that makes a new column, PATTERN, that has a 0 if the PROGRAM_ID is 666 and a 1 if it's 667. I know I need to use cases but I'm unsure of the syntax.

Use DECODE:

SELECT ID, PROGRAM_ID, DECODE(PROGRAM_ID, 666, 0, 667, 1, PROGRAM_ID)     AS PATTERN
FROM (
  SELECT ID, PROGRAM_ID
  FROM YOURTABLE
);

Documentation link to DECODE().

Using CASE, alternatively:

SELECT ID, PROGRAM_ID,
       CASE PROGRAM_ID WHEN 666 THEN 0
                       WHEN 667 THEN 1
                       ELSE PROGRAM_ID
       END AS PATTERN
FROM (
  SELECT ID, PROGRAM_ID
  FROM YOURTABLE
);

Example SQL Fiddle here.


When does SQL Server determine to use a new index? I have been performing some maintenance on a server that runs some software the company bought, so I can't really change how the queries have been written.  I have used some tools to determine some of the missing indexes on the server.  Using my tools, though, I didn't see which queries were indicating these.  I do, however, monitor how much the indexes are being written and read.

So, my question is, when adding new indexes that have been determined as missing, when does SQL Server notice the new indexes and determines whether they actually help the query?  Is it immediately after the index is created and the query is ran again, or is it more complicated?

when does SQL Server notice the new indexes and determines whether they actually help the query?

Immediately.  Creating an index causes a recompile because of a schema change.

Here's an illustration of this.  First to setup the objects in the test database:

use TestDatabase;
go

if exists (select 1 from sys.tables where object_id = object_id('dbo.TestTable1'))
    drop table dbo.TestTable1;
create table dbo.TestTable1
(
    Id int identity(1, 1) not null
);
go

insert into dbo.TestTable1
default values;
go 1000

if exists (select 1 from sys.procedures where object_id = object_id('dbo.GetIdFromTestTable1'))
    drop proc dbo.GetIdFromTestTable1;
go
create proc dbo.GetIdFromTestTable1
    @Id int
as
    select Id
    from dbo.TestTable1
    where Id = @Id;
go

As you would expect, running this stored procedure would result in a table scan:

exec dbo.GetIdFromTestTable1 500;
go

Now let's create an index that we know this query can benefit from:

create unique index IX_TestTable1_Id
on dbo.TestTable1 (Id);
go

Re-execute the stored procedure and take a look at the post-execution plan:

exec dbo.GetIdFromTestTable1 500;
go

We can see that our table scan turned into an index seek.  This shows that a new plan was generated.

Why?

We can trace this behavior with an XEvents session:

if exists (select 1 from sys.server_event_sessions where name = N'RecompileTracing')
    drop event session RecompileTracing
    on server;
go
create event session RecompileTracing
on server
add event sqlserver.sql_statement_recompile
(
    set
        collect_statement = 1,
        collect_object_name = 1
)
add target package0.event_file
(
    set
        filename = N'RecompileTracing.xel'
);
go

alter event session RecompileTracing
on server
state = start;
go

/*
alter event session RecompileTracing
on server
state = stop;
go

drop event session RecompileTracing
on server;
go
*/

The output we get from the above illustration/repro is that we had a sql_statement_recompile on the statement select Id from dbo.TestTable1 where Id = @Id; (condensed with removed whitespaces/tabs/newlines) and the recompile_cause is Schema changed.


Sql Server 2008 Database server Minimum Requirements Does anybody knows what is the base scenario for the sql server 2008 r2 minimum hardware requirements?. By base scenario I mean: Database Size, average # of transactions, number of users connected, etc...

Here are the min specs from Microsoft if that's what you're look for. It's not going to give you user counts and such: http://msdn.microsoft.com/en-us/library/ms143506.aspx


how to find who used SA for login in SQL Server? In an organization if a community used one SA account for a database. So, Is there a way in SQL server to find that person who used SA password and logged in as a user ?

community used one SA account for a database.

This is a big security risk, since your "SA" password is a known password in your organization. "SA" is the most elevated privilege account that if misused will cause a disaster and/or data breach by internal people.

I agree with @AaronBertrand, that you should not give "SA" password out to people you do not trust. IMHO, "SA" should be disabled (or atleast change its password on frequent basis), since there are other ways wherein you can give people access following principle of least privilege to get their work done. SQL Server does not have LAPS (like windows server now has), so you have to design and implement a process that changes "SA" password on frequent basis. Below are some things that you can do to limit sa use (see reference cited below for more information article by - K. Brian Kelley):

Set a hard to guess password.
Rename sa.
Disable sa.
Ensure that no other accounts exist named sa.

  So, Is there a way in SQL server to find that person who used SA password and logged in as a user ?

Given above points, SQL Server does not have an easy way of detecting what users/applications are using "SA" to connect. I have implemented a light weight server side trace that can be used to find out applications/users/host name that uses "SA" to connect. Script can be found here and below is the sample output :

For complete automation, you can use TRACE_FILE_CLOSE WMI event to trigger import of trace file into sql server database table.

Alternatively, you can gradually move away from SQL Server authentication to Windows Authentication.

Refer to :

SQL Server 2012 Security Best Practices --> (direct Word doc download)
Best Practices to Secure the SQL Server sa Account - by K. Brian Kelley
How to Get SQL Server Security Horribly Wrong


Table design for multi-type data The information model I am modelling from includes an "any type". For example a customer can have zero or more 'entries' each of which can have a number of values. Each value could be a either date, numeric, string or spatial. Note query performance is more important than insert performance.

I wondering if you have any ideas for table design.

My ideas are:
  (a) a single table encompassing all the types. Each record in entry_data would only use one column for the value. Also each valuetype column needs to be indexed. This seems a little wasteful (although I'm aware oracle won't index the null values )

create table entry (
  id number(12) primary key,
  name varchar2(256) not null unique,
  datatype varchar2(256) not null,
  customer_id not null references customer(id)
);

create table entry_data (
  id number(12) primary key,
  entryid number(12) not null references entry(id),
  strvalue varchar2(256),   -- index
  datevalue date,   -- index
  numvalue number,   -- index
  sdo_geometry geom  -- index
);

OR (b)  create a separate entry_data table for each type

create table entry (
  id number(12) primary key,
  name varchar2(256) not null unique,
  datatype varchar2(256) not null,
  customer_id not null references customer(id)
);

create table entry_data_str (
  id number(12) primary key,
  entryid number(12) not null references entry(id),
  strvalue varchar2(256),   -- index
);

-- etc

but I'm not sure if introducing more joins will impact the performance.

Any suggestions welcome.
Thanks.

Not sure why one of the answers made such bold statement .
Regardless: everything can be modeled in relational model

create table entry
(
  id number(12) primary key,
  name varchar2(256) not null unique,
  customer_id number not null references customer(id)
);

create table datatypes
(
 id number primary key,
 name varchar2(256) not null unique
);
create table entry_data (
  id number(12) primary key,
  datatype_id  number not null references datatypes(id),
  value     varchar2(256),  --or CLOB ,no index
  value_geometry geom  ,    -- no index
  --value_blob   blob -- if you neeed, no index
  entry_id number not null references entry(id),
);

your task is related to the multi-valued attributes task.
Datatypes table will abstract your datatypes, however 2 value** columns are created in entry_data due to the fact that your data can be different in nature(belong to different Classes), thus would need different storage. But as far as varchar2 vs. number vs.date -- all can be placed into varchar2 , just would need later on some casting. And with very simplistic logic of NVL in code you can determine which column was populated out of value or value_geometry.


how to show combined results of two tables? I want to show the result as combination of two.
Table 1

SELECT au.invoice`.`InvoiceID`, au.invoice`.`UserID`
FROM au.`invoice`
WHERE au.`invoice`.`InvoiceID` > 11126
-- total results 757

Table 2

SELECT nz.`invoice`.`InvoiceID`, nz.`invoice`.`UserID`
FROM nz.`invoice`
WHERE nz.`invoice`.`InvoiceID` > 11236
-- total results 757

I want result combined as table 1 result and table 2 results

SELECT au.invoice`.`InvoiceID`, au.invoice`.`UserID`, nz.`invoice`.`InvoiceID`, nz.`invoice`.`UserID`
FROM au_._invoice`
JOIN nz.`invoice` ON nz.`invoice`.`UserID` = au.invoice`.`UserID`
WHERE au.invoice`.`InvoiceID` > 11126
  AND nz.`invoice`.`InvoiceID` > 11236
-- total results 1000+

What I want it should be the same no of results because I just assigned new invoiceId to table 2, all the users have the same no of invoices.

Can you help me to achieve this result?

Thanks
Rajesh

Test

SELECT t1.InvoiceID, t1.UserID, t2.InvoiceID, t2.UserID
FROM ( SELECT InvoiceID, UserID, @num1 := @num1 + 1 rownumber
       FROM au.invoice, (SELECT @num1 := 0) variable
       WHERE InvoiceID > 1234
       ORDER BY UserId ) t1
JOIN ( SELECT InvoiceID, UserID, @num2 := @num2 + 1 rownumber
       FROM nz.invoice, (SELECT @num2 := 0) variable
       WHERE InvoiceID > 1234
       ORDER BY UserId ) t2 USING (rownumber)

fiddle

If the list of UserID values in both subqueries is the same with guarantee (although it is unclear why) you may use JOIN ... USING (rownumber, UserID). Or even use the enumeration while partitioning by UserID (using 2 variables in each subquery).


Should I place my Quartz Scheduler tables in a separate database? I am designing a web application that uses Quartz Scheduler to scheduler jobs.

I have Quartz configured to use a JDBCJobStore, so it automatically stores each created job in its tables.

I am wondering if I should create a separate database to store these Quartz tables in, or if I should just store these tables in my web application's main database.

My application's database is not that complex, with only a couple of tables.

I should also note that Quartz allows you to specify a table name prefix for all of its tables, for instance "quartz_".  So if I did store these tables in my application's main database, they would be easily distinguishable from the other tables.

My input is that it should be within the application database since it is part of the application. I would put them in a different schema however to keep them separate from the business tables but with only a couple tables, the table prefix should be sufficient too.


Logically Horizontale-Partitioned Tables I have two legacy tables, lets call them TableA and TableB. Both of them fall under the same schema design with identical columns, in other words you can union them directly as one table. The only difference is based on code column and you need to manipulate a specific table based on that code. So if the code falls under (1001, 1002,..) then it goes to TableA else goes to TableB. The reason they were partitioned intially was to reduce the cost in space and cpu but now these costs can be ignored (hardware is cheap)

The DBA can union both tables into one but unfortunate there are alot of legacy code (which is being constantly updated with new features) that explicitly specify the table name.

Is there anyway to in Oracle to solve this issue by merging the tables under one table for better administration? and bare in mind the developers can still query against the old tables and the new ones as well?

The answer is simple, as long as you can guarantee that self-joining the merged table will not affect all queries that hit the table.

Just merge the tables, then create a synonym for the old table names that points to the newly merged table.

eg:

create table mergedtable as ( select * from tablea union all select * from tableb );

drop table tablea;

drop table tableb;

create synonym tablea for mergedtable;

create synonym tableb for mergedtable;

The synonyms will allow the legacy code to "see" the new table as if it is the old one.


Recover MySQL database on windows with ibdata1, ib_logfile and mysql-bin.00000 files I have a problem with MySQL database in a WAMP environment. I dropped my database via phpmyadmin and now I am trying to recover it. I created a new database with the same name and I imported some data to correct the database, but it was not enough.

Now I need your help to restore data from the mysql-bin or ibdata1 directories. So please help me:

How can I recover data using the ibdata1, ib_logfile1, ib_logfile0 and mysql-bin.000001 files on Windows 7?

Basically, ibdata1 contains the tables metadata and change buffer and ib_logfiles are having innodb redo logs.

So the important thing is to being your ibdata1 first, the only way use any recovery software, or if you have MySQLdump then restore it.


Set the primary key for many to many relationship For the two relations, Customer and Equipment:
There is a limited number of equipments (stock)  and upon hiring I need to store the date of hire and the expected return date.

I sketched this:

Table Customer
customer_id

Table Equipment
equip_id
available_stock

Table Hire_Equipment
customer_id
equip_id
hiring_date
expected_return_date

What I'm not sure about is what is the primary key of Hire_Equipment?
If it is customer_id and equip_id then the customer wont be able to hire the equipment again in the future?
Did I mis-designed the schema? do you suggest a better approach?

In a practical system (as opposed to a textbook problem) you would want to have some kind of transaction identifier on any transaction like hiring one or more pieces of equipment.  Assuming you are running an equipment hiring business, your transactions would probably have to account for letting out multiple pieces of equipment at once.  Therefore you need to add an order table as follows:

Table Order
order_id
order_date

and your Hire_Equipment table would change as follows:

Table Hire_Equipment
customer_id (PK)
order_id (PK)
equip_id (PK)
expected_return_date

Note that the hiring_date has been normalised up to the Order level, but expected_return_date has been left at the item level, since you might want to allow a single order to contain a mixture of rental periods.

This design allows a customer to hire a piece of equipment more than once on a given date but it prevents hiring the same equipment more than once on the same order - which is what you would expect.


MongoDB 3.4 Sharding Error. “Cannot accept sharding commands if not started with --shardsvr” I have installed mongoDB 3.4.5 in centOS 7.2 machine. I am configuring sharding in standalone mode.

Steps I followed:

For Replica Sets:

mongod --replSet rs0 --dbpath /data/srd/rs0 --logpath /data/srd/rs0/log.rs0 --port 27000 --fork --logappend --smallfiles --oplogSize 50 &
mongod --replSet rs0 --dbpath /data/srd/rs1 --logpath /data/srd/rs1/log.rs1 --port 27001 --fork --logappend --smallfiles --oplogSize 50 &
mongod --replSet rs0 --dbpath /data/srd/rs2 --logpath /data/srd/rs2/log.rs2 --port 27002 --fork --logappend --smallfiles --oplogSize 50 &

For Shards:

mongod --replSet rs0 --shardsvr --dbpath /data/srd/srd0 --logpath /data/srd/srd0/log.srd0 --port 27020 --fork --logappend --smallfiles --oplogSize 50 &
mongod --replSet rs0 --shardsvr --dbpath /data/srd/srd1 --logpath /data/srd/srd1/log.srd1 --port 27021 --fork --logappend --smallfiles --oplogSize 50 &
mongod --replSet rs0 --shardsvr --dbpath /data/srd/srd2 --logpath /data/srd/srd2/log.srd2 --port 27022 --fork --logappend --smallfiles --oplogSize 50 &

For Config server:

mongod --configsvr --replSet configReplSet --dbpath /data/srd/cfgrep0 --logpath /data/srd/cfgrep0/log.cfgrep0 --port 27006 --fork &
mongod --configsvr --replSet configReplSet --dbpath /data/srd/cfgrep1 --logpath /data/srd/cfgrep1/log.cfgrep1 --port 27007 --fork &
mongod --configsvr --replSet configReplSet --dbpath /data/srd/cfgrep2 --logpath /data/srd/cfgrep2/log.cfgrep2 --port 27008 --fork &

To start Mongos:

mongos --configdb configReplSet/sit:27006,sit:27007,sit:27008 --port 26060

I am able to login the mongos shell and also able to add the shards. But while enabling the shard I am getting exceptions as below.

mongos> sh.enableSharding("test");
{
        "code" : 193,
        "ok" : 0,
        "errmsg" : "Cannot accept sharding commands if not started with --shardsvr"
}

First.. You cannot have two same name replica sets in one cluster (rs0), rename that second one to rs1. (Ports 27020 - 270122).

Error message is telling directly what you should do.. So, add --shardsvr to every started replica set.

From mongod --replSet xxx  to  mongod --shardsvr --replSet xxx

And of course after log in to mongoS service, you need to add those (two) replica sets to your cluster

sh.addShard("rs0/address1:27000, address2:27001, address3:27002")

And that other rs1 too..


Table optimize command crashed MySQL server I have a large 5GB table on one of my MySQL databases that I wanted to optimize.

I went into the console and ran the optimize command and since it's an InnoDB database it cloned the table to a temporary one (is that accurate?).

Anyways, I did not notice that I don't have enough space on my HD to complete this action so it failed in the middle of it:

+------------------+----------+----------+-------------------------------------------------------------------+
| Table            | Op       | Msg_type | Msg_text                                                          |
+------------------+----------+----------+-------------------------------------------------------------------+
| support.exported | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| support.exported | optimize | error    | The table '#sql-420a_65fb' is full                                |
| support.exported | optimize | status   | Operation failed                                                  |
+------------------+----------+----------+-------------------------------------------------------------------+

Now, my HD is 100% full since the temporary table that was created ate up all the storage I had left and I don't have any files I can delete from other locations on the HD to free the space.

I tried to restart the mysql daemon since I thought that might initiate a drop to the temporary table that was created and that way it will free some space, but what now happens is that I cannot start the server:

MySQL Daemon failed to start.
Starting mysqld:                                           [FAILED]

Is there a way to drop/delete the temporary table that was created during the optimize process without having the mysql server running?

Is there a way to drop/delete the temporary table that was created during the optimize process without having the mysql server running?

Unfortunately, no.

InnoDB is a very complex beast, and there's a lot of interplay between ibdata,.ibd, ib_logfile, and .frm files that means manual tinkering is, at best, extremely dangerous.

Before you try anything, make a complete copy of all MySQL files to a remote server or another directory if at all possible. When your situation is as delicate is this, it's easy to make a change that breaks the database irreparably.

Your best bet (as Boris mentioned above) is to try and salvage some space somewhere - anywhere. Compress or truncate some log files. Move some non-critical MyISAM tables (if you have any) off of the server until you have things at least partially under control. Move a few .myd files to another directory and symlink them if you need to (be really careful doing this to InnoDB files).

If you're not using using innodb_file_per_table, then you're  going to end up with a giant ibdata file and no free disk space. Rolando has given an extensive answer about steps to resolve this issue, and you should follow that rather than the rest of these steps.

If you are using innodb_file_per_table:

Once you have started MySQL successfully, you can check to see if the temporary table still exists (the easiest way to do this is to try desc `#sql-420a_65fb`, but there are also more complex ways).

If it still exists, you can drop it with:

drop table #mysql50##sql-420a_65fb

With innodb_file_per_table on, this will remove the temp table's .frm and .ibd files, and you will get some of your disk space back.

  I went into the console and ran the optimize command and since it's an InnoDB database it cloned the table to a temporary one (is that accurate?).

Yes, that's accurate. MySQL will create a temporary table with a name that looks something like #sql-12a_345b, and copy the data across it so that it's fresh and non-fragmented. Once it's complete, it would normally swap the tables around and then drop the old (fragmented) table).


When using expdp to create a snapshot of an oracle schema, how do I ensure that no post-export additions are retained upon using impdp to revert? I want to create a snapshot of an Oracle schema that I can revert back to after testing. Impdp and expdp seem to be the best fit as far as how I'd like to accomplish it, but the problem is that impdp only overwrites the existing schema without getting rid of any data that is not included in the dumpfile.

I could "DROP USER megan CASCADE," but wouldn't that involve using SQL *Plus and quitting after the drop before executing impdp? Is that even possible?

I tried that, and it didn't work. I may be doing it incorrectly, as I have about two days of experience working with and researching databases, but I haven't been able to find a way to drop the user without using SQL *Plus.

Questions:

How can I clean my schema so that my import is an accurate snapshot?
Is using "DROP USER" a viable option?
Would that even accomplish what I'd like to do?
Are there other options?

Thanks!

As always with Oracle, there are many ways to reach your goal. You might want to check out flashback database. This enables you to restore your database in a previously saved state and might be the easiest in your case. Using Flashback Database and Restore Points

Drop user cascade could also do the trick, followed by impdb as you described. What was the problem with the drop user action?

Try flashback database.


MySQL slow log logs every query instead of just slow queries I have the following MySQL configuration for logging the slow queries:

    [mysqld]
    ### Logging options
    general_log_file = /data/mysql-log/mysql_general.log
    log-error = /data/mysql-log/mysql_error.log
    slow_query_log_file = /data/mysql-log/mysql_slow.log
    log-warnings = 1
    log-queries-not-using-indexes = ON
    log-slow-verbosity = full
    long_query_time = 2
    log-slow-admin-statements = 1
    log-slow-slave-statements = 1
    general-log = OFF
    slow-query-log = ON

However, I see the following in my slow log file:
[

To my knowledge, the Query_time in the log file denotes the time query took to run, but it defers the log time mentioned in the config file.

Anyone here to help me explain as to why this is happening?

Maybe your mysqld got it's long_query_time value from another source. What happens if you run:

select @@long_query_time;

Note that I would consider setting the long_query_time to 0 to be good practice, particularly on Innodb databases. Consider the case where query A takes 1 second and runs a thousand times a day vs query B taking 20 seconds and run once a day; you'll get more benefit from tuning query A.


selecting rows that have n foreign key conditions satisfied I'm trying to filter a master table rows based on 2-more conditions from the relative foreign key relation, i came up with this which is working fine, but i just can't convince my self that it's that much of a fuss

select r.pageid
from
((
select co.pageid
from (cmscontentpublished as co join cmsnode as cm on co.PageID = cm.ID)
where cm.parentid=365 and co.Title='Title' and co.Content like '%test%'
)
union all
(
select co.pageid
from (cmscontentpublished as co join cmsnode as cm on co.PageID = cm.ID)
where cm.parentid=365 and year(co.DateContent) =2012
)) as r
group by r.pageid
having COUNT(*)=2

any ideas on how to do correctly

Try if that works for you

SELECT page_id FROM
(
SELECT co.pageid , count(case when year(co.DateContent) =2012 THEN 1 END) AS cnt1,
count(CASE WHEN co.Title='Title' and co.Content like '%test%' THEN 1 END) AS cnt2
FROM cmscontentpublished as co
JOIN cmsnode as cm on co.PageID = cm.ID
WHERE cm.parentid=365 AND ((co.Title='Title' and co.Content like '%test%') OR
(year(co.DateContent) =2012))
)a
WHERE (cnt1 + cnt2 )= 2
-- if you want page_id only if each condition is satisfied exactly once,
-- then where should look "cnt1 = 1 and cnt2 =1"


SQL Schema Change; Modification of Existing Table vs Creation of New We are planning the data schema for our project. Currently we have a table with classifications, but there is the possibility of a new classification in the future which would require a new field to store associated data.

Is it better to extend an existing table or create a child table with a FK reference to the existing table? I assume creating a child table with a FK to the existing table is the better option because the existing database schema remains stable.

We're committed to a relational database so an advice on choosing NoSQL is not what we're looking for.
Keep in mind that the changes aren't expected to be frequent.

If your changes are not frequent and your parent table does not have a lot of data, then you could just update your schema on that table.

However, potential downsides are changes in the schema could propagate to changes in stored procedures and views.  This could be because of adding columns or removing columns.

(Of course, any change made in a database schema, will have some ripple effects that must be coped with.)

I like the parent table to be as stabile as possible.  Having a related table or tables to provide new requirements is a good general rule according to how I look at schemas.

But it is definitely an opinion-based choice that will depend on what you are most comfortable with doing.


what could make examined_rows change radically? I have a mysql-based application which sometimes takes forever to execute a select statement. When this happens, the statement appears naturally in my slow query log:

# Query_time: 51.826420  Lock_time: 0.000143 Rows_sent: 3  Rows_examined: 15574557

Rows examined above tells me that a full table scan occured. However, normally, the same query examines at least one order of magnitude less rows. Namely, performance_schema.events_statements_history tells me exactly 982937. Naturally, the execution time for this query is one order of magnitude smaller in this case.

I checked that explain on this query appears to give the same result in both cases. I also checked that if I add a USE INDEX statement when this problem occurs, the execution time goes down back to normal but, normally, I do not need to add the USE INDEX.

One of the queries that exhibit this behavior is shown below:

SELECT
     COUNT(DISTINCT(t.id)),
     t.tag_3 AS group_0
FROM
     tasks AS t
WHERE
         t.message_time >= FROM_UNIXTIME(1486508400)
     AND t.message_time  < FROM_UNIXTIME(1487113200)
     AND (   (t.type = 12 AND t.site_id = 172)
          OR (t.type = 1  AND t.site_id = 172)
          OR (t.type = 8  AND t.site_id = 173) )
     AND t.tag_1 IN (74,75,76,77,78,79,80,81,99,263)
     AND t.tag_3 IN (302,303,305)
     AND t.tag_4 IN (315,316,317,318,319,320,321,322,323,351,352,357)
     AND t.site_id IN (172,173)
GROUP BY
     group_0;

The explain output:

+----+-------------+-------+-------+-----------------------------------------------------------------------+----------+---------+------+-------+----------------------------------------------------+
| id | select_type | table | type  | possible_keys                                                         | key      | key_len | ref  | rows  | Extra                                              |
+----+-------------+-------+-------+-----------------------------------------------------------------------+----------+---------+------+-------+----------------------------------------------------+
|  1 | SIMPLE      | t     | range | IX_site_id,IX_site_id_type,IX_tag_1,IX_tag_3,IX_tag_4,IX_message_time | IX_tag_4 | 5       | NULL | 24732 | Using index condition; Using where; Using filesort |
+----+-------------+-------+-------+-----------------------------------------------------------------------+----------+---------+------+-------+----------------------------------------------------+

All tables use innodb engines.

What could trigger such different behaviours at different points in time ? Namely, what could have caused a full table scan ?

What is happening -- The Optimizer picked a different way to perform the query.

You are at the whim of

The Optimizer -- It gets better with each new version.
The statistics -- ANALYZE TABLE may help, or could hurt.  (OPTIMIZE is overkill, and ends with ANALYZE; "never" use it.)
The constants in the query -- May lead the optimizer to pick a different explain plan.
The "phase of the moon", as I call this combination of whims.

If id is the PRIMARY KEY and there is no JOIN, you are asking for a lot of extra work: COUNT(DISTINCT(t.id)),; switch to simply COUNT(*).

When a query is run, some EXPLAIN plan is generated.
When you use EXPLAIN, a plan is generated.
There is no guarantee that they match.  (This is solved only in the latest version, wherein the EXPLAIN is optionally included in the slowlog.

Any single-column index on any of these may be best; there is no way to know without a lot more info on the distribution of the data:  tag_1, tag_3, tag_4, site_id, message_time.  I would have several 2-col "composite" indexes starting with each and ending with message_time.  This way, if the query actually says foo IN (just-one-item), it can move past foo and also use message_time.

Before discussing further, please add SHOW CREATE TABLE.


cardinality & column order in compound index (MySQL/InnoDB) I know the column order in a compound index matters if you want to query by a subset of the indexed-columns, but if you're query specifies values for all the indexed columns, is there any performance benefit to having a high-cardinality column earlier than a low-cardinality column? I vaguely remember reading something that suggested that was the case because it narrows down the result set quicker, but I can't find anything to back that up now.

I'm using MySQL w/ InnoDB. InnoDB uses clustered indexes, which might be relevant to my question, but I think it only does so for primary keys, which my index is not. The table looks something like this:

CREATE TABLE `my_table` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `ref_a_id` int(10) unsigned NOT NULL,
  `ref_b_id` int(10) unsigned NOT NULL,
  `is_active` tinyint(1) DEFAULT '1',
  PRIMARY KEY (`id`),
  UNIQUE KEY `index_my_table_on_ref_a_ref_b_is_active` (`ref_a_id`, `ref_b_id`, `is_active`)
) ENGINE=InnoDB AUTO_INCREMENT=2818259 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

In relation to my question, imagine ref_a_id has a higher cardinality than ref_b_id.

It is an old wives' tale that cardinality matters.

INDEX(a,b) and INDEX(b,a) perform virtually identically when in a BTree.  The depth of the BTree is the same.

What does matter is that both columns are searched with =, as in WHERE a=12 AND b=45.

The PRIMARY KEY in InnoDB is a BTree, and so is each secondary index.  The only difference is what else is in the leaf nodes.  The PK's leaf nodes contains all columns; a secondary index's leaf nodes contains the columns of the PK.

In InnoDB (not in some competing products), the PK is, by definition, clustered and UNIQUE.

As for your table,...

Are you allowing to rows for each ref_a--ref_b combo?  One 'active', one not?  This seems unlikely.

Why have id at all?  Why not promote the UNIQUE key to be the PK?

Is is_active ever NULL?

See my tips on many:many mapping tables.


What are the cons of having a database server in a Docker container for production purposes? MySQL/Oracle/Postgres installed on a Docker container, in production, being referenced by other apps. What are the cons on doing this?

Docker containers are intended to isolate environments used by different users and/or versions of software to minimize spends during the development time. Production server shouldn't be the home for everyone and shouldn't have different versions of software "just for testing". Therefore there is no reasons to put services into containers unless you are not a VPS-hoster.


How to restore TDE encrypted DB on AWS (with certificate not stored in master)? The way Amazon RDS enable TDE feature is to generate a certificate stored in master. However, I need to restore a database that is encrypted by another certificate.
I have the cert file, private key file and password.
But I can only create a certificate with files and password in ANY OTHER DATABASE EXCEPT MASTER.
Could you please give me some advice on how to restore TDE encrypted DB with certificate not stored in master? Or is there a method to to back up certificate at master in Amazon RDS?
Thanks in Advance!

This is a bit of a gap with RDS.  Apparently the advice is to remove TDE on the source DB then back it up so that it can be restored to RDS.
This is a pretty poor scenario in my opinion - switching on and off TDE should not be taken lightly.  I really hope that AWS enable some functionality to backup/restore certificates for TDE


How to store several lists and their items The app I'm working on revolves around projects. Each project can contain several lists, and each list is made up of several items.  Each list relates to a single project.

Is it better to have one listitem table structured as such: id, list_id, event_id, content or 2 tables; one containing the relationship between project and list, and the other containing only list items and their relationship to a particular list?

Is it better to have just one table of which each row would be a list item, including that item's relationship to a project and the name of the list it belongs to:

list_item(id, project_id, list_name, content, <<other attributes>>)

or, have one list item table for all of the list items in the app and and a separate list table:

list(id, project_id, name, description, <<other attributes>>)
list_item(id, list_id, content, <<other attributes>>)

It sounds like you want three tables

PROJECT( project_id, <<other attributes>>)
LIST( list_id, project_id, <<other attributes (e.g. the name of the list)>> )
LIST_ITEMS( list_item_id, list_id, <<other attributes>>)

Combining the LIST and LIST_ITEM tables wouldn't be appropriate because then you'd be storing attributes of the list (i.e. the list's name and description) in multiple rows of the LIST_ITEM table rather than just once in a single row of the LIST table.


PostgreSQL9.6 Beta1：new setting remote_apply for configuration parameter synchronous_commit PostgreSQL9.6 add new setting remote_apply for  configuration parameter synchronous_commit, Reading the following document I still don't understand the difference between on the setting on and remote_apply. Can anyone help me? thanks a lot .

synchronous_commit (enum)

Specifies whether transaction commit will wait for WAL records to be written to disk before the command returns a "success" indication to the client. Valid values are on, remote_apply, remote_write, local, and off. Can anyone help me? thanks a lot.

When set to on, commits will wait until replies from the current synchronous standby(s) indicate they have received the commit record of the transaction and flushed it to disk. This ensures the transaction will not be lost unless both the primary and all synchronous standbys suffer corruption of their database storage. When set to remote_apply, commits will wait until replies from the current synchronous standby(s) indicate they have received the commit record of the transaction and applied it, so that it has become visible to queries on the standby(s)

synchronous_commit

http://www.postgresql.org/docs/9.6/static/runtime-config-wal.html#GUC-SYNCHRONOUS-COMMIT

When the standby initially receives the log records, the are written to the standby's pg_xlog directory.  So those records are there for safekeeping, but they have not yet been applied to the shared_buffers and the data files (and the pg_clog, etc.)  Until they are applied, anyone connecting to the standby cannot see the effects of those records.

It is kind of like the difference between getting a note from the post office saying "Your envelope has been delivered" (synchronous_commit=on) and getting a note from your bank saying "Your envelope had been received, opened, processed, and your instructions have been carried out and your account has been credited with the proceeds, have cleared, and are now available for withdrawal" (synchronous_commit=remote_apply).


Syntax error in postgresql query I am trying to migrate a sql query into PostgreSQL. After running the query I get this error:

ERROR: syntax error at or near "("
LINE 20: ..._date is null or fcm.effect_end_date>=current_date()) limit ...

Below is my query:-

select farechart_master_id,farechart_name,version_number_service_stype,
fcm.route_id,st.service_type_name,fcm.passenger_type_id,
fcm.effect_start_date,fcm.effect_end_date,fcm.nignt_service,
fcm.peak_time,fcm.flexi_fare,r.route_number,r.route_direction,
r.effective_from,r.effective_till from farechart_master fcm
left join rate_master rm on rm.rate_master_id=fcm.rate_master_id
left join route r on r.route_id=fcm.route_id
left join service_type st on st.service_type_id=fcm.service_type_id
where fcm.deleted_status=0
and (fcm.effect_end_date is null or fcm.effect_end_date>=current_date())
limit 0 offset 10

Where am I going wrong?

current_date is a keyword. There are no parentheses after it.

fcm.effect_end_date >= current_date


Install mysql-workbench failed: Requires: proj, tinyxml, python-paramiko >= 1.15.1 I am installing mysql-workbench to migrate Postgres database to Mysql.

  OS System Version: Red Hat Enterprise Linux Server release 7.2 (Maipo)

I am installing mysql-workbench and follow these instructions.

https://dev.mysql.com/doc/workbench/en/wb-installing-linux.html

I have the following repo.

repo id                                                                                repo name                                                                                status
cloudera-manager                                                                         Cloudera Manager, Version 5.7.5                                                                              7
mysql-connectors-community/x86_64                                                        MySQL Connectors Community                                                                                30
mysql-tools-community/x86_64                                                             MySQL Tools Community                                                                                40
mysql57-community/x86_64                                                                 MySQL 5.7 Community Server                                                                                164
rhui-REGION-client-config-server-7/x86_64                                                Red Hat Update Infrastructure 2.0 Client Configuration Server 7                                              6
rhui-REGION-rhel-server-releases/7Server/x86_64                                          Red Hat Enterprise Linux Server 7 (RPMs)                                                                13,573
rhui-REGION-rhel-server-rh-common/7Server/x86_64                                         Red Hat Enterprise Linux Server 7 RH Common (RPMs)                                                         209
repolist: 14,029

When I tried the command

sudo yum -y install mysql-workbench-community

I got the these erros.

Error: Package: mysql-workbench-community-6.3.8-1.el7.x86_64 (mysql-tools-community)
           Requires: proj
Error: Package: mysql-workbench-community-6.3.8-1.el7.x86_64 (mysql-tools-community)
           Requires: tinyxml
Error: Package: mysql-workbench-community-6.3.8-1.el7.x86_64 (mysql-tools-community)
           Requires: python-paramiko >= 1.15.1

My question is how to find the repo of these dependencies package - proj, tinyxml, python-paramiko?

You need install additional packages:

sudo yum install epel-release
sudo yum install proj
sudo yum install tinyxml
sudo yum install libzip

if You have trouble with repo

wget https://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm
sudo yum install epel-release-7.noarch.rpm

than

sudo yum -y install mysql-workbench-community


DB stored procedures: how do they help increase security in web systems? The usage of Oracle PL/SQL procedures for controlling data access is often emphasized in PL/SQL books and other sources as being more secure approach. I'v seen several systems where all business logic related with data is performed through packages, procedures and functions, so application code becomes quite "dumb" and is only responsible for visualization part. I even heard some devs call such approaches and driving architects as database nazi :) because all logic code resides in database. I do know about DB procedure performance benefits, but now I'm interested in a "better security" when using thick client model and database procedures. I assume such design is mostly used when Oracle (and maybe MS SQL Server) databases are used.

I do agree such approach improves security but only if there are not much users and every system user has a database account, so we might control and monitor data access through standard database user security. However, how such approach increases the security for an average web system where thick client model is used: for example one database user with DML grants on all tables, and other users are handled using "users" and"user_rights" tables? We could use DB procedures, save usernames into context use that for filtering but vulnerability resides at the root - if the main database account is compromised than nothing will help. Of course in a real system we might consider at least several main users (for example frontend_db_user, backend_db_user).

So, does the usage of pl/sql stored procedures (all DML operations through procedures) help to increase security in thick client model systems (web systems)?

The theory behind using stored procedures for increased security is that you don't give DML access directly to users.  The idea is that users never get anything other than execute permissions on the procedures.  There's also a slightly less paranoid version of this approach where all writes are by stored procs, but table/view reads can be done by users.

The reasoning goes that if users only have execute permissions on stored procs and no direct DML on tables or views, then they can't manipulate data in any unpredicable ways.

To take an example, let's say you've got a bank account system and the only way to make a funds transfer is through a stored proc, you can have this proc audit log details about when it happened, where the money went and so forth.  If you allowed direct DML access to the base tables then a compromised account could be used to take money without leaving an audit trail.

You are right that a compromised account is still a big security hole - big enough to drive a truck through.  If all the compromised account's access is restricted to stored proc execution however then at least the hole will only fit a smaller truck.


How to add a flexible database role to a database? I know there are two types of roles, one predefined (fixed), another user-defined (flexible). Here is my use case: I have a large number of AD Groups, and I am trying to map them up to a role and define permissions on that role. For example, ADGroup1, 2, 3 and 4 only should have access to database 1 tables 1, 2 and 3. To avoid re-defining the permission every single time for every single AD Group, I am trying to assign permissions to a ROLE and define the proper permissions to that single role.

Here is the issue I get:
For Logins, when I get properties, I get this nice "User Mappings" page that lets me define which DBs a user can have access to. But I cannot do that with a role, I cannot pick and choose databases a role can have access to.

What should I use to achieve this? How do you combine logins? (AD groups)?

THank you very much in advance

You need to do the user/role mapping per database.

So you create an AD group and add that as a login on the server

Within the database you define a role and set permissions for the role. Then you need to add the AD Group login as a user to the database and set them as a member of the database role. Each role can have multiple members so you can add many groups as members of the role.

You can also map the users to a server role and then add the server role as a user in the database and make that member of a database role.

Robin Page has a simple explanation on simple talk and it's all explained in details in multiple details in many StackExchange answers


SQL Server MDF still has old values after update Let me start off saying I am not a DBA.

My company is in the process of moving our SQL Server 2012 databases (recovery model of full) from one network to another.

Due to security requirements, we must transfer the .MDF and not backups.  We do not transfer any .LDF files. We also need to perform various update statements to "scrub" data that we deem sensitive. We then execute a full database backup.

After performing these steps, we must take the .MDF and process it through a file scanner to parse the file and find any words from a black list.  Now these update statements should have taken care of these words, but the file scanner finds them still.

It appears that the only way to fully "scrub" this data is to move the data into a temp table, drop and recreate the original table and re-insert the data back into the newly recreated original table.  This would involve dropping and recreating every constraint, index, and trigger each table may have.

There has to be a better way than this.  What are we perhaps doing wrong?

For clarity, the overall process sequence is:

scrubbing
checkpoint
backup
detach
filescan mdf

We don't use TDE because it is not the transferring that needs to be secure.  there is data that cannot exist on the new network (security requirements of govt. customers).

The blacklisted terms exist just in tables (not in stored procedures, functions, triggers etc.)

The file scanner is third party, so I don't know the internal mechanisms.  I know the scanner can scan various different files (text, Excel, PDF, etc.).

This is probably one of the silliest processes I've ever heard of, but anyway.

After you've updated your data to remove the offensive words, run a checkpoint, and then restarted SQL Server (just to make sure it's all committed), the only way to guarantee that the underlying disk has been overwritten, and the "offensive" data expunged, is to perform a shrink of the database file.

This effectively takes the data pages from the end of the file and inserts them into open spaces at the beginning of the file to fill up "empty" space. It's an horrific process for SQL Server, as it causes massive fragmentation, but it will "solve" your problem.

Once you've moved your data to the new server, you must run a full index rebuild to defragment the tables, but, hey, it should work.


Multiple one to many relationships in series I am making a DB for storing reports that have following fields:

Ad_system,
Ad_Campaing,
Ad_Group,
Keyword,
Date,
Impressions,
Clicks,
Conversions

Logic is that, Ad_system can have multiple Ad_Campaings, Ad_Campaing can have multiple Ad_Groups and Ad_Groups can have multiple Keywords, and for every Keyword there is a report. Should It be like this? Because it kind of makes sense to me, but it doesn't look correct. Primary key can also be:

ad.system.name for first table

ad.system.name ad.campaing.name for second table

ad.system.name ad.campaing.name ad.group.name for third table

ad.system.name ad.campaing.name ad.group.name keyword for fourth table

They will always be unique, but then Report will always have all DB columns in it, so it's kind of redundant.  Is the approach that I have in the diagram below correct? Or is a better one what I just described? If I am not mistaken both should satisfy 3rd NF.

Both are correct in the sense that both are normalized, based on your question. It seems like there isn't a strong consensus on whether to use natural keys or surrogate keys (the ids). Here are a couple of stackexchange questions on the subject:

Surrogate vs. natural/business keys [closed]
Should every table have a single-field surrogate/artificial primary key?


What does a best-practice SQL Server maintenance plan look like? I am a student from Fontys University in Eindhoven, and I am currently carrying out a series of interviews to help with the development of a SQL Server tool and I would like to get feedback from the experts in the field.

One of my questions is:

What does a best-practice SQL Server maintenance plan look like? Do you use the SQL Server maintenance plans for this, or do you use custom scripts?

You have to plan it with the business needs in mind.  Sometimes it's a maintenance plan (like when a shop doesn't have full time DBAs or wants a minimum of complexity.)  Other times it's Ola Hallengren's maintenance scripts.  Whatever you do, it has to take into account the business needs for that data.  I discussed it in my post Ozar's Hierarchy of Database Needs.


Average, between specific date/time, of the difference between current field and previous field I'm trying to calculate the average, between a specific date/time, of the difference between the current field and the previous field. At present I've got a query which displays this but I'm unsure how I can now filter this such that I can get the average of the 'Diff' column between a specific date/time. (Apologies in advance for my sql skill, or lack of.)

SELECT
      S.Pair,
      S.Date,
      S.Time,
      S.Bid,
      S.Time,
      ABS(S.Bid - T.Bid) as 'Diff'
FROM
    dbo.Data as S
CROSS APPLY
           (
            SELECT TOP 1 T.Bid
            FROM
                dbo.Data as T
            WHERE
                 T.Id > S.Id
           ) as T

Any pointers would be appreciated.

EDIT:

http://sqlfiddle.com/#!3/d4910/1

I used your SQL Fiddle and posted a working solution here.

WITH Calc AS
(
    SELECT
        S.Pair,
        S.Date,
        S.Time,
        S.Bid,
        ABS(S.Bid - T.Bid) AS Diff
    FROM dbo.forexData AS S
    CROSS APPLY
    (
        SELECT TOP (1)
            T.Bid
        FROM dbo.forexData AS T
        WHERE
            T.Id > S.Id
            AND t.Pair = s.Pair
        ORDER BY
            T.Bid DESC
    ) AS T
)
SELECT
    Pair,
    AVG(DIFF)
FROM Calc
GROUP BY
    Pair;

To summarize:

I fixed a hole in your existing logic - currently your CROSS APPLY doesn't link to the same pair value so if you have multiple pairs then your DIFF could be incorrect.
I put the fixed logic into a CTE
I queried the CTE with a GROUP BY on pair
I added an ORDER BY specification to determine the TOP ordering

You can add a date filter in a WHERE to the outer SELECT if you desire.


How do i copy my mongodb data-files to mongodb server I actually copied above two files in MongoDB's data folder which is default in windows machine (C:\data\db ). But when I start mongo shell and type show dbs, it will not show my database.

Steps I followed is as below.

1. copy the data files to C:\data\db
2. start the MongoDB server by mongod --dbpath C:\data\db
3. start the MongoDB shell by mongo command in command prompt
4. type show DB's command to list the databases

But I am not able to see my databases. Any help would be appreciated.

Thanks, Krishna

With MMAPv1 you could copy f.ex. local.* files to some new mongod instance and instance saw those. With wiredTiger, you cannot do it anymore.


SQL Server AlwaysOn cluster having issues seeding large availability database creating strange behavior I set up a 2-node SQL Server 2016 AlwaysOn Availability Group cluster (1 primary, 1 secondary).  The underlying hardware is fairly powerful; 4 CPUs, 16 GB of RAM and local-attached SSD disk.  Then I restored 10 databases on the primary.  One of these databases was large, about 38 GB in size, while the others were relatively compact being in the 0.5 - 2 GB range in size.  The restore happened quickly - then I added these 10 databases, all at the same time, as Availability Databases and chose Seeding as the method to initialize the replicas.

This is when the strange behavior happened.  The first 6 databases were copied across as expected and appear as Synchronized in the list of databases on the secondary replica - including the 38 GB database.  The CPU utilization on the secondary replica went from 0% to about 20-50%.  Then the remaining 4 databases never appeared.  So I ran this query:

SELECT
 r.session_id, r.status, r.command, r.wait_type
 , r.percent_complete, r.estimated_completion_time
FROM sys.dm_exec_requests r JOIN sys.dm_exec_sessions s
 ON r.session_id = s.session_id
WHERE r.session_id <> @@SPID
AND s.is_user_process = 0
AND r.command like 'VDI%'

And here is the output of the above query:

So it seems that the other databases are not getting seeded because they are sitting in the queue with status 'background'.  So I decided to wait 16 hours.  16 hours later, the CPU usage on the secondary replica is still in the 20-50% range.  There are no queries being issued on the cluster.  The Primary Replica is in Synchronized state for all of the databases and is at 0% CPU utilization (and is running on the same type of hardware).  However, the 4 databases are showing as Not Synchronized on the secondary replica.  The cluster is using a Cloud Witness for Quorum mode.

So my questions are:

Why are the 4 small databases still not seeded onto the secondary replica?
Why does the secondary replica seem to be stuck doing some kind of large CPU or I/O bound task?
What should I do now?  Should I keep waiting (it's already been 12 hours) to see if it finishes?

Any assistance here would be greatly appreciated.

Please have a look at the links given below. They all talk about similar situations, though not identical, and can help answer a few of your questions.

Many BACKGROUND Threads with Command VDI_CLIENT_WORKER
SQL Server 2016 AlwaysOn VDI_CLIENT_WORKER command
VDI_CLIENT_OTHER wait type

Apart from this you might want to check the following -

Check the SQL Server error log for any related messages - xp_readerrorlog
Check if the log movement is "Suspended" for the databases that show as not synchronized on the secondary replica? - If yes, then you will need to "Resume" the log movement.
You might want to remove these databases (the ones that are not synchronizing) from the AG and then add them again.


Database Role seemingly lost permission I have a windows application that accesses a SQLServer database.  Sometime within the last week or so it stopped working on other peoples machines with an exception thrown indicating they did not have execute permissions on a stored procedure.  I am wondering what mechanism might have changed my database (certainly I did not do anything . . . at least knowingly).

I have setup a role called db_execproc, added users (the application connects using windows authentication), and granted the role EXECUTE on the dbo schema.  Everything was working fine, but for some reason it stopped working.  Granting EXECUTE again to that role fixed the problem.

So I am wondering how this might have been broken (SQLServer update?).

Also, in the SQL Server Management Studio I was trying to verify that db_execproc had the right permissions, but I cant see anything (either before I granted them or after) . . . how can I see what permissions have been granted to a role?

So I am wondering how this might have been broken (SQLServer update?).

SQL Server update wont change any login / user or role permissions as part of update (installing CUs or SPs).

The permissions might have changed due to a possible database upgrade (your application version upgrade). Check your upgrade scripts.

To find out who changed the permissions, you need to have a server side trace running and collecting relevant information.

  how can I see what permissions have been granted to a role?

You can use this TSQL to check permissions granted to a role.


User mapping problem during deployment I have a development machine with a new database I have designed. The server has a login L1 and the database has a user U1. L1 is mapped to M1 and tables have permissions set up for U1. Authentication is SQL Server authentication. I wanted to fully backup the database and restore to the production server so that I could start to use it in production.

I had a problem with user U1: I could not map it to any login on the production server. So I could not use U1 with any login. Is this correct behavior?

You have to create a login on the prod server as when you restore the backup to PROD server, you are having an orphaned user.

So steps are :

Create Login using SID option (if you are just having 1 login to map). Else use sp_help_revlogin
You can use SID from sys.database_principals where type_desc = SQL_USER (since you mentioned you are using SQL Login).
Then run ALTER USER as below :

USE [DatabaseName]
go
ALTER USER [UserName]
WITH LOGIN = [UserName]

Note: sp_change_users_login is deprecated.


How can I make my database more concise? I am writing an application for Android using SQLite (backed by ORMlite if it matters). So far I'm in the conceptual stage and working on the database design. The database is being designed to contain a collection of assets that are registered to one or more users. I have never worked on any data outside of SQLite and even then I have only made a few very small databases. As such I am not sure that my I handling this design correctly. As I expand on my design, it is beginning to look like a mess. My questions are:

Am I designing this database in too much of an ObjectOriented fashion? As a java programmer, this is just how it makes sense to design the database.
Is there any way to make the database look less like a mess?
Am I even on the correct track?

For reference here is an earlier more easy to read protoype of the database design. No typings have been assigned yet, sorry.

Here is a more updated visualisation of my problem.

This is more of a comment, but since this is a lengthy response that's worth mentioning, I'm posting it as an answer.

Your database looks sufficient for your needs.  (Well, as best can be assumed from the diagram.)  I assume that the UserAccount table has a one-to-many relationship between the four tables Vehicle, Book, VideoGame and Movie (from here on, the V-B-V-M tables.)  Next, I assume that the Asset table has a one-to-one relationship with each link between the V-B-V-M table entries.  With this information, I would recommend moving the UserAccount_id from each V-B-V-M table, to the AssetTable.

Next, it seems that Asset is always associated with one of the four V-B-V-M tables.  Considering this type of setup, I would recommend creating an additional table-- AssetType.

+-----------+
| AssetType |
+-----------+
| id        |
| typeDesc  |
+-----------+

Next, update the Asset table and each V-B-V-M tables to include a AssetType_ID field.  the AssetType table should include one entry, for each type of asset.  In this case, Vehicle, Book, Video Game and Movie, each with a unique description and ID.

You may want to do this for a couple reasons-- First, you can now query the Asset table and discern what type of asset is stored in a specific record, without having to link the data to your four other V-B-V-M tables.  This will speed up your querying of the data, when all of the information from your four tables isn't needed.  Second, this also provides an easy means of linking in a textual description of the type of asset associated with the Asset entry. Again, without having to link to the V-B-V-M tables to figure that out.

Last, you may want to consider handling the barcode data differently.  If barcode is a simple look-up, you can add it to the asset table.  However, if barcode values have to be unique, keeping it in a secondary table is necessary because you'd have to put a constraint on the database.  You could do this if it was integrated in the Asset table, however, your vehicle data would cause problems because vehicles do not have barcodes.  To my knowledge most, if not all, major database engines won't allow you to define a unique field, that also allows null values because, then, the data is no longer unique.

You might be inclined to think that you could combine the Barcode field with the VIN.  Since VINs are unique and Barcodes are unique, and neither should be the same, why can't they be combined into one field on the Asset table, which allow you to drop the additional Barcode table.  I wouldn't recommend this because I think it gives the data less "focus".

Yes, it is a form of unique object ID but barcodes are barcodes and VINs are VINs-- the concept behind both is completely different and consolidating the two can become confusing with time.  Furthermore, if you expand your project in the future, it could cause collisions.  Technically, I don't see a big problem with that (in your case) but I'd still highly recommend avoiding that option.

Also, I would also add the assetType_id to the Barcode table, for the same reasons you added it to the Asset table.


Trigger error in MySQL I'm using Toad for MySQL, and I have to make a trigger so that:

When the rent of a car is <=15 and >= 25, I insert a letter "A" in the field category
When the rent is > 25 and <= 35, insert the letter "B"
When the rent is >35 and <= 50, insert the letter "C"

Here is my table:

CREATE TABLE `automoviles` (
  `id_automoviles` int(11) NOT NULL AUTO_INCREMENT,
  `tipo_automovil` varchar(25) NOT NULL,
  `rentamiento` decimal(5,2) NOT NULL,
  `marca` varchar(25) DEFAULT NULL,
  `año` int(11) NOT NULL,
  `estado` varchar(10) DEFAULT NULL,
  `categoria` varchar(2) DEFAULT NULL,
  PRIMARY KEY (`id_automoviles`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

I have the trigger this way, but it gives me an error:

DELIMITER $$
CREATE TRIGGER categoria AFTER INSERT on automoviles
  FOR EACH ROW
  BEGIN
    IF (old.rentemiento >=15 and old.rentamiento <= 25)
    THEN
    UPDATE autoviles
       set new.categoria = 'a'

    or if(old.rentamiento >=25 and old.rentamiento <=35)
    THEN
    UPDATE automoviles set new.categoria = "b"

    else if (olD.rentamiennto >=35 and old.rentamiento <=50)
    THEN UPDATE automoviles set new.categoria = "c"

and here is the error!

  ERROR! There is no OLD row in on INSERT trigger

Welcome to the site, very nice first post that includes table ddl, well done!

As indicated in comment, OLD exists only in update and delete triggers. Furthermore, there is a spelling error on rentamiento (at least it is spelled differently in your table).

For changes of the row under insertion, typically a BEFORE trigger is used. You can use something like:

DELIMITER $$
CREATE TRIGGER categoria
BEFORE INSERT on automoviles
FOR EACH ROW
    SET new.categoria = CASE WHEN new.rentamiento BETWEEN 15 AND 25 THEN 'a'
                             WHEN new.rentamiento BETWEEN 26 AND 35 THEN 'b'
                             WHEN new.rentamiento BETWEEN 36 AND 50 THEN 'c'
                             ELSE 'd'
                        END;
$$
DELIMITER ;

I added a fallthrough case, which may or may not be necessary.


Is there a shortcut for 'SELECT * FROM table' in relational algebra? Problem

A question I once found on a test was worded as such:

  Report the book information for the book with ISBN=43221

The relational schema for a book is depicted below:

πISBN, title, type, NumPages, edition, sequel, name(σISBN=43221(Book))

This is essentially the equivalent of

SELECT * FROM Book WHERE ISBN=43221.

I wrote out all the attributes in the Projection statement on the test, but I had the feeling there was a quicker way to do it.

Question

Is there a shortcut way of displaying all the information of one tuple in Relational Algebra, or must all attributes be written out in the Project statement?

Yes.

In relational algebra the relation

Book

corresponds to

select * from Book

If you want to restrict to some rows you apply a selection σ:

σISBN=43221(Book)

In SQL this is a where-clause like

select * from Book where ISBN=43221

If you want to reduce the columns you need a projection π:

πISBN, title, type(σISBN=43221(Book))

In SQL this is a select-list different from *:

select ISBN, title from Book where ISBN=43221


In terms of databases, what does 'roach motel' mean? Recently Larry used it in one of the OpenWorld keynotes.

Wikipedia says

  In computers, sometimes as slang use:

  Sometimes used to refer to a proprietary file standard -- "you can check your data in, but you can't check it out".

I fail to understand the meaning here. In what situations will one not be able to check out the data?

Generally, I understand (could be wrong of course) it to be either

opaque vendor lock-in
difficulty in re-using data how you want

The second point is seen every day

You buy an app that runs on a mainstream RDBMS: you can report on data, extract it, mine it whatever. Or you buy an app that locks the data into some internal, opaque, proprietary format (not a well known one like PDF or DOC or XLS) where you are forced to use the vendor's (perhaps limited) API.

A couple of other examples

Larry Ellison on Salesforce
Facebook vs Twitter


MySQL Crashed every hours MySQL Server crashed every hours and this logs submited.

MySQL Version : 5.6 + cPanel

my.cnf :

Ram of SERVER : 128GB
Cpu : 2x 8core with thareds : 32Core
Hard Disk : SSD Pro

Logs :

http://paste.ubuntu.com/12646771/

How to solve the problem now?!

My.cnf :

http://paste.ubuntu.com/12646776/

I set a cron every hours with MySQL -e 'FLUSH QUERY CACHE;' in the crontab server. Because the size query cache full and all websites slowed, after flush all website working fast and fine.

Having a query cache of 45GB is definitely not a good idea. It should not be more than say 128MB, usually much lower numbers are suggested, or even totally disabling it. Query cache of this size makes everything slow because on any update to any table it has to be traversed and entries invalidated, and you can have many entries to invalidate in 45GB.

The crash itself looks like a bug in the server for handling the big cache (signal 11 is a segfault afaik). You can check if it is known and if not then submit a bug report.

But definitely set the cache size well under 1GB, it is better to give that memory to innodb buffer pool.


Query Cache Implementation Details I am new to DBA stack exchange & this is my first question. Kindly help me in finding a low level documentation for Query Cache module in MySQL since I am planning to work with the source code of Query Cache. I googled a lot but I was provided with only high level details. Is it available in MySQL website (I tried but not an exhaustive search)? Please help me.

This is the best documentation available for the query cache:

sql_cache.cc

I am not kidding, the header of that file contains the most detailed overview I have ever found. Next thing is the own code, which is on the very same link.

There is a query cache chapter on this book, and this other book, but only 4-5 paragraphs, in the context of its relation with the handlers interface.

Apart from that, you should read the general internals code guide.


What is the correct implementation of handling concurrent SQL inserts while ensuring a non-primary key column (i.e. wid) unique and auto-incremental? What is the correct implementation of handling concurrent SQL inserts while ensuring a non-primary key column (i.e. wid) unique and auto-incremental?

Note: There was a design constraint that led this field (i.e. wid) in not becoming the primary key. A separate auto-increment PK is included in the column called id. Changing the wid to PK is not feasible as the database design is now heavily coupled to multiple software projects. So I need to devise a workaround.

Some more information:
The column wid follows a predefined format of concatenation of branch id and sequence number.

Sample rows:
ID - wid
1 - 11000001
2 - 11000002
3 - 10000001
4 - 11000003
5 - 10000002

I'm thinking of 3 possible ways:

1.) Reserving the wid by executing a transaction and proceeding with other SQL statements which should have been included in the same transaction. The reservation is executed in a separate transaction to make the locking as short as possible. However, if the subsequent transaction failed (e.g. due to database connection issues), then the invalidated row containing the reserved wid will remain in the table.
(note: the minimum and maximum threshold used in wid is the concatenation of branch id and sequence number)

START TRANSACTION;
SET @wid_d = 0;
SELECT COALESCE(MAX(`wid`), 0) INTO @wid_d FROM table
WHERE `wid >= 10000000 AND `wid <= 10999999 FOR UPDATE;
INSERT INTO table (`wid`) VALUES (IF (@wid_d = 0, 10000001, @wid_d+1) )
COMMIT;

2.) Including the reservation of wid in the entire transaction to eliminate the problem of insertion of invalidated row, should the transaction fail but this approach prolongs the locking of tables.

3.) Rely on the failure of transaction calls for re-executing the transaction until it performs successfully; should the transaction fail due to the race condition of inserting the same MAX(wid) + 1. This approach will prevent insertion of invalidated rows but the problem is the number of times the transaction should be re-run. There is also no distinction of knowing the problem that is caused by the conflicting wid or other problems, hence, this is a problematic approach.

All three options you are looking at are bound to perform poorly in a concurrent environment. Wrapping SELECT (MAX(wid)) ... in a transaction won't prevent race conditions; you'd need to write-lock the entire table for that.

If you don't need the sequence numbers to start from 1 for every branch, as long as the values are increasing and unique, you might consider creating a separate table with an auto-incerement column to generate the wid values, something like this:

create table gen (
  branch_id int not null,
  seq int not null auto_increment,
    index (seq),
    unique (branch_id, seq)
);

You can then simply insert the base branch_id into that table and retrieve the generated sequence value with last_insert_id().


Getting "sqlcmd sqlcmd: command not found" in Linux? Whenever I run sqlcmd I get "command not found"

sqlcmd -S localhost -U SA -P '<YourPassword>'
sqlcmd: command not found

How can I resolve that?

There are two methods of resolving this.

User-specific method advocated in the manual: add the directory to the environmental variable PATH.
System-wide method: symlink the executable to a directory already in the path.

User-specific Method
You can use the tutorial's recommended method. The tutorial states the following:

Optional: Add /opt/mssql-tools/bin/ to your PATH environment variable in a bash shell.
To make sqlcmd/bcp accessible from the bash shell for login sessions, modify your PATH in the ~/.bash_profile file with the following command:
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bash_profile

To make sqlcmd/bcp accessible from the bash shell for interactive/non-login sessions, modify the PATH in the ~/.bashrc file with the following command:
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc
source ~/.bashrc

This works, but it only affects that user's sessions, and only if they use bash.
System-wide Method
Link the MS SQL tools into a location accessible to all users:
sudo ln -s /opt/mssql-tools/bin/* /usr/local/bin/

This could be in their postinst script, which would make sense for installing an optional package.
Both methods are compliant with the File system Hierarchy Standard, which states:

Programs to be invoked by users must be located in the directory /opt/<package>/bin


PostgreSQL, what is the hostname address of my default database? I just created a PostgreSQL database on my laptop (following these instructions), which is running Linux CentOS 7.

Now I would like to understand what is the hostname address of my default database. I thought it was localhost but it's not.

I want to get to know the name or the IP address to use with the psql -h command.
Basically, running this psql -h HOSTNAME command should let me have the same result of the psql command alone, that is letting me connect to my database.

What is the hostname address of my default database?

> service postgresql status

states:

  ● postgresql.service - PostgreSQL database server Loaded: loaded (/usr/lib/systemd/system/postgresql.service; enabled; vendor preset: disabled) Active: active (running) since Thu 2017-03-30 14:40:30 EDT; 1 day 1h ago Main PID: 2256 (postgres

while both psql -h 127.0.0.1 and psql -h 127.0.0.1 -U davide produce this message:

  psql: FATAL: Ident authentication failed for user "davide"`

If you're connecting from the same machine, use localhost

A given computer can run multiple instances of PostgreSQL on different sockets (address:port pairs). The default port is 5432

A given PostgreSQL server can run multiple databases on the same instance. The postgres database is always there. There's no "default database" though really.

Are you sure the service is enabled and running? Did you set listen_addresses in postgresql.conf and set up authentication in pg_hba.conf?

Is the service actually running?