texts: Why might a parallel pg_restore command take hours longer to complete its non-parallel equivalent?

texts:
I used pg_restore to restore a 50 GB db from a directory backup using the following command which used 4 jobs:

pg_restore -d analytics -U postgres  -j 4 -v "D:\Program Files\PostgreSQL\10\backups\Analytics_08_2018__7_53_21.36.compressed"

I ran this from a command line. The restore took about  2 hours longer  than a non-parallel restore. it seemed to hang on creating indexes at the end of the restore job

pg_restore: launching item 2817 INDEX nidx_bigrams_inc_hits
pg_restore: creating INDEX "public.nidx_bigrams_inc_hits"
pg_restore: finished item 2965 TABLE DATA trigrams
pg_restore: launching item 2822 INDEX nidx_trigrams_inc_hits
pg_restore: creating INDEX "public.nidx_trigrams_inc_hits"
pg_restore: finished item 2823 INDEX nidx_unigrams_inc_hits
pg_restore: finished item 2822 INDEX nidx_trigrams_inc_hits
pg_restore: finished item 2817 INDEX nidx_bigrams_inc_hits
pg_restore: finished main parallel loop

Each of these pg_restore "creating index" jobs had a status of "idle" in pg_stat_activity.
Another pg_restore job was "idle" on commit.

I was hoping that the parallel restore would complete much faster than the default restore and it seemed to be doing that until it hung there after about 15 minutes.
There is not much else happening on this server. The indexes seemed complete when I checked them before the job finished i.e. they were the same size as the original database indexes.
Is there any particular reason why a parallel restore job would take longer to finalize/commit as opposed to the non-parallel restore?
I'd want to be prepared for that in a disaster recovery situation and rule out parallel if I cannot rely on the job duration.

By the way I am aware of file system based backups as opposed to logical, but I am only interested in logical for the time being.


texts: Does pure MongoDB use schemas and models like Mongoose?

texts:
I'm new on pure MongoDB and I've some questions about pure MongoDB.

1. Does pure MongoDB use schemas and models like Mongoose ODM?

If you have used Mongoose ODM, you know that Mongoose ODM uses schemas and models for defining fields with their properties. Like this;

/* Dependencies */
const mongoose = require('mongoose');

/* Define the user schema */
const userSchema = new mongoose.Schema({
  name: {
    type: String,
    required: true
  },
  lastname: {
    type: String,
    required: true
  }
});

/* Define the model */
const User = mongoose.model('User', userSchema);

Do we need to define schemas and models with their fields?

2. Does pure MongoDB have any validators?

For example, If we have a field in schema; we can define some properties to fields like;

...
  name: {
    type: String,  // Type built-in validator
    required: true // Required built-in validator
  },
  points: {
    type: Number,  // Type built-in validator
    min: 0,        // Min (minimum) built-in validator
    max: 100       // Max (maximum) built-in validator
  },
  verified: {
    type: Boolean, // Type built-in validator
    validate: {    // Custom schema validator
      validator: function(v) {
        return this.points > 50;
      },
      message: 'You can not register!'
    }
  }
...

Does pure MongoDB support for any validators or we define them while CRUD operations like this;

function createUser(userData, db, callback) {
  if(userData.name && typeof(userData.name) == 'string' && userData.username && typeof(userData.lastname) == 'string') { // Validator for object
    const collection = db.collection('users');
    collection.insertOne({
      name: userData.name,
      lastname: userData.lastname
    }, function(err, result) {
      if(err) callback(err, null);
      console.log('One record writed!');
      callback(null, result);
    });
  } else {
    callback(new Error('You must enter name and lastname to register!', null));
  }
}

All my questions are about these. Thanks...


texts: How do I tell the actual IOPS used by Oracle?

texts: This is hopefully a simple question. We have a large DB deployment that we need to scale up over the next year. For scaling we have been relying on our vendor, but recent developments have caused us to doubt their ability to provide good information about the DB. So, what I'd like to do is go into oracle and see what actual IO numbers look like. Is there any easy way to do this?


texts: Column is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

texts:
I'm trying to get the last time the data was modified in the tables of the database without any duplicating object_ids ,as I was getting 4 of the same object_Ids because the last_user_update field is present four times for the same object_id .I have tried different variations by grouping with Object_id ,max(Object_Id) and different fields and I still get the same error .
What is the right way to do this ?
This is the error message I'm getting

Column 'sys.dm_db_index_usage_stats.user_scans' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

    select  max(i.object_id)
                ,S.name
                ,I.last_user_scan
                ,i.user_scans
                ,i.user_lookups
            ,i.last_user_update
                ,i.last_system_scan
                ,i.user_updates
                ,s.modify_date
    from sys.dm_db_index_usage_stats I
    Inner Join sys.objects S
    ON I.object_id = S.object_id
    WHERE type = 'U'

group by S.name


texts: calculate or get price of parent items on bill of materials

texts:
i want to calculate or get the price of parent items on bill of materials query
here is the fiddle-> https://www.db-fiddle.com/f/o3jLgZxKNLG14mna8QGdVN/6
please note in the second row, betax has qty = 2 and the other parent items could behave the same
qty of the first row, always will be 1
what i need is to get the 'price' and 'subtotal' for every parent


texts: How to rollback database change in production after few hours or days

texts:
We are using Oracle 12c in production. Lets say there was release that went to production on Sunday and then some hours or some days later(e.g. Tuesday) we realized that we need to rollback the changes we did, assume there were DDL schema changes, along with DML changes which could be inserts, updates, deletes.
What is the best practice to rollback the changes? we can not restore database from backup because backup was from Sunday and there is data from Sunday to lets say Tuesday.
Just want to know what is the best practice for rolling back database changes in Oracle 12c.


texts: Sqlite3 indexes after deleting an element

texts: I am using sqlite3 in my project and a have a problwm with elements indexes. There are 2 columns in my table "number"(index) and "user_input". For example, if we have 5 notes in this table their indexes are 1, 2, 3, 4 and 5. If user who added note with index number 3 deletes his note, indexes bicome like 1,2,4,5. Is it possible to make them like 1,2,3,4 using any sql function?


texts: Simple query rephrasing for optimization

texts:
The following query:

select exists(select id
              from   avl_adc_equipo cfg
              where  cfg.imei=354676052451142
              and    cfg.adc=2)

Executes pretty fast, but as it gets called thousands of times using variations in imei and adc columns, it adds quite a high number of seconds.

It's explain analyze verbose output is:

Result  (cost=4.29..4.30 rows=1 width=0) (actual time=0.010..0.010 rows=1 loops=1)
  Output: $0
  InitPlan 1 (returns $0)
    ->  Index Only Scan using pk_avl_adc_equipo on public.avl_adc_equipo cfg  (cost=0.28..4.29 rows=1 width=0) (actual time=0.009..0.009 rows=0 loops=1)
          Index Cond: ((cfg.imei = 354676052451142::bigint) AND (cfg.adc = 2))
          Heap Fetches: 0
Planning time: 0.060 ms
Execution time: 0.027 ms

So, it's basically already using the table's PK for performance, but I guess the exists part is not the better approach.

Table schema:

Indexes:
    "pk_avl_adc_equipo" PRIMARY KEY, btree (imei, adc)
Foreign-key constraints:
    "fk_avl_adc_nombre__avl_adc_equipo" FOREIGN KEY (id_adcnom) REFERENCES avl_adc_nombre(id_adcnom) ON UPDATE RESTRICT ON DELETE RESTRICT

Any ideas? Anything else should I provide?


texts: Tombstone Table vs Deleted Flag in database syncronization & soft-delete scenarios

texts:
I need to keep track of deleted items for client synchronization needs.

In general, is it better to add a tombstone table and a trigger that tracks when a row was deleted from the server database - basically adding a new row to the tombstone table with the data from the deleted item - or to keep the items in the original table and flag them as deleted, typically with a column of type bit, to indicate that a row is deleted and another column to track when the delete occurred?


texts:
I has install Oracle RAC 11.2.0.3 on Oracle Linux 6.6 for 2 node. When install Clusterware to step execute root.sh had got error below. Can you help me check how to fix that and what wrong while install Clusterware. Please ask me if you need any information.

  Failed to create voting files on disk group DATA
  Change to configuration failed, but was successfully rolled back.
  CRS-4000: Command Replace failed, or completed with errors.
  Voting file add failed
  Failed to add voting disks at /u01/app/11.2.0/grid/crs/install/crsconfig_lib.pm line 6780.
  /u01/app/11.2.0/grid/perl/bin/perl -I/u01/app/11.2.0/grid/perl/lib -I/u01/app/11.2.0/grid/crs/install /u01/app/11.2.0/grid/crs/install/rootcrs.pl execution failed


texts: For 768M RAM server is 617M allocation in my.cnf good or bad?

texts:
I have a write-intensive web app, and I bought a VDS with 768M RAM

Is this my.cnf good for my purposes?

I specified bulk_insert_buffer_size to 126M and some other parameters as below:

[mysqld]
bulk_insert_buffer_size=126M
set-variable = max_connections=500
safe-show-database
max_user_connections=200
key_buffer_size = 16M
query_cache_size = 50M
tmp_table_size = 50M
max_heap_table_size  = 200M
thread_cache_size = 4
table_cache = 80
thread_concurrency = 8
innodb_buffer_pool_size = 100M
innodb_log_file_size = 25M
query_cache_limit = 50M
innodb_flush_log_at_trx_commit = 2


texts:
On my test environment, I have a SQL 2012 2-node physical cluster that accesses a common iSCSI storage on a separate Windows Server 2012 box. What options are there if I want to backup this storage to another storage array?

Also I want to mirror the DB to a DR site. Is log shipping ideal for this situation?

Thanks in advance,

Trev


