what is most efficient way to retrieve next and previous items based on ordering and current item only So I have this problem in many of my apps and I don't even know how to describe it correctly.

I'm using MongoDB (3.6) so my IDs are hashes.

I have a table/grid view, which displays items mapped to database records, then when someone clicks on one you get the single-item view page. But then from there, I want to allow next and previous "pagination".

I quote "pagination" because the URL never contains a page number, and can't. The url only contains a direct link to an item.

For example: mydomain.com/posts/59118d075e04da6104421bb4

Let's say to keep it simple, items have a created_at timestamp and the ordering is always by created_at ascending.

You have 100 items, you land on item 54 out of 100. But you don't know that because the id is a hash. Maybe it was 54th in the ordering when you bookmarked it and now since more items have been added it's now 58th out of 104.

The API returns the data for that item, but then what happens when the user wants to navigate to the previous or next item. What's the most efficient way to handle that?

A very slow way would be to query ALL items ordered by created_at, then loop through the list until you find the one that matches the current ID. And then -1 +1 that would be your previous/next item.

And every time a user clicks the prev/next arrow you repeat this slow process.

I have the same problem in MySQL (5.7). I don't know how to say "given this ID, what is the prev/next records based on this ordering"

Another way I thought of doing this is storing a persistent array of all the IDs, and then getting the position for the item that matches a given ID, and then you could more easily get the IDs of items +1 -1 that position. That wouldn't work too well if you had a huge number of records though.

Is there a more efficient way? Hope someone can enlighten me.. thank you!

In MySQL it would be simple. Given an @id value, you can easily find the next or previous rows - and efficiently assuming there is an index on (id) and another index on (created_at):

-- find next item
select t.*
from tablename as t
     join
     ( select created_at
       from tablename
       where id = @id
     ) as current
     on t.created_at > current.created_at
order by t.created_at asc
limit 1 ;   -- N next items

To find the previous items, you only have to change > to < and asc to desc.

If there can be rows with same created_at, then you need to modify:

-- find next item
select t.*
from tablename as t
     join
     ( select created_at, id
       from tablename
       where id = @id
     ) as current
     on t.created_at > current.created_at
     or t.created_at = current.created_at and t.id > current.id
order by t.created_at asc, t.id asc
limit 1 ;   -- N next items

The whole current subquery is not truly needed as the values @id and @created_at will likely be available from the previous run so the query can be simplified:

-- find next item
select t.*
from tablename as t
where   t.created_at > @created_at
     or t.created_at = @created_at and t.id > @id
order by t.created_at asc, t.id asc
limit 1 ;   -- N next items


Postgres get rows where a field stopps growing I have a table which has the following structure

id SERIAL PRIMARY KEY,
device_id INT REFERENCES(another_table),
uptime TIMESTAMP

Every device is pinged every N minutes.
The application logic and db is (Sadly) made so that for each ping instead of an update to one row there is a new INSERT.

How can I get the rows where the next row's timestamp is smaller than this row's in the context where they are grouped by their id's in ascending order (you basically start from the smallest one and finish with the biggest one) ?

Can it be done with some sort of group by or should it be written in pl/SQL or something along those lines ?

Use the window function lead() in a subquery:

SELECT id, device_id, uptime
FROM  (
   SELECT *, lead(uptime) OVER (PARTITION BY device_id ORDER BY id) AS next_uptime
   FROM   tbl
   ) sub
WHERE  next_uptime < uptime

BTW, "where a field stops growing" would translate to <=, but "where the next row's timestamp is smaller" requires <.


Database and query optimizacion I have a database containing three tables: tbl_database (main table is the primary), tbl_cmdatabase and tbl_blacklist. The three tables share the same structure but differ in the number of records that can be. This is the SQL structure, that basically is the same:

CREATE TABLE IF NOT EXISTS `tbl_ndatabase` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `id_telefonica` int(11) NOT NULL,
  `number` varchar(11) COLLATE utf8_spanish2_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_spanish2_ci AUTO_INCREMENT=1 ;

Table tbl_database contains ~194 074 records and they should continue to grow in large numbers, table tbl_cmdatabase contains ~45,742 records and similarly can increase but perhaps not so exaggerated as tbl_database but would increase and finally the table tbl_blacklist having ~92,038 records. I am developing an application where I need to verify that the number that I will insert into table tbl_database is not in tables tbl_blacklist and tbl_cmdatabase so I need to check for every row looking for the number (this queries are slow in this amount of records). What I need to do is to optimize some tables or change the structure or not because when I perform these queries the server tends to fall as the shared hosting does not support large queries, anyone can help me with this issue? Any suggestions?

Edit: Added a file for test data

I need to verify that the number that I will insert into table tbl_database is not in tables tbl_blacklist and tbl_cmdatabase

Perhaps you can do this with something like

SELECT count(*)
FROM (
  SELECT number FROM tbl_blacklist
  UNION
  SELECT number FROM tbl_cmdatabase ) AS Q
where Q.number = '$number'

When the count returned is 0, it means that $number is not in the tables. It's possible this is not the best query for this job, but I think it will work OK.

You can play around with it here:

http://sqlfiddle.com/#!2/7d697/23


Length of LOB data (%1) to be replicated exceeds configured maximum %2 We recently hit this error shortly after enabling replication on a table.

Length of LOB data (%1) to be replicated exceeds configured maximum %2

I've found solutions to this problem already:

https://stackoverflow.com/questions/925824/when-insert-length-of-lob-data-to-be-replicated-exceeds-configured-maximum-65536
http://sqlmusings.wordpress.com/2008/09/22/issue-length-of-lob-data-to-be-replicated-exceeds-configured-maximum-65536/

However there's no explanation of what this configuration was there for in the first place.  If we turn off the LOB size limit what side effects does that have - are we opening ourselves up to more issues in the future?

Thanks in advance.

The max text repl size options exists to limit how much LOB data can be added to a replicated column.  If you turn off the LOB size limit and a user inserts a large amount of LOB data, latency will increase and replication will come to a crawl.  The max text repl size option can be used to prevent this.


Store time series data efficiently without wasting space I'm building an application to calculate different KPI metrics for customers of an ecommerce website e.g. (avg. order value, avg. items count and so on). KPIs are integer and or double values e.g. number of items bought, avg. order value, gross margin...
The application fetches orders data, calculate metrics and store them.
I'm using MySQL as a relational database.
about metrics:
I currently have 10 metrics to calculate for each customer.
Metrics can increase in the future but not so frequently so I can consider "10" as quite definitive. Anyway altering schema in the future is not a problem at all.
I need to calculate each metric on a weekly basis (at minimum).
Metrics are about customers.
about customers:
Customers are 30k and they are growing + 0.5k/month rate.
Not all customers buy with the same frequency. I can have occasional buyers but also heavy buyers.
I want to show a graph with the overall trend of a specific KPI in a given timespan.
I want to show a graph with the trend of a metric for a specific customer in a given timespan.
My entites are:

orders
customers
customers_kpi

I'm worried about storing a huge amount of useless data
52 weeks * 30k users * 4+ years = 6.2M rows at minimum
I have 2 questions:

Should I store rows for customers without orders for a given timespan (e.g. the row will be all filled with NULL)? Can avoid it somehow without affecting data visualization?

Which table structure is more efficient ("thin" table vs "fat" table) given that not all customers buy every week and number of metrics are unlikely to change often?

I'm in doubt among these 2 structures for customers_kpi table:

customer_id
kpi1
kpi2
kpi3
..kpiN
from
to

VS

customer_id
kpi_name
kpi_value
from
to

First off, a few million rows is nothing to be afraid of. Modern relational database management systems can handle up to trillions of rows on pretty standard hardware, when architected and indexed properly. Secondly, I like to measure things by a minimum of 10 years as a good test of time, so let's take your metric and annualize it over 10 years which gives us closer to ~15.6 million rows, and let's round it up to say 20 million (to account for customer growth etc). We're still in medium sized numbers, as far as number of records in a single table, so no big deal.
To answer your questions directly:
1. "Should I store rows for customers without orders for a given timespan (e.g. the row will be all filled with NULL)? Can avoid it somehow without affecting data visualization?"
A: There's no need to store empty rows. You can create a dates dimension table which will store a single row per date and then you can outer join to it such as SELECT * FROM dateDimensions AS D LEFT OUTER JOIN orders AS O ON D.date = O.orderDate WHERE D.date >= 'some date value' AND D.date < 'some other date value'. (You can join in your other tables here as needed.) This will help keep your table much leaner (maybe that 20 million rows in 10 years becomes 10 million or 5 million roughly) because now you're not storing multiple empty order or empty customer_kpi rows every week for every customer who didn't make an order, you're only storing one row per date (or your dates dimensions table can even be simplified to just storing one row per week - though probably overkill). And outer joining to your dates dimension table will maintain the same visualization you're looking for.
2. Which table structure is more efficient ("thin" table vs "fat" table) given that not all customers buy every week and number of metrics are unlikely to change often?
A: Both table structures have their place, and sometimes a denormalized table structure like your first example structure is better for an OLAP heavy / heavy reporting database regarding performance, but in general normalization is best practice when all things are equal, like your second example structure. Because 20 million rows (worse case number of rows based on my answer to your first question) is really nothing to be concerned about in a single table, and because you get better flexibility with querying about only specific KPIs (which can be more performant by not bringing back a fat row with a bunch of unneeded data), e.g. SELECT * FROM customers_kpi WHERE kpi_name = 'SomeSpecificKPI', I'd personally recommend starting with your second table structure. You can always easily transform it later on to look like your first structure if you need or should you change your mind and want to materialize it as that structure into a new table.


PostgresQL Delete Trigger does not delete row I'm currently creating archive records for my database, where each table x has a corresponding x_archive table. My solution was to create a trigger for each table that needed replication that would insert the deleted data into the archive table. The trigger seems to run just fine, with the data being replicated to the archive table. The original data, however, is not deleted. The trigger only seems to run on the first delete query. If I run the same delete statement again (on the original data), both the row in the original table and the archive table is deleted. I've also tried to create trigger functions that are specifically tailored to inserting into a specific table, but the same result occurs. Any help is greatly appreciated.
Trigger function
CREATE OR REPLACE FUNCTION archive_record()
RETURNS TRIGGER AS $$
BEGIN
    RAISE NOTICE 'Running trigger';
    EXECUTE format('INSERT INTO %I.%I SELECT $1.*', TG_TABLE_SCHEMA, (TG_TABLE_NAME || '_archive'))
    USING OLD;
    RETURN NULL;
END;
$$ LANGUAGE PLPGSQL;

Example trigger
CREATE TRIGGER delete_test
    AFTER DELETE
    ON test
    FOR EACH ROW
    EXECUTE PROCEDURE archive_record();

