MySQL Forums - Database Administration

The InnoDB log sequence number is in the future (no replies)

Kaustubh Katdare — Wed, 19 Jun 2013 13:27:28 +0000

Our site was getting intermittent database errors and I asked my webhost to check what's happening. After some inspection they found that the database has few issues and tried repairing it. Finally I got following message from them-

----------

I have attempted every repair on the InnoDB database and we are still getting
the InnoDB log sequence number is in the future. At this point to get the
ibdata and the iblogfile to match up once again we will need to restore the
MySQL directory (which includes databases) from the backup we have on the
server. The process should not take to long but there will be some downtime
associated with a restore like this. If this is not the best time to restore
the MySQL directory I can schedule this for a different time. Please let me
know how you would like to proceed with this.
-----------

Can someone tell me what'd be the best way to address this issue. I really don't want to lose out on any data and want the dB to be repaired.

PS: If you need more information, please let me know and I'll get it from our web hosts.

Would really appreciate your help.

Synchronizing with SJA (1 reply)

Delanno Delanno — Wed, 19 Jun 2013 02:46:05 +0000

Good morning,
I have an application on several clients and each with a mysql database, are equal however each application instance has an identical copy of the database on the server, in this scenario I have the following problem, I need to keep some tables synchronized. For example all of the contacts registered in a customer may be viewed and edited by any other, but do not have access to the source code of the system and seek a solution directly into the mysql. One option I found was the SJA, but I have a doubt regarding the control of primary and foreign keys, when a customer register a contact run the risk of generating a key that already exists and an error occurs when synchronizing? Someone had contact with this tool and can tell me if she can make the correct control keys or suggest another option?

thank you!

Database collation (2 replies)

Dadvid Dev — Tue, 18 Jun 2013 12:12:05 +0000

Greetings to all...

