Oracle Database 11G Installation "Error al llamar al destino 'install' del archivo make" I get this error installing Oracle Database 11g in Linux..

INFO: /usr/lib64/libstdc++.so.5: referencia a `memcpy@GLIBC_2.14' sin definir

INFO: collect2: error: ld devolvió el estado de salida 1

INFO: make: *** [ctxhx] Error 1

INFO: Salida final del proceso iniciado.
INFO: ----------------------------------
INFO: Excepción devuelta de la acción: make
Nombre de la Excepción: MakefileException
Cadena de la Excepción: Error al llamar al destino 'install' del archivo make '/o/app/oracle/product/11.2.0/dbhome_1/ctx/lib/ins_ctx.mk'. Consulte '/o/app/oraInventory/logs/installActions2018-02-06_08-13-05AM.log' para obtener más información.
Gravedad de la Excepción: 1

Solved with:

Try to link with g++ instead of gcc.

cd /bin
mv gcc gcc.save
ln -s g++ gcc

and run the installer again. as suggested on: https://stackoverflow.com/questions/28774848/oracle-database-installation-issue-in-ins-ctx-mk


Get data from multiple databases at once I need to UNION data from tables with the same name in multiple databases. All databases have the same prefix: CDNXL_(NAME OF DB)

How would I execute this query against more than one database at at time?

SELECT
    prac_id,
    prac_name,
    prace_surname,
    prac_IDcard,
    prac_workplace
From prac_nag.CDNXL_ (NAME OF DB)
where prac_IDcard = @parameter

Can this be done with  'IF' or 'WHILE'?

-- Edit
Now I can see my mistake.

I have got 46 Databases with prefix CDNXL_ but one is DB with CDNXL_Configuration, and doesn't have got table was I wanna. How can I skip/ignore this DB?

One of the methods you can try is sp_msforeachdb. Making some assumptions about your datatypes in the @table below...

declare @results table (
    prac_id int,
    prac_name nvarchar(100),
    prace_surname nvarchar(100),
    prac_IDcard nvarchar(100),
    prac_workplace nvarchar(100)
);

insert @results
exec sp_msforeachdb N'
use [?]

if left(''?'',6) = ''CDNXL_'' -- only execute the query against databases
                              -- that match the naming pattern
    and ''?'' <> ''CDNXL_Configuration''
begin
    select
        prac_id,
        prac_name,
        prace_surname,
        prac_IDcard,
        prac_workplace
    from prac_nag.[?]
    where prac_IDcard = @parameter;
end;
';

select * from @results;


SQL Server imports data in wrong charset with SSIS I have a flat file which I am importing into a SQL Server table using a SSIS package. The code page is set to 65001.

However, when I run a

SELECT * FROM tableName

the data appears mangled. For some reason it is not being stored in Unicode. The column is nvarchar, so that should not be an issue.

Also, if I insert data manually, the data is stored as expected.

What could be going wrong?

The columns in the connection manager that your datasource is using need to be defined as "Unicode String (DT_WSTR)".


The  following gives you the most recent record per id

SELECT * FROM
(
   SELECT a.* ,
   ROW_NUMBER() OVER (PARTITION BY ID ORDER BY date DESC) rn
   FROM USER1.ROAD_INSPECTION a
)a
WHERE a.rn =1;

I'm not sure what you mean by complications when querying column of type DATE. 2010-01-01 is the same as midnight of Jan 1, 2010.

Update. Fixed missed FROM clause.


Can XtraDB be enabled on MariaDB 10.2 Can we create a table in MariaDB 10.2 with XtraDB engine?

I have installed MariaDB 10.2 and I am trying to create a table with storage engine XtraDB and I am getting error

*mysql> CREATE TABLE T1(ID smallint(6),NAME varchar(30)) ENGINE=XtraDB;

ERROR 1286 (42000): Unknown storage engine 'XtraDB'

If I lists the engines of MariaDB here, there are no XtraDB engine type available.

Part of show engines output:

As far as I understand, the answer is no, you can't use XtraDB in MariaDB 10.2. From the Changes & Improvements in MariaDB 10.2 page:

  XtraDB in 10.2 is not up to date with the latest features of InnoDB and cannot be used. As the InnoDB on disk format is identical to XtraDB's this will not cause any problems when upgrading to MariaDB 10.2. See Why does MariaDB 10.2 use InnoDB instead of XtraDB?


Problems attaching a SSAS database I am attaching a SSAS database that I have just moved to a different drive following the below link:

Attach and Detach Analysis Services Databases

First I had to grant permissions on the new drive to the ssas services account:

Second when I attach the database I get the following error:

  TITLE: Microsoft SQL Server Management Studio

  Errors in the metadata manager. The operation cannot be executed since
  the database folder '\?\F:\SSAS\Data\Boden BI DEV 9_02.0.db' for the
  database with the name of 'Boden BI DEV 9_02', ID of 'Boden BI DEV
  9_02' already exists.  (Microsoft.AnalysisServices)

As you can see on the picture below

It says the database already exist, but I have detached it from the engine, as you can see on the picture below.

I found what the problem was. I had not deleted the database from its original location, after I detached it.

I copied the database to the new drive, which on my case is T:.

After I deleted the database from its original location, in my case F:\ssas\data, then I could attach the database from T:.

After attached, I checked on the  F:\ssas\data folder and SSAS had created the following structure there, see below.


"Cheating" Normal Form I'm converting from using an Excel spreadsheet to an app with a MySQL database in a pool league I manage.

I understand about eliminating/minimizing redundancy to avoid data corruption. So in a spreadsheet where I have 3 columns for Bonus1, Bonus2 & BonusT, where BonusT=Bonus1+Bonus2, in the table I would just have Bonus1 & Bonus2 and if I want to use BonusT i just calculate on the fly.

But suppose the calculated field is a lot more difficult to calculate, involving data from many other records. For example I'd like to keep track of a player's number of games played in the league because I calculate the change in handicap after every match and do it differently depending on how many matches he has played.

I could add a record to the user_meta table (this is WordPress) for his number of games, and increment it when adding a new match. Or, I could do some sort of nested query to get this info every time.

I'm just wondering how complex the subqueries (or other methods I'm new to this) have to get before "cheating" normal form is justified and becomes appropriate?

I suggest you go with the normalized data model and use JOINS. In practicality, sometimes there is no need to drill down to atomic level, you can stop the level of aggregation based on the requirement. Also, if the historical data is important for you, you could consider the historical table instead of updating the meta data information. For this you can refer, Slowly Changing Dimensions (SCD).

If calculation involved complexities, understand the relationship between respective entities and capture them in table. This you can achieve by creating stored procedures or functions.

I hope, this answer will help you to get start. Thank you.


What utilities can I use to simulate I/O load for a DB restore and DBCC check? I'm trying to figure out the peak load I can sustain on new hardware I procured for validating backups and performing DBCC checks. I've been using Crystal Diskmark to get throughput stats which helped me benchmark sequential I/O for the copy/restore tasks. I'm having trouble gauging how much random I/O I can sustain for the DBCC check. I'm thinking about using iometer and sqliosim but want to know config would work best to simulate a DBCC check.

The hardware I'm testing consists of one R720 with dual E5-2609s for 8 cores, 32 GB RAM, Windows 2008 R2 Standard, SQL Server 2008 R2 Standard with SP2, and a PowerVault 3620f with 24 15k SAS spindles hooked up to two dual port HBAs on the R720. I've been experimenting with 4, 8 and 12 spindle RAID 0 groups (I can afford to lose the fault tolerance as the DBs have a life expectancy of minutes as part of the testing process).

I'm thinking I can run multiple simultaneous DBCC checks with the above hardware without hitting disk contention. I have the option to upgrade the RAM to 64 GB and the O/S to Enterprise but probably can't upgrade the SQL to Enterprise due to licensing costs.

Any suggestions on how to determine the max random I/O for DBCC using iometer, sqliosim or another utility would be deeply appreciated.

SQLIO is good for doing raw disk IO load testing, but a good way to simulate a comprehensive load testing is to use the SQL Profiler tool included with most sql server installations or using the SQL Load Test utility. Here is a link to some different ways to use these utilities to achieve your goal using profiler or other stock utilites.

You can simply run profiler during a normal load that you would like to record, save it off, and replay it against the server to load test under realistic load. If you are using SQL server 2012, you can use the Distributed Replay utility for performing this same kind of testing, but from multiple sources to simulate load. If you are looking for a commercial option, you could look at Quest Software's tool called Benchmark Factory that can be used to perform SQL Server load testing.


MySQL: SELECT more data than necessary or use multiple SELECT queries? I'm wondering if it would be better to use a single query and SELECT more data than I might need, or use multiple queries and SELECT only what I need when I need it. For example when the client submits a username and password, my PDO query string is this:

$sql = "SELECT passwordHash FROM users WHERE username=:username";

Then in PHP I verify the submitted password against the hash. If it succeeds, I do more queries to get more data like name and address. But would it be better to add those to my initial password hash query so that I'll already have it in the likely event that the password matches? That way I'd save a query. If the password is wrong though, I'd be wasting that lookup of extra information. In general, would it be better to do less queries and get more data each time, possibly more than necessary, or to do more queries and only get exactly the data I need each time?

The answer We should forget about small efficiencies, say about 97% of the time: premature optimization is the root of all evil. Yet we should not pass up our opportunities in that critical 3% is at softwareengineering.stackechange.com


How to log all SQL queries for a single connection or user or client host? Is there a way to log all SQL queries in Postgres 9.3.9 for a single connection or user or client host?

In other words, I want log_statement = all but only for a certain username or client hostname or client ip or connection id.

One option is to log SQL activity for all clients and search the logs. I'm hoping there's a better way.

I don't know how to do it for a client host, but for a single database user, you can use this (as a superuser):

ALTER ROLE someone SET log_statement TO 'all';

Which will then turn on log_statement every time that that someone connects in the future.

For a single connection which is cooperating with you, you could create this function (as a superuser):

create or replace function set_log_statement() returns void as $$
 set log_statement='all'
$$ language sql security definer ;

And then have the desired single connection invoke this function to turn on logging for itself.


SQL Server 2005 Merge Replication I've a large Db and most of tables have relationship with each other.
I wanna make Merge replication with another SQL Server. The problem is that when i create Merge replication it gives error "tblItem couled not be dropped, it has some foriegn key constraints". How can i avoid this problem and is there way to do it without dropping any article.

You can change the properties of the article (or of all the articles) when you create the publication.  You can also change this after the fact by editing the publication.  You can setup the replication to drop the tables (the default), truncate the data, delete the data, or do nothing.  IF you need to load the data on the subscriber from scratch you'll need to setup the articles to delete the data.  This means it will take a lot longer to apply the snapshot.  It would be best to make sure that all the foreign keys exist in the publisher so that the publisher knows in what order to drop and recreate the tables on the subscriber.


InnoDB Foreign key updating vs. manual in-application updating I've been working on a "legacy" schema in MySQL Workbench that uses a lot of relations. All of the tables are MyISAM, however, and there are no foreign keys.

However, I'm working on a new area of the schema now, and am wondering:

Would it be a good idea to force InnoDB and foreign keys for their ON UPDATE/ON DELETE triggers, or to implement the cascading logic in the application itself?

My super/the CTO seems to be remarkably inflexible when it comes to migrating to InnoDB/FKs (or using a real database construction program instead of phpMyAdmin) and I'm not sure if the gains for having auto-updating would be worth fighting over.

Probably the worst part about this issue is that if I went the application-side route I'd have to add code outside of the specific module, which would probably involve more issues and difficulty -- the system is already pretty complex as it is.

It is better to let InnoDB enforce foreign keys. Trying to implement referential integrity in an application can be brittle and would obligate you to write

rollback operations
check constraints on keys
manual cascading of DELETEs and perhaps UPDATEs

InnoDB is equipped for all that and more. Plus, operations done in application logic is difficult to rollback, especially if one uses stored procedures or user-defined triggers.


Delete rows in parent table based on criteria in child table - Oracle I have a table B with a foreign key to table A. I want to DELETE some rows in table B, and I also want to DELETE their parent rows in table A. However, the delete criteria is based on table B. The rows in table A cannot be deleted first because the reference in table B restricts the deletion, but I also have to get the keys of A from the rows to delete in B.

Here is a SQLFiddle with a sample table structure:
http://sqlfiddle.com/#!4/f156c/4/0.

My first inclination was to attempt to save the keys by SELECTing them from B into a variable, and then use that to DELETE from A.

DECLARE
  A_ID_TO_DELETE DBMS_SQL.NUMBER_TABLE;
BEGIN
  SELECT A_ID BULK COLLECT INTO A_ID_TO_DELETE
  FROM (SELECT A_ID
        FROM B
        WHERE LENGTH(B_DATA) > 4
       );

  DELETE FROM B
  WHERE LENGTH(B_DATA) > 4;

  DELETE FROM A
  WHERE A_ID IN A_ID_TO_DELETE;
END;
/

But that just gives an PLS-00382: expression is of wrong type error. The error itself is coming from the DELETE on A; I know this because if I comment it out, the block runs.

How can I either get around the expression is of wrong type error, or what's another way to approach this?

Oracle version: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

(Yes, I'm well aware of how old that is. Client's choice of DB, not ours.)

Try like this,

DECLARE
     A_ID_TO_DELETE DBMS_SQL.NUMBER_TABLE;
BEGIN
     SELECT A_ID BULK COLLECT INTO A_ID_TO_DELETE
     FROM (SELECT A_ID
           FROM   B
           WHERE  LENGTH(B_DATA) > 4
          );

     DELETE FROM B
     WHERE LENGTH(B_DATA) > 4;

     FORALL i IN A_ID_TO_DELETE.first..A_ID_TO_DELETE.last
          DELETE FROM A
          WHERE A_ID = A_ID_TO_DELETE(i);
END;
/


Are Availability Group Databases on a Secondary replica automatically set to read-only by the AG? I have read through the MS docs on read-only routing, but I am not talking about that.

Currently, if I execute an UPDATE against a database that is on a Secondary replica and is part of the Availability Group, I receive a message:

Msg 3906, Level 16, State 2, Line 6
Failed to update database "dbName" because the database is read-only.

My question is whether this configuration is established by default (and unavoidable) by virtue of the database being part of the AG and on a Secondary at the moment?

Some interesting extras:

The following SQL, run on that Secondary, shows I have UPDATE, ALTER and a whole lot more permissions against this database:

SELECT *
FROM fn_my_permissions(null, 'DATABASE')
ORDER BY subentity_name, permission_name;

The following SQL explicitly shows I have permission to UPDATE a table in that database:

SELECT *
FROM fn_my_permissions('dbName.dbo.tblName', 'OBJECT')
ORDER BY subentity_name, permission_name ;

In spite of this I am actually prevented from updating that table. This implies:

The output of fn_my_permissions is not strictly correct
The database "read-only" setting overrides individual permissions

I understand the setting in play is configured via:

ALTER DATABASE dbName SET READ_ONLY

and

ALTER DATABASE dbName SET READ_WRITE WITH NO_WAIT

The main reason for the question is to understand whether the "read only" configuration is a DBA/designer's responsibility during design and deployment of the AG, or an expected (and guaranteed?) feature by simple virtue of using an AG.

I would give the docs a good read. Particularly the section on availability replicas.

  Every availability replica is assigned an initial role-either the primary role or the secondary role, which is inherited by the availability databases of that replica. The role of a given replica determines whether it hosts read-write databases or read-only databases. One replica, known as the primary replica, is assigned the primary role and hosts read-write databases, which are known as primary databases. At least one other replica, known as a secondary replica, is assigned the secondary role. A secondary replica hosts read-only databases, known as secondary databases.

As can be seen above, each replica can either be read-only or read-write (secondary or primary respectively). In addition there can only be one primary (read-write) replica. When we refer to replica in this respect, we are referring to the database level. Each server can host multiple availability groups, with potentially mixed primary-secondary configuration.

The situation you are referring to with multiple read-write replicas is not supported by AlwaysOn Availability Groups, which are designed to meet High Availability and Read-Scale needs, not multi-master setups. You should look at two-way replication technologies to support this need.

As for the read-only setting itself, this is automatically set by the availability group, although it's worth saying that this is not the same setting as the "Database Read-Only" setting found in the database properties (or is_read_only in sys.databases), nor is it the same setting as the file group level read-only option.


What is causing Waiting for table level lock errors? We got the database hanging twice already and trying to find a cause.

show processlist
Waiting for global read lock | INSERT INTO {myisam_table} ...

In here the disk space was full so we thought the problem was over after giving it some more but the next day at midday it hanged again:

show processlist
Waiting for table level lock | UPDATE {myisam_table} ...

What could be causing it?

Mysql Default Engine: InnoDB.

Database has a mixture of tables with both MyISAM and InnoDB engines.

Log posted here:

http://arturito.net/2013/08/28/mysql-waiting-for-table-level-lock-errors/

INITIAL OBSERVATIONS

Process ID 42686 says its preparing to execute a SELECT query
There are some sleeping connections
All other processes cannot acquire a table lock
I would have expected an UPDATE, DELETE, or INSERT to do the lock. There are no claiming ownership of the table in question.
Can't see the full query in Process ID 42686, but I suspect it involves a JOIN, GROUP BY, or ORDER BY

WORKING THEORY

If you ran out of diskspace with the process list you gave me, then we can place blame on the MyISAM storage engine. Why?

In your particular case, it is not one of your tables. If a JOIN,GROUP BY, or ORDER BY was being executed and a temp table was being written to disk (on disk temp tables use the MyISAM storage engine) the MySQL simply freezes when out of space. How do I know that ?

According to MySQL 5.0 Certification Study Guide

Page 408,409 Section 29.2 Bulletpoint 11 says:

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

I have discussed this situation before

Apr 05, 2013 : "Site Offline" MySQL server failing to start and stop
Dec 17, 2012 : How to solve MySQL “The table is full” error 1114 with Amazon RDS?
Apr 25, 2012 : Very Large Log Files
Mar 15, 2012 : Why do MySQL tables crash? How do I prevent it?

Something tells me that you have one of these two situations

disk-based temp tables for your SELECTs and competing for space with your regular data
If temp table are landing in /tmp in the root partition, that's running out of space

SUGGESTIONS

Suggestion #1: Map tmpdir to another disk

[mysqld]
tmpdir = /another/disk/besides/root/partition

Suggestion #2: Create a RAM Disk

Run this code to install a RAM disk that will available on Linux reboot.

RAMDISK_SIZE=32g
service mysql stop
mkdir /var/tmpfs
echo "none   /var/tmpfs  tmpfs  defaults,size=${RAMDISK_SIZE} 1 2" >> /etc/fstab
mount -t tmpfs -o size=${RAMDISK_SIZE} none /var/tmpfs
cp -R /var/lib/mysql/* /var/tmpfs
mv /var/lib/mysql /var/lib/mysql_old
ln -s /var/tmpfs /var/lib/mysql
chown -R mysql:mysql /var/tmpfs
chown -R mysql:mysql /var/lib/mysql
service mysql start

Then, map tmpdir to /var/tmpfs

GIVE IT A TRY !!!


How do I efficiently partition a large MySQL (Aurora) table with multiple search columns? I have a large and rapidly growing table.  It takes in about 5k rows / second and that will be doubling in about a month.  The table is Amazon's Aurora (heavily modified InnoDB.)

As of today, total aggregate rows is around 4B.

Data is mostly searched on in 6 ways:

count(distinct col1,col2) where timestamp range (1 day range)
count(distinct col1,col2) where timestamp range (30 day range)
count(1) where country IN (list,of,countries) and timestamp range (30 day range)
count(1) where foreign_key = int and timestamp range (1 day range)
count(1) where foreign_key = int and timestamp range (30 day range)
select * where timestamp range (1 day range)

The foreign_key, timestamp and country columns are all indexed.

Data is only really "active" for a 90 day periods with most select's happening between -42 and -12 days.

How do I effectively partition this able to speed up those 6 queries?

And, almost as important, how do I accomplish this with minimal downtime?
(assumption here is to create a new table, move all inserts to the new table & copy old data in... like I would do with any new index)

I would consider using a date range as the partitioning scheme, and splitting by week which you can do using a function against the timestamp field.
Using days would create too many partitions, and months would not help your queries much especially when the range spans two months.

Using the range partition, you can create mutliple partitions covering your active time period, and out into the future for a year, then nearer the end of that range you can add partitions to the schema as required.
If necessary, you can also drop the old partitions when they are no longer needed.

I can't prepare a sample partition schema for you, as I'm running short of time at the moment, but the MySQL docs have pretty good coverage for this type of partitioning.

Hope that helps,
Dave


Prevent MariaDB from filling up tmpdir and hanging the system We're running MariaDB version 10. To speed up queries we have set up tmpdir to be RAMDISK with size of 35GB.

The challenge we have is that from time to time (like once or twice a week) MariaDB fills the tmpdir, after which the whole system freezes up till we have to stop MariaDB, unmount the tmpdir and remount.

Initially the tmpdir was 15GB we have been increasing it over time with hope that more space will prevent MariaDB filling up the tmpdir but we realize it's not ending. We could still expand tmpdir but we don't have unlimited RAM and are not sure how much will be enough.

At this time we're wondering whether there's a way to prevent MariaDB from filling up tmpdir? The alternative is to move tmpdir to disk but this is a route we're avoiding since it would present a performance nightmare.

open_files_limit=5000
query_cache_size = 0
wait_timeout=200
tmp_table_size=512M
max_heap_table_size=512M
thread_cache_size=64

key_buffer_size=256M
max_allowed_packet=512M

table_open_cache=10240
table_definition_cache=32768

delayed_insert_timeout=20 # Turn on if max_connections being reached due to delayed inserts
delayed_queue_size=300 # Turn on if max_connections being reached due to delayed inserts

myisam_sort_buffer_size=512M # can be increased per sessions if needed for alter tables (indexes, repair)

query_cache_limit=32M # leave at default unless there is a good reason
query_cache_type=1

read_rnd_buffer_size=16M # leave at default unless there is a good reason
read_buffer_size=2048M

collation_server=utf8_unicode_ci
character_set_server=utf8

general_log=0
log-output=TABLE # select * from mysql.general_log order by event_time desc limit 10;

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
innodb_file_per_table=1
innodb_buffer_pool_size=16448M
innodb_log_buffer_size=100M
innodb_thread_concurrency=8 # Number of physical + virtual CPU's, preset when server is provisioned to have correct # of cores
innodb_change_buffering=all
innodb_flush_log_at_trx_commit=0
innodb_support_xa=0
innodb_doublewrite = 0
innodb-flush-log-at-trx-commit = 2

tmp is used when mysql/mariadb engine can't use ENGINE=MEMORY for temporary tables. That happens for many reasons:

Temporary table is too big to fit into join/sort buffer. That may
be caused by too small buffers or by queries that produce too big
results due to the bad design.
Temporary table contains text/blob fields that are not appropriate
for MEMORY engine.
Proper index(es) are missed for the query, so massive filesorts performed on the disk in the mysql/tmp dir.

Anyway as far as disk-based temp-tables are significantly slower than in-memory ones, queries that fill up the mysql/tmp dir are listed in the slow queries log along with short diagnosis.


MYSQL Workbench Column Default/Expression MYSQL Workbench Column Default/Expression

What can be put in here, I see I can put in a default value for the field

Can I also put mysql statements? Or is this just for default values?

For a Column detail, if I check the "G" for generate it will create a statement

GENERATED ALWAYS AS () VIRTUAL ,

What does this mean, and where can I find documentation on how to use this?

You are kind of mixing 2 different topics.

DEFAULT clause only accepts constants in MySQL. For example 0, -1, '', or '2000-01-01'. Workbench cannot override this limitation.
Virtual columns may be what you really need (or not). They have an automatic value determined by an SQL expression (no queries), but you cannot change that value. Check this article for more details.


Only describing postgres tables with "\d" (not sequences, indexes, views, ...) I want to describe every table in a postgres database.

Currently I have this:

psql -d db -c "\d *" > db_description.txt

But this also includes all sequences, views, etc.

Is there a command where the * pattern only matches tables?
Or is there another way to do this?

How about simply using the \dt command? It's here to list tables only. You can get more help on PostgreSQL inner commands by issuing the \? command.

If you want to know what those commands do behind the scenes, you could also start psql with the -E option, as asked here. You could therefore rewrite this query to get exactly what you want, be it views, sequences, tables... or any combination of the above.


pt-table-sync for 3 master servers We had a mysql multi master with 3 nodes and unfortunately the replication broke. We have some set of tables which has to be in sync with other database servers, so i was looking into Percona-Toolkit for trying out the sync between these 3 servers. I could able to sync the records in the tables between 2 db servers in bidirectional way.

Is there any way to sync bidirectionally with 3 servers using pt-table-sync ? Or any steps involved in syncing three db servers?

Thanks,
Swaroop.

first of all, please have a look at this recent response about multi-master replication, where I remark how likely is this setup to create problems and some alternatives. This was probably a direct or indirect cause of your desync.

Bidirectional sync is supported in pt-table-sync, but not in a replication environment, and only between 2 servers. You must unlink your slaves from the replication process in order to apply it, separately for each pair. This will likely create downtime, at least for your writes. The reason for this is because it is implemented by checking rows that do not exist on the other node, and they are added. It does not handle deletes and complex situations.

Bidirectionally syncing 3 servers automatically is technically impossible, because if the 3 of them have the same row with different data, which one is right? You can still use the tool to help you define your own rules (e.g. tableX in server A is the reference one), or detect differences and apply the changes conditionally (pt-table-sync can log the changes before being applied).

The normal procedure for pt-table-sync is to use it in a single-direction replication, as you usually only have one master "reference" database (again, a problem with multi-master replication), and more reliable sync process is available. It also allows syncing multiple nodes at the same time. I recommend you to explore the options on my first link to avoid these kind of issues in the first place.


table_open_cache won't increase above 431 - MariaDB 5.5 Moved a few sites to a new server running CentOS 7, Plesk Onyx, 32gb, 2x480gb SSD, Intel(R) Xeon(R) CPU D-1521 @ 2.40GHz.

[root@server ~]# mysql -V
mysql  Ver 15.1 Distrib 5.5.52-MariaDB, for Linux (x86_64) using readline 5.1

Trying to tune MySQL, but am stuck on the table_open_cache not going above 431.

| Open_tables                              | 431         |

My /etc/my.cnf.d/server.cnf as follows:

skip-name-resolve               = 1

innodb_file_per_table           = 1
innodb_buffer_pool_size         = 16G
innodb_buffer_pool_instances    = 16
innodb_log_file_size            = 2G

tmp_table_size                  = 256M
max_heap_table_size             = 256M

thread_cache_size               = 4
table_open_cache                = 2000

query_cache_type                = 0
query_cache_size                = 12M

Already checked the OS limit:

[root@server /]# ulimit -n
4096

Not sure where to look next.  Any ideas or help would be awesome.

Based on Open_tables being 431, I assume that you have only 431 tables in all the databases?  Or at least only that many are in use?

What is the value of table_open_cache?

What is the value of Table_open_cache_misses / Uptime?  If that is less than, say, 1/sec, then the table_open_cache is "big enough".


Group by in DAX like in SQL How can I convert this SQL statement to DAX?

SQL query and tables:

select SUM(Kiekis )
from Info
left outer join Prekes on Info.PrekeID = Prekes.PrekeID
left outer join PrekiuGrupes on Prekes.PrekiuGrupeID = PrekiuGrupes.PrekiuGrupeID
group by PrekiuGrupesID

SUMMARIZECOLUMNS is the main function to use here.

So this:

select SUM(Kiekis )
from Info
left outer join Prekes on Info.PrekeID = Prekes.PrekeID
left outer join PrekiuGrupes on Prekes.PrekiuGrupeID = PrekiuGrupes.PrekiuGrupeID
group by PrekiuGrupesID

would be simply

   EVALUATE (
         SUMMARIZECOLUMNS(PrekiuGrupes[PrekiuGrupesID], "TotalKiekis", Sum(Info[Kiekis]) )
   )

DAX traverses the active relationships in the model, so there's no need to use explicit JOINs.  And instead of having a structured query language DAX uses function argument types to detect the different "clauses".  The first string argument to SUMMARIZECOLUMNS indicates the transition from "group by" columns, to pairs of (String,Expression) to specify the result columns.


Repeating rows of date intervals Between dates Firebird I am stuck in a case that on my financial sys, I need to add a option for value to be monthly, weekly or every 2 weeks

example:

DESC    |   VALUE |    DATEADD |  FREQUENCY
--------+---------+------------+-----------
SALARY  | 1000,00 | 26/03/2017 |         30 (MONTHLY)
DINNER  |   50,00 | 26/03/2017 |          7 (WEEKLY)
BUSTICK |   75,00 | 26/03/2017 |         15

select
    *
from
    tableexpensives
where
    datesys between '01/01/2017' and '31/12/2017'

so it should show on sql

APR | SALARY   | 1000,00 | (1x month)
APR | DINNER   |  200,00 | (4x month)
APR | BUSTICK  |  150,00 | (2X month)
MAI | SALARY   | 1000,00 | (1x month)
MAI | DINNER   |  200,00 | (4x month)
MAI | BUSTICK  |  150,00 | (2X month)
....

Any idea how to do that?

Your question is not 100% clear, and I'm proposing a tentative answer using PostgreSQL (I don't have FireBird installed at the moment). The concepts are the same regardless of database make.

1st: I would represent data in an easier way:

CREATE TABLE expenses
(
    description varchar,
    amount numeric(10,2),
    dateadd date,
    times_per_month numeric(10, 0)
) ;

INSERT INTO expenses
    (description, amount, dateadd, times_per_month)
VALUES
    ('SALARY',    1000.00, '2017-03-26', 1),
    ('DINNER',      50.00, '2017-03-26', 4),
    ('BUSTICK',     75.00, '2017-03-26', 2) ;

From this point on, and assuming you would like this data to appear under the heading MARCH (and not APRIL, MAY, ... forever), you would just use something very simple: a multiplication.

SELECT
  to_char(dateadd, 'MON') AS month,
  description,
  (amount * times_per_month) AS subtotal
FROM
  expenses ;

And you'd get:

| month | description | subtotal |
|-------|-------------|----------|
|   MAR |      SALARY |     1000 |
|   MAR |      DINNER |      200 |
|   MAR |     BUSTICK |      150 |

Check it at: http://sqlfiddle.com/#!15/34efd/1/0


Which one to use? InnoDB Replication or MySQL Cluster? I am building a url shortener and I use InnoDB as the storage engine for link data. I will start with a single VPS containing both application instance and MySQL database instance on the same Virtual Machine. I want the application to be easily scalable, I want to be able to add new nodes and make the system perform better as easily as possible when needed.

I have been reading about MySQL Cluster and MySQL Replication, but I haven't been able to decide on which one to use. You can imagine that (a popular) url shortener will be both write and read intensive. What is the structure that you would use in such a case? Would you go for cluster or replication?

Then based on the choice of cluster or replication, what is the infrastructure/configuration that I am supposed to have in order to be able to expand from a single innoDB engined Database to a cluster or replication structure? I want to start correctly, I don't want to be stucked in a situation where I can not expand/improve the database system when I need to.

Thanks A lot

sr.

MySQL Cluster possibly a good fit for you because of your key:value lookup traffic pattern. NDB is traditionally poor with joins or compex queries. I would say however that it is not the same as MySQL and has caveats that you need to understand before jumping into bed with it. It's shared nothing architecture means that you should operate it at scale with decent (and inclusive redundancy) network infrastructure. It is perhaps worth a proof of concept project to fit it out and test it. The guys at severalnines have configuration tools to get you up and running.

Mongo isn't a bad fit either and as you state you cannot give up A_I, why not? What about values like (1902jdd,"/page/id") needs and auto inc? Mongo documents are schemaless and you add superfluous data to a document where you see fit and query in a key:value style too. Statistics moving forward would put you into the map reduce world which could be fun.

Keeping with traditional RDBMS and requiring performance you should look at memcached and the mysql memcached api for rapid key:value access to the data which would retain the relational aspect for aggregation queries (select [count|max|avg]... group by order by blah, blah...


Oracle: How to split records across days I have data with two timestamp columns startdate and enddate. The data is produced continuously everyday and some records are such that the startdate is one day and the enddate is the nextday as it passes midnight. For example:

Original Record:

No.   StartDate                        EndDate
1    2014-01-10 20:50:04.45           2014-01-11 01:15:25.67

Transformed Record:

No.   StartDate                        EndDate
1     2014-01-10 20:50:04.45          2014-01-10 23:59:59.99
1     2014-01-11 00:00:00.00          2014-01-11 01:15:25.67

How do I do this in Oracle?

There are several ways to do this.  One would be to use a pipelined function to return the additional row when the day spanning condition is detected.  Another way is to duplicate the rows and then eliminate the ones that don't need to be duplicated and adjust the times for those that do.  Here is an example:

SELECT
   Case When TwoRowsNeeded = 1 And RowNumber = 2 Then Trunc(EndDate)
        Else StartDate
   End StartDate,
   Case When TwoRowsNeeded = 1 AND RowNumber = 1 Then Trunc(EndDate)-1/24/60/60
        Else EndDate
   End EndDate
FROM
(
SELECT StartDate, EndDate
   , Row_Number() OVER (PARTITION BY StartDate, EndDate ORDER BY NULL) RowNumber
   , trunc(EndDate,'DD')-trunc(Startdate,'DD') TwoRowsNeeded
FROM t1
CROSS JOIN (select level from dual connect by level <=2)
)
WHERE RowNumber=1 Or TwoRowsNeeded=1;

Setup:

drop table t1;
create table t1 as (
   select trunc(sysdate) StartDate, trunc(sysdate)+2/24 EndDate from dual
   UNION ALL
   select trunc(sysdate)-1, trunc(sysdate)-1+2/24 from dual
   UNION ALL
   select trunc(sysdate)-2/24, trunc(sysdate)-3/24 from dual
   UNION ALL
   select trunc(sysdate)-2/24, trunc(sysdate)+1/24 from dual
   UNION ALL
   select trunc(sysdate)-1/24/60, trunc(sysdate)+1/24/60 from dual
   );

SELECT to_char(StartDate,'MM/DD/YYYY HH24:MI:SS') StartDate
   , to_char(EndDate,'MM/DD/YYYY HH24:MI:SS') EndDate
FROM t1;


Using COALESCE when joining multiple tables In a PG 12 database I have the reservations table with the reserved_between tsrange column.

For a specific analysis, I need to know the ending of the last reservation (later in time) and teh following query does just that.

SELECT MAX(upper(reservations.reserved_between))
FROM dev.reservations
JOIN dev.analyses ON reservations.analysis_id = analyses.id
JOIN dev.analytical_tests ON analyses.analytical_test_id = analytical_tests.id
WHERE analytical_tests.specification_id = <MYID>
AND analytical_tests.tag = <MYTAG>

Where <MYID> and <MYTAG> are the params needed to identify the analysis.

Now, in case there a no reservations, I need to return the  as_early_as (datetime) value of the batch the analysis refer to.

I think COALESCE could be a good option for the job. Thus, I have updated the quesry as follows.

SELECT COALESCE(MAX(upper(reservations.reserved_between)), batches.as_early_as)
FROM dev.reservations
JOIN dev.analyses ON reservations.analysis_id = analyses.id
JOIN dev.analytical_tests ON analyses.analytical_test_id = analytical_tests.id
JOIN dev.batches ON analyses.batch_id = batches.id
WHERE analytical_tests.specification_id = <MYID>
AND analytical_tests.tag = <MYTAG>

However, when I run I get

  ERROR:  column "batches.as_early_as" must appear in the
  GROUP BY clause or be used in an aggregate function LINE 1:
  ...ALESCE(MAX(upper(reservations.reserved_between)), batches.as...

And I'm not sure how to fix it and the documentation doesn't help me too much.

What should I change in my query?


File Stream Data Clarification If at all File stream option is enabled.

Will sp_helpdb show the database size which includes even FileStream Data.
Or only .mdf and .ldf sizes ?

No sp_helpdb wont show info about filestream data. You have to use a custom solution modified as per your needs.

Remember that sys.database_files with file type 2 = FILESTREAM shows data about filestream.


How to securely connect app and database servers? (Updated) For a start, I have my app and database on separate servers. It's easy to connect them, except I am not sure how to secure my database server.

Here's what I've already done:

Ran mysql_secure_installation right after installing MySQL. So, all of these have been taken care of:

strong 64 char root password
no anonymous users
root login only on localhost
no test databases

A non-public network for the inter-server communication (in my my.cnf, there's something like this: bind-address = 10.128.2.18, where 10.128.2.18 is the private network IP address of the MySQL database server).
A separate user for the database, an unguessable username and 64 char strong password to go with it; and the ip addresses of the user accounts set to the private IP addresses of the app server. I created the user with command like this:

GRANT ALL ON `app_db`.* TO 'db_user'@'10.128.2.9' IDENTIFIED BY 'password';

The app is wordpress, so I need GRANT ALL ON to avoid any unexpected issues.

Options considered (but not employed):

I've been told that technologies like SSH Tunnel, SSL, OpenVPN, Tinc, and IPsec are not generally used because they have a performance cost (resource usage due to encryption, latency, etc).

So, whatelse do I need, or am I good enough? How else do others do it? Please be as detailed as possible (link to a tutorial or something you are suggesting would help a lot).

I'm not sure why you've been told that VPN technologies are not generally used. It is correct that they have a performance cost but then again, so does everything! For the amount of data typically moving between an application server and its database server, the CPU overhead of TLS should be negligible, especially on the database server. If that is not the case then look at purchasing SSL-offloading hardware.

My preference is two network cards in the application server. The connection between it and the database server goes over a different network than client traffic.

If a separate network is not possible then my preference is TLS combined with configuring the firewall on the database server to only allow incoming connections from a small number of computers - the application server (obviously), administrative workstations, operations management servers, etc.


MySQL partitioning relatively large DB Table I have table structure similar to the following -
CREATE TABLE `ProductCatalog` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `SerialNumber` varchar(20) DEFAULT NULL,
  `BasePrice` decimal(10,2) DEFAULT NULL,
  `BatchCode` tinyint(3) unsigned DEFAULT NULL,
  `Type` varchar(5) DEFAULT NULL,
  `ItemCode` varchar(5) DEFAULT NULL,
  `ArrivalDate` datetime DEFAULT NULL,
  `InsertTimestamp` int(10) unsigned NOT NULL,
  `BrandID` tinyint(3) unsigned DEFAULT NULL,
  `Model` varchar(10) NOT NULL DEFAULT 'RX209',
  `Description` text,
  PRIMARY KEY (`id`),
  KEY 'idx_ic_sn_ad' (`ItemCode`, `SerialNumber`, `ArrivalDate`),
  KEY 'idx_sn_ad' (`SerialNumber`, `ArrivalDate`, `ItemCode`, `BasePrice`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8

There are about 600 Million rows in this table and is growing fastly. Everyday about 0.5 million records are inserted sometimes more sometimes less. So there is massive write activity going on  during certain period of the day (6 am to 8 pm)
Following are the queries I run against this table
SELECT *
FROM ProductCatalog
WHERE SerialNumber='1234567890'
  AND ItemCode!="ABCD"
ORDER BY id DESC LIMIT 1;

SELECT BasePrice
FROM ProductCatalog
WHERE SerialNumber='123456789'
  AND ItemCode!="ABCD"
  and ItemCode!="PQRS"
  AND ItemCode!="MNOP"
ORDER BY ID Desc LIMIT 1

SELECT *
FROM ProductCatalog
WHERE SerialNumber='123456789' AND ItemCode='ABCD'
  AND (ArrivalDate>='2019-01-01 00:00:00' AND ArrivalDate<='2020-12-31 23:59:59')
ORDER BY ArrivalDate ASC

SELECT BatchCode
FROM ProductCatalog
WHERE SerialNumber='123456789'
  AND ItemCode!="ABCD"
  and ItemCode!="PQRS"
  AND ItemCode!="MNOP"
ORDER BY ID Desc LIMIT 1

Above queries and table indexes are optimized as suggested in my previous question here
MySQL table proper indexes for performance optimization
PROBLEM
Since ProductCatalog Table is getting bigger, and third query mentioned above is frequently used, I am thinking of partitioning this table. For this I am thinking of range partitioning using ArrivalDate column. Following is the query I came up with.
ALTER TABLE ProductCatalog PARTITION BY RANGE (TO_DAYS(ArrivalDate)) (
PARTITION p11 VALUES LESS THAN (TO_DAYS('2011-01-01')),
PARTITION p12 VALUES LESS THAN (TO_DAYS('2012-01-01')),
PARTITION p13 VALUES LESS THAN (TO_DAYS('2013-01-01')),
PARTITION p14 VALUES LESS THAN (TO_DAYS('2014-01-01')),
PARTITION p15 VALUES LESS THAN (TO_DAYS('2015-01-01')),
PARTITION p16 VALUES LESS THAN (TO_DAYS('2016-01-01')),
PARTITION p17 VALUES LESS THAN (TO_DAYS('2017-01-01')),
PARTITION p18 VALUES LESS THAN (TO_DAYS('2018-01-01')),
PARTITION p19 VALUES LESS THAN (TO_DAYS('2019-01-01')),
PARTITION p20 VALUES LESS THAN (TO_DAYS('2020-01-01')),
PARTITION p21 VALUES LESS THAN (TO_DAYS('2021-01-01')),
PARTITION p22 VALUES LESS THAN MAXVALUE);

Later every year I will re-organize the partition like this
ALTER TABLE ProductCatalog
REORGANIZE PARTITION p22 INTO (
    PARTITION p22 VALUES LESS THAN (TO_DAYS('2022-01-01')),
    PARTITION p23 VALUES LESS THAN (TO_DAYS('2023-01-01')),
    PARTITION p24 VALUES LESS THAN MAXVALUE
);

QUESTIONS
Every year about 200 Million rows in average are inserted. So taking this into consideration -

Do this partitioning do any good in terms of performance with my existing table structures and indexes.
Do this partitioning do any good for selects statements I have specified above?
Do this partitioning do any good for lots of inserts going every moment?
I know we can delete particular partition, but is it easily possible to archive particular partition  to another archive database before deleting?
Is there any other better idea, to get maximum possible read and write performance in the database?
Do I need to worry about column id int(10) when data is increasing rapidly?
What this partitioning will impact on other three queries where no ArrivalDate is used in WHERE condition?

So going down your list of questions:

Do this partitioning do any good in terms of performance with my
existing table structures and indexes.

No!  If you always query on date, maybe.

Do this partitioning do any good for selects statements I have
specified above?

Only your third query, and that's a maybe.

What this partitioning will impact on other three queries where no
ArrivalDate is used in WHERE condition?

It depends on if the secondary indexes are also partitioned.  But generally there can be more overhead as you have to query each partition that possibly contains rows.  Partitioning also breaks a lot of features in MySQL so it shouldn't be undertaken lightly.

Do this partitioning do any good for lots of inserts going every
moment?

No.  You're still appending according to the meaningless row identifier.  Everything will be stuck on the last page.

I know we can delete particular partition, but is it easily possible
to archive particular partition to another archive database before
deleting?

I'm not sure about MySQL - moving/deleting partitions is relatively simple in other databases.

Do I need to worry about column id int(10) when data is increasing
rapidly?

Yes!  You either fix the problem now or have to rebuild the table with a larger, equally meaningless row identifier and further your woes.

Is there any other better idea, to get maximum possible read and write
performance in the database?

Yes.  It's called define the primary key, and cluster on that.
Right now you have a big unorganized mess of rows.  I'd call it a heap, but people reserve that for a certain type of unorganized mess of rows that hides the row pointer from the user instead of declaring it a primary key.
Your rows are located in the table according to their approximate insert order.  To locate these rows more efficiently you slap an index on top so you can find where they're hidden, but even with the index you could be reading one page per row, or the entire table, depending on how the data is distributed.  See my answer here for a very basic illustration: Save performance with large update on Index with Included Column
So it's not entirely obvious from your question if (SerialNumber, ArrivalDate, ItemCode) is unique, but let's assume it is (we can adjust later if an additional column is necessary to define uniqueness).  If we define your table like so:
CREATE TABLE `ProductCatalog` (
  `SerialNumber`    varchar(20)  DEFAULT NULL,
  `ItemCode`        varchar(5) NOT NULL,
  `ArrivalDate`     datetime NOT NULL,
  `BasePrice`       decimal(10,2) DEFAULT NULL,
  `BatchCode`       tinyint(3) unsigned DEFAULT NULL,
  `Type`            varchar(5) DEFAULT NULL,
  `InsertTimestamp` int(10) unsigned NOT NULL,
  `BrandID`         tinyint(3) unsigned DEFAULT NULL,
  `Model`           varchar(10) NOT NULL DEFAULT 'RX209',
  `Description`     text,
  CONSTRAINT PK_ProductCatalog PRIMARY KEY (SerialNumber, ItemCode, ArrivalDate)
)

Three things happen:

We save space because we're not slapping an unnecessary row pointer on every row
We are guaranteeing uniqueness
Rows are now grouped (for the most part) according to (SerialNumber, ItemCode, ArrivalDate).

The consequences of #3 is that all of the queries you submitted will traverse one b-tree and at usually no more than 1-2 pages to satisfy the query.
Yes, this can cause page splits (fragmentation), but the impact of page splits is generally less than the cost of additional indexes (which also fragment) and more read I/O.  If it gets really bad (which you'll monitor just like anything else), you can rebuild the table (clustered index).
Other general observations:

Don't store timestamps as anything other than DATETIME/TIMESTAMP.  The data type exists for a reason, if you have to convert on insert, that's better than having to convert on read.
If one or more of Brand, Model, or Description is dependent on the ItemCode, then that data should be stored in its own table.  Makes your main table even more compact.
If SerialNumber and ItemCode are fixed length, you're probably better off using CHAR(<the max length>) versus VARCHAR.


partitioning by datetime vs date vs int performance I am trying to determine which partitioning strategy will be most efficient for my queries on SQL Server.

We know we want daily partitions. The data has a natural DATETIME field in it, so my gut instinct is to partition on that value. However, we have discussed the possibility of adding a DATE column (with just the time information stripped out), or possibly coding the date as an integer (YYYYMMDD, e.g., 20130930) and using that as the partitioning column.

All queries on this data will be on a specific date (WHERE ItemTime = '2013-09-30') or range of dates (WHERE ItemTime BETWEEN '2013-09-15' AND '2013-09-30'). For now, we always query on a date, but future requirements may include time details (WHERE ItemTime BETWEEN '2013-09-29 20:30:00' AND '2013-09-30 10:15:45').

I have tested performance of each strategy on a few hundred thousand rows of data, and seen no real difference. The production deployment, however, will be in the hundreds of millions up to maybe a couple billion rows.

Is one of these strategies going to lead to more efficient queries than the others? Why or why not?

Thanks for your help.

[EDIT] The queries will be formatted by application code, so I'm not concerned about how to translate between DATETIME, DATE, and INT. I can assume the query will be properly formatted based on whichever partitioning scheme is chosen. I'm just trying to find out if partition elimination will be faster using one of these data types.

You shouldn't get any additional performance gain by adding the extra column. If any, I doubt it will outweigh the additional storage cost required for the extra column.

There is one perk that you'll get from having a date only column and that is that you can have a date dimension. If this is for a data warehouse, I'd definitely recommend including.

If you decide to add the additional column (and assuming you're on SQL 2008 +), use the Date datatype. Int (formatted in YYYYMMDD) used to be the recommended format for partitioning, as it was cheaper (@ 4 bytes/row) than datetime (@ 8 bytes/row). Date is 3 bytes/row and is in a natural date format. Also, INT is a pain in the butt to query on as you have to add conversions in your' search arguments:

WHERE DateID = (CONVERT([int],CONVERT([char](8),getdate(),(112)),(0)))


ERROR! MySQL is running but PID file could not be found - MariaDB 10.1 - CentOS 7 - Fresh VM So I'm having a little trouble with a BRAND new VM I just created today to test a Galera Cluster.

What I have done so far is...

Download and install the latest CentOS 7 in a VM.
Install open-vm-tools and configure SSH
Install MariaDB Repo and install MariaDB 10.1 via

    cat << 'EOL' >/etc/yum.repos.d/MariaDB.repo
    # MariaDB 10.1 CentOS repository list - created 2016-12-21 16:21 UTC
    # http://downloads.mariadb.org/mariadb/repositories/
    [mariadb]
    name = MariaDB
    baseurl = http://yum.mariadb.org/10.1/centos7-amd64
    gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
    gpgcheck=1
    EOL
    yum install MariaDB-server MariaDB-client

Now I get this error, but I am able to get to the MariaDB CMD Prompt which says its running, but I can't have this error on a brand new setup.

[root@localhost ~]# mysql -uroot
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 10.1.20-MariaDB MariaDB Server

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> exit;
Bye
[root@localhost ~]# /etc/init.d/mysql status
ERROR! MySQL is running but PID file could not be found

Seems the issue was how it is started.

Using the following, all is well!!

[root@sql ~]# systemctl start mariadb.service
[root@sql ~]# systemctl enable mariadb.service
[root@sql ~]# /etc/init.d/mysql status
SUCCESS! MySQL running (920)

So don't use /etc/init.d/mysql start as stated in the install guide...

https://mariadb.com/kb/en/mariadb/yum/


Copy of a database with an existing database with schema and data in sql server I have two databases one is having master kind of tables and other will have transaction data.
I should create a new database with a structure of an existing database to my clients. Here is the scenario..

If the particular db will comes under transaction type then structure of an existing db should copy
If the particular db will comes under master type then structure of an existing db along with data should copy

i.e Transaction--> Only Schema
       Master--> Schema+Data

I tried with Generate script, Is there any way to automate this process instead of manually generating script for both schema and Schema+Data for every copy of a new db.

Please suggest.

Create two template databases - once - one with the schema, and one with schema + data - populated with the manual generate scripts method.

Take a backup of each.

When you want to create a new database, instead of a manual CREATE DATABASE and then manual population, restore the backups as new names and the WITH MOVE options to move the data/log files.

Refresh the templates, obviously, every time you make meaningful changes to the schema and/or data that you want used for future, new databases.

You could reduce this work by putting just the schema in model, then CREATE DATABASE would create an empty database with your schema, and you'd only need to do something for the one with the data. Or vice-versa. But note that this will affect all future databases.


Postgres Search Path between connections I'm a noob to Postgres, and am setting up a multi-tenant database using one schema per tenant.

To target each tenant I need to set search_path accordingly.

I'm worried about accidentally executing something intended for one tenant on another tenant, if the wrong search path is picked up somehow.

I will be executing Functions from .Net code (via NpgSql). The functions will reside in the public schema. Each function will have the search path set inside it, like so:

BEGIN
 PERFORM set_config('search_path', tenant, true);
...
END

Is there any danger, via concurrency or connection pooling, that the search paths for two calls might be shared, for instance if the 'true' parameter was forgotten or set to false.

I really want to be sure that there's no danger of data from one tenant crossing over in to another.

Thanks in advance.

You should clean the state when you return a connection to the pool.

This is typically done with DISCARD ALL.

If all you need is to reset configuration parameters, you should use RESET ALL.


SELECT multiple times based on changed dates I'm trying to select different types of data depending on a payment method which is a simple intersection table with columns "id" (INT PK) , "customer_id" (INT FK), "payment_method_id" (INT FK) and "date_changed" (DateTime)

Now, the customer may request the admin to change the payment method at any time, however I need to keep track of the payment method even if it's no longer in use simply because if I change the payment method halfway through the month I'll get invoiced the first half of the month using payment method 1 and the second half using payment method 2.

Now, depending on payment method, I will need to run different queries (using PHP in the backend) however, I need to find out, what payment method to use for a certain period of time.

How could I get to know if there is a payment method more recent, and more outdated, oh and I obviously cannot select the payment methods by month since they may be left unchanged for years on end before seeing a change, and since this is about invoicing my data needs to be trackable for 5 years...

How could I go about selecting the different data based on the methods I get back and their "date_changed" ?

Thanks!

Does using a sub query on the same table, something like this, work for you?

select * from yourtable where storedatetime = (
select max(storedatetime) from yourtable )


Index deletion and Backup size If I delete some huge non-clustered indexes will the backup size of that database also decrease?

I am facing issue with huge backup files and don't have enough disk space to hold those backups.

I have some unused indexes of size 40 GB and above; if I delete this index will the backup file size reduce?

If you delete 40GB of indexes, then certain backups will be smaller, such as:

Full
File
Filegroup

Some backups won't generally be smaller, such as:

Log

Differential backups (Full, File, Filegroup) may or may not be much smaller as it depends if any objects those non-clustered indexes are created on are actually having data modifications happening.

Some backup sizes might not go down by 40GB, if for example compression is already being used or those indexes are on a read_only filegroup that isn't generally backed up via filegroup backups.

However, overall, the answer is "Yes, it should be smaller".


Should I normalise this database? I have two database designs thought of for storing messages(instant messages) between users. It will be a group chat system.

I have two table designs in mind.

One is this:

CREATE TABLE `mssg_grp_user` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned NOT NULL,
  `grp_id` int(10) unsigned NOT NULL,
  `body` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`created_at`,`grp_id`,`user_id`,`id`),
  KEY `mssg_grp_user_user_id_foreign` (`user_id`),
  KEY `mssg_grp_user_grp_id_foreign` (`grp_id`),
  KEY `mssg_id_is` (`id`),
  CONSTRAINT `mssg_grp_user_grp_id_foreign` FOREIGN KEY (`grp_id`) REFERENCES `groups` (`id`) ON UPDATE CASCADE,
  CONSTRAINT `mssg_grp_user_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=350201 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