texts:
Are there any advantages/disadvantages of making use of a merge over a insert function? Can an insert statement make use of the `OUTPUT'

Note: the ON clause for the merge is set to 1=0 (always insert)

Merge DeviceReading
    Using (
        --table here
    ) As [Source]
    On (1 = 0) -- Effectively Makes this an Insert as 1 Never Matches 0
    When Not Matched Then
        INSERT (
            [value1],
            [value2]
        ) VALUES (
            @value1,
            @value2
        ) OUTPUT Inserted.[value1], Inserted.[value2] Into @InsertIDTable;


texts: Which of the following pieces of SQL is most efficient

texts:
I need to add the date part of GetDate() to the time part of a stored DateTime.

Naively I thought I could do the following:

declare @testTime DateTime = '2013-04-23 13:55:06'
select cast(getdate() as date) + cast(@testTime as time)

but this gives the following error:

  Operand data type date is invalid for add operator.

I did some more research and have come up with three possible ways of doing the calculation:

declare @testTime DateTime = '2013-04-23 13:55:06'

select dateadd(dd, 0, DATEDIFF(dd, 0, GetDate())) + cast(@testTime as time)

select cast(cast(GetDate() as date) as datetime) + cast(@testTime as time)

select dateadd(day, datediff(day, @testTime, GetDate()), @testTime)

All three produce the same result but I'm not 100% happy about implementing any of them, though the third way seems to be a better way - though I have no evidence for this.

Which (if any) of the three is the most efficient?

Is there a better way?


texts: Which areas of a DBA's knowledge should a developer delve into?

texts:
I must admit the question is quite broad, so I'll try narrowing it down a bit. In our company we are 3-4 developers and have some SQL Server based installations running at our customer's sites (database sizes up to 100GB, up to 100 concurrent users, intranet applications). Nobody of us has real good experience in running/maintaining/administrating (whatever) databases. The customers not even that much. It's working ok till now, but I can't tell for sure if it's because we doing everything right or if we just didn't hit areas/situations we aren't proficient in.

So, I'm looking for the essential things you need to know when running a database from a DBA's point of view. You know the hard facts and know what matters most in your day for day job.

In which subjects should I gather deeper knowledge, what should I've heard of and what can I care of not until I face it for the first time?

I'm aware of the question Software Engineers and DBAs, but it's not quite what I was looking for. There are also lots of books around, but I'd like to hear it from those with practical experience.


texts: How to achive a many to one merge join in Azure Data Warehouse

texts: I have a merge join in azure data warehouse. My estimated execution plan currently shows it as a many to many join. I'd like to know if it is possible to achieve a many to one join. Currently I'm struggling to think of a way to do this as primary keys and unique constraints are unsupported. Is there anything available that would allow me to tell it that one of the tables will always contain unique values.


texts: mysql store search result as new table in memory

texts:
I am a novice, and I am wanting to know how to achieve this:

1million plus row in a table.

user runs a search, gets some results. I want to store this result in memory in a way, so that user can fire more SQL searches on this result. How is this done? I want this to go atleast upto 20 levels down.

in addition, lets say when I am 4th level down, can I have the previous levels intact for making fresh searches on them?

I also want to store some queries, which produce level X result, in a manner that it speeds the process in future (user do not have to make multiple searches to get to the result)

initial Table
|
|---1st search run on initial table (level 1)
      |
      |-----2nd search run on previously obtained result rows (level 2)

any help is highly appreciated.

thank you.


texts:
Please see the following statements. My question is, the table t1 was created in the users tablespace, so it will take up some space. But why it can be created before I impose the quota on the users tablespace?

Thanks.

sys@ORCL>create user a identified by a account unlock;

User created.

sys@ORCL>create table a.t1(c int);

Table created.

sys@ORCL>select owner, table_name, tablespace_name from dba_tables where table_name = 'T1';

OWNER                  TABLE_NAME             TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
A                  T1                 USERS

sys@ORCL>insert into a.t1 values(1);
insert into a.t1 values(1)
              *
ERROR at line 1:
ORA-01950: no privileges on tablespace 'USERS'

sys@ORCL>alter user a quota 10M on users;

User altered.

sys@ORCL>insert into a.t1 values(1);

1 row created.

sys@ORCL>commit;

Commit complete.

sys@ORCL>

BTW, I've tested using the user a to create the table before the user was imposed the quota. (Suppose the user has create session and create table privilege) The table still can be created. But in my example I'm creating the table using SYS user.

Edit:

This is the version information:

sys@ORCL>select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0  Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

sys@ORCL>


texts: How to change the files for a database through query?

texts:
Context:
Running with SQL server 2017 on our environment. We have a different drive that holds various files related to the database like F drive holds the .mdf files (primary and audit) and H drive holds the .ldf file (log).

Problem Statement:
Due to a small mistake, the audit and log file swap its place and placed in alternate drive like the log files switched to F drive along with the primary file and the audit file moved to the H drive.

Note: The database is running fine and transactions are happening without any error, but want to rectify this as there are 80 databases on the server and this happened for only few like 12 databases.

What I Tried so far:
I tried to shut down the server and swap the files to the correct directories but didn't work.

Is there a way to rectify this from any query without shutting down the SQL server for some time as other team is going to affect by this?

Please refer to the screenshot attached.


texts: How to avoid typecasting leading to full table update?

texts:
Is there a way by which update to ALL rows of a Mysql table could be denied or avoided?

Background:

I have a table 'Orders' having 'OrderID' (Type VARCHAR) and 'VendorID' (Type Integer) as two of the columns. If I run the below query on this table, it simply resets VendorID in all the rows:

SET `VendorID` = 0 WHERE `OrderID` = 0;

OrderID should not be 0 but because of some bug in the system, it escaped all the checks and created havoc. Had OrderID been of Integer type, this query would not have done anything. But since OrderID has alphabets, it could not be set as Integer type.

Can Mysql be configured in such a way that it rejects Updates to all rows? Would result of this query change if underlying database is not Mysql?

My environment:

nginx/1.4.6 Codeigniter (2.x) / Mysql (5.5) / InnoDB table type

Please suggest how this issue could be avoided in future, thanks.


texts:
We recently discovered one of our drives was being filled up by audittrace files. One of our DBAs turned off C2 auditing which should normally stop the files filling up the drive. However, it appears turning off C2 auditing has not had any affect.

I've tried looking at the existing traces to determine why the files are still being written.

SELECT * FROM sys.traces

ID    Status    Path
1     1         \\?\D:\MSSQL11.MSSQLSERVER\MSSQL\Data\audittrace20161113132900_260.trc
2     1         D:\MSSQL11.EPDM\MSSQL\Log\log_485.trc

ID 2 is currently set to the default but cycles after a max 5 files. This trace isn't the one filling up my hard drive.

ID 1 path and trace file are the ones filling up.

I've already turned off C2 auditing. What other troubleshooting can I do to see why the audittrace files are even being written to in the first place?

EDIT: I've restarted the services. It would seem the correct solution would be to turn off this audit trace by using the sp_trace_setstatus 1,0 but when I run that command I get the error:

Msg 8189, Level 14, State 32, Procedure sp_trace_setstatus, Line 6
You do not have permission to run 'SP_TRACE_SETSTATUS'.


texts: Stop MySQL generating repeated mysql-relay-bin-****.00000* files

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

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

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

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


texts:
I am in the process of planning a MongoDB deployment, this will be my first to production and I have a question regarding the disk size of each shard in a cluster.

This cluster itself will be for storing of time series building sensor data with the potential to accumulate 2TB of data every year quite soon after deployment.

So, I plan to start with a 2 shard cluster (2 x 3 node replica sets) since I then have the query routers and config servers in place from the get go. However I could do with some advice regarding how I go about choosing how much data I should store per shard.

Each 'shard node' will have a minimum of 48GB RAM and obviously the right disk configuration to meet the IOPS requirement.

If I am able to satisfy the IOPS requirement of the applications using this DB, what is to stop me sizing for 2TB per shard? Is there a limit on the volume of data a single shard should hold or guidelines to help my decision making process?

I am reading a lot about performance issues if the data volume exceeds the available RAM per host. But if the disks provide enough IOPS's surely this isn't a problem? I appreciate disks will still be far slower than memory but if MongoDB performs poorly when you exceed the RAM size then how to people deal with large databases? The cost to keep adding small shards to a cluster in order to stay within RAM is huge!

In short, to keep cluster expansion to a minimum, if I can satisfy my IOPS requirement, am I able to safely store any amount of data on a single shard or is there a much lower recommendation and if so, for what reason?

Also, I know i must try and keep my index size below my RAM size to ensure efficient query execution. Here is an example:

  If my data volume is 1TB per shard and I have 48GB RAM on each of the
  shard nodes, is there a way to estimate index size? The working set is
  hard to estimate since this is a data logging system which will update
  anything upto the total point count of data entering the database
  every minute, which might be 30,000 documents, inserted once, then
  updated with minute data for the whole day, then a new 30,000
  documents the following day, then updated etc....


texts: How to find Oracle is configured for OLTP or OLAP?

texts: Is there any specific query or configuration to check Oracle is configured for OLTP or OLAP?I have gone through many sites.But I can find the difference between OLTP and OLAP and not query or configuration to check that difference?


texts:
There were few inconsistency issues in our database and this happened because of unsuccessful fail-over of DB server(WSFC), same was asked in this question and is resolved last night after dropping and recreating primary key and moving data to another table.

There were many SQLDump files created before consistency error was fixed, its not getting created anymore however I see few SQLDIAG files in the log folder, opened the same using SSMS and it is set of events with name and timestamp, somewhere state_desc is appearing as clean whereas somewhere unknown or warning.

Can someone please help me understanding this and if its an issue which needs to be resolved?

  Version: Microsoft SQL Server 2014 (SP3) (KB4022619) - 12.0.6024.0
  (X64)   Sep  7 2018 01:37:51   Copyright (c) Microsoft Corporation
  Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3
   (Build 9600: ) (Hypervisor)


texts: How do we identify service shutdown time in MySQL

texts:
How can we identify service shutdown time in MySQL community version 5.7.20. I presume that "statement/sql/shutdown" and "statement/com/Shutdown" are the events that occurs while shutdown a service, using this events I tried to set triggers to load the time. But I am unable to set the triggers too.

The way I identified is through error log. Do we have any other variable or a way to identify the time of last service down.


texts:
I have created a messaging system with MySQL structure is as follows
      `message_id` int(11) NOT NULL AUTO_INCREMENT,
      `message_from` int(11) NOT NULL,
      `message_to` int(11) NOT NULL,
      `message_body` varchar(255) NOT NULL,
      `message_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
      `message_deleted` tinyint(1) NOT NULL DEFAULT '0',
      `message_read` tinyint(1) NOT NULL DEFAULT '0',
      PRIMARY KEY (`message_id`),
      KEY `message_from` (`message_from`),
      KEY `message_to` (`message_to`)

Currently The PK is an AUTO_INCREMENT so I am wondering if using this kind of key will be efficient for this type of system. Also the main reason why i am having a asking this question is to help in my function I have been having some issues with loading more messages. due to the fact that messages are deleted the PKs will not be consistent such as 1,2,3,10,255,300. When I try to run the more function using
(SELECT MAX(message_id)
 FROM member_messages
 WHERE (message_to = :userId
 AND message_from = :friendId)
 OR
 (message_to = :friendId
 AND message_from = :userId))AS max_id,