I have an arabic database built with the following encoding charset:
latin1 -- cp1252 West European
While converting it to utf-8 Data was corrupted (Could not be displayed properly in HTML with the following header :()

Any way, I have a backup.

My question is: Any one have an idea how I could convert My database from latin1 -- cp1252 West European to utf-8 and having it displayed well in HTML page with any data corruption?

Thanks for all contributers....

What is the diffrence between ClearText Authentication and PAM Authentication? (no replies)

Mahesh Patil — Wed, 12 Jun 2013 06:21:51 +0000

PAM Authentication:
PAM enables a system to use a standard interface to access various kinds of authentication methods, such as Unix passwords or an LDAP directory.

Cleartext Client-Side Authentication Plugin:
client-side plugin can be used to send the password to the server in clear text. There is no corresponding server-side plugin.

Questions:
Has anybody practically tried using PAM authentication OR Cleartext authentication mechanism?
If so please explain how to configure and setup with an example.?

Slow query Logs are auto purging (no replies)

Ashish Kumar — Tue, 11 Jun 2013 08:25:08 +0000

Hi All,
I've a mysql cluster management setup version 7.2.10 server, and mysql version 5.5.29 is installed.
I've checked all the possibilities of getting Slow query logs auto purging after every 3-4 min.
Can any body help me on this.
Thanks in advance.

Severe performance problem with mysql 5.5.19 (8 replies)

Gautam Prashant — Thu, 06 Jun 2013 17:29:08 +0000

I switched to MySQL 5.5.19 from 5.5.30 and noticed severe performance degradation. The website is not working at all. In particular, queries are getting struck and the status variable 'threads_running' goes up to 200.

Server Details:

8 cores cpu, 24 GB RAM
OS- Mac leopard 10.5

innodb_additional_mem_pool_size = 200M
innodb_autoextend_increment = 10
innodb_autoinc_lock_mode = 2
innodb_buffer_pool_size = 8G
= innodb1:10G;innodb2:10G;innodb3:5G;innodb4:5G:autoextend
innodb_flush_log_at_trx_commit = 2
innodb_lock_wait_timeout = 60
innodb_log_buffer_size = 2M
innodb_log_file_size = 200M
innodb_thread_concurrency = 16
innodb_write_io_threads = 8
innodb_read_io_threads = 8

Please suggest me what to do. I have read several forums and did not got any definite results.

temp table or query for API (12 replies)

Stephen Strickland — Sat, 08 Jun 2013 03:03:45 +0000

I have a mysql database that has an API ran in PHP that accesses the database via API. Currently I have a query in the API that combines multiple tables in the database and returns a response in a JSON format.

Would it be faster or better to setup a temp table or stored procedure from the query in the API and then have the API just filter the table? This RESTful API is being accessed through multiple clients around the world. Currently it is being accessed by API around 40-60 times a day, but there are changes that are happening that could increase this to 100's of times an hour.

The response from the API can be seen at the following.

www.serviidb.com/api/video
www.serviidb.com/api/media
www.serviidb.com/api/plugin

The current documentation can be seen at
www.serviidb.com/sites/default/files/Serviidb API_0.pdf

Here is an example of the query that the PHP query.

SELECT distinct node.title as "name", regions.region as region, field_data_field_media_url.field_media_url_value as url, media_type.media_type as mediaType, repositoryType.repositoryType as resourceType, plugins.plugin, media_resource_language.language, node.nid, resolution.resolution, (field_data_field_quality.field_quality_rating/20) as quality, (field_data_field_reliability.field_reliability_rating/20) as reliability, field_data_field_installs.field_installs_value as installCount FROM node left join field_data_field_region on field_data_field_region.revision_id=node.vid left join regions on regions.tid=field_data_field_region.field_region_tid left join field_data_field_media_url on field_data_field_media_url.revision_id = node.vid left join field_data_field_required_plugin on field_data_field_required_plugin.revision_id=node.vid left join field_data_field_source on field_data_field_source.revision_id = node.vid left join repositoryType on repositoryType.tid=field_data_field_source.field_source_tid left join plugins on plugins.tid=field_data_field_required_plugin.field_required_plugin_tid left join field_data_field_media_type on field_data_field_media_type.revision_id = node.vid left join media_type on media_type.tid = field_data_field_media_type.field_media_type_tid left join field_data_field_language on field_data_field_language.revision_id=node.vid left join media_resource_language on media_resource_language.tid= field_data_field_language.field_language_tid left join field_data_field_resolution on field_data_field_resolution.revision_id=node.vid left join resolution on resolution.tid=field_data_field_resolution.field_resolution_tid left join field_data_field_quality on field_data_field_quality.revision_id=node.vid left join field_data_field_reliability on field_data_field_reliability.revision_id=node.vid left join field_data_field_installs on field_data_field_installs.revision_id=node.vid WHERE node.status<>0 and node.type="media" and media_type.media_type ="video"

Visual Studio 2012 Supported in dbForge Fusion for MySQL, v.6.0 (no replies)

Ирина Писклярова — Tue, 21 May 2013 13:18:01 +0000

Devart Team is glad to present the release of dbForge Fusion for MySQL v.6.0 – powerful Visual Studio plugin designed to simplify MySQL database development and enhance data management capabilities. Now includes support for Visual Studio 2012! All users can now benefit from the Devart add-in in the latest version of the IDE.

Together with support for the latest version of Microsoft Visual Studio, all new features and improvements that were made in dbForge Studio for MySQL, standalone application for database administration and management, were added.

Main improvements in dbForge Fusion for MySQL include:

- Integration into Visual Studio 2012.
One installation file serves for installing the plugin into both Visual Studio 2010 and 2012 – users just need to check the needed IDE during installation.

- MySQL connectivity.
The latest version of MySQL server 5.5 is supported

- Database Refactoring.
This feature allows renaming objects in a database and automatically updates links to these objects in the whole database.

- Improved Code Completion and Formatting.
Comprehensive suggestions and flexible capabilities for code formatting will let you standardize SQL code in your team or the whole company.

- Query Builder with support of DML statements and usability improvements.

- Object Dependency Tree - unique feature.

Full list of improvements is available on the tool's features page - http://www.devart.com/dbforge/mysql/fusion/features.html.

Users are welcome to join forum - http://forums.devart.com/viewforum.php?f=23, participate in discussions concerning Devart software, find answers to technical questions and other valuable information on dbForge Fusion for MySQL and other products for MySQL.

For additional information about Devart, visit http://www.devart.com/.

my.ini (3 replies)

Peter Christov — Fri, 17 May 2013 17:23:35 +0000

Hello,

I installed MySQL Server (from mysql-installer-community-5.6.11.0.msi) and it seems, it runs OK.

Nevertheless I miss the file my.ini. I think I can rename the my-default.ini to my.ini and then I can also add the entries I need (e.g. innodb_file_per_table = 1). Is it so??

I am also wondering where the settings I made within the installer are (such as binary logging =on, Port 3306 and so on). I would like to be able to set binary logging =off and thought it can be done in my.ini

Thank you in advice!
Regards Peter

how to import wordpress data base (1 reply)

Hafeez Mughal — Sun, 05 May 2013 15:36:46 +0000

hi i have a problem with upload database .sql formate. i have 1 gb of data in mysql formate how to uplode lage file plz help my website is interesting strange facts plz some body help me i download data from my old website which is expair now studmate.net

Thanks

Hafeez Baig

Alter table crash MYSQL 5.6 (1 reply)

Sunil Divyeshvar — Thu, 02 May 2013 20:42:24 +0000

Hi ,

I am using Mysql 5.6.

whenever i am using "ALTER STATEMENT with ADD FOREIGN KEY" mysql server crash.

i am using ROW_FORMAT=COMPRESSED for some tables and file_format = barracuda.

I read this type of bug was there in Mysql 5.1. but as i am using Mysql 5.6 i am feeling there is some other issue.

Error Log shows following information.

stack_bottom = 4c6430a0 thread_stack 0x40000
/opt/mysql/bin/mysqld(my_print_stacktrace+0x35)[0x91dec5]
/opt/mysql/bin/mysqld(handle_fatal_signal+0x3e8)[0x697888]
/lib64/libpthread.so.0[0x306da0ebe0]
/lib64/libc.so.6(strcmp+0x2)[0x306d2795f2]
/opt/mysql/bin/mysqld[0x9bc540]
/opt/mysql/bin/mysqld(_ZN7handler30ha_prepare_inplace_alter_tableEP5TABLEP18Alter_inplace_info+0x65)[0x5baa65]
/opt/mysql/bin/mysqld[0x75fa56]
/opt/mysql/bin/mysqld(_Z17mysql_alter_tableP3THDPcS1_P24st_ha_create_informationP10TABLE_LISTP10Alter_infojP8st_orderb+0x1dd5)[0x761b75]
/opt/mysql/bin/mysqld(_ZN19Sql_cmd_alter_table7executeEP3THD+0x46e)[0x85e26e]
/opt/mysql/bin/mysqld(_Z21mysql_execute_commandP3THD+0x39c8)[0x715618]
/opt/mysql/bin/mysqld(_Z11mysql_parseP3THDPcjP12Parser_state+0x318)[0x7188c8]
/opt/mysql/bin/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0x827)[0x7191e7]
/opt/mysql/bin/mysqld(_Z10do_commandP3THD+0xd7)[0x71a487]
/opt/mysql/bin/mysqld(_Z24do_handle_one_connectionP3THD+0x116)[0x6e4c76]
/opt/mysql/bin/mysqld(handle_one_connection+0x45)[0x6e4d55]
/opt/mysql/bin/mysqld(pfs_spawn_thread+0x13b)[0x994aab]
/lib64/libpthread.so.0[0x306da0677d]
/lib64/libc.so.6(clone+0x6d)[0x306d2d49ad]