And another is this:

in the second one:
the Messages table has the following keys

alter table messages
drop primary key ,
add primary key (created_at, id),
add INDEX mssg_id_ix (id)    ;

And there is a primary key of (grp_id,mssg_id) in the mssg_grps table.

My main queries are to retrieve the newest 50 messages that are in a list of group IDs AND are newer than a certain time. For the 2nd one, I ran this query :

SELECT * FROM test.mssg_grp a LEFT JOIN test.messages b ON a.mssg_id = b.id
Where b.created_at >"1970-01-03 19:08:21"
AND a.grp_id IN(8,2,1,2,5,6,3)
order by created_at limit 50;

This took a long time. It took 0.1 seconds for one query (there are 200k messages) and around 2 minutes for 100 concurrent users.

Then I decided to merge the tables(i.e. change my db schema) and switched to the first one.

To get the same data, i ran this query:

Select sql_no_cache * from test.mssg_grp_user where  grp_id IN (8,1,5,6,7,8,9) and created_at>888880
order by  created_at desc limit 50;

This took 0.1 seconds for 100 concurrent users. This is obviously much faster. My questions are:

Why is the join+order taking so much time?
Should I switch to the first design.(the body field may have up to 500 characters)

The first solution is clearly more efficient than the second one, because you don't need to do the join (every join slows down a query, more or less depending on the access plan generated by the system).