Example table
create table test
(
    id                  serial primary key,
    name                varchar(128) not null,
);

Example archive table
CREATE TABLE test_archive (
) INHERITS(test);

Inheritance could be a useful tool, but for now it has its many flaws, as you noticed.
So you should dismiss it, if the system don't react to the way you think

create table test
(
    id                  serial primary key,
    name                varchar(128) not null
);

create table test_archive
(
    id                  serial primary key,
    name                varchar(128) not null
);

CREATE OR REPLACE FUNCTION archive_record()
RETURNS TRIGGER AS $$
BEGIN
    RAISE NOTICE 'Running trigger';
    EXECUTE format('INSERT INTO %I.%I SELECT $1.*', TG_TABLE_SCHEMA, (TG_TABLE_NAME || '_archive'))
    USING OLD;
    RETURN NULL;
END;
$$ LANGUAGE PLPGSQL;

CREATE TRIGGER delete_test
    AFTER DELETE
    ON test
    FOR EACH ROW
    EXECUTE PROCEDURE archive_record();

insert into test values (1,'test1'),(2,'test2')

2 rows affected

DELETE FROM test WHERE id = 1

1 rows affected

SELECT * FROM test_archive

id | name
-: | :----
 1 | test1

SELECT * FROM test

id | name
-: | :----
 2 | test2

db<>fiddle here


My PLE Counter value is 483.what is the safe side for PLE buffer manager value? Today morning i ran this scripts and i got 483.is that counter value is good ?

select [OBJECT_NAME],
[counter_name],
[cntr_value]from sys.dm_os_performance_counters
where[OBJECT_NAME]like '%Manager%'
AND [counter_name]='page life expectancy'

The Page Life Expectancy counter tells you for how many seconds an 8KB data page will remain in the buffer pool, before being flushed out.

A low number can be a sign of memory pressure, but it may also be a sign of nothing.

If your data access patterns involve a lot of report-style queries, reading a lot of data from large tables, this could be normal.

Monitor the value over several days, at different times in the day, and you may find that this is an outlier. I have a customer whose PLE goes to zero every time a certain large index is rebuilt, but it steadily increases after that.

My first sense is to review indexes, and see if a well-placed index can help with large reads. Another best practice is to avoid using SELECT * FROM table if you can help it.

You may need to increase the physical RAM in the server, but that shouldn't be your first reaction.

