Category Archives: mysql

  • 0

my phpmyadmin is not working

Category : mysql , programming , wordpress

my phpmyadmin is not working

my phpmyadmin is not working

 

Sometime it happens to your wampserver that accessing phpmyadmin gives error and there everything is correct , there you just need to follow this following procedure
Before following
Open your mysql console and type there
FLUSH PRIVILEDGES
Find the following file in  wamp-> apps-> phpmyadmin
config.inc
Find following  lines
$cfg[‘Servers’][$i][‘password’] = ”;
and replace it with the following
$cfg[‘Servers’][$i][‘password’] = ”;]

And replace them with

$cfg[‘Servers’][$i][‘password’] = ”;
and replace it with the following
$cfg[‘Servers’][$i][‘password’] = ‘root’;]


  • 0

structured query language string functions in details

Category : mysql

structured query language string functions in details

ASCII() Returns numeric value of left-most character
BIN() Returns a string representation of the argument
BIT_LENGTH() Returns length of argument in bits CHAR_LENGTH() Returns number of characters in argument
CHAR() Returns the character for each integer passed CHARACTER_LENGTH() A synonym for CHAR_LENGTH()
CONCAT_WS() Returns concatenate with separator CONCAT() Returns concatenated string
CONV() Converts numbers between different number bases
ELT() Returns string at index number
CONV() Converts numbers between different number bases ELT() Returns string at index number
EXPORT_SET() Returns a string such that for every bit set in the value bits, you get an on string and for every unset bit, you get an off string
FIELD() Returns the index (position) of the first argument in the subsequent arguments
FIND_IN_SET() Returns the index position of the first argument within the second argument
FORMAT() Returns a number formatted to specified number of decimal places HEX() Returns a string representation of a hex value
INSERT() Inserts a substring at the specified position up to the specified number of characters
INSTR() Returns the index of the first occurrence of substring
LCASE() Synonym for LOWER()
LEFT() Returns the leftmost number of characters as specified
LENGTH() Returns the length of a string in bytes
LOAD_FILE() Loads the named file
LOCATE() Returns the position of the first occurrence of substring
LOWER() Returns the argument in lowercase
LPAD() Returns the string argument, left-padded with the specified string
LTRIM() Removes leading spaces
MAKE_SET() Returns a set of comma-separated strings that have the corresponding bit in bits set
MID() Returns a substring starting from the specified position
OCT() Returns a string representation of the octal argument
OCTET_LENGTH() A synonym for LENGTH() ORD() If the leftmost character of the argument is a multi-byte character, returns the code for that character
POSITION() A synonym for LOCATE() QUOTE() Escapes the argument for use in an SQL statement REGEXP Pattern matching using regular expressions
REPEAT() Repeat a string the specified number of times
REPLACE() Replaces occurrences of a specified string
REVERSE() Reverses the characters in a string
RIGHT() Returns the specified rightmost number of characters
RPAD() Appends string the specified number of times RTRIM() Removes trailing spaces
SOUNDEX() Returns a soundex string SOUNDS LIKE Compares sounds
SPACE() Returns a string of the specified number of spaces STRCMP() Compares two strings
SUBSTRING_INDEX() Returns a substring from a string before the specified number of occurrences of the delimiter

SUBSTRING(), SUBSTR() Returns the substring as specified
TRIM() Removes leading and trailing spaces UCASE() Synonym for UPPER()
UNHEX() Converts each pair of hexadecimal digits to a character
UPPER() Converts to uppercase


  • 0

What are differences between different storage engines

Category : mysql

What are differences between different storage engines

What are differences between different storage engines

Inno DB
CSV
Merge
Blackhole
Federated
eBay Memory Engine
Maria
PBXT
MySQL storage engines
NitroEDB
XtraDB
eBay Memory Engine
Solid

NDB,NITRO EDB AND ebay Memory engines brief detailed study is given below for those developers who have keen interest of learning other engines other than mysql.