Thanks

insert into tablea if the same two fields exists in tableb (3 replies)

Tim Manochehri — Wed, 01 May 2013 06:04:04 +0000

OK... I have a UNIX User database I created. A user table and a host table.

When adding [insert] users to the host table I want to do a lookup of the user table and if exists [username and uid] then insert into the host table.

I have the insert working as expected... but I would like to do a lookup first and only insert if they previously exist in the user table.

Any ideas on how to accomplish this?

FYI: I am a UNIX Engineer not a DBA...

Tim

DB Compress (2 replies)

Peter Christov — Mon, 29 Apr 2013 14:13:06 +0000

Hello,
how can I compress our MySQL Database (installed on windows) e.g. once a week (e.g. manually). The database file is about 60 GB. After drop of some tables the database file remained as big as before…
Thanks and regards, P.

Hallo,
wie können wir am besten unsere MySQL Datenbank (auf Windows) ab und zu komprimieren? Z.B. die Datenbankdatei ist ca. 60 GB. Nachdem wir einige Tabellen gelöscht haben, blieb die Datei genau so groß….
Danke!, P.

Corrupted Database (3 replies)

Nini John — Fri, 26 Apr 2013 17:05:23 +0000

Hello,

On my environment I have a problem with a MySql Database. Some of my files are corrupted and I cannot acces any record of the database.

On the server I have:
- OS: Windows Server 2003 SP2
- MySql 5.1.35

The disk space used by the database it's about 200GB. Is there a problem regarding the size?