(Read more here about PLE and NUMA nodes: http://www.sqlskills.com/blogs/paul/page-life-expectancy-isnt-what-you-think/)


How to Bulk Insert sqlalchemy subclasses I am trying to bulk insert SQL-Alchemy Subclasses into the parent table and their respective tables ie fruits tables -> Apple Table and so I insert a table of APPLE and it will insert both the row into the fruits table then give me the id of the row in fruits table and put it Apple
This works when inserting one row at a time, but I need it to work with bulk insertion due to performance

I have tried to bulk insert which failed and I tried single row insertion it works with single row insertion but the thing is this data is not really unique except for the id of the row which is auto-generated so its going to be really hard to do a bulk insert to the parent table then do a bulk insert into the subclass table where the data matches and use the id by a mapping function

            for data in apple_list:
                db.session.add(Apple(
                    brand=data["brand"],
                    picked_date=data["picked_date"],
                    type=data["type"],
                    color=data["color"],
                    sub_type=data["sub_type"],
                ))

what I want is something more like bulk insertion

          db.session.bulk_insert_mappings(model_classes['Apple'], apple_list)

Actual results are that when it tries to insert it errors out on the insertion due to it not having the foreign primary key that tells the row for the fruits table to the apple table

Expect to insert without any errors and to populate both tables like when inserting both rows

I figured it out so SQLALCHEMY has a parameter in bulk insert mapping called return_defaults

WARNING: This is straight from the docs return_defaults – when True, rows that are missing values which generate defaults, namely integer primary key defaults and sequences, will be inserted one at a time, so that the primary key value is available. In particular this will allow joined-inheritance and other multi-table mappings to insert correctly without the need to provide primary key values ahead of time; however, Session.bulk_insert_mappings.return_defaults greatly reduces the performance gains of the method overall. If the rows to be inserted only refer to a single table, then there is no reason this flag should be set as the returned default information is not used.

so then all you have to do is this

db.session.bulk_insert_mappings(model_classes['Apple'], apple_list, return_defaults=True)

it's still alot faster than db.session.add


What are the difference between a "dimension" table in a star-schema and a "lookup" table in a relational database? I'm trying to design a star-schema fact table along with some dimension tables that will surround it. If I reuse the natural key called customer_key in both the fact_table and dim_customer then I don't see a difference between calling something dim and a lookup table. Additionally if I every need to update the customer_name I will loose historical data that represented this fact at the time of the recording. What am I missing when trying to model the dim and fact tables?

I would like to understand difference between relational "lookup" table technique and data warehouse "dimension" table?

fact_table               dim_customer          dim_product
-------------            ------------          -----------
customer_key             customer_key          product_key
product_key              name                  name
units_sold               email                 description
unit_price

Please forgive any ignorance I might show in this question. I am a data warehouse newbie.

If you only have those tables there will be little difference between your datawarehouse star schema and your actual schema.

You probably have a much more complex relational schema though, where you also have customer groups or item types and your schema looks more like

fact table -> customer table -> customer group table
fact table -> item table -> item type table

When you are working towards a star schema you would then denormalize your schema so you include the customer group description in your customer dimension table and your item type description in your item dimension table.

The basis of a star schema is that you have a fact table, and every dimension is a single table just a single step away from the fact table.

The way kimball describes it:

  Star schemas characteristically consist of fact tables linked to
  associated dimension tables via primary/foreign key relationships.

If you don't denormalize your dimensions into single dimension tables you end up with a snowflake schema. Snowflakes may seem like a good idea, but they often result in poorer query performance and can get you in all kinds of trouble later on, especially if you plan to build a cube on top of your data warehouse.

Again Kimball has a post on it:

  We generally encourage you to handle many-to-one hierarchical
  relationships in a single dimension table rather than snowflaking.
  Snowflakes may appear optimal to an experienced OLTP data modeler, but
  they’re suboptimal for DW/BI query performance. The linked snowflaked
  tables create complexity and confusion for users directly exposed to
  the table structures; even if users are buffered from the tables,
  snowflaking increases complexity for the optimizer which must link
  hundreds of tables together to resolve queries. Snowflakes also put
  burden on the ETL system to manage the keys linking the normalized
  tables which can become grossly complex when the linked hierarchical
  relationships are subject to change. While snowflaking may save some
  space by replacing repeated text strings with codes, the savings are
  negligible, especially in light of the price paid for the extra ETL
  burden and query complexity.

With regards to your question about changing the customer data and losing history, in a datawarehouse that's solved by using "slowly changing dimensions".

You basically have several types of slowly changing dimensions, but you are probably looking for "type 2" which largely comes down to storing a valid_from and valid_to date on your dimension row so you can select the row that was valid at the time of the transaction.

Type 2 SCD's

  In addition to the primary surrogate key, I recommend adding five
  additional fields to a dimension that is undergoing Type 2 processing.
  These fields are shown in Figure 1. The datetimes are full time stamps
  that represent the span of time between when the change became
  effective and when the next change becomes effective. The
  end-effective-datetime of a Type 2 dimension record must be exactly
  equal to the begin-effective-datetime of the next change for that
  dimension member.

It's a broad subject to cover but I hope this sets you on the right track for the basics and you can use the correct terminology in the future.

The kimball site is a great resource (and many call it the reference) for dimensional modeling so I suggest you browse the glossary and the design tips section


Microsoft TDE Permissions For Changing Keys I am setting up roles as a layer of protection inside of MSSQL. One of the roles will only have the ability to change database encryption keys for TDE on the database server. The server will be using EKM to supply the keys.

I have not been able to find the exact process to exchange the keys with EKM. Since I can't find the process, I can't give the roles the permissions it needs.

Right now, I have granted CONTROL permissions on certificates and asymmetric keys at the database level. I am assuming that this will be necessary.

Does anyone know if there are any server level permissions required to change the database encryption key? I believe that the user will at least need to have access to which keys are located in EKM.

ALTER DATABASE ENCRYPTION KEY

Requires CONTROL permission on the database and VIEW DEFINITION permission on the certificate or asymmetric key that is used to encrypt the database encryption key.

You must also add view definition according to MSDN.

https://msdn.microsoft.com/en-us/library/bb630389%28v=sql.110%29.aspx


Mysql max connections and event scheduler Is there a limit to how many "instance" of a scheduled event MySQL can run? I have set this up:

CREATE TABLE IF NOT EXISTS t (c1 INT UNSIGNED NOT NULL) ENGINE=INNODB;

SELECT * FROM t;

DELIMITER $$

DROP EVENT IF EXISTS e_second;
CREATE EVENT e_second ON SCHEDULE EVERY 1 SECOND
DO BEGIN
    INSERT INTO t (c1) VALUES (UNIX_TIMESTAMP());
    SELECT SLEEP(1000);
    INSERT INTO t (c1) VALUES (UNIX_TIMESTAMP());
END$$

DELIMITER ;

SELECT * FROM t;

SHOW PROCESSLIST;

SHOW VARIABLES LIKE '%connect%';

This creates a dumb event started every second, that runs for 1000 seconds. In my query log, I indeed have:

2020-02-18T15:47:00.289050Z      1016 Query     INSERT INTO t (c1) VALUES (UNIX_TIMESTAMP())
2020-02-18T15:47:00.430801Z      1216 Query     INSERT INTO t (c1) VALUES (UNIX_TIMESTAMP())
2020-02-18T15:47:00.430950Z      1216 Query     SELECT SLEEP(200)
2020-02-18T15:47:01.290518Z      1017 Query     INSERT INTO t (c1) VALUES (UNIX_TIMESTAMP())
2020-02-18T15:47:01.430943Z      1217 Query     INSERT INTO t (c1) VALUES (UNIX_TIMESTAMP())
...

So the event is run every second, and hangs (on purpose). Now, the processlist shows (after a while):

"559"   "root-heidisql" "127.0.0.1" "test_sql"  "Connect"   "702"   "User sleep"    "SELECT SLEEP(1000)"
"560"   "root-heidisql" "127.0.0.1" "test_sql"  "Connect"   "701"   "User sleep"    "SELECT SLEEP(1000)"
"561"   "root-heidisql" "127.0.0.1" "test_sql"  "Connect"   "700"   "User sleep"    "SELECT SLEEP(1000)"
"562"   "root-heidisql" "127.0.0.1" "test_sql"  "Connect"   "699"   "User sleep"    "SELECT SLEEP(1000)"
"563"   "root-heidisql" "127.0.0.1" "test_sql"  "Connect"   "698"   "User sleep"    "SELECT SLEEP(1000)"
...

So I have about 528 rows in this processlist now. But, my connections limit is 151 as shown by showing variables:

"max_connections"   "151"

So clearly, the running events are not part of the connections limit. Then, what is the limit? Is there one?

It feels to me that at some point, some mysql queue/list/whatever will be full of the pending events... Is this case documented somewhere?

the running events are not part of the connections limit. Then, what is the limit? Is there one?

Connection limit means outer connections, event scheduler is not counted. But there is a lot of another limited resources - limited by some settings or by some physical conditions. And it is too problematic to find what resource limits the server.

  there is no direct limitation of the number of currently running events?

Yes, such limitation not exists. MySQL treates an event as a trigger which fires on a timer event - and there is no limitation on the number of triggers in the system.

In general the event procedure must check does its previous instance have already finished its work... by using some flag field/value in a service table, for example, or by some another mark.


Why is deadlock alert not working? I created a deadlock alert including notification to an operator like this:

EXEC msdb.dbo.sp_add_alert @name=N'1205 Deadlock',
        @message_id=1205,
        @severity=0,
        @enabled=1,
        @delay_between_responses=30,
        @include_event_description_in=1,
        @database_name=N'myDB',
        @category_name=N'[Uncategorized]',
        @job_id=N'00000000-0000-0000-0000-000000000000';

This alert is registered for notification.

It's history says it never appeared although it is in place for months and there are multiple deadlocks per day. I created a deadlock manually. It shows up in the Log File.

What is missing for this alert to raise a notification?
Other notifications like e.g. 18456 Invalid Login work fine.

Probably the error 1205 is not set up to be logged in ERRORLOG. Look it up in sys.messages:

SELECT *
FROM sys.messages
WHERE message_id = 1205;

If the column is_event_logged is set to 0, change it this way:

EXEC master.sys.sp_altermessage
    @message_id = 1205,
    @parameter = 'WITH_LOG',
    @parameter_value = 'true';

The documentation for sp_add_alert describes under which conditions an alert is raised:

  Remarks

  sp_add_alert must be run from the msdb database.

  These are the circumstances under which errors/messages generated by SQL Server and SQL Server applications are sent to the Windows application log and can therefore raise alerts:

  Severity 19 or higher sys.messages errors
  Any RAISERROR statement invoked with WITH LOG syntax
  Any sys.messages error modified or created using sp_altermessage
  Any event logged using xp_logevent


Old archivelog files present on disk, but not in RMAN I recently spotted 3 very old archivelog directories on disk that do not appear within RMAN.

[oracle@ORACLE01 archivelog]$ du -sh *
122M    2017_02_25
4.9G    2017_02_26
2.5G    2017_02_27
1.6G    2017_06_28
474M    2017_06_29

Within RMAN, archivelogs appear from the 28th June (as I would expect)

RMAN> list archivelog all;  (list truncated for brevity)
using target database control file instead of recovery catalog
List of Archived Log Copies for database with db_unique_name R2CI
=====================================================================

Key     Thrd Seq     S Low Time
------- ---- ------- - -------------------
30329   1    81000   A 28/06/2017 10:45:26
        Name: /u01/.../archivelog/2017_06_28/o1_mf_1_81000_do72c0ng_.arc

30330   1    81001   A 28/06/2017 10:48:00
        Name: /u01/.../archivelog/2017_06_28/o1_mf_1_81001_do73f110_.arc

If I run a crosscheck archivelog all, this picks up only the recent archivelog files that I expect to be there:

RMAN> crosscheck archivelog all;

allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=21 device type=DISK
validation succeeded for archived log
archived log file name=/u01/.../archivelog/2017_06_28/o1_mf_1_81000_do72c0ng_.arc RECID=30329 STAMP=947846881
validation succeeded for archived log
archived log file name=/u01/.../archivelog/2017_06_28/o1_mf_1_81001_do73f110_.arc RECID=30330 STAMP=947847970
validation succeeded for archived log
archived log file name=/u01/.../archivelog/2017_06_28/o1_mf_1_81002_do74pzcw_.arc RECID=30331 STAMP=947849313
validation succeeded for archived log
(list truncated for brevity)

A "list archivelog all" after this point does not show these old files.

Questions:

Where are these files likely to have have come/why weren't they
eventually expired and deleted like the rest of the archivelogs?
Why does crosscheck archivelog all not detect them and add them
back? is it because there is a 4 month gap between them and the next
most recent files?
Is it safe to just delete these files off disk?

Thanks

Why does crosscheck archivelog all not detect them and add them back?

Because that's not what crosscheck does. Crosscheck reads the records of the archlogs that are recorded in the repository - the ones that rman reports. It reads a record from the repository, then checks to see if that file exists. If not, the record is marked 'expired'. If rman shows no sign of knowing of these files, I'd say it's safe to delete them.


Read-only on an SQLite database which is being modified by another program I wrote a program that has an SQLite database, modifying it every few seconds.

Now I want to write a small gadget program that would just read a bit of info from that database (read-only) and display it.

Can the second program open the database as "read-only", while the first program might be modifying it?
Is using Read Only=True when opening the database enough?
Or do I have to make a copy of the SQLite file, or something?
I did not find any tip about this on sqlite.org.

The second program does not need real-time info, it can be a bit outdated.

You are looking for WAL Mode.

  WAL provides more concurrency as readers do not block writers and a
  writer does not block readers. Reading and writing can proceed
  concurrently.


Failed attempt to fetch logical page - tempdb At least 2 times a week I get an error as the tempdb should be corrupt.

  Failed attempt to fetch logical page xxxx in database 2.

This on all my 8 SQL Servers 2008 R2.

The strange thing is that after the error, users can continue to work without any problem. And using the corrupted tempdb for months... I don't find any topics where you talk about this, is this a bug? Or real corruption?

I found as only solution people reccomend restarting the service, something I don't want to do.

Repeated corruption = faulty IO subsystem. Something, somewhere in the IO stack (driver, cache, controller, disk, etc)

I'd suggest that you get it onto a different IO subsystem to start with and investigate from there.


SQLQueryTableData-Important In SQL Server Management Studio, when I run the query

select * from tablename

it shows me the data.

But, that table is not physically present in that database. Is it possible?

It may be that tablename isn't actually a table but is another database object that acts like a table.

To find out, run this bit of T-SQL in the database containing tablename:

select schema_name(schema_id),name, type from sys.objects where name='tablename'

This will return rows if it finds for any object called tablename in the database. If it returns 1 single row, and type is U then it is a table and you'll need to see why it's not showing in SSMS. This could be just that it needs refreshing, or your view may be filtered by schema or something else.

If the second column isn't a U then the object isn't a table (ie; it could be a view, or a synonym for another table, etc). There is a lookup list of values here - http://msdn.microsoft.com/en-gb/library/ms190324.aspx - which you can use to identify the object type.


Mongo authentication user can't drop database my question is - is it possible to define user in the Mongo database who will be able to drop the database? Maximum user privilege is "dbAdmin" but that privilege doesn't allow users to drop the database, only collections can be dropped.

I know that "clusterAdmin" has rights to drop the database, but that role can't be defined in regular database, only in admin database...

No, it is not currently possible (in version 2.4) to create such a user/permission.

Permissions on a specific database grant permissions on entities contained in that database, but not on the database itself.  Cluster level permission is needed to create or drop databases.

Reference: http://docs.mongodb.org/manual/reference/user-privileges/#database-administration-roles


How to GRANT or DENY access to an object based on metadata for that object in SQL Server? We are in the process of reviewing our database permissions among various applications and business groups on SQL Server in a Windows Active Directory domain.
We currently do not use separate schemas for access control, and permissions are assigned at the database level via roles and windows groups. We have web applications that access the databases, and internal users who access the databases directly. Everything runs on at least SQL Server 2017 Enterprise. Using schemas may be a future practice, but should be outside the scope of recommendations for this question.
To start this process we classified all database tables based on several parameters. Such as the type of data it contains and it's use within our applications. These properties were mapped to user roles. We "tagged" the databases with this extra metadata using extended properties so that we can audit our systems. So, for instance, we might flag a table as containing PHI, and being used for SYSTEM or REPORTING. Finally, "Group1" may not be able to access any tables with PHI. Or, "Group2" may have SELECT only access to SYSTEM tables.
Now, we would like to implement a method of programmatically granting and restricting access to these tables based on this metadata. That way, when a developer adds a new table, they can set the appropriate metadata, and more granular permissions will be applied without them having intimate knowledge of our roles / groups / permissions as defined by the business and admin teams.
Now, I know there are other ways to accomplish things like this. But, this is one method that makes the most sense to us right now based on development requirements and resources. i.e. Achieve the best security value with as little application re-work as possible from the development team.

Can you provide any guidance on how to do this?
Any feature within SQL server that might allow us to do this in a native way?
Or, is my only option to periodically run a job that reads the metadata and updates permissions?
Do you foresee any problems with this method?

is my only option to periodically run a job that reads the metadata and updates permissions?

You could alternatively use Row Level Security to enforce security predicates dynamically at runtime.  But that has additional complexity and runtime cost.

Do you foresee any problems with this method?

For those roles you should not mix manual changes to the permissions and permissions generated based on your metadata.


MYSQL dump and problems character encoding I have a 2 gb old database sql backup file. The problem is that text is encoded in UTF-8. Actually this isnt the real problem. This database contains greek and english characters.

The problem is that for example greek Π letter is encoded as
C3 90 UTF-8  which is unicode U+00D0 , clearly not Π letter. But in ISO-8859-7 D0 is Π!

This occurs almost everywhere.. Is there a fix i can apply ?Ideally i want to convert it to utf-8..

NOTICE
ISO8859-7 was used

Assuming your source data contained only English and Greek characters, and was mis-exported with an ISO-8859-1 to UTF-8 conversion (rather than an ISO-8859-7 to UTF-8 conversion), you can get your data back by first repeating the missed conversion the other way around, and then doing the right one.

You could use iconv for this (available on pretty much all Linux distributions, and on Windows via gnuwin32).

You'd do something like:

$ iconv -f UTF-8 -t ISO-8859-1 < bad_dump_file.sql | \
  iconv -f ISO-8859-7 -t UTF-8 > good_dump_file.sql

As an example:

$ echo -n $'\xC3'$'\x90' | iconv -f UTF-8 -t ISO-8859-1 | \
   iconv -f ISO-8859-7 -t UTF-8 | hexdump -C
00000000  ce a0                                             |Î |
00000002

And 0xCE 0xA0 is the right UTF-8 encoding for Π.


As I mentioned in my comment, your request is not well defined because it is unclear how the 4-minute wide time windows are supposed to be chosen.
In addition, the nature and importance of the id column is unclear.
Be that as it may, I came up with the following query. It uses window functions in order to define the group edges, and then filters out any row that is not an edge:
WITH t AS (
    SELECT id, action, date
         , min(date) OVER (PARTITION BY round(extract(epoch FROM date)/240)) min_date
         , max(date) OVER (PARTITION BY round(extract(epoch FROM date)/240)) max_date
    FROM tab
)
SELECT id, action, date
FROM t
WHERE date = min_date OR date = max_date;


how to show some rows in multiple columns and one row? I have two tables

land
===========
lid name meter address

owner
==========
ownerid  name family mobile lid

Every land can have maximum 12 owners.

The result that I need:

lid name meter address owner_name_1 owner_family_1 owner_mobile_1 owner_name_2 owner_family_2 owner_mobile_2 .... owner_name_12 owner_family_12 owner_mobile_12

I tried pivot but I can not write it correctly.

One potential way of doing this would be with a common-table-expression combined with a ROW_NUMBER() aggregate to identify each owner as owner #1 to #12.

I solved it like this:

IF OBJECT_ID('tempdb..#land') IS NOT NULL
DROP TABLE #land;
CREATE TABLE #land
(
    lid INT NOT NULL IDENTITY(1,1)
    , name VARCHAR(30) NOT NULL
    , meter VARCHAR(30) NOT NULL
    , address VARCHAR(30) NOT NULL
);

IF OBJECT_ID('tempdb..#owner') IS NOT NULL
DROP TABLE #owner;
CREATE TABLE #owner
(
    ownerid INT NOT NULL IDENTITY(1,1)
    , name VARCHAR(30) NOT NULL
    , family VARCHAR(30) NOT NULL
    , mobile VARCHAR(30) NOT NULL
    , lid INT NOT NULL
);

INSERT INTO #land (name, meter, address)
VALUES ('port', 'meter1', 'address1')
    , ('star', 'meter2', 'address2');

INSERT INTO #owner (name, family, mobile, lid)
VALUES ('owner1', 'family1', 'mobile1', 1)
    , ('owner2', 'family2', 'mobile2', 1)
    , ('owner3', 'family3', 'mobile3', 1)
    , ('owner4', 'family4', 'mobile4', 1)
    , ('owner5', 'family5', 'mobile5', 1)
    , ('owner6', 'family6', 'mobile6', 1)
    , ('owner7', 'family7', 'mobile7', 1)
    , ('owner8', 'family8', 'mobile8', 2)
    , ('owner9', 'family9', 'mobile9', 2)
    , ('owner10', 'family10', 'mobile10', 2)
    , ('owner11', 'family11', 'mobile11', 2)
    , ('owner12', 'family12', 'mobile12', 2)
    , ('owner13', 'family13', 'mobile13', 2)
    , ('owner14', 'family14', 'mobile14', 2);

;WITH owners AS
(
        SELECT o.*
            , rn = ROW_NUMBER() OVER (PARTITION BY o.lid ORDER BY o.ownerid)
        FROM #owner o
)
SELECT *
FROM #land l
    LEFT JOIN (SELECT owners.lid, owners.name, owners.family, owners.mobile FROM owners WHERE owners.rn = 1) t1(lid1, name1, family1, mobile1) ON t1.lid1 = l.lid
    LEFT JOIN (SELECT owners.lid, owners.name, owners.family, owners.mobile FROM owners WHERE owners.rn = 2) t2(lid2, name2, family2, mobile2) ON t2.lid2 = l.lid
    LEFT JOIN (SELECT owners.lid, owners.name, owners.family, owners.mobile FROM owners WHERE owners.rn = 3) t3(lid3, name3, family3, mobile3) ON t3.lid3 = l.lid
    LEFT JOIN (SELECT owners.lid, owners.name, owners.family, owners.mobile FROM owners WHERE owners.rn = 4) t4(lid4, name4, family4, mobile4) ON t4.lid4 = l.lid
    LEFT JOIN (SELECT owners.lid, owners.name, owners.family, owners.mobile FROM owners WHERE owners.rn = 5) t5(lid5, name5, family5, mobile5) ON t5.lid5 = l.lid
    LEFT JOIN (SELECT owners.lid, owners.name, owners.family, owners.mobile FROM owners WHERE owners.rn = 6) t6(lid6, name6, family6, mobile6) ON t6.lid6 = l.lid
    LEFT JOIN (SELECT owners.lid, owners.name, owners.family, owners.mobile FROM owners WHERE owners.rn = 7) t7(lid7, name7, family7, mobile7) ON t7.lid7 = l.lid
    LEFT JOIN (SELECT owners.lid, owners.name, owners.family, owners.mobile FROM owners WHERE owners.rn = 8) t8(lid8, name8, family8, mobile8) ON t8.lid8 = l.lid
    LEFT JOIN (SELECT owners.lid, owners.name, owners.family, owners.mobile FROM owners WHERE owners.rn = 9) t9(lid9, name9, family9, mobile9) ON t9.lid9 = l.lid
    LEFT JOIN (SELECT owners.lid, owners.name, owners.family, owners.mobile FROM owners WHERE owners.rn = 10) t10(lid10, name10, family10, mobile10) ON t10.lid10 = l.lid
    LEFT JOIN (SELECT owners.lid, owners.name, owners.family, owners.mobile FROM owners WHERE owners.rn = 11) t11(lid11, name11, family11, mobile11) ON t11.lid11 = l.lid
    LEFT JOIN (SELECT owners.lid, owners.name, owners.family, owners.mobile FROM owners WHERE owners.rn = 12) t12(lid12, name12, family12, mobile12) ON t12.lid12 = l.lid

It's not a particularly elegant or efficient way of solving this problem, but it seems to do what you want.

This is sample output (it's wider than this in reality, I just didn't want to include a super-wide image because it won't display well).

The select statement updated to not show all columns is:

;WITH owners AS
(
        SELECT o.*
            , rn = ROW_NUMBER() OVER (PARTITION BY o.lid ORDER BY o.ownerid)
        FROM #owner o
)
SELECT l.lid
    , l.name
    , l.meter
    , l.address
    , name1, family1, mobile1
    , name2, family2, mobile2
    , name3, family3, mobile3
    , name4, family4, mobile4
    , name5, family5, mobile5
    , name6, family6, mobile6
    , name7, family7, mobile7
    , name8, family8, mobile8
    , name9, family9, mobile9
    , name10, family10, mobile10
    , name11, family11, mobile11
    , name12, family12, mobile12
FROM #land l
    LEFT JOIN (SELECT owners.lid, owners.name, owners.family, owners.mobile FROM owners WHERE owners.rn = 1) t1(lid1, name1, family1, mobile1) ON t1.lid1 = l.lid
    LEFT JOIN (SELECT owners.lid, owners.name, owners.family, owners.mobile FROM owners WHERE owners.rn = 2) t2(lid2, name2, family2, mobile2) ON t2.lid2 = l.lid
    LEFT JOIN (SELECT owners.lid, owners.name, owners.family, owners.mobile FROM owners WHERE owners.rn = 3) t3(lid3, name3, family3, mobile3) ON t3.lid3 = l.lid
    LEFT JOIN (SELECT owners.lid, owners.name, owners.family, owners.mobile FROM owners WHERE owners.rn = 4) t4(lid4, name4, family4, mobile4) ON t4.lid4 = l.lid
    LEFT JOIN (SELECT owners.lid, owners.name, owners.family, owners.mobile FROM owners WHERE owners.rn = 5) t5(lid5, name5, family5, mobile5) ON t5.lid5 = l.lid
    LEFT JOIN (SELECT owners.lid, owners.name, owners.family, owners.mobile FROM owners WHERE owners.rn = 6) t6(lid6, name6, family6, mobile6) ON t6.lid6 = l.lid
    LEFT JOIN (SELECT owners.lid, owners.name, owners.family, owners.mobile FROM owners WHERE owners.rn = 7) t7(lid7, name7, family7, mobile7) ON t7.lid7 = l.lid
    LEFT JOIN (SELECT owners.lid, owners.name, owners.family, owners.mobile FROM owners WHERE owners.rn = 8) t8(lid8, name8, family8, mobile8) ON t8.lid8 = l.lid
    LEFT JOIN (SELECT owners.lid, owners.name, owners.family, owners.mobile FROM owners WHERE owners.rn = 9) t9(lid9, name9, family9, mobile9) ON t9.lid9 = l.lid
    LEFT JOIN (SELECT owners.lid, owners.name, owners.family, owners.mobile FROM owners WHERE owners.rn = 10) t10(lid10, name10, family10, mobile10) ON t10.lid10 = l.lid
    LEFT JOIN (SELECT owners.lid, owners.name, owners.family, owners.mobile FROM owners WHERE owners.rn = 11) t11(lid11, name11, family11, mobile11) ON t11.lid11 = l.lid
    LEFT JOIN (SELECT owners.lid, owners.name, owners.family, owners.mobile FROM owners WHERE owners.rn = 12) t12(lid12, name12, family12, mobile12) ON t12.lid12 = l.lid;


Cassandra cqlsh browser queries possible only in bytes I am using Cassandra database through cqlsh client on Linux. I can make only queries with values given as bytes, but I would like to search with the client like in SQL where everything is Strings.

Should I use assume? Still using it I can only change output, but not queries.

I used in my example situation Hector Object Mapping, HOM also for creation of the database from scratch. This was not a good idea, because everything was handled as bytes, like they normally are, and no instructions about how to interpret them was thus given.

Solution to this was to use cqlsh client and give there CQL queries to add tables. So, I made queries like:

CREATE COLUMNFAMILY users (KEY varchar PRIMARY KEY, password varchar);

and with these the remarkable thing is the explicit type (varchar for password) that was given to each column. Now, on the very same tool, data was interpreted as I wanted.

See also:

More info in Getting started with CQL page.

Also see CQL Language Reference for more information e.g. about available datatypes other than varchar mentioned before.


Recover deleted records Possible Duplicate:
  How do I get back some deleted records?

Our C#.NET application mistakenly deleted all records from all tables of its database.
The application automatically takes backups, and the last backup it took after this delete is big in size, which means there must be useful data in the backup. The recovery model of the database is SIMPLE and I turned the server off after the records were deleted.

How can I recover the deleted records?

It sounds like, with your scenario, the easiest way would be to grab a backup of the database and restore it side-by-side your current database.  You need to ensure that this backup contains the deleted records.  Then simply do an INSERT INTO ... SELECT FROM ... to get the deleted records from the old database (via a backup/restored version) into your current.

If you don't have a backup of the database containing all of the data, you may be out of luck here.


How do redundant indexes impact query performance? The indexes are affecting the ADD/UPDATE operations however I was wondering whether the redundant indexes will affect the SELECT query performance or not?

How the redundant indexes are handled by the SQL Server in case more than one indexes are eligible to use by the select query?

If you want to really split hairs, at the very least they affect the compilation time, since the optimizer has more indexes to consider. They also consume time to load and memory to store their metadata. It may seem a trivial amount, but consider the extreme case of an index with 16 columns over 1000 partitions: it requires 16000 metadata objects to read and store in memory, it may add up to quite a bit. And by simply existing, they add storage space which translates into larger/slower full backups, more space needed for ops, slower restores etc.

But, realistically, a redundant index does not affect query performance. I know of some examples when certain indexes may fool the optimizer into generating a bad (slow) plan, but that is a separate topic.


Can I enable SQLCMD mode from inside a script? Is there something like a directive which I can use in a script to force SSMS to enable/disable SQLCMD mode?

As has been said, there isn't a way.
However, one thing we do is to include a quick check in our script header, to determine whether SQLCMD mode is on (and terminate the script if not):
:setvar DatabaseName "MyDatabase"
GO
IF ('$(DatabaseName)' = '$' + '(DatabaseName)')
    RAISERROR ('This script must be run in SQLCMD mode. Disconnecting.', 20, 1) WITH LOG
GO
-- The below is only run if SQLCMD is on, or the user lacks permission to raise fatal errors
IF @@ERROR != 0
    SET NOEXEC ON
GO

PRINT 'You will only see this when SQLCMD mode is on'
-- Rest of script goes here

GO
SET NOEXEC OFF
GO

There is a reason Severity 20 is used, it tends to kill the connection immediately, preventing any more script code from running, even if there are GO batch terminators later in the script.

Severities 20-24:
Indicate system problems and are fatal errors, which means that the Database Engine task that is executing a statement or batch is no longer running. The task records information about what occurred and then terminates. In most cases, the application connection to the instance of the Database Engine may also terminate. If this happens, depending on the problem, the application might not be able to reconnect.
Error messages in this range can affect all of the processes accessing data in the same database and may indicate that a database or object is damaged. Error messages with a severity level from 19 through 24 are written to the error log.


High SOS_SCHEDULER_YIELD and Signal Waits I have run Paul Randals wait script and can see SOS_SCHEDULER_YIELD is at 60% with an average signal wait of 10ms.  I have also run a script to find out the what percentage of my waits are signal waits and in total it is 47%.

I can seen in task manager that the CPU usage is high and I believe I do have a CPU issue, but how do I find which queries are the ones causing the signal waits?

Just to update:  The version of SQL is 2008R2

Recent expensive queries in the Activity Monitor in SSMS should be your first stop.

For more information grab SP_Whoisactive and find Glenn Berry's diagnostic queries which will help you a lot. The exact query depends on the version of SQLServer but this should be a start.

SELECT TOP(50)
  creation_time
 ,last_execution_time
 ,(total_worker_time+0.0)/1000 AS total_worker_time
 ,(total_worker_time+0.0)/(execution_count*1000) AS [AvgCPUTime]
 ,execution_count
 ,st.text
FROM sys.dm_exec_query_stats  qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle) st
 WHERE total_worker_time > 0
ORDER BY total_worker_time DESC


SQL Server 2012 Transactional Replication - twin subscriptions on same machine Server A publishes Pub1, Server B subscribes to Pub1.

Server A and Server B are both SQL Server 2012 Standard Edition, default instances.

When I use Replication Monitor to drop-in a Tracer Token, one publisher-subscriber shows-up passing the token as expected.

When I run sp_helpsubscription 'Pub1' on Server A, one subscription shows-up as expected.

When I expand Replication->Local Subscriptions in SSMS on Server B, two identical subscriptions show-up.

What's going on?  Which subscription should I delete at Server B?

Giova's query brought into sharper focus the name of the publication; Pub 1 vs. Pub_1, for example.

The difference between the two subscriptions was the under-bar, '_', character in the name of the publication.

The subscription referencing the non-existent publication was deleted.


JOINing four tables together I have four tables, categories, category_permissions, category_relation, and documents.

It's a part of a documentation project I'm working on, just to learn more about relational databases.

Pseudo data for the example tables:

Categories

id | name
-----------------------
 1 | Default Category

Category Permissions

category_id | permission_id
-----------------------------
1           | 1
1           | 2

Category Relation

document_id | category_id
---------------------------
1           | 1
2           | 1

Documents

id | title            | content
--------------------------------------
1  | Sample Document  | Content!!!!!
2  | Another Document | Hello, World!

What I'm trying to get is rows returned containing information using all of the tables combined. Right now I can get either all of the information surrounding the document (document table, joined with the relation and category tables) or only information surrounding the permissions.

I suspect the issue is because the category permissions aren't directly related to the documents that this is happening.

Something like this is what I'm expecting:

id | title            | content       | category_id | permission_id
--------------------------------------------------------------------
1  | Sample Document  | Content!!!!!! | 1           | 1
1  | Sample Document  | Content!!!!!! | 1           | 2
2  | Another Document | Hello, world! | 1           | 1
2  | Another Document | Hello, world! | 1           | 2

But I'm getting more like this:

id | title            | content       | category_id | permission_id
--------------------------------------------------------------------
1  | Sample Document  | Content!!!!!! | 1           | (NULL)
1  | Sample Document  | Content!!!!!! | 1           | (NULL)
2  | Another Document | Hello, world! | 1           | (NULL)
2  | Another Document | Hello, world! | 1           | (NULL)

Here's what I think the query should be, and like I mentioned it works how I expect except the permission id's return null.

SELECT d.id, d.title, d.content, c.id, cp.permission_id
FROM documents d
LEFT JOIN category_relation cr ON cr.document_id = d.id
LEFT JOIN category_permissions cp ON cp.category_id = cr.category_id
LEFT JOIN categories c ON c.id = cr.category_id;

If I switch the second LEFT JOIN category_permissions to a RIGHT JOIN, that is when the data flips and I get the permissions, but not the documents.

My question is, am I structuring my data wrong, or lacking the know-how in MySQL to pull this off? I could just pull all of the documents and then loop through them to query for their categories and permissions, but putting queries in loops has never worked out for me performance-wise, especially when trying it against larger sets of data.

If the structure is not giving you any other grief, then you could just "tighten" the query a bit, in order to get the result that you want.

Using the following test table / data:

create table categories
as select 1, 'Default Category' from dual ;

create table categorypermissions
as
select 1 category_id, 1 permission_id from dual
union select 1, 2 from dual;

create table categoryrelation
as
select 1 document_id, 1 category_id from dual
union select 2, 1 from dual;

create table documents
as
select 1 id, 'Sample Document' title, 'Content!!!!!' content from dual
union select 2, 'Another Document', 'Hello, World!' from dual;

This query seems to be doing the trick:

select
  D.id
, D.title
, D.content
, CR.category_id
, CP.permission_id
from documents D
  join categoryrelation CR on D.id = CR.document_id
  join categorypermissions CP on CP.category_id = CR.category_id
order by D.id, CP.permission_id
;

-- output:
+----+------------------+---------------+-------------+---------------+
| id | title            | content       | category_id | permission_id |
+----+------------------+---------------+-------------+---------------+
|  1 | Sample Document  | Content!!!!!  |           1 |             1 |
|  1 | Sample Document  | Content!!!!!  |           1 |             2 |
|  2 | Another Document | Hello, World! |           1 |             1 |
|  2 | Another Document | Hello, World! |           1 |             2 |
+----+------------------+---------------+-------------+---------------+
4 rows in set (0.00 sec)

(MySQL 5.7)

Dbfiddle here.


Replicate SQL Data Inside Firewall to Outside Firewall - Suggestions Currently I have an internal CRM application that houses all my data and runs my entire company.  The front end is ASP, and the back end is SQL.  The external site will run SQL

What I'd like to do is to have an external site for customer's to log in, manage their accounts, etc., but I am very concerned about my data and data security.  Are there any best practices that I can follow to "replicate" data to the external databases?  Are there some suggested methods people have done in this scenario?

My goal is to have the two databases linked with certain data.  Changes inside would be queued somehow to the outside, and vice versa.  If it is live, that wouldn't be the end of the world either.

The method I'm familiar with would be to to only have an internal database. The firewall separating your internal and DMZ networks can be configured to only allow appropriate communications through, in your case 1443 (or your SQL port) between the web server and database.

However, you can't simply rely on your firewall for security. Proper SQL permissions would be necessary to only allow access to your web data. And of course you should be validating user input to protect against SQL injection.

If you do want to go the replication route, check out the MS offerings such as SQL Server Transactional Replication.


ETL: extracting from 200 tables - SSIS data flow or custom T-SQL? Based on my analysis, a complete dimensional model for our data warehouse will require extraction from over 200 source tables. Some of these tables will be extracted as part of an incremental load and others will be a full load.
To note, we have about 225 source databases all the with the same schema.
From what I've seen, building a simple data flow in SSIS with a OLE DB source and OLE DB destination requires the columns and data types to be determined at design time. This means that I will eventually end up with over 200 data flows just for the extraction alone.
From a maintainability perspective, this strikes me as a big problem. If I needed to make some kind of sweeping change to the extraction code, I would have to modify 200 different data flows.
An an alternative option, I wrote a small script which reads the source databases, table names and columns I want to extract from a set of metadata tables. The code runs in multiple loops and uses dynamic SQL to extract from the source tables via a linked server and OPENQUERY.
Based on my tests, this is still not as fast as using an SSIS data flow with an OLEDB source and destination. So I am wondering what kind of alternatives I have. Thoughts so far include:

Using EZAPI to programatically generate SSIS packages with
simple data flow. The tables and columns to extract would come from
the same metadata tables mentioned earlier.
Purchase 3rd party software (dynamic data flow component)

What is the best way to approach this? When it comes to .NET programming I'm a beginner, so the time required to ramp up just with the basics is also a concern.

I wouldn't want to have 200 data flows in a single package. The time it'd take just to open up and validate would make you old before your time.

EzAPI is fun but if you're new to .NET and SSIS, oh hell no, you don't want that. I think you'll spend far more time learning about the SSIS object model and possibly dealing with COM than actually getting work done.

Since I'm lazy, I'll plug BIML as a free option you didn't list. From an answer on SO https://stackoverflow.com/questions/13809491/generating-several-similar-ssis-packages-file-data-source-to-db/13809604#13809604

Biml is an interesting beast. Varigence will be happy to sell you a license to Mist but it's not needed. All you would need is BIDSHelper and then browse through BimlScript and look for a recipe that approximates your needs. Once you have that, click the context sensitive menu button in BIDSHelper and whoosh, it generates packages.

I think it might be an approach for you as well. You define your BIML that describes how your packages should behave and then generate them. In the scenario you describe where you make a change and have to fix N packages, nope, you fix your definition of the problem and regenerate packages.

Or if you've gained sufficient familiarity with the framework then use something like EzAPI to go and fix all the broken stuff. Heck, since you've tagged this as 2005, you could also give PacMan a try if you're in need of making mass modifications to existing packages.

SSIS Design considerations

Generally speaking, I try to make my packages focus on solving a single task (load sales data). If that requires 2 data flows, so be it. What I hate inheriting is a package from the import export wizard with many un-related data flows in a single package. Decompose them into something that solves a very specific problem. It makes future enhancements less risky as the surface area is reduced. An additional benefit is that I can be working on loading DimProducts while my minion is dealing with loading SnowflakeFromHell package.

Then use master package(s) to orchestrate the child work flows. I know you're on 2005 but SQL Server 2012's release of SSIS is the cat's pajamas. I love the project deployment model and the tight integration it allows between packages.

TSQL vs SSIS (my story)

As for the pure TSQL approach, in a previous job, they used a 73 step job for replicating all of their Informix data into SQL Server. It generally took about 9 hours but could stretch to 12 or so. After they bought a new SAN, it went down to about 7+ hours. Same logical process, rewritten in SSIS was a consistent sub 2 hours. Easily the biggest factor in driving down that time was the "free" parallelization we got using SSIS. The Agent job ran all of those tasks in serial. The master package basically divided the tables into processing units (5 parallel sets of serialized tasks of "run replicate table 1", table 2, etc) where I tried to divide the buckets into quasi equal sized units of work. This allowed the 60 or so lookup reference tables to get populated quickly and then the processing slowed down as it got into the "real" tables.

Other pluses for me using SSIS is that I get "free" configuration, logging and access to the .NET libraries for square data I need to bash into a round hole. I think it can be easier to maintain (pass off maintenance) an SSIS package than a pure TSQL approach by virtue of the graphical nature of the beast.

As always, your mileage may vary.


Simple yet problematic update query I have a rather simple update/query, that has been causing me a lot of grief over the years.

in simplest form it is :

update VillageSemaphore
set TimeStamp = getdate()
        where VillageID in (@X, @Y)

However, in some stored procs, the query also include this "OR VillageID in (...)" subquery

update VillageSemaphore
set TimeStamp = getdate()
        where VillageID in (@X, @Y)

        OR VillageID in  ( -- this subquery can return many rows, many different VillageIDs
        select VSU.SupportingVillageID
        from VillageSupportUnits VSU
        where SupportedVillageID = @Z
            and VSU.UnitCount <> 0
            )

Note that this OR, can return many villageIDs, not just one, @Z. This version of the query, sometimes runs a very long time. No index rebuild, stats rebuild helps. It runs slowly when the contents of Villages table is deleted and repopulated. In this case, the row count would be just a few hundred rows. I never figured out why this is, and always just lived with it.

However, recently I was looking at the query plan:

It seems that estimated number of rows (4000) is huge in comparison with the actual number of rows (2).

I created this statistic but it does not help

CREATE STATISTICS [stat_x] ON [VillageSU]([UnitCount], [VillageID])

SO MY QUESTION : any suggestions why this could be and what I could do to improve this ?

for reference, the table looks like this :

CREATE TABLE VillageSemaphore(
    VillageID    int         NOT NULL,
    TimeStamp    datetime    NOT NULL,
    CONSTRAINT PK97 PRIMARY KEY CLUSTERED (VillageID)
)

UPDATE: Trying out this version of the query as suggested by srutzky

CREATE TABLE #VillagesToLock (VillageID INT NOT NULL);
insert into #VillagesToLock values (@X)
insert into #VillagesToLock values (@Y)
insert into #VillagesToLock select VSU.SupportingVillageID
        from VillageSupportUnits VSU
        where SupportedVillageID = @Z
            and VSU.UnitCount <> 0

update VillageSemaphore set TimeStamp = getdate()
    where VillageID in (select VillageID from #VillagesToLock)

this is the result so far : http://screencast.com/t/96KafTPoNGM - query plan does look better.

The cost of the query went down from 3% to 1% also, so that seems good. 3% may not seem like much, but this is a 2500 line stored proc!

QUESTION: I cannot make #VillagesToLock.VillageID a PK since it is not unique. And I expect the #VillagesToLock to be typically no more than 2-10 rows. VillageSemaphore  could be many thousands of rows. Is it worth putting an index on #VillagesToLock in this case ?

UPDATE NOV 24
I have implemented this alternative

The query plan does look a lot nicer

Thank you to all who too the time to help me out!

While I'm not convinced this is a problem with the query itself (did you check for blocking when it runs slow? did you check the wait type(s) occurring while it was running), IN and OR can be a problematic pattern to optimize for. Have you considered breaking this into multiple statements?

UPDATE dbo.VillageSemaphoreset
  SET [TimeStamp] = GETDATE() -- TimeStamp is a terrible column name btw
  WHERE VillageID = @X;

UPDATE dbo.VillageSemaphoreset
  SET [TimeStamp] = GETDATE()
  WHERE VillageID = @Y;

IF (whatever condition leads you to "sometimes add this OR")
BEGIN
  UPDATE v
    SET [TimeStamp] = GETDATE()
    FROM dbo.VillageSemaphoreset AS v
    WHERE VillageID = @Z
    AND EXISTS
    (
      SELECT 1 FROM dbo.VillageSU AS vs
      WHERE vs.VillageID = v.VillageID
    );
END

This may solve the estimation problem, but I agree with Max, a statistic with a leading column of UnitCount won't help the estimates for these queries anyway.


Export and import a table without a specific column I'm trying to export a table with an Auto Increment rowid column and then import it in another server.
Considering that the column is primary key and can't accept duplicate keys if i import rows after backup from old server, new rows with same rowid would be added to new server.
i also don't want to change start number for Auto Increment in new server and cause gaps in rows.
so i thought one solution can be exporting new rows without rowid and letting new server to assign new rowid to them. but how can i do that directly with mysql or with help with phpMyAdmin ?

Another solution is to use LOAD DATA [LOCAL] INFILE, but override the columns list so the auto-increment value is inserted into a dummy variable:
Here's a demo:
I have a text file I call c.csv:
1       foo     bar
2       baz     bam
3       blee    bloo

I want to insert into mytable, but id's 1,2,3 are already occupied. I want the csv data to be inserted, but given new id's.
mysql> select * from mytable;
+----+------+------+
| id | col1 | col2 |
+----+------+------+
|  1 | text | text |
|  2 | text | text |
|  3 | text | text |
+----+------+------+

Here's a solution:
mysql> load data local infile 'c.csv' into table mytable (@dummy, col1, col2);
Query OK, 3 rows affected (0.03 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select * from mytable;
+----+------+------+
| id | col1 | col2 |
+----+------+------+
|  1 | text | text |
|  2 | text | text |
|  3 | text | text |
|  4 | foo  | bar  |
|  5 | baz  | bam  |
|  6 | blee | bloo |
+----+------+------+

It added the three rows from my csv file, but the id column on each row of input was detoured into the dummy variable, so the rows had to use the default, allocating new auto-increment id's.
P.S.: You need to let go of your desire to have no gaps in the sequence if id's. Gaps are normal. You may delete some rows in the future. You may attempt an insert but it gets rolled back or gets an error. There are also cases where InnoDB generates non-consecutive id values by design (read https://dev.mysql.com/doc/refman/8.0/en/innodb-auto-increment-handling.html for details).


Listener protocol error - Oracle Okay first things first, this is probably a common issue which gets resolved going through all those questions and googling it but still I face this issue:

LSNRCTL> service
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
   TNS-00511: No listener
   Linux Error: 111: Connection refused

When I tnsping my service name it works:

$ tnsping TESTDB

TNS Ping Utility for Linux: Version 11.2.0.4.0

Copyright (c) 1997, 2013, Oracle.  All rights reserved.

Used parameter files:

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = FIHOST)(PORT = 1523))) (CONNECT_DATA = (SERVICE_NAME = TESTDB)))
OK (0 msec)