More, I think the second solution has some problem, since from the schema it seems that you are using the column id inside mssg_grp as foreign key referencing the table mssg, while in that table you use as primary key the couple of columns created_at and id, which conflicts with the definition of the foreign key, from which one could assume that id is already a primary key for mssg.

So, if, as it seems from the diagram, the relation between mssg and mssg_grp is 1:1, you should opt for the first solution which is still normalized and more efficient.


How can I write a query to recursively execute with varying parameter values? Given the following query:

DECLARE @14DayWindow Datetime = DATEADD(day, -14, GETDATE())
DECLARE @90DayWindow Datetime = DATEADD(day, -90, GETDATE())

SELECT    *
FROM      (SELECT   ID, COUNT(ID)
           FROM     SomeTable
           WHERE    SomeDate > @14DayWindow
           GROUP BY ID) as FourteenDayWindow
LEFT JOIN (SELECT   ID, COUNT(ID)
           FROM     SomeTable
           WHERE    SomeDate > @90DayWindow
           GROUP BY ID) as NinetyDayWindow
ON        FourteenDayWindow.ID = NinetyDayWindow.ID

Is there a way to re-write this query such that the SELECT subquery doesn't have to be written out for every window of time?  Ideally I would be able to supply an arbitrary number of parameters with each result being joined in sequence.  Possible?