The Percona XtraDB storage engine is a drop-in and backward-compatible InnoDB storage engine.
This engine includes all the listed features of InnoDB but includes specifi c modifi cations to provide additional instrumentation, better memory management, and performance and scalability improvements for large core and highly concurrent work environments.

NDB Storage Engine

This engine is actually an entire product originally purchased by MySQL and incorporated into the MySQL Cluster product. NDB includes a number of key features that distinguish it from a more traditional MySQL storage engine. The NDB engine provides transaction support and synchronous replication ensuring that all data nodes are consistent. It also provides a native C interface to directly with the data by passing the SQL interact if desired for three to fi ve times performance improvement.

NDB has a number of strengths and weaknesses in comparison to the MySQL server product.This does not make this a drop-in replacement for a MySQL replication topology.
NDB is popular with the telecommunication and gaming industries that use primary key lookups, few joins, and require much high availability.

eBay Memory Engine

The eBay Memory engine extends the default Memory storage engine by providing variable-length record support. As discussed in the Memory section, one key use of this engine is for session management.One key disadvantage here is the fi xed-width limitation. This engine enables a far greater amount of in-memory session data to be maintained, and could be ideal for large web sites that manage in-session information. This engine is also the default temporary table engine and Memory storage engine for the Drizzle product.

Nitro EDB Memory Engine

The NitroEDB storage engine was developed by Nitro Security. The underlying database product was developed in relative isolation from traditional relational databases and included some amazing features, including insert performance at 50,000 statements per second and aggregated index queries on large data sets up to 1,000 times faster than existing storage engines. This engine also supported patented technologies in N-Tree management that extended traditional relational functionality.

Solid Memory Engine

Solid is a transactional engine from Solid Tech that was acquired by IBM and effectively shelved. The current IBM solidDB product page does not include any references to the originally developed MySQL storage engine.


  • 0

Brief note of PBXT engine

Category : mysql , programming

Brief note of PBXT engine

Brief note of PBXT engine

 

pbxt engine is very dynamic storage engine and gives key features for developers like Row level locking Truly pluggable ,Short auto recovery ,Transactional

The Primebase Transaction engine (PBXT) was the fi rst truly pluggable engine and leverages underlying internal MyISAM data structure to achieve a greater compatibility with MySQL.

 

 

Key features of PBXT engine:

Row level locking
Truly pluggable
Short auto recovery
Transactional
ACID compliant
Logged based design aligns well with SSD technology

 

Limitations of PBXT engine:

Long development lifecycle
Almost solely developed by one individual

 

Important Parameters of PBXT engine:

pbxt_index_cache_size: The amount of memory assigned for index caching.
pbxt_record_cache_size: The amount of memory assigned for caching the table record data.
pbxt_log_cache_size: The amount of memory assigned for the transaction log data.
pbxt_checkpoint_frequency: The time for a checkpoint of the data written to the transaction
log file. This affects the performance of MySQL during a disaster recovery.
pbxt_data_log_threshold: The size of an internal data log file. This affects the maximum amount of data possible in the data tablespace.

 

 

Fast storage engines used in our databases:

 

Inno DB
CSV
Merge
Blackhole
Federated
eBay Memory Engine
Maria
PBXT
MySQL storage engines
NitroEDB
XtraDB
eBay Memory Engine
Solid


  • 0

what is Maria

Tags :

Category : mysql

what is Maria

what is Maria

Brief note on maria storage engine:

Maria crash save feature really amazing as it gives fast level crash saving to auto save and saves users from any accidental happenings.
The Maria storage engine also has a unique history. Originally part of a supported MySQL 5.1 branch of the official product, Maria is now the storage engine of MariaDB, a new MySQLcompatible product created by the original MySQL founder, Michael “Monty” Widenius.

Design of Mari

Maria is designed as a replacement to MyISAM including new features such as automatic crash recovery, foreign keys, and in the future, transactional support. An easy way to think of MariaDB is to consider the relationship with CentOS and Red Hat. This includes all the features of the original product and the closest compatibility but also value added features that are user friendly and hopefully may become features of the original product in the future.

 

Key Features of maria storage engine:

Transactional (in 2.x versions)
supporting ACID compliance, MVCC,
row-level locking
Crash save
Higher insert concurrency
Improved code quality

While we can download a historical version of Maria from the MySQL offi cial web site in the 6.0.11 archive version as used with the Falcon engine, all current development is included in MariaDB and is available from http://mariadb.com.

 

storage engines types

Inno DB
CSV
Merge
Blackhole
Federated
eBay Memory Engine
Maria
PBXT
MySQL storage engines
NitroEDB
XtraDB
eBay Memory Engine
Solid


  • 0

what is replacement for innodb

Category : mysql

what is replacement for innodb
what is replacement for innodb

Falcon
Falcon is a purchased technology from Netfrastructure in 1996. That was incorporated as a storage engine and was designed to be a possible replacement to InnoDB as the default transactional storage engine. This was due to the purchase of Innobase, the creators of InnoDB by Oracle Corporation, a competitor of the MySQL product.
Unfortunately this engine has never lived up to the published hype. A key feature of the now defunct MySQL 6.0 product, Falcon is not included in the current alpha version of MySQL 5.4. With a change in policy of future products from feature-based releases to time-based and feature-complete requirements, there is no schedule on when Falcon will be available and production ready. In addition, the original architect of Falcon is no longer a member of the development team or working actively on the product.

Key Features of Falcon:

Page and record caches
Flexible page size for tablespace
Additional meta data information in the INFORMATION_SCHEMA
Transactional
Supports MVCC (Multi Version Concurrency Control)
Designed for machines with lots of RAM
Dynamic data compression

Limitations of Falcon:

Not a GA product
No roadmap for implementation
Important Parameters
falcon_page_cache_size: This defines the amount of memory used for caching data.
falcon_io_threads: This defi nes the number of background threads for performing disk
writes.
falcon_page_size: This defines the size of the data pages for tablespaces.
falcon_checkpoint_schedule: This defi nes the frequency of checkpoints that synchronize
in memory and disk information. The specification is a crontab format string.
Falcon has a large number of system parameters (currently 29) that you can view using SHOW GLOBAL
VARIABLES LIKE ‘falcon%’. Though there is no output from the commands used by other engines
such as SHOW GLOBAL STATUS LIKE ‘falcon%’ and SHOW ENGINE FALCON STATUS. All diagnostic
information is available via Falcon specific INFORMATION_SCHEMA tables.

The current list is:
FALCON_RECORD_CACHE_SUMMARY
FALCON_SYNCOBJECTS
FALCON_TRANSACTIONS
FALCON_TRANSACTION_SUMMARY
FALCON_SERIAL_LOG_INFO
FALCON_RECORD_CACHE_DETAIL
FALCON_SYSTEM_MEMORY_DETAIL
FALCON_TABLESPACE_IO
FALCON_SYSTEM_MEMORY_SUMMARY
FALCON_VERSION

 

What are storage engines are used in databases:

Inno DB
CSV
Merge
Blackhole
Federated
eBay Memory Engine
Maria
PBXT
MySQL storage engines
NitroEDB
XtraDB
eBay Memory Engine
Solid


  • 0

what is federated server

Category : mysql

what is federated server

What is federated server

 

Mostly used storage engines types:

Inno DB
CSV
Merge
Blackhole
Federated
eBay Memory Engine
Maria
PBXT
MySQL storage engines
NitroEDB
XtraDB
eBay Memory Engine
Solid

Federated Servers

why we needed federated server:

To devise a better solution, in MySQL 5.1, the idea of a Federated Server was developed. This concept
was part of the SQL/MED specifi cation. It essentially lets you create a named database object
called a SERVER that is associated with various connection meta-data information. The other half
of this functionality is that the Federated storage engine can merely specify the server name (as well
as table if it is desired to name the table differently than the Federated table). This means you can
change the connection information of the table that one or more Federated tables use to connect to
their remote data source with a single SQL statement against the SERVER. So, in the 1000-table
scenario, not a single table would have to be changed!