But not with:

$ tnsping FIHOST

Used parameter files:

Used HOSTNAME adapter to resolve the alias
Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=))(ADDRESS=(PROTOCOL=TCP)(HOST=172.18.23.199)(PORT=1521)))
TNS-12541: TNS:no listener

AND

$ tnsping FIHOST.level.com

Used parameter files:

Used HOSTNAME adapter to resolve the alias
Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=))(ADDRESS=(PROTOCOL=TCP)(HOST=172.18.23.199)(PORT=1521)))
TNS-12541: TNS:no listener

PS: FIHOST.level.com is registered in DNS

Listener.ora:

LISTENER_TESTDB =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = FIHOST.level.com)(PORT = 1653)

SID_LIST_LISTENER_TESTDB =
  (SID_LIST =
    (SID_DESC =
      (ORACLE_HOME = /u01/11.2.0.4/product)
      (SID_NAME = TESTDB)

Tnsnames.ora:

TESTDB =
  (DESCRIPTION =
   (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = FIHOST)(PORT = 1523))
    (CONNECT_DATA =
      (SERVICE_NAME = TESTDB)

Also, local_listener                       string      (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=FIHOST.filevel.com)(PORT=1653)))

There isn't sqlnet.ora configured.

Ideally, it should show which parameter file it uses and also it should be able to tnsping with hosts as well right? What could be the issue?

Thanks!

You can only tnsping to a database that is defined in your tnsnames.ora or OID. If I understand well then FIHOST is a server. To see if that is up you must use a common ping.


LOG_RATE_GOVERNOR Wait Stat I'm noticing this wait stat on my Azure SQL database, and I've never seen it before. I've tried Googling this wait stat but haven't been able to come up with any good information on what this is, although I saw a couple mentions of it possibly being related to exceeding the Azure performance tier set for the database.

The different service tiers of Azure SQL Database are limited by DTUs. Microsoft guarantees a minimum level of resources, which they compute as DTUs. The documentation provides the definition for a DTU as (emphasis mine):

  This amount of resources is calculated as a number of Database
  Transaction Units or DTUs, and is a blended measure of CPU, memory,
  I/O (data and transaction log I/O). The ratio amongst these resources
  was originally determined by an OLTP benchmark workload designed to be
  typical of real-world OLTP workloads.

If you are seeing LOG_RATE_GOVERNOR waits, then you are bumping into the limits imposed on transaction log I/O by your performance tier.

If this wait is impacting performance negatively, you would need to either increase your service tier, or update your code to perform fewer writes. If you have one process that is IO intensive, you could throttle it yourself so that you leave transaction log IOs available for other processes before hitting the limit imposed by your SQL DB service tier.

I wrote a blog post where I used the DTU Calculator to estimate how each of these performance metrics combine to affect the DTU calculation. You may find that helpful in understanding how different types of load are "blended" into the DTU limits.


Why would anyone ever disable `index_condition_pushdown` (ICP)? I'm just wondering if there is every a valid reason to disable index_condition_pushdown?

  Index Condition Pushdown (ICP) is an optimization for the case where MySQL retrieves rows from a table using an index. Without ICP, the storage engine traverses the index to locate rows in the base table and returns them to the MySQL server which evaluates the WHERE condition for the rows. With ICP enabled, and if parts of the WHERE condition can be evaluated by using only columns from the index, the MySQL server pushes this part of the WHERE condition down to the storage engine. The storage engine then evaluates the pushed index condition by using the index entry and only if this is satisfied is the row read from the table. ICP can reduce the number of times the storage engine must access the base table and the number of times the MySQL server must access the storage engine.

So essentially if I have a table, people(zipcode,lastname,address) with all columns on the index this permits what other databases call Index-only scans, a query

SELECT * FROM people
  WHERE zipcode='95054'
  AND lastname LIKE '%etrunia%'
  AND address LIKE '%Main Street%';

can be satisfied without visiting the table.

How is it advantageous to visit the table in addition, or to not use the index at all -- the only two options I see that can result from disabling  index_condition_pushdown?

There are two related things...

Covering index:  When the index is "covering" because all columns mentioned anywhere in the SELECT are contained in the index, the query has always been performed via "index-only scan".  MySQL's EXPLAIN indicates it with

   Using index

Think of it as treating the Index BTree as a 'table'.

Index Condition Pushdown:  The relatively new (5.6) 'ICP' applies to other situations -- when the part of the index being used for filtering is not sufficient.

Version 4.0 split the "Handler" from the "Storage Engines".  The "Handler" does parsing, optimization, etc, etc.  The "Engine" talks to the disk.  This implies that rows are often fetched from disk by the engine, then handed off to the handler for more processing.  In some situations, this handoff can be avoided -- by pushing more processing down from the handler into the engine.  ICP is such a case.  In the past, stuff that was not in the index, but tested in the WHERE, was handled only by the Handler.  Now, the engine can handle it.  EXPLAIN indicates it via

   Using index condition

With ICP, InnoDB tries to read the row and make the rest of the WHERE tests.  This (usually) avoids handing off rows that are to be filtered out.

I have not heard of a case to turn ICP off.  I suspect most 'modes' exist "just in case" the Optimizer has a surprise bug for which the best solution is to turn off a feature.

Your query

Are there only 3 columns in the table?  If so, then a "table scan" is identical to an "index scan"; either would suffice.  Given a choice, the optimizer would pick an index starting with zipcode.  But it would not use the other 2 columns in the index for filtering -- because of the leading wildcard.

If there are only 3 columns in the table then a 3-column index would be "covering".

If there are more then 3 columns in the table, ICP lets all the filtering is done in the storage engine.  Without ICP, only the filtering by zipcode is done in the storage engine; the rest would be handed off to the Handler.


SSIS Standalone Installation In my organization we have several SQL Server 2016 Enterprise licenses in use.  None of them are running SSIS.  Since they are not in use, could I install just SSIS 2016 on a separate server and still be covered under the original enterprise license?

You can, but read this article on the implications of doing so.
https://docs.microsoft.com/en-us/sql/integration-services/install-windows/install-integration-services


Any option for mysqldump to ignore databases for backup? We have 40 databases in our server.

We want to take 36 databases backup using mysqldump. How can i ignore remaining 4 databases in mysqldump command? Is there any option for mysqldump to ignore databases for backup in MySQL?

I know the general mysqldump command but it is very lengthy. I want to ignore only 4 databases and need to take remaining dbs backup.

Back on Dec 16, 2011, I answered the question How do you mysqldump specific table(s)?

I collected all tables not including a certain set of table names.

Using the same principles, you could have collect all the database names from the metadata table information_schema.schemata that you want mysqldump'd, create a query to return that list, then use that list of databases to formulate the mysqldump command.

DATABASES_TO_EXCLUDE="db1 db2 db3"
EXCLUSION_LIST="'information_schema','mysql'"
for DB in `echo "${DATABASES_TO_EXCLUDE}"`
do
    EXCLUSION_LIST="${EXCLUSION_LIST},'${DB}'"
done
SQLSTMT="SELECT schema_name FROM information_schema.schemata"
SQLSTMT="${SQLSTMT} WHERE schema_name NOT IN (${EXCLUSION_LIST})"
MYSQLDUMP_DATABASES="--databases"
for DB in `mysql -ANe"${SQLSTMT}"`
do
    MYSQLDUMP_DATABASES="${MYSQLDUMP_DATABASES} ${DB}"
done
MYSQLDUMP_OPTIONS="--routines --triggers"
mysqldump ${MYSQLDUMP_OPTIONS} ${MYSQLDUMP_DATABASES} > MySQLDatabases.sql

All you need to do is put the databases you do not want mysqldump'd into DATABASES_TO_EXCLUDE

Give it a Try !!!


Multiple values in one table; need them to be in separate columns I'm relatively new to SQL so thank you in advance for any help you can give me. I'm writing a query that needs to produce a table that shows all students' majors, minors, and the dept. I've created multiple columns already: (maj, dept, min, dept, concentration, dept). My problem is that my table is producing multiple rows because I have not included anything in my query that allows for students who have multiple majors. So instead what is happening is that they are showing up in multiple rows. I need every student to occupy only one row. Students can have any combination of maj's, minor's, and concentrations (and multiple for each). I'm dealing with 5,000 students. What do I need to include in my syntax? Thank you.

What you need is

a "student" table
a "major" table
a linking table (also known variously as an "intersection" or "association" or "joining" table). The fancy name for this is "Associative Entity" (if you read nothing else, read this!).

1 row per "student" in the student table - this is critical for good design - otherwise you are going to have to keep track of multiple copies of your student data. This will typically contain their student id, name(s), address, tel: & - PRIMARY KEY student_id.

1 row per "major" - again, no multiple copies of data. Typically contains, id major name, department, prerequisites.... &c. PRIMARY KEY - major_id.

The joining table will probably contain only 1 record per student - the brigher/more hardworking ones might have more than 1 record per student and will just simply contain (student_id, major_id) - the PRIMARY KEY in this case will just simply also be (student_id, major_id). So, a student with 2 majors will have 2 records &c.

You will obviously then have a department table - possibly a joining table for department-major for multi-disciplinary majors? I imagine that most (all) majors will have a primary department?

NEVER have a .csv type arrangement where you have multiple "facts" stored in the same field of a database  - there are two reasons for this:

1) it makes SQL programming impossible and