FROM member_messages AS MSG
WHERE (MSG.message_to = :userId
AND MSG.message_from = :friendId)
OR
(MSG.message_to = :friendId
AND MSG.message_from = :userId)
AND MSG.message_deleted = '0'
HAVING MSG.message_id >= max_id - 20
AND MSG.message_id <= :upperId
ORDER BY message_id ASC

In effect this query should return messages from the last 20 entered, but due to the PK being inconsistent sometimes no messages are returned. Eg. if the max_id is 200, 200- 20 is 180 and the messages with ID between this range are deleted or not associated with this members(userId) recipient(friendId). Any insight into this problem as to whether I should change the key type or change the query itself? Thanks for your time.
EDIT
Just a side note I used ASC because I wanted the newest messages to appear at the bottom of the DIV, while the fetched results would be older and appear at the top.


texts:
I have a master-slave setup of MySQL.  If I make any changes in the slave database...

Will it mess up the sync in any way ?
Will the changes get overwritten from the master during the next replication event ?
Can I make above (2) option happen ?


texts: What will happen if I drop SYSAUX tablespace from Oracle 11G

texts:
As there are two  mandatory tablespaces in Oracle SYSTEM and SYSAUX.
They have mention on docs 1 " You cannot drop the SYSTEM tablespace. You can drop the SYSAUX tablespace only if you have the SYSDBA system privilege and you have started the database in MIGRATE mode. " that means we can drop SYSAUX tablespace, then how system will be affected to this


texts: how can I divide log files for specific DB queries?

texts:
Currently my db has a couple of triggers for some (not all) tables that get executed when a specific number of columns has been updated. I keep track if the triggers for those rows on a boolean column in the tables.

Id like to be able to log UPDATE and INSERT queries and the errors/warnings coming from them onto different files in my postgresql server as a singular log file can get quite big.

Ive thought of a couple of approaches to this problem and am looking for a better approach than these;

have the triggers (plpython3) create seperate log files when they only get called (not a lot but they insert millions of rows onto different tables.
write additional triggers that insert logging information onto another table (either by explicit means or a single JSON column)

thank you for your time and answers!


texts: Database Mail on Amazon RDS / SQL Server

texts:
Since Database Mail is not supported on Amazon RDS with SQL Server, can anyone offer any suggestions on how to send e-mail notifications for failed SQL Server jobs in Amazon RDS?

Thanks in advance for any suggestions or workarounds.


texts:
For an assignment, we are doing select statements using Shakespeare's works in PG Admin. I must find which character, and in what work, said a specific phrase. I'm stuck with the following code:

select workid, charid from paragraph

where plaintext like 'rose'

In which I'm trying to find who said "A rose by any other name would smell as sweet." Any tips?


texts: How to mark an item as "Expired" when it's ExpiryDate value is in the past?

texts:
I have a table of items, each of which has a Status and ExpiryDate.

What is the best way of enforcing this:

when ExpireyDate > DateTime.Now
update Status to "expired"

I could have a stored procedure which is run periodically, which checks all items and updates as needed. However I would like to avoid any delay of the item expiring, and being marked as expired.

Is there a better way of doing this? Some kind of constraint in the Db?

How would an auction site do this, for example? Where the 'closing' of an auction at a very precise time is important.

Edit

I marked gbn s answer as the solution, as it provides a solution to my issue. However I think user212102 is getting at the crux of the issue - why am I storing status, rather than computing it based on the state of the item? I am going to look into a better way of achieving this.


texts: Error "the system cannot find the file specified" when opening a large SQL Server file

texts:
I have generated the script of my whole SQL Server database into a file and it's more than 36 GB. When I try to open it, I get the following error:

The system cannot find the file specified.

At first I intended to create the script in a new window, but every time my SQL instance got crashed. Also I have all the necessary permissions and I even tried opening it in the server, but I keep getting this error.
The server's RAM is 16 GB.
My initial goal is to change the whole database's collation (including every field and every table) and since it's a time-consuming process, I decided to generate the script of the database in order to create a new one with the right collation. I couldn't create a new window in the "Generate Script" process; therefore I had to create a new file in order to generate the script. The file is 36 GB.
When trying to open the file, I just get the mentioned error and SQL doesn't crash.


texts: Unable to start SQL Server Instance 2008 R2 - DB file corrupt

texts:
I was not able to start the SQL Server 2008 R2 production DB instance. After reading the log file error message is

" The log scan number passed to log scan in database ‘master’ is not valid. This error may indicate data corruption or that the log file (.ldf) does not match the data file (.mdf). If this error occurred during replication, re-create the publication."

After reading several post i realize that my MASTER DB file is corrupted. I have followed the below setup

Copy the Master.mdf and Masterlog.ldf file from Template location to My Database Data folder.

C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn\Templates to D:\MSSQL\MSSQL10_50.MSSQLSERVER\MSSQL\DATA

Note: Same error occur when i copy the all DB file like Master, MasterLog, MSDBData, MSDBlog, Model and ModelLog

When i run my MSSQLSEVER instance different problem occur.

In My server i had only C, D- Drive i dont have the E drive. How can i override these below error path.

Error LOG

2012-10-24 02:51:12.79 spid5s Error: 17204, Severity: 16, State: 1.

2012-10-24 02:51:12.79 spid5s FCB::Open failed: Could not open file e:\sql10_main_t.obj.x86fre\sql\mkmastr\databases\objfre\i386\MSDBData.mdf for file number 1. OS error: 3(The system cannot find the path specified.).

2012-10-24 02:51:12.79 spid5s Error: 5120, Severity: 16, State: 101.

2012-10-24 02:51:12.79 spid5s Unable to open the physical file

"e:\sql10_main_t.obj.x86fre\sql\mkmastr\databases\objfre\i386\MSDBData.mdf". Operating system error 3: "3(The system cannot find the path specified.)".

2012-10-24 02:51:12.79 spid5s Error: 17207, Severity: 16, State: 1.

2012-10-24 02:51:12.79 spid5s FileMgr::StartLogFiles: Operating system error 2(The system cannot find the file specified.) occurred while creating or opening file 'e:\sql10_main_t.obj.x86fre\sql\mkmastr\databases\objfre\i386\MSDBLog.ldf'. Diagnose and correct the operating system error, and retry the operation.

2012-10-24 02:51:12.79 spid5s File activation failure. The physical file name "e:\sql10_main_t.obj.x86fre\sql\mkmastr\databases\objfre\i386\MSDBLog.ldf" may be incorrect.


texts: designing database where users can have multiple positions

texts:
I am trying to determine the best model for the following case:

There are multiple offices
Each office has multiple staff, each of which have a role (chief, deputy chief, etc)
Each office also has many projects
Each project is managed by one or more staff (project manager, assistant project manager, deputy project manager, etc)

so, for example, an office has an office head, deputy head, etc.  They also have project managers (which could be the same as office head) and sub managers that run projects.

I want to have a design where we can tag office employees to an office as well as tag employees that are project managers to a project.  Here is what I have:

I have an office table
I have a project table
I have a office_project table

I have an employee table
I have a role table

I have an office_employee table
I have an employee_role table

The question is, what is the best way to tie the project table to the employee table?  I assume I should have one employee table, or does it make sense to have a table to employees and a table for project managers, each with their own roles table?  It doesn't seem that way. And, the way it is now, I can tie an office to a project, and see all the project employees, but not all office project managers would be associated with a given project.

If you need an ERD design of what I have, please let me know.

thanks


texts: Is there any way to set the default data source for the SQL Server Import and Export Wizard?

texts:
When I launch the SQL Server Import and Export Wizard for SQL Server 2014, the selection in the Data source drop-down defaults to .Net Framework Data Provider for ODBC.  This happens regardless of whether I launch the wizard standalone, or from the context menu after right-clicking on a database within the SSMS Object Explorer.

Is there a way, perhaps through a registry setting, to change that default value to a data source I will use regularly, such as SQL Server Native Client or the Microsoft OLEDB Provider for SQL Server?


texts: How to control blocking sessions while an index maintenance job runs?

texts:
I would like to know, is there any possibility to control the locks while Ola's index optimize job is running?

I have implemented Ola Hallengren's index optimize job and am running it every day, but it is blocking the user transactions and causing performance issues.

How can I avoid blocking and locks while an index job is running?


texts: Using Table as a Variable for a Stored Procedure

texts:
I am working on adding some stored procedures to my database and am stuck on how to do the SQL for what I want to do.

For the procedure I am working on right now, it needs to have two parameters, a table name, and a column name.

The procedure needs to check if that column exists within the table, and if it does, return all of the data within that column.

Now I have been going through both this site, and Google trying to figure out how I'd go about programming this, but needless to say I am utterly confused and not sure what to do.

If I were to pseudocode the procedure it would look like so:

CREATE PROCEDURE GetTableColumn (
    @ColName VARCHAR(25),
    @TblName TABLE READONLY
) AS
BEGIN
    IF (EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TblName AND COLUMN_NAME = @ColName))
        SELECT @ColName
        FROM @TblName
END

I have tried the above code, and it doesn't work, basically I have no idea how to set up a variable that can be a table.


texts: 'Privileges dependent' error when try to drop a role/user even if there are no database attached and no role attributes

texts:
I had a user/role that was a database owner;

so I dropped first the database;
I try dropping the user and I get the following error:

  role 'user_name' cannot be dropped because some objects depend ot it
  DETAIL: privileges for schema public

This is very confusing for me;

The schema public is the default schema schema when a database is created.

By dropping the database, shouldn't schema and other objects related to database dropped ?  Why this privileges error ?

I want not just a solution to fix it, but also to understand why.

I checked:

with \l to be sure the database was droped
\du to check role attributes(empty)


texts: How to manage partitions in MariaDB with InnoDB tables?

texts:
I'd like to set up partitioning for the "time series" use case as in
https://mariadb.com/kb/en/partition-maintenance/.

However, I'd also like to control data directories. And I want the "max" or "future" partition as a catch all fallback.

So I start with (minimalist example):