I also want to mention the posibility that certain areas on the disk may be corrupted.

If I successfully force the start of mysql database, I cannot run any commands, not even a "select" because it will crush.

Is there any possibility to recover my data?

Appreciate any answer
Thanks!

Bulk insert operation : Need to add datafiles (2 replies)

Lukas Pise — Mon, 29 Apr 2013 05:34:36 +0000

We need to perform hugh DML Operation in our MySQL Database , where data to be populated is nearly 35 GB. ( INSERT operations )

I have one auto-extending data file, named ibdata1 & two log files : ib_logfile0 and ib_logfile1 in MySQL data directory /var/lib/mysql

innodb_data_file_path = ibdata1:1000M:autoextend

As we are planning to load bulk insert statements , here we add ibdata2 datafile in autoextend mode.

innodb_data_file_path = /ibdata/ibdata1:2000M;/dr2/ibdata/ibdata2:2000M:autoextend

Could you suggest us how we could keep all our Datafiles in autoextend mode and add Datafiles across different mount points.

Error while Pulling mysqlreport (1 reply)

Lukas Pise — Fri, 26 Apr 2013 04:11:58 +0000

While Pulling MySQL Report , I am getting errors , On Linux Server x86_64 x86_64 x86_64 GNU/Linux , where MySQL 5.6.10 is installed

To pull MySQL Report , I have followed these steps

[root@node1 ~]# ls -l mysqlreport-3.5.tgz
-rw-r--r-- 1 root root 41421 4æ 24 18:13 mysqlreport-3.5.tgz

[root@node1 ~]# cd mysqlreport-3.5

[root@node1 ~]# ls
COPYING mysqlreport mysqlreportdoc.html mysqlreportguide.html

[root@node1 ~]# ./mysqlreport

Can't locate DBI.pm in @INC (@INC contains: /usr/lib64/perl5/site_perl/5.8.8/x86_64-linux-thread-multi /usr/lib64/perl5/site_perl/5.8.7/x86_64-linux-thread-multi /usr/lib64/perl5/site_perl/5.8.6/x86_64-linux-thread-multi /usr/lib64/perl5/site_perl/5.8.5/x86_64-linux-thread-multi /usr/lib/perl5/site_perl/5.8.8 /usr/lib/perl5/site_perl/5.8.7 /usr/lib/perl5/site_perl/5.8.6 /usr/lib/perl5/site_perl/5.8.5 /usr/lib/perl5/site_perl /usr/lib64/perl5/vendor_perl/5.8.8/x86_64-linux-thread-multi /usr/lib64/perl5/vendor_perl/5.8.7/x86_64-linux-thread-multi /usr/lib64/perl5/vendor_perl/5.8.6/x86_64-linux-thread-multi /usr/lib64/perl5/vendor_perl/5.8.5/x86_64-linux-thread-multi /usr/lib/perl5/vendor_perl/5.8.8 /usr/lib/perl5/vendor_perl/5.8.7 /usr/lib/perl5/vendor_perl/5.8.6 /usr/lib/perl5/vendor_perl/5.8.5 /usr/lib/perl5/vendor_perl /usr/lib64/perl5/5.8.8/x86_64-linux-thread-multi /usr/lib/perl5/5.8.8 .) at ./mysqlreport line 24.
BEGIN failed--compilation aborted at ./mysqlreport line 24.

But still , I am getting errors , Could some one throw light on this

Regards
Lukas

Start/Stop mysqld (1 reply)

Danny Teok — Fri, 26 Apr 2013 04:19:31 +0000

Hi
I'm seeking help to understand what happens when the following command line is executed:
root@prodn$ service mysqld stop

Yes, it shuts down the MySQL server so access to it is no longer available until the service is starts again.

However, more specifically, is there anything else that happens when the service is stopped?

The reason I ask is the following:
It is not a normal practice to start/stop mysqld service. Our data warehouse DB is a MySQL DB (so, executing the command line above means our data warehouse is offline). Our ETL process also uses this database and in the past 4 months, it has taken on average 8.5 hours. All DML processes are also done in the ETL process.

[Pentaho ETL: mysql-connector-jdbc] -- [mysql: data_warehouse]

Last Wednesday, I've stopped the mysql service, and then restarted it after 30 minutes. Since, I begin to notice our data warehouse (DW) and the overall performance of a SELECT process were more efficient. DW finished almost 4 hours earlier with the same number of rows of data -- it process about 1.8 millions rows daily with inserts and updates.