2) it's a breach of Codd's rules (the second one) which states:

  Rule 2: The guaranteed access rule:

  Each and every data (atomic value)
  in a relational data base is guaranteed to be logically accessible by
  resorting to a combination of table name, primary key value and column
  name.

Even if you don't understand them yet, there is a reason why Codd's rules are regarded as fundamental amongst database practicioners.

Start with this - break down the problem, determine your entities (or "objects", or just plain "things"). Have a table for each one. That and joining tables for "things" which are connected in a 1->n or m->n relationship and you'll at least have the basics right. Come back to us if you have more specific requirements after having done that.


SQL Server Data types and operators clarification needed I would like to confirm the following with respect to the basic SQL Queries:

The float and real data types cannot be used with the equals and not equals operator in sql. If so then what else operators can i use for these data types.
Filtering based on the BLOB and XML columns is not allowed. So shall we disable any search based on these datatypes, also is there any other data type that cannot be filtered from the sql query?

[edit]
3. Also what kind of operators can i use for the timestamp data type.

Untrue. You can use them but the "approximate" nature means they could be unreliable. Use range queries (BETWEEN etc) or use decimal
If you use the max types (text, image etc are deprecated) then you can CAST or SUBSTRING etc as needed to search and filter
For XML you can search using XQuery
Note: a binary BLOB that is a word document or such can not be searched in SQL Server
Never tried any. timestamp (deprecated, use rowversion) isn't designed to be manipulatedUnless you mean datetime which as datetime functions