CREATE TABLE partitioned (
  timestamp INT(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (timestamp)
)
ENGINE=InnoDB
PARTITION BY RANGE (timestamp)
(
  PARTITION pstart VALUES LESS THAN (1) DATA DIRECTORY 'G:/MariaDB/partitions/start',
  PARTITION p1 VALUES LESS THAN (1577833200) DATA DIRECTORY 'G:/MariaDB/partitions/1',
  PARTITION pmax VALUES LESS THAN MAXVALUE DATA DIRECTORY 'G:/MariaDB/partitions/max'
);

Works as expected.

Now I want to add a new partition, splitting the "pmax" partition:

ALTER TABLE partitioned
  REORGANIZE PARTITION pmax INTO (
    PARTITION p2 VALUES LESS THAN (1609455600) DATA DIRECTORY 'G:/MariaDB/partitions/2',
    PARTITION pmax VALUES LESS THAN MAXVALUE
);

This only kind of succeeds, as the DATA DIRECTORY clause is ignored, with a warning. So the result is useless. The "pmax" partition is now in the default data directory and the "p2" partition is in G:\MariaDB\partitions\max.

Specifying both data directories:

ALTER TABLE partitioned
  REORGANIZE PARTITION pmax INTO (
    PARTITION p2 VALUES LESS THAN (1609455600) DATA DIRECTORY 'G:/MariaDB/partitions/2',
    PARTITION pmax VALUES LESS THAN MAXVALUE DATA DIRECTORY 'G:/MariaDB/partitions/max'
);

gives two warnings, the "pmax" stays in its place now, but the "p2" partition is still created in that same directory.

ADD PARTITION doesn't work either, as it collides with MAXVALUE condition.

Any way around this?

MariaDB version 10.4.12


texts: Update Quantity Available using Quantity from another table

texts:
You will demonstrate an update that includes two tables and uses a subquery.

  Make sure to prove that your update executed correctly by showing data in the tables before and after the update.

I'd like to update the quantity available based on quantity ordered - table book Available = Available - quantity from orders table

This is what I have so far: BUT IT's not Working

SELECT Available
FROM   book
WHERE  Available IN (SELECT ALL(Available)
                     FROM   orders
                     WHERE  Quantity >= 1);

UPDATE book
SET book.Available = Available - (SELECT orders.quantity
                                  FROM orders
                                  WHERE orders.ISBN_No=book.ISBN_No)
WHERE Available IN (SELECT ALL(Available)
                    FROM orders
                    WHERE Quantity >= 1);

SELECT Available
FROM book
WHERE Available IN (SELECT ALL(Available)
                    FROM orders
                    WHERE Quantity >= 1);

Here are the 2 tables:

CREATE TABLE book
(
ISBN_No INT(10) PRIMARY KEY,
Title VARCHAR(30),
Author VARCHAR(50),
Publisher VARCHAR(50),
Publication_Date DATE,
Genre VARCHAR(25),
Condition_type VARCHAR(5),
Cost DECIMAL(8,2),
Available INT(5)
);

INSERT INTO book VALUES
(0234567890, 'Harry Potter Collection', 'J.K. Rowling', 'Bloomsbury', '2010-01-01', 'Children', 'NEW', '65.00', 7),
(0987654321, 'Lord of the Rings Collection', 'J.R.R. Tolkein', 'Harper', '2001-02-15', 'Fantasy', 'USEDA', '18.00', 1),
(0421548752, 'The Help', 'Kathryn Stockett', 'Del Rey', '2015-03-25', 'Fiction', 'USEDG', '9.00', 3),
(0545454235, 'Pride and Prejudice', 'Jane Austin', 'Scholastic', '1999-12-05', 'Classic', 'USEDG', '12.00', 2),
(0458742154, 'Alexander Hamilton', 'Ron Chernow', 'Scholastic', '2017-06-12', 'Biography', 'NEW', '24.00', 12);

CREATE TABLE orders
(
Order_No INT(6) PRIMARY KEY,
Order_date DATE,
ISBN_No INT(10) NOT NULL,
Quantity INT(5),
Cost DECIMAL(8,2),
Total DECIMAL(8,2),
Delivery_Method VARCHAR(6),
Payment_type VARCHAR(12) NOT NULL,
Credit_Card VARCHAR(16),
Credit_Exp DATE,
Employee_ID INT(3),
Customer_No INT(11) NOT NULL,

FOREIGN KEY (Customer_No)
REFERENCES customer(Customer_No),
FOREIGN KEY (Employee_ID)
REFERENCES employee(Employee_ID),
FOREIGN KEY (ISBN_No)
REFERENCES book(ISBN_No)
);

INSERT INTO orders VALUES
(101112, '2018/01/29', 0234567890, '2', 65.00, 130.00, 'UPS', 'D', '54661625258778596', '2020/01/22', 004, 100100),
(101100, '2018/01/18', 0421548752, '1', 9.00, 9.00, 'UPS', 'CC', '8546587785965222', '2020/12/22', 004, 100100),
(101106, '2018/01/23', 0545454235, '1', 12.00, 12.00, 'USPS', 'CASH', 'NULL', 'NULL', 004, 784521),
(101109, '2018/01/25', 0458742154, '5', 24.00, 120.00, 'USPS', 'CC', '6541254265874856', '2025/02/22', 005, 784521),
(101105, '2018/01/27', 0545454235, '1', 12.00, 12.00, 'USPS E', 'CASH', 'NULL', 'NULL', 004, 784521);

I'm stuck.  What I have above I've tried changing in so many different ways to no avail.


texts: Split Date/Time value for Night Shift Employee


texts: MariaDB | Sync Master with Slave in another Datacenter

texts:
We have a web application hosted on a dedicated server at Datacenter A. I am in the process of setting up a disaster recovery protocol and am considering the following.

My current plan is to purchase another dedicated server (or vps) at Datacenter B. This server will host a slave copy of the database located at Datacenter A. This will be my first time configuring replication and am curious about the following:

Will there be a performance drop for the server hosted in Datacenter A due to network latency during the replication with server in Datacenter B?
Database on server in Datacenter A is currently only accessible to applications running on the server (or via ssh tunnel). In order to configure replication with server hosted in Datacenter B, will I need to open port 3306 or can the replication take place via ssh?


texts: MariaDB "EXECUTE AS" or User Impersonation

texts:
With MariaDB, is there a way to impersonate a user execution context when  connected with a higher privileged account?

For instance, when connected with a "MasterUser" execute a SELECT using the more limited privileges of a "ClientUser".

The classic idea is to avoid the server application to perform thousands of connections to MariaDB -- if most requests could be handled through a "broker" user which would connect once and operate under the appropriate privilege level for each given transaction.

Thank you!


texts: Can't connect to MySQL NDB manager using ndb_desc

texts:
I've got a basic MySQL NDB cluster running - it works well.  Manager, data  and SQL nodes are all OK.  1 manager, 2 SQL nodes, 4 data nodes.  2 of the data nodes are from the initial creation, 2 are nodes I'm trying to add.

Environment-wise, this is what I have (applies to all nodes).

OS: CentOS 7
SELinux: disabled
firewalld : not installed
MySQL version: mysql-5.6.28
NDB: ndb-7.4.10

View of the configuration looks like this:

[root@mysql-ndb-manager ~]# ndb_mgm -e "SHOW"
Connected to Management Server at: localhost:1186
Cluster Configuration
---------------------
[ndbd(NDB)] 4 node(s)
id=2    @10.133.16.108  (mysql-5.6.28 ndb-7.4.10, Nodegroup: 0, *)
id=3    @10.133.16.196  (mysql-5.6.28 ndb-7.4.10, Nodegroup: 0)
id=6    @10.133.16.121  (mysql-5.6.28 ndb-7.4.10, Nodegroup: 1)
id=7    @10.133.16.112  (mysql-5.6.28 ndb-7.4.10, Nodegroup: 1)

[ndb_mgmd(MGM)] 1 node(s)
id=1    @10.133.16.179  (mysql-5.6.28 ndb-7.4.10)

[mysqld(API)]   3 node(s)
id=4    @10.133.16.117  (mysql-5.6.28 ndb-7.4.10)
id=5    @10.133.16.180  (mysql-5.6.28 ndb-7.4.10)
id=8 (not connected, accepting connect from any host)

NoOfReplicas is set to 2, so this node count should be OK.  I realised pretty quickly that NoOfReplicas set to 2 means adding a single additional data node doesn't work.

As you can see, I have an available node ID (8) that can be allocated for things like ndb_desc, etc.

New nodes I'm trying to add:

IDs: 6 & 7

They are configured properly, as far as I can tell, but have no data on them.  They've started properly and are listed as part of the cluster.  It's the redistribution process that isn't working for me.

The docs are pretty easy to understand, i.e. they require the use of ndb_desc to connect to the manager and alter the table structure so that the data is distributed to the new nodes.

When I run the required commands, I get this:

[root@mysql-ndb-manager ~]# ndb_desc -c 10.133.16.179:1186 my_table -d
appdb -p --ndb-nodeid=8
Unable to connect to management server.

NDBT_ProgramExit: 1 - Failed```

I've seen others with similar issues, and it nearly always seems to be the lack of a node ID that can be allocated.  This isn't the case for me, as far as I can tell.

I've tried connecting with the manager's IP address, localhost, 127.0.0.1.
 I've tried locally and remotely.  I've tried using the exact command from the docs, i.e. their database and table names but that doesn't work (as expected, I guess).

The only data I have right now is as follows:

Database: `appdb`
Table: `my_table` (ID auto_increment and `name` char(25))
Data: 14 rows, random names

Data is replicated between the old/working nodes without issue, i.e. I can create a database or run any SQL command on one of them and the change is immediately reflected on the other node.  Offline nodes show up as offline as expected and show up as online when I bring them back up.

I've also got a rolling restart script that works without any issues so it looks like the clustering side of things is OK.

Lastly, here's my manager's configuration file:

[ndb_mgmd default]
DataDir=/var/lib/mysql-cluster

[mgm]
HostName=10.133.16.179

[ndbd default]
NoOfReplicas=2
DataMemory=256M
IndexMemory=128M
DataDir=/var/lib/mysql-cluster

[ndbd]
HostName=10.133.16.108

[ndbd]
HostName=10.133.16.196

[mysqld]
HostName=10.133.16.117

[mysqld]
HostName=10.133.16.180

[ndbd]
HostName=10.133.16.121

[ndbd]
HostName=10.133.16.112

[mysqld]

What else could be causing this?

Thanks!


texts: is it possible to create a dynamic website without a database?(Only use a File to save Data)

texts:
I learn PHP from now. And  for create a dynamic website usually use PHP + MYSQL(For database).

In PHP have function like fopen() to create, write result from form to a file like txt or other.

My Question is,  is it possible to create a dynamic website without a database?
(For very small Website maybe)?

Im very Appreciated your advice

Thanks.


texts:
This query is not too complex:

select
    trunc(created_date, 'MONTH') as created_date,
    al.op_name,
    al.region,
    al.alarm_type,
     COUNT(1) as total_new,
     SUM(
        (SELECT
            COUNT(1)
         from alarm_table ial
            WHERE ial.status_alarm = 'SOLVED'
            AND TRUNC(ial.solved_date, 'MONTH') = TRUNC(al.created_date, 'MONTH')
            AND ial.region = al.region
            AND ial.op_name = al.op_name
            AND ial.alarm_type = al.alarm_type)
    ) as total_solved
FROM
    alarm_table al
WHERE
    created_date is not null
group by
    trunc(al.created_date, 'MONTH'),
    al.op_name,
    al.region,
    al.alarm_type
order by trunc(al.created_date, 'MONTH') desc

And here is its explain plan:

Plan
SELECT STATEMENT  ALL_ROWSCost: 5,497
    3 SORT AGGREGATE  Bytes: 51  Cardinality: 1
        2 TABLE ACCESS BY INDEX ROWID TABLE USER.alarm_table Cost: 22  Bytes: 51  Cardinality: 1
            1 INDEX RANGE SCAN INDEX USER.IDX_alarm_table_RESU Cost: 3  Cardinality: 73
    6 SORT ORDER BY  Cost: 5,497  Bytes: 1,055,263  Cardinality: 24,541
        5 HASH GROUP BY  Cost: 5,497  Bytes: 1,055,263  Cardinality: 24,541
            4 TABLE ACCESS FULL TABLE USER.alarm_table Cost: 3,025  Bytes: 11,696,387  Cardinality: 272,009

The Question is: I have an index containing the columns al.op_name, al.region and al.alarm_type (that IDX_alarm_table_RESU, which it even uses on the first part).

Since there is no further joins, why would it be doing the FTS?


texts: How To Merge Database Projects on Visual Studio 2013

texts:
Can I have multiple database projects to the same SQL Server database? Like, one main project referencing multiple subprojects.

I have an application with multiple modules and each module has its own database structure living in a schema.

I want to reuse the modules in another application, for example, the security module, which has the user, roles, groups, etc.

I want to know if there is a way I can define those schemas and all of its objects (tables, stored procedures, indexes, views, etc) in one database project and then reference this "base" database project in the database project of the actual application.


texts: Different execution plans during INSERT at @table variable and #temporary table

texts:
I faced with strange situation: inserting of ~800 000 rows into table variable is much more faster than inserting (with the same select) into temporary table. INSERT is under stored procedure. SELECT have parameters from stored procedure.

Sample code below:

create procedure p1
    @Param1 int
AS
    declare @t table (id int)

    insert into @t
        select id
        from dbo.SomeTable
        where SomeField = @Param1

And if I change @t to #t - than I have more than 2.5 difference in procedure's execution time.

All the time as DBA I thought that so big row count should be processed in temporary tables... But now, I don't think so :)

Anyway, I'm just trying to understand what is going on and why the same SELECT have different execution plans during INSERT.

The same case (as I thought) was here: INSERT performance difference between temporary tables and table variable. But I have the opposite situation - INSERT into # is SLOWER than @

So, do you have any ideas?

what is the better in this case: use @ or #?
why do I get different execution plans ?

Execution plan here: https://drive.google.com/file/d/0B1iqgEJ5a7vfcjAwMmtJaVFrNWM/view?usp=sharing

Thank you in advance!

P.S. Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)

P.P.S. I've found really good article about temporary tables and table variables here: What's the difference between a temp table and table variable in SQL Server?. But, unfortunately, it didn't help me.


texts:
I am stumped and need your help.

I am trying to select three columns from my table where one of them is a VARCHAR and the other two are either "Y" or "N".

SELECT  CurrencyPair, Count(Accepted) As Accepted, Count(Successful) As Successful
    FROM  Trades
    WHERE  Accepted = "Y"
    GROUP BY  CurrencyPair
    ORDER BY  Successful/Accepted DESC, Successful DESC
    LIMIT  5

This returns a table just as I like in regards to format, but the data seems to only show where the three columns coincide. For example id three were accepted and two were successful I would expect the count for accepted to be 3 and the count for successful to be 2. My table always returns the same number of accepts and successes despite knowing the data in the table having more accepted than successes.

Furthermore, please have a look at the division operator to make sure it is correct syntax. It's purpose is to place higher success ratios at top.

Thanks in advance.


texts:
What are some of the good options to sync two or more MySQL databases of the same schema, situated at different locations; reliably?

We have four database in four different locations.

we tried using the generic master master replication but the link breaks again and again leading to inconsistencies.


texts: MS SQL Server: Import Flat File with auto-increment ID

texts:
So I've downloaded a sample database. It originally didn't have an ID column so I inserted one in through Excel. Anyways, I went through DatabaseName > Tasks > Import Flat File and the usual process, and arrive at the modify columns below.

My issue is, where can I specify that the ID column should be auto-incremented? And if I can't do that here, what should I do as a workaround?


texts: Does Write-Ahead-Logging allow me to make concurrent changes to a table?

texts:
In Sqlite3, Write-Ahead-Logging mode allows one process to read a table, while another is writing to it. It doesn't say anything about one process writing and another deleting/updating.

Is this possible in Sqlite3?


texts: Can I execute a prepared statement inside a MySQL SELECT?

texts:
I'm running a search query in MySQL to return items from a products and pricelist table. When a user does a search query I need to get products from the products table LEFT JOINED with all authorized/unlocked sellers from a 2nd table (any number of sellers).

I have the basic search query working, but I can't get the "dynamic LEFT JOIN" to work. I was told to do this in a prep statement, which I'm (a) struggling mightly (first time) and which I don't know where to put when working.

My search query will look like this:

SELECT articles AS art
<< need to left join here >>
FROM bigtable AS bt
WHERE
  a lot of other criteria

This is the prep statement I came up with:

SET @sql_text := '
    DECLARE strCount   INT DEFAULT 1;

    SELECT sid, ifnull(pricelist,"BASE"), count(*) AS recs
    FROM buyerList AS b
    LEFT JOIN sellerList AS s ON s.sid = b.sid AND s.pass = b.pass
    WHERE b.bid = ?

    SET @string = "LEFT JOIN preislisten AS p";

    lj:
      LOOP

        SET @string = CONCAT( @string, "ON (p.iln = a.iln AND p.preisliste = sid AND p.ean = a.ean AND p.iln = pricelist ) OR");

        SET strCount = strCount+1;
        IF strCount = recs
        THEN LEAVE lj;
        END IF;

    END LOOP lj;

    SET @string = CONCAT( @string,"(1=0)")
    ';

SET @param_iln = param_iln;
PREPARE stmt FROM @sql_text;
EXECUTE stmt using @param_iln;
DEALLOCATE PREPARE stmt;

So I'm basically finding all sellers & pricelist-name (default BASE) and then try to construct a string like this:

LEFT JOIN pricelists p ON
(p.sid = a.sid AND p.pricelist = "foo" AND p.ean = a.ean AND p.iln = 23467 ) OR
(p.sid = a.sid AND p.pricelist = "bar" AND p.ean = a.ean AND p.iln = 99999) OR
...
(1=0)

My questions:
(1) If there are 25 sellers, I will do 25 loops, but will the correct seller and pricelist be inserted into my string like this?
(2) How do I insert this into my actual search query? If I execute, I may get a string back but I can't just put the string into the query, can I?

Thanks for being easy on me... first time prep-statement, 2nd week MySQL...

EDIT:
So this is what I came up with:

# bigtable query
SELECT count(a.id) AS gesamt_datensaetze, a.nos, a.nos_anzeige
    FROM artikelstammdaten AS a

    #JOIN colors
    LEFT JOIN farbenzuordnung AS zu
        ON a.farbe = zu.farbe
        AND ( ( param_filter = '' ) AND (1=1) OR ( zu.ILN = param_filter ) )

  # ========== NEW PART ==========
  LEFT JOIN preislisten AS p
    ON (        p.iln = a.iln
            AND p.EAN = a.EAN
            AND p.preisliste = (  #NEW GET pricelists
                                                        SELECT IFNULL(klhs.preisliste, "-Standard-") AS pricelistID
                    FROM kundenliste_haendler AS klhd

                    LEFT JOIN kundenliste_hersteller AS klhs
                        ON klhs.iln = klhd.iln_verkaeufer
                        AND klhs.plz = klhd.plz
                        AND klhs.cid= klhd.cid
                                                WHERE klhd.iln_kaeufer = param_iln
                    GROUP BY pricelistID
                              )
           AND p.iln = ( #GET seller
                  SELECT klhd.iln_verkaeufer AS sellerID
          FROM kundenliste_haendler AS klhd

                   LEFT JOIN kundenliste_hersteller AS klhs
                     ON klhs.iln = klhd.iln_verkaeufer
                     AND klhs.plz = klhd.plz
                     AND klhs.cid= klhd.cid

                     WHERE klhd.iln_kaeufer = param_iln
                     GROUP BY pricelistID
                     )
    )
    # active
    WHERE   a.aktiv = "ja"
    # more criteria

Stil trying to find out if this works correctly. If anyone can shed some insights on whether I can combine both my (identical) nested selects into a single nested select, it would be greatly appreciated!


texts:
I have installed oracle correctly and also set ORACLE_HOME correctly on server,still sqlplus command is not working in my PC while it is working in others PC. I think I missed out some important step can you tell me what should I do ?

 $ echo $ORACLE_HOME
/u01/app/oracle/product/11.2.0/dbhome_1

This following is error message:

$ sqlplus
bash: sqlplus: command not found


texts: MySQL Galera cluster can't initiate first node

texts:
I'm using Ubuntu 16.04 and installed MySQL version 5.7 via apt-get in AWS.

After that, I've managed to upgrade the MySQL to Galera Cluster by following the Documentation from the official website using apt-get install galera-3 galera-arbitrator-3 mysql-wsrep-5.6

After that I've configured the file /etc/mysql/conf.d/galera.cnf to be:

[mysqld]
binlog_format=ROW
default-storage-engine=innodb
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0

# Galera Provider Configuration
wsrep_on=ON
wsrep_provider=/usr/lib/galera/libgalera_smm.so

# Galera Cluster Configuration
wsrep_cluster_name="test_cluster"
wsrep_cluster_address="gcomm://this_node_ip,second_ip,third_ip"

# Galera Synchronization Configuration
wsrep_sst_method=rsync

# Galera Node Configuration
wsrep_node_address="this_node_ip"
wsrep_node_name="this_node_name"

The step above is repeated for the other 2 nodes (with it's own IP address). And stop the mysql with the following command sudo service mysql stop for each of the node.

And in the first node, I'm trying to start with the following command:

sudo service mysql start --wsrep-new-cluster

But it returns: Job for mysql.service failed because the control process exited with error code. See "systemctl status mysql.service" and "journalctl -xe" for details.

In the systemctl status mysql.service, it says:

● mysql.service - LSB: start and stop MySQL
   Loaded: loaded (/etc/init.d/mysql; bad; vendor preset: enabled)
   Active: failed (Result: exit-code) since Fri 2018-02-09 15:53:16 +08; 33s ago
     Docs: man:systemd-sysv-generator(8)
  Process: 6264 ExecStart=/etc/init.d/mysql start (code=exited, status=1/FAILURE)

Feb 09 15:53:15 systemd[1]: Starting LSB: start and stop MySQL...
Feb 09 15:53:15 mysql[6264]: Starting MySQL
Feb 09 15:53:15 mysql[6264]: .180209 15:53:15 mysqld_safe Can't log to error log and syslog at the same time.  Remove all --log-error configuration options for --syslog to take effect.
Feb 09 15:53:15 mysql[6264]: 180209 15:53:15 mysqld_safe Directory '/var/run/mysqld' for UNIX socket file don't exists.
Feb 09 15:53:16 mysql[6264]:  * The server quit without updating PID file (/var/run/mysqld/mysqld.pid).
Feb 09 15:53:16 systemd[1]: mysql.service: Control process exited, code=exited status=1
Feb 09 15:53:16 systemd[1]: Failed to start LSB: start and stop MySQL.
Feb 09 15:53:16 systemd[1]: mysql.service: Unit entered failed state.
Feb 09 15:53:16 systemd[1]: mysql.service: Failed with result 'exit-code'.

Hope these information helps.


texts: SSIS Merge component performing a UNION instead of merging

texts:
I have a package to combine data from multiple databases. I'm trying to bring together two data sets so they can be written to a single table.

There's a common SSIS_ID (DT_I4) used to match rows and the other columns I want to bring in to the same data set are also DT_I4.

Here's the section of the flow where the problem is; as you can see the data from both paths is sorted on SSIS_ID before entering the Merge:

And here is the configuration of the Merge component. The intention is to put the CRO_CompanyID and Sponsor_CompanyID for the same SSIS_ID in to a single row:

However the output of the merge is acting like a UNION; there are 1,532 rows entering from the left and 4,303 from the right resulting in 5,835 rows at the output.

A data viewer on the output shows the problem:

I can get around this problem by processing the data in a different way but it's really bugging me that I can't get this to work.

Any suggestions as to what I have done wrong or not done to be able to combine the data in this way?

I'm using SSIS for SQL Server 2008 but there wasn't a tag for that version.


texts: Identity column as clustered index bad idea?

texts:
Its just been a few months of programming in SQL Server for me so my knowledge is not good in many regards. In an already existing project at work I came across many tables with large composite primary keys with clustered index. From what I have gathered, a large column/composite column with clustered index hits performance very hard, and at times the logical solution is an identity column. But at the same time I have come across many people flaming the over-usage of identity columns.

But I have never came across an example where identity column is a bad idea.

Recently we have standardized that every table should have an identity column as the clustered index - whether we use it as PK or not, as we require it for some export purposes.

So I would like some examples, in real life scenarios, where using an identity column as a clustered index is a bad idea.

Though at times it makes our life easy I have never encountered a scenario where it will be considered bad.

PS: I think my question is a bit naive but it is bugging me so much so I had to ask about it.


texts:
Currently, I have a script queries my CMS server to get the list of servers in a certain CMS group. With that list, I perform a foreach loop to execute a query against each instance in the CMS group individually.

I am wondering if there is a way (such as in SSMS) where my query can be run against all targets in the CMS group and results from all targets are shown in one table?

Essentially I don't want to end up with my results separated. I would like them all in one table. If this is possible it would be easier than formatting data rows in PowerShell.


texts: How do I easily create the DDL statements for a schema represented by the contents of an XML file?

texts:
I have been given an xml file and it's pretty obvious in looking at the contents that it contains an inherent schema. What I'd like to do is run a program against it that outputs the schema represented by the contents, then create said schema in / as a database (in Postgres), and then populate said database so I can run SQL queries against the data.

I figured that this has to be a commonly desired capability, but I've never needed to do this before and am not sure what already exists.

I work in Postgres, and it has many XML related tools, so I figure that's a good place to look but so far haven't found it.

Added information:

Doing some digging, I found that the people who gave us the xml file also have an "xml schema file" that matches, and I've gotten a copy. It's on the web here for those that care.)

OK, so step one was satisfied; get to an "xml schema."

Now I need to convert that into an SQL schema...


texts: How to Duplicate Column in same table in SQL

texts:
I'm pretty basic with SQL and need help with the following task:
I already have a table with data in it. What I would like to do is to Duplicate a Column (Including the data) in the same table but with a different column name. Is this possible?
Any help would be greatly appreciated.


texts: Cannot Start/Stop Services using SSMS 2014 (but 2008R2 works)

texts:
In SSMS 2014, when I connect to a server in Object Explorer using my network credentials (I am a domain admin), I can do pretty much everything I would expect as a DBA. But I cannot start/stop/restart services. For instance, if I right-click SQL Server Agent,  the options to Start, Stop, and Restart are greyed out.

Using SSMS 2008R2, from the same client machine using the same credentials connecting to the same server, the options are available and functional when I right-click.

This is true for all servers in our environment.

What do I need to configure in order to be able to manipulate SQL services in SSMS 2014?


texts: Was 'ActiveX Scripting' step type secretly removed from SQL Server 2016?

texts:
Migrating our database from SQL Server 2012 to 2016, we can't recreate an SQL Agent job that involves an ActiveX Scripting step written in VBScript.

The error message suggests that 'ACTIVESCRIPTING' is not an allowed value for the @subsystem parameter of sp_add_jobstep.

I know ActiveX Scripting is marked as "will be removed in a future version of SQL Server". However I cannot see anything about it actually being removed in this version of SQL Server.

SQL Server documentation seems to suggest it is still available in SQL Server 2016:

Create an ActiveX Script Job Step (SQL Server 2016)
Documentation on sp_add_jobstep, updated in March 2017, still lists @subsystem ='ACTIVESCRIPTING' as available.

Was @subsystem ='ACTIVESCRIPTING' in fact secretly removed in an undocumented way, or did we miss an installable component that enables it?


texts: MySQL used all drive space running an ALTER and now won't start

texts:
I tried to alter a table without paying attention to the size, and MySQL ate up all the free space on the drive. I deleted a million or so old records but the space was still being used.

Googling suggested that restarting the MySQL server would help, but the server won't start:
[FAIL] /etc/init.d/mysql: ERROR: The partition with /var/lib/mysql is too full! ... failed!

So now I'm a bit stuck. My database only has one (very big) table:

# ls -lh
total 18G
-rw-r--r-- 1 mysql mysql    0 Sep 25  2014 debian-5.5.flag
-rw-rw---- 1 mysql mysql  18G Jul 13 15:17 ibdata1
-rw-rw---- 1 mysql mysql 5.0M Jul 13 15:17 ib_logfile0
-rw-rw---- 1 mysql mysql 5.0M Jul 13 15:15 ib_logfile1
drwx------ 2 mysql mysql 4.0K Jul 13 11:44 log
drwx------ 2 mysql mysql 4.0K Sep 25  2014 mysql
-rw------- 1 mysql mysql    6 Sep 25  2014 mysql_upgrade_info
drwx------ 2 mysql mysql 4.0K Sep 25  2014 performance_schema
drwx------ 2 mysql mysql 4.0K Dec  4  2013 test

So, specifically, my question is: how do I free up the drive space that MySQL used when I tried to alter a large table?

[edit]I should mention that I've got no space on any other drives, and I can't change the size of the drive either :-(

[edit2]In response to comments:

mysql> OPTIMIZE TABLE `emails`;
+------------+----------+----------+-------------------------------------------------------------------+
| Table      | Op       | Msg_type | Msg_text                                                          |
+------------+----------+----------+-------------------------------------------------------------------+
| log.emails | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| log.emails | optimize | error    | The table '#sql-8aa_25' is full                                   |
| log.emails | optimize | status   | Operation failed                                                  |
+------------+----------+----------+-------------------------------------------------------------------+
3 rows in set, 1 warning (1 hour 12 min 10.62 sec)

# df -h
Filesystem      Size  Used Avail Use% Mounted on
rootfs           20G   19G     0 100% /
udev             10M     0   10M   0% /dev
tmpfs           100M  128K  100M   1% /run
/dev/xvda1       20G   19G     0 100% /
tmpfs           5.0M     0  5.0M   0% /run/lock
tmpfs           200M     0  200M   0% /run/shm


texts: Query that only get one result per field

texts:
I'm developing with CodeIgniter and I have the following problem:

Let's suposse that I have the following table:

| id |   employee   |     job     |    date    |
|----+--------------+-------------+------------|
|  1 |  John        | sysadmin    | 2017-12-01 |
|  2 |  Charles     | programmer  | 2017-11-24 |
|  3 |  Peter       | programmer  | 2017-11-29 |
|  4 |  Chris       | g.designer  | 2017-06-30 |
|  5 |  Paul        | web devel.  | 2017-10-08 |
|  6 |  Maria       | g.designer  | 2017-11-05 |
|  7 |  Abigail     | programmer  | 2017-09-24 |

I want to make a query that selects all fields, showing only 3 rows where the employees won't repeat the same job and the results must be ordered by date desc, so it would be:

| id |   employee   |     job     |    date    |
|----+--------------+-------------+------------|
|  1 |  John        | sysadmin    | 2017-12-01 |
|  3 |  Peter       | programmer  | 2017-11-29 |
|  6 |  Maria       | g.designer  | 2017-11-05 |

I have found the word DISTINCT, but after many tries, I couldn't make it to work as expected, so I made the following workaround:

for($i=0; $i<$limit; $i++)
{
    $this->db->where('employee NOT IN("'. implode($employees, '", "') .'")');
    $this->db->order_by('date DESC');
    $q = $this->db->get('employees', 1);
    $r = !empty($q) ? $q->result() : 0;
    $employees[] = $r[0]->employee;
    $result[] = $r[0];
}

Which makes the following query and stack the result into an array

SELECT * FROM employees WHERE employee NOT IN (implode($employees)) ORDER BY date DESC LIMIT 1

it works perfectly, but quite dirty, since if I want 3 rows, It will make 3 queries, that is not so bad, but when I change it to 18 rows, it takes too long to load the webpage.

I have tried the functions group_by() and distinct() from CodeIgniter, but it neither worked as I expected.
Any way to achieve it with only 1 query?


texts: SQL is producing CROSS JOIN but I want INNER JOIN (records being multiplied)

texts:
SELECT *
  FROM        GRNHISTORY GR
  inner Join Inventorytransferlog itl on GR.ReferenceEntryID=itl.ReferenceEntryID

Both tables GRNHISTORY and Inventorytransferlog have their own ID column and unique ids. But we did not need it nor are we using it.
ReferenceEntryID is a foreign key from another table. Both tables have 10 records. Now when I run this query it shows me 100 records which I seem not to be able to understand. It should be showing 10 records as per my understanding. Please correct me where I am wrong.


texts: SQL Server heap tables fragmentation monitoring

texts:
As a SQL Server DBA I have maintenance jobs to regularly rebuild/reorganize indexes. Recently, I ask myself about table fragmentation in SQL Server. Then I have read article regarding heap tables and their fragmentation. We constantly check the status of SQL servers in various way, and I am wondering if adding a check on table fragmentation will be useful.

I’m thinking to develop something like: "Is their a cluster index ?" yes -> do nothing, no -> so check :

percentage of fragmentation > x%

numbers of forward pointing > x

numbers of rows > x

if x > some values then raise an alert.

What do you think of setting up that kind of check, useful or not?

Are Defragmentation of heap tables going to reduce physical size of database's files on disk ?

EDIT :

I finally use this piece of code to detect heap tables which need to be rebuild :

SELECT Database_id, Object_name([object_id]) as TableName, Index_type_desc,
Avg_fragmentation_in_percent, rowmodctr, forwarded_record_count
From sys.dm_db_index_physical_stats(db_id(),object_id(''),null,null,'detailed') AS SDDIPS
Inner join sys.sysindexes AS SI on SDDIPS.[object_id] = SI.id
AND SDDIPS.index_id = SI.indid
--Inner join sysobjects AS SO on  SI.id = SO.id
Where index_level = 0 AND index_type_desc = 'HEAP' AND Avg_fragmentation_in_percent > 40
AND rowmodctr > 100 AND forwarded_record_count > 1000

However it runs for a long time period and performance like PLE collapse


texts: "key violation" when trying to import data into MS Access

texts:
I am trying to import data into an MS Access table from a flat ASCII file.  I've done this many times before but now I am getting a "key violation."  There is only one key in the table and it's an autonumber, so this is not just a simple matter of duplicating previous keys.  I have looked over other potential problems like integrity violations but have found nothing.  So: Does MS Access keep any more detailed records about which field(s) have the problem exactly?  And if so, how to I call this information up?  Thanks.

Addendum:  I kept hitting the import button and trying to think of what else there might be to examine and then, lo and behold, it worked.  I didn't change anything.  It just worked all of a sudden.  Anyone ever hear of this happening?


texts:
Say have table like:

user_uuid | value | state
------------------+------+-------
uuid_1    |  111 | t
uuid_1    |  111 | f
uuid_1    |  111 | f
uuid_1    |  111 | f
uuid_2    |  333 | t
uuid_2    |  444 | t
uuid_2    |  555 | f
uuid_3    |  666 | t
uuid_4    |  777 | f
uuid_1    |  222 | t
uuid_1    |  222 | t

Task is select total count for each user_uuid and percent from total count to count with some value and state.
For example:
total count for uuid_1 is 6.

We need to get percent with value = 111 and status 'true'.

For 'uuid_1' count is 1. So need to calculate percent from 1 to 6

For 'uuid_2' and others counts are 0 so percents are 0 too.

I tryid this SQL:

 SELECT tq.user_uuid
     , COUNT(1) AS total
     , t.cnt
  FROM test_query tq
 CROSS
  JOIN (SELECT COUNT(user_uuid) AS cnt FROM test_query ) AS t
 GROUP
    BY tq.user_uuid, t.cnt;

But i cannot get how to select in JOIN part count for some conditions (like state=true, value=111)


texts: SQL Server : keep track of all connections/disconnections

texts:
I have a Microsoft SQL Server 2008r2 SP1.

I need to know which users connect to, and disconnect from, database.

Ideally, I would like a list of (datetime, username, connect/disconnect).

Can this be done using extended events? Or is there a better way?


texts: Mysql database offline-online synchronization

texts:
I need to synchronize database on server with database of local server upon active internet connection.

For its implementation I execute mysqlbinlog utility to read transaction logs and write the transaction queries into a sql file

Thereafter I execute sql file on database server to apply transaction updates on database on server

But I need to fetch only latest transaction logs within specific time interval across multiple binary log files . Please assist me in its implementation


texts: Which is the best tool to monitor replication on MySQL 5.5.20?

texts:
I have just installed and setup replication for 3 MySQL 5.5.20 server( 1 Master/ 2 slaves). I intend to add more slaves to the equation.

Which is the best software tool to monitor the replication status ( like SHOW SLAVE STATUS \G;)

Also i want to know, how much is the replication lagging in time and error reports if there is any error in replication.

Thanks in advance


texts: How do I do a join and exclude certain records?

texts:
Table STUDENT has columns ID, LASTNAME, FIRSTNAME

Table TEACHER has columns ID, TEACHER_ID, STUDENT_ID

Students can have many teachers. Teachers can add students to their class.

I want to be able to list all students available to add for a teacher. Once the teacher adds that student, they are no longer available to see on their list; however, other teachers can still see the student so they can add them to their list.

What would the query look like for the view page? I would like for the view page to be the same page for all teachers and the data to only change.

SELECT ID, LASTNAME, FIRSTNAME
FROM STUDENTS
LEFT JOIN TEACHERS ON TEACHERS.ID = STUDENTS.ID
WHERE TEACHERS.ID <> $teacherID  ???

This is where I get confused. It doesn't return results except for the ones in TEACHERS table that don't have the $teacherID (that's not how I want it to work). How Do I pick all students except the one's that the current teacher has already added?

I'm coding in PHP and using DQL, but any help on just the query part would be great.

Hope that makes sense. I confuse myself.


texts: How to format a date field into diferente languages in the same command? (multiple locales)

texts:
The following will return the day in Italian:

SET lc_time_names = 'it_IT';
select date_format('2018/01/01','%W') as day_italian;

However I need to convert or format the date into multiple languages, so it would return me another column in English, Japanese, so on...

My problem is that I have to set the locale BEFORE running the select command.


texts: How to find total used and remaining space in Azure SQL mi

texts:
I queried sp_databases and the sum of space occupied by all db's was 380Gb. The total allocated space for the managed instance is 800Gb.

But, enquiring with the infra team found that we have only 40gb of space left.

What am I getting wrong here? How to check the total used and remaining space in a MI.?


texts: SQL Server index randomly performs poorly

texts:
In a SQL Server and an Entity Framework based PMS application, we have a table that stores data of Postings (for invoices). One of the columns in this table is the following:

[PostingDate] [datetime] NOT NULL

It has no triggers or constraints or anything else, except for an index that is created extremely simply, like the following:

CREATE NONCLUSTERED INDEX PostingDateIndex
ON dbo.Posting(PostingDate)

For some reason queries against this index randomly become slow, about twice per month, while the server of the application is 24/7 online (not even reboots) and - being a PMS - is used frequently through day and night.

DBCC CHECKTABLE('dbo.Posting')

does not find any errors when the queries slow down. The only symptoms are that queries that heavily rely on filtering rows by this PostingDate column start getting horribly slow, they usually go out of the 10min timeout our queries have (when the index is okay they take less than 30sec to complete).

The dbo.Posting table itself still remains useful, you can insert and edit rows, you can also make simple selects, whatever you want, but when you start queries that are heavily dependent on PostingDate, they just run infinitely.

The database has over a dozen other indexes (one of them is on another, int type column of this very same table) but none of them encounters this issue. Also the issue randomly occurs both with SQL Server 2014 and SQL Server 2016.

To fix it all we have to do is right click on the index in Management Studio and select Rebuild. Once that is done everything runs quickly again... however the issue randomly comes back and it's not really acceptable in a production environment that some of the queries just die until we manually rebuild an index.

I have absolutely no idea what could cause this issue and why only this one specific index is affected. Could anyone point me in the right direction?
How do I even find what causes it?


texts:
Assume that we have a secondary index for an attribute in a table and
  we want to execute a range query on that attribute. Recommend a
  possible approach to  reduce the number of page accesses
  for this query.

This is from my advanced databases course.


texts:
I have difficulties with my codes in inner join. I need to join 4 of my tables but it has a error:

  "Not unique table/alias: 'tbl_users'"

and when I added alias my table didn't recognized and its attribute.

$sqltran = mysqli_query($con, "
    SELECT  `tbl_users`.`userid` , `tbl_applicant`.`applicant_id`,`tbl_payment`.`userid`

    FROM `tbl_users` , `tbl_applicant`

    INNER JOIN `tbl_users` ON `tbl_users`.`userid` = `tbl_applicant`.`applicant_id`

    INNER JOIN `tbl_applicant`  ON `tbl_applicant.applicant_id` = `tbl_payment`.`userid`

    WHERE `u_id.userid` = '".$_SESSION['userid']."'"
)or die(mysqli_error($con));

$arrVal = array();

thank you!!..
i'm hoping for your answers.


texts: How to grant all privileges on a database?

texts:
There are many almost-duplicates to this question and all I found so far were wrong and/or incomplete, so I feel we need a canonical answer.

In PostgreSQL, how can I grant all permissions on a database to a user, without giving them SUPERUSER permissions?

This includes all tables, sequences, types, triggers, indexes, procedures and whatever other objects are currently in the database and any objects that are created in the database at a later time, by this user or others. Of course, it also includes to ability to create objects in the database.


texts: How to retrieve granted privileges without access to the database “mysql”?

texts: As a MySQL user without access to the mysql database, is it possible to retrieve my privileges?


texts:
When I run the following query:

SELECT S.OWNER   AS SYN_OWNER,
  S.SYNONYM_NAME AS SYN_NAME,
  S.TABLE_OWNER  AS OBJ_OWNER,
  S.TABLE_NAME   AS OBJ_NAME,
  CASE
    WHEN O.OWNER IS NULL
    THEN 'MISSING'
    ELSE O.STATUS
  END AS OBJ_STATUS
FROM DBA_SYNONYMS S
LEFT JOIN DBA_OBJECTS O
ON S.TABLE_OWNER = O.OWNER
AND S.TABLE_NAME = O.OBJECT_NAME
WHERE O.OWNER   IS NULL
OR O.STATUS     != 'VALID';

It gives me the following output:

PUBLIC  GV$LOADPSTAT    SYSTEM  GV_$LOADPSTAT   INVALID
PUBLIC  GV$LOADISTAT    SYSTEM  GV_$LOADISTAT   INVALID
PUBLIC  V$LOADPSTAT SYSTEM  V_$LOADPSTAT    INVALID
PUBLIC  V$LOADISTAT SYSTEM  V_$LOADISTAT    INVALID
PUBLIC  LOADER_CONSTRAINT_INFO  SYSTEM  LOADER_CONSTRAINT_INFO  MISSING
PUBLIC  LOADER_COL_INFO SYSTEM  LOADER_COL_INFO MISSING
PUBLIC  GV$DB_TRANSPORTABLE_PLATFORM    SYS GV$_DB_TRANSPORTABLE_PLATFORM   MISSING
PUBLIC  LOADER_COL_TYPE SYSTEM  LOADER_COL_TYPE MISSING
PUBLIC  LOADER_OID_INFO SYSTEM  LOADER_OID_INFO MISSING
PUBLIC  LOADER_PARAM_INFO   SYSTEM  LOADER_PARAM_INFO   MISSING
PUBLIC  LOADER_FULL_ATTR_NAME   SYSTEM  LOADER_FULL_ATTR_NAME   MISSING
PUBLIC  LOADER_INTCOL_INFO  SYSTEM  LOADER_INTCOL_INFO  MISSING
PUBLIC  LOADER_NESTED_VARRAYS   SYSTEM  LOADER_NESTED_VARRAYS   MISSING
PUBLIC  LOADER_LOB_FLAGS    SYSTEM  LOADER_LOB_FLAGS    MISSING
PUBLIC  LOADER_DIR_OBJS SYSTEM  LOADER_DIR_OBJS MISSING
PUBLIC  GV$SYSTEM_FIX_CONTROL   SYS GV$SYSTEM_FIX_CONTROL   MISSING
PUBLIC  LOADER_PART_INFO    SYSTEM  LOADER_PART_INFO    MISSING
PUBLIC  LOADER_COL_FLAGS    SYSTEM  LOADER_COL_FLAGS    MISSING
SYS DEF$_SCHEDULE   SYSTEM  DEF$_SCHEDULE   MISSING
PUBLIC  GV$TRANSPORTABLE_PLATFORM   SYS GV$_TRANSPORTABLE_PLATFORM  MISSING
PUBLIC  LOADER_TRIGGER_INFO SYSTEM  LOADER_TRIGGER_INFO MISSING
PUBLIC  LOADER_TAB_INFO SYSTEM  LOADER_TAB_INFO MISSING

If I try to recompile or drop these:

ALTER PUBLIC SYNONYM GV_$LOADPSTAT COMPILE;

It says:

SQL Error: ORA-01031: insufficient privileges
01031. 00000 -  "insufficient privileges"

How could this be so? I'm logged in as SYSTEM.

If I try to drop it:

DROP PUBLIC SYNONYM GV_$LOADPSTAT;

It says:

SQL Error: ORA-01432: public synonym to be dropped does not exist
01432. 00000 -  "public synonym to be dropped does not exist"

How can it not exist when the query result shows it?


texts: If a database only ever has one insert, is it bad to index every possible column combination?

texts:
I am working on a reporting system that will require large select queries, but is based on a database that is only filled once. The database management system is Microsoft SQL Server 2017. There is probably a better way to design a system like this, but let's approach this theoretically.

Theoretically speaking:

If we have a very large database (150M+ rows on several tables)
And we can assume the database will only be populated once.

Could indexing every possible column combination have a negative performance impact on a select query?


texts:
I am trying to import tables from an unknown DBMS.  The file extensions for a table end in -nothing-, .d and .k.

Presumably there is a table file, an index file and a memo file (like FoxPro uses) but I can't work out what the database file format is.

An example table can be found here.


texts: Change month partition to day partition with current data in Oracle 12c

texts:
I have sales table and currently sales table have interval partition by month.

I changed partition type to month to day;

ALTER TABLE sales  set interval (NUMTOYMINTERVAL(1, 'DAY'));

However current data still in Month partitions.

How can i change partition type with existing data on table?


texts:
I just need to confirm that I understand something correctly:

I recently viewed an SO question in which a user posted an answer in Linq like:

from p in db.table where p.column.AddMinutes(1) > DateTime.Now select p

To those unfamiliar with Linq, I would expect the output of that statement (not tested it in fairness) to be:

SELECT *
FROM table t
WHERE DATEADD(min, 1, t.column) >= GETDATE()

I posted an reply to this saying that the datetime manipulation should be on the variable (in this case GETDATE()) so in fact the statement should reflect something like:

SELECT *
FROM table t
WHERE t.column >= DATEADD(min, -1, GETDATE())

In my reply, the bits i'm now unsure of, assume the following:

Indexes will not be used because of the manipulation of the column
The query plans will be different partly because of the above (not tested, assuming so)
Because of the above, the 1st query will actually perform worse than the 2nd.

My question:

Have I missed anything out in my reasoning? Am I correct? Lastly, does any body have any good articles on SARGability?


texts: Are there tips for writing Oracle DDL Triggers for auditing? Or should I just use AUDIT?

texts:
I've been tinkering with writing a trigger to log my table creation and manipulation for later tracking and review.

The challenge is that my trigger logic is stored in the same schema on which I've put the trigger.  Because of this, whenever I recompile the trigger or the package or whatever, the trigger itself fires. Also, if another component requires a recompile, the trigger fires.

The trigger has been failing and, in fact, locking my database session.  I have started putting guards on the trigger to prevent this and haven't worked it out yet.

Perhaps someone on the site has experience or advice?  Or just tell me to use AUDIT commands instead?


texts: Query SQL Server from Excel with parameters linked to cells

texts:
I have a bunch of Excel spreadsheet that have data connections to a SQL Server DB (Hosted by AWS RDS) and I follow exactly this procedure to get those connections up and running with the parameters linked to cells: https://superuser.com/a/758792/164558

My connection string ends up looking like this:

DRIVER=SQL Server Native Client 11.0;SERVER=blabla.yadayada.eu-west-1.rds.amazonaws.com;UID=MyUserID;PWD=MrPasswordMan;APP=Microsoft Office 2013;WSID=COMPUTER-NAME;

And this works perfectly on my PC (I have SQL Server installed), I can link to cells with dates and it accepts them as date parameters. However when I try refresh the data in this spreadsheet from other PCs (that do not have SQL Server installed on them), the data won't refresh throwing ODBC errors about default driver's not being specified.

If I change the driver in the connection string to DRIVER=SQL Server (i.e. drop the native client bit) then it will work on the other PCs. However, it now will only accept text cells for date parameters instead of date cells!

Is there a way to get those SQL Server native client 11.0 drivers installed on the other PCs? Is this something that can be solved via the odbcad32.exe (which I do not know anything about)? Or if not, does anyone know how to get the normal SQL Server driver to accept cells containing dates and not date strings to work as parameters?


texts: Migrated a database to mySQL 8.0.17, unable to upgrade or start service

texts:
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.


texts: What does the presence of rsAccessDenied mean?

texts:
I'm troubleshooting a SSRS security issue where one user is being denied access, while another user with apparently identical settings is working as expected.

I'm seeing "The permissions granted to user 'mydomain\myAccount' are insufficient for performing this operation" in the browser, but curiously NOT seeing the (rsAccessDenied) at the end.

Is this indicative of something that can help me troubleshoot the issue?


texts: Difference installing SQL Server 2014 on stand alone vs. VM Cluster

texts:
I have installed SQL Server many times on standalone servers with DASD and SAN storage allocated to it.  We have a new Hyper-V Virtual machine Cluster set up for us by a third party IT shop.   I am planning to install SQL Server 2014 on it and would like advice on what I should look out for and what to keep in mind.
What things are different or additional tasks I should prepare for as compared to a simple install on a stand-alone server.

There are 2 nodes to each cluster one for fail over clustering.  Do i just need to install 1 instance of SQL server in 1 of the nodes ?


texts: SQL Server - linked Oracle server: EXECUTE permission denied on 'xp_prop_oledb_provider'

texts:
Inside of our SQL Server, we have created a linked server to an Oracle server.  We ave mapped several users to the Oracle server and specified the credentials.  On the Linked Server 'Security' properties, it says, "For a login not defined in the list above, connections will:" and we have selected "Be made using this security context:" and specified an Oracle user with read-only access.

Now, when we open SQL Server Management Studio as a user that is not explicitly specified in the list defined for the Oracle linked server, it uses the credentials with read-only access to connect.  However, when we try to expand 'Catalogs' under the linked Oracle server, we receive the error:

The EXECUTE permission was denied on the object 'xp_prop_oledb_provider'


texts:
I (well, my cron script) tried killall mysqldump and that didn't end very well - mysql server stopped accepting connections after a while.

It was Debian Jessie machine with mysql 5.5.55-0+deb8u1.

Usage scenario was:

there was a long running (few hours) SELECT query which was either really that slow or the client that sent it was having problems (query state was Sending data), but all the other queries were happily coming and going (only the load was perhaps little higher).
in the night backup was being run with mysqldump --max_allowed_packet=2147483648 --hex-blob --single-transaction --master-data --routines --order-by-primary --databases db1 db2 db3... | pigz -p8 > backup.sql.gz. It never finished, probably because it was waiting for the SELECT above to finish first (guessing here - it was only thing which looked out of ordinary, and same setup worked OK for months).
cron job run in the morning, which had killall -q mysqldump which was supposed to safely terminate backup in case it was not finished by set time (notifying the admin to examine and fix the problem later), thus allowing people to continue working with mysql server normally.
result however was full connection table and thus no user able to log in to mysql server. There was FLUSH /*!40101 LOCAL */ TABLES query stuck in Waiting for table flush and hundreds of SELECT queries stuck in same Waiting for table flush state.
in addition, admin killing LOCK TABLES mysql query didn't help, as other SELECT queries remained in Waiting for table flush (which seems to be intended behavior?)

Restarting mysql server finally "fixed" the problem.  However, Wanting to avoid this situation (and emergency admin interventions) repeating , I'd like to safely terminate mysqldump backup in Debian Jessie mysql-5.5.55 (or upcoming Debian Stretch mariadb-10.1.23-8). Is there a way?

If not, what are other options for accomplishing mysql backup and avoiding server load in the morning (which is - in this case - almost as bad as completely hung server)?

(I'd like to stay with Debian Stable packages if at all possible)


texts:
We have a moderately busy WordPress blog that runs currently on MySQL 5.5, and I'm wondering if I would experience any significant performance upgrades by moving it to MySQL 5.6?  Anyone know?

I'd also appreciate any thoughts on using MyISAM over InnoDB for the engine. Any benefits or drawbacks with respect to WordPress?