Federated
The Federated storage engine is a storage engine that, instead of accessing data from a local fi le or
tablespace, accesses data from a remote MySQL table through the MySQL client API. It builds SQL
statements internally, based on what the query was against the Federated table, and runs those
statements on the remote MySQL table.
If the query against the Federated table is a write statement such as INSERT or UPDATE, the
Federated storage engine builds a query, deriving the column names and values from internal data
structures that are dependent on the fi elds and values of the original query. Then it executes the SQL
to perform that write operation on the remote table, reporting back to the storage engine the number
of rows affected.
If it’s a read operation, it constructs a SELECT statement also built using internal data structures for column names, as well as WHERE clauses for ranges and indexes. It then executes that statement after which the Federated storage engine retrieves the result set from the remote table and iterates over that result. It then converts the results into the same internal format that all other storage engines use and then returns the data to the user.
A DELETE statement is similar to a SELECT statement in how the column names are built into the constructed SQL statement, as well as in building the WHERE clause. The main difference is that the operation is DELETE FROM versus SELECT, resulting in the rows specifi ed in the SQL statement being deleted and the count of the rows affected being returned to the storage engine, which in turn decrements its total row count.

Characteristics of the Federated Storage Engine:

Transparency: The remote data sources and details thereof are not necessarily known by the
user, such as how the data is stored, what the underlying schema is, and what dialect of SQL is used to retrieve information from that data source.

High degree of function: To be able to have, as much as possible, the same functionality that is had with regular tables.

Extensibility and openness: To adhere to a standard as defi ned in the ANSI SQL/MED (Management of External Data) extension to the SQL standard. Autonomy of data sources: Not affecting the remote data source, not interfering with its
normal operation. This also means that the Federated storage engine cannot modify the definition of the remote data source, as in the case of statements such as ALTER and DROP TABLE
not being sent to the remote data source.

Optimized performance: Utilizing the optimizer to create the most effi cient statements to run on the remote data source. Also, the long-term goal is to have a means of delegating
operations to the local server and remote server according to which is best suited for each operation.
characteristics of the Federated storage engine Column Requirements When creating a Federated table, the table must have the same named columns as the remote table, and no more columns than the remote table.
The remote table can have more columns than the Federated table.

Queries and Results in federated server:

A query on a Federated table internally produces an entire result set from a table on a remote server, and as such, if that table contains a lot of data,
all of that data will be retrieved. One way to deal with huge result sets is to define an index on a column of the Federated table, even if that column
is not indexed on the remote table, and try to use any means to limit the result set. However, note that LIMIT does not aff ect the size of the result
set from the remote table.

Remote Table:
The remote table must be in existence prior to creating the Federated table that references it.

Index Support

The Federated storage engine supports indexes insofar as the column that is defined as an index is specifi ed in a WHERE clause in the SQL query the table generates, and that the column it specifi es is an index on the remote table. This means that you could have a Federated table with an index on a column that is not an index on the remote table, which is not a problem, and in fact can be used to reduce result set size.

can federated table reference other:

Federated Table
The manual states a Federated table can reference a Federated table. This is considered not good.
Transactions
Transactions aren’t supported in Federated, but are support with FederatedX if using a transactional engine (InnoDB, MariaDB, Falcon, and so on) as the remote data source.

What Federated Supports

Federated supports SELECT, INSERT, UPDATE, and DELETE. However, ALTER TABLE cannot be used to change the remote table’s definition but it can be used to modify the local Federated table’s definition.

Using DROP TABLE
This only drops the local Federated table. It’s worthwhile to mention that although the Federated storage engine may not support some features such as transactions as well as other enhancements, there is a fork of Federated called FederatedX, which is a more active development branch of Federated initiated by the authors of
Federated: Patrick Galbraith and Antony Curtis.


  • 0

Brief notes on mysql storage engine

Category : mysql

Brief notes on mysql storage engine

Brief notes on mysql storage engine

 

Different types of storage engines:

Inno DB
CSV
Merge
Blackhole
Federated
eBay Memory Engine
Maria
PBXT
MySQL storage engines
NitroEDB
XtraDB
eBay Memory Engine
Solid