ERROR: Arithmetic overflow error converting float to data type numeric I unfortunately have some bad data and need to work around it. I have a Parcel column in a spreadsheet with some parcels that have inadvertently been converted to floating point numbers. I posted a question to solve this problem Handling sql column with mixed varchar and float data types
However, now I am getting an error when I try the following code:

SELECT CAST(CAST(CAST([Parcel Number 2(max)] AS FLOAT) AS DECIMAL) AS NVARCHAR(255))
FROM XLSIMPORT WHERE [Parcel Number 2(max)] = '1.4515020001e+012'

The error I'm getting is:

Msg 8115, Level 16, State 6, Line 41
Arithmetic overflow error converting float to data type numeric.

The error seems to indicate that the DECIMAL datatype is not defined with the correct precision and scale. The default is DECIMAL(18,0), but something may have happened with your defaults? When I run the query below as you have (just changed to a table variable and added the precision and scale), I am not receiving any error.

DECLARE @test TABLE
([Parcel Number 2(max)] FLOAT)

INSERT INTO @test
        ( [Parcel Number 2(max)] )
VALUES  ( '1.4515020001e+012'  -- Parcel Number 2(max) - float
          )

SELECT CAST(CAST(CAST([Parcel Number 2(max)] AS FLOAT) AS DECIMAL(18,0)) AS NVARCHAR(255))
FROM @test WHERE [Parcel Number 2(max)] = '1.4515020001e+012'