EDIT: Output should be something like

ID | FourteenDayIDCount | NinetyDayIDCount

You can use an INLINE USER DEFINED FUNCTION for this purpose.

Have a look at Inline UDF rules:

The RETURNS clause contains only the keyword table. You do not have to define the format of a return variable, because it is set by the format of the result set of the SELECT statement in the RETURN clause.
There is no function_body delimited by BEGIN and END.
The RETURN clause contains a single SELECT statement in parentheses. The result set of the SELECT statement forms the table returned by the function. The SELECT statement used in an inline function is subject to the same restrictions as SELECT statements used in views.
The table-valued function accepts only constants or @local_variable arguments

CREATE TABLE SomeTable(ID int, SomeDate datetime);

INSERT INTO SomeTable VALUES
(1, '20170301'),(1, '20170305'),(1, '20170310'),(1, '20170315'),(1, '20170320'),(1, '20170325'),(1, '20170329'),
(1, '20170401'),(1, '20170404'),(1, '20170412'),(1, '20170418'),(1, '20170422'),(1, '20170424'),(1, '20170430'),
(2, '20170303'),(2, '20170308'),(2, '20170312'),(2, '20170319'),(2, '20170323'),(2, '20170325'),(2, '20170330'),
(2, '20170405'),(2, '20170406'),(2, '20170412'),(2, '20170418'),(2, '20170422'),(2, '20170424'),(2, '20170430');
GO