Brief notes on mysql storage engine:

The detailed study of mysql storage engines reveals us that this is normally used engine in our sql programming and what key features it has on developers.

MySQL storage engines brief notes:

mysql strorage engines gives greater flexibility for every user and it is best soluiton for all developers.
With the stability of newer engines you should strongly consider a dedicated engine when your requirements are for a  very nice purpose such as a data warehouse. The lack of transaction support when expected and the complexity of consistent backups are a common problem in a mixed engine instance. We should first start with a decision of the default storage engine.

While MySQL provides an easy method to mix and match different storage engines for individual tables within a MySQL instance, care should be taken.  This is generally a decision of a transactional storage engine or a non-transactional storage engine.
This affects the development approach that is undertaken. Generally by default the options chosen are InnoDB or MyISAM, respectively.
However, as discussed there are multiple variances of InnoDB at present, and Maria as a MyISAM replacement.

Integrated Hardware Engines

MySQL storage engines are not limited to just software. Two commercial providers include specialized hardware with their respective implementation of customized MySQL storage engines.

Virident

Virident is a combination of custom improvements in memory management based on the InnoDB
storage engine, as well as providing a dedicated memory hardware appliance
http://www.virident.com.

Kickfire
Kickfire is a column-oriented data store with additional specifi c hardware for compression and indexing. Due to the nature of requiring the specialized hardware it’s not possible to evaluate or demonstrate this product.
http://www.kickfire.com.


  • 0

csv storage engine

Category : mysql , programming

csv storage engine

csv storage engine

 

what types of storage engines are available:

Inno DB
CSV
Merge
Blackhole
Federated
eBay Memory Engine
Maria
PBXT
MySQL storage engines
NitroEDB
XtraDB
eBay Memory Engine
Solid

CSV
The CSV (Comma Separated Value ) storage engine allows the management of an underlying CSV file via SQL statements. In MySQL 5.1, the general query log and the slow query log are actually implemented as the CSV engine.

Key features of csv:

 

Interchangeable data with other programs
Fast import time via fisle copy
Key Features of CSV :
Plain text data format

Limitations of CSV :

Non-transactional
Does not support indexes
Table level locking

Understanding CSV Table Usage:

How csv stores data in table:

An CSV table is represented as three separate files in the file system located in the defi ned data directory for the MySQL instance.
table.CSV: This is the table data file
table.CSM: This is the CSV table meta data
table.frm: This is the table format definition file.


  • 0

Merge storage engine

Category : mysql , programming

 Merge storage engine

Merge storage engine

Normally used storage engines:

Inno DB
CSV
Merge
Blackhole
Federated
eBay Memory Engine
Maria
PBXT
MySQL storage engines
NitroEDB
XtraDB
eBay Memory Engine
Solid

Merge Storage engine:
The Merge storage engine is effectively a view over MyISAM tables. This engine was originally designed to overcome operating system limitations when file systems could not support individual
files greater than 2G.
With Merge you can select data from multiple identical defined tables. You can then update and
delete data appropriately. Inserts occur on the last specified table in the merge definition. While you
can perform DML and SELECT statements on the Merge table, you can also perform these on the
underlying MyISAM tables. Primary key and unique key constraints are also on a per-table basis, not
on the Merge table.

 

parameters of Merge

understanding the properties and parameters of Merge, refer to MyISAM. Though this may appear to be similar to partitioning, there is no intelligence with Merge. Queries are executed against underlying
tables serially.
Merge can be successfully used when you want to manage manual partitioning and improved locking
strategy with MyISAM.

Using myisam-recover=FORCE,BACKUP with Merge. There have been many reported problems in this area.

Design of merge table :

A Merge table is represented by the individual files for each underlying MyISAM table and two additional
 tables in the file system located in the defined data directory for the MySQL instance.
table.frm: This is the Merge table format definition file.
table.MRG: This is the Merge table meta data of underlying tables.

Storage engines



Fatal error: Call to undefined function weblizar_navigation() in /home/websolutionsdone/public_html/wp-content/themes/ielts-career/category.php on line 23