As an FYI, I do get an arithmetic overflow if I try DECIMAL(12,0).


TempDB default number of files in SQL Server 2016 Noticed the docs for SQL Server 2016 CTP2 state "by default, setup adds as many tempdb files as the CPU count or 8, whichever is lower."
https://msdn.microsoft.com/en-us/library/bb510411(v=sql.130).aspx

But I always thought that the "correct" ratio would be half the number of CPU cores, for boxes under 8 cores that is.

Should this be taken as the official recommandation from Microsoft and even applied to 2012/2014 ?

According to Paul Randal the number of tempdb files should be:

equal to the number of CPU cores for 8 or less cores
1/4 to 1/2 of CPU cores for more than 8 cores

I think it's a great news that Microsoft finally changed this default setting. The previous one was really bad - it caused problems with latch contention.

Paul Randal's article on the topic


Bit map of yes/no answers: Should I store it as binary, blob, varchar, or int? I am developing a web app in Laravel where I have to ask users a set of Yes/No questions and save the answers in the database. I thought that I should try and save all answers in binary rather than saving them one-by-one since the number of questions vary from time to time. Say in three questions, the user answered Y-Y-N; that would convert into "110".
Now, on the database side, should I:

save the data as is (varchar)
use binary/blob
convert to int and save sa int