CREATE FUNCTION dbo.fnCount(@MinDate datetime)
RETURNS TABLE
AS
RETURN
    SELECT   ID, COUNT(ID) NumID
    FROM     SomeTable
    WHERE    SomeDate > @MinDate
    GROUP BY ID
GO

DECLARE @XDays Datetime = '20170401';
DECLARE @YDays Datetime = '20170308';

SELECT t1.ID, t1.NumID as [X-Days], t2.NumID as [Y-Days]
FROM   dbo.fnCount(@XDays) t1
JOIN   dbo.fnCount(@YDays) t2
ON     t1.ID = t2.ID
GO

ID | X-Days | Y-Days
-: | -----: | -----:
 1 |      6 |     12
 2 |      7 |     12

dbfiddle here


Database design for one key with multiple values In my application, users are able to create lists. Each list contains infinite records. Each record contains one key and this key, may contain zero or infinite values.

To clarify consider this:

List title: "Greeting in some languages"

List records: (Below table is a simple UI design (HTML form) that user has filled)

| Hello         | Hallo        | Ciao        |
| Bye           | Tschüss      | arrivederci | ... | ... | ... |
| Good morning  | Guten Morgen | Buongiorno  |
| Good night    | Gute Nacht   | Buonanotte  | ... |
|[Another key]  | [Value 1]    | [Value 2]   |