However, with each passing day, 15-20 minutes is added to the finish time. So, I suspect I may have to restart the service weekly.

Is there an explanation for this behavior? Is this something to do with a mysql cache -- that the cache is cleared when mysqld is restarted? I don't know what other questions are relevant but it'd be brilliant to know what happens when mysqld service restarts.

Can anyone shed some light on this please?

How to create a database in MySQL? (2 replies)

Abdul Khan — Fri, 24 May 2013 07:15:32 +0000

Kindly anyone tell me about creating a new databse in MySQL? I want to create this database for my website: www.onlineustaad.com

mysqldump complains about .my.cnf variables meant for mysql client (1 reply)

Roderick Mann — Fri, 12 Apr 2013 09:06:42 +0000

mysql Ver 14.14 Distrib 5.5.28, for debian-linux-gnu (x86_64) using readline 6.2

I have, among other variables, the following specified in my .my.cnf file:

[client]
init-command='set autocommit=0'

Unfortunately, mysqldump tries to interpret that line and complains:

mysqldump: unknown variable 'init-command=set autocommit=0'

I tried putting in a [mysqldump] section, but it still complains about the client line. It really should ignore anything it doesn't recognize. Is there a better way to get what I want? Thanks!

Function user() (1 reply)

Christophe Le Lannou — Wed, 03 Apr 2013 13:15:44 +0000

Hi,
Reading lot of things about the function user(), but nothing about its properties: Is it a NOSQL function? What is the exact data type of the result?
I am building a function based on that function. Thanks for you help.

"show grants for xyz" does no show all privileges (6 replies)

Didier Wiroth — Wed, 03 Apr 2013 13:13:44 +0000

Hello,

I'm rather new to db administration and was ordered to take care of a mysql db a few months ago.

So here I'm am.

I upgraded from mysql 5.1.X to 5.5.28 on windows 2008 r2 a few month ago. This is the way I upgraded:
1) did a mysqldump for every single db in 5.1.X
mysqldump --default-character-set=utf8 -c -e -v -h 127.0.0.1 -u dump --password=xyz --dump-date --single-transaction BTS > bts.dump

2) shutdown the 5.1.x version
3) started the 5.5.28 version and imported the BTS db like this:
mysql -u root -p < bts.dump

I did that for every single database.

At some point I created 1 user per DB (that are used by our web application servers) and gave them grants (I don't remember the exact syntax I used). The DML queries work as expected.

(All the users have the same name as the database and the host is always 127.0.0.1 f.ex. 'bts'@'127.0.0.1')

But now ...

a) I checked grants like this:
show grants for 'bts'@'127.0.0.1';

Result is:
GRANT USAGE ON *.* TO 'bts'@'127.0.0.1' IDENTIFIED BY PASSWORD 'blahblah'

Only the USAGE grant appears???

b) If I query mysql.db:
select user,host,db,select_priv,insert_priv,update_priv,delete_priv from mysql.db where user = 'bts';

result is:
+------+------+-----+-------------+-------------+-------------+-------------+
| user | host | db | select_priv | insert_priv | update_priv | delete_priv |
+------+------+-----+-------------+-------------+-------------+-------------+
| bts | % | bts | Y | Y | Y | Y |
+------+------+-----+-------------+-------------+-------------+-------------+

The show grants in a) is incomplete. I can reproduce this for every user and every schema/db on this server, the show grants does only show the USAGE privileges the dml stuff does not appear, it is only visible via mysql.db.

I've checked the show grants on our test db running on another server, the privileges are shown correctly select, insert etc privs are displayed correctly.

My question now.

a) Is/Are the mysql schema or some tables or other objects corrupted?
b) How can I solve the problem?
c) Is my upgrade procedure ok, or would suggest taking additional steps?

Thank you very every advice and help!

Kind regards,
Didier

restore from mysqldump --all-databases fails with "cannot add foreign key constraint" (3 replies)

Didier Wiroth — Thu, 21 Mar 2013 07:18:15 +0000

Hello,

I'm testing my backup & restore strategy and actually really glad I did ...

I'm using mysqldumP to backup all our databases:
mysqldump -u root --all-databases > full.sql

And when I try to restore a single database from within the full dump file:
mysql -D bts -o -u root < full.sql
ERROR 1215 (HY000) at line 58: Cannot add foreign key constraint