Not sure which option is the best, considering that the system sometimes output 50 questions.
MySQL -V info:
mysql  Ver 14.14 Distrib 5.5.62, for debian-linux-gnu (x86_64) using readline 6.3

One bit per answer

If you are willing to limit the number of questions to 64, then BIGINT UNSIGNED will work somewhat easily, but limited to 64 true/false values.  Let's say q is the column being used.

q & 1<<0    -- the bit for question 1 (assuming question numbers start at 1)
q & 1<<22   -- the bit for question 23
(q & 1<<22)  = 0   "false" for q23
(q & 1<<22) != 0   "true"  for q23
(q & 0x7FF)  = 0x7ff   "true" for all of q 1-11

Etc.

The SET datatype is implemented similarly but is clumsier to use for your case since it wants you to name each bit.

If you need more than 64, then BINARY and BLOB would let you have up to 4 billion bits (in LONGBLOB), but it is much messier to extract a single bit from them.

INT UNSIGNED has only 32 bits (4 bytes), thereby not satisfying your "50".

One byte per answer

It is probably not unreasonable to have the answers in rows -- a table with (student, question_number, answer).  In this case answer could be any of these.  Each takes 1 byte and is about as easy to work on as any other.

CHAR(1) CHARACTER SET ASCII NOT NULL.  -- and insert "T"/"F" (or whatever)
TINYINT UNSIGNED NOT NULL   -- 0=false, 1=true
ENUM("F", "T") NOT NULL


dbvisualizer: number of rows limited by maxrows I'm running a simple query on an Oracle database table like

select * from tableA

and DBVisualizer cuts the query short at 1000 rows.  How do I remove this limit?
Message is "number of rows limited by maxrows".

Max rows is specified just above the SQL Commander tab in the second field from the right.   On the Object View tab it is at the bottom left of the results window.  For a single table query I generally use the Object View window.  It is easy to setup filters in the Data sub-tab.

Setting this value too high will result in a crash when you run out of memory.

Is there a reason you want more than 1000 records returned?  That's a lot of data to scroll through.


User response to Database Mail I'm doing some high level planning of an application at the moment. When a bcp import finishes, I'm going to send an email using Database Mail.

I'd like the email to have options or filters for the recipient. Their choices will determine which particular SQL scripts kick off.

For example:

  from: database@mail.com

  to: henry@mail.com

  Import has completed, would you like to:

+------------+---------+
|  Update    |   Find  |
+------------+---------+

My intuition was that I could make the choices mailto links, to populate the body of a response email. But I haven't been able to find any resources on triggering an alert when receiving an email. In fact, the diagram I found below suggests to me that you actually can't reply to Database Mail:

Has anyone ever worked on a project like this?

Database Mail in itself can only send emails, because it only 'talks' SMTP:

  A Database Mail account contains the information that SQL Server uses to send e-mail messages to an SMTP server. Each account contains information for one e-mail server.

(from the documentation)

But (depending on the configuration of your SMTP server), you can use a valid email address within your organization (a user, or a group like info@example.com) as the sender of the email. Then, the receiver can just reply as they would to a normal email.

If you need to make some automated actions based on a reply (such as scheduling another task in the database), you're looking at a (much) more complicated integration, way beyond the scope of Database Mail.


Why use synchronous_standby_names = 'barman_receive_wal' will cause database aberrant? Use barman's synchronous_standby_names feature in PostgreSQL's postgresql.conf file
synchronous_standby_names = 'barman_receive_wal'

Can't do these operations:

createuser(hanging)
createdb(hanging)
can't save data in primary database if use repmgr for replication

Main configuration for them is
max_wal_senders = 10
max_replication_slots = 10
shared_preload_libraries = 'repmgr'
wal_level = 'replica'
archive_mode = on
archive_command = 'rsync -a %p barman@backup_server:/var/lib/barman/mydb/incoming/%f'
synchronous_standby_names = 'barman_receive_wal'

If remove synchronous_standby_names from this configuration, it works well.

I see you've set up PostgreSQL to push WAL files to barman via archive_command - is barman also set up for WAL streaming? If that's not the case, there will be no synchronous standby with the name barman_receive_wal and hence any writes to the primary will not succeed, as PostgreSQL is waiting for a standby commit confirmation which never arrives.


MongoDB start ID and end ID I want to insert bulk data into a MongoDB.
Later on I want to find exactly the bulk I inserted.

In MySQL I would have done:

 SELECT * FROM data WHERE id >= start_id AND id <= end_id

How can I fix this in MongoDB?

If you know start _id and end _id then you can retrieve the bulk you inserted using query like this:

db.yourCollection.find({_id:{$gt:ObjectId("56e13d5aed63a8477a7e2e78"), $lt: ObjectId("570f5a27b2d1a50996d3a0f0")}})

Replace the default mongodb ObjectIds with your _id fields.

Also, if the _id you are using is default mongodb ObjectId then you can easily compare them using above query since mongodb ObjectIds are monotonically increasing.


SSRS Report returning old data I have created and tested a report in Visual Studio and it returns data from a SQL server database.
Once uploaded to SSRS, the report returns old data.

What would I need to check as it appears the data source has cached?

Update: I have created a table in the SQL database, and can query it from VS and report builder, but not from a report in SSRS.

When a report is first run in Visual Studio, it creates a cache of the data that is used for all subsequent executions, as long as you don't change parameters. You can refresh the data by either changing parameters, or deleting this cache of the data.

To do so, navigate to your solution file and find the file for the report like YourReport.rdl.data and delete that. The next time you preview your report, it will query the database for the fresh data.


Why this query returns rows that I don't want to be there I'm trying to running this query:

SELECT *
FROM
    (`default_users`)
    LEFT JOIN `default_profiles` ON `default_profiles`.`user_id` = `default_users`.`id`
WHERE
    `default_users`.`group_id` = '3'
    OR `default_users`.`group_id` = '4'
    OR `default_users`.`group_id` = '5'
    AND `default_users`.`email` LIKE '%polar%'
    OR `default_users`.`username` LIKE '%polar%'
ORDER BY `default_users`.`id` DESC

But get unwanted results and don't know the cause and by unwanted results means webmaster which I think isn like %polar%. Here are some sample data for testing http://pastebin.com/Fyx0TcGY, any advice?

Yes @jon-seigel you're right and thanks for your suggestions didn't see it before :( I add some parentheses and now it works see the right query below:

SELECT * FROM (default_users) LEFT JOIN default_profiles ON
    default_profiles.user_id = default_users.id WHERE
    (default_users.group_id = '3' OR default_users.group_id = '4' OR
    default_users.group_id = '5') AND (default_users.email LIKE '%polar%'
    OR default_users.username LIKE '%polar%') ORDER BY default_users.id DESC


Multiple SELECT vs single SELECT over TCIP My question is a performance one:

I need to select roughly 400+ rows, i know their ids.

currently i loop in code and fire off multiple queries over tcp/ip - because its easy:

...
select [columns] where id = 1544;
select [columns] where id = 4765;
select [columns] where id = 3544;
select [columns] where id = 4090;
select [columns] where id = 5060;
...

Would it be more efficient on SQL server to fire off a single query for a date range instead (the rows I'm after are date-sequential, but we currently don't have a date field in that table), keeping in mind the overhead of multiple transactions vs a single transaction:

select [columns] where date between x AND y;

With SQL server (and, I suspect, pretty much everything else) querying for a range will be more efficient than querying for many individual rows, even when working locally. The range query will likely result in an index seek followed by a short scan through the few subsequent the index pages (followed by any other page lookups needed), then many single selects will result in many individual index seeks (followed by any other page lookups needed).

The "where in (<static list>)" option mentioned by user503207 may perform similarly to the date based range query in SQL Server, try it and see what query plan results, though in some DBMSs (mySQL for instance, unless that has improved in that area since I last used it) such queries can be quite inefficient (sometimes resulting in a full index scan).

As your IDs are in sequential order by date and you know which ones you are needing (or you wouldn't be able to put together the list of individual selects) then you could instead sort them when producing your query to find the range and use where id between <first> and <last> which would also avoid needing to add the date column.

When considering doing the same remotely the query performance difference will be the same if all the selects are sent as a single batch, though you will see a little more bandwidth used because transferring many small resultselts is less efficient than a single larger one containing the same rows. If you are submitting the selects individually then you will see a significant slowdown as you are adding network latency to each individual query instead of to the batch as a whole.


AWS RDS Really Odd Error... #1041 OUT OF MEMORY, Buffers OK and Memory OK I have an AWS RDS (MySQL 5.6.35) db.m3.medium that has given me a random error when attempting to modify a table's structure in the past two weeks:

#1041 - Out of memory; check if mysqld or some other process uses all available memory; if not, you may have to use 'ulimit' to allow mysqld to use more memory or you can add more swap space

I've never encountered this error on even a much smaller instance with much less memory. It should be noted that encryption is enabled on this instance, so that might be adding a little bit of load.

When I check the memory statistics in the console, it doesn't appear to be even using anywhere near 100% usage. It doesn't appear that the instance is swapping at all either. After a reboot, I can modify tables fine. This table is very small... < 10 columns, little overhead, and not many rows (<100). Oddly enough, we have not modified the default buffer sizes in RDS (3/4 of instance memory allocation). Not only this, after checking the buffer tables, everything seemed OK as well.

Am I missing something here?

UPDATE: It happened again. Rebooting the RDS server appears to mitigate whatever problem is occurring. Here's a screenshot of buffer statistics at the time of the incident (this is BEFORE the reboot that fixes the problem)...

UPDATE 9/1/2017: I thought that we mitigated the issue for a while after we upgraded our RDS instance to m4.large... and for a while, it did. However, while performing some migrations earlier this evening... sure enough, error #1041 - OUT OF MEMORY. I immediately checked our CloudWatch reports for anything out of the ordinary, and our freeable memory is really high, not even concerningly fluctuating. Additionally, a reboot fixed the issue. I have yet to see it happen again, but I'm sure in a few weeks after some transactions it will happen again. Is this possibly a sign of a corrupted database or something? We have NEVER had any issues reading or writing to the database, and all applications are using their databases on this server just fine. We just can't initiate ALTER TABLE changes from inside phpMyAdmin...

I do not believe you are alone here. AWS has push me from 5.7.11 to 5.7.17 and now I am unable to perform Alter table commands when there is any sort of memory pressure on the Database.

Have a look at https://forums.aws.amazon.com/thread.jspa?threadID=251866

At this point it looks like there may be an issue present in MySQL 5.6 < 5.6.37 and 5.7 < 5.7.19.

Unfortunately AWS RDS does not have any 5.6 images > 5.6.35 or 5.7 > 5.7.17

Before using 5.7.17 I had used 5.7.11 for some time without issue. If you are able to dump/restore your databases that might be the best option for you, otherwise you can give larger instances a shot.