First column always contains keys. Next columns are values for the first column.

The approach that came to my mind is:

Use a NoSQL database (e.g., MongoDB).
Create a table called lists which contains two columns: id title (in this example "Greeting in some languages").
Create a table called lists_contents which contains three columns: list_id, key, values

In the lists_table I would like to store all values which belong to the key, in the values column as sub-record.

So this is a record (in above list) which is going to insert:

{
    list_id: 123,
    key: "Hello",
    values: ["Hallo","Ciao","Salam","..."]
}

Another approach is to create an independent table for values column.

What is your idea?
Is  using a NoSQL database a good tool or a relational database? Are my approaches suitable?

Instead of the dynamic number of columns (1 for each new language), I expect it would easier to manage this in the form of two related tables: 1. with your key and potentially one or more properties (to which new rows can be added for each new word) and 2. a table with translations for each key in table 1.

I have recently reviewed some strategies on this and found the below linked answer to be most useful, which expands on what I note above.

The answer provided a sample table:

    CREATE TABLE T_PRODUCT (pr_id int, PRICE NUMBER(18, 2))
    CREATE TABLE T_PRODUCT_tr (pr_id INT FK, languagecode varchar, pr_name text, pr_descr text)

https://stackoverflow.com/a/323365/10340388


Mysql to Mongodb data migration application for use in production environment I have a mysql database that receives data every 10 seconds and its a lot of data i receive.I am looking for an application that can transfer my database from mysql to mongodb. I am looking for a proven solution that works in production environment.

depends from what Your need, it could be replication, such as:

https://pubs.vmware.com/continuent/tungsten-replicator-3.0/deployment-mongodb.html
http://www.symmetricds.org  -

or ETL Tools, like:

Telend
Pentaho
Informatica


How can I get the last inserted id from an specific table from a given stored procedure? I know about the existence of LAST_INSERT_ID() function... Though I am not quite sure about its behaviour... Does it take the last inserted id from the current user into account? Or uses all the sessions as reference? If the later holds true I am afraid another query by another user modifies such value before using such data. Are there any alternative ways to do this without recurring MAX()?

From here: (My emphasis below)

  The ID that was generated is maintained in the server on a
  per-connection basis. This means that the value returned by the
  function to a given client is the first AUTO_INCREMENT value generated
  for most recent statement affecting an AUTO_INCREMENT column by that
  client.

  This value cannot be affected by other clients, even if they
  generate AUTO_INCREMENT values of their own. This behavior ensures
  that each client can retrieve its own ID without concern for the
  activity of other clients, and without the need for locks or
  transactions.

A fundamental point about LAST_INSERT_ID() is that bit about per_connection - if it wasn't on that basis, then the function would be useless in a multi-user environment.

Unless there are further complications, you have absolutely no need to resort to the MAX() function - and you have no guarantee anyway that (in a multi-user scenario), your MAX(xxx) value won't be incremented by another user in the instant after you call it.


I'm not understanding the concept of login/user on Azure So as a SQL Server DBA, I can normally create a user, then inside the database U just create a login pointing to that login and then I give the necessary privileges.

I'm trying to understand the concept of logins and users on Azure. Official documentation has some confusing concepts about then and I think I just need to understand the basics to go on.

So, To create a user for a database, do I need to have a Login as we have in a SQL Server instance? I can only find information about AD users.

The original problem I'm having here is that, there's a user inside the database and I just can't change it's Password ( Dev people lost the pass ).

When I use (Inside the database I want to change its passowrd):

ALTER LOGIN TheUser WITH PASSWORD = 'SomePassword'

SQL Server Management Studio says:

  Msg 5001, Level 16, State 3, Line 139 User must be in the master
  database.

But there's no user in the master database. how was this user created here without beign created first on master? Do I need a user inside master and then transfer it to the database I want it to access?

This is not helping me

EDIT:

So I could connect with a different login ( for some reason im not sysadmin I guess) and I could change the user  password with the command above, but I still would like som esimple explanation about how logins and users works on azure.

Simple explanation :

A login is an access to a server. It does not necessarily mean access to a database.

When you create a user from a login in a database and grant access (explicit or using a role), the login is able to access a given database/s.

What you are doing is incorrect since you are inside a database and then trying to change the password for a login.

YOu should be in master database to change a login's password - provided you have rights to do it.