And this is true for every DB in the file
mysql -D rde -o -u root < full.sql
ERROR 1215 (HY000) at line 2571: Cannot add foreign key constraint
etc

The restoring fails for every DB in the file with foreign key problems.

If I use mysqldump to backup a single DB and restore them from a file containing only one db, restoration is possible without any errors.

The Server I'm doing the dump is:
Server version 5.5.28-log
Protocol version 10

I'm trying to restore the dump on a server 5.5.30 or 5.6.30 ... no luck so far.

Do I miss something here?

Thanks a lot!!!
Didier

Zero-downtime MySQL maintenance and schema operations (no replies)

Petri Virsunen — Wed, 13 Mar 2013 18:03:00 +0000

Tungsten University: Zero-Downtime MySQL Maintenance And Schema Operations

Do you know how to do rolling maintenance on your database hosts so you can make changes without stopping applications? How about upgrading schema and applications themselves? Tungsten clusters have a host of features that can help you with everything from basic administration to complex application upgrades. This webinar will show you the the different types of administration you need to perform, and introduces the procedures to perform them without application downtime.

Course Topics

- What is rolling maintenance?
- How to perform simple maintenance on hosts
- How to plan, test, and perform a MySQL version upgrade
- Standard SQL schema upgrade patterns and how to do them with zero downtime
- Combining SQL schema and application code upgrades
- Structuring applications to make zero-downtime upgrade work better
- Backing out of failed upgrade or maintenance

Course Dates

- Thursday, March 21 @ 10 am PDT/1 pm EDT
- Tuesday, March 26 @ 15:00 GMT/15:00 BT/16:00 CET

Register at http://bit.ly/13qOxgh

MYSQL housekeeping tasks (1 reply)

Alan Mckeown — Tue, 26 Feb 2013 15:10:16 +0000

Hi all,
Am new to MYSQL although have a few years experience with Oracle rdbms.

Are there any general day to day housekeeping tasks that need to be carried out on a MYSQL install?

We will be monitoring the database via a product called dbwatch but in terms of things like log files / trace files etc etc are there the equivalent on MYSQL as there are in Oracle?

regards

Alan

FREE! MySQL Health Check (no replies)

Edwin Desouza — Fri, 08 Feb 2013 04:15:34 +0000

FREE! MySQL Health Check
https://www.mysql.com/news-and-events/health-check/

Passwordless root access (4 replies)

Matthew Exon — Sat, 09 Feb 2013 23:57:16 +0000

Hi,

I want to make a backup cron job run by the root user. At the moment it looks like I'm going to have to hard-code the root user's database password in plaintext in the cron job, which feels like a security sin.

How else can I identify the root user to MySQL when run off the command line? I know how to allow a user to connect from localhost without a password, but I don't know how to allow only a particular unix user to connect as a corresponding MySQL user.

How to deal with huge MYISAM corrupted table (6 replies)

sandeep thakur — Fri, 22 Feb 2013 07:29:13 +0000

Hi,

While executing queries on a table I am getting the below error:
"error no 13 - tablename.MYD file not found"

I am using Mysql 5.1.52 on windows server. The table has around 60million of records & the size of this table is 20GB. Can someone please help me out resolving this issue in less time & as soon as possible.

Sandeep

How do i clone a table? (1 reply)

James Orji — Thu, 31 Jan 2013 14:48:21 +0000

I don't know if it's possible to clone an sql table in such a way that the clonedtables will have a different name and whatever content is added to the main table is also reflected in the cloned table.

Comments in dump (1 reply)

Jens Ruena — Tue, 29 Jan 2013 17:22:19 +0000

How do I get comments within a function form a database dump into the database?

mysql user -h host < dump.txt

dump.txt
=============

DROP FUNCTION IF EXISTS `foo`;
DELIMITER $$

CREATE DEFINER=`user`@`%` FUNCTION `foo`() RETURNS bigint(1)

BEGIN

SET @var1 = "TEST";
#please keep this comment

return 1;
END$$

DELIMITER ;

creation of table with 25000 col how and good practice (1 reply)

Test Rest — Sat, 19 Jan 2013 21:15:57 +0000

Hi

i have a project on my head where the DB structure requires to create me with 20,000 Cols in a table and would be populated with various ans for the same which would be again in INT format i.e 0 or 1

But the main issue is how do i make this table and store the data, will it make sense to put the value in coma seprated values or something similar.

If you can kindly help me with eg if you have any.