Restore PostgreSQL data using PITR I am unable to restore the database to a previous time using the PITR steps mentioned on PostgreSQL website Article 23.3.3. Recovering with an On-line Backup (http://www.postgresql.org/docs/8.1/static/backup-online.html#RECOVERY-TARGET-XID)

The following are the steps I followed:

I made the below changes to postgresql.conf file which is located in the Data Folder:

wal_level = archive
archive_mode = on
archive_command = 'copy "%p" "C:\\archivedir\\%f"'

I created a folder named 'archivedir' on C Drive
I made the below changes to recovery.conf file:

recovery_target_time = '2014-02-05 11:45:00 CST'
primary_conninfo = 'host=localhost port=5433 user=owner password=xxxxx'
restore_command = 'copy "C:\\archivedir\\%f" "%p"'

Stopped the service of postgresql and Deleted Folder contents of pg_xlog
Re-started the service
Recovery.conf is changed to recovery.done
But I am unable to restore the database to a previous state.

I have checked multiple times by dropping tables and trying to restore the database to an earlier state. Is there anything that I am missing?

First, never, ever, ever delete anything from pg_xlog, ever. You will corrupt your database. Now, on to what you've misunderstood about all this:

  I have checked multiple times by dropping tables and trying to restore the database to an earlier state. Is there anything that I am missing?

PITR doesn't work like that. It doesn't revert a running database to an older point in time.

How it works is that you set it up first, before you need to restore:

Set up WAL archiving, so you record WAL to a safe location.
Before you need to revert, you make a base backup. You can use pg_start_backup() and rsync for this, but it's easiest and safest to use pg_basebackup(). See the documentation for details on creating base backups. You must do this after setting up WAL archiving, so the archive contains all WAL from the moment the basebackup is taken onward.
Continue to run with WAL archiving, periodically taking a new base backup to reduce the amount of old WAL you have to keep and the amount of recovery time you need. Automated tools like pgbarman help with this.

When you need to create a historical copy of the database state to recover from something, you:

Make a copy of the base backup to writeable storage
Set the copy up with a recovery.conf file that specifies an appropriate restore_command for fetching WAL and the recovery_target_time you want to replay up to.  Setting primary_conninfo makes no sense, since you won't be using this as a warm or hot standby streaming replica.
Start the copy, and allow it to replay WAL until it reports that recovery is complete.
Connect to the copy, and do whatever you need to grab the data you wish to recover out of it. You may use pg_dump to extract a database or parts of it, use COPY to extract contents of tables, etc.

If you wish to completely revert a database you can delete the datadir, replace it with the copy, and follow the procedure above to replay the copy up to a point in time.

What you cannot do is take an existing database and roll it back to some point in the past. WAL replay only goes forward in time. So you must restore an old base backup then allow it to replay WAL up to the point in time you want.


Migrated a database to mySQL 8.0.17, unable to upgrade or start service I recently setup a new host with mySQL ver 8.0.17 and moved an old mySQL database (ver 5.1.73) to it using mysqldump -u root -p --all-databases > somefile.sql and then importing it with mysql -u root -p < /somefile.sql . Right now, I am unable to start mysqld and am unable to run an update to fix the tables.

I have attempted to run a mysql upgrade as mentioned in the log, but it doesn't seem to help, or im doing it wrong. Initially I ran mysql_upgrade but that feature is deprecated so instead I ran mysqld --upgrade=AUTO and still am having issues with the service starting up. What am I doing wrong?

Here is the output from mysqld.log:

2020-02-07T01:09:23.054386Z 0 [Warning] [MY-011070] [Server] 'Disabling symbolic links using --skip-symbolic-links (or equivalent) is the default. Consider not using this option as it' is deprecated and will be removed in a future release.
2020-02-07T01:09:23.056223Z 0 [System] [MY-010116] [Server] /usr/libexec/mysqld (mysqld 8.0.17) starting as process 1533
2020-02-07T01:09:24.005561Z 0 [Warning] [MY-010727] [Server] System table 'plugin' is expected to be transactional.
2020-02-07T01:09:24.188459Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2020-02-07T01:09:24.206417Z 0 [Warning] [MY-010929] [Server] Storage engine 'MyISAM' does not support system tables. [mysql.user].
2020-02-07T01:09:24.206431Z 0 [Warning] [MY-010929] [Server] Storage engine 'MyISAM' does not support system tables. [mysql.db].
2020-02-07T01:09:24.206442Z 0 [Warning] [MY-010929] [Server] Storage engine 'MyISAM' does not support system tables. [mysql.tables_priv].
2020-02-07T01:09:24.206466Z 0 [Warning] [MY-010929] [Server] Storage engine 'MyISAM' does not support system tables. [mysql.columns_priv].
2020-02-07T01:09:24.206486Z 0 [Warning] [MY-010929] [Server] Storage engine 'MyISAM' does not support system tables. [mysql.procs_priv].
2020-02-07T01:09:24.206719Z 0 [Warning] [MY-013143] [Server] Column count of mysql.user is wrong. Expected 51, found 39. The table is probably corrupted
2020-02-07T01:09:24.206740Z 0 [Warning] [MY-013139] [Server] Cannot load from mysql.db. The table is probably corrupted!
2020-02-07T01:09:24.206762Z 0 [Warning] [MY-013139] [Server] Cannot load from mysql.db. The table is probably corrupted!
2020-02-07T01:09:24.206799Z 0 [Warning] [MY-013139] [Server] Cannot load from mysql.tables_priv. The table is probably corrupted!
2020-02-07T01:09:24.206818Z 0 [Warning] [MY-013139] [Server] Cannot load from mysql.tables_priv. The table is probably corrupted!
2020-02-07T01:09:24.206834Z 0 [Warning] [MY-013139] [Server] Cannot load from mysql.tables_priv. The table is probably corrupted!
2020-02-07T01:09:24.206853Z 0 [Warning] [MY-013139] [Server] Cannot load from mysql.columns_priv. The table is probably corrupted!
2020-02-07T01:09:24.206876Z 0 [Warning] [MY-013139] [Server] Cannot load from mysql.columns_priv. The table is probably corrupted!
2020-02-07T01:09:24.206892Z 0 [Warning] [MY-013139] [Server] Cannot load from mysql.procs_priv. The table is probably corrupted!
2020-02-07T01:09:24.206912Z 0 [Warning] [MY-013139] [Server] Cannot load from mysql.procs_priv. The table is probably corrupted!
2020-02-07T01:09:24.206942Z 0 [Warning] [MY-013139] [Server] Cannot load from mysql.procs_priv. The table is probably corrupted!
2020-02-07T01:09:24.207159Z 0 [ERROR] [MY-010316] [Server] Fatal error: Could not read the column 'authentication_string' from table 'mysql.user'. Please perform the MySQL upgrade procedure.
2020-02-07T01:09:24.207286Z 0 [ERROR] [MY-010952] [Server] The privilege system failed to initialize correctly. For complete instructions on how to upgrade MySQL to a new version please see the 'Upgrading MySQL' section from the MySQL manual.
2020-02-07T01:09:24.208023Z 0 [ERROR] [MY-010119] [Server] Aborting
2020-02-07T01:09:25.468177Z 0 [System] [MY-010910] [Server] /usr/libexec/mysqld: Shutdown complete (mysqld 8.0.17)  Source distribution.

First make sure you are pointed to the right directory in you configuration/settings files - /usr/share/mysql[-8.0]
Then use command
mysqld --upgrade=FORCE

This worked for me.


Login to SQL Server from command line I am trying to access a SQL Server 2014 database from cmd. I am using the literal command below where the only thing I am changing is the servername:

sqlcmd [ /E ] [ /S servername ]

However I get an error:

  Sqlcmd: '[': Unknown Option. Enter '-?' for help.

Any idea what I am doing incorrectly?

The correct syntax for Connecting to a default instance by using Windows Authentication to interactively run Transact-SQL statements

sqlcmd -S <ComputerName>

Connecting to a named instance by using Windows Authentication to interactively run Transact-SQL statements:

sqlcmd -S <ComputerName>\<InstanceName>

Connecting to a named instance by using Windows Authentication and specifying input and output files:

sqlcmd -S <ComputerName>\<InstanceName> -i <MyScript.sql> -o <MyOutput.rpt>

Connecting to the default instance on the local computer by using Windows Authentication, executing a query, and having sqlcmd remain running after the query has finished running:

sqlcmd -q "SELECT * FROM AdventureWorks2012.Person.Person"


MySQL SELECT n-th row between dates (time interval) I have a server which records various sensor data each second and inserts it into a MySQL table.
To view the sensor data on a dashboard I have a chart which also gets updated with live data each second. The database is growing at a rate of more than 250,000rows / 24h (3 sensors+).

When loading the dashboard I want to display history data. At least 24h, more would be better (72+ ideal); my testings are all done with 24h.

The amount of data is huge (86400 rows), so I though of selecting only a subset of data for the history, which can be refined on zoom. I chose an interval of 5 minutes, or 300 seconds:

SELECT * FROM `data` WHERE `sensorId` = 0
 AND `unixDate` >= (1458829800 - 86400)
 AND `unixDate` < 1458829800
 AND NOT `unixDate` % 300 -- left out for all data
ORDER BY `id` DESC

This query is of course amazingly slow, on the damn small machine I'm running this on (a raspberry pi) it takes more than 10 seconds!

Querying all the 86400 rows takes roughly below one second.

I might be able to install a separate database server on a heavy machine as well, but I'd like to get this project done before my server environment is up and running, also I don't want to send so much data around my network each second...

I wonder two things: is there a quicker way of querying data by time intervals and might there be a completely better solution (time series db, which seemed a bit overkill to me) for this kind of work?

You should create a summary table that will store this summary data.

5 minute summaries will require additional 288 rows per day (24 * 60 / 5); 30 minute summaries - 48 rows per day (24 * 60 / 30); hourly summaries - 24 rows per day. In total, you're looking at storing additional 360 rows per day (288 + 48 + 60), which is a tiny overhead (360 / 86400 ~= 0.4167%) for a massive performance boost.

Try something like this:

CREATE TABLE data_summary (
    granularity_mins INT NOT NULL,
    unixDate BIGINT NOT NULL,
    sensorId INT NOT NULL,
    sensor_min_value DECIMAL NOT NULL,
    sensor_max_value DECIMAL NOT NULL,
    sensor_ave_value DECIMAL NOT NULL,
    sensor_readings_count INT NOT NULL,
    PRIMARY KEY(granularity_mins, sensorId, unixDate)
)

You can then get your 5 minute summary with a SELECT like this:

SELECT *
FROM data_summary
WHERE granularity_mins = 5
    AND unixDate > 1458829800 - 86400
    AND unixDate < 1458829800
    AND sensorId = 0


How to login to a remote SQL Server instance via IP and specify the Server name? I'm following instructions HERE to add a remote publisher to a Distributor server here. However, when trying to add a publisher via SQL Server Authentication I get the following error:

THIS recommends checking to see the machine name and servername is the same:
SELECT SERVERPROPERTY(N'servername')
SELECT @@SERVERNAME

They are.
And there is a stack overflow question (https://stackoverflow.com/questions/8212032/sql-server-replication-requires-the-actual-server-name-to-make-a-connection-to-t) that recommends adding an additional connection parameter SERVER='XXX'. But this also doesn't work. I get the following error screen:

Both named pipes and TCP connections are enabled on both machines. And I'm able to login via the connection manager when not trying to configure publications.
So I think my question is, how can I specify a servername using SQL Server authentication as if I were logging into the server via a Windows login?

The solution that worked was to create an alias of (server name => IP address of the publisher) on the local machine on the configuration manager.

Though I suspect that this could also have been added to the windows host file on the local server

On configuration manager, I added the following:

Name: <@@SERVERNAME>
Server: IP address
Protocol: TCP (not sure why this isn't named pipes)
port: 1433


SQL Server 2012 : how to add missing collation When we run:

ALTER DATABASE abc COLLATE Latin1_General_CP1_CI_AS ;
GO

we receive:

  Msg 448, Level 16, State 3, Line 1
  Invalid collation 'Latin1_General_CP1_CI_AS'.

How can we add the missing collation?

There never has been a "Latin1_General_CP1_CI_AS": it is "Latin1_General_CI_AS" or "Latin1_General_100_CI_AS"

You may be confusing it with "SQL_Latin1_General_CP1_CI_AS" which is legacy, basically.

Saying that, the collation offered at setup varies based on your regional settings. See SQL Server 2005 collation issue during installation for more (I've no idea why US English locale offers a "legacy" SQL collation)

SELECT * FROM ::fn_helpcollations() FH WHERE name LIKE '%Latin%_CI_AS'


Is it possible to reference multiple foreign keys with one primary key? Really hope this is the right place to ask this, but I just want to make sure this is modelled correctly.

I'm trying to create a relationship between store ownership (i.e., stores that bought other stores, or stores that are owned by other stores).

I have 3 tables:
Stores
parentStores
childStores

Essentially a store can have multiple children stores, but only one parent store. I just don't know the best way and type of relationship to link them--currently I have:

stores has a 1-to-Many relationship with parentStores (on storeID which is PK in stores and FK in parentStores)

stores has a 1-to-1 relationship with childStores (on storeID which is PK in stores and FK in childStores)

Is this the right way to go about it?

Sorry if it's an obvious question; new to 'backend' work.

This is a typical 1-to-n relationship: one parent store is related to several child stores.

You don't need separate junction tables to model such a relationship, and it is best to avoid unnecessary tables.

You'd add a foreign key column parent_store_id to the store table that points to the parent store. If there is no parent store, the column contains NULL.


Listener Port 1433 for Always on Availability Group In our Availability group I have a reporting server where report writers will connect directly and also crystal reports will be used, If the listener port is 1433 and the clients also gets connected through port 1433 to the reporting server that is part of the Availability group will there be any port conflict.

If the listener port is 1433 and the clients also gets connected through port 1433 to the reporting server that is part of the Availability group will there be any port conflict.

If 1433 is just dedicated to sql server and no other service is using it, there wont be any conflicts.
This is documented well in BOL :

If you use the default port of 1433 for availability group listener VNNs, you will still need to ensure that no other services on the cluster node are using this port; otherwise this would cause a port conflict.
If one of the instances of SQL Server is already listening on TCP port 1433 via the instance listener and there are no other services (including additional instances of SQL Server) on the computer listening on port 1433, this will not cause a port conflict with the availability group listener. This is because the availability group listener can share the same TCP port inside the same service process. However multiple instances of SQL Server (side-by-side)should not be configured to listen on the same port.


Protect SQL Server Express data file I need to protect a Microsoft SQL Server 2008 data file (.mdf) against restoring/attaching on another machine. I'm aware of TDE or Transparent Data Encryption but unfortunately this option is not available in Express version. Any other way to protect the database?

Thanks

You cannot do encryption with express edition but can you can do it by other way

or file encryption only. For more reference follow http://sqlmag.com/database-security/sql-server-encryption-options.


Relational algebra question Here is the relational schema given :

employee (person-name , street, city)
works (person-name, company-name, salary)
company (company-name, city)
manages (person-name, manager-name)

Q: Find the names of all employees who live in the same city and on the same street as do their managers.

I find the solution somewhere :

but solution seems wrong which is just after second natural-join sign there is no select statement sign and just after that sign, a predicate appears 0_o , in that, from where does that manager-name attribute comes from?. I am completely lost.

My solution seems to be long since I used the Cartesian-product , and the query is bit long (of which I'm not even sure it's right or wrong) :(

DISCLAIMER : Never Learned Relational Algebra but it looks interesting

From the schema given and your question, this is what the SQL should be:

SELECT
    emp_mgr.person_name
FROM
    manages emp_mgr
    INNER JOIN employee emp ON emp_mgr.person_name  = emp.person_name
    INNER JOIN employee mgr ON emp_mgr.manager_name = mgr.person_name
WHERE
    emp.street = mgr.street AND
    emp.city = mgr.city
;

Here is another query that only uses JOINs, no WHERE clause:

SELECT
    emp.person_name
FROM
    (SELECT A.person_name,B.street,B.city FROM manages A
    INNER JOIN employee B ON A.person_name = B.person_name) emp
    NATURAL JOIN
    (SELECT A.manager_name,B.street,B.city FROM manages A
    INNER JOIN employee B ON A.manager_name = B.person_name) mgr
;

The first query gets all employees who are managed and their managers in the form of a Cartesian Product. Then, it looks for a common street and city.

The second query collects personnel records (name,street,city) of employees and their managers and performs a NATURAL JOIN between the employess and their managers using (street,city).

If you can transalate both queries back to Relational Algebra, I think you will have what you are looking for. I believe the second may be of better help.


After Restore Log_reuse_wait_desc of Replication I'm wondering if anybody has ran into this issue before and has any suggestions on what's causing it/how to fix it.  We are restoring a database into lower Dev environments and after the restore the log_reuse_wait_desc is Replication.  The problem is we are not using keep replication or CDCs.

This happens in a couple of different setups but I'll keep this initial one simple.

Database from a 2008 instance restored to a 2012 instance.  Only way to clear it out is to create a test publication on any random table, no subscription needed, and log_reuse_wait clears.

Can you try this following script on your database?

use master
exec sp_replicationdboption @dbname = N'your db name', @optname = N'<<name of your
publication>>', @value = N'false'
GO

select name, is_published, is_subscribed, is_merge_published, is_distributor
from sys.databases where name =<<your database name>>

This might be due to orphaned replication metadata. You can use the below query to remove:

USE master
EXEC sp_removedbreplication
    @dbname = 'myDB'


How can I resolve the error "Could not load file or assembly 'Microsoft.DataWarehouse.AS'" in SSIS? I launched good old Visual Studio 2017 today to create a new Integration Services project/package. When attempting to add a new target destination (OLE DB connection), upon selecting the drop down menu for the Table/View, I was greeted with an exciting message:

Could not retrieve the table information for the connection manager
'MyFancyConnection.Server'.
Could not load file or assembly
'Microsoft.DataWarehouse.AS' or one of its dependencies. The system
cannot find the file specified.

I tried upgrading my local SQL Server 2017 to the latest CU16, this did not resolve the issue.
How can I resuscitate my Visual Studio installation?

I was able to defeat the error by uninstalling these two extensions for Visual Studio:

Microsoft Reporting Services Projects
Microsoft Analysis Services Projects

Then I reinstalled the latest SSDT for VS 2017.

On installation, I chose all 3 options (Analysis Services, Integration Services, Reporting Services). Although I would bet the only requirement to solve the problem would be AS as the original error referenced AS specifically.


Best approach for a one-to-many list - how to store/acces some related count info? I have a table with persons, and other with interactions, notes and tasks, all related to person(s).
I need to have quick access to:

last interaction date.
number of interactions in the past 1,6, 12 months.
number of notes (Total)
number of tasks

All this info are used for sorting highlighting in a list/table. When user select a person, all related info can be requested from DB, question is how to get this data for all persons from db?
Version 1:
Sub-query and add the info for each person in table.
Could be intensive when tables grows.
Version 2: store this info in person table, update them when new info is created/modified.
This violates the 'single source of true' principle and could be prone to errors.
Any ideas?

Without specifics such as which database system, version, and implementation (when applicable), your table schema, how much data there is, anticipated will be, and general growth pattern, it's hard to recommend one option over the other.
It looks like you're trying to do standard types of OLAP querying against your data in which case both options are valid (to a degree). Most modern RDBMS can handle OLAP type queries for Tables with rows into the multi-millions without a blink of an eye. So option 1 can be sufficient of a solution up to a certain point, as long as proper architecting and indexing is adhered to. For example (and this is where it's important to know specifics like which database system you're on), Microsoft SQL Server offers columnstore indexing which can immensely improve the performance of an OLAP type query by efficiently compressing the data in a columnar format which helps improve aggregation queries. YMMV on these kinds of features depending on which database system you're using.
Alternatively, when you get to a point where the data is just too much for your regular OLTP architected database to handle, then pre-storing data rollups and the answers to your other OLAP type queries in data cubes (loosely using the term here) makes sense and is accomplished by either a real-time process (such as via triggers) or periodically calculated at a rate acceptable to the business needs, and is considered the one source of truth for whatever state it's in at any point of time. But I would still follow some level of normalization and not store this information directly on the person table, rather stored on a table that references the person table (by storing the person_id for example).


Does modifying an access file copy affect original file? This might me a stupid question, but it is crucial for me to know, so please bear with my limited knowledge of Access 2002 application.

Let's say I have an original database file, let's call it original.mdb. And I have Ctrl-C + Ctrl-V'd this file to the same location, with the same settings and everything, let's call it original_back.mdb.

Now, I want to understand the structure of files in Access 2002. Since both files will be pointing to the same database, will modifying a backup file affect the original? I might need to modify database for testing purposes, but I don't want to mess with original DB.

No, modifying a backup file will not affect the original. Access databases are self-contained in the MDB file.

Your statement "Since both files will be pointing to the same database" is incorrect. While both files are identical at the time of copying, they are (for all intents and purposes) separate databases.

If you're pointing an Access application to an external data source (e.g. SQL Server), this does not hold true.


Error When Trying to Run Subquery I am having issues getting my subquery to run properly.  Everything runs quickly and correctly until I add the subquery.  I get the error:
"Msg 4145, Level 15, State 1, Line 66
An expression of non-boolean type specified in a context where a condition is expected, near ','."

FROM FactPerson FP
JOIN FactCrash FC ON FP.CrashSK = FC.CrashPK
JOIN DimHWYType DHT ON DHT.HWYTypeCode = FC.HighwayTypeCode
JOIN FactVehicle FV ON FV.CrashSK = FC.CrashPK
JOIN DimVehicleType DVT ON DVT.VehicleTypeCode = FV.VehicleTypeCode
WHERE (FC.HighwayTypeCode = 'A' OR FC.HighwayTypeCode = 'B' OR FC.HighwayTypeCode = 'C')
AND (FV.VehicleTypeCode = 'A' OR FV.VehicleTypeCode = 'B' OR FV.VehicleTypeCode = 'S')
AND (FP.Age, FP.PersonType) IN
    (SELECT FP.Age, FP.PersonType
    FROM FactPerson FP
    WHERE (FP.PersonType = 'A')
    AND (FP.Age BETWEEN 18 AND 21))

I've also tried EXISTS, but it returns records that are outside the Age criteria.

FROM FactPerson FP
JOIN FactCrash FC ON FP.CrashSK = FC.CrashPK
JOIN DimHWYType DHT ON DHT.HWYTypeCode = FC.HighwayTypeCode
JOIN FactVehicle FV ON FV.CrashSK = FC.CrashPK
JOIN DimVehicleType DVT ON DVT.VehicleTypeCode = FV.VehicleTypeCode
WHERE (FC.HighwayTypeCode = 'A' OR FC.HighwayTypeCode = 'B' OR FC.HighwayTypeCode = 'C')
AND (FV.VehicleTypeCode = 'A' OR FV.VehicleTypeCode = 'B' OR FV.VehicleTypeCode = 'S')
AND EXISTS
    (SELECT FP.Age, FP.PersonType
    FROM FactPerson FP
    WHERE (FP.PersonType = 'A')
    AND (FP.Age BETWEEN 18 AND 21))

The AND (a,b) IN (SELECT x,y FROM ... WHERE) is standard SQL but not yet implemented in SQL Server. You can replace it with a correlated EXISTS subquery:

AND EXISTS (SELECT * FROM ... WHERE ... AND x=a AND y=b)

Your EXISTS subquery is not correct because it is not correlated.

In your specific query, it should be written:

AND EXISTS
    (SELECT *
    FROM FactPerson FPi
    WHERE FPi.PersonType = 'A'
    AND FPi.Age BETWEEN 18 AND 21
    AND FPi.Age = FP.Age AND FPi.PersonType = FP.PersonType)

But all the subquery is really unnecessary. The simple conditions would be equivalent:

AND FP.PersonType = 'A'
AND FP.Age BETWEEN 18 AND 21


How to use the METHOD_OPT parameter in DBMS_STATS.gather_table_stats Hi there I was just wondering what would be the difference between these 2 METHOD_OPT parameter settings:

FOR COLUMNS SIZE 5 some_column

and

FOR COLUMNS some_column SIZE 5

both will create a histogram with 5 buckets for the column 'some_column' right?

The official Oracle reference states that METHOD_OPT should have this form:

  FOR COLUMNS [size clause] column|attribute [size_clause]
  [,column|attribute [size_clause]...]

but this doesn't really help my case.

I've seen sources which use both versions so not sure if there is any difference.

EDIT

I was told that both of these are the exact same, but then what about these 2:

FOR COLUMNS SIZE 5 some_column SIZE 6 some_column2

and

FOR COLUMNS some_column SIZE 5 some_column2 SIZE 6

Looking at the syntax I would expect that COLUMS size_clause is a global setting, that can be overridden by specific size_clause for a single column. So in case of FOR COLUMNS SIZE 5 col1 SIZE 6, col2 means SIZE 6 takes precedence for col1, but col2 falls back to the default SIZE 5. This is what I would certainly expect, but I haven't verified this experimentally.